multi grain/allocating

View previous topic View next topic Go down

multi grain/allocating

Post  kiwiNspain on Fri Apr 12, 2013 5:37 am

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?

kiwiNspain

Posts : 6
Join date : 2013-04-12

View user profile

Back to top Go down

Re:multi grain/allocating

Post  hkandpal on Fri Apr 12, 2013 7:09 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: multi grain/allocating

Post  kiwiNspain on Fri Apr 12, 2013 7:31 am

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

kiwiNspain

Posts : 6
Join date : 2013-04-12

View user profile

Back to top Go down

Re: multi grain/allocating

Post  hkandpal on Sat Apr 13, 2013 4:48 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: multi grain/allocating

Post  kiwiNspain on Mon Apr 15, 2013 11:39 am


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

kiwiNspain

Posts : 6
Join date : 2013-04-12

View user profile

Back to top Go down

Re: multi grain/allocating

Post  BoxesAndLines on Tue Apr 16, 2013 9:45 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: multi grain/allocating

Post  VHF on Wed Apr 17, 2013 10:08 am

kiwiNspain wrote: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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: multi grain/allocating

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum