Cellsets

A cellset is the result of an execution of a view or an MDX expression, representing a snapshot of your data in a certain point in time. You can use the cellset ID within a session instead of running a view or MDX expression multiple times.

When a view is executed, a snapshot of the data in the cube at a certain point in time is displayed based on the dimensions specified for the view.

Find cell values in a cellset

You can use the $expand query option to return all of the cell values in the cellset. To retrieve columns and rows, see Column and row dimensions.

POST /api/v1/Cubes('Metrics cube-Sales')/Views('Metrics cube-Sales')/tm1.Execute?$expand=Cells

Returns the cell values, starting at ordinal 0, for the view specified.

{
    "@odata.context": "../../$metadata#Cellsets(Cells)/$entity",
    "ID": "kyMtu_8DAIB7AQAg",
    "Cells": [
        {
            "Ordinal": 0,
            "Value": -1,
            "FormattedValue": "($1.00)"
        },
        {
            "Ordinal": 1,
            "Value": -1,
            "FormattedValue": "($1.00)"
        },
        {
            "Ordinal": 2,
            "Value": 1723646.69,
            "FormattedValue": "$1,723,646.69"
        },
        {
            "Ordinal": 3,
            "Value": 4405316.5,
            "FormattedValue": "$4,405,316.50"
        },
        ...
        }
    ]
}

Ordinal values start from left to right, top to bottom on a chart display, as represented in the following example:

Run a view and expand the cellsets

The service $metadata document defines actions and specifies where the actions can be applied in the data model.

The Execute action is bound to the View entity type and provides a snapshot of the contents of the view (cellsets) at the point in time that the action runs:

   POST /api/v1/Cubes('plan_BudgetPlan')/Views('plan_budget_input')/tm1.Execute

The result of the action provides the value of the ID property of the Cellset:

{
   "@odata.context": "../../$metadata#Cellsets/$entity",
   "ID":"DWjRKwMCAIAFAAAg"
}

Only the ID property is included in the result because the ID is the only primitive property that is defined for a Cellset.

Add the $expand option to include the values and ordinals of the cells within the cellset:

   POST /api/v1/Cubes('plan_BudgetPlan')/Views('plan_budget_input')/
         tm1.Execute?$expand=Cells

Expand the cells to display the value of all the cells in the cellset in the result. Use $top and $skip to minimize the number of cells returned.

{
    "@odata.context": "../../$metadata#Cellsets(Cells)/$entity",
    "ID": "DWjRKwMCAIAdAAAg",
    "Cells": [
        {
            "Ordinal": 0,
            "Value": 146938800.55,
            "FormattedValue": "146,938,801"
        },
        {
            "Ordinal": 1,
            "Value": 35723553.53,
            "FormattedValue": "35,723,554"
        },
        {
            "Ordinal": 2,
            "Value": 11256636.11,
            "FormattedValue": "11,256,636"
        }
        ...
    ]
}

Get the cells of a cellset in multiple partitions

You can use the bound function GetPartition to get the cells of a cellset in multiple partitions.

You can use the GetPartition function to manage processing of area of the crosstab based on a defined boundary.

The cells property of the cellset can be used with the bound function GetPartition( Begin=<ordinal>,End=<ordinal>) in a Get. For example:

/api/v1/Cellsets('FBxz3b4HAIBEAAAg')/tm1.GetPartition( Begin=0, End=10 )

The Begin ordinal defines the cell ordinal that makes up the left or right corner of the closest rectangular area of your cells, and the End ordinal specifies the other edge.

In this example, (Begin=0,End=10) gives you the column : cells 0,2,4,6,8,10.

If you supply (Begin=1,End=10), you get cells 0-11.

Use $top and $skip to minimize the number of cells returned.

Update a single cell value

You can update a single cell value by using the PATCH operation.

To update the value of the cell, determine the value of the ID property of the cellset that the cell belongs to, and determine whether it is a value that can be updated.

   POST /api/v1/Cubes('plan_Budgetplan')/Views('Budget input detailed')/
         tm1.Execute?$expand=Cells($select=Ordinal,FormattedValue,Consolidated)

The request in the previous example returns the cells in the cellset as shown below.

{
    "@odata.context": "../../$metadata#Cellsets(Cells(Ordinal,FormattedValue,Consolidated))/$entity",
    "ID": "DWjRKwMCAIAFAAAg",
    "Cells": [
        {
            "Ordinal": 0,
            "FormattedValue": "938,285",
            "Consolidated": true
        },
        {
            "Ordinal": 1,
            "FormattedValue": "315,513",
            "Consolidated": false
        },
        {
            "Ordinal": 2,
            "FormattedValue": "311,041",
            "Consolidated": false
        },
        ...

The Consolidated property identifies if the value is a result of an aggregation or other operation based on other cell values. If the property is true, you cannot change the value unless Updateable=true. If a consolidated value is updateable, you can update the value with a spreading command.

If the property is set to Consolidated=false, use a PATCH operation to update the cell, specifying the ordinal and the new value.

   PATCH /api/v1/Cellsets('DWjRKwMCAIAFAAAg')/Cells

   [
      {
         "Ordinal": 1,
         "Value": 315999
      }
   ]

To verify that the cell is updated properly, repeat the GET operation.

Update many cell values

You can use the Update action to update multiple cells.

Since 10.2.RP2.FP4, the following Update actions are available:

An Update (Cellset) action that is bound to the cellset
An Update (Cube) action that is directly bound to the cube
An Update (Cube) action that isn't bound to anything

These actions let you update one cell at a time. This cell can be a consolidated cell when you are using a spreading command. When you use the Update action on a cellset, the cellset also defines the bounds of some of the spreading commands and the cell ordinal can be used to specify which cell in the cube needs to be updated. The other two versions of the Update action require you to specify the cell by using elements references instead.

The Update(Cube) actions accept an array of sets of arguments. You can make multiple updates in one request, as if you had called the same Update action multiple times.

Note: Using an Update action with an array of arguments is not OData compliant and therefore this approach is not documented in the metadata document.

Delete a cellset

If you run a view or MDX expression multiple times, it can yield different results each time and can have an impact on memory resources. After operations on a cellset finish, you must delete the cellset if you do not end the session, as shown in the following example:

   DELETE /api/vi/Cellsets('ID#')

When a session ends, the cellset is deleted automatically, if it is not explicitly deleted beforehand.