Sep 5, 2016

Using $expand and $select with the TM1 REST API

In the previous article we went through some simple GET requests to retrieve a list of cubes and a single cube. We are now going to expand on those examples by using two OData query options: $expand and $select

HOW TO USE $EXPAND

The $expand query option is used to display related properties on a object when executing a GET request. For example if we look at the Cube entity type in the $metadata document (https://localhost:8881/api/v1/$metadata) we will see that each cube has:

  • A unique key (the Name).
  • 5 properties: <Property ….
  • 5 navigation properties: <NavigationProperty ….

It is the navigation properties that we can use the $expand query option for, in fact, the navigation properties will ONLY be returned when you use $expand. Let’s try an example to “expand” the dimensions for a given cube:

https://localhost:8881/api/v1/Cubes(‘General%20Ledger’)?$expand=Dimensions

This will return the Dimensions for the cube as well all of the other standard Properties:

You can also “expand” multiple Navigation Properties by separating them by a comma:

https://localhost:8881/api/v1/Cubes(‘General%20Ledger’)?$expand=Dimensions,Views

The above query will result in BOTH the Dimensions and Views being returned for the cube:

HOW TO USE $SELECT

The next query option I am going to talk about is $select$select does the opposite of $expand, it reduces the content returned from TM1 by “selecting” the properties we want to return. For example, if we want to only return the name of our cube we would use $select this way:

https://localhost:8881/api/v1/Cubes(‘General%20Ledger’)?$select=Name

NOTE: The $select option is important way of optimising your REST API queries. A smaller response results in fastest network transfer and less time parsing the result into something you can use.

Just like the $expand option you can also “select” multiple properties:

https://localhost:8881/api/v1/Cubes(‘General%20Ledger’)?$select=Name,LastSchemaUpdate

HOW TO COMBINE $EXPAND AND $SELECT

You can combine the use of these two query options to return exactly what you need. For example, if we wanted to return the name of the General Ledger cube and its dimensions we would do the following:

https://localhost:8881/api/v1/Cubes(‘General%20Ledger’)?$select=Name&$expand=Dimensions

You will notice that we get all of the properties of the dimension, you can use $select on Navigtation Properties as well. To get just the names of dimensions on the cube we would use this:

https://localhost:8881/api/v1/Cubes(‘General%20Ledger’)?$select=Name&$expand=Dimensions($select=Name)

GETTING A LIST OF ALL CUBES AND THEIR DIMENSIONS

We can use these same query options to retrieve a full list of cubes with their dimensions. Just remove the (‘General Ledger’) part like so:

https://localhost:8881/api/v1/Cubes?$select=Name&$expand=Dimensions($select=Name)

Related content

Loading related content