Data Warehouse Questions

View previous topic View next topic Go down

Data Warehouse Questions

Post  rsankey on Wed Mar 04, 2009 1:29 pm

This year, were planning on creating a data warehouse and implementing an OLAP solution. From what Ive read on this site and others, I will be better off if the data warehouse is designed with a dimensional model. I have a few questions about the dimensional model, operational data store, and a staging area:

1. Is the operational data store a staging area? Is the operational data store to be used for reports that dont need to run off cubes?

2. If you use the dimensional model for your data warehouse, should the staging area use the dimensional model as well or do you use a relational model there instead? Do you need a staging area at all?

3. In Joys talk during the Week of Warehousing webinar series, she mentioned that having a relational database for data management is important. Where does the relational database fit into the dimensional model for the data warehouse? Is that a second database or the staging area or the operational data store?

4. I was looking at the AdventureworksDW sample database, and all the tables are prefixed with Dim or Fact. Is this strictly for the sample database or is this a best practice that should be followed when building a data warehouse with the dimensional model?

As you can probably tell from my questions, this will be my first data warehouse implementation, so Im trying to wrap my head around the structure and how all the pieces fit together.

Thanks in advance for your time and responses

rsankey

Posts : 1
Join date : 2009-03-04

View user profile

Back to top Go down

Re: Data Warehouse Questions

Post  BoxesAndLines on Wed Mar 04, 2009 7:25 pm

rsankey wrote:This year, were planning on creating a data warehouse and implementing an OLAP solution. From what Ive read on this site and others, I will be better off if the data warehouse is designed with a dimensional model. I have a few questions about the dimensional model, operational data store, and a staging area:

1. Is the operational data store a staging area? Is the operational data store to be used for reports that dont need to run off cubes?
Yes, it should serve as your staging area for DW loads. It can be used for reports not used in cubes, but normally you will want to run those off your fact and dimension tables. I have gone back to the ODS to get data that was not included in the warehouse.

rsankey wrote: 2. If you use the dimensional model for your data warehouse, should the staging area use the dimensional model as well or do you use a relational model there instead? Do you need a staging area at all?
My ODS reflects the source system data structure. I add some history and auditing columns for the obvious reasons.

rsankey wrote: 3. In Joys talk during the Week of Warehousing webinar series, she mentioned that having a relational database for data management is important. Where does the relational database fit into the dimensional model for the data warehouse? Is that a second database or the staging area or the operational data store?
The dimensional model is a relational model. All I believe Joy is saying is that you need to use a relational database like Oracle or SQL Server.

rsankey wrote: 4. I was looking at the AdventureworksDW sample database, and all the tables are prefixed with Dim or Fact. Is this strictly for the sample database or is this a best practice that should be followed when building a data warehouse with the dimensional model?
Naming conventions for database objects are always important. Hire a good data modeler and they will manage that for you. My naming convention is [SystemID]_[FACT or DIM]_[TableName]. This way, when I browse the database objects in Toad or some other tool, all the facts and dimensions line up like I like them.

rsankey wrote: As you can probably tell from my questions, this will be my first data warehouse implementation, so Im trying to wrap my head around the structure and how all the pieces fit together.

Thanks in advance for your time and responses
Good luck. You've find the right website to help.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Data Warehouse Questions

Post  Guest on Sat Dec 12, 2009 12:05 am

Nice discussion and thanx for posting
Data Entry India

Guest
Guest


Back to top Go down

Re: Data Warehouse Questions

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