Microsoft® SQL Server® 2012 Bible

1312

Part IX: Business Intelligence

This means that there are three tables, DimProduct, DimProductSubCategory, and

DimProductCategory, that are related to each other by foreign key constraints as shown in

the Diagram View in Figure 58-12

FIGURE 58-12

Product tables’ relationships in the Diagram View.

In order to create a hierarchy, you need to fl atten these three tables, in one of the follow-

ing ways:

1. Load a single Product table with all levels represented as columns.


2. Create a view that fl attens all three tables with all levels represented as columns.


3. Import the three tables into the BI Semantic Model as a single fl at table using a query.


4. Use the related() DAX function to look up the name values of the related tables
   and use them as hierarchy levels in the Product table.
   In this example, we will work with option 4 to exemplify the use of the related()DAX
   function. To create the Product Drilldown hierarchy follow these steps:


5. Follow steps 1 to 10 from the section “Creating BI Semantic Models Using
   PowerPivot.”


6. Click the DimProduct tab.

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

http://www.it-ebooks.info