How to load a BW Hierarchy into PowerPivot using Xtract PPV

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.