How Aggregation is calculated in Cube in SSAS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » How Aggregation is calculated in Cube in SSAS...

How Aggregation is calculated in Cube in SSAS

Rate Topic

Display Mode

Topic Options

Author

Message

maneeshkrverma

Posted Wednesday, January 30, 2013 3:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:07 PM
Points: 2, Visits: 25

Hi,

I am new in ssas. I want to know the why do we need to create aggregation in measure while we specify group function (count, sum) while creating measures.

what if we dont create aggregation and create measures using sum group function, will the sum not be calculated on the column when cube is processed.

regards
Maneesh

Post #1413552

ldorian81

Posted Wednesday, January 30, 2013 8:28 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 1:37 AM
Points: 275, Visits: 222

Hi Maneesh,

I'm not sure I understand what you mean exactly can you explain further? Cool

Regards
ld

Regards
ld

Stoke-on-Trent
United Kingdom

If at first you don't succeed, go to the pub and drink away your current thought plan.

Post #1413661

happycat59

Posted Wednesday, January 30, 2013 9:15 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 2,471, Visits: 2,065

Underneath the rapper, Analysis Services pre-calculates various sub-totals for the different dimensions you have in your cube.
It does this so then when you ask for the value of a measure, it may be able to give the answer by doing lookups of a few records
instead of, potentially, 100's 1000's or more records (which is what the relational database would need to do).

e.g. if you have a cube with a Product dimesion (Product --> Product Sub-Category --> Product Category)
, a geography dimension (City --> Region -- > State -- > Country) and a date dimension (Date --> Month -- > Quarter -- > Year)
when you ask Analysis Services to give you the sales for "Quarter 1, 2012", instead of adding up the sales for all products
for all Cities and for all Dates (in the quarter), Analysis Services may already have aggregations product sub categories,
for regions and for the months.

The particular aggregations that are available is initially determined when the cube is being designed and should be updated
using the actual queries that are being used

This means that is has to do less work when then query is executing. The tradeoff is that the extra work is still required. However, instead of being required for each an every query, it is done once when the cube is processed (which is typically done during the night although real-time processing of cubes is becoming more common).

Post #1413837

stevefromOZ

Posted Thursday, January 31, 2013 1:04 PM

SSCommitted

Group: Moderators
Last Login: Friday, May 17, 2013 11:00 AM
Points: 1,763, Visits: 3,187

and getting down to the details - yes, even if you don't have an aggregate, the sum measure will still work. You'll be expecting/asking the engine to calculate that value on the fly, but assuming it can do so, it will.

But, just because something can or will, doesn't mean it's right. Do yourself a favor and add aggregations :)

Steve.

Post #1414300

happycat59

Posted Thursday, January 31, 2013 4:16 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 2,471, Visits: 2,065

I definitely agree with what StevefromOz says...aggregations are the way to go so that you can get good performance with little effort.

Post #1414388

« Prev Topic | Next Topic »

Permissions