Design a multidimensional business intelligence (BI) semantic model

Thought experiment answers

This section contains the solution to the thought experiment.

1. The design for the star schema includes two fact tables—Policy Sales and Policy Quotas—and four dimension tables: Date, Customer, InsuranceLine, and SalesTerritory. The table columns and relationships between the fact tables and dimension tables are shown in Figure 1-63.

   

   FIGURE 1-63 Star schema design

2. On the Dimension Structure page of the dimension designer for the sales territory dimension, drag the Year to the Hierarchies pane to create a new user-defined hierarchy, drag the Month attribute to the new hierarchy and drop it below Year, and then drag the Date attribute to the hierarchy and drop it below Month. Right-click the Hierarchy name and type a new name, such as Calendar. On the Attribute Relationships page of the dimension designer, drag the Month attribute and drop it on the Year attribute to arrange the attributes from left to right in the following sequence: Date, Month, and Year. You can set the RelationshipType property for each attribute relationship to Rigid because members do not change in this dimension.

3. The answer is C. AttributeHierarchyEnabled. This attribute property prevents SSAS from storing data for this attribute’s members and hides it from client applications for use in queries on rows, or columns, or as a filter.

   Answer A, AttributeHierarchyOrdered, is incorrect because this property only specifies whether the attribute members are ordered. Answer B, AttributeHierarchyVisible, is incorrect because, although it hides the attribute from the client application for slicing and dicing, it does not enable the attribute as a member property. Answer D, IsAggregatable, is incorrect because this property removes the All member from the attribute hierarchy.

4. The answer is D. The FormatString property adds a thousands separator, sets the number of decimal places, or adds symbols such as a currency symbol or percent sign, which makes the values easier to read in client applications.

   Answer A, DataType, is incorrect because the data type does not improve the legibility of measure values. Answer B, MeasureExpression, is incorrect because it affects the calculation of a measure, but does not change its appearance. Answer C, AggregateFunction, is also incorrect because it changes how SSAS returns a value for parent members, by summing or counting for example, but does not change the appearance of the measure.

5. For the Month attribute, bind the KeyColumn property to both the Year and Month columns in the source. Also, ensure the OrderBy property is set to Key.

6. Set Month as the granularity attribute. You then map this attribute to the Year and Month columns in the measure group in the Define Relationship dialog box.

7. The answer is B. Add profit as a calculated measure by defining an MDX expression like this:

   [Measures].[Profit] / [Measures].[Revenue]

8. Answer A is incorrect because profit percentage is nonadditive and must be calculated at query time, and therefore it cannot be stored in the fact table. Answer C is incorrect for a similar reason. A measure group requires a measure based on a fact table column, and profit percentage cannot be stored in the fact table. Answer D is incorrect because the MeasureExpression calculation is performed at the detail level and then aggregated, which produces an incorrect result. The profit percentage calculation must be based on the aggregated value for profit and divide that value by the aggregated value for revenue.

9. Set the IgnoreUnrelatedDimension on the measure group containing the quota measure to False.