MDX Query Submitted to TM1 server failed during Execution & Report Studio error

[Varshini Niharika]

Hello Members,

Running the Cognos BI report in Report Studio we faced the below error as
TM1-ERR-0061
"The MDX Query submitted to TM1 server failed during Execution"

On trail to inhibit the error..there where many adjustments made to the Cube and the respective dimension as below:

a. Kept the Level names and dimension as different.
b.The dimension values doesn't have any special characters.
c. Cube name is also simple without any special character.
d. Declared the measure n period in the Properties while creating the Cube in TM1.
e. Rearrangement of the Dimensions in the Cube.

On analysis by multiple trial and error modifications to the Dimension..

Dimension which is a Job role dimension which has Values of Length around 70-80 and Count of the Values around 500-1000.
We reduced the Length to some specific value and the decreased the Count of the Dimension too. And then when this dimension was used in the Report studio with the Measure, We dint face any MDX error.

If the Length kept as same with Less count..IT WORKS..
If the Length is reduced and the Count remains the Same...Still IT WORKS..

But with a dimension with higher Length value and Count, even the Data is been Loaded in TM1, The Report Studio gives an error as above "The MDX Query.."  :o :o

So we like to know is there any other Reasons for this Error or is there any Specific Length and Count Specification for the Dimension in a Cube, any Memory Allocation of the Cube or dimension property.

Kindly reply with any of your thoughts or idea for this above Query.  ::)

Thanks  :)

[MFGF]

What are you doing in your report? Are you simply retrieving data, and/or filtering? How is data being retrieved - are you manually selecting members? bringing in levels? adding expressions to fetch members from the cube?

It's difficult to comment on what the issue might be without knowing what you are trying to do...

Cheers!

MF.

[Varshini Niharika]

Hi,

We are trying to Display Jobrole in Multi select prompt Corresponding to the Service lines selected in Value Prompt.
Only those Jobrole's which has Values >0 for Specific Service Line given.

Ex. SLC prompt having values : A B C D and Jobrole prompt has A1,A2,A3,A4,B1,B2,B3,B4,C1....D1.....
So if we Select SLC Prompt as "B" then Jobrole prompt should display "B1,B2,B3..." in which we can select multiple options..

In a Query, We are just placing SLC dimension Member Data item, Jobrole dimension Data item and the respective Measure with a filter Measure >0.

When we keep Measure>0 filter.. then run the report..it gives this MDX Query error.. If we dont keep the Measure>0 filter...
The Report runs properly.. But there is no filter on Jobrole prompt based on SLC..( Jobrole displays a1,a2,b1,b2.... even when SLC selected as b)

[MFGF]

Whan you refer to "filter", do you mean you are using a detail filter in your query, or are you coding a report expression using the dimensional filter() function?

Detail filters are generaly a big no-no when reporting off dimensional sources such as TM1. You really need to replace them with filter function expressions in the row or column headings.  Eg - if you want to filter your Job Role Levels to show only those beginning with B, bring in a query calculation with the expression

filter([Job role level], roleValue('_memberCaption',[Job role level]) starts with 'B')

or if you want to prompt for this,

filter([Job role level], roleValue('_memberCaption',[Job role level]) starts with ?StartChar?)

You should use a filter() function like this to retrieve only level members with non-zero measure values too.

Hope that helps!

MF.