I am modeling a DW and have a design issue that I am looking for feedback on. I search the internet and either find simplistic answers or no reference so I am hoping someone here will have some experience.

I would like to leverage the star schema design and in 99% of the case this works well. However, we often have the concept of a "fact at multiple levels", not to be confused with "varying dimensionality".

Varying Dimensionality - Where a specific fact is dimensions by different dimensions.
Fact at multiple levels - Where a fact can be dimensioned by different levels of a dimension. Simple example is to use Time, some facts are dimensioned by Day and others by Week. This same concept can apply to pretty much any dimension.

I have resolved this issue in the past by pushing all members of a dimension to the lowest level (create a week member at the day level). However this can explode the number of dimensional members for a larger dimension and forces the BI layer to intelligently hide the children. And yes, you then have the scenario where the sum of days' might not equal the sum for a week.

Confusing yes! But imagine the scenario where you are asking the field to forecast product sales. All reps report their forecast at SKU but one guy sells nails, lots of different SKUs and just wants to report at Nails (Brand).

A simple answer is create another fact table. However, if you figure that this could happen with mulitple dimensions and with multiple levels, causing too many fact tables.

Clear as mud? I could create separate dimension tables but then I need to dynamically join when querying fact table. Will violate the star schema benefits.

any thoughts?