Reporting and DW recommendations

Recently (6/15/2012), JJ-469859 asked a question on SQL Server Central about recommendations for a DW and reporting solution in SQL Server 2012.

… are you planning on using both AlwaysOn to create a read-only copy for OLTP reporting and then also creating a DW in SSAS? Or if you do a DW, do you skip the AlwaysOn OLTP reporting and only use SSAS?

When using the new Tabular data model in SSAS, can you report directly off of OLTP tables? Or is the recommendation to get the data into a DW format similar to how AdventureWorks is translated to AdventureWorksDW? If you need to get into a DW format, are you using CDC via SSIS?

This reply may be useful to others:

Unforetunately there are no simple answers to your questions. I think ultimately it comes down to how many users are on your system and how much they thrash the OLTP database and what sort of expectations the organisation has of the BI system and their budget.   The main purposes of a BI system are to provide very fast reporting, data analysis and ultimately prediction. There is a hierarchy that goes something like this:  data -> information -> knowledge -> wisdom -> prediction

Obviously running reports against a production OLTP system is not ideal, and using a read-only copy will help but the queries that drive the reports will still require joining a lot of tables and any analysis queries will run really really slowly, but it might be enough to stop there.

A star/snowflake style of schema in a Data warehouse is enough to provide faster reporting and it’s a more accessible model for end users. You could use SSIS to pump data from the production OLTP systems into the DW, but usually via a staging area/landing zone. The idea is to keep production DB and DW downtime to a minimum. You could stop there.

The next step beyond that would be to create an OLAP cube from the DW. Technically you could create an OLAP cube directly from the OLTP production database but it’s not advised. [quote] When using the new Tabular data model in SSAS, can you report directly off of OLTP tables [/quote] Technically no. SSAS is an OLAP cube solution. It creates another structure (cube) separate from the source database (ideally the DW). It’s a semantic model of the data designed to be very quick, have friendly names and obvious data relationships. It also has roll-up and other types of aggregations pre-calculated so data analysis is fast. When you use SSAS you are never directly using the source tables whether they are from the DB or the DW. The question of Tabular vs Multidimensional model in SSAS has been well covered by others especially James Serra.

To get data into a DW format, you have many choices. You could use java to write modules that control sql scripts for example. Using SSIS is probably the best choice because it creates all the procedural flow for you and makes it easy to deploy and do source control. CDC is one option yes, you could use the CDC change tables as a data source for SSIS.

Change data capture data flow (from BOL)

Another option I’ve seen is to use log shipping to the staging area and then populate the DW from there. The ETL process is probably the most tricky because you are not only trying to do an incremental (delta) load into the DW, you are also taking the opportunity to clean, merge, filter, sort and reject data.

My advice to you starting out is to do it in small steps. A complete BI solution requires an enterprise DW (combine data from all systems, HRIS/CRM/ERP etc etc) and can take a very long time to complete and fine tune.


1.Basic) Reporting direct from OLTP production DB
2. Easy) Reporting from a mirror/ always on / other clone
3. Interim solution) Use a reporting database. A bunch of specific purpose reporting tables. I use one of these to report from an oracle prod DB. It’s similar to a DW but not as structured.
4. Advanced) Use a DW 5. Pro) Use a cube

Be careful implementing a cube as a reporting solution. The wrong way to do it is to store every possible level of granularity and every possible dimension. The strength of cubes is in analysis. Cubes provide aggregations and the ability to slice and dice through accumulated data to create predictive models. Using a cube to run detailed operational reports will lose the performance benefits and you may as well report direct from the DW or source DB. By all means create SSRS reports against a cube, I do it all the time. I just find that MDX queries that start to get complex tend to run very slowly.

For as many different organisations that exist, there are as many possible solutions.

One thought on “Reporting and DW recommendations

Leave a Reply

Your email address will not be published. Required fields are marked *