Hierarchy but not always the lowest grain is filled in

Hi,

I've sales data with a sales organization, channel and company information. Now the issue is that salesorganization (lowest grain) is not always filled. Sometimes the sales are booked on channel and sometimes on company level. I'm thinking of creating a compound key in the fact and determine every permutation of the Salesorganization, channel and company and in the dimension I'll determine this key too..

Good idea?

Let me know. I don't have a better solution at this moment;-) I could individually insert a dimension for company and one for salesorganization...etc but also not a neat solution imo...

But there is always a container for the rest of the sales?!.....

Greetz,

Hennie

I assume Sales Org > Channel > Company is a hierarchy? If so just create a dimension with records for every combination, including "Unknown", "Not Applicable" or whatever phrase you want, for any values that can be "missing" in the hierarchy.
I'm not sure what you mean by a compound key - this Dimension would just have a normal surrogate key