Is dimensional modeling too difficult? I do hear this question when debating sources for cube data in business intelligence solutions. At Microsoft, our OLAP engine and data warehouse tools are all wrapped up into SQL Server and its components: SQL Server database engine, SQL Server Analysis Services (SSAS) and SQL Server Integration Services. But if the requirements for your BI application are real time or near real time, there may be a need to build your cube directly off an OLTP or transactional database system.
Data warehouse and BI purists will generally tend to steer away from that. In fact, in SSAS, there are many capabilities allowing you to have your cube built and refreshed upon partitions and measure groups and sensing source data changes so that portions of the cube are refreshed. Many of these mechanisms give you essentially the near real time capability of what is sometimes refered to as "operational BI".
But most certainly a common good practice is to build your BI solution upon a cube in the OLAP engine that is based on a data warehouse of some sort. In my role at Microsoft, my group generally looks to build functional BI solutions built all Microsoft and partner products that give you a snap-in solution for a particular business problem. Therefore, we do not generally provide an enterprise data warehouse. Instead, we call them data marts with entities and attributes that fit just what is needed for a particular business problem.
Indeed, we utilize dimensional modeling based upon the TDWI methodolgy devised by Ralph Kimball. We have found that SQL Server is very well suited for building what we call a Unified Dimensional Models (UDM) for your data warehouse. But it can be challenging for database administrators or developers who are comfortable and well versed in relational database design. A very good source of information on dimensional models can be found from the Microsoft reference architecture of Project Real: http://www.microsoft.com/sql/solutions/bi/projectreal.mspx. I recommend looking at the sample data warehouse that ships with SQL Server to see examples of a star schema for storing your measures & dimensions. Taking the time to learn these techniques to build a data mart or data warehouse will provide the best foundation for a successful Microsoft BI implementation. And this way, your decisions can be based on application requirements such as realtime, near realtime, quarterly reporting, predictive reports, etc. as opposed to the technical challenges of building a star schema.
I think that's it from here at the DBCF BI Solutions desk for the year. Happy Holidays and I look forward to blogging in the new year! BTW, be sure to keep an eye out for our MEC BI solution launch events coming in 2008 ...