Neler Yeni
Hos Geldiniz,

SAPOfis Türkiye üzerinde yer alan içeriklere erişim sağlayabilmek, projelerinizde karşılaştığınız sorunlara birlikte çözüm üretebilmek adına üye olunuz. Unutmayalım ki, bilgi paylaşıldıkça değer kazanır.

SAP BLOG Eclipse Based Query Modeling in BW4HANA Step by Step

SAP Blog

Kayıtlı Üye
Katılım
22 Ara 2017
Mesajlar
304
Beğeniler
3
Puanları
6
#1
The purpose of this document is to provide an overview on the new and improved eclipse based BW query modeling in BW/4HANA system and how this compares from the traditional Bex reporting.

Overview

SAP has been constantly evolving with its new innovations in terms of new application upgrades or be it with a completely new application itself as a next-generation solution with better features, integrations, performance and so on.

With BW/4HANA coming in as a next-generation data warehousing solution, SAP has finally put a full stop to very old and good Bex Query designer giving way to a whole modern, user-friendly, eclipse based BW Query modeling. The new terminology in BW/4HANA is called as BW Query.

Until recent SAP BW on HANA versions from BW7.4, BW query existed but was optional. However, from BW 7.5 on HANA SP4, the BW Query eclipse based has become mandatory which means we no longer use the Bex query designer.

BW Query provides a new, improved, well integration of BW data modeling in one single tool. Which means it is one stop place to create BW data models and Queries. For example, in the earlier version, to change query properties, you would have had to log into the back-end system with transaction codes like RSRT. But with new eclipse based query modeling, all this is achieved on a single platform. It is easy to jump from one object to another, view, change whether it Is modeling or query will be done in a single tool.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F1-8.png&hash=225260a1ab1422ce3500e375c72c96d7


You can view the query result directly using the drill downs, filters etc., without the need to have a JAVA server for web-based querying or to switch to excel based Bex analyzer tool. What more, these queries can be accessed by other visualizations tools, for example, SAP Lumira to create more sophisticated dashboard representations.

Step by Step to create a BW Query

Let us go through each step on how to create a BW query and to have a look and feel of the eclipse based query modeling layouts and options all in one tool.

Please refer my previous posting on BW/4HANA modeling which I will use here to create a BW query on top of the info provider already existing.

Log into BW/4HANA system and connect to your BW project. Right click on the info provider, here it could be a Master data, advanced DSO, Composite provider, or anything else. Here we use the composite provider.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F2-10.png&hash=1488bbb982cdb777a2555bda3670a919


Provide the technical details and finish. There is an option to copy from existing BW query.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F3-9.png&hash=0218489bcb4f6551a1847d34302cf486


You will go to the General tab section. Here you have familiar options to set, like in Bex query designer. Suppressing results, rows, external access permissions, setting key date parameters and variables settings etc. Another new option is to generate HANA view from BW query itself. Below you can see in Remote access section.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F4-8.png&hash=8bb1b0e0bf3427ac619a3b186c75cedc


I will jump to Sheet definition as this will initial step to draw the layout of the query. As you see there are three sections which have the same name and mean the same as in old version but with a different layout.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F5-8.png&hash=f67d866f4ee5fac235524c63de5f555b


You can find the info provider details on the top left of the screen.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F6-6.png&hash=b5d5782d27f8a8e7a91c7dbd198978ea


Drag and drop the fields in respective sections.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F7-6.png&hash=48a8e8f743642c5e0f4de6d631da5c11


Below you will see the key figure setting and options available. These are like old versions, like the conversions, calculating result as, scaling, renaming the description, add text variable and so on.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F8-7.png&hash=ce50c3be48d0641ce2f87071633cc8c9




The characteristic properties as shown below. Like in the old versions. You can define a hierarchy if available and needed.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F9-5.png&hash=895d067ac8270b219fa60ea7219ac8c3


You can go to the filter tab to provide any filter restriction if needed. Drag and drop the required field in the top block – Fixed values and right click and restrict.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F10-7.png&hash=fec2a7f6ef40ad6a0c8630a5e833ce63


You have the option to restrict on value, variables, hierarchy node variables or nodes itself. Operator selection and ranges can be defined.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F11-4.png&hash=f028e4d3c1c83b107c8e67f6f4f61e35


You can define conditions like in Bex. Let’s say for example you want to show the top 10 customers.

Goto Conditions tab, right click on condition workspace and New condition

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F12-2.png&hash=54db8390337e78608d8a236cab43d9ae


You can change the description, click on Add. You can select the KPI from the member drop down. Select the required operator, in this case, its TopN. Provide the details and OK.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F13-3.png&hash=ca0a1f0b66656a6673016a9e0239b32a


Next is to create an assignment on the field level. Click on Assignments tab and select the field, in this case, it is Customer.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F14-3.png&hash=fd9c9044060d91257c38f0c14c877b82


Similarly, you can create Exceptions like in Bex. I will not walk through this. It is very much simple and similar to before.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F15-1.png&hash=3853048ef841ea1a5b39e118d154c7d1


You can see Dependency Tab. This is handy as well to provide information on any other queries which use the same variable for example as used in your query.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F16-2.png&hash=819ef6a0d799928768772f68a6b5cf6f


Now coming to the interesting part which is the Runtime properties. This can be defined from the same query space without having to log into separate application or transaction codes.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F17-2.png&hash=8cf291a6b460bad69d063ae53e0f253f


I will try to provide some information on these properties.

Common Runtime properties:

Process Key figure with High Precision – With this, the decimal floating points are stored as 34 decimals and 16 decimals if this setting is not activated.

No Parallel Processing – The query is processed as per info provider by splitting the query into subqueries.

Calculate Commut Formula after Aggregation – Sometimes calculating after aggregation improves performance.

Generation Log – Gives technical details on the query statistics and inconsistencies can be identified.

OLAP Effort for Query – With this, we use the benefits of Analytic Manager to handle queries with large results. There are options to select. If nothing selected then infinite is considered.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F18-2.png&hash=f7463d9c8071919ebda380d89581dd2d


Runtime Profile Properties

The initial cache settings are same as old versions. I will talk about some features which would be different or new.

Update Cache objects in delta process – The setting helps to only cache the delta updates and not the entire for the complete rebuilding of cache. In the old version, this was available in RSA1 under info provider level.

Operations in SAP HANA – With this setting, we can optimize the operations in SAP HANA DB if we have HANA as underlying DB.

Materialize Intermediate Query Result – This option enables the reuse of intermediate calculations & subtotals.

Use Selection of Structure Elements – This should be activated to ensure only selections and key figures of the columns are passed to the database.

Read Child Members – With this the filters on the structure element will include all its child nodes/members.

Optimization Mode – This setting is to optimize OLAP processor internal memory size and processing.

Stats Detailed Level – This is to control the detail level for statistical data that can be recorded in the system. In earlier BW versions, this option was only available in the Admin workbench (RSA1).

Once the query design is finalized, go to file and click save.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F19-2.png&hash=0808433fdf763bda39f7bdde24bbf895


You can find the query under the Composite provider.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F20-2.png&hash=0c08a04d580ae0b4158f4c94737c05df




I will walk through with creating some other query elements like Variables, restricted/calculated key figures.

Variable

Right click on the query and New Variable

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F21-1.png&hash=03bb44653d65e78831ef076e213ad3fc


Provide the technical details and selections. The details are similar like in older version.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F22-2.png&hash=6e9dfaf150b2c3e24e0c578afc8177ed


Type of Variable

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F23-2.png&hash=a9c1b7097d54fdf8c1fdb10fd7e187a7


Processing by. You can write the exists using the BW GUI in the CMOD like earlier. A new option is create BW variables of processing type SAP HANA Exit

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F24-1.png&hash=1ebcd0fe6276656849425d9f80142b7e


Variable represents

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F25-1.png&hash=fdf218d55d6ab2e0b3fd88ddf178f14a


You select the reference characteristic accordingly as applicable and finis

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F26-1.png&hash=8379753b82111729bccf2b68faa2a62c


Calculated Key Figure:

You can create CKF on info provider level.

Right click on info provider, in this case, the composite provider

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F27-1.png&hash=e6cb5c6dc7ade17555cbfbb087343afa


Provide the technical details and finish

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F28-1.png&hash=16823489b276932b529bcbf19260ab36


In the General Tab, you can define the properties such as Number of decimal places, Scaling, Hide or Display etc. like old versions.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F29-1.png&hash=ac9db1d9f30d0170434341cca109ea73


In the Aggregation tab, you can define the exception aggregation condition to be handled and define the reference characteristic.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F30-1.png&hash=f67115cc0738eef46c8b307a30b45965


Any currency conversion definitions can be done in the conversion tab

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F31-1.png&hash=29adddfcb7c1b6546bac32cc596600e6


The calculation definition parameters can be set in Calculations Tab. Like old versions

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F32.png&hash=2fb07db2cbdf816eb64a526d2f1c8783


In the Formula Tab from the bottom of the screen, you can define the formula to be implemented. You have options to use from other objects such as Key figures, RKF, CKF, Variables, and apply different functions available.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F33.png&hash=99598fa68185120aa26f2b92620a0025


Once the CKF has been defined click on save.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F34.png&hash=75c77cd46ca4e4cbfa54a183616cd05f


You can find the CKF under the Composite provider

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F35.png&hash=51d62dea8c0a6c96d34302a296323ef2




Restricted Key Figure:

Right click on the composite provider

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F36.png&hash=ae590f82e6132843c456f560dfd9f631


Provide Technical details. You have the option to provide the key figure to be used for restriction or you can define this later as well. I will keep this empty for now.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F37.png&hash=1025b55fcf529c988e2cac60acd058ed


The General Tab is like what is explained in the CKF section. However, you will not see the Aggregation tab here.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F38.png&hash=70ed5af535220ac9756d71c37e0d759e


In the selection Tab, right click and you can add the objects. You can continue to create nested objects as well and use re-usable components.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F39.png&hash=c89fd41deb7be0746651fa9003309c99


You can select the key figure to be used.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F40.png&hash=7f77226adf83325cd334b6ecedefdea0


Then right click and add a characteristic to apply restriction On.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F41.png&hash=9b286bf029be54130756367ce4e70c87


Once the RKF has been defined click on Save like done for CKF. You can find the RKF under the info provider.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F42.png&hash=1f66d9f1ad3aaf3a5b3edb52d24c8e43


You have the option to include New selection, New formula like in older version. The design interface is very much like earlier versions.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F43-1.png&hash=26f4d4cc88639846fd249731e09123d5


The query can be executed directly and the data can be analyzed in the eclipsed based BW Modelling area.

The query can be accessed in HANA View if the external HAVA View check was selected as mentioned in the steps above.

proxy.php?image=https%3A%2F%2Fblogs.sap.com%2Fwp-content%2Fuploads%2F2018%2F09%2F44.png&hash=a360e25c88609f6fdea4256ccdfc04ba


The Reporting Preview shows the data format with the measures and dimensions defined in the query sheet definition.

The Analysis List UIBB gives more sophisticated data preview.

Hopefully, I shall provide the delta details on how to consume the query in further reporting tools like Analysis for Office/Lumira and in the HANA Views.

Okumaya devam et...
 
Üst