MDX Subsets Kill View Performance?

[raeldor]

We have a cube that is fed from another cube for reporting. It's pulling just a single metric from another cube with no calculations other than that DB pull from the other cube. When I run a query pulling all vendors (one of our dimensions) it takes a long time (about 1 minute; I will assume a first retrieve will calculate the data, so this is ok). If I then change to a single vendor it retrieves quickly (couple of seconds; a lot less data), when I go back and select all vendors again it's quick to get the data (couple of seconds), as expected, as the data is cached. All good so far! However, if I use an MDX subset to pick the vendors based on a control cube with a search box it all seems to go out the window. Even though in the subset editor the subset gets the vendor list quickly, when the view is refreshed AFTER the control cube value is changed, we're back to about 1 minute again. The data is already cached, we know that because a simple retrieve without a control cube reference in the MDX returns in seconds. So why does an MDX subset seem to kill the performance? Interestingly, if I switch between a simple 'all leaves' pull, and back to the MDX based on the search, that completes quickly again (I assume because the control cube value hasn't changed).

It really doesn't seem like having subset MDX based on a control cube should have this negative a performance impact. If the control cube was referenced in the cube RULES, I could understand.

EDIT: YIKES. I removed references to the control cube in the MDX subsets and changing the control cube value and having auto-refresh on the other view STILL kills the performance. Now I'm really confused. If I refresh the report view manually it comes back immediately. If I change the control cube value and the report view refreshes automatically it takes 1 minute.

EDIT2: If I replace the reporting cube view with with the original source cube view (the one the reporting cube is fed from), the performance is fine. Even with MDX subsets. So this seems to be something to do with a synchronized refresh of a FED cube.

EDIT3: It looks like changing the control cube sets 'Memory Used for Calculations' to zero for my reporting cube. Even though the rules have NO references to the control cube.

EDIT3: This is unbelievable. Even after moving the report view to a separate tab so it doesn't refresh, changing a value in the control cube wipes out the stored calculated cells for the reporting cube. Even though the reporting cube has NO mention of the control cube and the control cube has NO feeders into the reporting cube. I don't even know what to think at this point.

EDIT4: I created a brand new cube in the same format with the same calcs and feeders. The calculated values do NOT get cleared when the control cube value changes. Somehow the original reporting cube (which DID reference the control cube in the RULES) still has some kind of reference to the control cube that TM1 has held onto despite the rules being changed to remove that reference. I even tried restarting the instance, but it still gets cleared. This is very concerning. Do we have to re-create cubes everytime we change the rules to make sure things work properly? :S

EDIT5: Lol, moving the new cube's view onto the same tab as the control cube view results in the pre-calculated values being wiped out again when the control cube values change. To add insult to injury, moving it back to it's own tab, modifying the control cube continues to wipe out the pre-calculated values on the new reporting cube. When I remove that view completely, it continues wiping out the pre-calculated values on the new reporting cube when I change control cube values. What kind of madness is this?

Thanks
Ray

[burnstripe]

Assuming you've not got a chore reloading data frequently, I know you've said it isn't but it really does sound like there are some rules that are based on the control cube, maybe not in the reporting cube, but further down the rule chain, such as the other cube the DB pulls from.

If you have access to the server, within the data directory I would do a search in the .rux files and do a search for any mention of the control cube name to see if it gives you any indication. Something like this using powershell would generate you an output

Cd 'C:\Location of Data Directory'
Select-string -path .\*.rux -pattern "ControlCube" | out-file "C:\test.txt"

It searches all the rule files (rux) for any string matching controlcube and outputs it to C:\test.txt
You could also change rux to pro to search on processes.

Note: If changes to rules aren't being retained after a server restart the blb file possibly hadn't been updated with the changes, perhaps because of a cue or maybe the link had been list or the blb was locked. If it's not playing ball, backup the rule file in a notepad somewhere and you can do the following. Delete the rule file, stop the service, take a copy of the data directory (for backup purposes), delete the file called, cube.blb, cube by the name of cube who's rules aren't saving. Start the service back up. Create a rule for the cube, adding back content from the rule backup taken earlier.

Hopefully the next time the server is restarted the changes will then stick.

This kind of issue can occur if the anti-virus is scanning the data folder whilst tm1 is running.

[gtonkin]

Are you deriving any string rules in your cube?

[Steve Rowe]

Are you 100% sure there are no sheets with a DBS or users inputting data into the reporting cube whilst testing this?

Don't forget an Attrs type reference will create a rule link, just in case your control cube is an attribute cube.

You can see the dependancies being built in the message log when the DB starts, so this might help you prove that TM1 thinks there is a link even if you don't think there is.

Sounds like you have a pretty good handle on what should be happening and assuming you are on a recent version this might be a case for IBM support.

You may/will need to build a cut down worked example so that you can give them a server to reproduce the issue. This may help you understand the problem too....

[raeldor]

Firstly, thanks for all the replies. I really appreciate you all reading through my rant.

Doing a string search on the control cube was a good call. It turns out that there were rules in a second control cube that referenced the first control cube, that in turn linked back to the original cube that does the feeding. Apparently this was enough to wipe out ALL the pre-calculated cells in the fed cube AND the source cube.