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

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:


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

Hi Hubert,

thank you for the extensive explanation and the sample. Very much appreciated.

I was unaware of the fact, that the cellset only contains the cell properties that were either requested in the MDX or the $select statement.
Would it be possible that you adjust the cellset-building-logic to include not only the properties that are referenced in the $select part, but also the properties that are used in $filter?

The reason I am asking for this enhancement is that we are currently adding functionality to TM1py that allows us to execute MDX queries and retrieve only the cells that are not consolidated or rule derived.
Since TM1py is used as a library I have no control over how users are writing their MDX queries. I wouldn't dare to enforce the explicit use of CELL PROPERTIES in MDX queries or manipulate the CELL PROPERTIES in the queries before forwarding them to TM1.
https://github.com/cubewise-code/tm1py/issues/273
https://github.com/cubewise-code/tm1py/pull/279

On a side note, I assume that if we limit the DIMENSION PROPERTIES and CELL PROPERTIES in the MDX query this would have a positive impact on the performance. Is that correct?

Cheers,

Marius




------------------------------
Marius Wirtz
------------------------------

Original Message:
Sent: Wed July 15, 2020 06:19 AM
From: Hubert Heijkers
Subject: TM1 REST API - filter cells by cell properties

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

Hi Marius,

As you guessed, materializing all CELL PROPERTIES would be very expensive, albeit that some can be done more on-demand then others potentially getting rid of some of the overhead. Only thing I can say is that I've done quite some optimization to make it perform as best as possible while still being able to produce the correct values for all cell properties (our ODBO connector, and therefore our MDX support in the C-API, had it's quirks;-) all the time, if so requested. 

Let me repeat however that using the cell properties from the $select, if provided on the ExecuteMDX action, is kind of hacky and if I'd have to do it again I wouldn't, specifying them in the MDX is the right way to go, but at the same time I do understand that the limitation of that for generic library builders like yourselves. A better solution would have been an extra, optional, parameter to the ExecuteMDX action, explicitly instructing the inclusion of those cell properties in the result as opposed to trying to deduce from the $select (in retrospect a misuse of the definition of the projection of the result as a parameter, for which I apologize;-).
Also, I don't expect very many people to use this $select hack anyway, that's provided you knew about it to begin with;-), as it only applies to the $select on the POST request to the ExecuteMDX action which I'd argue would typically be an asynchronous request, most of the time and followed by one or more GET requests against the cellset entity that was returned by that request.

In the meantime, I also found that apparently specifying the $select on that POST request to ExecuteMDX thrumbs the CELL PROPERTIES specified in the MDX, that for sure can never have been the actual intend here (read: I have to 'fix' something here no matter what).

I'm guessing such an extra parameter to the ExecuteMDX action would help you accomplish what you are asking for here as well right?

Cheers, -H

PS DIMENSION PROPERTIES are not materialized IN the Cellset but simply always looked up on the fly, so no overhead to those. The DIMENSION PROPERTIES clause therefore only creates a default projection for the Attributes property. The only edge case for you in this would be if you would want to retrieve the Caption and you didn't know if/what DIMENSION PROPERTIES clause would have been specified in that case. The workaround for that would be to expand the element for a member and only select the Attributes/Caption property for that Element if you didn't need to know anything else about the element.

PS Thanks for getting rid of the use of 'Content', Ryan was absolutely spot on when he said I'd like it if you'd get rid of it;-!

------------------------------
Hubert Heijkers
------------------------------

Original Message:
Sent: Wed July 15, 2020 08:42 AM
From: Marius Wirtz
Subject: TM1 REST API - filter cells by cell properties

Hi Hubert,

thank you for the extensive explanation and the sample. Very much appreciated.

I was unaware of the fact, that the cellset only contains the cell properties that were either requested in the MDX or the $select statement.
Would it be possible that you adjust the cellset-building-logic to include not only the properties that are referenced in the $select part, but also the properties that are used in $filter?

The reason I am asking for this enhancement is that we are currently adding functionality to TM1py that allows us to execute MDX queries and retrieve only the cells that are not consolidated or rule derived.
Since TM1py is used as a library I have no control over how users are writing their MDX queries. I wouldn't dare to enforce the explicit use of CELL PROPERTIES in MDX queries or manipulate the CELL PROPERTIES in the queries before forwarding them to TM1.
https://github.com/cubewise-code/tm1py/issues/273
https://github.com/cubewise-code/tm1py/pull/279

On a side note, I assume that if we limit the DIMENSION PROPERTIES and CELL PROPERTIES in the MDX query this would have a positive impact on the performance. Is that correct?

Cheers,

Marius




------------------------------
Marius Wirtz

Original Message:
Sent: Wed July 15, 2020 06:19 AM
From: Hubert Heijkers
Subject: TM1 REST API - filter cells by cell properties

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

Hi Hubert,

Yes. An extra parameter in the ExecuteMDX action is exactly what I need.
Thanks for the insights on the dimension properties. Much appreciated!

------------------------------
Marius Wirtz
------------------------------

Original Message:
Sent: Wed July 15, 2020 09:38 AM
From: Hubert Heijkers
Subject: TM1 REST API - filter cells by cell properties

Hi Marius,

As you guessed, materializing all CELL PROPERTIES would be very expensive, albeit that some can be done more on-demand then others potentially getting rid of some of the overhead. Only thing I can say is that I've done quite some optimization to make it perform as best as possible while still being able to produce the correct values for all cell properties (our ODBO connector, and therefore our MDX support in the C-API, had it's quirks;-) all the time, if so requested. 

Let me repeat however that using the cell properties from the $select, if provided on the ExecuteMDX action, is kind of hacky and if I'd have to do it again I wouldn't, specifying them in the MDX is the right way to go, but at the same time I do understand that the limitation of that for generic library builders like yourselves. A better solution would have been an extra, optional, parameter to the ExecuteMDX action, explicitly instructing the inclusion of those cell properties in the result as opposed to trying to deduce from the $select (in retrospect a misuse of the definition of the projection of the result as a parameter, for which I apologize;-).
Also, I don't expect very many people to use this $select hack anyway, that's provided you knew about it to begin with;-), as it only applies to the $select on the POST request to the ExecuteMDX action which I'd argue would typically be an asynchronous request, most of the time and followed by one or more GET requests against the cellset entity that was returned by that request.

In the meantime, I also found that apparently specifying the $select on that POST request to ExecuteMDX thrumbs the CELL PROPERTIES specified in the MDX, that for sure can never have been the actual intend here (read: I have to 'fix' something here no matter what).

I'm guessing such an extra parameter to the ExecuteMDX action would help you accomplish what you are asking for here as well right?

Cheers, -H

PS DIMENSION PROPERTIES are not materialized IN the Cellset but simply always looked up on the fly, so no overhead to those. The DIMENSION PROPERTIES clause therefore only creates a default projection for the Attributes property. The only edge case for you in this would be if you would want to retrieve the Caption and you didn't know if/what DIMENSION PROPERTIES clause would have been specified in that case. The workaround for that would be to expand the element for a member and only select the Attributes/Caption property for that Element if you didn't need to know anything else about the element.

PS Thanks for getting rid of the use of 'Content', Ryan was absolutely spot on when he said I'd like it if you'd get rid of it;-!

------------------------------
Hubert Heijkers

Original Message:
Sent: Wed July 15, 2020 08:42 AM
From: Marius Wirtz
Subject: TM1 REST API - filter cells by cell properties

Hi Hubert,

thank you for the extensive explanation and the sample. Very much appreciated.

I was unaware of the fact, that the cellset only contains the cell properties that were either requested in the MDX or the $select statement.
Would it be possible that you adjust the cellset-building-logic to include not only the properties that are referenced in the $select part, but also the properties that are used in $filter?

The reason I am asking for this enhancement is that we are currently adding functionality to TM1py that allows us to execute MDX queries and retrieve only the cells that are not consolidated or rule derived.
Since TM1py is used as a library I have no control over how users are writing their MDX queries. I wouldn't dare to enforce the explicit use of CELL PROPERTIES in MDX queries or manipulate the CELL PROPERTIES in the queries before forwarding them to TM1.
https://github.com/cubewise-code/tm1py/issues/273
https://github.com/cubewise-code/tm1py/pull/279

On a side note, I assume that if we limit the DIMENSION PROPERTIES and CELL PROPERTIES in the MDX query this would have a positive impact on the performance. Is that correct?

Cheers,

Marius




------------------------------
Marius Wirtz

Original Message:
Sent: Wed July 15, 2020 06:19 AM
From: Hubert Heijkers
Subject: TM1 REST API - filter cells by cell properties

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