Using tables for data input

  • Dec. 15, 2013

Introduction

TM1 models very often use Excel spreadsheets for data input collection: Excel spreadsheets that we will be enrich by adding TM1 functions like the well-known DBRW and SUBNM functions. By using DBRW formulas, input by the user is sent automatically to cubes, nothing new here. Usually, input in the Excel sheets is done using Active forms and/or cube slices, thereby using among other tools TM1 picklists (data validation if you like). But it becomes interesting is we use Excel tables…, introduced in Excel 2007. If you do not know what an Excel table is, please read these 10 interesting tips about Tables.

What can we do with tables when it comes to data input to TM1 cubes? Allow me to not just write and write long paragraphs of text, instead an example will be an eye-opener to you. The business case is as follows. You are the manager of a company called Great Outdoors (sounds familiar, no ? :-) ) and you are planning on opening new stores in a number of regions. This will increase revenues and profit, yet the initial opening costs will be substantial too. While being granted a budget of 100,000,000 $ you will need to come up a number of viable scenarios.

TM1 cube and data input

The underlying TM1 cube is as follows:

You see the picklists for 'Retailer Type' and 'Size'. These 2 measures (and 2 other measures not shown) determine the 'Total opening costs' for that kind of store configuration. This information is captured in a different cube, following best practices for a cubes architecture setup. The user can then choose how many such stores he plans to open (in a chosen month - not shown here), which leads to the Total costs for all the new stores combined. That total should not exceed the given budget.

Now over to Excel and its tables. If you want to follow along with me and do the same exercise as I did, go to the TM1 samples (if you installed them). One of the TM1 models there is used for my illustrations. Okay, first slice the view above to Excel, and you will get something like this:

Fair enough, this is not that fancy and appealing sheet that you want to give to budget owners as input collection sheet. Therefore, we will rework the sheet by doing the following trivial manipulations (the list is long but exhaustive):

  1. Save your file ! TM1 and Excel will not always play nicely together;
  2. Remove the formatting for cell F6;
  3. Type in cell A6 the header 'Line number';
  4. Move the cube name and the title selections (cells A1:B3) over to, say, cell H6;
  5. Delete rows 1 to 5;
  6. Delete rows 3 to 11 (or use a smaller cube view to slice from);
  7. Select cells A1:F2 and insert a Table (press Ctrl + T to bring up the 'Create Table' dialog screen. Create the table by checking the headers box;
  8. If wanted, change the table layout;
  9. If wanted, uncheck 'Banded Rows' in the 'Table Tools' ribbon menu, the 'Design' group;
  10. Add a formula to cell A2: ="Line " & TEXT(ROW()-1,"00"). This formula will format your line number;
  11. Format cells E2:F2 as dollar amounts;
  12. Also in the 'Table Tools' ribbon menu, the 'Design' group, add a 'Total Row'. By default, you will get the sum of the values above;
  13. Turn on automatic calculations of formulas, unless your users are the type of persons that generally open up many more spreadsheets at a time than needed;
  14. You have just finished the setup. Congratulations!

The result will be more or less equal to what I print below:

Now your input can start. Go to cell A2 and tab to cell B2. Choose a 'Retailer Type' and tab to cell C2 where you pick a 'Size'. Total opening costs per store will change if you also set the other 2 measures that I left out in the discussion of this article. Specifically, the measures 'Franchise/Corporate' and 'Asset Type' were omitted, and are needed to come to an Opening cost for a store. Tab to cell D2 and enter a non-zero value for the number of new stores to be built. You will notice the total opening cost needed for your chosen number of stores (note that this calculation, a simple multiplication, is done in TM1 rather than Excel - we hold all logic in the model and not in Excel, unless it’s too heavy or cumbersome for the cube).

Adding a new line number

Here is the upshot: if you tab to cell F2 and then tab again, a new line will be inserted! All formatting and all formulas will be filled down! Both Excel formulas (see column A) and TM1 formulas (DBRW) will be copied down. No need to refresh the sheet. No need for VBA coding at all. A Turbo Integrator process could transfer the values from this input cube to a different cube.

It gets even more interesting when we will use Conditional formatting to indicate whether the budget was exhausted or not. I will keep this idea for one of the upcoming TM1 blog articles on my website.

Advantages of this approach

I see a good number of advantages when we organize the data input to TM1 cubes using data tables. Some of them include:

  • Automatic inserting of line numbers (provided the respective dimension holds enough line number ID’s);
  • Automatic filldown of any formulas and formatting, increasing consistency;
  • Tables are built-in functionality in Excel and VBA (where they are called ListObjects in the Object Model). They expose a good deal of properties and methods for more automation through VBA;
  • Charts, sparklines and other functionality based of tables will be dynamic: a chart could show 4 input numbers, and automatically extend to 5 when you press tab in the last column. Here is an illustration of sparklines based on 4 and 5 rows of input, tabbing is sufficient:

  • You do not need to worry anymore about providing too many rows to the user, which could impact performance in a negative way. The user tabs until all input is done. The usual 'spacer' trick from IBM is circumvented with this approach;
  • Excel formulas within tables are much more readable than the usual cell references and ranges. For example, check out the differences, but read carefuly the first of the disadantages below:

Disadvantages of this approach

On the other hand, let us not be blind for the drawbacks:

  • The structured referencing to other columns in the same table (or a different table) works fine as an input argument to TM1 formulas like DBRW: the formula will update and show the correct result. However, you cannot do input on a DBRW formula where you use this kind of notation. You will need to stick with the usual cell references and/or named ranges and/or formulas;
  • TM1Web does not support the tables in the websheets;
  • Subsets - dynamic or static - driving the rows in an input sheet, will be more difficult to implement. Therefore, primarily cases where input is done in a 'line number' approach, will be useful candidates for tables;
  • If a different title element is chosen, the DBRW formulas will reflect the new selection, but you might see too many 'rows': you can easily delete them or use VBA. Or, use an IF statement that checks the current row number against a (user-chosen) number of input lines. If more rows than necessary, bring the row number to "" instead of a valid row number.
  • Just as with the 'spacer' trick, one will want to provide a (small) mechanism to allow the user to clear values. If not, picklists will work against the user at the time of clearing a cell;
  • Lastly, let me just add that the TM1 Tools cannot handle these structured cell references.

Wrap-up

I showed you an interesting new approach to collect data from users, by 'line number'. I hope that this article can inspire you in a great deal.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links