Business Intelligence Tutorial

Lesson 21. Joining and editing dimension tables

The star schema represents the relationships between the fact table and the other dimensions in the model. In this lesson, you will see how the structure of the star schema is defined by joins between the dimension tables and the fact table. You will learn how to hide columns in the dimension tables so that the columns do not appear as members of the dimensions in the model.

The left side of the Fact Table Joins page lists all the dimensions in the model. The right side shows which columns are joined between the dimension tables and the fact table, if a join exists. In the Dimension List field, an X next to a dimension means the dimension is joined with the fact table. Notice that all the dimensions are joined to the fact table.

1. In this exercise you will show which column joins the fact table to the Time dimension. In the Dimension List field, select the Time dimension.
   
   

   Notice that the TIME_ID column joins the fact table with the Time dimension.

2. Click Next and the Dimensional Table Joins page is displayed. You can use this page to create joins between the primary tables for the dimensions and any additional dimension tables you added on the Select Relational Tables page. In this tutorial, you did not add any additional relational tables, so none are listed.
3. Click Next and the Edit Tables page is displayed.

Editing dimension tables

In this exercise, you will hide a column in the Product dimension so that it does not appear in the model:

1. In the Dimension List field, select the Product dimension.
2. Look for CAFFEINATED in the Column Detail field. Under the Hidden heading next to CAFFEINATED, click the check box. The window looks like this:
   
   

   You could also give the columns more descriptive names without having to change the column names in the source data. These names are called Essbase generation names and they identify the columns in the final OLAP application. If you do not assign Essbase generation names, they default to the columns names. Do not assign generation names at this time.

3. Click Next and the Define Hierarchies page is displayed.

What you just did

In this lesson, you saw how the structure of the star schema is defined by joins between the dimension tables and the fact table. You also learned how to hide columns in the dimension tables so that the columns do not appear as members of the dimensions in the model.