SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculated measure based on dimension attribute repeats


Calculated measure based on dimension attribute repeats

Author
Message
msmithson
msmithson
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 578
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. Blink
msmithson
msmithson
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 578
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. Blink


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' ;


w00t
Mackers
Mackers
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 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
msmithson
msmithson
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 578
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


Blink

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.

Blink
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search