Options for Budget Input
Posted: Mon Oct 17, 2011 4:32 pm
Can any of you experts give me some advice on what is the best way to develop a budget input user interface in TM1?
The scenario is:
* The main input is sales quantities by product by customer
* There are 20 to 30 territory managers who will be inputting the sales quantities
* Each territory has 50 or more customers
* The product list is summarized for budgets but there are still more than 500 values
* Some products are discontinued each year some are new introductions
* There are new customers as well without sales history
* Each customer tends to only purchase a limited number of products
* Displaying only previously sold products for each customer is preferable though new products will need to be added
* Displaying last year's sales quantities at the time of entry is preferable
* Text string comments are a nice to have
* Submissions workflow is a nice to have
* The TM1 version is 9.5.2, the existing TM1 system is used for sales reporting not budgets
This is probably a fairly standard scenario for an inventory business?
Option 1 - TM1 Contributor and Views
My first thought was to use TM1 Contributor. With two views / tabs. Each view would have customer at the top of the page, then product in the rows and months in the columns. One view would be non zero suppressed and would be for finding the products and entering the quantities. It would need to have the year in the columns as well to display last year's quantities. That is it would need to have 26 columns for 12 months and total for each of this year and last year. The second view would be zero suppressed, not contain the year in the columns, and would display the resulting budget for this year, quantities as well as the calculated currency amounts. I figured that a UNIONed MDX subset could be used to give a list of products that were sold last year plus new products, but this would be for the system as a whole, not for the relevant customer as an element from a view cannot be passed into an MDX subset can it? I would also have to copy last year's actual sales quantities to the budget version to prevent the "this year actual, last year actual, this year budget, last year budget" display duplication. The territory managers would then need to work through the 500 long list of products for each customer and enter quantities. They would see the last year quantities for each product for that customer, so that would help, but there would be lots of zero lines. I concluded that this approach would work but it would be very unwieldy to go through 500 plus products for each of up to 100 customers for each territory manager. I also concluded that any other view based UI would also have this 100 x 500 selection problem, that is Perspectives, Executive Viewer etc. And I am not sure about the use of 26 columns. That sounds wrong.
Option 2 - Worksheets and Active Forms
I had thought that nearly all budget input in TM1 was via worksheet or websheet applications. Is that true? But when I considered this, I concluded that worksheets themselves would not help me as the products in the rows would be static. An active form in the worksheet would give me dynamic product lists in the rows, but the product selection problems in a view would apply to an active form too.
But then I started thinking about using a combination of an Excel worksheet, two active forms above and below each other, some DBRWs, some IFs, an Excel data filter, and some VBA. The first Active form would have customer at the top, product in the rows, and month along the columns. As the two active forms need to have the same columns, the year would have to be in the rows of this first active form as well. Like the first Contributor view it would be non zero suppressed and would be for finding the products and entering the quantities. The second active form would be like the second Contributor view and would be zero suppressed. It would just display the resulting budget for the budget year for the territory. The DBRWs and IFs would be to the right of the first active form. One DBRW would pull last year's sales for that customer / product against both the last year and this year rows. The second could pull 0 or 1 from a cube or subset of new products. The IF would be T if there were sales for the product or the product was new and F otherwise. The VBA and filter would then supress all the products which are not applicable to the customer. I would not have to copy last year's actual sales quantities to the budget version to prevent duplication as the DBRWs could be configured to pull actual last year. The user would enter the customer, run the VBA by pressing a button, the active form would start by listing all products, duplicated by year, the DBRWs/IFs/filter would then restrict the active form to show only products relevant to that customer, there would be two rows for each product, last year and this year, the user could then enter the budget quantities for this year, click on another VBA button to F9 and rebuild the active form, and see the result in the second active form.
I concluded that this option might just work but that it would be a client program maintenance nightmare. And this approach would not allow for the adding of ad hoc extra products inserted by the user, that is selling additional existing products that were not sold last year but which are not new. To do this the user would have to switch off the filter, find the product, add some quantities for this year, and then re filter.
Option 3 - TI Generated Subsets
I then started thinking about using TM1 dimension subsets as what I want to do is have a subset of relevant products for each customer to simplify the entry. I would need to have 1000 - 2000 subsets created in the products dimension! Is this even possible? These subsets could be created by TI with the customer number as the subset name, have the products sold to each customer last year inserted by TI, have the new products for this year inserted by TI, and any ad hoc extra products for the customer could be inserted by the user from a worksheet application using action buttons and TI. If I was happy with thousands of subsets created in the products dimension then this option 3 might work.
I would have to use a worksheet / active form approach to allow for an action button. I would have to train the users to select the same customer number twice in the active form, as both the customer number and then as the subset name for products. But that is OK and much better than some of the user training mentioned above. And if I am using a worksheet perhaps there is some way of defaulting the subset name in the SUBNM off the customer number cell? Indeed could this be done using Excel concatenations? I think this option 3 might work for me.
Option 4 – Working Cube
This would involve creating a measures dimension with perhaps 17 elements: Last year Actual Total, 12 months for entry of this year, Total this year, Total sales value this year, a display flag, and perhaps a text comment field. And then creating a cube with this measures dimension, product, customer, but no version or time as these will be built into the measures. And this measure would get rid of the 26 columns problem as last year would only be a single field. I could then use TI to populate this working cube with last year's sales by customer and update the display flag to T. I could use another TI to insert new products with no sales for last year but with the display flag set to T. And use an action button TI for ad hoc additions. And then use a active form or perhaps view to enter the 12 months of this year. The active form would have to supress zeros in rows but not in columns, hence the display flag would show all relevant product records. This might work and might be neater than 1000 subsets.
Am I on the right track with any of these options? Have I missed something really obvious? What do people do in real life for this scenario? Does everyone just use option 1?
The scenario is:
* The main input is sales quantities by product by customer
* There are 20 to 30 territory managers who will be inputting the sales quantities
* Each territory has 50 or more customers
* The product list is summarized for budgets but there are still more than 500 values
* Some products are discontinued each year some are new introductions
* There are new customers as well without sales history
* Each customer tends to only purchase a limited number of products
* Displaying only previously sold products for each customer is preferable though new products will need to be added
* Displaying last year's sales quantities at the time of entry is preferable
* Text string comments are a nice to have
* Submissions workflow is a nice to have
* The TM1 version is 9.5.2, the existing TM1 system is used for sales reporting not budgets
This is probably a fairly standard scenario for an inventory business?
Option 1 - TM1 Contributor and Views
My first thought was to use TM1 Contributor. With two views / tabs. Each view would have customer at the top of the page, then product in the rows and months in the columns. One view would be non zero suppressed and would be for finding the products and entering the quantities. It would need to have the year in the columns as well to display last year's quantities. That is it would need to have 26 columns for 12 months and total for each of this year and last year. The second view would be zero suppressed, not contain the year in the columns, and would display the resulting budget for this year, quantities as well as the calculated currency amounts. I figured that a UNIONed MDX subset could be used to give a list of products that were sold last year plus new products, but this would be for the system as a whole, not for the relevant customer as an element from a view cannot be passed into an MDX subset can it? I would also have to copy last year's actual sales quantities to the budget version to prevent the "this year actual, last year actual, this year budget, last year budget" display duplication. The territory managers would then need to work through the 500 long list of products for each customer and enter quantities. They would see the last year quantities for each product for that customer, so that would help, but there would be lots of zero lines. I concluded that this approach would work but it would be very unwieldy to go through 500 plus products for each of up to 100 customers for each territory manager. I also concluded that any other view based UI would also have this 100 x 500 selection problem, that is Perspectives, Executive Viewer etc. And I am not sure about the use of 26 columns. That sounds wrong.
Option 2 - Worksheets and Active Forms
I had thought that nearly all budget input in TM1 was via worksheet or websheet applications. Is that true? But when I considered this, I concluded that worksheets themselves would not help me as the products in the rows would be static. An active form in the worksheet would give me dynamic product lists in the rows, but the product selection problems in a view would apply to an active form too.
But then I started thinking about using a combination of an Excel worksheet, two active forms above and below each other, some DBRWs, some IFs, an Excel data filter, and some VBA. The first Active form would have customer at the top, product in the rows, and month along the columns. As the two active forms need to have the same columns, the year would have to be in the rows of this first active form as well. Like the first Contributor view it would be non zero suppressed and would be for finding the products and entering the quantities. The second active form would be like the second Contributor view and would be zero suppressed. It would just display the resulting budget for the budget year for the territory. The DBRWs and IFs would be to the right of the first active form. One DBRW would pull last year's sales for that customer / product against both the last year and this year rows. The second could pull 0 or 1 from a cube or subset of new products. The IF would be T if there were sales for the product or the product was new and F otherwise. The VBA and filter would then supress all the products which are not applicable to the customer. I would not have to copy last year's actual sales quantities to the budget version to prevent duplication as the DBRWs could be configured to pull actual last year. The user would enter the customer, run the VBA by pressing a button, the active form would start by listing all products, duplicated by year, the DBRWs/IFs/filter would then restrict the active form to show only products relevant to that customer, there would be two rows for each product, last year and this year, the user could then enter the budget quantities for this year, click on another VBA button to F9 and rebuild the active form, and see the result in the second active form.
I concluded that this option might just work but that it would be a client program maintenance nightmare. And this approach would not allow for the adding of ad hoc extra products inserted by the user, that is selling additional existing products that were not sold last year but which are not new. To do this the user would have to switch off the filter, find the product, add some quantities for this year, and then re filter.
Option 3 - TI Generated Subsets
I then started thinking about using TM1 dimension subsets as what I want to do is have a subset of relevant products for each customer to simplify the entry. I would need to have 1000 - 2000 subsets created in the products dimension! Is this even possible? These subsets could be created by TI with the customer number as the subset name, have the products sold to each customer last year inserted by TI, have the new products for this year inserted by TI, and any ad hoc extra products for the customer could be inserted by the user from a worksheet application using action buttons and TI. If I was happy with thousands of subsets created in the products dimension then this option 3 might work.
I would have to use a worksheet / active form approach to allow for an action button. I would have to train the users to select the same customer number twice in the active form, as both the customer number and then as the subset name for products. But that is OK and much better than some of the user training mentioned above. And if I am using a worksheet perhaps there is some way of defaulting the subset name in the SUBNM off the customer number cell? Indeed could this be done using Excel concatenations? I think this option 3 might work for me.
Option 4 – Working Cube
This would involve creating a measures dimension with perhaps 17 elements: Last year Actual Total, 12 months for entry of this year, Total this year, Total sales value this year, a display flag, and perhaps a text comment field. And then creating a cube with this measures dimension, product, customer, but no version or time as these will be built into the measures. And this measure would get rid of the 26 columns problem as last year would only be a single field. I could then use TI to populate this working cube with last year's sales by customer and update the display flag to T. I could use another TI to insert new products with no sales for last year but with the display flag set to T. And use an action button TI for ad hoc additions. And then use a active form or perhaps view to enter the 12 months of this year. The active form would have to supress zeros in rows but not in columns, hence the display flag would show all relevant product records. This might work and might be neater than 1000 subsets.
Am I on the right track with any of these options? Have I missed something really obvious? What do people do in real life for this scenario? Does everyone just use option 1?