nhavis's TM1 Questionnaire #1

[nhavis]

1. Can anyone think of an algorithm to search an entire cube for a specific value? Keep in mind that the possible number of cells usually far exceeds billions.


2. My VBA Macro that makes use of the TM1 API works correctly when 'Perspectives for MS Excel' has been loaded however does not work correctly when 'Perspectives for MS Excel' isn't loaded. Why?


3. My VBA Macro goes through every single cell and attempts to find a value in each of those cells using the

TM1 API. Upon first running the macro, the results are quite slow... however if and _only_ if I break (using control+break) code execution, end it, then re-run the macro, results are much much faster.

I initially had ideas that my calculation must have in some way been stored in RAM - however if I break execution immediatly (ie the macro runs for only 2 second) - when i re-run the macro I would expect it to run fast for that 2 seconds of data - however it runs fast for the whole process (maybe 20 seconds).

I then thought since I broke execution early, maybe I hadn't closed connections properly after I'd used them - _somehow_ improving performance. However I commented out some of the closing code, expecting performance gains on the 2nd run, though got nothing.

I really don't know, does anyone have an idea?



4. Is it possible at all for a rule file to enter an infinite loop when calculating on save (somehow avoiding a stack overflow)?

[Martin Ryan]

1. Just a TI process that runs through the entire cube.

2. Because without Perspectives or at least Client, Excel can't be connected to TM1, so running the TM1 API will be pointless.

3. No idea, though I've often found a similar frustration where stepping through something works, but just running it doesn't.

4. Anything's possible, but this has not happened in my experience. I've written a few circular rules in my time, and the error only appears when you look at the data, not when saving the rule. Likewise with stack overflows.

Cheers,
Martin

[Alan Kirk]

2. Because without Perspectives or at least Client, Excel can't be connected to TM1, so running the TM1 API will be pointless.

It's interesting that we were speaking about ambiguous questions not so very long ago... perhaps this one should be on the Iboglix test, though it doesn't lend itself to multiple choice.

2. My VBA Macro that makes use of the TM1 API works correctly when 'Perspectives for MS Excel' has been loaded however does not work correctly when 'Perspectives for MS Excel' isn't loaded. Why?

I don't know whether that was supposed to mean "loaded on the machine", or "loaded into that Excel session". If it's the former, then I concur with you; no library files means no API calls.

However if it was supposed to mean "Perspectives (or Client) is not loaded into that Excel session", it would still be possible to run TM1 API code. (After all, I can do it in Access, which obviously has no Perspectives or Client .xlas). The only thing that WON'T work is if the code accesses the TM1_API2HAN function in the TM1.xll library. That's the one that piggybacks access from the user's Client session so clearly no client = no session to piggyback on. It would be necessary to use the TM1SystemOpen and related functions to create a connection, passing a login and password to the server as required.

[nhavis]

Martin: a search through every cell wouldn't be possible as there are too many cells to check - I was thinking something about checking consolidated cells and only searching the children when there was nothing in the consolidation - which may or may not be effective...

Perspectives is not loaded in to Excel - I can still make API function calls however my program does not function correctly (I might try and isolate the problem later on).

[Gregor Koch]

Hi,

I guess the intention is to use a TI which has a view as source and a parameter which is the value to be searched.
By default a view suppresses zeroes and therefore will only check cells with values.

Cheers

[Steve Rowe]

Also you need to take care when checking against a consolidation for zero values since this doesn't imply that all the values under it are zero since you could have +10+(10)=0. If you hit that then you can delete elements that have value. The only safe way is to check the system at L0. Possible approach.

1. Create an attribute against every dimension in the cube called populateD.
2. Process the whole cube in TI with a Level 0, zero suppressed view as a data source, if your cube is very very big then you may need to break teh view up.
3. In the data tab write a 1 to the attribute in the dimension.

This should give you 1 against all populated elements and a 0 against un populated elements.

If your cube is way too big to process in 1 chunk then another approach could be.
1. Create an attribute against every dimension in the cube called populate.
2. Create a view of L0 MDX subsets which are only populated with elements where populated=0.
3. Set you view to process cube 1 month at a time, as we are using dynamic MDX subsets each call to the view should result in a reduced query set. This should be much more better, IF the MDX udates between each call.
.... actually this is wrong...won't work, you'd could only have 1 MDX subset in the view, not all of them, this might give you the idea of how to move this forward though.

Cheers,

[Jeroen Eynikel]

1. Can anyone think of an algorithm to search an entire cube for a specific value? Keep in mind that the possible number of cells usually far exceeds billions.

Export the cube to a flat file and run a search on the flat file. My guess is that this is going to be much faster since you can eliminate all the zero cells from the export.

Edit: alternatively: use a zero-suppressed view as source
- avoid having any code in the metadata tab
- put something in the datatab like
if (value = pValue) (the value you are looking for);
asciiexport(blabla)
endif;
itemskip;

I have experimented with algoriths working on consolidations and then going to lower levels in case the consolidated value fulfilled certain search criteria, but I was looking for things like value > 1000. Being certain that all lower level elements were positive I could make the checks on consolidated levels first. In your case however: if you are looking for a specific value that seems more problematical - still doable if you are certain all L0 values are positive but if not...

[Gregor Koch]

Hi Jeroen

I beg to differ.

Don't you think that 'eliminate all the zero cells from the export' and 'zero-surpressed' view will return the same number of records? By the way the former is sort of based on the latter anyways, but in the flat file case will have to write them to disk first rather than just 'checking' them in memory?

Cheers

[Jeroen Eynikel]

Hi Jeroen

I beg to differ.

Don't you think that 'eliminate all the zero cells from the export' and 'zero-surpressed' view will return the same number of records? By the way the former is sort of based on the latter anyways, but in the flat file case will have to write them to disk first rather than just 'checking' them in memory?

Cheers

Yeah you're right. I realized so myself when I edited my post and added the zero suppressed source view as an option.