Click here to Skip to main content
15,860,972 members
Articles / Database Development / SQL Server

Using Silverlight TreeView to Display BI Dimension

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
9 Nov 2010CPOL5 min read 24.6K   6   2
It explains how to display business intelligence dimension table using Silverlight TreeView

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.png

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:

listing_2.png

<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">

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).

Figure1.png

Figure 1

To setup the parent-child relationship, click on the Relationship button shown in Figure 2 below:

Figure2.png

Figure 2

Then click on the Add button on the Foreign Key Relationships dialog form (see Figure 3):

Figure3.png

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:

Figure4.png

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).

Figure5.png

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

Figure6.png

Figure7.png

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.

Figure8.png

Select the Settings Tab and click on the link to create the project’s settings file.

Figure9.png

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).

Figure10.png

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.

Figure11.png

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:

Figure12.png

Figure13.png

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.

Figure14.png

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.

Figure15.png

Figure 9

On the Add New Domain Service Class, make selections as shown in the following Figure 10 and click OK.

Figure16.png

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:

Figure17.png

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:

Figure18.png

Now open the MainPage.xaml.cs, add reference to the NWAnalytics.Web project and the initialization code that populates the TreeView as shown below:

Figure19.png

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:

Figure20.png

History

  • 9th November, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Australia Australia
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.

Comments and Discussions

 
GeneralMy vote of 5 Pin
venugopalm11-Nov-10 18:30
venugopalm11-Nov-10 18:30 
GeneralMy vote of 5 Pin
Mamta D9-Nov-10 16:46
Mamta D9-Nov-10 16:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.