MSAS: Working with Parent-Child Dimensions

Parent child dimensions when viewed from within a cube reveal some interesting features. We will add an private employee parent child dimension to the sales cube and study the features thereof.

In the cube Editor right click the Dimensions folder and click New Dimension to launch the Dimension wizard

Select the parent child option and click Next.

Select the Employee dimension table and click Next.

Select the Employee_ID in the member key list, Supervisor_ID in the Parent Key List, and Full_Name in the Member Name list. Then click Next.

Click Next to skip the Advanced options screen. Type Employee as the dimension name, clear the check box that allows you to share the dimension and click Finish.

Click the Data tab to see the Data Pane and drag the employee dimension down to the row area, replacing the existing dimension. The cube will display the sample data because it hasn’t been processed and the dimension members will be visible.

Expand the members to see the entire employee hierarchy.

Allow data for non-leaf members

In a parent child dimension there are values in the fact table both at the leaf level and at the parent level. Before processing the cube it is important to change the property of the dimension.

In the cube tree, select the Employee dimension and switch to Advanced tab of the properties window. A drop down list for the Members with the Data Property is displayed. There are three values: Leaf Members only(default); Non-Leaf Data Hidden and Non-Leaf Visible. Select the third option so that a comparison can be made on the sales figures.

The data pane refreshes to include new members for the supervisor data. The new member names are in parentheses. Since the total rows for the supervisor clearly include the word “Total”, the user may want to simply display the employee name for the new data fields. The template for displaying the new member name can be changed.

Clear the Data Member caption Template property and press enter.

This tutorial is part of a Analysis Services Training tutorial series. Read it from the beginning and learn yourself.

Analysis Services Training

Comments