Indexes

Each index identifies an item that can be placed as a row or column in the cube table. Each index must have a unique name to distinguish itself from the others, but the name does not have to match any database table identifier - indeed you can create several indexes that read from the same dimension table, perhaps one grouping dates by quarter, and another grouping those same dates by day of the week.

Index Dialog

Name

Each index must have a unique name - the Ok button will be disabled if the name is empty, or not unique.

Joins

An index joins the fact table to a dimension table. In the star schema, there is a single join. In the snowflake schema there may be a series of joins through intermediate dimensions to get to the target dimension.

Join Dialog

Starting from the Fact Table we need to traverse to our target dimension. We do this by matching foreign keys to their corresponding primary keys. The first Join in an Index will be joining a foreign key in the Fact table to a specific Dimension table. For example the Fact date_id column could map to the primary key of the Date dimension. In the Joins list this would be shown as:
date_id -> Date
With a star schema it ends here. However, with a snowflake schema we may need to add more joins. For example the Date table may have a dow_id which is the foreign key for the DayOfWeek table. The Joins list would be shown as:
date_id -> Date
dow_id -> DayOfWeek
We have now linked the Facts to the DayOfWeek indirectly by going through Date.

Index Field

When the Join or Joins have been traversed, we end up at a target dimension. We need to choose one particular column of this target dimension as the indexed property. For DayOfWeek it might be the name column, for example.

Group On

Finally we need to choose how the Index Field should be grouped. Most of the time we want "Each Value", which keeps Monday, Tuesday etc. as distinct groups. When working with dates we often want to group based on other criteria, for example 2008-01-01 and 2008-02-01 might be grouped together as Q1.

Grouping is often related to sorting, because items are sorted as part of the grouping procedure. This is useful in most cases - we probably want Cities and Products to be listed in alphabetical order. But it isn't usually helpful to see Friday before Monday, or April before January. In this case, you should choose the "Original Order" option and ensure that your values in the Dimension table are already in the correct sequence, eg. Monday, Tuesday etc.