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:
![Pivot table showing ordinal values.](images/pivot_table_cellsets.jpg)
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.
0 1
2 3
4 5
6 7
8 9
10 11
12 13
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.
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.