Khoder Elzein's Blog: How to extract a BEx Query and a BW Hierarchy with SSIS / Xtract IS from SAP BW and build them in SSAS

In this blog I will use Xtract IS to extract a BEx query and a BW hierarchy from an SAP BW system and load them into SQL Server. Then I will show how to build a cube in SSAS upon the BEx query and the BW hierarchy.

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.

In SSIS create a BEx query extraction using the BW Cube / Query component

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

Now run your SSIS packages to load the BEx query

and the BW hierarchy into the SQL server database.

Create an SSAS project. Create a DataSource and a DataSource view. Import the two tables and set the logical primary keys. NodeID is the key of the hierarchy. Create a foreign relation between the correspondent column of the bex query and the column NodeName of the hierarchy.

Create two new dimensions based on the SQL tables. The BEx query dimension has two columns: 0D_CO_CODE and 20D_COUNTRY.

The hierarchy dimension has three columns: NodeID, NodeName and ParentNodeID.

For the column NodeID set the following properties: Usage = Key, AttributeHierarchyVisible = False and NameColumn = NodeName.

For the column ParentNodeID set the following properties: Usage = Parent, AttributeHierarchyVisible = False and NameColumn = NodeName. The property Usage = Parent defines a parent-child hierarchy, where ParentNodeID is the parent and NodeID is the child.