TM1 Forum

Hi All,

In my client's business we have Partners who sell products. Some products are sold by multiple partners. New products are added regularly.
4 years ago I build a generator in vba which generates a report (a worksheet) for each product sold by a partner. Each report comprises a whole bunch of metrics over time from 2 different cubes, plus 4 charts. The generator first opens the report template consisting of dbrw's, retrieves the the values for the product, copies the results into a new sheet, replaces the formulas with values, then repeats for each of the products sold by the partner. This has worked well for the client for the last 4 years and is relatively fast. e.g. for a partner with 4 products, plus a total of those products, the Workbook containing the 5 reports is built in 22 seconds.

Now the client would like to do something similar on the web to provide direct access to partners via an extranet, so I've set up a prototype using the same report template. To generate the report for each product I just do a Snapshot to Excel Export from TM1 Web and specify the Product dimension to export. This works fine, but for the above example (5 report worksheets) it takes 5 minutes! I've tried setting the UseBookRecalcSetting in web.config to true (the template is set to Manual Calc) and even experimented with the BlockFetchCellSize setting, so far to no avail. (doing an iisreset and clearing the web cache after each change). Of course once the report values have been cached on the server it runs in a similar time as the generator, but the generator takes the same time the first time after the daily load as subsequent times and we need the web report to run in a reasonable time immediately after the daily load.

The environment is TM1/TM1 Web 952 | Windows Server 2008 R2 Standard | IIS 7.0 | Excel 2007.

Any thoughts?

Thanks,

Gregory

Perhaps its time to stop trying to turn TM1 into Cognos BI and start using the features that make it popular. Why the need to create a range-valued web sheet for each partner. You could create a single standard template that has the report layout you want, data tables, charts, etc., and have a drop-down that lets the partner select which product they want to look at. If you secure the product dimension then a partner will only be able to select the products they sell. If you absolutely have to have the range valued sheet via VBA then leave that for Perspectives,

Hi Tomok,

thanks for your reply. I take your point and what you are suggesting is exactly what I've done for the TM1 Web prototype. What the client wants for their partners, however, is the ability to print out the report for each one of their products & pass copies around in meetings etc. They do not have TM1 so the options are to send them the reports or allow them to run them themselves via TM1 Web. At the moment we are sending them daily and if we can't get them to run at a reasonable speed on TM1 Web we will have to continue to do so.

cheers,

Gregory

Why not have an overnight burst process run on the server with windows scheduler that cycles through all the customers and prints the report output to pdf with a set file naming convention then just have a websheet using the hyperlink function to retrieve the correct pdf?

Wouldn't that get the job done?

GPC wrote:This works fine, but for the above example (5 report worksheets) it takes 5 minutes! ... Of course once the report values have been cached on the server it runs in a similar time as the generator, but the generator takes the same time the first time after the daily load as subsequent times and we need the web report to run in a reasonable time immediately after the daily load.

It looks like your Excel application is doing something that TM1 Web isn't regarding caching the new results. If you need to report after e.g. intra-day loads then perhaps some judicious use of ViewConstruct could help? Really you are masking the delay in the data-load process but it would cut down on user frustration.

The documentation was never quite clear on whether a restart of TM1 was required post a ViewConstruct ? Does one need to restart TM1 services post a ViewConstruct ?

nick_leeson wrote:The documentation was never quite clear on whether a restart of TM1 was required post a ViewConstruct ? Does one need to restart TM1 services post a ViewConstruct ?

No, re-starting the TM1 service will clear all the stargate (cached data) views in memory - so you wouldn't want to do that after a ViewConstruct. Using ViewConstruct in TI is a way of programatically caching the views so that e.g. delays can be avoided when a user opens a cube-view or report for the first time after a data load.

In the case of the OP, working out what the correct view to cache is the difficult part. I'd guess looking at the =VIEW formula he uses in his Excel generator application would be the right place to start.

Hi lotsaram,

Thankyou for your suggestion. We are actually doing the first part at the moment i.e. scheduling the generation, then we are emailing the reports as we don't yet have the capability to host them on an extranet.

My client's objective is to empower the Partners to produce their own reports on demand - there are other dimensions they can analyse as well.

What I was hoping to achieve with my original question was some insight as to why the web takes so much longer to produce the same reports. It seems to have something to do with the way TM1 Web retrieves the values when it cycles through the dimension elements - changing the Product selector at the top of the worksheet retrieves the data for the selected product quite quickly - 2-3 seconds. Looking at TM1 Top while the web export is in progress is indicating that the process is retrieving the values for the second cube.

I wonder if anyone else has found the dimension based export to be slower than a worksheet/websheet update, particularly with a report that is based on multiple cubes?

cheers,

Gregory

By Chance have you noticed something like R<#> IX<#> W<#> in TM1Top ? This would indicate locking. Have you enabled Parallel Interaction ?

Hi Nick,

good thought. Yes the Object Lock status is (R) 1 (IX) 0 (W) 0 and TM1 Top is allways showing this on the 2nd Cube (there are actually 3 cube VIEW's on the template). I turned on PI, retarted the Server & the time and the locking status is still the same. There are no dependencies between the 3 cubes so I'm puzzled as to why there is a lock.

I tried disabling the VIEW for the 2nd cube in the template and the whole export ran in 45 seconds, so the 2nd cube is definately implicated. It has 15 dimansions, one of which has over a million elements, so that could have something to do with it, although it only seems to have an impact on the export function...

cheers,

Gregory

Have you got Dynamic Subsets in any of the Websheets ??

Hi Nick,

There are no dynamic subsets in the template.

I decided to do a cube optimisation on the 3 cubes involved & it has improved the speed quite markedly. It is now down to 70-90 seconds. Cube 1 gave a 20% reduction in memory, Cube 2 74% (from 1.1Gb to 253Mb) and cube 3, 37%. However after a server reboot and another daily load Cube 2 went up to 581 Mb. This is a bit puzzling as it should have only reloaded the same data that was loaded this morning. I'll continue to watch the memory usage and test the export speed...

cheers,

Gregory

One other thing make sure your workbooks published to the web are as small and lean as possible! Especially make sure there aren't additional rows and columns included in the used range that aren't actually being used, sometimes by deleting additional rows and columns you can dramatically reduce the file size and the export time of the worksheet.