TM1 Forum

I've been having a problem where a report that is based on a MDX report doesn't reflect the latest MDX subset. Mike Cowie's already diagonsed the problem by noting that MDX subsets that are based on attributes (as mine is) can't be sure to refresh automatically.

So my question is how do I manually force the refresh? My attributes are populated via a TI process, so I can write something in the epilog to fire the refresh, but I'm not sure what to write. Perhaps cycle through the subset using subsetgetsize etc? Or put a dummy element in the dimension and take it out again?

Version is 9.1.3

TIA,
Martin

Hello Martin

I'm not sure what would trigger the update, though a DimensionElementDelete statement cannot be part of the Epilog tab.

Wim

Hi Martin,
What about deleting and rebuilding the MDX sub from scratch at the end of (or in a 2nd TI), if it's used in a view you'll obviously need to put a dummy susbet in the view and then put the subset back.
Cheers

Wim Gielis wrote: I'm not sure what would trigger the update, though a DimensionElementDelete statement cannot be part of the Epilog tab.

True, but I can call another TI process from the Epilog which could do this.

Steve Rowe wrote: What about deleting and rebuilding the MDX sub from scratch at the end of (or in a 2nd TI), if it's used in a view you'll obviously need to put a dummy susbet in the view and then put the subset back.

It's not used in a view, just for an Excel report. Yes, I was thinking that it might be easiest to rebuild, but I wonder if the new subset would get triggered correctly or if it would have the same problem. If it's possible, I'd rather find a way to make the MDX fire. Or at least understand what makes MDX fire, so I can be sure the new subset would work ok.

Martin

Hi

You could try running the M_CLEAR Macro in Excel followed by a recalc. This tends to cure quite a lot of issues where Excels local caching gets out of step with the server.

From memory, it is just Application.RUN("M_CLEAR")

As far as I know, there isn't anything you can do in a TI Process

Regards


Paul Simon

How about this - it looks like its intended purpose is for something else, but it could have the 'side-effect' of doing what you want.

RefreshMdxHierarchy
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.

This function updates the MDX hierarchies in a TM1 server without requiring you to restart the server.

Use this function after configuring or editing the custom named hierarchy levels for a dimension in the }HierarchyProperties control cube.

For details on using named levels with dimensions, see the related section in the IBM Cognos TM1 Developers Guide.

Syntax
RefreshMdxHierarchy(dimensionName)

Thanks Loadza, will try that. I've also tried the other suggestions already and waiting on feedback from the user.

Cheers,
Martin

It would appear that function doesn't exist in 9.1.3, Loadza. The other things haven't worked either and I've no idea why. Surely destroying the subset and rebuilding would have to work. Anyway, I guess I'm going to have to kill the dynamic subset idea. I'll either make it a static subset, or create dummy dimensions. The stink thing about a static subset is I have to run through it before adding each new element to ensure it's not there twice, which I why I'm contemplating a dummy dimension. If only there was a subix function.

Martin

Martin Ryan wrote:It would appear that function doesn't exist in 9.1.3, Loadza.

It's not mentioned in any of the release notes (colour me surprised ), but doing a scan of my collection of manuals shows RefreshMdxHierarchy first (and only) showing up in the 9.5 Developer and Reference documents. It appears to have been brought in to support named hierarchies.

a scan of my collection of manuals shows RefreshMdxHierarchy first (and only) showing up in the 9.5 Developer and Reference document

Yes, I think it is new in 9.5... should have mentioned that...

Martin, have your tried reordering the dimension? That may kick off the mdx to re-evaluate itself too, although it'd need to be all on its own as dim changes won't take effect until the TI completes its epilog.

I've chucked this in the too hard basket and gone with a static subset. End up being marginally faster anyway, which surprised me. I thought having to cycle through the subset would have increased execution time quite a lot.

I wonder now if there might've been an element of "warmware" error where the user wasn't waiting for the report to update before deciding it was wrong, but as I've now implemented the static subset solution I'm not worried enough to go back and test that out.

Thanks for all your suggestions. Might try it all again when the client upgrades.

Martin