Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Reference Tables

3 posters

Go down

Reference Tables Empty Reference Tables

Post  juliasuzuki Thu Aug 29, 2013 4:19 pm

Hello,

I have two questions regarding reference tables in the area of dimensional data modeling.

1. We have a total of 581 reference areas/categories.  Which method is considered a good practice below and what are pros and cons?
Method 1: Put all the codes and descriptions columns of all 581 reference areas/categories into one master reference table.
Method 2: Create 581 individual reference tables.

2. Have you seen two schemas created for a set of dimensional data models where one schema will hold all the core/confirmed dimension tables and the other schema will hold all the reference tables.  Would you please give me a scenario where this design is implemented?  I would like to understand the rationale behind this design.

Thank you very much!
Julia

juliasuzuki

Posts : 2
Join date : 2013-08-29

Back to top Go down

Reference Tables Empty Re: Reference Tables

Post  BoxesAndLines Fri Aug 30, 2013 10:29 am

juliasuzuki wrote:Hello,

I have two questions regarding reference tables in the area of dimensional data modeling.

1. We have a total of 581 reference areas/categories.  Which method is considered a good practice below and what are pros and cons?
Method 1: Put all the codes and descriptions columns of all 581 reference areas/categories into one master reference table.
Method 2: Create 581 individual reference tables.
Neither. Codes and preferably their descriptions are integrated into the dimensions.

2. Have you seen two schemas created for a set of dimensional data models where one schema will hold all the core/confirmed dimension tables and the other schema will hold all the reference tables.  Would you please give me a scenario where this design is implemented?  I would like to understand the rationale behind this design.

Thank you very much!
Julia
I have not. I like to keep all my dimensional tables in the same schema. ETL, staging, and work tables go into a different schema.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Reference Tables Empty Re: Reference Tables

Post  Kumarpunna Wed Sep 04, 2013 11:11 am

Answers


I have two questions regarding reference tables in the area of dimensional data modeling.

1. We have a total of 581 reference areas/categories. Which method is considered a good practice below and what are pros and cons?
Method 1: Put all the codes and descriptions columns of all 581 reference areas/categories into one master reference table.
Method 2: Create 581 individual reference tables.

I would put all of them in one table because it does not sound a big and fat table and advanteage is maintance, it hard to rember 500+ name , where as in one table you search "%abc%"

2. Have you seen two schemas created for a set of dimensional data models where one schema will hold all the core/confirmed dimension tables and the other schema will hold all the reference tables. Would you please give me a scenario where this design is implemented? I would like to understand the rationale behind this design.
It better to have the dim table in one schema, because when you automate your reporting tool normally these are parameterized and one parameter is good, when dba want to do maintainence also its easy
stagging can be seperated because you don't use that in that in the reports and stagging is always (mostly) drop and reload, any typo will not impact the actual dim & fact table (stagging is closely name to dim & fact)

Thanks
Kumar

Kumarpunna

Posts : 6
Join date : 2013-09-04

Back to top Go down

Reference Tables Empty Re: Reference Tables

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum