Consolidating using dimension hierarchies
The data you import into a cube provides a snapshot of your business at a specific level of detail. For example, you might import your weekly or monthly sales for your surfboards in dollars for a specific city in which they were sold. The dimension elements that identify these data points are simple or leaf-level elements in each dimension. These data points can be surfboards sold in one week and in a particular city.
By using dimension hierarchies, you can easily aggregate numeric data into categories that are meaningful in your analyses. Each category corresponds to an aggregation of detail for two or more elements in a dimension. For example, you could create quarterly elements that sum monthly sales amounts. In TM1, elements that represent aggregations are called consolidated elements or consolidations.
We'll speak more about hierarchies later in this chapter.
Element attributes
In addition, to define an element's type (numeric, consolidation, or string), elements can have attributes defined for them. If elements identify data in a cube, then think of the element attributes describing the elements themselves.
For example, let us say that some of your users would like to display an account using the account name followed by the account number. Other users would like to display only the account name. You can define an alias attribute for each of these requirements. In fact, you can define as many alias attributes as you need.
I can have an account number of "01-0000-00001" and define multiple aliases so that the user can view the element as any of the following:
- "01-0000-00001"
- "01-0000-00001 - Long Surfboard"
- "Long Surfboard"
Some interesting uses for attributes include:
- To define features of elements. For example, an employee may have attributes that include "title", "hire date", or "department"
- To provide alternative or "friendly" names, or aliases. For example, an accounting code of "02-0000-00001" may have an alias of "salary and wages"
- To control the display format for the numeric data. This is a clever idea and we will drill into this later in this chapter
An alias attribute may also be used to present data in different languages!
You can select elements by attribute value in the Subset Editor. You can also display element names in TM1 windows using their aliases.
To create attributes and assign attribute values, you use the Attributes Editor.
When adding attributes using the Attributes Editor, you will notice that it "defaults" to an attribute type of string, so be careful to select the desired type before proceeding. In most cases, TurboIntegrator processes will be the tool used to add, update, and delete your dimension attributes. You use the following programming functions:
A key point to know is that, if you try to view or update dimensions attributes and there are a large number of elements in the dimension when you open the Attributes Editor, you will receive a message as shown in the following screenshot:
The alternative is to access the attributes of this dimension through the attributes cube instead, as it is much faster:
This is another important point. The }ElementAttributes cubes are known as Cognos TM1 control cubes. These cubes are automatically generated by TM1. As an application developer you can either create a new (lookup) cube or use a control cube to look up data, depending upon your needs. Lookup and control cubes are discussed later in this chapter.
Some key attribute terminology and concepts you should be able to recognize are descriptive attributes, alias attributes, and when to use an attribute versus an additional element. Let us briefly touch on these.
Descriptive attributes are simply the attributes which are data that describe the data.
For example, consider some attributes for selecting a surfboard:
These attributes provide alternative names for elements:
It can be tempting to add many attributes to describe your elements and in most cases this is fine as you can filter your data by attribute value, however you should consider how your data is going to be used and presented. Sometimes it is more appropriate to create elements rather than attributes and sometimes even additional dimensions.
For example, board length is an attribute of surfboard models. The 6.6 boards often outsell the other length boards. If you create one element per board and another dimension with elements for each length, you can use TM1 to track surfboard sales by the length of the board. If you combine sales into a single board length, you might lose valuable detail.
As I mentioned earlier in this chapter, a clever use for element attributes is formatting what is displayed in the Cube Viewer. When you create a dimension, an attribute named format is created for you automatically by TM1. This attribute can be used to set a display format for each individual numeric element.
Display format attributes can also be set programmatically with a TurboIntegrator process using the AttrPutS function. Just remember to add the c: to the format string to indicate that it is a custom format:
AttrPutS('c:###,###.00', myDimensioName, myElementName, 'Format');
Referring to the IBM Cognos documentation, you see that the numeric data can be displayed in the following formats:
The Cube Viewer will display the format to use:
- Elements in the column dimension are checked for formatting.
- Elements are checked in the row dimension for display formats.
- Elements are checked in the title dimension for display formats (left to right).
The current view formatting is used