March 3, 2009 at 11:30 am
This year, we're planning on creating a data warehouse and implementing an OLAP solution. I've read a little about having a dimensional model or a relational model for the data warehouse. I'm planning on purchasing the The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset to help with this process, and maybe my questions will be answered there, but I thought I'd ask them here first.
So here are my questions:
1. Is the dimensional model for the data warehouse using the star schema approach to the data warehouse design or is the dimensional model the Analysis Services cubes that are built on top of the data warehouse?
2. When looking at the AdventureworksDW example, the tables are prefixed with Dim and Fact. Is this only to identify the dimension and fact tables quickly as a beginner or do people actually use those prefixes in their Data Warehouse?
3. Does anyone implement both a relational and dimensional model in their data warehouse? If so, what are you accomplishing by doing so?
Thanks for your time and responses.
March 5, 2009 at 3:58 am
Hi,
in the real world there are always compromises from the ideal. The most recent warehouse that I built is laid out with fact and dimensional tables. The naming convention is whs_fact_xxx_vvv or whs_dim_xxx_vvv where xxx describes the source system that the data came from, vvv describes the contents of the table. So for customers :whs_dim_crm_customer. The customers details are stored in a flattened table i.e all of their details in one table. There are no tables customer town, customer county etc. This layout works very well for cubes and and for information gatherers it works very well (less joins to do).
I have had to incorporate some elements of relational design in some parts as it is required for relational style reporting and is pretty much the same layout as the source system. The relational elemnent stays in a seperate data mart.
Hope this helps.
Ells
March 5, 2009 at 8:50 am
Thanks for your response.
It's starting to make more sense now.
March 5, 2009 at 11:53 am
To add to Rookies comments.
The data in data warehouse is humongous, trillions of records which are maintained... which means if you have more joins the execution of queries takes more time.
When the data from the OLTP system is migrated to the warehouse the data is categorised and confirmed. The data source of warehouse could be mutilple. the data needs to be cleaned, processed and fixed at various levels.
there are various data marts present in warehouse which are dependent on each other . Thus it is recommended to have less joins. this sometimes leads to duplication of data which is fine and in turn gives better performance .
March 5, 2009 at 4:12 pm
When you setup a data mart, do you create the data mart outside the data warehouse as a separate database or is it created inside the dimensional data warehouse?
March 5, 2009 at 4:44 pm
Sankey (3/3/2009)
This year, we're planning on creating a data warehouse and implementing an OLAP solution. I've read a little about having a dimensional model or a relational model for the data warehouse.
[font="Verdana"]I wouldn't recommend even thinking of creating a relational model data warehouse. [/font]
Sankey (3/3/2009)
I'm planning on purchasing the The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset to help with this process, and maybe my questions will be answered there, but I thought I'd ask them here first.
[font="Verdana"]Good starts. Can I also recommend you look at some of the data warehouse builders out there. I can recommend Wherescape, which is what we use.[/font]
Sankey (3/3/2009)
So here are my questions:1. Is the dimensional model for the data warehouse using the star schema approach to the data warehouse design or is the dimensional model the Analysis Services cubes that are built on top of the data warehouse?
[font="Verdana"]The recommendation from most of the data warehousing people is to use a dimensional model. That would be in your SQL Server database. What's in Analysis Services is an OLAP representation of that.
You could do it otherwise, but you'd just create a nightmare.
[/font]
Sankey (3/3/2009)
2. When looking at the AdventureworksDW example, the tables are prefixed with Dim and Fact. Is this only to identify the dimension and fact tables quickly as a beginner or do people actually use those prefixes in their Data Warehouse?
[font="Verdana"]I think it's an individual preference thing. It's rather nice to have all of your dimensions, facts, aggregates, etc grouped together. You could use extended attributes to label them and achieve the same thing I suppose, but they wouldn't group as nicely on most tools.
[/font]
Sankey (3/3/2009)
3. Does anyone implement both a relational and dimensional model in their data warehouse? If so, what are you accomplishing by doing so?
[font="Verdana"]We have one of each. The older one was basically a relational model, and we handed out access to it via the SQL Server query tool. The new one uses whizz-bang query tools as the front end, and it's a star-schema (dimensional model) form.
[/font]
Sankey (3/5/2009)
When you setup a data mart, do you create the data mart outside the data warehouse as a separate database or is it created inside the dimensional data warehouse?
[font="Verdana"]Up to you. A data warehouse is usually the superset of your data marts, so you can't really create a data mart "outside the data warehouse". Whether you separate the data marts into databases... well, I wouldn't recommend it. There's really no need. You want to head towards a conformed warehouse where people can report right across the enterprise business units, so everything plugs together. Separate databases for data marts discourage that. And by using partitioning and file groups, there's no real technical need for the separate databases.
I think most of what you are asking about is "best practices" (or "recommended practices" or however you want to phrase that.) A lot of that will depend on what toolset you choose, as most toolsets make some assumptions on what those practices are.
[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply