ViewCreatebyExpression Tm1 9.4 Cognos 8.4

[Olivier]

Hi All,

We have set up a few Cognos BI reports using Report Studio that query one of our Tm1 cubes.
Our BI version is 8.4 and our Tm1 version is 9.4MR1 FP3 HF12.

I noticed that every time a BI report get updated and read data from Tm1,
a specific function "ViewCreatebyExpression" is be executed which I can see from the Tm1 Top tool.

This function seems to take much longer to run then a classic "viewarrayconstruct" that is run when a user open or refresh a view from Tm1 server explorer for an equivalent piece of data...

I could live with that but unfortunately it seems that these Report Studio MDX queries to the Tm1 server are quite heavy and perturbate the Tm1 user experience.

What I am trying to understand is why the execution of this function "ViewCreatebyExpression" impact so heavily the Tm1 Server ?

For example,
it would put a login attempt from a user in "Wait" mode while it is executing, As these "viewcreatebyexpression" are usually lengthy, it give the user the impression that the Tm1 system is not accessible...

Another impact I have noticed is that it put any TI triggered after "ViewCreatebyExpression" started in "Wait" mode until it is completed...regardless of what the TI is doing or where it is writing to...

I might assume wrongly that the behaviour is different from what happen with "viewarrayconstruct" and I am maybe mislead by the fact that "viewcreatebyexpression" just takes much longer to run and therefore these impacts are a bit more in my face ?

I struggle to find documentation for that function "ViewCreatebyExpression".
Could anybody point me to a source of information regarding this particular function ?

I can not find it anywhere in Tm1 documentation and a google search returned 4 results including our favorite Olap Forum http://www.tm1forum.com/viewtopic.php?p=37319

How to make the set up optimum interface between BI 8.4 and Tm1 9.4 ?

- Schedule the BI reports to be refreshed outside of Tm1 business hours ?

- Customising the MDX query issued by Report Studio to optimise the query and reduce impacts.
Did Anybody successfully did that and would like to share their challenges/experiences ?

- Would an Upgrade of Cognos BI and Tm1 to 10.1 significantly improve this interface ?

Are there other options out there that one use to reduce the impact of report update triggered by Cognos BI on the Tm1 server ?


Thanks in advance for your inputs,

Kind Regards,

[rmackenzie]

What I am trying to understand is why the execution of this function "ViewCreatebyExpression" impact so heavily the Tm1 Server ?

ViewCreateByExpression is an internal function within TM1 that takes an MDX query as an input and returns a set of data as an output. These MDX queries are different from dynamic subset type MDX as they are querying the cube to get data, not just the dimension to get elements. E.g.

Code: Select all

SELECT [Account].[Interesting Account Subset] ON ROWS, {TM1SUBSETALL([Month])} ON COLUMNS
FROM [Reporting Cube] WHERE ([Year].[FY2012])

The integration layer between Cognos BI and TM1 uses this ViewCreateByExpression function to get data out of TM1 cubes and into a structure that Cognos can use. Also, people used to leverage this functionality when they built VBA MDX reports in Excel using the older ADOMD libraries.

it would put a login attempt from a user in "Wait" mode while it is executing, As these "viewcreatebyexpression" are usually lengthy, it give the user the impression that the Tm1 system is not accessible...

Another impact I have noticed is that it put any TI triggered after "ViewCreatebyExpression" started in "Wait" mode until it is completed...regardless of what the TI is doing or where it is writing to...

I might assume wrongly that the behaviour is different from what happen with "viewarrayconstruct" and I am maybe mislead by the fact that "viewcreatebyexpression" just takes much longer to run and therefore these impacts are a bit more in my face ?

My suspicion - based on experience - is that ViewCreateByExpression creates a server lock even when using Parallel Interaction (not available on your TM1 version anyhow). Also, as I mentioned in the other post, querying TM1 cubes with MDX does not leverage TM1's normal caching system and you will sometimes see quite simple 'views' take a long time to compute, especially where there are large and sparse dimensions in the cube.

I struggle to find documentation for that function "ViewCreatebyExpression".

Could anybody point me to a source of information regarding this particular function ?

Although IIRC the .NET and Java APIs do document their functions to run MDX queries over TM1 cubes, the underlying function is undocumented in the underlying API. You'd have to get more information directly from IBM.

How to make the set up optimum interface between BI 8.4 and Tm1 9.4 ?

The general suggestion for optimisation is to have the FM package target a summary cube based on the cube that you are having problems with. For this summary cube you may want to look at getting rid of rules, feeders, big dimensions, alternate hierarchies, etc.

[Olivier]

Thanks Robin for the prompt response.

The summary cube is an option we have suggesting for a while...for a range of other purposes
I will add this point to my business case for a summary cube

My suspicion - based on experience - is that ViewCreateByExpression creates a server lock even when using Parallel Interaction (not available on your TM1 version anyhow). Also, as I mentioned in the other post, querying TM1 cubes with MDX does not leverage TM1's normal caching system and you will sometimes see quite simple 'views' take a long time to compute, especially where there are large and sparse dimensions in the cube.

Is this still the case in 10.1 or could you feel any improvement on that front ?

[qml]

A couple of quick points on the subject.

- I didn't think there was any cube locking as such during ViewCreateByExpression, I could be wrong though.

- TM1 does not leverage any caching, stargate views etc when running that function, making it perform worse than any "native" querying for data.

- MDX queries passed from Cognos BI to TM1 are often really sub-optimal and that adds to the poor performance.

- Cognos BI 10 has a new feature called Dynamic Query Mode that you configure on the level of packages. This addresses a lot of the MDX shortcomings and in many cases results in a better performance. DQM is incompatible with some of the features available in the older query mode (CQM), like e.g. putting different levels of the same dimension hierarchy on both rows and columns of a report, so you might need to review/redo your reports. This is basically a somewhat more native (to TM1) way of Cognos BI talking to TM1, hence this behaviour.

[whitej_d]

I tested this in Cognos Express 10.1, and on a reasonably heavy calculated view, the performance between ViewCreateByExpression in the BI report was comparable with the ViewArrayConstruct in the cube view (about 70 seconds in this case).

Views calculated by the BI report were cached and stored (as verified by performance monitor) and were accessible via cube viewer. The same was true vice-versa ie. cache populated from accessing the view in cube viewer were used by the BI report. I did not have Dynamic Query enabled.

ViewCreateByExpression did not cause any lock contentions with read only operations, but did with write operations. However, I don't have PI enables due to a different issue IBM are looking at so this would be expected.

I would assert that the performance of BI in the latest versions is much improved in this department.

[Olivier]

Thanks for this update whitej_d, it sounds promising.

Looking forward to an upgrade of our environment...