Cube switching

  • richardmgreen1

    SSCrazy Eights

    Points: 9674

    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?

  • richardmgreen1

    SSCrazy Eights

    Points: 9674

    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: 4063

    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

    SSCrazy Eights

    Points: 9674

    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: 4063

    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

    SSCrazy Eights

    Points: 9674

    Brilliant, thanks Brian.

    That's saved a whole host of headaches.

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

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