rka (4/14/2013)
Someone from my company's strategy has said that the Data Warehouse model needs to be:- Atomic
- 3rd NF
- Relational
But I disagree. I always considered DW models to be:
- Dimensional
- Data has to be very granular (represents 3NF atomic data)
- Subject Oriented
But the Strategist from the team came back to me and commented:
dimensional layer is for user access. What if user doesn't need to see the the data structures, eg if using a dashboard. Should we build a dimensional model? If so what advantages does it bring? Could just build a single layer? The atomic? The dimensional ? What are the implications if the dashboard includes data from both the DW and a OLTP application?
I think some of the disagreement boils down to the differences in architecture between the Kimball and Inmon "schools" and perhaps in some different use in terms.
James Serra gave a good presentation on an effective BI architecture at this past Saturday's SQL Saturday in Chicago. The slides can be found here: http://www.slideshare.net/jamserra/data-warehouse-architecture-16065902
It seems that the Inmon school wants what I'd refer to as a "clean repository" and Inmon would call a CIF (Corporate Information Factory) and others call the data warehouse. A 3rd normal form repository from which your data marts are built off of.
The Kimball school goes directly to the data marts off of a dimensional star schema; but there is an emphasis on designing the bus matrix so you don't have a bunch of isolated silos.
Our current setup is very much like you describe, though I think I'm going to move towards a more hybrid model that incorporates some of both schools. My thought is that with the new tabular capabilities in SQL Server 2012, it may be more straightforward for the analysts/power users to use PowerPivot off of a clean repository/CIF and continue our MDX/OLAP development with our dimensional data marts.
I'm don't think I answered all of your questions, but there's a few things to think about.
HTH,
Rob