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.

Preview a datasource

You can use the REST API actions ExecuteCubeDrillthrough and ExecuteRelationalDrillthrough to preview a datasource.

These actions takes two parameters:

A TI Process
The parameters for executing the TI Process

The TI Process can be a reference to an existing Process or a transient one. The parameters are optional.

The process of the ExecuteCubeDrillthrough returns a view handle that is used by the action to a create a Cellset. The process of the ExecuteRelationalDrillthrough returns an SQL or CSV handle that is converted to a collection of DrillthroughRows.

Example 1: Preview an ODBC (SQL) datasource

POST /api/v1/ExecuteRelationalDrillthrough
{
  "DrillthroughProcess":
  {
    "DataSource": {
      "Type": "ODBC",
      "dataSourceNameForServer": "Test",
      "query": "SELECT * FROM [Car Sales$];"
    },
    "EpilogProcedure": "ReturnSqlTableHandle;"
  }
}

Example 2: Drill to a drillthrough object (a TI process that returns view handle)

POST /api/v1/ExecuteCubeDrillthrough
{
  "DrillthroughProcess":
  {
    "Name": "Tmp_Drill",
    "EpilogProcedure": "#****Begin: Generated Statements***\r\nReturnViewHandle('C2_D2D3','C2_V3');\r\n#****End: Generated Statements****",
    "DataSource": {
      "Type": "TM1CubeView",
      "dataSourceNameForServer": "C2_D2D3",
      "view": "C2_V3"
    },
    "Parameters": [
      { "Name": "cubename", "Value": "C1_D1D2" },
      { "Name": "D1", "Value": "D1_E1" },
      { "Name": "D2", "Value": "D2_E1" }
    ]
  },
  "Parameters": [
    { "Name": "cubename", "Value": "C1_D1D2" },
    { "Name": "D1", "Value": "D1_E1" },
    { "Name": "D2", "Value": "D2_E2" }
  ]
}

Example 3: Drill to an existing drillthrough object

POST /api/v1/ExecuteCubeDrillthrough
{
  "DrillthroughProcess@odata.bind": "Processes('ABC')"
}

To support drillthrough to CSV, the TI function ReturnCSVTableHandle returns the handle of the CSV datasource. The REST API reads the datasource and converts the data to drillthrough rows.

The naming scheme of the CSV columns follows the Excel scheme: A, B, ... Z, AA, AB, and so on.

For example:

POST /api/v1/ExecuteRelationalDrillthrough?$top=1
{
  "DrillthroughProcess":
  {
    "DataSource":
    {
      "Type": "ASCII",
      "asciiDecimalSeparator": ".",
      "asciiDelimiterChar": ",",
      "asciiDelimiterType": "Character",
      "asciiHeaderRecords": 1,
      "asciiQuoteCharacter": "\"",
      "asciiThousandSeparator": ",",
      "dataSourceNameForServer": ".\\data_load\\budget_input\\Plan_Load_Budget_ascii.csv"
     },
    "EpilogProcedure": "ReturnCSVTableHandle;"
  }
}

Response:

{
  "@odata.context": "$metadata#Collection(ibm.tm1.api.v1.DrillthroughRow)",
  "value": [
    {
      "A": "Jan-2003",
      "B": "105",
      "C": "10120",
      "D": "41101",
      "E": "658008"
    }
  ]
}

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.