CHAPTER 5 Developing SSAS Cubes

Chapter Summary

• Every SSAS solution must include a data source view (DSV) that contains schema metadata to support the other objects within that solution. The solution might also include named calculations and named queries.
• The BIDS Cube Wizard and Dimension Wizard provide easy mechanisms for adding new cubes and dimensions to an SSAS solution.
• The BIDS Cube Designer provides tabs for reviewing and modifying various aspects of a cube. The two tabs you use to control the basic structure of a cube are the Cube Structure tab and the Dimension Usage tab.
• You use the BIDS Dimension Designer to modify a dimension and to add, modify, or delete specific attributes and multilevel hierarchies within the dimension.
• You can set various attribute properties to control the behavior of the attributes from an end user's perspective. Likewise, you can control the behavior of dimension attributes through various properties available within the Dimension Designer.
• A measure group is a container of measures. The most important property of a measure is AggregationFunction, which tells the server how to aggregate the measure. Additive measures aggregate across all dimensions joined to the measure group. Semiadditive measures aggregate across all dimensions except time. Non-additive measures require custom calculations that define their aggregation behavior.

Lesson 1: Creating Data Sources and Data Source Views

1 . What is the purpose of a data source view (DSv)?

A data source view captures schematic information related to the relational database that serves as a source for your SSAS solution. The DSv contains metadata describing the tables and views selected from the database, the primary key definitions, and the relationships that exist between the tables. The DSv lets you extend the data source schema by adding named calculations and named queries and by defining table relationships and logical primary keys that do not exist in the data source.

2 . Within a DSv, why are primary key and table relationship definitions important?

The various designers and wizards within an SSAS project use the primary keys and the table relationships to determine how dimension members are uniquely defined and how the dimension and fact tables are related.

3 . If you have multiple tables that contain information related to a single dimension, how would you simplify the representation of that information within your DSv?

you can combine the relevant columns from each of the tables into a single named query by using a SELECT statement that joins the tables together.

Lesson 2: Creating and Modifying SSAS Cubes

1 . Which cube creation method should you select in the Cube Wizard if you want to build the cube on top of an existing data source?

you need to select the use Existing Tables method when you build the cube from the bottom up.

2 . What do you need to do to make the cube available for browsing?

you need to deploy the cube definition to the server and process its objects to load it with data. BIDS combines these two tasks in one step when you deploy the database to the server.

3 . Can you modify the design of a dimension that has been included in a cube?

Although you can modify certain properties for a dimension to change its behavior within a cube, the physical design of a "cube dimension" is inherited from the dimension at the database level; you can find that dimension definition under the Dimensions folder in Solution Explorer.

Lesson 3: Creating and Modifying Dimensions

1 . While using the Dimension Wizard, you inadvertently select the wrong primary key column and fail to select all the attributes you need for the dimension. Do you have to delete the dimension and start over?

Although you can certainly delete the dimension and start over, it is likely that you can make all necessary corrections by using the Dimension Designer. Simply update the key attribute to reflect the correct KeyColumns property and add the additional attributes as needed.

2 . When creating a new dimension by using the Dimension Wizard, can you specify the cubes to which the new dimension will be added after it is created?

no, you cannot specify the cubes to which a new dimension will be added. The Dimension Wizard simply creates any new dimension as a database-level dimension. If you want to use the new dimension within one or more existing cubes, you must open each cube within the Cube Designer and add the dimension.

3 . your factInternetSales fact table references the DimDate table three times with orderDateKey, DueDateKey, and ShipDateKey foreign keys. you want the end users to browse the cube data by these dates. Do you need to create separate Time database dimensions?

no, you add the same time dimension multiple times to the cube as a role-playing dimension.

Lesson 4: Creating Measure Groups and Measures

1 . What is the difference between measures and a measure group?

A measure group is a container of measures. Typically, a measure group represents a fact table, and its measures represent the facts.

2 . What types of measures can you identify based on the way they aggregate?

Based on how it aggregates data, a measure can be additive, semiadditive, or nonadditive.

3 . How do you set the aggregation function of a measure?

you can set a measure's aggregation function by setting its AggregationFunction property to one of the SSAS standard aggregation functions.

Building an SSAS Solution as a Prototype

Case scenario

The business development department at Adventure Works has asked you to develop a solution to analyze Internet and reseller sales by using Excel 2007. Given the strong support for working with cubes in Excel, you decide to create an SSAS solution that provides the requested sales data, with various dimensions, hierarchies, and attributes available to support a broad range of analysis needs.

1. How can you quickly create a prototype of your solution to gain a better understanding of the data and the end-user requirements?
2. What SSAS features are you likely to take advantage of to improve the usability of the cube from an end-user perspective?

Answers

1. To build an initial prototype, you would create a new SSAS solution and add a data source and a data source view (DSV) on top of the database whose schema defines dimension and fact tables. You could then use the Cube Wizard to generate a cube with measure groups for Internet and reseller sales data, along with dimensions for business entities such as time, products, customers, geography, sales reasons, employees, and sales territories. After processing the new SSAS database, you could use the browsers within BIDS or Microsoft Office Excel to review the resulting design, possibly even sharing it with a few of your end users, to better understand the available data and the end user's analytical requirements.