Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

View previous topic View next topic Go down

Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

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

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


omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

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

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

View user profile

Back to top Go down

Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

Post  omm on Tue Aug 02, 2011 9:22 am

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

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

Post  cjrinpdx on Tue Aug 02, 2011 6:25 pm

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

View user profile

Back to top Go down

Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

Post  omm on Wed Aug 03, 2011 9:23 am

@cjrinpdx:Thanks for you suggestion

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Is it good to have dimension table alone instead of having both fact and dimension... in this scenario?

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