Reusing dimesion keys ?

View previous topic View next topic Go down

Reusing dimesion keys ?

Post  VTK on Mon Aug 12, 2013 12:20 pm

I would like some views on what I am trying to do like if it is a common practice or is it a NO..NO...

I have couple of fact table(say life cycle and a transaction) with same grain (say policy). Lot of the dimensions are common to both fact tables. We are done with lifecycle and planning to build transaction fact. Can I reuse the dimension keys from the lifecycle fact for transaction fact ? I was thinking of taking a Policy and through Policy dimension go to the lifecycle fact and get some dimensional keys. Is this good practice or do we need to recode them everytime? I think ideal solution would have been to come up with reusable codes to derive these keys but it's kind of little late for that. so exploring other options.

Same question goes to facts/measures in different fact tables. First, is it OK to have the same measure replicated in different fact tables to make it easy for users ? If so, then can we just source it from one fact table to other ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  ngalemmo on Mon Aug 12, 2013 1:14 pm

The easiest thing to do is simply put the dimension keys you need on each fact.  You do not join to another fact just to get dimension keys.

When loading a fact you could copy keys from another fact but you never do this when querying.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Reusing dimesion keys ?

Post  VTK on Mon Aug 12, 2013 2:03 pm

Thanks for the quick reply. I am planning to put the keys in my fact tables but my question is more towards on how to populate them.
Can I just lookup tp the other fact table and get it or should I do all the logic again ? Reason is some of them are real complex logic and did not want to repeat them again.

Is this common practice ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  BoxesAndLines on Mon Aug 12, 2013 2:31 pm

Yes. You don't want to process data multiple times.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  VTK on Mon Aug 12, 2013 6:55 pm

Thanks for the reply. So, I am happy to know that I am not crazy and this is a common practice to use dimension fields from one fact table to other. Can we do the samething for measures also ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  BoxesAndLines on Mon Aug 12, 2013 7:12 pm

You really don't want the same measures in different fact tables. That's what drill across is for.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  VTK on Tue Aug 13, 2013 11:32 am

I am just wondering why drill across is allowed for measures but not for dimensions...Can't I just join the fact tables(lifecycle and transactional) using a natural key and create a view on top of two fact tables ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  LAndrews on Tue Aug 13, 2013 4:35 pm

Consider the following situation

Fact # 1, Grain = Policy, Rowcount = 1,000,000
Fact # 2, Grain = Transaction, Rowcount = 10,000,000 (avg 10 per poilicy)

Dimension = State, Rowcount = 50

If you add the dimenion to fact #1, and join the facts on Policy#, then every query against fact # 2 requiring state, will snowflake thru the million row fact table --> Performance issues.
If you add the dimension to fact #2,and join the facts on Policy#, then every query against fact # 1 requiring state, will snowflake thru the 10 million row fact table --> Performance issues.

And the big one, with any query that has either a measure or dimensional attribute from fact #1 star and fact #2 star --> All the measure from fact # 1 will be overstated --> the measure in fact #1 will be multiplied by the number of transactions for that policy in fact # 2.

Thats why best practice would say the state dimension belongs on both fact tables, and the fact tables shouldn't be joined.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

Post  VTK on Tue Aug 13, 2013 5:49 pm

Thanks


VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Reusing dimesion keys ?

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