Data Warehouse Questions

This year, we’re planning on creating a data warehouse and implementing an OLAP solution. From what I’ve 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 don’t 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 Joy’s 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 I’m trying to wrap my head around the structure and how all the pieces fit together.

Thanks in advance for your time and responses

rsankey wrote:This year, we’re planning on creating a data warehouse and implementing an OLAP solution. From what I’ve 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 don’t 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 Joy’s 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 I’m 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.

Nice discussion and thanx for posting
Data Entry India