MS SSAS 2008 Unleashed - Chapter 9. Multidim - Models and BI Development Studio

Designing a Cube

A cube is the fundamental object of the multidimensional model. It is a major object, one that defines the data that you can access. We have just worked on defining dimensions because dimensions play a major role in defining a cube. You do not necessarily need to design your dimensions before you define your cube; you can start with the cube. For our purposes, however, we are going to design a cube with the dimensions we already have. For detailed information about cubes, see Chapter 6.

Creating a Cube

You can use the Cube Wizard to design a simple cube, including dimensions, if you have not already created them. As handy as it is to create an entire cube using just a single wizard, it is something we recommend only for simple models. If you are using advanced dimensions (for example, a dimension with more than one table), we recommend that you use the Dimension Wizard to create your dimensions before you create your cube.

We are going to show you how to create a cube, and will assume that you have already created the dimensions you need for your cube.

When the Cube Wizard appears, you can—as usual—speed on through the Welcome page. Then, you get down to the business of creating your cube:

Modifying a Cube

The MySampleCube you have created has a structure similar to that of the Warehouse and Sales cube in our sample FoodMart 2008 database. We will use the Cube Designer in BI Dev Studio to examine the structure of MySampleCube.

To open MySampleCube in BI Dev Studio, right-click the file in the Solution Explorer and select View Designer from the resulting menu.

On the Cube Structure tab of the Cube Designer, you will see a diagram of your cube, as shown in Figure 9.12.

Working with Measures and Measure Groups

All of your measure groups and measures are listed in the Measures pane. (See Chapter 7, “Measures and Multidimensional Analysis,” for more information.)

You can click a measure group or a measure to see its properties in the Properties pane. You can drag and drop columns from Data Source View pane, which shows all the tables that your cube is based on.

Right-click anywhere in the Data Source View pane. On the resulting menu, select Show Tables. The tables that you see listed in the Show Tables dialog box are the tables from your DSV that are not included in the diagram. You can select one or more of these tables to include them in the diagram.

Right-click anywhere in the Measures pane (see Figure 9.12). From the resulting menu, you can do the following:

Working with Linked Objects

You use linked objects to reuse an object (dimension or measure group) that resides in a different database, perhaps on a different server. You can link only dimensions from different databases. The linked object points to the object you want to reuse.

For more information about linked objects, see Chapter 25, “Building Scalable Analysis Services Applications.”

When the Linked Object Wizard appears, you can, as usual, speed on through the Welcome page. Then, you get down to the business of creating your linked object:

Defining Cube Dimension Usage

The role of a dimension in a cube is defined by its relationship to a measure group. We are going to move now to the Dimension Usage tab of the cube editor to review those relationships in the Warehouse and Sales cube from the FoodMart 2008 sample database.

The Dimension Usage tab will look like the one depicted in Figure 9.14. It displays a grid that contains a column for each measure group and a row for each dimension. The intersections of the rows and columns show the names of the granularity attributes. For example, the intersection of Product dimension and Warehouse measure group shows that the granularity attribute is Product.

Click the Product granularity attribute, and the Define Relationship dialog box appears, as shown in Figure 9.15.

In the Define Relationship dialog box, you can see how a dimension is related to a measure group. The dialog box displays the granularity attribute, the dimension table, and measure group table.

Under Relationship, you will find two columns: Dimension Columns and Measure Group Columns. Under Dimension Columns, you will see the name of the key column of the Product attribute: product_id. Under Measure Group Columns, you will see the name of the column in the inventory_fact_1997 table (the measure group table): product_id.

Our Product granularity attribute is based on a single key column. If a granularity attribute is based on more than one key column, you must map every dimension key column to a measure group column.

In the Select Relationship type box, you will find a drop-down list of relationship types. (For more information about relationship types, see Chapter 8, “Advanced Modeling.”) Depending on your selection, the Define Relationship dialog box looks different. Earlier we described the Define Relationship dialog box for the relationship type Regular. In the following list, we briefly define some of the other types of relationships:

Building a Cube Perspective

A cube perspective is similar in concept to a view in relational databases. There is a difference, however: A perspective in Analysis Services 2008 does not make it possible to specify security for the perspective—all users that have access to the cube can see all the perspectives in the cube. In essence, a cube perspective is another view of the cube that can be presented to the end user.

Suppose that you want to build a complex cube that involves lots of dimensions and that every dimension has a lot of attributes and hierarchies. And, the measure groups have lots of measures. Such a cube would be hard for a user to navigate. To make the cube easier to navigate and browse, you can create a perspective in your cube that shows customers only the information they are interested in.

To create a cube perspective, we will use the Perspectives tab in the Cube Designer. Right-click anywhere in the Perspectives tab and select New Perspective from the resulting menu.

The essence of designing a perspective is to select the measure groups and measures that will be included in the perspective. You also select the hierarchies and attributes that will be visible to the user. You can do all this on the Perspectives tab, shown in Figure 9.16.

In the Cube Objects column, you can see the measure groups (those are highlighted), measures, dimensions, hierarchies, and attributes available for your perspective. In the Perspective Name column, just click the ones you want to include.

After you have created your perspective, name it in the Perspective Name column. Our sample perspective is named Product Sales. To the user, it will look just like another cube, very much the same way the regular cubes in Analysis Services 2000 were visible to the end user.

Defining Cube Translations

Earlier in this chapter, we have described how to define translations for dimensions in your database. Now we are going to turn our attention to defining translations for your cube.

You define object captions in different languages for your cube objects. To do this, you create a translation for each language. Figure 9.17 shows the Translations tab in the Cube Designer.

In Figure 9.17, you can see a Russian translation defined for the Warehouse and Sales cube. When you want to create a new translation, you will use the Translations tab:

If you have not specified a caption for a certain object in this new language, the server uses the default object name. Go back and look at Figure 9.17, and you will see that we did not create a translation for the Product Sales perspective. There is nothing in the cell in the Russian column, but the server will supply one.