Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculated measure based on dimension attribute repeats Expand / Collapse
Author
Message
Posted Thursday, January 31, 2013 6:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:41 AM
Points: 493, Visits: 548
I'm new to SSAS so bear with me.

I have a calculated measure based on an attribute in one of my dimensions. The calculation looks like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Office Visits]
AS sum(EXISTING({[Location].[Place of Service].&[11]}), [Measures].[Encounters]),
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Encounters] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Encounters'

It calculates correctly when I browse by Place of Service but when I try to browse by another attribute of that Location dimension "LocationName" then the calculated measure value just repeats for each LocationName. I'm sure this must have something to do with attribute relationships or the hierarchy but I just don't understand the behavior here.
Post #1414412
Posted Thursday, February 07, 2013 10:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:41 AM
Points: 493, Visits: 548
msmithson (1/31/2013)
I'm new to SSAS so bear with me.

I have a calculated measure based on an attribute in one of my dimensions. The calculation looks like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Office Visits]
AS sum(EXISTING({[Location].[Place of Service].&[11]}), [Measures].[Encounters]),
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Encounters] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Encounters'

It calculates correctly when I browse by Place of Service but when I try to browse by another attribute of that Location dimension "LocationName" then the calculated measure value just repeats for each LocationName. I'm sure this must have something to do with attribute relationships or the hierarchy but I just don't understand the behavior here.


Well after re-reading a chapter on MDX scripting and cube navigation I learned enough to figure out what was happening. This calculation was aggregating at the [Place of Service] level of the hierarchy but not the children. Changed it to this and it worked:


CREATE MEMBER CURRENTCUBE.[Measures].[Office Visits]
AS sum(EXISTING({([Location].[Place].[Place Of Service].&[11].Children)}, [Measures].[Encounters])),
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Encounters] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Encounters' ;





Post #1417214
Posted Friday, February 08, 2013 5:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Hi,

That's fine if you are writing all the MDX yourself but not ideal if you are using an ad-hoc query tool (like pivot tables in Excel or ProClarity or XLCubed etc)

You might find it easier to push the data down to the lowest level of your hierarchy

For example my company budgets at sales team but the lowest level is salesperson. I then push the budget down to and aribitary salesperson in that team and apply the budget to them (or you could split the budget evenly).

I appreciate that this is not a true representation of the data but it allows correct rollups & ad-hoc query tools will work correctly

Mack
Post #1417624
Posted Friday, February 08, 2013 9:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:41 AM
Points: 493, Visits: 548
Mackers (2/8/2013)
Hi,

That's fine if you are writing all the MDX yourself but not ideal if you are using an ad-hoc query tool (like pivot tables in Excel or ProClarity or XLCubed etc)

You might find it easier to push the data down to the lowest level of your hierarchy

For example my company budgets at sales team but the lowest level is salesperson. I then push the budget down to and aribitary salesperson in that team and apply the budget to them (or you could split the budget evenly).

I appreciate that this is not a true representation of the data but it allows correct rollups & ad-hoc query tools will work correctly

Mack




The calculated measure is now aggregating at the lowest level of the hierarchy so I am not sure what you mean. Also, it works just fine in Excel.


Post #1417789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse