SSAS for the DBA - replication questions

  • Hey all,

    Sadly, I know close to nothing about SSAS. I recently joined a new company and we're working on some environment architecture. They're currently targeting the utilization of a "stage" environment to perform all of our SSIS/SSAS work on to stage the data appropriately to be QA'd by users and keep the load off the production environment. From there, they want to be able to migrate the data (easy on the database end) as well as the appropriate SSAS pieces.

    Now, I see within SSAS you have the ability to perform a sync. With that though, a few questions:

    - Does this bring both the database and the cube(s)?

    -- If it doesn't bring the cube(s), how do you handle that?

    - The XML it can generate, how would you run that outside of SSMS (to say have it launched from within an app)

    Again, I'm a complete SSAS noob sitting here reading/playing trying to get up to speed very fast. Any and all ideas/thoughts/recommendations around this matter would be very much so appreciated.

    Thanks!

  • Adam Bean (8/10/2012)


    Hey all,

    Sadly, I know close to nothing about SSAS. I recently joined a new company and we're working on some environment architecture. They're currently targeting the utilization of a "stage" environment to perform all of our SSIS/SSAS work on to stage the data appropriately to be QA'd by users and keep the load off the production environment. From there, they want to be able to migrate the data (easy on the database end) as well as the appropriate SSAS pieces.

    Now, I see within SSAS you have the ability to perform a sync. With that though, a few questions:

    - Does this bring both the database and the cube(s)?

    -- If it doesn't bring the cube(s), how do you handle that?

    - The XML it can generate, how would you run that outside of SSMS (to say have it launched from within an app)

    Again, I'm a complete SSAS noob sitting here reading/playing trying to get up to speed very fast. Any and all ideas/thoughts/recommendations around this matter would be very much so appreciated.

    Thanks!

    I'll answer what I can:

    synching - we use this as it sounds you intend to - process in one place, synch to production. This brings the cube only. once the cube is processed, you can consider the data to already be "in it".

    I only use the XMLA scripts if I have made a cube structure change in development to alter the cube in staging. not clear on what you are attempting to use XML to launch???

  • Thanks,

    So the question in regards to creating the scripts. Via SSMS, it allows you to create a script in XML for the sync process. What I'm trying to determine is how is that run without SSMS? I ask because instead of having a dev/dba manually sync post processing, I'd like for that to be an option within the application to simply sync SSAS between environments.

    Thanks

  • You can use SSIS to run the XMLA script through the Analysis Services Execute DDL Task component.

  • XMLA code against SSAS can also be executed within a SQL Server Agent job. I use this approach for backing up SSAS cube databases to .abf backup files. For synchronization, I use a SQL Agent job to execute an SSIS package, though, in order to have a nicer control flow between the first step, processing, and the second, synchronization. It could be a risk to offer synchronization via your external app if it is not aware of the processing state of the cube database. In other words, you will need to some how avoid the scenario of trying to synchronize a cube database that is not yet fully processed. Otherwise, you will have a corrupt cube database at the destination.

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

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