CHAPTER 6 Extending SSAS Cubes

Chapter Summary

• The cube space is defined by attributes. Sometimes there are logical relationships among attributes within the same dimension. You should understand and explicitly define such relationships to optimize the UDM.
• In addition to attribute hierarchies, you can define user hierarchies that provide useful navigation paths in the UDM. A dimension can have several user hierarchies.
• You can browse data in a measure group by a dimension only if the dimension is joined to the measure group. You must review the Dimension Usage tab and correct the dimension relationships if needed. In addition to regular relationships, SSAS supports other relationship types to support more complex schemas.
• Key performance indicators (KPIs) are quantifiable measures that organizations can use to track business performance. A KPI has four main properties: Value, Goal, Status, and Target.

Lesson 1: Defining User Hierarchies and Dimension Relationships

1. Why should you spend time defining appropriate attribute relationships?

Proper attribute relationships optimize storage and improve query performance because the server might be able to produce the totals from the related attribute totals instead of scanning the fact table.

2. When creating a dimension, can you create different hierarchies to represent every possible combination of attributes and to maximize the options available to end users for using the hierarchies to explore cube data?

Although technically you can create different hierarchies to represent every combination of attributes, a large number of hierarchies within a dimension design will likely offer too many options and confuse end users. Generally, users can create their own hierarchies by simply nesting (cross-joining) different attributes onto the rows or columns of a given query, although this capability is somewhat dependent on the applications they are using. So having attributes available only for the most commonly requested or needed hierarchies is probably your best design strategy.

3. Can you create hierarchies directly from columns within a dimension's table?

no, you cannot create hierarchies directly from columns within a dimension's table. Hierarchies can be created only based on attributes that have been added to the dimension's design.

4. Can a dimension be related to a measure group if the underlying dimension table is not related to the appropriate fact table in a primary key–to–foreign key relationship?

Although the dimension cannot be related to the measure group in a Regular relationship, you might be able to create a Referenced or Many-To-Many relationship if an intermediate dimension table or intermediate fact table related to the dimension and measure group in question is  available. This capability within SSAS provides an elegant solution to various database modeling requirements.

5. Must every dimension you add to a cube be related to every measure group within that cube?

no, you do not have to relate every dimension you add to a cube to every measure group in the cube. In fact, you can create a cube that  includes multiple measure groups whose source fact tables are related to different sets of dimensions. This lets end users browse the data in a way that makes sense from a business perspective rather than forcing them to analyze data in a way that is constrained by the underlying database design.

Lesson 2: Creating KPIs, Actions, Translations, and Perspectives

1. What types of actions can you identify?

Regular, drillthrough, and reporting actions are the three types of actions available.

2. How you can localize dimension member's captions?

you can localize dimension member's captions by selecting a translation column that stores the translated captions.

3. Can you use perspectives to enforce security?

no, you can use perspectives to make the cube easier to browse but not as a security mechanism.

4. What is a KPI?

A KPI, or key performance indicator, is a quantifiable measure used to track business performance.

5. What are the main properties of a KPI in SSAS?

The main properties of an SSAS KPI are Value, Goal, Status, and Trend.

6. What will the KPI Status expression return to indicate underperformance?

The KPI Status expression will return –1 to indicate underperformance.

Lesson 3: Creating Calculations and Queries by Using MDX

1. What are the first two axes of an MDX query?

Columns and Rows are the first two axes of an MDX query.

2. What is the difference between a calculated member and a regular measure in terms of storage?

The values of a regular measure are stored on the disk, and the values of a calculated member are calculated at run time.

3. What is a named set?

A named set is a set consisting of dimension members.

Extending SSAS Cubes

Case scenario

As it stands, the Adventure Works cube has data only for Internet sales. However, the business requirements state that the reporting solution must support consolidated reports that show both Internet and reseller sales. Adventure Works is also in the process of developing a Webbased dashboard solution, which needs to display vital business metrics in the form of KPIs.

1. What do you need to change in the dimensional model to accommodate the reseller sales?
2. How could you implement the Adventure Works KPIs?

1. In general, you can accommodate reseller sales by creating a new Reseller cube or by adding a Reseller measure group to the existing cube. If you need to handle large data volumes, you might find that a new cube will give you better performance. On the downside, you will not be able to author consolidated reports that draw data from separate cubes. With smaller cubes, consider adding a new measure group to the existing cube. Incorporate a performance- testing plan early in the design cycle to gauge the performance of the single-cube approach.