Transpose internal table using GROUP BY function

Imagine you have a table with an account number (0ACCOUNT), currency type (0CURTYPE) and an amount (0AMOUNT). The account number and currency type are part of the key. For every account three different records are created, namely Currency Type 00 (Document Currency), 10 (Local Currency) and 20 (Group Currency). From reporting perspective you want to have the three separate records on one line.
Tranpose internal table SAP BW
The basic idea behind transposing is that you want to loop over an unique combination of characteristics and then loop over it’s members. As of SAP ABAP 7.4 you are able to do this very easily using the GROUP BY function. So let’s state you want to loop over the unique accounts within the internal table above? Then you should specify 0ACCOUNT as your GROUP BY clause.
LOOP AT GROUP BY statement

The above ABAP statement will add two records (100001 and 100002) to a new internal table. Very nice, but it does not cover our full requirement. As an additional option we can loop over it’s members and transpose them into a single line. This results in the complete code below.

Don’t forget, that because this is default SAP, it is HANA optimized, which implies that the GROUP BY statement is pushed down to the HANA database!

No data when exposing calculation view as OData service

In the last couple of months I did a lot of developments in SAP HANA and SAP UI5. One of the requirements was to consume a calculation view as an OData service. This OData service is then consumed by an UI5 dashboard. The actual exposing of a calculation view is quite easy.
Expose calculation view as OData service
However one of basics which I have forgotten was to setup the client of the calculation view properly. When you do not change this to the right setting the OData service will not give you any data.

The client of a calculation view is default set to “session client” but it should be “cross client”. Once you change this setting the OData service will deliver you the right data.
Setup client of calculation view

Multi-dimensional clustering in SAP NetWeaver Business Intelligence

Getting the best possible query performance out of a SAP Business Intelligence system is one of the main responsibilities of a BI consultant. Optimizing data models (e.g. remodeling and creating indexes) is one of the activities you could perform in order to achieve this. In some cases optimizing is not sufficient. Partioning data objects is sometimes forgotten, but might do the trick.

Partitioning data objects is a piece of cake from SAP BW 7.3 onwards (see attachment). However, when running an old version of SAP BW with a DB2 database, this could only be achieved by using multi-dimensional clustering (MDC). This feature, which is only applicable for DB2 databases, allows you to physically cluster data in a table along multiple dimensions. It is especially beneficial for query performance in business intelligence applications that use star and snowflake schemas and process large amounts of data with restrictions on several dimensions.
Multi-dimensional clustering option for data objects in SAP Business Warehouse
If you have tried almost everything to optimize your query performance then MDC could be your last option. In the end upgrading SAP BW to a higher version (which makes is possible to create semantic partitioned objects) and using an accelerator (like the BWA or HANA) will be more beneficial for the long term.

Role generation tool (PFCG upload ready)

As of September 8th I am proud to be part of a BI project team at ASML. As a team we are responsible for a greenfield BW on HANA implementation. One of the requirements for this project was to define a new authorization concept. After a thorough research a framework was proposed containing the objects which should be created in order to make data authorization possible. This meant that for a certain area 360 authorization objects and roles had to be created.

For the mass creation of authorization objects there is currently no tool available (at least not one that I amaware off), resulting in spending over more than 6 hours of clicking and saving in transaction RSECADMIN (!). However for the mass generation of roles I knew that there was a upload possibility available in transaction PFCG. With this knowledge in mind I downloaded a already existing role  analyzed the content of this downloaded .SAP file. Concluding that this .SAP file is just a simple text file which could be easily generated by Excel.
Download / upload roles from PFCG in SAP
So in order to create 360 roles I started developing an Excel Macro which generates a .SAP upload file based on the given roles names, descriptions and authorization objects. As a result the “PFCG upload – role generation tool” was born. Refer to the attached document to view the result. Hopefully this tool could save you alot of time and effort!
PFCG upload – role generation tool

Verify dimension sizes of InfoCubes

Maybe you are one of the lucky few who is currently working with BW on HANA and do not have to model InfoCubes anymore (from a performance perspective). But for those who still need to do this, ABAP program SAP_INFOCUBE_DESIGN could help you verify how well your InfoCubes are modelled in SAP Business Warehouse. This program collects data for every InfoCube in the BW system and counts the records available in the related dimension tables and fact table. The program then checks the ratio between the number of records in the dimension tables against the number of records in the fact table.
A ratio between 10% and 20% is considered as high cardinality and therefore indicated in red. In most cases you should avoid dimension tables larger than 10%. Of course this it all depends on how frequent the data is used in the mentioned dimension table. If possible use line item dimensions instead!
Note: The data which is used for this analysis comes from BW statistics, so be sure that if you want to analyze a specific InfoCube, statistics are created for it.