January 30, 2013 at 3:47 am
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
January 30, 2013 at 8:28 am
Hi Maneesh,
I'm not sure I understand what you mean exactly can you explain further? 😎
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.
January 30, 2013 at 9:15 pm
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).
January 31, 2013 at 1:04 pm
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.
January 31, 2013 at 4:16 pm
I definitely agree with what StevefromOz says...aggregations are the way to go so that you can get good performance with little effort.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply