Click here to monitor SSC
  • Av rating:
  • Total votes: 83
  • Total comments: 3
Robert Sheldon

Implementing User-Defined Hierarchies in SQL Server Analysis Services

02 February 2010

To be able to drill into  multidimensional cube data at several levels, you must implement all of the hierarchies on the database dimensions. Then you'll create the attribute relationships necessary to optimize performance. Analysis Services hierarchies offer plenty of possibilities for displaying the data that your business requires. Rob Sheldon continues his series on SQL Server Analysis Services 2008.

In a recent article on SQL Server Analysis Services 2008, I explained how to implement a multidimensional database. The Analysis Services database I used as an example included a data source, data source view, multiple database dimensions, and a cube based on those dimensions. I then showed you how to deploy the database and browse the cube data in SQL Server Business Intelligence Development Studio (BIDS). However, the cube was missing important functionality—the ability to drill down into the data in order to view aggregated values at more granular levels. That’s because no hierarchies had been created in the database dimensions.

A hierarchy lets you view aggregated fact data at multiple levels. For example, the solution in my recent article contains the Territory dimension, which is based on the DimSalesTerritory table in the Sales data source view (shown in Figure 1).

Figure 1: Default diagram from the Sales data source view

The DimSalesTerritory table includes the SalesTerritoryRegion, SalesTerritoryCountry, and SalesTerritoryGroup columns. Although the columns are unrelated in the table, they form a natural hierarchy in which the sales groups contain countries and countries contain regions. If you were to define a hierarchy in the Territory database dimension based on this natural relationship, you could then drill down into the data first by sales group, then by country, and finally by region. And each time you drilled deeper, you would view aggregated data at a more granular level.

In this article, I explain how to implement hierarchies in your Analysis Services database dimensions. The examples in this article are based on the solution in the article mentioned above. For that solution, I created the following database components:

  1. A data source that points to the AdventureWorksDW2008 database on a local instance of SQL Server 2008.
  2. A data source view that includes the tables shown in Figure 1.
  3. Database dimensions for each dimension table in the data source view.
  4. A cube based on the database dimensions as well as on the two fact tables in the data source view.

Be sure to refer to the original article for more details about the solution. In addition, if you don’t know how to implement a basic cube in Analysis Services, read that article first and refer to SQL Server Books Online for additional information. Once you know how to implement an Analysis Services database, you’re ready to add hierarchies to your solution.

Creating a Named Query

Before you create any hierarchies in your Analysis Services database, you must ensure that the data supports those hierarchies. For instance, as I mentioned above, the data already supports a hierarchy based on the sales territory data (sales group, country, and region).

However, you might want to implement a hierarchy that the data does not support. For example, suppose you want to create a hierarchy based on the product categories and subcategories. Currently, the DimProduct table in the data source view (shown in Figure 1) includes only the ProductSubcategoryKey column, but no other category-related data. One way to get around this is to replace the DimProduct table with a named query that joins the table to the DimProductSubcategory table and then to the DimProductCateogory table. (You’re essentially flattening out the snowflake configuration of these tables.) A named query is a stored SELECT statement whose results are treated like a table in the data source view.

To replace the DimProduct table with a named query, open the Analysis Services database solution in BIDS, and then open the Sales data source view. In the default diagram, right-click the DimProduct table, point to Replace Table, and then click With New Named Query. This opens the Create Named Query dialog box (shown in Figure 2).

Figure 2: Default view in the Create Named Query dialog box

As Figure 2 shows, when you open the dialog box to replace a table, the dialog box includes details about the table in the three panes: Diagram, Grid, and SQL. You can create your named query by adding the DimProductSubcategory and DimProductCategory tables and then selecting the columns you want to include, or you can simply write the SELECT statement. For this example, I wrote the following statement:

SELECT

  p.ProductKey,

  p.EnglishProductName,

  CASE

    WHEN p.SpanishProductName = '' THEN p.EnglishProductName

    ELSE p.SpanishProductName

  END AS SpanishProductName,

  CASE

    WHEN p.FrenchProductName = '' THEN p.EnglishProductName

    ELSE p.FrenchProductName

  END AS FrenchProductName,

  p.ListPrice,

  p.StandardCost,

  s.EnglishProductSubcategoryName,

  s.SpanishProductSubcategoryName,

  s.FrenchProductSubcategoryName,

  c.EnglishProductCategoryName,

  c.SpanishProductCategoryName,

  c.FrenchProductCategoryName

FROM

  DimProduct p

  INNER JOIN DimProductSubcategory s

     ON p.ProductSubcategoryKey = s.ProductSubcategoryKey

  INNER JOIN DimProductCategory c

     ON s.ProductCategoryKey = c.ProductCategoryKey

WHERE

  p.ListPrice IS NOT NULL;

There are a few items in this statement you should note. First, I include only those columns I know I need in my dimensions. Normally, when you add a table to a data source view without modifying the SELECT statement, all columns are included, as was the case with the original solution. Because I don’t need all columns, I bring in only the ones I’m going to use now. However, you can modify a data source view at any time. Just be aware of how your modifications affect the dimensions and cubes that rely on the data source view.

Another component of the SELECT statement worth noting is that I use inner joins to join the tables. I did this because I want to include only those products that are associated with subcategories and categories. I also limit the results to those products that include a list price. That way, only products that are for sale are included in the cube data.

Finally, I use CASE statements in the SELECT statement to handle those products for which there are no French or Spanish names. Because the source columns for the data are not null, missing product names are treated as empty strings. Attribute relationships, which I describe later, do not like empty strings or nulls, so better to sidestep this problem now.

After you create the SELECT statement, you can test whether it’s returning the results you want by clicking the Run button. The results are displayed in the Result pane, shown in Figure 3. (The Diagram pane and Grid pane have been closed.) If you’re satisfied with the results, click OK to close the Create Named Query dialog box. Your data source view should now include the modified table.

Figure 3: Running a query in the Create Named Query dialog box

That’s all there is to creating a named query. You can now use it as you would any other table. In addition, now that you’ve included the category information, you can create a hierarchy that lets you drill down into the data.

Creating a User-Defined Hierarchy

You create hierarchies in the database dimensions that are used by the cubes. However, you must first ensure that any changes you made to the data source view are reflected in the dimensions. In this case, you’ll need to add the category-related attributes to the Product database dimension, as shown in Figure 4.

Figure 4: Adding attributes to the Product dimension

To add the category-related attributes to the Product dimension, open the dimension in BIDS and drag the following columns from the DimProduct table (in the Data Source View pane) to the list of attributes (in the Attributes pane):

  • EnglishProductSubcategoryName
  • SpanishProductSubcategoryName
  • FrenchProductSubcategoryName
  • EnglishProductCategoryName
  • SpanishProductCategoryName
  • FrenchProductCategoryName

After you add the attributes, you’re ready to create the hierarchies. You’ll be creating three hierarchies, one for each language. For example, the English hierarchy will include the English Product Category Name attribute (top level), English Product Subcategory Name attribute (middle level), and English Product Name attribute (bottom level).

To create a hierarchy, drag the attribute that represents the highest level of the hierarchy to the Hierarchy pane. For the first hierarchy, start with the English Product Category Name attribute. When you drag the attribute to the Hierarchies pane, the hierarchy is automatically created, as shown in Figure 5.

Figure 5: Adding a user-defined hierarchy to the Product dimension

Notice in Figure 5 that, after you add the first level of a hierarchy to create a new hierarchy, you’ll see a blue squiggly line beneath the name of the dimension in the Attributes pane. The line shows that there is a warning about the hierarchy. The warning states that you should avoid visible attribute hierarchies if those attributes are used in the user-defined hierarchies. In other words, if you’re going to include an attribute in a user-defined hierarchy, make it invisible in the attribute tree so users can’t see it when they browse cube data. That way, users will see only the attributes from the user-defined hierarchy. (Some prefer to keep the attributes visible in the tree.) If you decide to hide the attributes, which I’ve done for this solution, set the value of the AttributeHierarchyVisible property to False for each attribute in a user-defined hierarchy. The warning message will go away.

To add the next level of the hierarchy—English Product Subcategory Name—drag the attribute to the Hierarchies pane and drop it into the hierarchy box, where it says <new level>. Then do the same for the English Product Name attribute. After you’ve added the third level of the hierarchy, your Hierarchy pane should look similar to Figure 6.

Figure 6: Adding a user-defined hierarchy to the Product dimension

After you’ve created the hierarchy, you can modify its properties. For example, as you can see in Figure 6, I changed the value of the Name property to English Product. Notice, however, that the hierarchy includes a warning message. This one is telling you that attribute relationships have not been defined between levels of the hierarchies, so let’s look at how to do that.

Creating an Attribute Relationship

Attribute relationships improve the performance of Analysis Services when aggregating data. When an attribute relationship is defined between levels of a hierarchy, the engine can use the aggregations of one level to calculate the aggregations of another level, which is why you always receive a message that warns you to create those relationships.

To define an attribute relationship on the English Product hierarchy, select the Attribute Relationships tab for the Product dimension (shown in Figure 7). The tab shows a graphical representation of the default relationships as they apply to your user-defined hierarchies. In addition, the tab includes the Attributes pane, which lists all the dimension’s attributes, and the Attribute Relationships pane, which lists all the dimension’s default relationships—those that exist between the attribute key and each of the other attributes.

Figure 7: Default attribute relationships for the Product dimension

Notice in Figure 7 that a relationship exists between the Product Key attribute and each of the three attributes in the hierarchy. However, to improve performance, the relationships should be defined between the following pairs of attributes:

  • Product Key and English Product Name
  • English Product Name and English Product Subcategory Name
  • English Product Subcategory Name and English Product Category Name

As you can see, the relationships follow the steps of the hierarchy. You can easily create these relationships in the graphical portion of the Attribute Relationships tab. First, drag the English Product Name attribute to the English Product Subcategory Name attribute. (You always drag from the lower level of the hierarchy to the next level up.) Then drag the English Product Subcategory Name attribute to the English Product Category Name attribute. Your Attribute Relationships pane should now look similar to the one shown in Figure 8.

Figure 8: Defining an attribute relationship on the English Product hierarchy

Notice that the Attribute Relationships pane now reflects the new relationships. One other step you can take when defining attribute relationships is to set the relationship type. By default, a relationship is configured as flexible, which means Analysis Services does not retain aggregations when dimensions are updated. However, if you define the relationship as rigid, those aggregations are retained. Although this can improve performance, if a relationship is defined as rigid, Analysis Services will generate an error when processing the dimension, unless it is fully processed. When possible, you should define your attribute relationships as rigid, unless they change frequently. To configure a relationships as rigid, right-click the relationship in the graphical portion of the Attribute Relationships tab, and then select Rigid as the relationship type.

Now you’re ready to create your remaining hierarchies and attribute relationships. (Actually, you could have defined all your hierarchies first and then defined your attribute relationships.)

For this solution, I added hierarchies for the French and Spanish product categories, as shown in Figure 9. When you add the hierarchies, follow the steps you used to create the English Product hierarchy. Also, be sure to rename the hierarchies and, if desired, set the value of the AttributeHierarchyVisible property for each attribute to False.

Figure 9: Adding hierarchies for the French and Spanish products

After you’ve added the hierarchies, you can create attribute relationships. Again, follow the steps you followed previously when defining the attribute relationships for the English Product hierarchy. Once you’re completed creating the attribute relationships, your Attribute Relationships tab should look similar to the following:

Figure 10: Defining attribute relationships for the French and Spanish products

That’s all there is to creating your hierarchies and attribute relationships. Be sure to save your work and redeploy the database. You’ll then be ready to browse the cube.

Browsing the Cube Data

To browse the cube data in Analysis Services, open the AW_sales cube in BIDS, and then select the Browser tab (shown in Figure 11). Drag the Calendar Year attribute from the Order Date dimension to the Drop Row Fields Here section of the browser, and then drag the Sales Amount measure from the Fact Internet Sales measure group to the Drop Totals or Detail Fields Here section. Now expand the Product dimension in the Measure Group pane. Notice that the hierarchies are included in the dimension tree. Expand the English Product hierarchy, and drag the English Product Category Name attribute to the Drop Column Fields Here section in the browser.

Figure 11: Browsing the cube for products in English

As you can see in Figure 11, I’ve drilled down in the Bikes category and then the Mountain Bikes subcategory. An aggregated value is provided for each product for each year. In addition, if you were to scroll to the right, you would find the total for all mountain bikes for each year, and if you were to scroll all the way to the right, you would find totals for all bikes.

You can just as easily drill down into data for the other languages. For example, Figure 12 shows the aggregated values for products in the Bidon et porte-bidon subcategory of the Accessoire category. Notice that the product Water Bottle is listed in English. That’s because no French name is stored for this product.

Figure 12: Browsing the cube for products in French

As you can see, the ability to drill into data is a valuable feature when browsing cube data. But you need to implement hierarchies on the database dimensions in order to support that functionality. You can then create the attribute relationships necessary to optimize performance. However, you’re not limited to the hierarchies I’ve described here. For example, you can create a hierarchy based on sales territories, as described earlier in the article. And you can use multiple hierarchies when browsing data. The key is to know the data that you want to include in your cube and to understand the business needs that are driving your decisions for including that data. But as you can see, Analysis Services hierarchies offer plenty of possibilities for displaying that data.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 83 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Implementing User-Defined Hierarchies in SQL Server Analysis Services
Posted by: munishb (view profile)
Posted on: Tuesday, February 23, 2010 at 5:30 AM
Message: Hi,
After reading the above article, I have got fair idea on the topic, but still I have few questions:
1. How to choose the dimension for creating the hierarchy. I mean the hierarchy which you have created in the example could have been created using some other dimension other than product?
2. Is there any need for creating the attribute relationships for the attributes of the same table?
3. Does Granularity has any impact on the attribute relationships?

Thanks,
Munish

Subject: Prior article on SQL Server Analysis Service 2008
Posted by: bobalink (view profile)
Posted on: Monday, January 17, 2011 at 5:10 PM
Message: The link to your previous article is broken.

Thanks,
Bob Dickey

Subject: SSAS Hierarchy
Posted by: samikane (view profile)
Posted on: Friday, March 18, 2011 at 8:27 AM
Message: Here are this and some other articles on SSAS Hierarchy:
<a href="http://ssas-wiki.com/w/Articles#Hierarchy">
http://ssas-wiki.com/w/Articles#Hierarchy</a>

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.