May 19, 2018 at 4:33 am
Hi All,
I am facing a problem while querying on hierarchical data from cube.The problem area is nth level is having duplicate, in case of below example when querying on dummyCube, Level 4 which is nth level in hierarchy of Levels dimension. below is the illustration of Cube, Dimension.
With below query i get only $100 value in output whereas I expect $300.
Cube Structure :-
DimLevels - LevelID, Level1, Level2, Level3, Level4
Fact- LevelID, Amount
Sample Data:-
1 > Direct > Component > Hvac > Fans > $100
2 > Direct > Electronic > Motor > Fans > $200
Query being used:
WITH SET TopPodData AS TOPCOUNT
( [dimLevels].[hr_Levels].[Level 1], 10, [Measures].[Amount])
SELECT NON EMPTY { ORDER( { TopPodData }, ([Measures].[Amount]), BDESC) } on Rows,
{ [Measures].[Amount] } on Columns
FROM (
SELECT ({[dimDate].[hr_Date].[YEAR].&[2018]},
{
[dimLevels].[hr_Levels].[Level 3].[HVAC], [dimLevels].[hr_Levels].[Level 4].[FANS]
}
) on COLUMNS from [dummyCube]
)
May 20, 2018 at 7:03 am
From the above, I'm guessing that your attribute relationships also follow the same structure as your hierarchy. If my assumption is correct, then your query will always return two rows for the "Fans" attribute, because it is honoring the structure you have created.
The question is whether you're just trying to get a total for "Fans" in your query, or whether it is a business requirement to enable end users to select an element at any level and get the total without it being split by the hierarchy? The way you approach the solution would depend on exactly what you are trying to achieve.
May 20, 2018 at 11:56 pm
Martin Schoombee - Sunday, May 20, 2018 7:03 AMFrom the above, I'm guessing that your attribute relationships also follow the same structure as your hierarchy. If my assumption is correct, then your query will always return two rows for the "Fans" attribute, because it is honoring the structure you have created.The question is whether you're just trying to get a total for "Fans" in your query, or whether it is a business requirement to enable end users to select an element at any level and get the total without it being split by the hierarchy? The way you approach the solution would depend on exactly what you are trying to achieve.
Hello Martin,
Yes attribute relationship also follows the same structure. The query is returning 2 rows, however if I pull only Level1 along with measure the total for "Fans" is shows only for one value not the summation of both rows.
May 21, 2018 at 8:28 pm
The query is behaving as expected. You are returning both Level 3 and Level 4 attributes on the query axis, and because your attribute relationships are configured the way they are it will return 2 rows because Level 3 contains 2 different values for "Fans".
If you want to filter on "Fans", you should move the condition to a where-clause in your query and not the axis of the results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply