Tm1 - mdx query for cube cellset

[kavitha2002]

Hello everyone,

Is there a way to know that cube's cell has feeders/rules in the cellset retrieved by Mdx statement.

Usually it returns the below:
"Cells": [
{
"Ordinal": 0,
"Value": 82.10000000000001,
"FormattedValue": "82,10"
},

[macsir]

?$expand=Cells($select=Ordinal, Value, RuleDerived)

The RuleDerived field will give you boolean value based on rule-based or not.

[macsir]

To your question, MDX can't do that. Only the above method can do it.

[kavitha2002]

Can you give me some example query on that?

[macsir]

Code: Select all

https://SERVERNAME:PORTNUMBER/api/v1/Cellsets('YOURCELLSETID')?$expand=Cells($select=Ordinal, Value,RuleDerived)

Get your cellset id from your MDX query and replace it here.
And then you will get result like this.

Capture.PNG (18.51 KiB) Viewed 8617 times

[kavitha2002]

Thanks for your reply.. It works:)

Do have another question, Is there any MDX statement to know whether the cube cells are readable or writable?

Can I use "Updateable"? but it returns "Updateable": 258 ??

?Cells($select=Ordinal, Value,RuleDerived,Updateable)

[Wim Gielis]

Can I use "Updateable"? but it returns "Updateable": 258 ??

?Cells($select=Ordinal, Value,RuleDerived,Updateable)

What happens when you test it on both a cell that is updatable and one that isn’t ? Do you get a different result ?

[kavitha2002]

Yes, for updateable its return different values for the one which can be updateable and non-updateable

[macsir]

You probably can but can't guarantee if IBM change this code further or not.
I will stick to rulederived key to determine if updateable or not.

[kavitha2002]

Ok..I agree..

[kavitha2002]

Hi @macsir,

I have a quick clarification on RuleDerived, I have made MDX request on REST api first then with the help of the ID I have made another request for

Code: Select all

http://host:8000/api/v1/Cellsets('ID')?$expand=Cells($select=Ordinal,Value,RuleDerived) 

This above returns all RuleDerived as "True", though rule are not specified in some cells.

Then I tried with

Code: Select all

http://host:8000/api/v1/Cellsets('ID')?$expand=Cells($select=Ordinal,Value,RuleDerived,Updateable) 

then it returns the proper value.. why the first request returning the values wrongly? and Idea?

[macsir]

Not sure, I always use the first query and got right answers. Which version of PAL are your using? Maybe it is a bug?

[kavitha2002]

I am using PA 2.0.6 version. I have tried in 2.0.4 and 2.0.2 version all gives the same response.

I have tried in ARC for the rest api
First request:

http://localhost:8000/api/v1/ExecuteMDX?$expand=Cells for mdx query

Second request:

http://host:8000/api/v1/Cellsets('-first request cellset ID')?$expand=Cells($select=Ordinal,Value,RuleDerived)

return response "Rulederived" all as true. When I give "Updateable" then response is correct in all the versions.

Is there something wrong am doing here? which version are you using?

[macsir]

I am using 2.0.6 too but with Postman to check rest api.
The first query shouldn't use expand operator and it is purely for executing MDX to get cellset id.

Code: Select all

http://localhost:8000/api/v1/ExecuteMDX

and then use this id in your second query.
If you can post some snapshots, that would be good.

[kavitha2002]

I dont want the entire cube cellset, based on the query I would like to filter the cellset.

Herewith I have attached the screenshots for both the query.

Do have another question, Is there a possibility of saying which cell is editable, non-editable and type of the cell(String, Number, Consolidated).

[macsir]

Hi, I still couldn't see your original problem from your snapshots?

return response "Rulederived" all as true. When I give "Updateable" then response is correct in all the versions.

Is there something wrong am doing here? which version are you using?

But for your next question,

Do have another question, Is there a possibility of saying which cell is editable, non-editable and type of the cell(String, Number, Consolidated).

For editable or non-editable, with false in "RuleDerived" and "Consolidated" properties, the cell is editable.

Code: Select all

ExecuteMDX?$expand=Cells($select=*)

There is no type for cells, but for elements. Elements in a dimension have type property, which can tell you those three types.

Code: Select all

Dimensions('XYZ')?$expand=Hierarchies($expand=*)

[kavitha2002]

I have attached two text file of the same query, but one with Updateable and other not. In mdx_withoutUpdateable.txt, you can find the response "RuleDerived": false' for all the cells. But in mdx_withUpdateable.txt, response is correct and expected one.

For editable or non-editable, with false in "RuleDerived" and "Consolidated" properties, the cell is editable.

I tried the same, now am in good shape that to decide whether cell is editable or not.

Yes, I know in dimension level we can get the type of the element, but i need it on cells level:(

[macsir]

Can't figure out why and couldn't reproduce your errors. I always got consistent results regardless of using updateable or not.
It might be a bug in some versions. Have you contacted IBM about this?
For cell level type, you still can use the the very original logic to check, i.e., if the value only has numbers then it is numeric, otherwise it is string or null.

[kavitha2002]

ok I will reach to IBM. Thanks for your time and guidance:)

For cell level type, you still can use the the very original logic to check, i.e., if the value only has numbers then it is numeric, otherwise it is string or null.

Sometimes values are empty, in such a case, below is the response with value and empty value.
-----------------------------------------------------------
HasValue Empty
-----------------------------------------------------------
String "Year" ""
Number 1 null

Problem is in application I have defined the class cellset, Property Value of Type 'String', so it will convert the number/string value to String.

I am planning to implement in the code, based on the last dimension.
condition 1) If the last dim is in Title, depends on the type of the element chosen, will decide entire cube hold the string value or numeric value. condition 2) LastDim is in row, then row level evaluation should be done
condition 3) If in column then column level evaluation.

Write back your comments as well.

[macsir]

I am planning to implement in the code, based on the last dimension.
condition 1) If the last dim is in Title, depends on the type of the element chosen, will decide entire cube hold the string value or numeric value. condition 2) LastDim is in row, then row level evaluation should be done
condition 3) If in column then column level evaluation.

The cell type in a cube is dependent on the element type of the measure dimension(last dimension in order). It is irrelevant to the position where you put in the cube view.