Iterate through SQL rowsets to speed up drill through queries

You can use TM1® REST API actions and entities to execute a relational drill through and retrieve results as a rowset entity. You can use the rowset to iteratively retrieve subsequent subset rows. This approach improves the performance of retrieving drill through results.

Actions

In Planning Analytics version 2.0.8, you can use the following actions to execute relational drillthroughs but return rowsets:

Use ExecuteRelationalDrillthroughWithRowset to execute a relational drill through to get a rowset of a relational table.
Use RelationalDrillthrough.ExecuteWithRowset to drill into a cell to get a rowset of a relational table.

Entity types

In Planning Analytics version 2.0.8, you can use the following entity types:

A Rowset represents the result of an execution of a relational drill through. A rowset is session-scoped and must be deleted after you use it. Closing a session invalidates all of its rowsets.
A RowsetRow represents a row in a rowset. The RowsetRow can be retrieved in subsequent requests. These subsequent requests do not cause the drillthrough process to be re-executed.

Example: Drill into a cell to get a rowset

POST /api/v1/Cubes('x')/Views('x')/tm1.Execute
POST /api/v1/Cellsets('x')/Cells(N)/DrillthroughScripts('x')/tm1.ExecuteWithRowset
GET /api/v1/Rowsets('x')?$expand=Rows($top=1000)
DELETE /api/v1/Rowsets('x')

Example: Use a transient process to open a CSV rowset

POST /api/v1/ExecuteRelationalDrillthroughWithRowset?$expand=Rows($select=A,B)
{
    "DrillthroughProcess":
    {
        "EpilogProcedure": "#****Begin: Generated Statements***\r\nReturnCsvTableHandle;\r\n#****End: Generated Statements****",
        "DataSource": {
            "Type": "ASCII",
            "asciiDelimiterChar": ",",
            "asciiDelimiterType": "Character",
            "dataSourceNameForServer": "CSV_FILENAME.csv"
        }
    }
}