Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?
2 posters
Page 1 of 1
Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?
We currently have a fact and dimension tables for a client details.
The dimension table is storing name and id related which rarely changes. The fact table is storing effective and end dates(very rarely change) which is a factless table.
In this scenario, the ratio between fact and dimensional rows is generally 1:1 or 1:2. So, I think it is better to move these effective date fields to the dimension table and eliminate the fact table.
Current design:
claim fact -> client dmsn -> client fact->date/time dimension
Proposed design:
claim fact -> client dmsn->date/time dimension
Please suggest me if the proposed change is feasible or not and why.
Thanks,
Om
The dimension table is storing name and id related which rarely changes. The fact table is storing effective and end dates(very rarely change) which is a factless table.
In this scenario, the ratio between fact and dimensional rows is generally 1:1 or 1:2. So, I think it is better to move these effective date fields to the dimension table and eliminate the fact table.
Current design:
claim fact -> client dmsn -> client fact->date/time dimension
Proposed design:
claim fact -> client dmsn->date/time dimension
Please suggest me if the proposed change is feasible or not and why.
Thanks,
Om
omm- Posts : 11
Join date : 2011-07-29
Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?
Can you provide a little more information? It sounds like you are describing a type 2 SCD. What is the relationship between the client and the data in the fact table?
cjrinpdx- Posts : 51
Join date : 2011-07-14
Location : Portland, OR
Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?
Client dmsn holds data related to client name, client id where as client fact holds effective dates of when client has joined, terminated and status code to specify whether he is active or not.
Please let me know if any more details are needed.
Thanks,
Om
Please let me know if any more details are needed.
Thanks,
Om
omm- Posts : 11
Join date : 2011-07-29
Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?
I'm not going to talk about the date because I think that has been covered in your other post. Without knowing all the business requirements I was say yes, it looks like you are on the right track. It looks like you are describing the student/enrolled-in-class situation or "Factless Fact" where you are only tracking that the Client is participating in something and the related dates. Good luck.
cjrinpdx- Posts : 51
Join date : 2011-07-14
Location : Portland, OR
Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?
@cjrinpdx:Thanks for you suggestion
omm- Posts : 11
Join date : 2011-07-29
Similar topics
» Is it a good design to allow zeros in Fact table?
» SCD Type 2 dimension and fact table being the same table
» joining dimension table to dimension and again fact table
» attribute on fact table or dimension table?
» SCD Type 2 dimension and fact table being the same table
» joining dimension table to dimension and again fact table
» attribute on fact table or dimension table?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum