I have a bunch of executive queries set up with ‘Delete Dimension Pair’ but they deleted the whole cube. Has anybody seen this before? They are running as part of a process set with 23 cubes, some delete all, some delete pair. I’m going to try and put the delete pair ones first and the delete all ones last. Other than that I’m not sure what to do. Any ideas why this would happen?
Do you have any that delete pair as expected? What do you want them to delete?
To be honest I’ve never used that option because I couldn’t understand what it would do and why the rest of the data wouldn’t need to be recalculated.
Yes when I ran them manually during testing they worked fine and many ran fine on Feb 1st. The Mar 1st one was a trainwreck.
A dimension pair is a snapshot in time. If you are running a metric for example trending your metric for Days of Inventory outstanding you’ll want to know your inventory value at the point in time the cube is run.
Ours is calculated like so: (Raw Material +WIP + FGI) / (Cogs last month / Work Days in the month)
You will want the inventory value and WIP value at that point in time. So while it would be possible to look at transaction histories and such for past values it’s much easier to just calculate the value right now and use the month ending date as your second dimension pair. If you run it again and don’t delete that pair it will double the values. I have two queries that also refer to the previous month. For example if you’re calculating net bookings you’ll want the backlog value at the beginning of the month and the end of the month. The net change should account for any bookings or de-bookings (we did not have booking tracking enabled when we went live but do now and plan to use that functionality someday) For the previous month backlog value you could just look at the previous dimension pair. I put the source numbers for the metrics into the cube for this purpose.
I’m switching to scripting DMT for my metrics into a UD table. I’ve horsed around with the executive queries long enough.
1 Like
Ohh, I think I see. Thanks for explaining on your own question! I’ve always wondered about the uses of that Dimension Pair delete option.
Hah, fair enough. We have a massive executive query for analyzing sales by every cross-view of dimensions that might possibly be useful, and I had to play Tetris with schedules so its long run-time wouldn’t interfere with backups.