Can a dimension table directly link to another dimension table?

View previous topic View next topic Go down

Can a dimension table directly link to another dimension table?

Post  omm on Mon Aug 01, 2011 4:35 pm

I have a dimension table which has a couple of date fields. I would like to use the date dimension table for those dates.
I am not sure, if that is OK to have direct link between two dimension tables.
Can anyone please clarify?

Thanks,
Om

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  VHF on Mon Aug 01, 2011 5:18 pm

Sure, that is an outrigger relationship between dimensions that you describe. Happens most frequently with dates as in your situation.

However, don't do it too often lest you find yourself sliding down the slippery slope of a snowflake schema! :-)


Last edited by VHF on Mon Aug 01, 2011 5:21 pm; edited 1 time in total (Reason for editing : added humor)

VHF

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

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  cjrinpdx on Mon Aug 01, 2011 5:53 pm

Unless you are going to count or group the data in the dimension by the dates I would suggest to just store the date in your dimension table.

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  ngalemmo on Mon Aug 01, 2011 7:19 pm

It is a snowflake and should be avoided.

The question that needs to be asked is: What are these dates and why do they need attributes from the date dimension?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  omm on Tue Aug 02, 2011 11:12 am

The dates here are effective from and end dates of the client. We are planning to store the dates in BigInt instead of dates by doing a lookup against the date dimension table.
Functionality: We are trying to figure out the count for the active clients for a given point-in-time.

For more info, please find the link which explains why I am doing this
http://forum.kimballgroup.com/t1284-is-it-good-to-have-dimension-table-alone-instead-of-having-both-fact-and-dimension-in-this-scenario#5840
[Thanks for your valuable replies :)]

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  VHF on Tue Aug 02, 2011 11:23 am

Usually begin/end effective dates are stored as a Date data type rather than an integer key.

omm wrote:Functionality: We are trying to figure out the count for the active clients for a given point-in-time.
Do you need date dimension functionality in determining the point-in-time, in other words do you need to use dimension attributes for a fiscal calendar to identify the point-in-time? If you are just specifying a date, this is one case where not using the date dimension would result in a simpler design and improved query performance.


VHF

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

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  omm on Tue Aug 02, 2011 11:32 am

Yes, we are using only that count functionality comparing with dates. So, I think I rather leave the effective dates in Date DataType and keep it simple.

Thanks for your advice
Om

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  hang on Tue Aug 02, 2011 4:39 pm

omm wrote:We are planning to store the dates in BigInt instead of dates by doing a lookup against the date dimension table.
Generally you should not use date key in dimensions. However on different note, why are you using BigInt for date key instead of Int anyway? Use Int for all the dimensions by default as you would unlikely have any dimension with more than a billion records. From performance perspective, DB engines would love Int data type as dimension key in star schema.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  rschulz01 on Wed Aug 10, 2011 3:23 pm

I have a similar situation which involves dimensions other than the date dimension. I am modeling invoices; the company provides servers/internet hosting. Each customer is issued an INVOICE, which is comprised of one or more INVOICE_ANCHOR (the server), which in turn is composed of INVOICE_ITEM (the processor, the hard drives, the RAM, etc). The fact table, with the various fees, is at the INVOICE_ITEM level. The problem concerns other dimensions, such as the BUSINESS_CLASS and PRODUCT - the business wishes to select/filter by the PRODUCT, BUSINESS_CLASS, etc at the INVOICE_ANCHOR level as well as at the INVOICE_ITEM level, and of course the anchor will different values from those dimensions than the invoice item. In the model the previous data architect "snowflaked" the design, creating relationships from BUSINESS_CLASS and PRODUCT to the INVOICE_ANCHOR. This approach was used to model orders as well, so there is some desire on the part of my management to remain "consistent with what we've done before." However, I'm not sure this is the correct approach.

Have we gone down the wrong path with this design? What would you recommend?

Thanks in advance for your help!

Rick

rschulz01

Posts : 5
Join date : 2011-07-26

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

Post  hang on Wed Aug 10, 2011 4:51 pm

I guess you would have a PRODUCT dimension. So in that case, the question is, can you de-normalise BUSINESS_CLASS and INVOICE_ANCHOR into the PRODUCT dimension.

To me, INVOICE_ANCHOR sounds like an invoice number that groups invoice items together, which could be a candidate for degenerate dimension (DD). So I don't think you should put INVOICE_ANCHOR in PRODUCT dimension. However BUSINESS_CLASS seems to be another level of grouping attribute and its relationship with product may be changing on different invoices. I would have standalone dimension for BUSINESS_CLASS and have FK in the fact table.

So basically, you may not link BUSINESS_CLASS and INVOICE_ANCHOR to the PRODUCT directly, but let the fact table reflect the relationship. It looks like the previous "snowflaked" design attempts to have some sort of factless fact table to reflect the relationship while the correlation could be contained in invoice fact table itself.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Can a dimension table directly link to another dimension table?

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