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.
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).
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).
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.
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.
