In several measure groups I am using our historical customer dimension as an
intermediate dimension for 6 smaller referenced dims such as geography, club
level, etc. The customer dimension is over 5 million rows, 4 of the 6
referenced dims are parent child.

When looking at the select statement for one of the measure groups, I
noticed that the customer dimension - all 5 million rows, had to be read for
each of the 6 referenced dimensions. Since I have 6 other measure groups
using the same structure, that means the customer dimension is being queried
at least 36 times during the cube load. In this case, the intermediate
dimension is a named query, so that could be an issue also.

Is it good design to have an intermediate dimension connect to 6 different
referenced dims, or to move the keys out of the intermediate dimension to the
fact table so there won't be 6 reads, just 1 to get them all.

moving the keys into the fact table is the better way.
if you continue to use views, you'll control how to retrieve the keys, so
you'll reduce the number of joins
if you change your table himself the performance was better, but need more
disk for the database.

but control what is the real overhead if these joins, with a correct
indexation plan the queries can be returned faster then expected.

verify the performance of the entire process of each option.
Improving the cube loading step can result in a slower ETL process...



"bhorwatt" <bhorwatt (AT) discussions (DOT) microsoft.com> wrote