July 21, 2010 at 6:55 am
Hello,
I have a problem with a Cube in SSAS 2005. I will describe my situation first.
I have a cube, which is connected to 4 dimensions. In the cube I have defined a measure "Wert" and I have set the AggregateFunction = None, because the values in this measure cannot be aggregated across any dimension.
The following data is in my fact table
DatumID: 2009121601
KennzahlID: 2
Wert: 70731448,3251
OrganisationID: 6
Risikoart: 1
I execute the following MDX:
SELECT
{ [Measures].[Wert] } ON COLUMNS
FROM [MarGeAdditiv]
WHERE ( [Organisation].[Dim Organisation].&[6],
[Risikoart].[Dim Risikoart].&[1],
[Datum].[DatumID].&[2009121601],
[Kennzahl].[Kennzahl].&[2])
It returns NULL. All attributes that I select in the MDX are dimension keys and also the granularity attribute of the measure "Wert". Shouldn't the query return the actual value I have in my database?
What am I doing wrong? What properties should I check?
All of the dimensions contain parent/child hierarchies, just for information, I don't see how this could influence the result, though.
ANY help is appreciated, as I have searched the the net and have not found a solution to this.
Nicolas
July 21, 2010 at 7:23 am
More Info: It seems to be an issue with Parent-Child hierarchy, because when I delete it it works. But why? And what properties would I have to set on the hierarchy in order to make it work?
July 31, 2010 at 8:14 am
Hi nmeseth,
OK first: you are right that it has everything to do with parent-child dims.
Quick answer - try the following: put .DATAMEMBER after members that belong to parent-child dimensions like this
SELECT
{ [Measures].[Wert] } ON COLUMNS
FROM [MarGeAdditiv]
WHERE ( [Organisation].[Dim Organisation].&[6].DATAMEMBER,
[Risikoart].[Dim Risikoart].&[1].DATAMEMBER,
[Datum].[DatumID].&[2009121601],
[Kennzahl].[Kennzahl].&[2].DATAMEMBER)
Explanation: Some or all of the members of the parent-child dimensions that you were filtering on are not leaf members of the parent-child dimension, but instead have children. So even though this query translated to T-sql would result in a single cell, because of the hierarchical structures you are actually querying data for that particular member AND its descendants. For additive measure a defined measure aggregation would aggregate this data and return the resulting value, but since this is a non additive measure it simply yields null.
And there comes .DATAMEMBER - it allows you to slice the cube on a non-leaf member of the parent-child dimension to return only fact rows that have this member key.
You can also test by specifying function leaves(<parent-child dim>) on columns or rows.
Hope it helped and kind regards,
Hrvoje Piasevoli
Hrvoje Piasevoli
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply