Can a dimension table directly link to another dimension table?

[omm]

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

[VHF]

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! :-)

[cjrinpdx]

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.

[ngalemmo]

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?

[omm]

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 :)]

[VHF]

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

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.

[omm]

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

[hang]

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.

[rschulz01]

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

[hang]

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.

[Sponsored content]