TM1 cube types
There are four types of cubes in Cognos TM1. They are as follows:
- Standard cubes
- Control cubes
- Virtual cubes
- Lookup cubes
- Virtual: This term has been defined in philosophy as that which is not real but may display the salient qualities of the real—Wikipedia.
- Lookup: This term usually refers to searching a data structure for an item that satisfies some specified property—Wikipedia.
Cubes are to TM1 what tables are to relational databases.
- You can 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.
- Data is loaded into these cube cells and these cells are identified by the intersection of dimensions.
Virtual cubes
It is a TM1 cube which is sometimes termed as a soft cube where no data is loaded to or resides in, but only references data points in other cubes.
Being a fully rules-calculated cube, virtual cubes have no data stored in them. They just have rules pulling data from other cubes and possibly performing additional calculations on that data.
Virtual cube creation
The steps to create a simple virtual cube are as follows:
- From TM1 Server Explorer, right-click on Cubes and then select Create New Cube. The Creating Cube dialog will be opened. The Available Dimensions list (on the left) will list the current dimensions stored on the server.
- Type a name for your virtual cube in the Cube Name
- In the Available Dimensions box, select the dimensions that you want to include in your virtual cube.
- Click on Create Cube to create your virtual cube.
- Select your virtual cube from the cubes list.
To write the appropriate TM1 cube rules to associate with your virtual cube that will pull data from other cubes in TM1 into your virtual cube, you will have to perform the following steps:
- If you right-click on your virtual cube name in TM1 Server Explorer, you can select Create Rule. Then, the TM1 Rules Editor will be displayed.
- Create rules that reference or pull data points from other TM1 cubes.
Virtual cube rule association
As with all TM1 cubes, the rules that pull data from a source cube into a virtual cube must be associated with that virtual cube.
Feeders
Adding rules to a cube will impact TM1’s ability to utilize its sparse consolidation algorithm. Fortunately, you can tell TM1 where to look for rules-derived values with feeders that you create.
Intercube feeders
When working with intercube rules, as in the case of virtual cubes, rule statements reside in the target (the virtual) cube, while feeder statements always reside in the source cube.
It may be easier to understand if you think of your feeders pushing and pulling. Feeders do the pushing of data from the source cube (so, they will reside in or be associated with the source cube) while rules do the pulling of data into the destination cube (so, they will reside in or be associated with the destination cube). This is true for standard TM1 cubes as well as virtual TM1 cubes.
Submitting a forecast to a virtual cube
Suppose you had a global TM1 application with multiple cubes defined—possibly a cube for each of a company’s sales regions—Northern, Western, Southern, and Eastern.
Let us say that each of the global region cubes are made up of the following dimensions:
- Version
- Period
- Region
- Product
- Measure
To do this you would create a rule in the virtual cube:
['Current Forecast','Northern']=N:DB('Northern','Actual',!Period,!Regi on,!Product,'Current Forecast');
The other cube reference rules are as follows:
['Current Forecast','Western']=N:DB('Western','Actual',!Period,!Region ,!Product,'Current Forecast');
['Current Forecast','Eastern']=N:DB('Eastern','Actual',!Period,!Regio n,!Product,'Current Forecast');
['Current Forecast','Southern']=N:DB('Southern','Actual',!Period,!Reg ion,!Product,'Current Forecast');
Unmatched dimensionality
Additionally, even when cube dimensionality matches, specific elements may require translation between source and destination cubes.
Conditional rules
Cognos TM1 rules can be written with conditional evaluation (you can use the IF function to force TM1 to evaluate a rule differently depending on a logic test).
Read these Top Trending Cognos TM1 Interview Q’s that helps you grab high-paying jobs!
Using conditional rules within a virtual cube
Sometimes, it might be more realistic to wait until data is in a complete or approved state before making that data available in the consolidation cube, yet it is not desirable to have to intervene for sending or copying the data. One way to accomplish this might be to use conditional logic to modify your rule slightly:
['Current Forecast',' Northern']=N:IF(DB('SysCntrl', 'Northern', 'ForecastStatus')=1 ,DB('Northern','Actual' , !Period, !Region,!Product,'Curre nt Forecast'),0);
Consolidating data with a virtual cube
Another example of using a virtual cube is entity consolidation. For example, suppose a corporation consists of several legal entities that must report their financials to the parent. The parent, as well as each legal entity (LE), has its own Profit and Loss (P&L) cube in TM1.
So, for example, in the parent P&L cube we have:
['8151', 'Legal Entity 1'] =N: DB('LegalEntity1 P&L','8151',!Legal_ Entity,!Version,!_Source,!_Period,!_Currency);
['Legal Entity 1'] =N: DB('LegalEntity1 P&L',!Account',!Legal_ Entity,!Version,!_Source,!_Period,!_Currency);
Reporting with virtual cubes
Another area where you can benefit from virtual cubes is with reporting. Some Cognos TM1 cubes may be very complicated, large, or have intensive rule calculations. Defining several specific reporting virtual cubes—based upon unique views of the larger cube or cubes—can be a solution:
Other advantages of virtual cubes
The use of virtual cubes in your solution may also simplify the development of some TM1 active reports and web sheets by centralizing information into a single location. In fact, some active reports may not be possible without the use of a virtual cube.
Additional reporting options
Before choosing to implement virtual cubes as part of your reporting solution, all of the other options provided by Cognos TM1 should be explored and considered.
Lookup cubes
Finally, another type of TM1 cube is the cube referred to as a lookup or conversion cube. This is a sort of utility cube. Lookup cubes are cubes that you can set up and use to support other cubes and processes within a TM1 application. These cubes are most often set up to be read-only by design or even made non-visible to TM1 users and may contain calculations, controls, or reference data that are then used in your TM1 processing or pulled into other cubes using TM1 rules.
Translation of data
Whenever there is a need to perform a translation or conversion of information, you have to decide whether it is best to use an element attribute or a lookup cube.
Typically, if it is a single point translation (which involves only one dimension), you would have to use an attribute. If the translation involves multiple points, then you would use a lookup cube.
One advantage of using a lookup cube of this kind is that additional measures can be added in the future, which you would need to translate the source system account code to additional values, and no changes to the TI process would be required.
Loading data with lookup cubes
For performance reasons, lookup cubes can also be used to load data into a TM1 application without locking the cubes that your users see and use. Another cube (or cubes) will then use TM1 rules to reference the data in the lookup cube and would then be used by users for reporting or other updating.
Finally, a reporting cube may be defined at a certain level of aggregation.
Spreading data with lookup cubes
Cognos TM1 provides numerous types of pre-defined data spreading functions. These functions are useful for quickly distributing data to selected cells in a Cognos TM1 cube.
From the TM1 Cube Viewer, In-Spreadsheet Browser, and in slice worksheets you can right-click on a cell and TM1 will offer a menu that includes Data Spread where you can then click to select the desired data spreading method.
