SAP BW 7.4 SP9 powered by SAP HANA comes with a first version of a query designer integrated in the BW-Modelling Tools (BWMT) in eclipse. With this feature it is now possible to define not only the InfoProviders of SAP BW 7.4 (Advanced DataStore Object, CompositeProvider, OpenODS View), but also queries with specific functionality in eclipse. This enables an end-to-end modelling experience in eclipse.
With the current feature set available with BWMT 1.6 you can choose between various functions:
- Create structures in the row and column axes of the query
- Use reusable restricted and calculated key figures
- Define conditions & exceptions
- Add currency and unit conversion
At present this is a subset what BEx QueryDesigner can offer and should be used in addition to its functionality when simple queries are sufficient. We’ll further enhance the eclipse based query definition in future.
See the full feature complete Query Designer here: #BWonHANA’s New Query Designer
See a query example in eclipse below:
Furthermore a new feature of the BW query in eclipse is to generate SAP HANA views based on BW queries. (Also possible with SAP BW 7.4 SP8 with a generation report)
If a query definition can be expressed as a flat view the calculation can fully take place in SAP HANA and a corresponding HANA view can be generated. To do so the query in eclipse offers a flag to create an “External SAP HANA view”.
This will generate a HANA view based on the query definition into a separate HANA schema which allows consuming the data for example via SQL or further modelling with native HANA views.
At present the following query functions are supported to be generated into SAP HANA views:
- Currency/unit conversion, variables, restricted key figures, global restrictions, inventory key figure (closing balance) and formulas (SAP Note 2080686)
- All other features will either be not supported and then prevent the generation of the SAP HANA view or simply be ignored during generation
- Please find a detailed list of offered functionalities and prerequisites here
The main use case of the HANA view query generation is to be able to leverage the possibilities of the query in regards to conversions, filter and formulas. Having this feature available allows us for example to filter at the earliest possible layer if further logic will be added in HANA or data consumed via SQL externally.
See example of a generated view according the query definition below:
This can now be used in any kind of HANA modelling or for direct SQL consumption like shown in the screenshot below:
The following video demonstrates the functionality in the system: