Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
David C UK
David C UK
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 63
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
cees van diermen
cees van diermen
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 83
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search