multi grain/allocating

[kiwiNspain]

I’m modelling an order process that includes 3 different grains (Order Header, Order Item, Order Billing). All have logical keys to join to each other and are currently in 3 different staging tables

The lowest granularity is order billing, which is what I want to use for my fact table. If possible I only want to have 1 fact table.

For example:
Order header
headerID
supplier
order date

Order Item
itemID
headerID
item
item type
item total cost
etc etc

Order billing
billingID
itemID
billing description
billing cost


With the above tables my fact table would look something like this. With the header and item columns now allocated to the billing level grain.




My problem comes when I am designing the measures. They need to include Orders Count and Order Item Count. I can ignore the item total cost which now makes no sense, and sum the billing cost.

With only 1 fact table these measures(Order count, Item Count) would have to be distinct counts, which I really am trying to avoid. I could end up with 5 or more distinct counts. (In SSAS creating 5 or more measure groups)

Is it possible to allocate these measures. For the above fact example, the order count would allocate to 0.25 for each row. The order count would now be a sum rather than a distinct count.

Any ideas?

[hkandpal]

Hi,

any reason why you want to include Order count as it is at the order level ? The item count is fine as it is at the item level.


thanks

[kiwiNspain]

hi,

sorry, maybe my explanation wasnt very clear.

in the example with 4 lines, the grain is the billing detail. so the 4 lines represent 1 order with 1 item with 4 billing lines

a question that might be asked is... for supplier 'shop1' how many orders do they have. and to get the answer from this fact table you would have to make a distinct count on the headerID. supplier 'shop1' = 1 order

[hkandpal]

Hi,

one way you could di is to store the lowest grain the order details in a fact table and create a summary table/ materalized view (which you can polpulate hwhen the fact table is populate). where you can store the count.
You may need to consider things like how many rows are being loaded in the fact, what is the frequency of the load, the tool that is loading the data..


thanks

[kiwiNspain]

http://www.kimballgroup.com/2001/07/01/design-tip-25-designing-dimensional-models-for-parent-child-applications/

the above article is how I'm planning to design my model... but it still doesn’t help with my main problem.

If I have 3 different degenerate dimensions in my fact table, from the different parent-child relationships, how do I turn these into measures? the only way I can think of is a distinct count of the degenerate dimensions keys (and I don’t want to have distinct counts)

this must be a common question across parent-child models "how many invoices did I have for year...", "how many products did I sell last year"... well in the second case it would just be a count based on the article... but again maybe that's not the lowest granularity

[BoxesAndLines]

Unless you want to violate the grain of the fact table or create an aggregate fact table, distinct counts are your only solution.

[VHF]

Is it possible to allocate these measures. For the above fact example, the order count would allocate to 0.25 for each row. The order count would now be a sum rather than a distinct count.

Unfortunately, this approach doesn't always give correct results. For example, if you asked how many orders included a keyboard your result would only be a fraction of the correct answer.

[Sponsored content]