Global Query calculation

[sanjay0828]

Hi,

I am using Cognos 8 Report Studio against an SSAS backend

Is it possible to create a Query calculation in, maybe, the framework manager, so that all the users who use the package can see it.

Also, is it possible to write the Query Calculation using my own MDX or can I write it only using the functions supplied by Cognos?

If there is any way I can include my own MDX into the Report, then it would be great.

Thanks,
Sanjay

[david.stachon]

You can't build a SSAS calculation in FM because it has no awareness of the SSAS cube.

If you want a calculation available to everyone, build it in your cube. SSAS has formidable capability in this respect....then it will be available to everyone including non cognos users (i.e. excel users, or other SSAS interfaces)

Yes, you CAN code MDX in report studio....I would think that it would be a rare occasion that you would need to. The MDX constructs that Report Studio build with a GUI are awesome (especially in 8.4)

...to create your own MDX however; in your Query explorer, just drag over the "MDX" object from the insertable objects pane and start coding.

(note: it's my personal opinion, that if you're requiring to code things like this, you've bought the wrong tool. cognos can support this type of development, but its paradigm is high productivity and configure versus code)

[sanjay0828]

Hi David,

Thanks for the reply.

This is my need :-

I need to create the followinng named set within Cognos :-
NonEmpty([Product].[Product].members)

I cannot create this in SSAS because in SSAS (2005), the named sets are created as static
i.e. the above named set is evaluated before the query.

But, what I want is depending on my current selections, the set returned by the named set should vary.

The only way to do this is to inject this code into the client tool.

Hope I am making my point clear.

The equivalent MDX generated by Cognos for the above named set is not too optimised.
This MDX takes 18 minutes to execute.

Whereas, creating the named set as above and using it in a query takes only 2 minutes.

[david.stachon]

interesting.

what version of cognos are you on?

...are you sure the same can't be accomplished by using zero suppress (8.4 only)?

[sanjay0828]

The version is 8.3

[david.stachon]

If possible, I would try and get a hold of 8.4 and see if it helps any....(the zero suppress options are just fantastic....by rows or columns or both, 0's, nulls, divide by zero errors and overflow)

The way I see it, the upgrade to 8.4 from 8.3 is trivial, and if it prevents you from having to go down a path of coding many reports with custom MDX it's well worth it.

[CognosPaul]

You can mimic the zero suppress option by using the following:

filter(members([Cube].[Hierarchy].[Level]), [Cube].[Measures].[Measure] is not null)

The Measure parameter can also be a tuple:

filter(members([Cube].[Hierarchy].[Level]), tuple([Cube].[Measures].[Measure],[Cube].[Hierarchy].[Level].[Member]) is not null)

There are times when it is more efficient to use filters instead of, or in conjunction with, the Cognos Zero Suppress. Specifically, if you have a huge level (> ten of thousands of members) it is faster to suppress an already limited crosstab, than to have Cognos run a crosstab on the entire set then suppress the results.

[sanjay0828]

Hi Paul,

I did use the same query you mentioned.
The time taken to return the results is 18 minutes.

I was trying this report for a small subset of my original requirement.

The complete report with the above query calculation takes 2hrs 5min.
..without the query calculation - more than 1 day
..with the MDX I wrote - less than 2min

It is a huge difference.
Anyways, bringing the time down from more than a day to 2hrs 5min is enough for me.
But is there any way I can create this query calculation in the package itself so that the users can just drag and use this, instead of writing it themselves.
As I had mentioned earlier, it is not possible to create this in SSAS 2005 itself (because 2005 does not support dynamic sets)

Thanks,
Sanjay

[david.stachon]

I wouldn't recommend it, but YES, you could SSAS cube calculations in FM....I've tried it, and it does work.

....with the caveat that there's no way it's going to validate (in FM) because as I mentioned, FM has no awareness of the cube.

Here's how:

1) Build your calculation in Report Studio first to make sure it works. Be sure to use fully qualified names
2) In FM, create a standalone calculation, and use that calculation code from the previous step
3) Drag the calculation under the cube pointer in your FM model and publish.

Yes, the calculation will appear as invalid in FM and you will get error when publishing.

...however, it will work at runtime in a report.