Data Warehousing and Data Science

6 October 2011

SSAS Videos

Filed under: Analysis Services — Vincent Rainardi @ 4:16 am

Thanks to Boby Joseph who mentioned Craig Utley’s SSAS videos to me:

Contents include Cube Sparsity, Query Performance, Perspectives, Translations , Storage Modes, ProClarity.

Craig Utley is a former SQL Server MVP. He is a mentor with Solid Quality Mentors and a former Program Manager on the SQLCAT team. He wrote Business Intelligence with Microsoft® Office PerformancePoint Server 2007.

There are quite a lot of videos in this web site. Probably about 30 SSAS videos and 5 ProClarity videos.

The other place where you can watch SSAS videos is SQLbits:

2 October 2011

Two Methods of Reporting

Filed under: Data Warehousing — Vincent Rainardi @ 10:11 pm

For an application we have two methods of reporting:

a) We build reports to extract data directly from the application database
b) We send the application data to a data warehouse and we build reports from the data warehouse

I have worked with some companies which did a) as well as b). The advantages for doing b) are:

  1. It enables us to report over time. We will be able to understand how the numbers are moving from day to day, month to month.
  2. It enables us to store history. The history of attributes is stored in Slowly Changing Dimensions and the history of measures is stored in Periodic Snapshot Fact Tables.
    Note: Point 2 enables point 1.
  3. It integrates the data from this particular application with data from other application.
  4. It enables you to do OLAP/cube, e.g. QlikView or SSAS. The main benefit of cube over report is speed. In report the response time is typically 2-5 seconds, but in some areas it could be 30 to 2 minutes. In cube the response time is always under 1 second.

The advantages of doing a) are:

  1. It enables us to report in real time. As soon as the user enters the data into the application, they can see it in the report. There is no delay. In DW you typically need to wait one day, and in some cases 3 hours.
    Note: There are techniques to make it real time, but there are strings attached.
  2. It is simpler, quicker to build and lower cost. Less than half of b).








Blog at