Introduction
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, Category and Item. The entities of each level are called dimension members. Here are some examples of the Category level members: Beverages and Produce. Examples of the leaf level members could be Chai and Pears.
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.
Category and 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...
Listing 1
...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 CategoryID and ProductID. Here is the T-SQL code that does it:
Now open 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).
Figure 1
To setup the parent-child relationship, click on the Relationship button shown in Figure 2 below:
Figure 2
Then click on the Add button on the Foreign Key Relationships dialog form (see Figure 3):
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:
Figure 4
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).
Figure 5
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 ProductID and CategoryID values into the new ProductID and 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
Select 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).
Figure 6
Save the solution and build it to clear the warning message.
Step 4: Create an ADO.NET Entity Data Model
Select 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.
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.
Figure 8
Save and build the project.
Step 5: Create Domain Service
Select 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.
Figure 9
On the Add New Domain Service Class, make selections as shown in the following Figure 10 and click OK.
Figure 10
Save the project and build it.
Step 6: Populate the Product Dimension TreeView Control
Drag the 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:
History
- 9th November, 2010: Initial post
