Microsoft® SQL Server® 2012 Bible

1314

Part IX: Business Intelligence

5. Locate and right-click the ProductSubCategory attribute of the DimProduct
   table. Select Add to Hierarchy ➪ Product Drilldown.


6. Locate and right-click on the EnglishProductName attribute of the DimProduct
   table. Select Add to Hierarchy ➪ Product Drilldown.
   Figure 58-14 shows the Product Drilldown hierarchy in the DimProduct table.

FIGURE 58-14

Product Drilldown hierarchy.

At this point you have successfully created a hierarchy that allows you to drill down from

product categories, then to subcategories, and fi nally down to products. Optionally, you can

hide the DimProductSubcategory and DimProductCatetogory tables by right-clicking

on each table and selecting Hide from Client Tools.

To interact with the Product Drilldown hierarchy you just created follow these steps:

1. Click the PivotTable option on the Offi ce Ribbon.


2. Select New Worksheet from the Create PivotTable pop-up window.


3. Expand FactResellerSales and drag SalesAmount to the Values section.


4. Expand DimProduct and drag the Product Drilldown hierarchy to the Row Labels
   section. Notice the + symbol next to Product Drilldown in the fi eld list. Click the
   the + symbol to expand the hierarchy members as shown in Figure 58-15.
   You have successfully created a hierarchy by utilizing the related() function to fl atten
   the three product category tables.

c58.indd 1314c58.indd 1314 7/31/2012 10:36:03 AM7/31/2012 10:36:03 AM

http://www.it-ebooks.info