Partial Idea on MDX Query submitted to TM1 server, "BUT"

[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.."

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

[macsir]

I don't think it is caused by the char length of that dim. As long as tm1 allows, it would not be an issue for report studio.
Have you checked the MDX query which report studio generated? Or you can paste it to let us have a look.

[niharika]

Hi Macsir,

The below attached is the MDX query error we got while running the report in Report Studio

[macsir]

No, I mean the MDX query which RS generated automatically in the backend. That damn SELECT statement.

[niharika]

The Generated MDX query for the Specific Dimension in the Specific Query

WITH MEMBER [careerframework_measures].[careerframework_measures].[@MEMBER].[COG_OQP_USR_] AS '[careerframework_measures].[careerframework_measures].[@MEMBER].[COG_OQP_INT_m2]', SOLVE_ORDER = 2 MEMBER [careerframework_measures].[careerframework_measures].[@MEMBER].[COG_OQP_INT_m2] AS 'IIF([careerframework_measures].[careerframework_measures].[@MEMBER].[Measures] > 0, [careerframework_measures].[careerframework_measures].[@MEMBER].[Measures], NULL)', SOLVE_ORDER = 2 SELECT FILTER([careerframework_jrss].[careerframework_jrss].[All JRSS].MEMBERS, NOT ISEMPTY([careerframework_measures].[careerframework_measures].[@MEMBER].[COG_OQP_USR_])) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), {[careerframework_measures].[careerframework_measures].[@MEMBER].[COG_OQP_USR_]} ON AXIS(1) FROM [gdi_careerframework_slc_comp]


Concern: 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.

[macsir]

Can't see any unusual stuff. Did you look at the detailed cognos log?

[niharika]

Hello

We haven't checked the Cognos Detailed log, as it is based on the server side which is not accessible for us.

Sir

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.

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..

Any idea y this happens

[macsir]

Did you consider FILTER function instead of using measure > 0? They are the difference between Relational and Multidimensional author style.

[niharika]

Yes tried using Filter function also.. It too gave the same error.