Reference Tables

View previous topic View next topic Go down

Reference Tables

Post  juliasuzuki on 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

View user profile

Back to top Go down

Re: Reference Tables

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Reference Tables

Post  Kumarpunna on 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

View user profile

Back to top Go down

Re: Reference Tables

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