What are Cubes?
Cubes in TM1 hold data. They are used for storing the data. You can create a cube with 2 or more dimensions. The cells in the cube keep the data for intersection of elements of the dimensions.
Also, I’m assuming you’ve completed the homework of tutorial 3
We’ll create a cube which will store the data for different products for different months. We’ll use the dimensions that we created in third tutorial.
Open your TM1Beginner server that we created in second tutorial. Login using Admin as ID and no password.
Right click on Cubes node and click on Create New Cube.
The create cube box shows up. Now you can one by one select all three dimensions as shown in the following screenshot. Remember to keep them in the order specified.
Now click on Properties and assign Measure as measure dimension and Months as time dimension. It’s of not much user TM1 but is done for BI. Click on OK.
Now give the cube a name (Products) and click Create Cube. Your newly created cube will show up under Cubes node in Architect. Open it. Press F9. You press F9 to load (or reload) the data in a cube. Pressing F9 will show you the data (or updated data) of that cube.
Cube will show up like this:
2. Arrange Cube
Let’s arrange it to show the data in a better way.
Drag the Products dimension over Months dimension (notice the green mark) and leave it. Doing this will swap the months dimension with Products dimension.
Similarly swap the months dimension with measure dimension so that the measures exist in page (on top), months in columns and products in rows.
Double click on Products dimension, expand all nodes and click on OK.
Similarly double click on months dimension, expand and select only the months. Click on OK. Again double click on months and click Show all button this time.
Click on FY, copy it (Ctrl+C) and click Cancel. Again double click on months dimension, click Dec and paste the FY element that we just copied below Dec. Now select all elements and click OK.
Ready?? Press F9 and a new view will be opened in front of you.
This is much better. Now let’s save it as our default view so that from the next time when you open a cube, this view opens.
Go to file click on Save. A save dialog opens. Check mark Default check box and uncheck Private check box. Click on Ok to save the view.
From the next time the same view will open.
Create a new ‘Versions’ dimension which includes 3 items Actuals, Budget, NY Forecast (next year forecast)
Create a new Products cube (with name Products2) which includes 4 dimensions now: Products, Months, Versions and Measures
Arrange the cube so that Products exist in rows, Months and Measures dimensions in columns, Version and measure in pages. In months dimension, only Q1, Q2, Q3, Q4 and FY should be visible. Final layout should be like this:
What are dimensions
In TM1 there are objects to hold data. These objects are called cubes. Dimensions in TM1 are basic building blocks of cube. So if you have an excel file which captures the grocery products’ data for different months you’ll create one cube in TM1 to keep this data. To do that you’ll create 2 dimensions 1. Products and 2. Months to create this cube.
How to create dimensions
2. Using Turbo Integrator Process
3. Using dimension work sheets
In beginner tutorial we’ll just follow the first method to create what we want.
Creating dimension with first method
Open your TM1Beginner server that we created in second tutorial. Login using Admin as ID and no password. Expand the tree to see the dimensions node.
1. What we will create
We’ll create 2 dimensions.
Products dimension will contain following elements:
Months dimension will have following hierarchy:
Let’s start to create these dimensions.
2. Insert Elements
Right click on the node and click ‘Create New Dimension’. Dimension editor will be opened for you. Go to Edit in the menu and click on Insert Element.
Doing this will open Dimension element editor. You can now write your element’s name and click on Add. This will add your element to the list of newly added (but not saved yet) elements. Write ‘Total Products’ (without quotes) in the Insert element Name box and click on Add. Notice that element type is simple. This means that this element will be of numeric type.
Click Add and then click OK. You’ll see the Total Products element in your dimension.
Now right click on Total Products and click Insert child.
Again dimension element editor will be opened and you can insert immediate children of Total Products (Soft Drinks, Chocolates and Breads) from our hierarchy. Click Ok and you’ll see them as children of Total Products. Now time to insert remaining elements.
You can even copy and paste elements from an excel file. So to test this, write all the remaining elements in an excel file. Copy them, select Total Products and paste them directly in the dimension editor. Editor will ask you where to paste the elements.
Paste Above, Paste below and Paste as Child. You should select paste below. All elements will be added to the dimension.
3. Arrange Elements
Now time to move them to their designated places. Select all three beverages (Pepsi, Cocacola, Seven Up) and drag them to the Soft Drinks element. When you do that you’ll see mouse cursor change from pointer to another pointer (as shown below)
Drop them and they will be inserted as children to that element.
Now select Chocolates (Almond Joy, Amul Chocolate, Bournville), right click on them and click Cut. Then right click on Chocolates and click ‘Paste as child’.
They will be added to the chocolates element and element’s sign (which is in front of element) will change from n to ∑ (sigma). That means the element is now of type consolidation.
Insert the remaining elements like this and you’ll have your hierarchy ready. Now you’ll notice that soft drinks are not in alphabetic order. So I need to move Cocacola above Pepsi. Just right click on cocacola and click Cut. Then right click on Pepsi and click Paste Above. The element will be inserted above Pepsi.
This is a way of moving elements in the dimension. There are many other ways of arranging dimension but we’ll discuss them in advanced tutorials.
Now click save and give name to your dimension: Products.
Click on OK and OK again. You’ll see your dimension appear under dimensions node in Architect.
Now create the months dimension the same way. Just write all elements in an excel file and copy paste from there. Then arrange them in Q1, Q2, Q3, Q4 and FY.
Now create a measure dimension that contains Quantity, Sales Price and Sales as items.
Here’s what you’ll do to setup tm1 application server:
1. Create a directory structure on your disk as below:
That means on your D: drive you’ll create a folder TM1Beginner and in the folder, you’ll create these 4 folders.
2. Now, Create following 2 files in Data folder.
Tm1s.lic. Copy and paste following content in this file:
Tm1s.cfg. Copy and paste following content in this file:
AuditLogMaxFileSize= 100 MB
3. Create a shortcut in the TM1Beginner folder and paste following code for it in the Location window of shortcut.
“C:\Program Files\cognos\TM1\bin\tm1s.exe” -z “D:\TM1Beginner\Data”
Remember, if you’ve installed the TM1 in “C:\Program Files\cognos\TM1\Bin” directory, only then your shortcut will work. So better take a look at this disk location to see if tm1s.exe does exist in “C:\Program Files\cognos\TM1\Bin”. If it doesn’t then you need to find out the disk location of tm1s.exe. May be you can search for it in your Program Files.
Once you have found the tm1s.exe file, give that disk path instead of the above. For example if you found the tm1s.exe file in D:\Program Files\IBM\TM1\Bin folder, you have to supply:
” D:\Program Files\IBM\TM1\bin\tm1s.exe” -z “D:\TM1Beginner\Data”
4. Showtime!! Launch your application server by double clicking on the shortcut. Doing this will launch the application server on 22222 port on your computer.
5. It will create TM1 objects in the data directory and will create log files in Logs directory. Open these 2 directories and confirm this. After the complete launch, the launch window will automatically minimize which means you can start working on Tm1 application server. Keep that window minimized (don’t close it as long as you work)
6. Now launch Architect from Start > All Programs > IBM Cognos (or any other start path you provided). It will show the server that you just created. Double click on it and it will ask for a username and password. Put Admin as username and press enter.
7. You’ll see following hierarchy. Now you’re ready to create your tm1 model.
TM1 Installation and Configuration is very easy to do. Still, developers find it difficult to believe that it would be this easy. I have seen people drawing blank when the installation wizard asks for certain information. So just for the sake of documenting everything, I’ll start the beginner tutorials with this topic.
For someone with even basic computer skills, it should be a piece of cake to install TM1.
So here go the screen shots of installation:
1. Launch the wizard
2. Then select the disk location in which you want to install your TM1. I selected C:\Program Files\Cognos\TM1 as my disk location.
3. Select the components that you want to install.
TM1 Architect – This component is for developers. They can use this tool for developing TM1 models. We’d be using this tool the most in our tutorials.
TM1 API, TM1 Java API, TM1 .NET API – As their name suggests, they are APIs. They can be used to create tools which interact to TM1.
TM1 Web Components – If you want to access TM1 models using an internet (or intranet) website, you’d have to install TM1 web components on a web server. On a distributed environment, these components are installed on a separate web server. You need to have IIS to use TM1web.
TM1 Perspective and TM1 client – TM1 perspective is an excel add-in. In layman language, it’s a Microsoft excel component and can be used to connect to TM1 application server using Microsoft excel. Using this tool, a TM1 end user can get and send data from and to a TM1 server.
TM1 Admin server – TM1 admin server is an administrative server which keeps track of application server. It’s like a headmaster of a school. Each teacher (application server) needs to report to the headmaster before doing anything. If you want to talk to the teacher (application server) you better ask the headmaster (admin server) whether the teacher (application server) is available.
TM1 Server – It’s required if you want to create TM1 models on a server. Without TM1 server component you can’t create your virtual application server.
TM1Top – It’s a utility to monitor user activity on TM1 application servers.
TM1 Contributor – It’s required to set up TM1 contributor which can be used to create and host TM1 contributor applications of models that you created in TM1.
4. Keep admin host port and SSL port number as they appear on your system. As we’re installing it on a single machine, there is no need to change these settings.
5. Provide your computer-name\some-admin-name and password. Here my computer name is dell. so I provided dell\administrator. I could have used dell\amit if I want to run my services with account amit (which exists on my system/server) or may be you can create an admin service account in your windows installation and provide those credentials here. Anyway you have to provide some account so that TM1 services (admin server and application server) could run using that account.
6. Install it.
This was a tutorial on tm1 installation and configuration.
If you want to understand skipcheck and feeders, you need to learn the whole concept in detail.
NOTE: Please remember that in example that is being used in this tutorial, I’ve kept the simple cube for clarity and have ignored irrelevant details (for this concept) such as “Price at consolidated elements should be weighted average instead of simple roll up”.
What are Skipcheck Feeders in TM1
Let’s learn the Whole Story:
TM1 can handle very large cube sizes. In TM1, leaf level data (the data that users input) is stored in cube files whereas the rule based (e.g. Sale = Units * Price) and consolidated data (e.g. Q1, Q2, Year) is just kept in memory.
If a cube is just an input cube and it doesn’t contain any rules TM1 uses the fast consolidation algorithm to do the consolidation in the cube. Fast consolidation algorithm ignores the consolidation of cells which contain zero values. So if Q1 is consolidation of (Jan, Feb, Mar), it will check values of Jan, Feb and Mar; and if all three are zero, it will not put any values in Q1. It will simply ignore it. For a sparse cube this results in tremendous performance improvements.
For example: In below diagram, the fast consolidation engine just had to consolidate 6 cells
[‘1 Quarter’,’Units’], [‘1 Quarter’,’Price’], [‘1 Quarter’,’Sales’], [‘Year’,’Units’], [‘Year’,’Price’], [‘Year’,’Sales’]
Fast consolidation engine ignores ‘2 Quarter’ ‘3 Quarter’ and ‘4 Quarter’ because values in detail level cells (Apr, May, Jun etc.) of these rollups are zeros.
Because of this, it didn’t have to do any consolidation for 9 cells: (Units, Price and Sales X Q2, Q2,Q3)
In large sparse cubes, this approach results in hundreds of thousands of cells and results in better performance.
Now see what happens when you write a rule in a cube:
When you write a rule (any simple rule), TM1 turns this fast consolidation engine off. That means TM1 no longer ignores zero cells while doing consolidation. While processing the cube, it will check ALL cells and will perform consolidation on all of them.
So if we take above example, it will now have to perform 15 consolidations:
‘1 Quarter’ ‘2 Quarter’ ‘3 Quarter’ ‘4 Quarter’ ‘Year’ values of ‘Units’, ‘Price’, ‘Sales’ (5X3=15)
This deteriorates the performance in sparse cubes. The reason simply being that sparse cubes contain a huge number of zero cells and TM1 will check all cells to do consolidation even though it is not necessarily required for all of them.
What should we do to correct the issue?
Now the first logical thought that comes to mind is, why the hell turn the fast consolidation algorithm OFF? Let’s keep it ON.
Okay, point taken. So We’ll use a keywork SKIPCHECK for that. You have to put it in the beginning of the rule file. If you do so, TM1 will turn the fast consolidation algorithm (FCA) back ON. But doing so will not solve our problems. Because now, here is what will happen:
1. To find the value of [‘1 Quarter’, ‘Sales’], FCA will check the hierarchy of Months dimension. It will check values of [‘Jan’, ‘Sales’], [‘Feb’, ‘Sales’] and [‘Mar’, ‘Sales’].
2. However you must know that TM1 doesn’t save the values of calculated cells in cube file, it just saves them in memory. So although [‘Jan’, ‘Quantity’] and [‘Jan’, ‘Price’] do exist in cube, [‘Jan’, ‘Sales’] remains 0 in cube file and, is calculated and shown every time someone opens the cube.
3. Because of this when FCA checks values of [‘Jan’, ‘Sales’], [‘Feb’, ‘Sales’],[‘Mar’, ‘Sales’] and finds them to be zero; it ignores the value for [‘1 Quarter’, ‘Sales’] and shows 0. Similar thing happens with [‘Year’, ‘Sales’] values.
Now although we’re using the powers of fast consolidation engine, we have incorrect values in so many consolidations.
Here come the Skipcheck Feeders
Only if we could tell it which rule-based-cells are producing values!
Our problem lies in the fact that FCA takes the rule calculated cells as zero while doing the consolidation. If we could somehow check which rule based cells will produce non-zero values and tell FCA ‘that’, FCA will correctly do the consolidation for those cells.
So here comes what we’ve been trying to learn: FEEDERS
You can use Feeders statement to do that.
Let’s understand it using above example:
Our problem will get resolved if we could find those rule based cells which will produce values (will be non-zero) and tell FCA that. E.g. if we could just find those ‘Sales’ values which are non-zero and tell FCA to take only those values into consideration for consolidation. Here is how we’ll do that:
[‘Units’] => [‘Sales’];
We use Feeders (followed by semicolon) statement to say that we’re specifying rules for FCA.
By using the above other statement we’re saying that Sales value depends on Units. If Units is zero, Sales will be zero and if Units is non-zero, Sales will be non-zero. So please consolidate those Sales values for which Units are non-zero and ignore the others (ones for which units are zero).
In this scenario, FCA will consider Sales-Jan, Sales-Feb and Sales-Mar as non-zero values (because their Units values are non-zero) and will consolidate 1Quarter correctly this time. Furthermore, it will ignore ‘2 Quarter’, ‘3 Quarter’ and ‘4 Quarter’ consolidations because units in all respective 9 months (Apr, May, Jun etc.) are zero so Sales will be zero and so no consolidation for 3 quarters. Doing so will improve the performance because now we’re consolidating only those cells which DO require consolidation (total 6 cells)
Search Terms which could be helpful to find this article:
what is the point of a feeder in tm1
What are skipcheck and feeders
Skipcheck and feeders in tm1
what are feeders
why should i use skipcheck and feeders
Error: Invalid (blank) application link appears in application and when you try to delete it or click on it, nothing happens but an error message is shown. Clicking on error messages also results in nothing.
Description and Message: Cannot find ” object on server <application server name>. Would you like to delete this reference.
Resolution: Restart your application server TM1 service.