Page 1 of 2
Tm1 - mdx query for cube cellset
Posted: Fri Mar 29, 2019 3:22 pm
by 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"
},
Re: Tm1 - mdx query for cube cellset
Posted: Mon Apr 01, 2019 8:41 pm
by macsir
?$expand=Cells($select=Ordinal, Value, RuleDerived)
The RuleDerived field will give you boolean value based on rule-based or not.
Re: Tm1 - mdx query for cube cellset
Posted: Mon Apr 01, 2019 8:42 pm
by macsir
To your question, MDX can't do that. Only the above method can do it.
Re: Tm1 - mdx query for cube cellset
Posted: Wed Apr 03, 2019 1:31 pm
by kavitha2002
Can you give me some example query on that?
Re: Tm1 - mdx query for cube cellset
Posted: Wed Apr 03, 2019 8:44 pm
by 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 11581 times
Re: Tm1 - mdx query for cube cellset
Posted: Thu Apr 04, 2019 8:11 am
by 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)
Re: Tm1 - mdx query for cube cellset
Posted: Thu Apr 04, 2019 10:33 am
by Wim Gielis
kavitha2002 wrote: ↑Thu Apr 04, 2019 8:11 am
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 ?
Re: Tm1 - mdx query for cube cellset
Posted: Thu Apr 04, 2019 12:24 pm
by kavitha2002
Yes, for updateable its return different values for the one which can be updateable and non-updateable
Re: Tm1 - mdx query for cube cellset
Posted: Thu Apr 04, 2019 8:11 pm
by 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.
Re: Tm1 - mdx query for cube cellset
Posted: Fri Apr 05, 2019 6:09 am
by kavitha2002
Ok..I agree..
Re: Tm1 - mdx query for cube cellset
Posted: Wed Apr 10, 2019 2:57 pm
by 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?
Re: Tm1 - mdx query for cube cellset
Posted: Wed Apr 10, 2019 8:53 pm
by 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?
Re: Tm1 - mdx query for cube cellset
Posted: Thu Apr 11, 2019 7:04 am
by 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:
Second request:
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?
Re: Tm1 - mdx query for cube cellset
Posted: Thu Apr 11, 2019 8:43 pm
by 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.
Re: Tm1 - mdx query for cube cellset
Posted: Fri Apr 12, 2019 8:15 am
by 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).
Re: Tm1 - mdx query for cube cellset
Posted: Mon Apr 15, 2019 1:17 am
by 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=*)
Re: Tm1 - mdx query for cube cellset
Posted: Mon Apr 15, 2019 6:47 am
by 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:(
Re: Tm1 - mdx query for cube cellset
Posted: Mon Apr 15, 2019 9:11 pm
by 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.
Re: Tm1 - mdx query for cube cellset
Posted: Tue Apr 16, 2019 7:33 am
by 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.
Re: Tm1 - mdx query for cube cellset
Posted: Tue Apr 16, 2019 9:15 pm
by 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.