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]