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