Ad Hoc Reports Against Cube or Dimensional Model

[jimbo1580]

I am building a data warehouse on the Microsoft BI Platform. The architecture will include a dimensional model database residing on SQL Server 2008 that is updated nightly from a daily snapshot of a production database. The architecture will also include an OLAP cube in Analysis Services built on top of the dimensional model. I have been doing some research on dimensional modeling and report building and am a little confused on how I should set up the ad hoc environment. Should I make both the cube and the dimensional model available to the end users for ad hoc analysis? If so, how would they know which to utilize for the task they are trying to accomplish? The reason that I am thinking to make both available is because there seems to be certain queries that the users need to run that you can't run against the cube. For right now, due to budget constraints, my ad hoc environment will consist of Excel 2007 and Report Builder.

[BoxesAndLines]

Analysis services support drill to detail. I wouldn't expose the base tables in warehouse to the end user community though. You lose control of information when you do that.

[jimbo1580]

In that case, how would they issue queries that require two passes through the data to compile?

[John Simon]

It depends on how much data you have. If you are talking hundreds of millions or billions of rows of data, then I'd build a report model for users who want transaction level detail, with the cubes supplying aggregated queries. Especially when users want to see things like TransactionID and you need to build a dimension in SSAS which can be quite slow depending on the data volumes.

Put it this way: For most ad-hoc, the users can view data via the cube. For more detailed level reporting, or for canned-reports, they should use Reporting Services.

[Sponsored content]