Report Performancre Issue:Dynamic Columns for a DMR Crosstab Report

[raj_aries81]

Hi All,

I have created a dynamic crosstab report. Users should be able to select the required dimensions & measures that needs to be displayed for columns, rows and  measures.

There are around 15-20 dimensions and 40+ measures. For hiding & showing the columns in the crosstab, I have created boolean variables so that whenever the dimension is not selected it is hidden by changing the left & right padding to 0 and removed the borders for it.Also, I have created prompt macros for the each dataitem for eg: case when 'CUSSEG' in (#promptmany('Rows','Char','')#) then (nvl([xxx].[yyy].[Customer Segment],''))

There are around 90 style variables that I had to created based on the users' dynamic selection from the prompt page. The problem here is, report is taking tooo long to run. I presume this is due to the number of variables that are created in the report.

Please let me know if there is a way to accomplish this dynamic column selections in crosstab without using so many style variables.

Thanks & Regards
Raj

[navissar]

Performance would be horrible. You're basically nesting n layers, and nesting on its own is quite hurtful.
Let's think outside the box: Why don't you create a DMR/transformer cube, and allow users access to analysis studio or workspace advanced, to achieve the same functionality of allowing users to populate their own crosstabs?

[raj_aries81]

Hi Nimrod,

We too have suggested to go with a cube based approach.However, there are existing SAS reports that has this dynamic column selection hence end users are insisting on having the same functionality in Cognos reports too.

Regards
Raj

[navissar]

Right, and then the users can also have SAS perform drill through between different data sources. Oh, wait a minute, they can't, because what do you know - this isn't the same tool.
Anyway, going with a self-service module such as analysis services or workspace advanced would give the users much more flexibility, better performance and the ability to do more simpler. It may not be what SAS is offering - it's better than the functionality you describe in many ways -  but I'm pretty sure your client didn't pay hundreds of thousands of dollars for Cognos just to replicate exactly what a software they already had did.

[raj_aries81]

I'm pretty sure your client didn't pay hundreds of thousands of dollars for Cognos just to replicate exactly what a software they already had did.

Thanks Nimrod.

Yes thats what we tried to explain 'em  by building some POCs and showcasing the slicing & dicing features that are more advanced  :). Unfortunately, users were so adamant that they wanted to have something similar and started questioning the Cognos Vs SAS capability.

The problem with crosstab is when we hide some column left shift occurs. To avoid this am padding the the column that is not selected.

Regards
Raj

[CognosPaul]

The thing with creating crosstabs with dynamic columns is that the hidden columns are still being processed in the query. Hiding an object with a style or render variable will only prevent the query from running if the entire object is hidden.

So you're essentially instructing Cognos to run a query over all 20 dimensions * 40 measures.

While the correct response for demanding "dynamic columns" should be showing off analysis or query studio (you really can't get much more dynamic than that), there are always work-arounds. Basically, Cognos can do anything if given enough time and money.

Have you seen this thread? http://www.cognoise.com/index.php/topic,10171.0.html

That's based on a powercube, but it's the same idea.

[raj_aries81]

Thanks Paul :)

Further to that, users also want to see multi-select static choices like (Sum, Avg, Max...etc) for measures. Yes, I certainly feel that its better go with Analysis Studio.

Thanks & Regards
Raj

[raj_aries81]

Paul,

I don't see a way to apply rendering for the crosstab columns. If I hide any crosstab column it results in a left shift Hence, I am using Style variable and then setting the left & right padding property of the column to 0. Is there any alternate approach.

Regards
Raj

[CognosPaul]

Are these nested columns? If so, then yeah, there will be an issue with that. If not, are you hiding the node in the crosstab as well?

[Lynn]

Thanks Paul :)

Further to that, users also want to see multi-select static choices like (Sum, Avg, Max...etc) for measures. Yes, I certainly feel that its better go with Analysis Studio.

Thanks & Regards
Raj

Cognos Workspace Advanced (formerly Business Insight Advanced) would be a better option than Analysis Studio IMHO.

[raj_aries81]

Are these nested columns? If so, then yeah, there will be an issue with that. If not, are you hiding the node in the crosstab as well?

Yes..there are nested columns. :( . DBA says that there isn't an DB issue. I am trying to explain him that its not about the data or database but the issue is bcz of too many style variable that are being applied

[raj_aries81]

Cognos Workspace Advanced (formerly Business Insight Advanced) would be a better option than Analysis Studio IMHO.

Thanks Lynn.. I need to go back and explain them about this approach :)