Create a TM1 Send Sheet in Excel
Sometimes data created in Excel needs to be loaded into TM1 directly from the Excel sheet. Here I am going to show you the details in how to create a TM1 Send Sheet in Excel. With it we can have calculations done in Excel and then when ready, load that data into TM1.
For example, we have just been working with a client to load their sales and margin budgets for next year into TM1. They have a bunch of users who do not have TM1 licences and had thus created excel spreadsheets that were distributed and completed by managers. We could have either created a text file and used a TI to load the data. Or we could add a send sheet to the Excel. We chose the latter method as it was easier for the users to manage.
How To Create a TM1 Send Sheet
You are going to use the DBSW Excel based TM1 function to send values to TM1. This function takes an existing value in excel and loads it into TM1 into a specific, writable, location. Thus we are going to be adding new formulae to the spreadsheet, rather than replacing.
This is the syntax of the DBSW:
DBSW ( value, cubename, element1, element2, element3 [,...elementN] )
We are then going to wrap that in an Excel based IF against a flag we will create in Excel to determine if the send sheet is active or not.
Setup of the Send Sheet
- First, take the spreadsheet that has been developed with all the excel formulae in it. Then find a location that makes it easy to copy a formula down and across for the relevant variables. In the sheet I just created these variables were month, account and measure, so I created a formula to grab the month name from the source section in the spreadsheet and copied it across for each month. I then did the same with the account and measure in two columns.
- Then in cells nearby, enter values for the elements that will not change. These also will be used in the DBSW formula. Here I entered the cubename, year, version and cost centre. Then name those cells as named ranges, so you can refer to them in the DBSW using a name rather than a cell reference.
- Finally, create an additional entry near those fixed elements as a flag to either enable the send sheet or not. I just call this a Send Flag and set it to 0 or 1. Then put some data validation on it so that is all that can be used. Then create an IF statement beside it to indicate if it is disabled or enabled.
Create the Send Formula
- In the first cell of the now empty grid beneath the first month and to the right of the first intersection of account and measure, create the formula. Here the order must be identical to the dimension order of the cube (so just have Architect open in another window).
Test the Send Formula
- Open Architect to the show the same intersection of the cube that you have created the DBSW for.
- Go back to Excel and change the source value.
- Recalculate the spreadsheet and the value will be sent to TM1.
- Flip back to Architect and recalculate the view and the value should appear.
This proves that the function is working.
Wrap the Send Formula in an IF Statement
- The last step of the process is to wrap the Send Formula in an IF statement so that data is only sent to TM1 when the Send Flag is active.
The screenshot below shows the Send Formula, with the IF statement, the Send Flag, the fixed elements (Scenario, Cost Centre, Measure) and the row and column titles of variable elements. The excel formula is:
=IF(SendFlag=1,DBSW(DR8, ModelCube, Year, DR$4,Scenario,$EG8,CostCentre,$EF8,Measure),0)
Always, always, always, save your spreadsheet with the Send Flag set to DISABLED, otherwise every time you open the spreadsheet, the DBSW will activate and send whatever is in it to TM1. This will overwrite whatever is already in the cube – including those direct changes you made this morning via Architect….
This function will only send values to TM1, not strings. To send strings, please use the DBSS function.