In business intelligence (BI) dimensions provide a natural way to browse business measures, such as sales volume, profit, etc. For example, information consumers might want to view sales volume broken down by Product Dimension Hierarchy and have an ability to drill from its top level, let’s say,
Category to the bottom or leaf level that might be called Item. In our case, drilling is possible due to the structure of the Product Dimension Hierarchy that consists of two levels,
Item. The entities of each level are called dimension members. Here are some examples of the
Category level members:
Produce. Examples of the leaf level members could be
In BI applications, dimensional hierarchies are often displayed using
TreeView control that offers a convenient way to navigate the hierarchies and filter the data displayed by other controls.
In this post, I’ll show you how to:
- Create a view out of a normalized dimensional hierarchy (snowflake schema) represented by two tables, i.e.
Product tables, and
- Populate a Silverlight TreeView control from a dimension table.
If you would like to follow me, you need to download the Northwind database from here.
Step 1: Building the TreeView Source View
Open the SQL Server Management Studio and create the following view...
...and save it as
dim_product_vw, for example.
In order to populate the
TreeView and take advantage of using the
TreeView HierarchicalDataTemplate we have to put that data into a table, create a primary index and parent-child relationship between
ProductID. Here is the T-SQL code that does it:
<shape id="_x0000_i1045" style="VISIBILITY: visible; WIDTH: 220.2pt; HEIGHT: 27pt" type="#_x0000_t75"><imagedata src="file:///C:\Users\Sergei\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png">
dim_product table in the design view, select
ProductID field and make it a primary key by clicking on the Set Primary Key button on the Table Designer toolbar (see Figure 2 below).
To setup the parent-child relationship, click on the Relationship button shown in Figure 2 below:
Then click on the Add button on the Foreign Key Relationships dialog form (see Figure 3):
Next, click on the highlighted button (labelled with horizontal ellipsis) which is located at the right hand side of the Tables and Columns Specification property cell (Figure 3).
Select the Primary and Foreign keys to establish the parent-child relationship as shown in Figure 4 below:
Close both forms to save the results.
Now if you open
dim_product table, you should see the following result (Figure 5 displays only the first 14 records).
The T-SQL view (Listing 1, 2) converts the snowflake schema of the
Product dimension represented by the two tables with one-to-many relationship into a table with a parent-child relationship, where the
CategoryID is a parent ID and
ProductID is a child ID.
Note: For simplicity, I hardcoded the conversion of the original
CategoryID values into the new
CategoryID (logic of this conversion is self-explanatory, I hopeJ) and added a root node, ‘All Products’, to the hierarchy.
Step 2: Create a new RIA Services Enabled Silverlight Project in Visual Studio 2010
The description of this step and the pictures above speak for themselves.
Step 3: Create a Connection to the Northwind Database and Create ADO.NET Entity Model
NWAnalytics.Web project and press combination of ALT+ENTER keys.
Select the Settings Tab and click on the link to create the project’s settings file.
Select (Connection string) as a type of your setting’s value from the drop-down box as shown above and, if you like, change the name of the setting to make it more descriptive. Click on the button with horizontal ellipsis (it is not visible until you click inside the
Value cell) to setup the connecting string; select your server instance and the database. Test the connection and click the OK button (see Figure 6 that makes things clear).
Save the solution and build it to clear the warning message.
Step 4: Create an ADO.NET Entity Data Model
NWAnalytics.Web project and press combination of CTRL+SHIFT+A keys to Add New Item to the project. From the Installed Templates, select Visual C#\Data\ADO.NET Entity Data model template as shown in Figure 7.
On the Choose Model Content (1st step of the Wizard), screen select Generate from database, click the Next button; the rest of the answers to the Wizard questions are shown in the following screenshots:
Rename the Navigation Properties of the model to make them more descriptive as well as to indicate the relationship between the entities as show in Figure 8.
Save and build the project.
Step 5: Create Domain Service
NWAnalytics.Web project and press combination of CTRL+SHIFT+A keys to Add New Item to the project. From the Installed Templates, select Visual C#\Web\Domain Service Class template as shown in Figure 9.
On the Add New Domain Service Class, make selections as shown in the following Figure 10 and click OK.
Save the project and build it.
Step 6: Populate the Product Dimension TreeView Control
TreeView control from the Visual Studio Toolbox, drop it on to the MainPage.xaml and name it
tvProduct. Here is the corresponding generated XAML code:
Now we need to add a Hierarchical Data Template to the
TreeView control and bind it to the Domain Services Products proxy class. The final XAML code should look similar to the following snippet:
Now open the MainPage.xaml.cs, add reference to the
NWAnalytics.Web project and the initialization code that populates the
TreeView as shown below:
Press F5 to Start Debugging. If you have not made any typos, you should see the
TreeView populated with the
Product dimension members as shown below:
- 9th November, 2010: Initial post
Sergei has over 20 years of experience in various roles that span from a database application developer to a company’s chief technical officer, who last 10 years mainly involved in development and implementation of business intelligence solutions and data mining applications. From 2007 he works as a senior business intelligence solution architect / pre-sales engineer for Information Builders Pty Ltd in Sydney, NSW.