Excel / Websheet cell dependencies

[martinwagnerde]

I´m quite new to the TM1 technology but nontheless I was honored to build a complex websheet frontend using active forms. But now I am stuck.

The requirement is a websheet display that contains a data selection area (built by SUBNMs), the filtered output of three cubes and a chart (the chart is the only thing that seems to work fine, once the data ist displayed properly)

Based on the data selection (Week From / Week To) I use to restrict the displayed data in a three step approach:
1.) Displaying all calender weeks from one cube and selecting the needed data using a named area based on the the excel OFFSET command.
2.) The named area is used as subset element parameter in a TM1RPTROW command
3.) Nested under the calender weeks there is a order ID dimension. These order ID element names are selected by another OFFSET and then passed as a subset element parameter to another active form on the display page

The only way I got this working was to sort the (hidden) worksheet pages in order of calculation. Worked fine, but the performance was poor.

No I´m trying to reduce the number of sheets. But putting two related active formes in a top down order of calculation on one page proofed not to be stable. Sometimes it works, sometimes not. On the web it rather does not work at all.

Putting everything on one page does not work either. Putting the preceding active forms above the displayed active forms does not work because I can not hide the result of the preceding active forms permanently. Putting them far down below row 1000 does not work either because the active forms do not seem to be processed in the order needed.

Got anyone a clue on this?
THIA martin

[ScottW]

Hi Martin,

I'm surprised there haven't been any responses to your question, it sounds like an interesting problem. It might be because it is a little difficult to figure out what exactly you are doing with your report.

For a start TM1 web is not optimised for large websheets. A better design approach would be to use individual active forms on individual sheets and use action buttons to contextually navigate from one sheet to another. I wouldn't recommend putting 3 large active forms in a single sheet.

Your design approach is also a little confusing. By the sounds of it with the sequential approach you are actually using Excel to do a lot of the logical processing or "heavy lifting" in getting to the final result (bear in mind that a web sheet is NOT Excel and doesn't have the same calculation efficiency). Have you considered an approach to do the filtering and logic in intermediate staging cubes for the report or use TI? This could give a result in less steps for the user and with better performance for the user.

I hope that helps at least a bit, but it is hard to decipher what exactly your problem is and what approaches there might be to solve it.

[martinwagnerde]

Hi,

thank you for your recommendations. I had already got the idea that the developers of TM1 didn´t expect anyone to use TM1 the way we do. But once the decision aboout the software is made, we have to get the best of it.

I now found out, that I can get rid of at least one of the Active Forms by an approach of element names for the calender week that are calculated, while displaying an attribute. And due to the project documentation I can drop some additional functions where classic slices can do the job.

But since one can only select one element in a subnm we need two dependent active forms, because there are two inputs "weeks from" and "weeks to" that refer to the same dimenison (a generic olap design problem). So I first have to select the corresponing weeks in one active form and pass from there the correspodning orderIDs to another. These interrelated active forms seem to be a real performance killer, having the subnm selection window opened for 90 seconds after klicking ok. No customer will accept that kind of system behaviour. I wonder quite a lot, what might be eating up all this performance, because waiting all this time does not mean that the active forms show any reaction - one has to update them by a click to the refresh icon afterwards.

So, as all our tests failed - we did a lot by combining Excel cell cell references that do not work as line headers with classic slices, neither do refrences using the INDIRECT excel function; mixing active forms to fill the line headers and classic slices to fill the measures work fine in Excel, but not on the web etc. - now we reached the point to decide to build a huge cube that has all the dimensions and all the facts. Maybe that can help us out.

CU
martin

[lotsaram]

But once the decision aboout the software is made, we have to get the best of it.

... and to get the best out of TM1 the approach may be very different from other systems! It might pay to get some good training and advice on TM1 cube and report design. One of the problems with this tool is that it is very flexible and there is more often than not many alternate ways to solve a given problem.

I say this because you are talking about building a "huge cube with all dimensions and facts". This is seldom the best approach in TM1 as data can be easily shared between cubes via rules which are quite flexible.

[Steve Rowe]

This approach may not work as I'm not using versions that support active forms and action buttons yet so I could be suggesting stuff that just does not work.

My understanding of what you are trying to do is, using a start and finish week number derive a list of Order Ids that are populated/valid. Use this list of valid Orider Ids as row items to populate a report.

It is running too slowly because you are pulling a list of all Order IDs into the workbook in order to make your valid Order ID selection, so in order to speed the creation up we need to move the creation of the subset server side.

Suggestion
Once you have the start and end period selected, populate a "control" cube with the start and finish data. (I don't mean a } cube, just a 2d cube to store standing data)
Use a rule based attibute on the Order ID dimension and the start and finish date to flag valid Order Ids
Set up a dynamic MDX subset that runs off the attribute flag as the ValidOrderID subset.
Reference the ValidOrderID subset on the rows of your web report using Subnm formula.

I hope this gives you an idea of an alternate approach, I'd have some concerns about how this would work in a multi-user environment as the dynamic MDX subset would be constantly changing. If action buttons work in TM1 Web then you could replace the some of the above with a TI process but then you are moving away from a truly dynamic approach.

I think this is probably reinforcing Scotts point that you need to find ways of doing the "grunt" work on the server rather than in the reporting environment, expecially if it is TM1Web

Obviously if an active form on TM1Web is taking 90 seconds to accept a selection of an element then that's something I would be raising with IBM directly as a bug. If you have not already tried it might be worth trying to discuss what you are trying to build with directly with IBM, some of their pre-sales guys were able to build some pretty funky stuff in TM1 Web.

HTH
Cheers

[martinwagnerde]

Hi,

sorry for letting you wait, but after we reached a satisfying state in our project, I wanted to say "thank you" for your support. It was rather a hard time, including night shifts and all the things that occure on an late project. What we learned from your suggestions is to keep the frontend slim and pass all calcuations to a backend TI process. And whar we learned too is that "stacking" Active Forms, ie. referencing the result of one AF as selection for another AF is a no-go.

He is the solution we found:
We banned all Active Forms from the project, and used Classic Slices that referenced dimension subsets, which are calculated on demand. The line headers are made with SUBNMs and a running index up to 1000. As these calculated subsets could not use a fixed name in multiuser env we found that a concatenation of dimension element names is a generic approach that will give a correct respond even if two users create / access the same subset at the same time. Using the TM1User command will fail with a C8/LDAP security because it returnes a string with double quotes and Excel string operations are nor reliable in websheets. And having a TI process giving back result parameters to a frontend or each others is still a developer´s dream.
Because of slashes in dimension element names this concatenation subset naming caused many troubles, so I will recommend to name dimension elements with surrogate keys and use aliases an attributes für the user frontend.

Another conclusion is to check out if the user frontend as well as the backend processes, that work on the cubes can be realized by using the API. Using Excel brings a lot of surprises - some well tested frontend will fail to work while prensenting it to the customer :-O. And using TI for complex backend processes will bring problems as there is no runtime error handling, no debugging features, and no functions or process structures.

CU
martin