You are not using a modern browser version. As a result, the website may not be displayed correctly. You can find more information here.

Performance Analysis with and without Transitive Attributes

Analytics Warehouse Nagarro ES

Table of Contents

 Performance Analysis with and without Transitive Attributes

PRoblem Scope

Because of the diversity and range of technical capabilities related to implementation, a number of challenges are encountered with data modeling in the SAP BW on HANA environment. The goal of the conceptual design is always to achieve the best possible performance. In my PoC I will show you how the use of transitive attributes in the data model affects performance in the SAP BW system.

Transitive Attributes

Transitivity is a relation and in mathematics is described as follows. An element A contains a transitive dependency to an element C when the element A is in relation to another element B that contains a dependency to element C.

A transitive attribute is an attribute of an object (e.g. a sales organization of a plant) which itself is also just one attribute of a superior object (for example the company code of the sales organization), but which you would like to directly access in reporting.

The advantage of transitive objects lies in the avoidance of redundant physical data storage as compared to direct navigation attributes. The navigation attributes are usually used to allow better navigation through particular attributes in reporting.

Fig. 1 – Transitive Attributes

Although there was a solution in the classic BW that could be used to make these 2nd level attributes directly available in BW reporting ( ), its performance in conventional info providers (standard-DSO, CUBE) was subpar and the modeling of transitive attributes was prone to error and time-consuming. It is now possible as of version BW 7.5 SP4 to design transitive attributes directly on the infoobject via configuration in Eclipse / HANA Studio. One project task was to analyze whether these transitive attributes can also be used in new objects with good performance.

Activating transitive attributes

To illustrate how transitive attributes are activated, we will create a composite provider where the plant 0PLANT is included as infoobject. The plant 0PLANT has the sales organization navigation attribute 0SALESORG, which in turn has a company code navigation attribute 0COMP_CODE.

So that you can activate the 0COMP_CODE as a transitive attribute on 0PLANT in our example, open the infoobject 0PLANT and go to the tab “Attribute”. Right-click on the attribute on which you want to select the transitive attribute. A context menu appears. Click on “Maintain Transitive Attribute” (see Fig. 2).

Fig. 2: Maintain Transitive Attribute

A list with all of the attributes of the selected (Nav) attribute LVl 1 is displayed. Select the desired infoobject as a transitive attribute – in this example the company code 0COMP_CODE – and then click on the button “Equal to” and “OK” (see Fig. 3).

Fig. 3: Select Transitive Attribute

The company code is now displayed in the attribute table. The value “true” in the “Transitive Attribute” column indicates that this is not a regular attribute, but a transitive attribute.

Fig. 4 – Activating transitive attributes

Activate the check box Navigation Attribute.

Adding a transitive attribute that has the same name as the already existing attribute will return an error message. In this case you must use the button “Other” (this assigns the attribute to another infoobject) or “New” (this creates a new infoobject as a reference to the original infoobject).

Transitive Attributes and Performance -PoC:

To determine the influence of activating a transitive attribute on performance, we created two queries, one with and one without a transitive attribute (here we added the actual transitive attributes directly as a NavAttribut to the infoobject ).

In the test installation we iteratively loaded up to approx. 10 mil. records to data in the data model. With each data load we then used Statistics Data for Query Runtime to calculate the execution time on the database, the so-called event ID 9000.

To map a comparison between Query 1 (without transitive attributes) and Query 2 (with transitive attributes), we generated a graph with an X-axis representing the number of data records and a Y-axis representing the execution time in milliseconds.

The entire execution time (Figure 3) designates the summation of the execution time of all actions (events in the Statistics Data for Query Runtime). The event ID 9000 (Fig. 4) measures only the pure execution time on the database and thus the time in the Data Manager is measured as soon as this is called from the OLAP.

Summary

Both analyses show approximately equal progressions over the time period. The number of data records showed no influence on the execution time for the two queries in comparison.

The results of our PoC showed that the use of transitive attributes has no negative influence on performance, and is even slightly faster. The reason for this in our view is that transitive attributes are now pushed down directly to HANA via Eclipse Modeling, and classic Nav attributes are still resolved through the application layer.

Fig. 5  PoC: Complete execution time of queries
Fig. 6 – PoC: Database execution time (Event ID 9000)

Contact us!

    I hereby consent to my personal data being collected, processed, and used for the purpose of processing my inquiry. I may revoke my consent anytime without stating my reasons for doing so. More information can be found in our privacy statement.

    Rufen Sie uns an
    +49 6173 3363 000

    Schreiben Sie uns
    info@nagarro-es.com

    Besuchen Sie uns
    Alle Standorte ansehen