Hi Marius,
The most important thing here is to separate the querying of the data, which in this case you do using MDX and which results in the creation of a new Cellset entity representing the query result, and the querying of that query result itself, which you do with the OData 'query'.
MDX has the notion of DIMENSION and CELL properties. DIMENSION properties map to properties and attributes of the members in the tuples on the individual axes and CELL properties represent data about the cells, the data points, in the query result itself.
It is also important to recognize the difference in granularity in which you can define what you are querying. In MDX you can be specific to the hierarchy level when it comes to DIMENSION properties whereas when querying the query result you can only specify which properties you'd wish to retrieve for ALL hierarchies in the query result. And obviously you can only retrieve information from the query result that's been queried to begin with.
So to bridge these two 'worlds' what we did is that if asking for Attributes but not specifying which once explicitly, we will simply project the [DIMENSION] PROPERTIES specified in the query and return none if none where specified. BTW there is a difference between having a [DIMENSION] PROPERTIES clause and not having one at all in which case ALL is presumed again. You can use the MEMBER_NAME [DIMENSION] PROPERTY, which we presume is mapped to the build-in 'Name' property anyway and as such can already be requested using $select.
The same is true for CELL PROPERTIES. If no CELL PROPERTIES clause is specified it is presumed that you are only interested in the ordinal, value and formatted value for a cell (the default set of cell properties for MDX).
For convenience, if no CELL PROPERTIES clause was given in the MDX, we used the set of cell properties requested in the OData query, specified using $select as part of the Cells expand (on the original ExecuteMDX request) into the set of CELL PROPERTIES to be included in the cellset result.
So, back to your case, where you are only interested in the VALUE property but also need the CONSOLIDATED property for filtering purposes, you would specify the CELL PROPERTIES clause in your MDX and specify the VALUE and CONSOLIDATED properties there. Then, when you retrieve the data, you only ask for the value while filtering on the Consolidated property. And since you are not interested in the Consolidated value itself you'd include a $select=Value in the expand to prevent the Consolidated property to be projected in the response (but know that the Consolidated property was part of the cell-set because of the CELL PROPERTIES you specified in the MDX.
Have a look at the result of this MDX query fired against the Planning Sample database:
SELECT
{[plan_time].[plan_time].[Q1-2004], [plan_time].[plan_time].[Q1-2004].CHILDREN} DIMENSION PROPERTIES [plan_time].[Time_Swedish] on 0,
[plan_chart_of_accounts].[plan_chart_of_accounts].MEMBERS DIMENSION PROPERTIES MEMBER_NAME on 1
FROM [plan_BudgetPlan]
WHERE ([plan_business_unit].[plan_business_unit].[10110], [plan_department].[plan_department].[105], [plan_source].[plan_source].[input]) DIMENSION PROPERTIES MEMBER_NAME, [plan_business_unit].[BusinessUnit_Swedish], [plan_business_unit].[Currency], [plan_department].[Department_Swedish]
CELL PROPERTIES CELL_ORDINAL, VALUE, CONSOLIDATED
Have fun!
Cheers, -H
------------------------------
Hubert Heijkers
------------------------------
Original Message:
Sent: Tue July 14, 2020 08:46 AM
From: Marius Wirtz
Subject: TM1 REST API - filter cells by cell properties
Hello,
I am trying to retrieve only the cells from a cellset that are not rule derived or consolidated.
I found a strange behaviour in the TM1 REST API and I wonder if this is by design.
When I select the Value property and filter by the Consolidated property the filter does not apply.
https://localhost:12354/api/v1/ExecuteMDX?$expand=Cells($select=Value;$filter=Consolidated eq false;$expand=Members($select=Name))
When I select the Value and the Consolidated property and filter by the Consolidated property, the filter does apply.
https://localhost:12354/api/v1/ExecuteMDX?$expand=Cells($select=Value,Consolidated;$filter=Consolidated eq false;$expand=Members($select=Name))
This is not ideal for my case. I don't want to retrieve the Consolidated property from all cells.
Is this a bug or is this just how the REST API works?
Cheers,
Marius
------------------------------
Marius Wirtz
------------------------------
#PlanningAnalyticswithWatson