Suppose you have one measure and two dimensions. One dimension is Color (eg. red, green, yellow), the other dimension is Size (e.g. small, medium, large). Maybe the fact table looks like this

  PK
  Color
  Size
  Amount

The dimensions are not related to each other. There is no relationship like Category/Subcategory between them. However, you want to offer a hierarchical dimension. What should be done for creating a dimension with such a hierarchy?

My own solution has been as follows:

- create a new table (pk, color, size)
- fill it with all existing combinations of color and size
- in the fact table remove the two foreign keys
- replace them with the correct foreign key to the new table
- then in dimension design the new table with both columns can be used

Of course, creating the table can be avoided by performing all steps on the data view level. But is the general procedure correct? Is there an easier one?

Michael G. Schneider

I do not quite understand the "not practical" statement. The number of rows in the new table will not be larger than the number of rows in the fact table. Actually, if the hierarchy makes any sense, the number will be significantly smaller.

I did only create rows in the new table for combinations that exist in the fact table. If a combination doesn't exist in the fact table, it is not necessary.

Michael G. Schneider

Building the new table is a process which is done for the cube. Why should a customer want to build every possible combination?

For satisfying the relational model only those combinations have to be built that occur as foreign key combinations in the fact table. Building all possible combination creates many rows that are never hit by relation. When using Excel and working in the cube these combinations are also troublesome, as they only lead to empty cells. The user drills down the hierarchy, only to find out that no fact row to this combination exists.

Michael G. Schneider