Tm1 - mdx query for cube cellset
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Tm1 - mdx query for cube cellset
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"
},
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
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
?$expand=Cells($select=Ordinal, Value, RuleDerived)
The RuleDerived field will give you boolean value based on rule-based or not.
The RuleDerived field will give you boolean value based on rule-based or not.
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
To your question, MDX can't do that. Only the above method can do it.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
Can you give me some example query on that?
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
Code: Select all
https://SERVERNAME:PORTNUMBER/api/v1/Cellsets('YOURCELLSETID')?$expand=Cells($select=Ordinal, Value,RuleDerived)
And then you will get result like this.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
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)
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)
-
- MVP
- Posts: 3116
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Tm1 - mdx query for cube cellset
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 wrote: ↑Thu Apr 04, 2019 8:11 am Can I use "Updateable"? but it returns "Updateable": 258 ??
?Cells($select=Ordinal, Value,RuleDerived,Updateable)
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
Yes, for updateable its return different values for the one which can be updateable and non-updateable
- Attachments
-
- mdx-updateable.png (6.82 KiB) Viewed 8596 times
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
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.
I will stick to rulederived key to determine if updateable or not.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
Ok..I agree..
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
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
This above returns all RuleDerived as "True", though rule are not specified in some cells.
Then I tried with
then it returns the proper value.. why the first request returning the values wrongly? and Idea?
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)
Then I tried with
Code: Select all
http://host:8000/api/v1/Cellsets('ID')?$expand=Cells($select=Ordinal,Value,RuleDerived,Updateable)
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
Not sure, I always use the first query and got right answers. Which version of PAL are your using? Maybe it is a bug?
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
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:
Is there something wrong am doing here? which version are you using?
I have tried in ARC for the rest api
First request:
Second request:http://localhost:8000/api/v1/ExecuteMDX?$expand=Cells for mdx query
return response "Rulederived" all as true. When I give "Updateable" then response is correct in all the versions.http://host:8000/api/v1/Cellsets('-first request cellset ID')?$expand=Cells($select=Ordinal,Value,RuleDerived)
Is there something wrong am doing here? which version are you using?
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
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.
and then use this id in your second query.
If you can post some snapshots, that would be good.
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
If you can post some snapshots, that would be good.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
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).
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).
- Attachments
-
- REST-SecondRequest.png (54.66 KiB) Viewed 8459 times
-
- Rest-FirstRequest.png (35.83 KiB) Viewed 8459 times
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
Hi, I still couldn't see your original problem from your snapshots?
There is no type for cells, but for elements. Elements in a dimension have type property, which can tell you those three types.
But for your next question,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?
For editable or non-editable, with false in "RuleDerived" and "Consolidated" properties, the cell is editable.Do have another question, Is there a possibility of saying which cell is editable, non-editable and type of the cell(String, Number, Consolidated).
Code: Select all
ExecuteMDX?$expand=Cells($select=*)
Code: Select all
Dimensions('XYZ')?$expand=Hierarchies($expand=*)
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
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.
Yes, I know in dimension level we can get the type of the element, but i need it on cells level:(
I tried the same, now am in good shape that to decide whether cell is editable or not.For editable or non-editable, with false in "RuleDerived" and "Consolidated" properties, the cell is editable.
Yes, I know in dimension level we can get the type of the element, but i need it on cells level:(
- Attachments
-
- mdx_withUpdateable.txt
- (3.73 KiB) Downloaded 190 times
-
- mdx_withoutUpdateable.txt
- (3.3 KiB) Downloaded 184 times
- macsir
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
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.
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.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Tm1 - mdx query for cube cellset
ok I will reach to IBM. Thanks for your time and guidance:)
-----------------------------------------------------------
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.
Sometimes values are empty, in such a case, below is the response with value and empty value.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.
-----------------------------------------------------------
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
- MVP
- Posts: 783
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Tm1 - mdx query for cube cellset
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.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.