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.

Define the cube with the two measures Cost and NetValue based on the BEx query table. Add the two created dimensions to the cube dimensions.

Define the relationship between the measures and cube dimensions.

Now process the cube. Go to the browser tab, drop some fields and check the result.

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.