There are 2 ways of loading data in TM1 cubes:
1. Using Turbo Integrator
2. Writing rules
1. Loading Data Using Turbo Integrator
Turbo integrator (TI) is an ETL (extraction – transformation – load) tool, in layman terms a data loading tool, for loading data into TM1 cubes. Although, there is so much that you can do with TI, loading data is just one part of that.
So let’s load the data into our TM1 cube. We’ll use the Products2 cube that you created in last tutorial. You can follow this tutorial without that too, but you’d have to follow it just on concept, you’ll not be able to follow it step by step.
Now, here is what we’ll do to load the data:
- Check our cube and note down the columns that we’ll need in our data.
- Create a csv (comma separated) file of dummy data that we have to load to our cube.
- Write a TI to read that csv file and load data to our cube.
Ok, now open the products2 cube. The data columns that you see in it are:
Products, Months, Versions,Measures
So our data should be in following format in csv file:
Product, Month, Version, Measure, DataVal
e.g. Pepsi,Apr,Budget,Quantity,100
Create a csv file using samples of data such as above. I’ve shown below the ones which I’m going to load to my TI. You can copy it and create your data.csv from this. Remember to create your data.csv in D:\TM1Beginner\InputFiles folder in your app server directory.
Product,Month,Version,Measure,DataVal
Pepsi,Jan,Budget,Quantity,10
Coca Cola,Jan,Budget,Quantity,15
Coca Cola,Mar,Budget,Quantity,3
Coca Cola,Sep,Budget,Quantity,2
Coca Cola,Dec,Budget,Quantity,7
Seven Up,Jan,Budget,Quantity,20
Seven Up,Jul,Budget,Quantity,5
Seven Up,Oct,Budget,Quantity,6
Almond Joy,Feb,Budget,Quantity,8
Almond Joy,May,Budget,Quantity,2
Almond Joy,Jul,Budget,Quantity,5
Amul Chocolate,Feb,Budget,Quantity,9
Bournville,Mar,Budget,Quantity,17
Bournville,Sep,Budget,Quantity,3
Bournville,Dec,Budget,Quantity,5
Wonder Bread,Apr,Budget,Quantity,3
Britannia,May,Budget,Quantity,7
Now launch your TM1 architect, login to your TM1Beginner server, go to Processes node, right click, and click create new process.
NOTE: Close your data.csv file before proceeding further.
Select data source as text. Browse to your D:\TM1Beginner\InputFiles files and select your data.csv file. You’ll get a warning box, click OK on it. There is a field of “Number of title records” on the page (on Data Source tab), write 1 in it. Because we have first record as title record in our data.csv file. Click Preview, it should look like below:
Go to the variables tab, Rename V2 as vMonth. In content column, select “Element” for first 4 variables. For the fifth one, select data. It will look like below:
Come to the maps tab, Select Update Cube, select your CubeName (which is Products2). Go to the dimensions tab, select dimensions under column “Dimension”. This will tell Tm1, which column of data file, will be matched with which column of cube. You don’t need to map data column, because there is only one data column.
Go to the advanced tab and one by one, click all tabs under advanced tab (Prolog, Metadata, data, epilog). These tabs contain the code for us to load the data into cubes. When you select your data source, select cube and data columns etc. this code is automatically created. Clicking all 4 of these tabs is necessary because sometimes this code is not updated when you change something in turbo integrator front end. Selecting these 4 tabs ensures that automatically generated code has been properly updated. It’s also a good practice to review the code changes before running TI.
Now save your process with name LoadDatatoProducts2 and click Run. It’s a button next to Save button.
If you’ve followed the tutorial correctly the process will complete successfully. Now launch your Products2 cube, rearrange the dimensions as follows and verify that you see the following data in your cube:
Homework
Load the following sale price data to your Products2 cube:
Pepsi,Jan,Budget,Sale Price,2
Coca Cola,Jan,Budget,Sale Price,3
Coca Cola,Mar,Budget,Sale Price,4
Coca Cola,Sep,Budget,Sale Price,5
Coca Cola,Dec,Budget,Sale Price,6
Seven Up,Jan,Budget,Sale Price,7
Seven Up,Jul,Budget,Sale Price,8
Seven Up,Oct,Budget,Sale Price,9
Almond Joy,Feb,Budget,Sale Price,1
Almond Joy,May,Budget,Sale Price,2
Almond Joy,Jul,Budget,Sale Price,3
Amul Chocolate,Feb,Budget,Sale Price,4
Bournville,Mar,Budget,Sale Price,5
Bournville,Sep,Budget,Sale Price,6
Bournville,Dec,Budget,Sale Price,7
Wonder Bread,Apr,Budget,Sale Price,8
Britannia,May,Budget,Sale Price,9
2. Loading Data Using Rules
Now that you have your data ready in Products2 cube, we’ll see how to load data using rules in TM1. For this we’ll load the Products2 data in Products cube that we created in our last tutorial (4. Cubes in TM1)
Open your Products cube and verify that you don’t have quantity nor do you have sale price.
Now before we proceed further, let me just tell you that we use DB function for loading data in a cube from another cube. Here is the syntax of a rule along with DB function:
1 Selection 2 Filter 3 DB
[Data selection where you want to load data] = Filter: DB(CubeName, Dim1Item, Dim2Item, Dim3Item, …. );
1 = It’s data selection, the cells into which you want to load data. For example if I want to load just my budget data, I’ll write [ ‘Budget’ ] in it.
2 = It’s filter. We specify numeric (N:) or string (S:) here to load data to numeric or string cells of target cube.
3 = It’s DB function. First argument is the cube’s name from which you’re pulling the data and remaining arguments are dimension item names against which you’re pulling the data. For example if I want to take Products2’s Pepsi’s Budgeted Quantity for Apr, I’ll write DB(‘Products2’, ‘Pepsi’, ‘Apr’, ‘Budget’, ‘Quantity’).
Remember, order is important in DB function. You should write all dimension items names in the order they exist in the cube.
Now let’s write our rule to pull data from Products2 to Products cube.
Right click on Products cube and click “Create Rule…” (we’ll right rule in products cube because this is the target cube). A window opens. That’s rule editor.
Write following:
[ ‘Quantity’ ] = N:
Now click cube icon on the toolbar and select Products2 cube. This is how we select the source cube.
Click OK. You’ll see the following rule in your rule editor:
[ ‘Quantity’ ] = N: DB(‘Products2’, !Products, !Months, , !Measure)
!Products (dimension name with exclamation mark) is a dimension variable. It tells you that for each cell of cube for which this rule runs, !Products will take the corresponding item of TARGET dimension. Confusing?? Read that again.
I’ve explained this in detail in my advanced rules tutorial (which is part of a video series and costs you a few bucks), for now just understand that for different cells, the !Products, !Months and !Measure represent different dimension items. So for Target cube’s cell [‘Pepsi’ -> ‘Apr’ -> ‘Quantity’], when TM1 calculates the formula, it will automatically convert the formula to DB(‘Products2’, ‘Pepsi’, ‘Apr’, ‘Quantity’) in memory and take the value (‘Pepsi’, ‘Apr’, ‘Quantity’) from Products2 cube. But what will formula take from Versions? It will take Budget? or Actual? or what?
To specify the version, you need to modify the formula. As we don’t have any version in our target cube, we need to hard code the version in our formula. Let’s take Budget data from source cube. So change your formula to this:
[ ‘Quantity’ ] = N: DB(‘Products2’, !Products, !Months, ‘Budget’, ‘Quantity’);
Also, change !Measure to Quantity as we just want to take the quantity value from Products2 cube, at least for now. And remember to put a semi colon in the end of formula.
Click on Verify toolbar icon (third toolbar icon from the left) to verify the formula and then click Save icon. Close rules editor and open the Products cube to verify your data. You’ll see following data:
Homework
Bring the price data too from Products2 cube into your Products cube. You’d write a similar rule for Sale price in rule editor for that.
Now, think about what would you have to do to bring both values (Qty and Price) using a single rule?
After you’ve brought both Quantity and price, open your Products cube and check the Sale Price in rollup levels (at Q1/Q2 etc. or at Total Products level). It’s just summing up, which is not correct right? At rollup, the price should be a weighted average based on quantity. We’ll learn about correcting it in our tutorial on rules.
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.
Creating Cubes
Prerequisites
1. TM1 Installation and Configuration
2. Setup TM1 Application Server
Also, I’m assuming you’ve completed the homework of tutorial 3
1. Create
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.
Homework
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
1. Manually
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.
Prerequisites
I hope you’ve followed previous 2 tutorials (TM1 Installation and Configuration, Setup TM1 Application Server) and now now know how to login to a TM1 application server.
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.
1. Products
2. Months
Products dimension will contain following elements:
| Total Products | ||
| Soft Drinks | ||
| Pepsi | ||
| Coca cola | ||
| Seven Up | ||
| Chocolate | ||
| Almond Joy | ||
| Amul Chocolate | ||
| Bournville | ||
| Bread | ||
| Wonder Bread | ||
| Britannia | ||
Months dimension will have following hierarchy:
| FY | ||
| Q1 | ||
| Jan | ||
| Feb | ||
| Mar | ||
| Q2 | ||
| Apr | ||
| May | ||
| Jun | ||
| Q3 | ||
| Jul | ||
| Aug | ||
| Sep | ||
| Q4 | ||
| Oct | ||
| Nov | ||
| Dec |
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.
Homework
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:
DATABASE_TYPE=TM1 PROPRIETARY
CLASS=505
LICENSE=5499da3942a5ee-8,16699,13108
MAX_USERS=99999
MAX_READONLY_USERS=99999
EXPIRE_DT=–
LOCATION=
ORGANIZATION=Cognos
LICENSOR=16699
INSTALL_ID=13108
PRODUCT=TM1Server8
OPTION1=Turbo Integrator
OPTION2=SAP BW
OPTION3=Planning Manager
OPTION4=Financial Reporting
Tm1s.cfg. Copy and paste following content in this file:
[TM1S]
ServerLogging=F
SecurityPackageName=Kerberos
IntegratedSecurityMode=1
UseSSL=T
ServerName=TM1Beginner
DataBaseDirectory=D:\TM1Beginner\Data
LoggingDirectory=D:\TM1Beginner\Logs
AdminHost=
PortNumber=22222
ClientMessagePortNumber=
Language=ENG
SaveTime=
DownTime=
ProgressMessage=True
AuditLogOn=F
AuditLogMaxFileSize= 100 MB
AuditLogUpdateInterval=60
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.
7. Done.
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:
Feeders;
[‘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
Loading ...