no Custom MDX Query is working against SSAS

[Fleschgordon]

hi,

we always get:
XQE-PLN-0256 User-defined MDX queries are not supported

also we tried to generated the same query which cognos should generate

any suggestions are welcome

thx

[MFGF]

hi,

we always get:
XQE-PLN-0256 User-defined MDX queries are not supported

also we tried to generated the same query which cognos should generate

any suggestions are welcome

thx

The message seems to suggest that the connection type you are using does not support user-defined MDX. How is your package connecting - using CQM or DQM?

Why would you want to hand-code MDX queries in the first place?

MF.

[Fleschgordon]

The message seems to suggest that the connection type you are using does not support user-defined MDX. How is your package connecting - using CQM or DQM?

Why would you want to hand-code MDX queries in the first place?

MF.

hi

DQM

because somethimes the generated mdx queries are slow.

[MFGF]

hi

DQM

because somethimes the generated mdx queries are slow.

What happens if you try it via CQM - is it supported? If not, you're stuck, because it appears user-defined MDX isn't supported againd SSAS under DQM.

If your queries are slow, maybe it's a facet of less-than-optimal cube design or (more likely) a poorly written report? Reports using detail filters against OLAP sources are a common culprit. Is this the case here? If so, there are many options for making reports more efficient without hand-cranking MDX.

MF.

MF.

[CognosPaul]

The only time I've ever used hand-written MDX in Cognos was to use some unsupported functions (that was once in ten years). And that was only because the client couldn't make changes to the cube.

As long as your building the query correctly, DQM should (on average) return the results faster than a hand-written queries. In addition to the detail filters Mark mentioned, you should also take into account how many tuples are being generated by the query. Do you have a lot of nested nodes? Are you using report level suppression? Are slicing by hundreds of members?

[Fleschgordon]

I testet the generated mdx query inside sql management studio - it take 2 seconds to run.

when we first start the cognos report it takes about 40 seconds to run and show the results - if you run it then in another browser its fast. its only the first time if cognos connect to the cube. the question is if we can improve this first start (of the infrastructure) to connect to the cube.
We consider to connect to the report via "warmup script" to have it in the server cache.
have you got any other suggestions ?
thanks a lot...

[MFGF]

I testet the generated mdx query inside sql management studio - it take 2 seconds to run.

when we first start the cognos report it takes about 40 seconds to run and show the results - if you run it then in another browser its fast. its only the first time if cognos connect to the cube. the question is if we can improve this first start (of the infrastructure) to connect to the cube.
We consider to connect to the report via "warmup script" to have it in the server cache.
have you got any other suggestions ?
thanks a lot...

Yes - it sounds to me like you have a report that is resulting in local processing on the Cognos server (which is then caching the result set). This could easily be due to the way you have written the report (as Paul and I mentioned previously). Can you tell us what the report is doing? How is it coded? Are there any detail filters? How many tuples? Do you have lots of nesting? Are you suppressing cells with zero-suppression? Do you have slicers using large member sets?

If you give us some information on how your report is constructed we may be able to suggest ways to make it more efficient...

MF.