Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error
This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise
------------------------------

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)
Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon
------------------------------

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Paul,

First up, the syntax errors you are seeing, those seem to all come from the fact that you are using a comma as a separator while nested query options are semi-colon separated! Also, you seem to be mixing your $expand query option in with the list of properties you are passing to $select. I.o.w. use:

expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description;$expand=Element($select=Type))

As for the Cellset I was trying to say that the Dimension Properties clause effectively, or at least that's how I think about it, shapes the result and therefore which attributes are still included in the result unless you explicitly ask for specific attributes, which overrules the Dimension Properties clause.

BTW, did you know that immediately after your WHERE clause in the MDX you can add one more DIMENSION PROPERTIES clause to specify any attributes you'd be interested in coming from any of the dimensions in the where clause? Remember the where clause is effectively just an extra axis with just one tuple on it but other then that behaves exactly the same;-!
Also, the CELL PROPERTIES clause can be used to specify which properties of the Cell you are interested in. In that case you can simply expand the Cells fo the cellset without specifying a $select and you'll just get the cell properties referenced in the MDX query. Actually, you MUST specify cell properties in the MDX clause that you intend the to retrieve later on to guarantee the correct value (not an issue technically for the Value properties but some of the derivative properties, which require additional work, might not get initialized correctly if not mentioned in the query, hence not being queried!).

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

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise
------------------------------

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon
------------------------------

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Paul,

Yeah, 2.0.5 (TM1 11.3) is just a tad too old, this behaviour got introduced late June 2018 and was first released in TM1 11.4.0. So yeah, you might want to consider updating to a version from this decade ;-) 

As for the confusion, full well knowing that people prefer trial and error over reading specifications, but TM1 is 100% compliant to the OData specification which includes the URL conventions which outline exactly when to use what and what it is based on. IMHO the cool thing of complying to a standard like OData is exactly that we don't have to re-invent, document and elaborately explain what things mean and how to consume them as that's all done by the standard specification already.

Cheers,

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

Original Message:
Sent: Tue January 05, 2021 06:24 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Hubert

OK so if I understand you correctly, you are saying that in the latest version the MDX Dimension Properties will combine with the ODATA Query to limit what is returned?

I did look at the URL conventions link you gave but I found it rather impenetrable eg "Split undecoded query at "&" into query options, and each query option at the first "=" into query option name and query option value".

I looked at the URL Components section which covers : '?' '#' '/' '&' but not ';'. It is probably covered later on in the document. I think you might have to accept that most people are going to find standards document rather terse. I will try reading through it a bit more, but I am also learning several other technologies at the moment and it is a matter of time.. In my experience people learn better from seeing examples of a programming language rather than reading through the manual to learn every function.

Something got lost from my earlier post. What I was suggesting is that the TM1 Rest API documentation should start off by covering how to achieve some common tasks such as:

1) Running an MDX query which is probably going to entail getting at least a code and description for the rows and columns members, and whether they are consolidated or not. My assumption would be that the Title area Members would either be selected via point (3) below, or that people will have set a DefaultMember for the dimension in }HierarchyProperties. Therefore the return for the Title area is not so critical. On the Cell Value side, it is probably going to entail getting the Cell Set Id, Orginal, Numeric and Formatted Value and whether the cell is rule calculated. We can then format rows and columns that are consolidations differently and also cells that are rule calculated and we can know that they cannot be updated..Thanks to you I now know how to do that.

2) Update cell values using the Cell Set Id and Ordinal and also the bulk update via the function..

3) Select a dimension, then a hierarchy, then an element in that hierarchy, or a subset, then an element in that subset,

4) Run a process and pass parameters to it, and get a return value back - The process can do most of the other things that need to be done do like building dimensions, updating workflow, loading data, etc.There is possibly more scope for re-use using the Rest API, but you can get a fair amount of re-use in TI by calling processes. The only thing that can't be done easily is to return values.

5) Live promotion of an object via the Rest APi, in other words export it to JSON from one server and import the JSON into another.

If there were examples of the above, then that would cover quite a lot of what people need to do. There is already a reasonable set of examples around native views but as you can't use Named Hierarchies with those, they are likely to become less relevant.

We did start evaluating 2.0.9 last year. However, the person tasked with that got pulled on to other priorities, but we will be prioritising an upgrade this year. 

Regards

Paul Simon

------------------------------
Paul Simon
------------------------------

Original Message:
Sent: Tue January 05, 2021 08:18 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Yeah, 2.0.5 (TM1 11.3) is just a tad too old, this behaviour got introduced late June 2018 and was first released in TM1 11.4.0. So yeah, you might want to consider updating to a version from this decade ;-) 

As for the confusion, full well knowing that people prefer trial and error over reading specifications, but TM1 is 100% compliant to the OData specification which includes the URL conventions which outline exactly when to use what and what it is based on. IMHO the cool thing of complying to a standard like OData is exactly that we don't have to re-invent, document and elaborately explain what things mean and how to consume them as that's all done by the standard specification already.

Cheers,

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

Original Message:
Sent: Tue January 05, 2021 06:24 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Hi Paul,

Correct, if you grab effectively anything newer than the version you have, but I'd grab TM1 11.8.3 (PA 2.0.9.4) or newer, that would work.

Wrt standards, I fully understand, I was a normal person once upon a time myself but since I've become one of those standard committee people I've also come to understand the enormous amount of work that goes into these 'normative' specifications and the value that they bring once understood and applied consistently. In turn standards like OData, in turn, build on already established industry standards as well, most notably the HTTP standard and the generally accepted REST principles.

The reason that you quickly found the split on '?', '#', '/' and '&' is because these are defined as part of the HTTP standard, breaking with those rules would get any HTTP based implementation struggle with running in an HTTP infrastructure (which includes gateways, ingress/egress services, [reverse-]proxies etc. etc). Within the restrictions imposed by the HTTP standard, the OData specification then defines how we interpret the values specified for the various defined query options ($select, $filter, $expand etc). This is where the splitting by comma and semicolon characters comes in for example where the comma is used to separate items in a list and the semicolon is used to separate nested query options (as the HTTP standard prevents us from reusing the '&' character to do so). 

But having said that, I fully agree that examples can do magic in helping people understand, yet it might not be trivial to deduce the 'rules' for how to build requests yourself, which is where the specification comes in again. And as you can imagine I've given many people many samples over the years, often answering questions they had for their specific use-case/application, but also generic sets of samples, both for the REST API generically as well as for our GIT based source control integration which is OData JSON based as well. 

For example, I've, since 2014 when we introduced the first public version of the REST API, done Hands-On Labs pertaining to the REST API and, as one of the multiple ways of using the REST API with TM1, used Postman to show a set of standard requests which pretty much covers the set of examples you are asking for. I actually started a public repository once upon a time to help people set up a similar environment themselves which contains that Postman collection from last time I've updated it which can be found here.

Documentation could most definitely still be, quite significantly IMHO, improved. I for one would love to have some demo environment (read: a live TM1 server with demo models to play with) and associate that directly with the documentation with live examples.

And yes, focussing on MDX is the way to go, TM1 is, as you probably already gauged, is ultimately moving away from native views (hence them being quite limited and there not being any hierarchy support added) as well. 

As always, more than happy to help you be successful with TM1 using the REST API, just should me a message or start another thread here on this community;-!

Cheers,




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

Original Message:
Sent: Wed January 06, 2021 05:47 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Hubert

OK so if I understand you correctly, you are saying that in the latest version the MDX Dimension Properties will combine with the ODATA Query to limit what is returned?

I did look at the URL conventions link you gave but I found it rather impenetrable eg "Split undecoded query at "&" into query options, and each query option at the first "=" into query option name and query option value".

I looked at the URL Components section which covers : '?' '#' '/' '&' but not ';'. It is probably covered later on in the document. I think you might have to accept that most people are going to find standards document rather terse. I will try reading through it a bit more, but I am also learning several other technologies at the moment and it is a matter of time.. In my experience people learn better from seeing examples of a programming language rather than reading through the manual to learn every function.

Something got lost from my earlier post. What I was suggesting is that the TM1 Rest API documentation should start off by covering how to achieve some common tasks such as:

1) Running an MDX query which is probably going to entail getting at least a code and description for the rows and columns members, and whether they are consolidated or not. My assumption would be that the Title area Members would either be selected via point (3) below, or that people will have set a DefaultMember for the dimension in }HierarchyProperties. Therefore the return for the Title area is not so critical. On the Cell Value side, it is probably going to entail getting the Cell Set Id, Orginal, Numeric and Formatted Value and whether the cell is rule calculated. We can then format rows and columns that are consolidations differently and also cells that are rule calculated and we can know that they cannot be updated..Thanks to you I now know how to do that.

2) Update cell values using the Cell Set Id and Ordinal and also the bulk update via the function..

3) Select a dimension, then a hierarchy, then an element in that hierarchy, or a subset, then an element in that subset,

4) Run a process and pass parameters to it, and get a return value back - The process can do most of the other things that need to be done do like building dimensions, updating workflow, loading data, etc.There is possibly more scope for re-use using the Rest API, but you can get a fair amount of re-use in TI by calling processes. The only thing that can't be done easily is to return values.

5) Live promotion of an object via the Rest APi, in other words export it to JSON from one server and import the JSON into another.

If there were examples of the above, then that would cover quite a lot of what people need to do. There is already a reasonable set of examples around native views but as you can't use Named Hierarchies with those, they are likely to become less relevant.

We did start evaluating 2.0.9 last year. However, the person tasked with that got pulled on to other priorities, but we will be prioritising an upgrade this year. 

Regards

Paul Simon

------------------------------
Paul Simon

Original Message:
Sent: Tue January 05, 2021 08:18 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Yeah, 2.0.5 (TM1 11.3) is just a tad too old, this behaviour got introduced late June 2018 and was first released in TM1 11.4.0. So yeah, you might want to consider updating to a version from this decade ;-) 

As for the confusion, full well knowing that people prefer trial and error over reading specifications, but TM1 is 100% compliant to the OData specification which includes the URL conventions which outline exactly when to use what and what it is based on. IMHO the cool thing of complying to a standard like OData is exactly that we don't have to re-invent, document and elaborately explain what things mean and how to consume them as that's all done by the standard specification already.

Cheers,

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

Original Message:
Sent: Tue January 05, 2021 06:24 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

PS for those that want to learn more about the basics of OData compliant APIs, like TM1's, there are some quite informative getting started tutorials on the OData.org website to learn the basics quickly which can be found here.

Paul, perhaps I should create a TM1 equivalent for that one, once I find a way to get a live server out there ;-)​

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

Original Message:
Sent: Thu January 07, 2021 05:31 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Correct, if you grab effectively anything newer than the version you have, but I'd grab TM1 11.8.3 (PA 2.0.9.4) or newer, that would work.

Wrt standards, I fully understand, I was a normal person once upon a time myself but since I've become one of those standard committee people I've also come to understand the enormous amount of work that goes into these 'normative' specifications and the value that they bring once understood and applied consistently. In turn standards like OData, in turn, build on already established industry standards as well, most notably the HTTP standard and the generally accepted REST principles.

The reason that you quickly found the split on '?', '#', '/' and '&' is because these are defined as part of the HTTP standard, breaking with those rules would get any HTTP based implementation struggle with running in an HTTP infrastructure (which includes gateways, ingress/egress services, [reverse-]proxies etc. etc). Within the restrictions imposed by the HTTP standard, the OData specification then defines how we interpret the values specified for the various defined query options ($select, $filter, $expand etc). This is where the splitting by comma and semicolon characters comes in for example where the comma is used to separate items in a list and the semicolon is used to separate nested query options (as the HTTP standard prevents us from reusing the '&' character to do so). 

But having said that, I fully agree that examples can do magic in helping people understand, yet it might not be trivial to deduce the 'rules' for how to build requests yourself, which is where the specification comes in again. And as you can imagine I've given many people many samples over the years, often answering questions they had for their specific use-case/application, but also generic sets of samples, both for the REST API generically as well as for our GIT based source control integration which is OData JSON based as well. 

For example, I've, since 2014 when we introduced the first public version of the REST API, done Hands-On Labs pertaining to the REST API and, as one of the multiple ways of using the REST API with TM1, used Postman to show a set of standard requests which pretty much covers the set of examples you are asking for. I actually started a public repository once upon a time to help people set up a similar environment themselves which contains that Postman collection from last time I've updated it which can be found here.

Documentation could most definitely still be, quite significantly IMHO, improved. I for one would love to have some demo environment (read: a live TM1 server with demo models to play with) and associate that directly with the documentation with live examples.

And yes, focussing on MDX is the way to go, TM1 is, as you probably already gauged, is ultimately moving away from native views (hence them being quite limited and there not being any hierarchy support added) as well. 

As always, more than happy to help you be successful with TM1 using the REST API, just should me a message or start another thread here on this community;-!

Cheers,




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

Original Message:
Sent: Wed January 06, 2021 05:47 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Hubert

OK so if I understand you correctly, you are saying that in the latest version the MDX Dimension Properties will combine with the ODATA Query to limit what is returned?

I did look at the URL conventions link you gave but I found it rather impenetrable eg "Split undecoded query at "&" into query options, and each query option at the first "=" into query option name and query option value".

I looked at the URL Components section which covers : '?' '#' '/' '&' but not ';'. It is probably covered later on in the document. I think you might have to accept that most people are going to find standards document rather terse. I will try reading through it a bit more, but I am also learning several other technologies at the moment and it is a matter of time.. In my experience people learn better from seeing examples of a programming language rather than reading through the manual to learn every function.

Something got lost from my earlier post. What I was suggesting is that the TM1 Rest API documentation should start off by covering how to achieve some common tasks such as:

1) Running an MDX query which is probably going to entail getting at least a code and description for the rows and columns members, and whether they are consolidated or not. My assumption would be that the Title area Members would either be selected via point (3) below, or that people will have set a DefaultMember for the dimension in }HierarchyProperties. Therefore the return for the Title area is not so critical. On the Cell Value side, it is probably going to entail getting the Cell Set Id, Orginal, Numeric and Formatted Value and whether the cell is rule calculated. We can then format rows and columns that are consolidations differently and also cells that are rule calculated and we can know that they cannot be updated..Thanks to you I now know how to do that.

2) Update cell values using the Cell Set Id and Ordinal and also the bulk update via the function..

3) Select a dimension, then a hierarchy, then an element in that hierarchy, or a subset, then an element in that subset,

4) Run a process and pass parameters to it, and get a return value back - The process can do most of the other things that need to be done do like building dimensions, updating workflow, loading data, etc.There is possibly more scope for re-use using the Rest API, but you can get a fair amount of re-use in TI by calling processes. The only thing that can't be done easily is to return values.

5) Live promotion of an object via the Rest APi, in other words export it to JSON from one server and import the JSON into another.

If there were examples of the above, then that would cover quite a lot of what people need to do. There is already a reasonable set of examples around native views but as you can't use Named Hierarchies with those, they are likely to become less relevant.

We did start evaluating 2.0.9 last year. However, the person tasked with that got pulled on to other priorities, but we will be prioritising an upgrade this year. 

Regards

Paul Simon

------------------------------
Paul Simon

Original Message:
Sent: Tue January 05, 2021 08:18 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Yeah, 2.0.5 (TM1 11.3) is just a tad too old, this behaviour got introduced late June 2018 and was first released in TM1 11.4.0. So yeah, you might want to consider updating to a version from this decade ;-) 

As for the confusion, full well knowing that people prefer trial and error over reading specifications, but TM1 is 100% compliant to the OData specification which includes the URL conventions which outline exactly when to use what and what it is based on. IMHO the cool thing of complying to a standard like OData is exactly that we don't have to re-invent, document and elaborately explain what things mean and how to consume them as that's all done by the standard specification already.

Cheers,

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

Original Message:
Sent: Tue January 05, 2021 06:24 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Original Message:
Sent: 1/7/2021 5:32:00 AM
From: Hubert Heijkers
Subject: RE: Rest API MDX Query select more than one Attribute

Hi Paul,

Correct, if you grab effectively anything newer than the version you have, but I'd grab TM1 11.8.3 (PA 2.0.9.4) or newer, that would work.

Wrt standards, I fully understand, I was a normal person once upon a time myself but since I've become one of those standard committee people I've also come to understand the enormous amount of work that goes into these 'normative' specifications and the value that they bring once understood and applied consistently. In turn standards like OData, in turn, build on already established industry standards as well, most notably the HTTP standard and the generally accepted REST principles.

The reason that you quickly found the split on '?', '#', '/' and '&' is because these are defined as part of the HTTP standard, breaking with those rules would get any HTTP based implementation struggle with running in an HTTP infrastructure (which includes gateways, ingress/egress services, [reverse-]proxies etc. etc). Within the restrictions imposed by the HTTP standard, the OData specification then defines how we interpret the values specified for the various defined query options ($select, $filter, $expand etc). This is where the splitting by comma and semicolon characters comes in for example where the comma is used to separate items in a list and the semicolon is used to separate nested query options (as the HTTP standard prevents us from reusing the '&' character to do so). 

But having said that, I fully agree that examples can do magic in helping people understand, yet it might not be trivial to deduce the 'rules' for how to build requests yourself, which is where the specification comes in again. And as you can imagine I've given many people many samples over the years, often answering questions they had for their specific use-case/application, but also generic sets of samples, both for the REST API generically as well as for our GIT based source control integration which is OData JSON based as well. 

For example, I've, since 2014 when we introduced the first public version of the REST API, done Hands-On Labs pertaining to the REST API and, as one of the multiple ways of using the REST API with TM1, used Postman to show a set of standard requests which pretty much covers the set of examples you are asking for. I actually started a public repository once upon a time to help people set up a similar environment themselves which contains that Postman collection from last time I've updated it which can be found here.

Documentation could most definitely still be, quite significantly IMHO, improved. I for one would love to have some demo environment (read: a live TM1 server with demo models to play with) and associate that directly with the documentation with live examples.

And yes, focussing on MDX is the way to go, TM1 is, as you probably already gauged, is ultimately moving away from native views (hence them being quite limited and there not being any hierarchy support added) as well. 

As always, more than happy to help you be successful with TM1 using the REST API, just should me a message or start another thread here on this community;-!

Cheers,




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

Original Message:
Sent: Wed January 06, 2021 05:47 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Hubert

OK so if I understand you correctly, you are saying that in the latest version the MDX Dimension Properties will combine with the ODATA Query to limit what is returned?

I did look at the URL conventions link you gave but I found it rather impenetrable eg "Split undecoded query at "&" into query options, and each query option at the first "=" into query option name and query option value".

I looked at the URL Components section which covers : '?' '#' '/' '&' but not ';'. It is probably covered later on in the document. I think you might have to accept that most people are going to find standards document rather terse. I will try reading through it a bit more, but I am also learning several other technologies at the moment and it is a matter of time.. In my experience people learn better from seeing examples of a programming language rather than reading through the manual to learn every function.

Something got lost from my earlier post. What I was suggesting is that the TM1 Rest API documentation should start off by covering how to achieve some common tasks such as:

1) Running an MDX query which is probably going to entail getting at least a code and description for the rows and columns members, and whether they are consolidated or not. My assumption would be that the Title area Members would either be selected via point (3) below, or that people will have set a DefaultMember for the dimension in }HierarchyProperties. Therefore the return for the Title area is not so critical. On the Cell Value side, it is probably going to entail getting the Cell Set Id, Orginal, Numeric and Formatted Value and whether the cell is rule calculated. We can then format rows and columns that are consolidations differently and also cells that are rule calculated and we can know that they cannot be updated..Thanks to you I now know how to do that.

2) Update cell values using the Cell Set Id and Ordinal and also the bulk update via the function..

3) Select a dimension, then a hierarchy, then an element in that hierarchy, or a subset, then an element in that subset,

4) Run a process and pass parameters to it, and get a return value back - The process can do most of the other things that need to be done do like building dimensions, updating workflow, loading data, etc.There is possibly more scope for re-use using the Rest API, but you can get a fair amount of re-use in TI by calling processes. The only thing that can't be done easily is to return values.

5) Live promotion of an object via the Rest APi, in other words export it to JSON from one server and import the JSON into another.

If there were examples of the above, then that would cover quite a lot of what people need to do. There is already a reasonable set of examples around native views but as you can't use Named Hierarchies with those, they are likely to become less relevant.

We did start evaluating 2.0.9 last year. However, the person tasked with that got pulled on to other priorities, but we will be prioritising an upgrade this year. 

Regards

Paul Simon

------------------------------
Paul Simon

Original Message:
Sent: Tue January 05, 2021 08:18 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Yeah, 2.0.5 (TM1 11.3) is just a tad too old, this behaviour got introduced late June 2018 and was first released in TM1 11.4.0. So yeah, you might want to consider updating to a version from this decade ;-) 

As for the confusion, full well knowing that people prefer trial and error over reading specifications, but TM1 is 100% compliant to the OData specification which includes the URL conventions which outline exactly when to use what and what it is based on. IMHO the cool thing of complying to a standard like OData is exactly that we don't have to re-invent, document and elaborately explain what things mean and how to consume them as that's all done by the standard specification already.

Cheers,

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

Original Message:
Sent: Tue January 05, 2021 06:24 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson

Original Message:
Sent: 1/8/2021 2:47:00 PM
From: Paul Simon
Subject: RE: Rest API MDX Query select more than one Attribute

Hi Hubert

 

Thanks for the link to your GitHub with Rest API examples. I will certainly take a look. You have always been very helpful when it comes to the Rest API.

 

I do appreciate the role of standards, but, the necessary exactitude of a standards document is never going to win any prizes for literature. You can do a lot with the Rest API, but I would suggest that most people will only need to do a little.

 

Now that I know that MDX DIMENSION PROPERTIES will limit the result set in future I will for the moment not bother trying to limit the Attributes that are returned by the ODATA Query, as in future this will become unnecessary. I will instead concentrate on the MDX. I find the MDX approach easier and in any case it allows more granular control. It is clear that PAX uses this approach.

 

You can consider this thread closed.

 

Thanks for your help.

 

Regards

 

Paul Simon

 

Original Message:
Sent: 1/7/2021 5:32:00 AM
From: Hubert Heijkers
Subject: RE: Rest API MDX Query select more than one Attribute

Hi Paul,

Correct, if you grab effectively anything newer than the version you have, but I'd grab TM1 11.8.3 (PA 2.0.9.4) or newer, that would work.

Wrt standards, I fully understand, I was a normal person once upon a time myself but since I've become one of those standard committee people I've also come to understand the enormous amount of work that goes into these 'normative' specifications and the value that they bring once understood and applied consistently. In turn standards like OData, in turn, build on already established industry standards as well, most notably the HTTP standard and the generally accepted REST principles.

The reason that you quickly found the split on '?', '#', '/' and '&' is because these are defined as part of the HTTP standard, breaking with those rules would get any HTTP based implementation struggle with running in an HTTP infrastructure (which includes gateways, ingress/egress services, [reverse-]proxies etc. etc). Within the restrictions imposed by the HTTP standard, the OData specification then defines how we interpret the values specified for the various defined query options ($select, $filter, $expand etc). This is where the splitting by comma and semicolon characters comes in for example where the comma is used to separate items in a list and the semicolon is used to separate nested query options (as the HTTP standard prevents us from reusing the '&' character to do so). 

But having said that, I fully agree that examples can do magic in helping people understand, yet it might not be trivial to deduce the 'rules' for how to build requests yourself, which is where the specification comes in again. And as you can imagine I've given many people many samples over the years, often answering questions they had for their specific use-case/application, but also generic sets of samples, both for the REST API generically as well as for our GIT based source control integration which is OData JSON based as well. 

For example, I've, since 2014 when we introduced the first public version of the REST API, done Hands-On Labs pertaining to the REST API and, as one of the multiple ways of using the REST API with TM1, used Postman to show a set of standard requests which pretty much covers the set of examples you are asking for. I actually started a public repository once upon a time to help people set up a similar environment themselves which contains that Postman collection from last time I've updated it which can be found here.

Documentation could most definitely still be, quite significantly IMHO, improved. I for one would love to have some demo environment (read: a live TM1 server with demo models to play with) and associate that directly with the documentation with live examples.

And yes, focussing on MDX is the way to go, TM1 is, as you probably already gauged, is ultimately moving away from native views (hence them being quite limited and there not being any hierarchy support added) as well. 

As always, more than happy to help you be successful with TM1 using the REST API, just should me a message or start another thread here on this community;-!

Cheers,




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

Original Message:
Sent: Wed January 06, 2021 05:47 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Hubert

OK so if I understand you correctly, you are saying that in the latest version the MDX Dimension Properties will combine with the ODATA Query to limit what is returned?

I did look at the URL conventions link you gave but I found it rather impenetrable eg "Split undecoded query at "&" into query options, and each query option at the first "=" into query option name and query option value".

I looked at the URL Components section which covers : '?' '#' '/' '&' but not ';'. It is probably covered later on in the document. I think you might have to accept that most people are going to find standards document rather terse. I will try reading through it a bit more, but I am also learning several other technologies at the moment and it is a matter of time.. In my experience people learn better from seeing examples of a programming language rather than reading through the manual to learn every function.

Something got lost from my earlier post. What I was suggesting is that the TM1 Rest API documentation should start off by covering how to achieve some common tasks such as:

1) Running an MDX query which is probably going to entail getting at least a code and description for the rows and columns members, and whether they are consolidated or not. My assumption would be that the Title area Members would either be selected via point (3) below, or that people will have set a DefaultMember for the dimension in }HierarchyProperties. Therefore the return for the Title area is not so critical. On the Cell Value side, it is probably going to entail getting the Cell Set Id, Orginal, Numeric and Formatted Value and whether the cell is rule calculated. We can then format rows and columns that are consolidations differently and also cells that are rule calculated and we can know that they cannot be updated..Thanks to you I now know how to do that.

2) Update cell values using the Cell Set Id and Ordinal and also the bulk update via the function..

3) Select a dimension, then a hierarchy, then an element in that hierarchy, or a subset, then an element in that subset,

4) Run a process and pass parameters to it, and get a return value back - The process can do most of the other things that need to be done do like building dimensions, updating workflow, loading data, etc.There is possibly more scope for re-use using the Rest API, but you can get a fair amount of re-use in TI by calling processes. The only thing that can't be done easily is to return values.

5) Live promotion of an object via the Rest APi, in other words export it to JSON from one server and import the JSON into another.

If there were examples of the above, then that would cover quite a lot of what people need to do. There is already a reasonable set of examples around native views but as you can't use Named Hierarchies with those, they are likely to become less relevant.

We did start evaluating 2.0.9 last year. However, the person tasked with that got pulled on to other priorities, but we will be prioritising an upgrade this year. 

Regards

Paul Simon

------------------------------
Paul Simon

Original Message:
Sent: Tue January 05, 2021 08:18 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Yeah, 2.0.5 (TM1 11.3) is just a tad too old, this behaviour got introduced late June 2018 and was first released in TM1 11.4.0. So yeah, you might want to consider updating to a version from this decade ;-) 

As for the confusion, full well knowing that people prefer trial and error over reading specifications, but TM1 is 100% compliant to the OData specification which includes the URL conventions which outline exactly when to use what and what it is based on. IMHO the cool thing of complying to a standard like OData is exactly that we don't have to re-invent, document and elaborately explain what things mean and how to consume them as that's all done by the standard specification already.

Cheers,

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

Original Message:
Sent: Tue January 05, 2021 06:24 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

What I was seeing when I used that ODATA Query  $expand=Members($select=Name,Attributes) with a DIMENSION PROPERTIES restriction in the MDX was that it was returning all Attributes. It seemed that the ODATA Query was overriding the MDX rather than combining with it. This might be something that is fixed in later versions. We are still on 2.0.5. However, Hubert has given me a work around for that. 

Still finding the whole thing confusing. When do you use a comma as opposed to a slash as a opposed to a semi-colon as opposed to an ampersand. I think the documentation on the Rest API probably need a bit of re-focussing on general principles first then common applications, and then launch into the detail. There are a lot of things in there that I will probably never want to do with the Rest API like setting TM1S.CFG parameters. 

Regards

Paul Simon


------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 05:52 PM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

If you use a combination $expand=Members($select=Name,Attributes) and DIMENSION PROPERTIES in the MDX you will get the specific attributes (not all of them). You should specify the attributes you want on both axis.

The URL filtering of attributes only supports A-Z and 0-9 characters so it isn't really a general solution, as you know TM1 allows lots of special characters.

------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Mon January 04, 2021 07:42 AM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi Tim

Thanks for the reply but I had already tried that, but it still gives the problem of returning all Attributes which bloats the result set.

Hi Hubert

Thanks for the detailed reply.

As per your advice, if I use this

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),Tuples($expand=Members($select=Name,Attributes/ControlBudget,Attributes/Description))),Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

Then I get what I want ie the result set contains just the Attributes Control Budget and Description.

However, if I try to also ask for the Element Type as well then I get a syntax error

expand=Members($select=Name,$expand=Element($select=Type),Attributes/ControlBudget,Attributes/Description)

Syntax error at position 81, near "$" for $expand query option: Unexpected token. -

Even if I just go for 

​expand=Members($select=Name,$expand=Element($select=Type))

Then I get the same error. 

On its own as stated belore 

$expand=Members($expand=Element($select=Type))

This works but then I get all Attributes.

So how can I get just the Name for the Member, only two Named Attributes, and the Element Type? I know I always want more!

I may also be misunderstanding what you mean by a cellset. You seem to be saying that the Attributes are returned as part of the CellSet and are then elevated to the Tuple Members. However, the results that I am seeing show:

The Cells part of the resulting JSON only have simple property values and no complex types like attributes

The Attributes are under Axes\Tuples\Members\Attributes. Using the \ and comma I get just the 2 attributes that I selected

However, what I think of as the CellSet is just the Cells part of what is returned. This appears to be the part of the returned set that contains the CellSet ID that must be quoted, eg if you then want to update cell values.

Is my understanding of what a CellSet is correct, ie just the Cells part?

Regards

Paul Simon





------------------------------
Paul Simon

Original Message:
Sent: Mon January 04, 2021 04:23 AM
From: Hubert Heijkers
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

Let me start by saying Tim is absolutely correct, just wanted to elaborate on this for a sec to give a bit of background and answer some of your implicit questions as to why it works the way it does as well. 

While everything in the REST API is driven of an Entity Data Model, the data IN TM1 itself is actually a multi-dimensional model. And while we query all the meta data we deal with using OData query syntax the queries for data coming from the model described by this metadata are executed using MDX. The result of such MDX query is then represented using EDM constructs so it can be 'queried' (read: represented) using OData again.

Note that this is not unique by itself, albeit I think I was the first to do this using the OData standard but OLE-DB for OLAP (ODBO) did the same using OLE-DB as the base as well it's XML equivalent, XML for Analysis a couple of years later. The big difference with those two is that neither ODBO or XMLA had a fixed schema to work with and on top of that simply flattened the space whereas I had to define the resulting structure as part of the EDM (the REST API's metadata) with the only escape being the use of open types. The Attributes property is a complex type which is an open type and as such, if we followed OData standard, could have any property but for TM1's case we used it to map to the attributes defined in TM1 (this applies to any object type that can have attributes in TM1).

In other words, executing an MDX query (against the data in the model) results in the creation of a Cellset Entity that subsequently, in the same or subsequent requests, can be queried. Using the Dimension properties clause on an (any) axes causes the set of those properties (read: attributes in TM1 speak) to be 'included' in that Cellset. If no dimension properties were specified explicitly then implicitly all attributes are presumed to be included.
Now, the bit of a mind twister in all this is perhaps that this set of attributes is used to project the attributes property of the Members property on the Tuples on the individual axes. There are no implicit attributes 'invented' here (I can see however how you reached that conclusion) as they simply wouldn't exist and in open types properties (representing attributes here) that don't exist simply never show up anywhere. In hindsight, we could/should perhaps have invented some CellsetAxis specific property representing these attributes and leave the projections of those Member's Attributes alone??? But it felt like, still does, like a nice and intuitive way retrieve that information (not in the last place inspired by ODBO which effectively has two Member classes for this one just extending the other with one extra attribute only used in Cellset results). 

Quickly getting back to some of your questions/observations Paul:

- One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attributes, eg Description and Control Budget.
Following OData specification [apart from the omitting space in this case]; $select=Attributes/Description,Attributes/ControlBudget,Attributes/Etc
However, using the Dimension Properties clause in the MDX and simply selecting Attributes on the Member in the Tuple is the preferred way;-!

- Errors you were getting when using $expand=Attributes
These were simply because Attributes is a complex type and therefore it's a regular property and not a navigation property.

- $expand=Members($expand=Element($select=Type,Attributes)) works but you get the Attributes twice
Well, that's correct, you are asking for them twice. First, because you are omitting a $select on the Member, which implies including all properties, therefore including Attributes, which however would be limited if you used a Dimension Properties clause in the query, and then you expand the element of each member for which you explicitly select ALL Attributes again (note that the Attributes of the associated Element are not influenced by the MDX query whatsoever). 

Ok, hope that this helps you, and others that end up reading this, understand this topic a bit better. At the same time, I'm open to suggestions on improving this in a next version of the REST API, one that will likely be based on OData v4.01 which gives us more flexibility and freedom as well ;-).

Cheers,

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

Original Message:
Sent: Sun January 03, 2021 01:51 AM
From: Tim Ryan
Subject: Rest API MDX Query select more than one Attribute

Hi Paul,

You are doing it the right way with using dimension properties, you need to use $select rather than $expand to get the attributes because it is a normal property of a member and not a navigation property. You URL should look like this $expand=Members($select=Name,Attributes), then use MDX dimension properties to filter the ones you want.


------------------------------
Tim Ryan - Cubewise

Original Message:
Sent: Sat January 02, 2021 04:30 PM
From: Paul Simon
Subject: Rest API MDX Query select more than one Attribute

Hi all

Happy New Year.

I am trying to write a MDX Rest API Query that will just return the selected attributes. One Attribute, or all Attributes I can do, but I cannot figure out how to return two specific Attriutes, eg Description and Control Budget.

I am using PA 2.0.5 on premises.

I am executing an MDX Query using the following MDX:

SELECT
CROSSJOIN( {[N_Effective_Mth].[e_F4_Curr Year Pers] }, { [N_Version].[Curr Forecast] } ) on columns ,
NON EMPTY CROSSJOIN ( { [N_UCoA_Programme].[d_UCoA Programmes_Base_Excl_Std] }, {[N_UCoA_Account].[d_UCoA Accounts_Base_and_Top] } ) DIMENSION PROPERTIES [N_UCoA_Programme].[Control Budget] on rows
FROM N_Central WHERE ( [N_UCoA_Org_Unit].[1000TT] )

In the above the [d_UCoA ... are MDX Subsets which each give a large number of elements.

The following ODATA Query gives a result that includes just the Description Attribute. It seems to ignore the MDX which only asks for the Control Budget attribute

ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes/Description))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

If I change the query to get just Control Budget (remembering to remove the space)

Tuples($expand=Members($select=Name,Attributes/ControlBudget))),

That works. Interesting as the spec applies to the whole Axis and only the Programme dimension has the Control Budget Attribute, not the Account dimension, I find that the result invents a Control budget Attribute on the Account dimension with a null value.

If I change the ODATA Query to omit '/ControlBudget'

"ExecuteMDX?$expand=Axes($expand=Hierarchies($select=UniqueName),
Tuples($expand=Members($select=Name,Attributes))),
Cells($select=Ordinal,Value,FormattedValue,RuleDerived,Consolidated,Updateable)"

The query works but regardless of the MDX, it returns every possible Attribute.

To a certain extent I can filter out the attributes that I don't want before returning the result to the client, but I am concerned about a potentially very large result set on the server as these dimensions have a lot of attributes some of which have a lot of text.

If I modify the query to have

$expand=Members($expand=Attributes)

I then get the error

Syntax error at position 86, near ")" for $expand query option: Expecting '/' after property of complex type in expand path.

This produces the same error

$expand=Members($expand=Attributes($select=Caption,Description))

Both attributes given above appear on both of the dimensions on the axis.

Is there a way around this?

Is my general approach right? Should I be running the MDX, getting a CellSet Id and then doing further queries to get the detail?

I ideally want to combine this with the following:

$expand=Members($expand=Element($select=Type))

So that I can get an indication as to whether a Member is Consolidated or Numeric (Base level)

This works

$expand=Members($expand=Element($select=Type,Attributes))

but then I get a full set of Attributes at both the Members and the Element levels. The key problem seems to be how to select Attributes.

Is this possible?

Ideally I would prefer to do it in the MDX rather than ODATA. However, it seems that the MDX cannot produce an Attribute that the ODATA does not include. However, the MDX DIMENSION PROPERTIES seems to have no effect on limiting the Atttributes in the query result.

Regards






------------------------------
Paul Simon
------------------------------

#PlanningAnalyticswithWatson