In this blog I will use Xtract PPV to extract a BW hierarchy and a BEx query from an SAP BW system and load them into PowerPivot. Then I will show how to build the relation between the Hierarchy and the BEx query in PowerPivot.
I have created a simple BEx query based on the Demo InfoCube 0D_DECU, which has two dimensions (characteristics): company code and country and two measures (key figures). The follwing screenshot shows the output of the BEx query in the SAP client.

In this sample I will extract a sample hierarchy of the dimension country.

Open Xtract PPV and create the BEx query extraction using the BW Cube/Query component

and create the BW hierarchy extraction using the BW/BI Hierarchy component.

Hierarchies are defined as parent-child hierarchies in SAP BW. To process them in PowerPivot we need a naturalized representation. Click on Extraction Settings, set Representation to Natural and Level Count to 3 since the hierarchy has only 3 levels (top level -> continent -> country).

Now open PowerPivot, click on From Data Feeds, set the Data Feed Url and click on Next.

Select the created extractions from the Xtract PPV repository

and click on Finish. In the next screen PowerPivot imports the SAP data. PowerPivot requests the BEx query and the BW hierarchy from the Xtract PPV server which delivers the data as a data feed.

After the data are imported click on Close. In PowerPivot you will have now two tables: the BEx query table

and the hierarchy table.

Now create a foreign key relationship between the BEx query column Company_code_Country__Key_ and the hierarchy column NodeName.

Now you can use the hierarchy in your pivot tables.

Written by Khoder Elzein

Khoder is responsible for presales and always has an attentive ear for our customers, prospectives and partners. When travelling in Germany or abroad, he provides customer support at PoCs, workshops and on training courses. He also looks after the further development of our software solutions. Khoder has been working in IT since the turn of the millennium; he has been a member of team Theobald since 2009. When it comes to private interests, family, nature and reading feature at the top of his list – along with innovative fusion cuisine, as you may infer from his favourite dish of Swabian cheese noodles with tabouleh.