October 7, 2016 at 3:07 am
Hi! I'm new with this howl MDX thing and cant seem to figure out how to make the following work.
I have a few Calculated measure that calculates a value based on the line related to the rest of the data.
eg. The total contribution of sales a single store has to the total of all stores.
Here are 2 Expressions that makes this posible:
- ([Measures].[Total] / MAX([Store].[Store ID].ALLMEMBERS,[Measures].[Total])
- ([Measures].[Total] / SUM([Store].[Store ID].[All],[Measures].[Total])
The problem I'm having is that my slicers do not affect the results.
In other words:
Lets say a store has a 10% contribution with regards to the total of 100 store (all stores).
When filtered by region (for example) it lowers the total number of store to 50, but the contribution still shows 10%. I need the contribution to be relative to the 50 store not the 100.
For some reason the date seems to work, but this is probably because it does not affect the axis.
I'm not sure how the cube (built in SSAS) construct the query behind the scenes but I would imagine it looking something like this:
WITH
MEMBER
[Measures].[%GT Total]
AS
(
[Measures].[Total] / MAX([Store].[Store ID].ALLMEMBERS,[Measures].[Total])
--[Measures].[Total] / SUM([Store].[Store ID].[All],[Measures].[Total])
),
FORMAT="Percent"
SELECT
{[Measures].[Total],[Measures].[%GT Total]} ON AXIS(0)
,NON EMPTY{[Store].[Store ID].CHILDREN} ON AXIS(1)
FROM
[RMS_BISale]
where
[Store].[Division].[Division ID].[Wholesale]
another example will be the Store Average which is also not affected by slicers:
- (AVG([Store].[Store ID].[Store ID].members, [Measures].[Total]))
or the region division total of a store... they all seem to have the same problem.
Any help\guidance will be very much appreciated.
October 7, 2016 at 3:28 am
BlackRabbit (10/7/2016)
Hi! I'm new with this howl MDX thing and cant seem to figure out how to make the following work.I have a few Calculated measure that calculates a value based on the line related to the rest of the data.
eg. The total contribution of sales a single store has to the total of all stores.
Here are 2 Expressions that makes this posible:
- ([Measures].[Total] / MAX([Store].[Store ID].ALLMEMBERS,[Measures].[Total])
- ([Measures].[Total] / SUM([Store].[Store ID].[All],[Measures].[Total])
The problem I'm having is that my slicers do not affect the results.
In other words:
Lets say a store has a 10% contribution with regards to the total of 100 store (all stores).
When filtered by region (for example) it lowers the total number of store to 50, but the contribution still shows 10%. I need the contribution to be relative to the 50 store not the 100.
For some reason the date seems to work, but this is probably because it does not affect the axis.
I'm not sure how the cube (built in SSAS) construct the query behind the scenes but I would imagine it looking something like this:
WITH
MEMBER
[Measures].[%GT Total]
AS
(
[Measures].[Total] / MAX([Store].[Store ID].ALLMEMBERS,[Measures].[Total])
--[Measures].[Total] / SUM([Store].[Store ID].[All],[Measures].[Total])
),
FORMAT="Percent"
SELECT
{[Measures].[Total],[Measures].[%GT Total]} ON AXIS(0)
,NON EMPTY{[Store].[Store ID].CHILDREN} ON AXIS(1)
FROM
[RMS_BISale]
where
[Store].[Division].[Division ID].[Wholesale]
another example will be the Store Average which is also not affected by slicers:
- (AVG([Store].[Store ID].[Store ID].members, [Measures].[Total]))
or the region division total of a store... they all seem to have the same problem.
Any help\guidance will be very much appreciated.
I think what you need to be doing is a calculation of the current member of your slicer against the parent of your current member.
So your Percent of Total calculation will become:
WITH
MEMBER
[Measures].[%GT Total]
AS
//This case makes it 100% if it is the "All" member
CASE WHEN [Store].[Store ID].CURRENTMEMBER.LEVEL IS [Store].[Store ID].[All]
THEN 1
ELSE
([Store].[Store ID].CURRENTMEMBER, [Measures].[Total]) / ([Store].[Store ID].CURRENTMEMBER.PARENT, [Measures].[Total])
END,
FORMAT_STRING="Percent"
You will need to put a division by zero check in there or use DIVIDE(([Store].[Store ID].CURRENTMEMBER, [Measures].[Total]) , ([Store].[Store ID].CURRENTMEMBER.PARENT, [Measures].[Total])) if you are on 2012 or later.
October 7, 2016 at 5:41 am
Thank you for the fast reply!
I tried what you suggested, the Axis show right but the values show #Error.
Error:
The Is function expects a level expression for the 2 argument. A member expression was used.
October 7, 2016 at 5:58 am
If I remove .LEVEL it does not give an error, but I get the same results as before. Where the slicer has no effect.
WITH
MEMBER
[Measures].[%GT Total]
AS
//This case makes it 100% if it is the "All" member
CASE WHEN [Store].[Store ID].CURRENTMEMBER IS [Store].[Store ID].[All]
THEN 1
ELSE
DIVIDE(([Store].[Store ID].CURRENTMEMBER, [Measures].[Total]) , ([Store].[Store ID].CURRENTMEMBER.PARENT, [Measures].[Total]))
END,
FORMAT_STRING="Percent"
October 10, 2016 at 1:20 am
The issue is that there is no parent-child relationship between [Store ID] (child) and [Division ID] (parent). So when you are saying [Store].[Store ID].CURRENTMEMBER.PARENT what you are returning is [Store].[Store ID].[All] so the percentage will always be of the grand total. To resolve this you can create a hierarchy in your cube and then you can traverse this properly in your query to produce correct results.
Or.....
You could divide the currentmember of the Store ID and the currentmember of the [Division ID]. This is an example using AdventureWorks (so in this scenario I guess City would be your Store ID):
WITH
MEMBER [% of Total]
AS
CASE WHEN [Geography].[City].CURRENTMEMBER IS [Geography].[City].[All Geographies] THEN 1
ELSE
DIVIDE(([Geography].[City].CURRENTMEMBER, [Measures].[Reseller Order Quantity]),
([Geography].[Country].CURRENTMEMBER, [Measures].[Reseller Order Quantity]))
END,
FORMAT_STRING="Percent"
SELECT
{[Measures].[Reseller Order Quantity], [% of Total]} ON 0,
NON EMPTY
[Geography].[City].[City] ON 1
FROM
[Adventure Works]
WHERE
[Geography].[Country].&[Australia]
I'd go with creating a hierarchy in the cube though as it'll make your life easier going forward as you could make this % of total calculation dynamic.
October 10, 2016 at 5:13 am
OK cool, things are starting to move in the right direction 🙂
I have 2 hierarchies in the cube related to the store:
1. Division
- Division ID
- Store ID
2. Region
- Country ID
- Region ID
- Store ID
The results are starting to make more sense using the correct hierarchy with the right slicer:
[Store].[Region] or [Store].[Division]
However, if there are no "filters" applied or the data is "filtered" by Division when [Store].[Region] is used, the value still does not adjust.
So my question is: Is it possible (or what is needed) to get the following, to allow for more flexible filtering.
- If no filter: shows % of the grand total.
- If Region filter: shows % of the filtered total.
- If Division filter: shows % of the filtered total.
- If Region and Division: shows % of the filtered total.
The Slicers requested by the user:
Calendar <- Working
- Year
- Qtr
- Month
- Day
Division
- Division
- Store
Region
- Country
- Region
- Store
On a side note: I added an attachment of the cube structure I'm testing with to the original post.
October 10, 2016 at 5:38 am
BlackRabbit (10/10/2016)
OK cool, things are starting to move in the right direction 🙂I have 2 hierarchies in the cube related to the store:
1. Division
- Division ID
- Store ID
2. Region
- Country ID
- Region ID
- Store ID
The results are starting to make more sense using the correct hierarchy with the right slicer:
[Store].[Region]or[Store].[Division]However, if there are no "filters" applied or the data is "filtered" by Division when [Store].[Region] is used, the value still does not adjust.
So my question is: Is it possible (or what is needed) to get the following, to allow for more flexible filtering.
- If no filter: shows % of the grand total.
- If Region filter: shows % of the filtered total.
- If Division filter: shows % of the filtered total.
- If Region and Division: shows % of the filtered total.
The Slicers requested by the user:
Calendar <- Working
- Year
- Qtr
- Month
- Day
Division
- Division
- Store
Region
- Country
- Region
- Store
On a side note: I added an attachment of the cube structure I'm testing with to the original post.
Okay, so reading between the lines here I think what you are trying to do is create a calculation on the cube itself that dynamically displays the percentage of parent total and not just fix one query. Is that correct?
It also would be useful to know what the users are using to look at this data and how they are looking at this data. Are they browsing the cube ad-hoc with a client such as excel or are they consuming pre-written reports using something like SSRS?
It makes a difference as cube browser clients construct MDX in different ways.
So just assuming that the answer to my first question is affirmative then you could use the original currentmember / currentmember.parent pattern and create a dynamic calculation along these lines:
IIF(
ISEMPTY( Axis(1).Item(0).Item(0).Dimension.CurrentMember.Parent )
,1
,DIVIDE([Measures].[Your Measure], ( Axis(1).Item(0).Item (0).Dimension.CurrentMember.Parent, [Measures].[Your Measure] ))
)
This calculation will only ever work in the context of the hierarchy that you have on axis 1. Adding a slicer will affect what is displayed but it will not affect the calculation unless the slicer is a part of the hierarchy on Axis 1, which you can't do anyway (you'd have to create a set in your query to do this).
Perhaps for what you are trying to do it would be easier to define this calculation in the presentation layer?
October 11, 2016 at 8:01 am
Division and Region are both dimensions and Hierarchies.
So seeing the actual MDX being generated will be helpful.
October 11, 2016 at 8:46 am
PB_BI (10/10/2016)
Okay, so reading between the lines here I think what you are trying to do is create a calculation on the cube itself that dynamically displays the percentage of parent total and not just fix one query. Is that correct?It also would be useful to know what the users are using to look at this data and how they are looking at this data. Are they browsing the cube ad-hoc with a client such as excel or are they consuming pre-written reports using something like SSRS?
It makes a difference as cube browser clients construct MDX in different ways.
So just assuming that the answer to my first question is affirmative then you could use the original currentmember / currentmember.parent pattern and create a dynamic calculation along these lines:
IIF(ISEMPTY( Axis(1).Item(0).Item(0).Dimension.CurrentMember.Parent )
,1
,DIVIDE([Measures].[Your Measure], ( Axis(1).Item(0).Item (0).Dimension.CurrentMember.Parent, [Measures].[Your Measure] ))
)
This calculation will only ever work in the context of the hierarchy that you have on axis 1. Adding a slicer will affect what is displayed but it will not affect the calculation unless the slicer is a part of the hierarchy on Axis 1, which you can't do anyway (you'd have to create a set in your query to do this).
Perhaps for what you are trying to do it would be easier to define this calculation in the presentation layer?
The user used PowerBI to build and view reports, but the file size limitation became a problem, now they want an alternative solution that will give them the same reports.
Currently using an In-house dashboard viewer\designer created using the BI DevExpress plugin. Unfortunately it does not seem to have the built in "Contribution to Total" function as in Power BI. I was then asked to see if it can be done in the cube using mdx.
Yes, you have the right idea. At this point the user is consuming pre-written reports, they just slice using the slicers provided.
Will try the above suggestion and provide feedback.
Someone also suggested I should have a look at SCOPE, but not sure if that will work in this case.
Will also see if I can set up a trace to grab the mdx queries that a executed agents the cube.
Side note: We are looking at other solutions in terms of cube browser clients.
October 13, 2016 at 1:38 am
BlackRabbit (10/11/2016)
The user used PowerBI to build and view reports, but the file size limitation became a problem, now they want an alternative solution that will give them the same reports.Currently using an In-house dashboard viewer\designer created using the BI DevExpress plugin. Unfortunately it does not seem to have the built in "Contribution to Total" function as in Power BI. I was then asked to see if it can be done in the cube using mdx.
Yes, you have the right idea. At this point the user is consuming pre-written reports, they just slice using the slicers provided.
Will try the above suggestion and provide feedback.
Someone also suggested I should have a look at SCOPE, but not sure if that will work in this case.
Will also see if I can set up a trace to grab the mdx queries that a executed agents the cube.
Side note: We are looking at other solutions in terms of cube browser clients.
I tried using a variant of the above suggested code, but I got the same results.
Changed from a pivot table to a normal grid in the DevExpress plugin to try and simplify the logic, before using SQLProfiler to get the below mdx query.
select
non empty { { [Store].[Store ID].[All], { {[Store].[Store ID].[Store ID].members}} } * { [Measures].[Total], [Measures].[%GT Total]}} dimension properties MEMBER_VALUE on columns
from (
select
{[Store].[Region].[Store ID].&[66], [Store].[Region].[Store ID].&[67], [Store].[Region].[Store ID].&[69], [Store].[Region].[Store ID].&[70], [Store].[Region].[Store ID].&[71], [Store].[Region].[Store ID].&[72], [Store].[Region].[Store ID].&[73]} on columns
from (
select
{descendants({[Store].[Division].[Division ID].members} - { {[Store].[Division].[Division ID].&[4], [Store].[Division].[Division ID].&[1], [Store].[Division].[Division ID].&[3]}} , [Store].[Division].[Store ID], LEAVES) - { {[Store].[Division].[Store ID].&[12], [Store].[Division].[Store ID].&[74], [Store].[Division].[Store ID].&[76], [Store].[Division].[Store ID].&[77], [Store].[Division].[Store ID].&[78]}} } on columns
from [RMS_BISale]
)) CELL PROPERTIES VALUE, FORMAT_STRING , LANGUAGE
The table consists of 3 columns: Store, Total and the calculated measure %GT Total. And is sliced by Region(Hierarchy) and Division(Hierarchy).
October 13, 2016 at 2:29 am
BlackRabbit (10/13/2016)
BlackRabbit (10/11/2016)
The user used PowerBI to build and view reports, but the file size limitation became a problem, now they want an alternative solution that will give them the same reports.Currently using an In-house dashboard viewer\designer created using the BI DevExpress plugin. Unfortunately it does not seem to have the built in "Contribution to Total" function as in Power BI. I was then asked to see if it can be done in the cube using mdx.
Yes, you have the right idea. At this point the user is consuming pre-written reports, they just slice using the slicers provided.
Will try the above suggestion and provide feedback.
Someone also suggested I should have a look at SCOPE, but not sure if that will work in this case.
Will also see if I can set up a trace to grab the mdx queries that a executed agents the cube.
Side note: We are looking at other solutions in terms of cube browser clients.
I tried using a variant of the above suggested code, but I got the same results.
Changed from a pivot table to a normal grid in the DevExpress plugin to try and simplify the logic, before using SQLProfiler to get the below mdx query.
selectnon empty { { [Store].[Store ID].[All], { {[Store].[Store ID].[Store ID].members}} } * { [Measures].[Total], [Measures].[%GT Total]}} dimension properties MEMBER_VALUE on columns
from (
select
{[Store].[Region].[Store ID].&[66], [Store].[Region].[Store ID].&[67], [Store].[Region].[Store ID].&[69], [Store].[Region].[Store ID].&[70], [Store].[Region].[Store ID].&[71], [Store].[Region].[Store ID].&[72], [Store].[Region].[Store ID].&[73]} on columns
from (
select
{descendants({[Store].[Division].[Division ID].members} - { {[Store].[Division].[Division ID].&[4], [Store].[Division].[Division ID].&[1], [Store].[Division].[Division ID].&[3]}} , [Store].[Division].[Store ID], LEAVES) - { {[Store].[Division].[Store ID].&[12], [Store].[Division].[Store ID].&[74], [Store].[Division].[Store ID].&[76], [Store].[Division].[Store ID].&[77], [Store].[Division].[Store ID].&[78]}} } on columns
from [RMS_BISale]
)) CELL PROPERTIES VALUE, FORMAT_STRING , LANGUAGE
The table consists of 3 columns: Store, Total and the calculated measure %GT Total. And is sliced by Region(Hierarchy) and Division(Hierarchy).
The issue is still with the attributes that are being selected.
The parent of [Store].[Store ID].[Store ID] is [Store].[Store ID].[All].
The [Store].[Store ID] attribute has nothing to do with the [Store].[Division].[Store ID] attribute or the [Store].[Region].[Store ID] attribute in terms of familial relationships because they are in different hierarchies.
So if you encapsulate the [Attribute].CURRENTMEMBER / [Attribute].CURRENTMEMBER.PARENT calculation paradigm in one hierarchy, for example, [Store].[Region] then the [Store].[Region].[Store ID] members will show the percentage of the [Store].[Region] total. If you apply the calculation to the [Store].[Store ID].[Store ID] attribute then it'll give you the percentage of the (effective) grand total, in this case [Store].[Store ID].[All].
What you could do is only allow the calculation on attributes that make sense and return null otherwise. You can achieve this with a SCOPE statement.
Something like
SCOPE ([Store].[Store ID].MEMBERS, [Measures].[%GT Total]);
THIS = NULL;
END SCOPE;
Or use SCOPE to only apply the calculation to the hierarchies that you need it to apply to. Something like
CREATE MEMBER CURRENTCUBE.[Measures].[%GT Total]
AS NULL,
FORMAT_STRING="Percent",
VISIBLE=1;
SCOPE ([Measures].[%GT Total]);
SCOPE ([Store].[Division].MEMBERS);
THIS = DIVIDE(([Store].[Division].CURRENTMEMBER, [Measures].[Total]), (([Store].[Division].CURRENTMEMBER.PARENT, [Measures].[Total]));
END SCOPE;
SCOPE ([Store].[Region].MEMBERS);
THIS = DIVIDE(([Store].[Region].CURRENTMEMBER, [Measures].[Total]), (([Store].[Region].CURRENTMEMBER.PARENT, [Measures].[Total]));
END SCOPE;
END SCOPE;
Or something along those lines.
October 13, 2016 at 10:05 am
You might also look and see if this[/url] will help with the file size limit.
Part of the issue that you might want to consider - user created report, an you are trying to replicate logic using more complex mdx.
So will users be able to apply this to another view they come up with?
When I look at Store being the level of display, Division Hierarchy goes directly to store, while Region Hierarchy does not include Division.
Which is the disconnect mentioned.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply