Cubes and views

A cube is the basic container for data and a view defines how a cube's dimensions are arranged.

View a cube

You can use the following GET request to view the Metrics cube-Sales:

    GET /api/v1/Cubes('Metrics cube-Sales')

The GET request returns the following Metrics cube-Sales cube:

{
    "@odata.context": "$metadata#Cubes/$entity",
    "Name": "Metrics cube-Sales",
    "Rules": "#Region System\nFEEDSTRINGS;\nSKIPCHECK;\nUNDEFVALS;\n#EndRegion\n\n#Region ...
    "LastSchemaUpdate": "2014-05-23T12:45:14.016Z",
    "LastDataUpdate": "2014-05-23T12:45:14.015Z",
    "Attributes": {
        "Caption": "Metrics cube-Sales",
        "Caption_Default": "Metrics cube-Sales",
        "CUBE_TYPE": "METRICS"
    }
}

Display the available views in a cube

You can use the following GET request to display views in the Metrics cube-Sales cube:

    GET /api/v1/Cubes('Metrics cube-Sales')/Views?$select=Name

The GET request lists the available views for the Metrics cube-Sales cube:

{
    "@odata.context": "../$metadata#Cubes('Metrics%20cube-Sales')/Views(Name)",
    "value": [
        {
            "@odata.type": "#ibm.tm1.api.v1.NativeView",
            "Name": "All"
        },
        {
            "@odata.type": "#ibm.tm1.api.v1.NativeView",
            "Name": "Metrics cube-Sales"
        },
        {
            "@odata.type": "#ibm.tm1.api.v1.NativeView",
            "Name": "View-Sales"
        }
    ]
}

Get the specification of a view

You can use the following GET request to see the specification of the Metrics cube-Sales view:

   GET /api/v1/Cubes('Metrics cube-Sales')/Views('Metrics cube-Sales')

The result provides the specification of the view. Columns, Rows, and Titles are complex types, which in turn, contain navigation properties. As a result, they are not shown by default:

{
    "@odata.context": "../$metadata#Cubes('Metrics%20cube-Sales')/Views/ibm.tm1.api.v1.NativeView/$entity",
    "@odata.type": "#ibm.tm1.api.v1.NativeView",
    "Name": "Metrics cube-Sales",
    "Columns": [
        {}
    ],
    "Rows": [
        {}
    ],
    "Titles": [
        {},
        {},
        {},
        {}
    ],
    "SuppressEmptyColumns": false,
    "SuppressEmptyRows": false,
    "FormatString": "0.00"
}

Columns, Rows, and Titles are type ViewAxisSelection, that defines a navigation property to the Subset of the dimension.

   <ComplexType Name="ViewAxisSelection">
      <NavigationProperty Name="Subset" Type="ibm.tm1.api.v1.Subset" />
   </ComplexType>

Generally, a cube is displayed as a pivot table, such as the Cube Viewer in TM1 Architect. Suppress buttons in TM1 Architect correspond to the Suppress properties of a view.

Pivot table showing a cube.

Use options to view specific properties

To minimize the amount of data that is returned in a result, you can use a $select option to specify which properties of the entity to include.

You can use the following GET request to list cube names:

    GET Cubes?$select=Name

In the result, only the names of the cubes are returned.

{
   "@odata.context": "$metadata#Cubes(Name)",
   "value": [
      {
          "Name": "plan_BudgetPlan"
      },
      {
          "Name": "plan_BudgetPlanLineItem"
      },
      {
          "Name": "plan_Control"
      },
      ...
    ]
}

You can select more than one property to return in the result. For example, if you specify select=Name,Dimensions, the GET request returns the following result:

{
    "@odata.context": "$metadata#Cubes(Name,Dimensions)",
    "value": [
        {
            "Name": "plan_BudgetPlan",
            "Dimensions@odata.navigationLink": 
                  "Cubes('plan_BudgetPlan')/Dimensions"
        },
        {
            "Name": "plan_BudgetPlanLineItem",
            "Dimensions@odata.navigationLink": 
                  "Cubes('plan_BudgetPlanLineItem')/Dimensions"
        },
        ...
    ]
}

In the previous example, the navigation property that provides a link to the dimensions are returned instead of the actual dimensions.

List dimensions in a cube

When an entity contains navigation properties, a query operation returns only the navigation references to the contained contents, not the entities themselves.

In the following example, only references to the dimensions that are contained within cubes are returned. The dimensions are not contained in the cube, but are referenced at another location.

    GET Cubes('plan_BudgetPlan')/Dimensions

To include the dimensions as part of the payload that is returned, use the $expand query option to include them.

    GET Cubes?$select=Name&$expand=Dimensions

In the previous example, the $select and $expand query options are separated with the ampersand symbol (&). This request returns the name of the cube and the actual dimension instead of a reference to the dimension:

{
    "@odata.context": "$metadata#Cubes(Name,Dimensions)",
    "value": [
        {
            "Name": "plan_BudgetPlan",
            "Dimensions": [
                {
                    "Name": "plan_version",
                    "UniqueName": "[plan_version]",
                    "Attributes": {
                        "Caption": "plan_version"
                    }
                },
                ...

Expand dimensions and hierarchies in a cube

You can expand dimensions and hierarchies in a cube by using the $expand query option.

To recursively expand items in a resource, use the $expand query option. In the following example, the cubes are listed by Name and the dimensions that are contained are listed in addition to the hierarchies contained within each dimension.

    GET /api/v1/Cubes('plan_BudgetPlanLineItem')?$select=Name&
      $expand=Dimensions($select=Name;$top=2;
      $expand=Hierarchies($select=Name))

In addition to the $select option, you can use the $top=2 query option to restrict the results to a smaller subset. Separate these options with a semi-colon (";") character. The opening and closing parenthesis contains the scope of the options to the context of the entity that precedes the parenthesis. For example, the last instance of ($select=Name) is contained within open and close parenthesis after $expand=Hierarchies. So this portion of the query, $select=Name;$top=2; $expand=Hierarchies, applies only to Dimensions while $select=Name applies only to Hierarchies.

{
    "@odata.context": "$metadata#Cubes/$entity",
    "Name": "plan_BudgetPlanLineItem",
    "Dimensions": [
        {
            "Name": "plan_version",
            "Hierarchies": [
                {
                    "Name": "plan_version"
                }
            ]
        },
        {
            "Name": "plan_business_unit",
            "Hierarchies": [
                {
                    "Name": "plan_business_unit"
                }
            ]
        }
    ]
}

Hide hierarchies

The Visible property of a Hierarchy entity has been extended to allow a client to filter any collection of hierarchies.

For example:

GET /api/v1/Dimensions('dim')/Hierarchies?$filter=Visible eq true

By default, the Visible property is determined by the Visible dimension property (currently this is in the }DimensionProperties cube, which includes every individual hierarchy). This property defaults to True.

If a control cube exists with the name }HierarchyVisibility_{{DIMNAME}}, which has two dimensions: }Hierarchies_{{DIMNAME}} and }Groups, then more specific values here will overwrite the default dimension property (if TRUE or FALSE is written to the appropriate cell). Similar to security, if a user belongs to multiple groups, and any of those groups sets the hierarchy as visible, then the hierarchy will be visible.

Determining the value of the Visible property uses the following logic:

  • Is there a value of TRUE anywhere in the }HierarchyVisibility_{{DIMNAME}} cube for this hierarchy and any group the current user belongs to? Then, Visible = true.
  • Is there a value of FALSE anywhere in the }HierarchyVisibility_{{DIMNAME}} cube for this hierarchy and any group the current user belongs to? Then, Visible = false.
  • Is the value of the VISIBILITY dimension property (note that this is per hierarchy) set to TRUE? Then, Visible = true.
  • Is the value of the VISIBILITY dimension property set to FALSE? Then, Visible = false.
  • Otherwise, Visible = true.

Use the Or operator to specify cube rules across hierarchies

You can use a single rule statement to cover a set of same-named consolidated elements across hierarchies of the same dimension. This approach gives you greater flexibility with alternate hierarchies.

Suppose that your dimension has two hierarchies, H1 and H2, and each hierarchy has a consolidated element named US. Previously, a modeler would need to replicate statements to specify a rule calculation for US, as in the following example:

['Dimension':'H1':'US'] = C:<formula>;
['Dimension':'H2':'US'] = C:<formula>;

In this example, the <formula> are identical. You can now write a single statement that triggers on either of the US elements by using the extended rule area grammar.

To illustrate, you need to understand how area definitions, AND operators, and OR operators work.

Area definitions

An area definition is an expression in square brackets, comprising the left side of a cube rule statement. For example:

[ 'element' ] = <formula>;

This statement is eligible for evaluation when a cell retrieval request includes 'element' among the cell coordinates. The statement is eligible because the cube's rule file might have other statements upstream that take precedence. To simplify the concept, remember that if this were the only rule statement for this cube, then it's guaranteed that the statement will be evaluated as long as 'element' is among the cell retrieval coordinates.

AND Operator

An area definition can contain a comma-separated list of element references. Such a list represents an AND operator that further restricts the eligibility of the rule statement. For example:

['element', 'measure1' ] = <formula>;

This statement is eligible only if both 'element' AND 'measure1' coincide among the cell retrieval coordinates. In typical practice, if there were two rule statements, one with area ['element'] and the other with the area ['element', 'measure1'], the latter statement would be placed upstream in the rule file. This way, coordinates with ['element'] and any other measure than 'measure1' would trigger evaluation of the ['element'] rule while those that have the 'measure1' in addition would run the more specific statement. If these statements were in the opposite order, then the ['element', 'measure1'] statement would never run, it is shadowed by the more general statement that precedes it.

OR Operator

The top-level comma-separated list of terms inside the enclosing [] characters of the area definition represent coordinates that must appear together in the retrieval coordinates to make that rule statement eligible. In the previous examples, the terms are simple element references like 'element' and 'measure1'. But each term can optionally be list, enclosed in curly braces {}, that represent different elements from the same dimension. These list terms must be simple element references and they represent alternatives each of which would make the statement eligible if they appear in the retrieval coordinates. This is an OR operation, as illustrated by this example:

['element', { 'measure1', 'measure2' } ] = <formula>;

This area definition specifies that if the retrieval coordinates include 'element' from its dimension, AND ( 'measure1' OR 'measure2' from the measures dimension) then the rule statement is eligible.

Dimension and Hierarchy Qualification

Previously, dimension qualification of element references is recommended, though it is also optional. Dimension qualification of an example would look like this:

[ 'Products':'element', 'Measures':{ 'measure1', 'measure2' } ] = <formula>;

The terms in the AND list must be from different dimensions of the cube, while the terms in the OR list must be from the same dimension.

Hierarchy qualification is now recommended, for example:

[ 'Products':'H2':'element', 'Flavors':'H1':{ 'flavor1', 'flavor2' } ] = <formula>;

However, the OR operator {} is limited to elements within the same hierarchy. It should be more general than this, since elements from different hierarchies of the same dimension, are still elements from the same dimension.

You can now take advantage of more flexible use of the OR operator in rule area definitions.

Examples

The following examples illustrate the various permitted forms of the OR {} operator.

The following examples show an unqualified OR list, and a dimension qualified OR list. In all cases, the elements in the list must resolve to being from the same dimension.

{ 'e1', 'e2' }
'd':{'e1', 'e2' }

The following examples are just like the previous set of examples but now include the ability to have a list qualifier that is both dimension and hierarchy qualified. The elements in the list must come from the same dimension. But there is no ability to provide an explicit hierarchy-qualified list that mixes elements from different hierarchies of the dimension.

{ 'e1', 'e2' }
'd':{ 'e1', 'e2' }
'd':'h':{ 'e1', 'e2' }

The following three examples show the ability to provide dimension and hierarchy qualification inside an unqualified list. The list elements must come from the same dimension, but now they can explicitly mix hierarchies of the same dimension. If a list qualifier is present, then this restricts what kind of qualification is allowed inside the list:

{ 'e1', 'e2' }
{ 'd':'e1', 'd':'e2' }
{ 'd':'h1':'e1', 'd':'h2':'e2' }

In the following two examples, the list elements are restricted to being at most hierarchy qualified. A three-part name won't compile because the dimension is already expressed by the list qualifier.

'd':{ 'e1', 'e2' }
'd':{ 'h1':'e1', 'h2':'e2' }

The following example is in the current form.

'd':'h':{ 'e1', 'e2' }

Delete a cube

You can use the DELETE operation to remove a cube.

Any GET action for a specific entity can be replaced with a DELETE action if the logged in user has the appropriate permissions. In the following example, the 'plan_BudgetPlan' cube is deleted with the same resource path as would be used with a GET action.

   DELETE /api/v1/Cubes('plan_BudgetPlan')

You can confirm a DELETE action by attempting a GET action on the same resource path, which returns an error message if the deletion was successful:

   {"error":{"code":"","message":"'plan_BudgetPlan' can not be found in collection
         of type 'Cube'."}}

Iterate through SQL rowsets to improve 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:

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"
        }
    }
}