case statement not working in DQM

[xyz]

Hi Gurus,

Can you guys help me on the performance improvement for master detail, as this is high priority and I could not get help any where about the performance improvement for master detail style reports running against SSAS cube data source.

To improve the performance of this type of reports, we have been communicated by IBM support and Cognos experts to go for DQM. We have configured and published the package with DQM.

Now the problem I am facing is DQM is erroring out for Case statement condition used in one of my dataitem.
Does any one know, what could be the alternative for case statement. I assume DQM is pushing MDX to the query engine, which is not allowing case statement.




Thanks & Regards,
xyz

[navissar]

Basically, case statements shouldn't be used over dimensional, because they do not exist in MDX. There are ways to achieve almost anything you need without a case statement.
Let's give it a go: What is the logic you're trying to create with the case statement?

[xyz]

Thanks for Nimrod Avissar,

I have a prompt with name as 'Org Level' which is coming from [Org Hierarchy], in this prompt I have to display unique 'level names', as [Org Hierarchy] has multiple members at the same level, I used case statement to get unique level names.

Example:[Org Hierarchy] data below

X - Level 1
A -- Level 2
B -- Level 2
C --- Level 3
Y - Level 1
D --Level 2
E -- Level 2

If I use roleValue('_levelLabel', [Org Hierarch]) then when I try to display in the 'Org Level' prompt then I will get 'Level 1' display twice, 'Level 2' four time and 'Level 3' one time, but whereas I have to display every level one time only like

Level 1
Level 2
Level 3

So the data item1 I created is roleValue('_levelLabel', [Org Hierarch]), on top of that I created one more data Where I have used case statement like below

Case When data item1 = 'Level 1'
Then 'Level 1'
When data item1 = 'Level 2'
then 'Level 2'
When data item1 = 'Level 3'
then 'Level 3'
End

I used case even though there are multiple level members exist for the same members also then it will show only once.

Thanks & Regards,
xyz

[MFGF]

An easy approach would be to define a prompt with static choices. Manually enter three choices with "Use Value" set as the LUN (level unique name) of each level, and "Display Value" set to "Level 1", "Level 2" and "Level 3"

I'm willing to bet your levels will never change - if they do, all manner of reports will breal because the MUNS will have changed, so this should be a one-time thing to set up.

Cheers!

MF.

[navissar]

I like MF's proposal. It's good and it'll work. Nevertheless, let's try to look at the bigger picture: you're describing performance issues, and you're using DQM in order to improve that. That's good. But I don't think you're fully utilizing DQM, and from what you've shared, I don't think your cube is optimal.
I'll elaborate: Suppose your user picks a level (Let's assume they pick level 2). Now, that level is an aggregate of all instances of level 2. Each measure you display aggregates on the fly all Level 2 instances. Also, DQM caches in dimension information. The information that will be cached is the low-level data in your case, all the instances of all the levels.
So, the correct way to go about this, if I understand your data correctly, is to add a "Level" level to your hierarchy, Like this:
Parent: Level 2
Children: A-Level2, B-Level2, D-Level2,E-Level2
And so on. This way, not only will you have a unique level for your prompt, but also you will have the data aggregating to a clear, unique "Level" level when the cube is built, and DQM will be able to cache all your levels.
So, if I got your data correctly, my advice would be to change the Org Hirarchy and add a unique "Level" level.

Good luck!

[xyz]

Thanks MF and Nimrod Avissar,

MF thanks for the suggestion, but I can't go with the approach you suggested because of my requirement. Please find my requirement in this email below.

Nimrod, thanks for your suggestion on prefixing the level name to each member of the hierarchy, I am not sure this is feasible for asking our onsite counter parts at this point of time. Let me explain you the requirement clearly below.

Actual requirement is, report is having two prompts on the same hierarchy i.e. [Org_Hierarchy_Tree]. Where as [Org_Hierarchy_Tree] hierarchy have levels like Org level1, Org level2, Org level3, Org level4, Org level5, Org level 6 and Org_Hierarchy, overall seven levels in it. First prompt they are calling it as 'Org Unit', in which they want to display only members information and in second prompt, which they call it is as 'Org Level', it should display only level names below selected level in first prompt i.e. 'Org Unit'.

Say suppose in 'Org Unit' first prompt below, If I am choosing value 'India', which is level 2 member, 'Org Level' second prompt should start from 'Org Level 3' only and should show only level names. Subsequently user can choose Org Level 4 or Org Level 5 or Org Level 6 in 'Org Level' prompt.

    Org Unit        Org Level
   Org Level 1     
X India     
   Org Level 3  XOrg Level 3
   Org Level 4    Org Level 4
   Org Level 5    Org Level 5
   Org Level 6    Org Level 6

To achieve the above requirement from first prompt, I used Descendants function and roleValue function and Case statement.

I hope, my requirement is clear, can you guys please tell me how will I go about it, which will improve the performance.

Thanks & Regards,
xyz