 | |
"Simple" calculation
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
"Simple" calculation -
11-22-2004
, 05:35 AM
#2
| |||
| |||
|
|
I have problems with calculations of this kind: sale=price*quantity. If I use calculated members it will calculate right if you are on leaf level in all dimensions. But when you go to for example total product it will aggregate prices and quantity and then multiply the sum of prices with the sum of quantities. That is very - of cause - very wrong. The easy way to solve this problem is to use calculated cells. When doing that you can tell it only to calculate on lowest level in all dimensions and then the result will be right everywhere (it will then aggregate "sale" in stead of price an quantity). BUT then performance becomes very bad, even with smal cubes ... I have looked at solve order and pass order, but can't figure out if that is the right way to go. Thank you in advance for any help! Regards, -- Kai Ettrup BI PARTNER A/S |
#3
| |||
| |||
|
| ... |
whose values are created at runtime, based upon the MDX expression(s)
they contain, a derived measure, just as any other cube measure, is
calculated during the cube processing cycle, and is stored within
Analysis Services. In the case of the calculated member, only its
definition is stored, and the values it renders are calculated in memory
when specified in a query. Because only the definition is stored, cube
processing time ("build time") is not materially increased, and, indeed,
depending upon the environment, this might be an appropriate tradeoff.
In our present scenario, however, query processing is the more important
concern, so we lose the primary benefit behind the choice of a
calculated measure to provide the needed values.
Derived measures differ from "ordinary" measures because they take
advantage of the flexibility that Analysis Services offers us in
modifying the source column property for a given measure. Because they
are stored in the cube file, as we have mentioned, they typically mean
more efficient query processing. Derived measures, by their nature, are
calculated prior to the creation of aggregations. (In contrast,
calculated measures are calculated after aggregations are created.) In
general, derived measures make sense if they will be called upon
frequently, as in reporting scenarios such as that of our hypothetical
information consumers. Calculated measures might be a better choice for
values that we expect to be used infrequently, again because the
tradeoff is tipped more in favor of a lower overhead on the cube
processing side, where query response time might not be as high a
priority.
We are limited to creating derived measures from the columns of the fact
table, since MSAS essentially only offers these columns as options in
the measure creation process. However, as many of us have found, a view
can be created to contain columns that lie outside the physical fact
table, making this limitation a bit less restrictive than it might
appear at first blush. Derived measures can extend well beyond simple
math, as we shall see in our example, and can apply conditional logic
and other nuances in derivation of the measure. The syntax obviously has
to fit the database
...
Quote:
- Deepak
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
#4
| |||
| |||
|
|
Defining a SQL view with a calculated column like "sale" should work - this column would drive a cube "sum" measure. A similar result can be achieved, without a new view, by entering a SQL formula, like "price * quantity", in the "Source Column" property of the cube measure. This is discussed as a "derived measure" in the article below: http://www.databasejournal.com/featu...10894_3394681_ 2 ... Unlike calculated members (including, of course, calculated measures), whose values are created at runtime, based upon the MDX expression(s) they contain, a derived measure, just as any other cube measure, is calculated during the cube processing cycle, and is stored within Analysis Services. In the case of the calculated member, only its definition is stored, and the values it renders are calculated in memory when specified in a query. Because only the definition is stored, cube processing time ("build time") is not materially increased, and, indeed, depending upon the environment, this might be an appropriate tradeoff. In our present scenario, however, query processing is the more important concern, so we lose the primary benefit behind the choice of a calculated measure to provide the needed values. Derived measures differ from "ordinary" measures because they take advantage of the flexibility that Analysis Services offers us in modifying the source column property for a given measure. Because they are stored in the cube file, as we have mentioned, they typically mean more efficient query processing. Derived measures, by their nature, are calculated prior to the creation of aggregations. (In contrast, calculated measures are calculated after aggregations are created.) In general, derived measures make sense if they will be called upon frequently, as in reporting scenarios such as that of our hypothetical information consumers. Calculated measures might be a better choice for values that we expect to be used infrequently, again because the tradeoff is tipped more in favor of a lower overhead on the cube processing side, where query response time might not be as high a priority. We are limited to creating derived measures from the columns of the fact table, since MSAS essentially only offers these columns as options in the measure creation process. However, as many of us have found, a view can be created to contain columns that lie outside the physical fact table, making this limitation a bit less restrictive than it might appear at first blush. Derived measures can extend well beyond simple math, as we shall see in our example, and can apply conditional logic and other nuances in derivation of the measure. The syntax obviously has to fit the database ... - Deepak *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
| |||
| |||
|
|
Defining a SQL view with a calculated column like "sale" should work - this column would drive a cube "sum" measure. A similar result can be achieved, without a new view, by entering a SQL formula, like "price * quantity", in the "Source Column" property of the cube measure. This is discussed as a "derived measure" in the article below: http://www.databasejournal.com/featu...10894_3394681_ 2 ... Unlike calculated members (including, of course, calculated measures), whose values are created at runtime, based upon the MDX expression(s) they contain, a derived measure, just as any other cube measure, is calculated during the cube processing cycle, and is stored within Analysis Services. In the case of the calculated member, only its definition is stored, and the values it renders are calculated in memory when specified in a query. Because only the definition is stored, cube processing time ("build time") is not materially increased, and, indeed, depending upon the environment, this might be an appropriate tradeoff. In our present scenario, however, query processing is the more important concern, so we lose the primary benefit behind the choice of a calculated measure to provide the needed values. Derived measures differ from "ordinary" measures because they take advantage of the flexibility that Analysis Services offers us in modifying the source column property for a given measure. Because they are stored in the cube file, as we have mentioned, they typically mean more efficient query processing. Derived measures, by their nature, are calculated prior to the creation of aggregations. (In contrast, calculated measures are calculated after aggregations are created.) In general, derived measures make sense if they will be called upon frequently, as in reporting scenarios such as that of our hypothetical information consumers. Calculated measures might be a better choice for values that we expect to be used infrequently, again because the tradeoff is tipped more in favor of a lower overhead on the cube processing side, where query response time might not be as high a priority. We are limited to creating derived measures from the columns of the fact table, since MSAS essentially only offers these columns as options in the measure creation process. However, as many of us have found, a view can be created to contain columns that lie outside the physical fact table, making this limitation a bit less restrictive than it might appear at first blush. Derived measures can extend well beyond simple math, as we shall see in our example, and can apply conditional logic and other nuances in derivation of the measure. The syntax obviously has to fit the database ... - Deepak *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.