Beginner questions: Benefits of using an AS cube, over just database & SSRS?

  • Firstly, I am used to using SQL Server and SSRS for quite a long time. And have recently started dabbling with Report Models.

    I keep reading about Cubes & AS; and I think to myself, there must be some huge benefit to using these, and I’m sure my workplace could probably benefit from them too. But whenever I have a play around creating one, I can’t seem to see the benefit. I’m sure there must be something I’m probably doing wrong. Or I’m missing some key concept and have the wrong idea of what they can offer.

    I thought it was essentially a faster way of reporting on data that I similarly already do with SQL Server & Reporting Services.

    But essentially, what are the benefits of using Cubes/AS over not using it and carrying on just using RS against SQL Server.

    Maybe I’m doing wrong, being basically creating a ‘play’ cube from one of my database applications, using the main tables and joining most lookups to it. Pretty much to have a ‘play around’ to see how it works and what it can offer.

    Another additional question I have which may help me is:

    Q. Do I have to create a separate full on data warehouse database to report from (rather than the transactional one used by the application). Or is that what ‘processing and publishing’ a cube does?

    Thanks,

    David

  • Hello David,

    Just a few (obviously there are more, but my time is limited) benifits I see in using a (SSAS-) Cube versus using SSRS are:

    - Performance. During cube processing SSAS will (when setup properly) pre-calculate and physically stores aggregations of facts (Amounts, Quantities, Money). These aggregations, for example Turnover by Year and Region, are used when an enduser queries the cube for this type of information. Therefore the query responsetime can be very short.

    When the query is fired SSAS does not have to calculate the outcome from the underlying details (like Tranact Sql has to do), but can take the values directly from the stored aggregations.

    Besides that SSAS stores query-results in a cache. The next time the same type of query is fired, it will try to get it from the cache.

    - Advanced time-calculations. It's very easy to implement advanced time calculations like 12 month rolling average (trend !), yeartodate and references to paralell periods in previous years. This is typically the stuff decision-makers in the organisation want to have. Imagine, how many (transact)-sql-queries are required for calculating rolling averages for each of the previous 12 months (2009-May..2010 April) => 12 ! using the cube as a datasource => Only 1.

    - Security

    You can use the security setting to give end-users acces to only those parts (slices) of the cube relevant to them.

    - Access/Frontend-tools

    Endusers can use Microsoft Excel:-):-D:-) as a powerfull front-end tool on top of the cube. Besides that the same cube can at the same time be used as a datasource for SSRS and Sharepoint (KPI-) webparts.

    Getting back to your question on ‘processing and publishing’ a cube does ? Once you've created a SSAS solution you have deploy it to the AS-server. Once deployed the metadata is available on the AS server. In order to also get actaul data in your cube, you have to process it.

    The deployment properties of your Visual Studio AS project determine what should be done once you select 'Deploy' from the menu.

    Kind regards,

    Cees

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply