MDX subset refresh after attribute update

[Martin Ryan]

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

[Wim Gielis]

Hello Martin

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

Wim

[Steve Rowe]

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

[Martin Ryan]

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.

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

[paulsimon]

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

[LoadzaGrunt]

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)

[Martin Ryan]

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

Cheers,
Martin

[Martin Ryan]

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

[Alan Kirk]

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.

[LoadzaGrunt]

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...

[Steve Vincent]

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.

[Martin Ryan]

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