Tm1 - mdx query for cube cellset

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Fri Mar 29, 2019 3:22 pm

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"
},

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Mon Apr 01, 2019 8:41 pm

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

The RuleDerived field will give you boolean value based on rule-based or not.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Mon Apr 01, 2019 8:42 pm

To your question, MDX can't do that. Only the above method can do it.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Wed Apr 03, 2019 1:31 pm

Can you give me some example query on that?

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Wed Apr 03, 2019 8:44 pm

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
Capture.PNG (18.51 KiB) Viewed 1079 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Thu Apr 04, 2019 8:11 am

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
MVP
Posts: 2151
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Tm1 - mdx query for cube cellset

Post by Wim Gielis » Thu Apr 04, 2019 10:33 am

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 ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Thu Apr 04, 2019 12:24 pm

Yes, for updateable its return different values for the one which can be updateable and non-updateable
Attachments
mdx-updateable.png
mdx-updateable.png (6.82 KiB) Viewed 1048 times

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Thu Apr 04, 2019 8:11 pm

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Fri Apr 05, 2019 6:09 am

Ok..I agree..

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Wed Apr 10, 2019 2:57 pm

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?

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Wed Apr 10, 2019 8:53 pm

Not sure, I always use the first query and got right answers. Which version of PAL are your using? Maybe it is a bug?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Thu Apr 11, 2019 7:04 am

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:
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?

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Thu Apr 11, 2019 8:43 pm

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Fri Apr 12, 2019 8:15 am

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).
Attachments
REST-SecondRequest.png
REST-SecondRequest.png (54.66 KiB) Viewed 911 times
Rest-FirstRequest.png
Rest-FirstRequest.png (35.83 KiB) Viewed 911 times

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Mon Apr 15, 2019 1:17 am

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=*)
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Mon Apr 15, 2019 6:47 am

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:(
Attachments
mdx_withUpdateable.txt
(3.73 KiB) Downloaded 11 times
mdx_withoutUpdateable.txt
(3.3 KiB) Downloaded 11 times

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Mon Apr 15, 2019 9:11 pm

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

kavitha2002
Regular Participant
Posts: 150
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

Post by kavitha2002 » Tue Apr 16, 2019 7:33 am

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.

User avatar
macsir
Community Contributor
Posts: 639
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Tm1 - mdx query for cube cellset

Post by macsir » Tue Apr 16, 2019 9:15 pm

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Post Reply