Ad Hoc Reports Against Cube or Dimensional Model

View previous topic View next topic Go down

Ad Hoc Reports Against Cube or Dimensional Model

Post  jimbo1580 on Tue Dec 08, 2009 3:19 pm

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.

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Ad Hoc Reports Against Cube or Dimensional Model

Post  BoxesAndLines on Wed Dec 09, 2009 10:25 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Ad Hoc Reports Against Cube or Dimensional Model

Post  jimbo1580 on Wed Dec 09, 2009 11:26 am

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

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Ad Hoc Reports Against Cube or Dimensional Model

Post  John Simon on Sat Dec 12, 2009 6:54 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Ad Hoc Reports Against Cube or Dimensional Model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum