Creating Dimensions and Cubes
Introduction
What tables are to relational databases in the way cubes are to TM1. Almost all data stored in TM1 is stored in and accessed from cubes.
There are two ways to create cubes:
- Empty cube: You can create an empty cube by selecting (at least) two dimensions from the list of existing dimensions in the Creating Cube window to create a new cube with no data.
- External data sources: You can create a cube and load it with data by using TurboIntegrator to identify and map dimensions and data from an external data source to a new or existing cube.
Each and every TM1 cube must have at least two dimensions and a maximum of 256 dimensions.
Dimensions
You create a cube with dimensions, and dimensions identify how to organize the information or data that you want to track and report on. Each element in each dimension identifies the location (or “x-y coordinate”) of a cell in a cube.
There are four ways to create dimensions:
- Use the Dimension Editor: You simply add your elements (as well as creating and rearranging consolidations of your elements) manually within the Dimension Editor window.
- TurboIntegrator: You can use your custom TurboIntegrator processes to import element names from an ASCII, ODBC, cube view, or dimension subset source.
- Importing data into a new cube: Of course, it should also be mentioned that TurboIntegrator processes can be used to map input rows from a data source to a cube and then identify the input columns that supply the cell values and the elements that identify the cell location.
- Dimension worksheets: Finally, an older, less flexible method of creating dimensions is the use of modified Microsoft Excel worksheets to list the elements and hierarchical relationships for one dimension.
Elements
Dimensions are made up of elements. TM1 currently supports three types of elements:
Editing element properties
Warning! Once your dimension is a part of the cube that contains data you must be very careful while changing any element properties to prevent loss of any data loaded to that element. The Dimension Element Properties dialog is used to make the changes to the selected element.
Aggregations
One of the key things to understand is that TM1 will quickly and efficiently aggregate numeric element values for your reporting. Aggregation is automatically applied in TM1 while building a dimension using something called hierarchies. A hierarchy is a way to organize data at different levels of aggregation.
It is important to understand that aggregation calculations will be based upon each element’s “weight”. IBM refers to this as the element’s weight factor. Weight factors determine the contribution of an element to a consolidation. The default element of weight is one.
Consolidating using dimension hierarchies
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. In TM1, elements that represent aggregations are called consolidated elements or consolidations.
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.
Some interesting uses for attributes include:
- To define features of elements.
- To provide alternative or “friendly” names, or aliases.
- To control the display format for the numeric data.
TurboIntegrator processes will be the tool used to add, update, and delete your dimension attributes. You use the following programming functions:
- AttrInsert: To add a new attribute
- AttrPutN or AttrPutS: To update the attribute which can be numeric or string value
- AttributeDelete: To remove an existing attribute
The alternative is to access the attributes of this dimension through the attributes cube instead, as it is much faster:
- Select View | Display Control Objects.
- Open the cube called }ElementAttributes_dimension.
- Modify the required fields like in any cube!
This is another important point. The }ElementAttributes cubes are known as Cognos TM1 control cubes. These cubes are automatically generated by TM1
Descriptive attributes
Descriptive attributes are simply the attributes which are data that describe the data.
Alias attributes
These attributes provide alternative names for elements:
Display format attributes
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’);
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).
Creating dimensions using the Dimension Editor window
Follow the next steps to create dimensions using the Dimension Editor window:
- Click on Dimensions | Create New Dimension and the Dimension Editor will open.
- Click on Edit | Insert Element and the Dimension Element Insert dialog box will open.
- To add a consolidated element, do the following:
- Type My Total in the Insert Element Name
- Select Consolidated from the Element Type
- Click on Add.
- Click on OK.
- My Total now appears as the first element of the dimension, which is a consolidated element. Now you can add child elements to the My Total element by following the next steps:
- Select My Total
- Click on Edit | Insert Child and the Dimension Element Insert dialog box will open.
- In the Insert Element Name field, type value1 and click on Add.
- In the Insert Element Name field, type value2 and click on Add.
- The dialog box now contains two children of My Total, each with a default weight of one.
- Click on OK.
- The Dimension Editor shows the new elements as children of My Total.
- Click on Dimension | Save | Dimension Save.
- As dialog box opens, enter a dimension name and click on Save.
After creating your dimension with the Dimension Editor, you can then use it to make modifications to that dimension. You can:
- Add siblings to existing elements.
- Add children to existing elements.
- Rearrange the hierarchy structure (such as repositioning elements within consolidations).
- Delete elements from the dimension.
- Delete elements from consolidations.
- Edit element properties, such as changing the weight of an element within a consolidation.
- Rearrange the order of elements in the dimension.
Ordering elements in a dimension
The order of elements in a dimension is very important! When you add elements to a dimension, TM1 assigns an index number to each element automatically. TM1 uses the index internally to keep track of the elements. You simply order the elements as you want them to appear in the dimension (see the sort options functionality of the Dimension Editor).
Setting the order of dimension elements from the Server Explorer
You select a sorting property for the dimension from these three automatic sort orders:
- Name: Sorts elements alphabetically
- Level: Sorts elements by hierarchy level
- Hierarchy: Sorts elements according to the dimension hierarchy.
Cubes
The mechanics of manually creating a cube is simple; from the TM1 Server Explorer, you can just click on Cubes | Create New Cube and then fill in or select the required information. Some interesting and therefore important facts about the mechanics of cube creation are:
- If you do not provide a name for your cube, TM1 names the new cube as Unnamed.
- You can always create cubes on your local server but you must be a TM1 administrator to create cubes on remote servers.
- You should always include your measures dimension as the last dimension in your cube.
- You should (try to) order your dimensions from the smallest and sparsest of dimensions to the largest and most dense dimensions.
- The order of your dimensions can impact the performance of your cube.
- You must select at least two dimensions. The maximum number of dimensions is 256.
- Setting up the measures and a time dimension as cube properties is not required for TM1. This feature is offered if an external OLAP client is going to access your TM1 cube as a data source.
- By default, TM1 loads all cubes into memory when a server starts.
- If you have certain cubes that are infrequently accessed (such as historical data cubes), you can set those cubes to load only when a client attempts to access the cube data. This is done using cube properties through TM1 server explorer.
- TM1 includes a feature that lets an admin user change the order of dimensions in a cube, consuming less memory and improving performance.
Loading cubes
The most flexible and effective method to load a cube with data is through the use of a custom Turbo Integrator process. This process can read data from any number of data sources such as such another TM1 cube, an external database, or a flat ASCII file.
Viewing cube characteristics
Within the Cognos TM1 Server Explorer, you can select View and then Properties Window. This will display the Properties Window (on the right side) in explorer.
Click on Cubes in Server Explorer, TM1 will display a list of all the cubes in the instance along with each cube:
- Owner
- Replication server
- The memory it (currently) uses when loaded
- If the cube is private or public
- The number of dimensions in this cube
- The name of its measures dimension (if one has been assigned)
- The name of its time dimension (if one has been assigned)
Click on an individual cube and view similar information about the specific cube.
List of all dimensions that make up that cube
- The order of the dimensions that make up that cube
- Memory each dimension (currently) uses
- If the dimensions are private or public
- The current number of subsets defined in each dimension
- The current number of elements in each dimension
Lookup cubes
Lookup cubes are cubes that you can set up and which can be used to support other cubes within a TM1 application. These cubes are usually read-only to the user and may contain calculations or reference data that are then pulled into other cubes using TM1 rules.
Control cubes
The cubes that TM1 itself uses to perform certain activities are called control cubes. These activities are:
- Security
- Client and group administration
- Object attribute and properties control
- Performance monitoring
- Hold tracking by username
Security
The security control cubes are used to apply security privileges for TM1 objects including cubes, dimensions, processes, and chores to user groups on a TM1 Server.
Client and group administration
These cubes assign clients to user groups and store properties for all clients on the TM1 Server.
Object attribute and properties control
These cubes store attribute and property values for objects on the TM1 Server.
Performance monitoring
The four control cubes that hold performance statistics for clients, cubes, and servers are as follows:
- }StatsByClient: This cube tracks message count, average message size, total elapsed time, and other measures.
- }StatsByCube: This cube tracks the memory used for each cube on the server.
- }StatsForServer: This cube tracks the connected clients, active threads, and memory used for the server.
- }StatsByCubeByClient: This cube tracks the number of cell updates, cell retrievals, view calculations, and view retrievals for each client and cube on the server.
Hold tracking by username
Once an individual cell holds is set on, Cognos TM1 creates a control cube named }Hold_ followed by the user ID and cube name to control the status of holds for the cube.
Cube replication
Depending on your access privileges (you need to be a TM1 Admin), you can copy cubes from one server to another, and synchronize the data between the cubes.
Replicating your cubes between your servers
Replicating cubes can offer the following advantages:
- Traveling “disconnected” users can update their local servers and then connect and have their cubes synchronized to the corporate database when it is convenient to them.
- The latest data can be synchronized to your laptop for “disconnected” presentations or training.
- Replication enhances the scalability of TM1.
Replication creates a relationship between two cubes and between two servers.
Replication must know
- TM1 versions: All TM1 Servers in a replication process must be the identical versions.
- Remote servers: You can replicate cubes that reside on only remote servers. You cannot replicate cubes that reside on other local servers.
- Local servers: The machine must have a network card. To run a local server as an independent process, clients need to select the Local Server Execution Mode: Independent Process option in the TM1 Options dialog box.
- Access privileges: When you replicate a source cube on a remote server to a local server, any elements to which the local client has None access on the remote server will have a value of zero.
- cfg file: The Tm1s.cfg file must be configured to register the target and source servers with the same TM1 Admin Server.
- Length of directory path and cube name: The total length of the pathname for the target TM1 server’s data directory and the name of the cube you are replicating cannot exceed the limit of 256 characters.
- Transaction logging: Transaction logging must be enabled for the mirror cubes on the target server which are a part of the replication and synchronization process.
- CubeProperties control cube: The values stored in the CubeProperties control cube are specific to a TM1 Server and are not copied from the master to the target server during a replication process.
- Dimension replication: TM1 can be set up to replicate dimensions but by default, it does not.
- Rule replication: TM1 can be set up to replicate cube rules but by default, it does not.
- Multiple server replication: Depending on your access privileges, you can replicate a single cube on many different servers.
- Replication of Replicated: You can replicate a replicated cube.
- Source cubes: A sourcing cube refers to the original cube in a replication.
- Mirror cubes: A mirrored cube refers to a copy of the source cube.
- Current replications: The Server Explorer window lists the current replication connections beneath the Replications
- Source servers: A source server is a remote server you log in to.
- Target servers: A target server is a server you logged in from.
- Replication connections: This is done in Server Explorer by double-clicking on the Replications icon and by filling in the required information on the Create Server Replication