Opinions & Best Practices for Front-end Design

[20 Ton Squirrel]

I'm still a newbling on TM1 development but I've had huge success in designing cube structures and processes… now I'm getting to the part where I need to build a user front-end and I'm hitting some issues.

TM1Web, PAW, and PAX provide a flexible, robust front-end for users. Each has their merits but, regardless of the interface, there are complaints that jeopardize user acceptance of the tool. The analysts I deal with are technologically savvy but they are phobic of change, so the slightest flaw sends them fleeing to the trees.

There are more than a few complaints, but here are the major two:

• Relay speed: The analysts I develop for are accustomed to Excel workbooks and expect near-instantaneous response when entering data. Any of the three interfaces suffer at least a few milliseconds for calculation or transfer time.
• Clipboard functionality: You can't paste data into the static or dynamic reports in PAX. There are some browsers like Chrome that muck up pasting into PAW. This is a major problem. There might be add-ins for Excel or Chrome that facilitate this but it seems very awkward to need external options to perform such a basic function.

These issues tempt me into learning the REST API to concoct some VBA-driven Excel solution, but before I dive down that rabbit hole I was curious how you venerated masters of TM1 approach these situations.

[burnstripe]

The clipboard functionality exists but you'll require a plug-in for newer browsers. (this will need installing on each machine your accessing tm1web/workspace from

https://www.ibm.com/support/pages/plugi ... te-tm1-web

My advice would be to stick with the out-of-the-box tools, tm1web/paw/pax for a user interface. If only to ensure the interface you've worked hard to build remains supported and successors can pick up the toolset. Planning Analytics is still a relatively niche job market, so if you venture from the main tools, models could fall into disrepair once your gone, as successors may not have the knowledge to support it.

I would steer clear of going overboard with VBA as they can be hard to maintain/update and rollout. By all means you can have some vba code to help with tasks, but I wouldn't rely on it as the primary interface for users.

Rest api, there is a large learning curve, and to develop an website/interface that api will take considerable time.

Regarding rely speed/interaction there are many factors that can affect performance, such as volume of data, calculation times and network speed...

I would also say excel itself is not prone to performance problems which can get unwieldy when they get too large or have too many links/lookups

Also try to avoid mass data dumps, teach users to interact with slices of the cube, rather than them exporting everything to excel as this will cripple the system and hinders security of data.

It sounds like you're users love excel, so maybe start with Pax to get them on board. If users need to contribute data, then you could try using explorations. If you know how to write a view on mdx you could also define exactly what data you wish to retrieve and no formulae are needed so users can paste merrily.

With Tm1web there are also various config settings which can enhance the user experience, such as choosing when to recalculate and picklist work very nicely to control user inputs

[20 Ton Squirrel]

This is fantastic advice, thanks burnstripe!

The clipboard functionality exists but you'll require a plug-in for newer browsers. (this will need installing on each machine your accessing tm1web/workspace from

«quietly sobs and blows snot bubbles in the corner»

My advice would be to stick with the out-of-the-box tools, tm1web/paw/pax for a user interface.

I confess that I'm so accustomed to building around problems that I often don't really consider how it would be maintained beyond me. The learning curve for the API is daunting, though, so that alone might frighten me away. You are the voice of reason on this one; I need to evaluate their requirements better before attempting to re-engineer the wheel.

Also try to avoid mass data dumps, teach users to interact with slices of the cube, rather than them exporting everything to excel as this will cripple the system and hinders security of data.

This is a tough one to approach, the main user is accustomed to seeing his entire dataset in one large Excel table. You raise a good point any developer should consider, however. I need to interview him a bit more and see how much data is absolutely necessary to see all at once.

It sounds like you're users love excel, so maybe start with Pax to get them on board. If users need to contribute data, then you could try using explorations. If you know how to write a view on mdx you could also define exactly what data you wish to retrieve and no formulae are needed so users can paste merrily.

My users are Excel fanatics even if their grasp of a spreadsheet is rudimentary. PAX seemed the way to go initially but the lack of support in pasting values sent my PAX demonstration into flames, the analysts all chimed up about refusing to use it if they couldn't copy/paste. Perhaps an exploration view with MDX really is the answer. I'm learning MDX, thus far it doesn't seem too difficult (thanks to Wim's website).

[Adam]

You can't paste data into the static or dynamic reports in PAX.

PAX/PAFE has 4 report types: Custom Reports, Dynamic Reports, Quick Reports, and Explorations.

Have you tried Quick Reports? It creates a formula-less grid and allows copy/paste as well as linking to other cells, workbooks, like mainstream Excel.

[20 Ton Squirrel]

PAX/PAFE has 4 report types: Custom Reports, Dynamic Reports, Quick Reports, and Explorations.

Have you tried Quick Reports? It creates a formula-less grid and allows copy/paste as well as linking to other cells, workbooks, like mainstream Excel.

If I recall correctly, Quick Reports are not dynamic so as the user edits on dimension elements the view would not update to reflect that. This breed of view isn't viable for the main interface I need to build. It is good to know, however, that you CAN use the clipboard for Quick Reports. I can use those where the view is static.

[paulsimon]

Hi

When it comes to typing in a value and seeing the result immediately, a single user working on their own data is probably always going to get a better response from an Excel sheet. However, planning is not about working alone. We still have some users who do spreadsheet based collections and then use macros to consolidate them together. The run time to do this is often around two days, and that assumes that it does not fail part way through. The macros used to do the consolidation are poorly written and no one would be able to maintain them if the user that wrote them left. They tend to need adjustments for every run which is a development overhead.

By comparison when budgeting, and forecasting are done in TM1 as soon as one user commits the figures for a Cost Centre, the effect can immediately be seen at all levels of the hierarchy.

We are comparing apples and pears.

If users want to work in isolation on their own data, then they might was well continue to work in Excel.

However, if users want to work collaboratively to combine data, immediately consolidate and review data by different hierarchies, and if they have standard ways to calculate ratios, depreciation, etc, then they should use TM1. Most large companies have this requirement.

Another area where TM1 comes to fore is in reporting. It is easy to maintain multiple views of the data. You can do almost the same in Excel with Pivot Tables, but they run out of steam as the size of data grows.

As was pointed out earlier, as soon as data is exported to Excel there is no security on it.

Similarly, if an Excel sheet is lost then so is the data. By comparison, data held centrally in a server can be backed up.

TM1 also gives an audit trail of who changed the data, the before an after value, etc.

We use TM1 Web for data collection. PAW was not available at the time the system was built. Converting a Spreadsheet to a Web Sheet is simple. It gets past the problem of formulae being corrupted. Yes, you need to download the Chrome Extension for copy and paste, but that only takes a few minutes and after that it is there.

We still have users who want big extracts. We produce these via TI process write them as CSV files to the Web Server, and give users the ability to download them. That is a faster option than users trying to get big extracts out of the live system, as the extracts can be produced overnight when the system is quiet, and at least the extract is only produced once, not once per user. It did need a small amount of HTML development but not exactly rocket science.

You could of course develop something with the TM1 Rest API but it is not easy. If you do go that way, I would recommend the DevExtreme Grid and other front end widgets.

If all else fails there are third party tools such as Cockpit and Apliqo. However, they come at a price.

Regards


Paul SImon

[Adam]

If I recall correctly, Quick Reports are not dynamic so as the user edits on dimension elements the view would not update to reflect that.

I would recommend enabling "Use type-in refresh" in PAFE options. Then, try adjusting the dimension elements. The grid will update to reflect the typed-in dimension change.

[JamiseBondi]

The clipboard functionality exists but you'll require a plug-in for newer browsers. (this will need installing on each machine your accessing tm1web/workspace from

https://www.ibm.com/support/pages/plugi ... te-tm1-web

My advice would be to stick with the out-of-the-box tools, tm1web/paw/pax for a user interface. If only to ensure the interface you've worked hard to build remains supported and successors can pick up the toolset. Planning Analytics is still a relatively niche job market, so if you venture from the main tools, models could fall into disrepair once your gone, as successors may not have the knowledge to support it.

I would steer clear of going overboard with VBA as they can be hard to maintain/update and rollout. By all means you can have some vba code to help with tasks, but I wouldn't rely on it as the primary interface for users.

Rest api, there is a large learning curve, and to develop an website/interface that api will take considerable time.

Regarding rely speed/interaction there are many factors that can affect performance, such as volume of data, calculation times and network speed...

I would also say excel itself is not prone to performance problems which can get unwieldy when they get too large or have too many links/lookups

Also try to avoid mass data dumps, teach users to interact with slices of the cube, rather than them exporting everything to excel as this will cripple the system and hinders security of data.

It sounds like you're users love excel, so maybe start with Pax to get them on board. If users need to contribute data, then you could try using explorations. If you know how to write a view on mdx you could also define exactly what data you wish to retrieve and no formulae are needed so users can paste merrily.

With Tm1web there are also various config settings which can enhance the user experience, such as choosing when to recalculate and picklist work very nicely to control user inputs

Assuming you're using MS Edge browser - have you tried the "Enterprise Mode Site List" feature which 'dumbs down' Edge to use IE11 compatibility. We've got copy/paste working in Edge using this setting without having to install any plugins. Hope it helps.