In a sales cube I have added dimension formulas to an account dimension. Example: NetRevenuePer100kg. The formula is:
NetRevenue / Quantity * 100
When I use this in a MDX query like
select
{[DIM_ACCOUNT].[NetRevenuePer100kg]} on columns,
{[DIM_PRODUCT].[WOOD]} on rows
from cube
where ([DIM_TIME].[2005].[Q1])
everything is fine.
But using the following calculated member in the where clause:
... member [DIM_TIME].[MY_TIME] as 'Sum([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...
... where ([DIM_TIME].[MY_TIME])
I get a value which is about 3 times greater than the correct one.
I think it is a problem about the calculation order. It seems that first the value for each month is calculated and then they are summed up. Using the AVG function is not the solution. It gives only a almost correct value.
When I use a calculated member instead of a dimension formula everything is ok.
But I wanna get it right with a dimension formula.
Thanks for any help
Cornelius,
You are correct about your issue being related to solve order. In the example you gave you should be able to get the result you are looking for by replacing the SUM() function with AGGREGATE().
Try:
... member [DIM_TIME].[MY_TIME] as 'AGGREGATE([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...
HTH,
- Steve
|||Thanks Steve for your reply!
I tried it. But there is no difference between Sum and Aggregate.
|||Cornelius,
I should have confirmed this to begin with, but is your "NetRevenuePer100kg" calculation in the cube? Can you provide the MDX for both calulations and the query?
- Steve
|||Hi Steve,
formula in the account dimension [NetRevPer100kg]:
iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)
The complete MDX Query (please copy and enlarge in your editor):
with
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle Bereichsleiter].Children
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechnung],[PRODUCT_GROUP].[sy_Slicer])
The calculated member [Test_FINE] gives the correct result. The member based on the dimension formula [NetRevPer100kg] only when I use the time member [Quartal] (uncommented).
It is a strange thing for me, that a calculated member and a member based on a dimension formula returning different results allthough using the identical mdx expression. And it only happens when I use a Sum (or Aggragate function) on an other member in the query.
Thanks for a reply
|||Cornelius,
I noticed in your MDX you still have a background member defined using SUM:
member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
The reason I think you are running into this issue is related to solve order. The AGGREGATE function will correctly sum up the numerator and denominator before performing the division. SUM will simply add up the percentages. In AS2K5 all calculations in the cube script are evaluated before any query or session scoped calculated members. This is why you are probably seeing a difference in the cube based calculated member results versus your query scoped calculated member. It is important that any aggregated member in the WHERE clause be calculated using AGGREGATE rather than SUM.
HTH,
- Steve
|||Hi Steve,
with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]'
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})'
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])
In my case there is no difference using the SUM or AGGREGATE function.
I played with the solve_order.
with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=1
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=1
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})', solve_order=0
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]', solve_order=0
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})', solve_order=0
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})', solve_order=0
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})', solve_order=0
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])
Applying the solve_order 1 to the Account members and the solve_order 0 to the other dimension members I receive the same results ([Test_FINE] is correct, [CM_NetRevPer100kg] not).
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=0
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=0
Setting the solve_order of the Account members to 0 and the solve_order of the other dimension members to 1 also the calculated member [Test_FINE] returns a incorrect value. This behaviour I understand: in this case first the calculation is performed and then the calculation results are aggregated.
But I still see no way to influence the aggregation of the member based on the dimension formula.
|||Cornelius,
I am sorry that this is still not working. When you say you are using a dimension formula, can you be more specific. Are you using a "custom rollup column" or are you using a MDX statement in the cube script? I am curious as to the source of this problem and if possible it would be helpful if you could email me a copy of the project files.
spontello@.proclarity.com
- Steve
|||Thanks to Steve there is a solution.
When using custom member formulas (what I called dimension formulas) there is no way to override the default aggregation behaviour of the cube. That issue is discussed in a previous post listed here:
http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_thread/thread/885149d85c381cb0/a0bc768a4066d4fe?q=custom+rollup+division&rnum=2#a0bc768a4066d4fe
The solution here is the ussage of calculated members.
But my calculations are regarding to members in an account dimension. In this account dimension I had declard properties like custom_caption, custom_style or custom_format I use in my client application.
On calculated members I cannot declare these properties.
So the next issue was to join an accurate calculation to the members in the account dimension. For this issue Steve proposed the ussage of calculated cells.
Using the Calculated Cells Wizard you can define a MDX Expression for the calculation and specify a specific member (in my case from the account dimension) to apply the calculation to.
It works wonderful.
No comments:
Post a Comment