Cube switching

Question

Cube switching

richardmgreen1

SSChampion

Points: 10356
More actions
December 3, 2018 at 8:40 am

#376578

Hi all
We've been looking at schema switching to minimise downtime for users (and it's going reasonably well in testing).
We have a 3-stage load process for the tables and cubes, as follows:-
1) Load the staging tables
2) Load the Dim/Fact tables
3) Update cubes
We've got parts 1 and 2 working reasonably well (downtime for anyone accessing the tables is down to about a minute) but the sticking point is the cubes.
What we want to do is update a set of cubes in the background and then switch them with the visible cubes so the downtime for those comes down as well.
We've been following this guide to update tables in the background and then make them visible to the users.
We want to do something similar with the cubes so we can bring them up to date in the background and then swap them with the cubes from, for example, yesterday.
Is what we want to do possible?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

richardmgreen1 SSChampion Points: 10356 More actions · Answer 1

Does anyone know if this is possible?

I've found I can rename tabular cubes (go into Properties and then rename it).
If I generate a script from that, it gives me a load of XMLA that looks like it's going to completely rebuild the database from the ground up and (I assume) will remove all the data from it.

Does anyone know if this is true?

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 2

I think what you're need to do is update the database names after processing. https://docs.microsoft.com/en-us/bi-reference/tmsl/alter-command-tmsl

richardmgreen1 SSChampion Points: 10356 More actions · Answer 3

Thanks for that.

Do you know if it will work on both multidimensional and tabular cubes (we have both)?
Also, will the rename empty the cube? It doesn't look like it will but I'd like to make sure.

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 4

The rename will not empty the cube. I've done this with both tabular and MD, but the syntax is probably different for MD. It's been too long now so I don't remember for MD.

richardmgreen1 SSChampion Points: 10356 More actions · Answer 5

Brilliant, thanks Brian.

That's saved a whole host of headaches.