locked
To persist the SSAS Cube RRS feed

  • Question

  • Hello All,

    I am using SSAS 2012 tabular to develop my cube now.

    Here is the requirement we have. Once the cube database(let's say ABC) is requested to finalize in the month of Jan 2013, we hope to keep this cube persisted as "ABC_Jan_2013"(no more change accetted afterwards even if we found some numbers are wrong in Dec 2012) and the original one(ABC) can keep the same name and is allowed to feed new data for revision. Each month, it is going be persisted to a new cube, whose naming pattern follows "ABC_MonthName_Year".

    In order to make this process automatical, SSIS is used. Right now, I am using XMLA scripts to backup and restore the cube but the tough point is I stored this backup/restore XMLA scripts in the table and each month, I have to impose the new cube name and change the XML scripts by some SQL updates to change the value of the element "databaseID".

    I am looking for a native/better way to make this process happen. Like, I hope to see some native tasks in SSIS to copy cube database(or maybe SYnc or restore/backup), by that design, I can expose some dynamic  elements into configuration table and every month, it gonna be easier for me to update the configuration table.

    Or maybe there are some good ideas to make this process happen easier? Any better idea?

    Thanks for all suggestions.


    Derek

    Monday, January 6, 2014 8:58 PM

Answers

  • When use the DatabaseName attribute on the XMLA Restore command, the restored database gets a new databaseID that is the same as the name.

    For example, the following command will work (I tried it on a tabular instance) and the new database will have a name and ID of "New Name And Id" (even if another database already exists with the old ID).

    <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <File>D:\test.abf</File>
      <DatabaseName>New Name And Id</DatabaseName>
    </Restore>

    An easy way to do this is to use an "Analysis Services Execute DDL Task" in SSIS.  Set the SourceType to Variable, and set the value above in the variable. You can populate the value from a config table if you wish.


    Christian Wade
    http://christianwade.wordpress.com/
    Please mark correct responses as answers!

    Monday, January 6, 2014 10:07 PM

All replies

  • Hello All,

    I am using SSAS 2012 tabular to develop my cube now.

    Here is the requirement we have. Once the cube database(let's say ABC) is requested to finalize in the month of Jan 2013, we hope to keep this cube persisted as "ABC_Jan_2013"(no more change accepted afterwards even if we found some numbers are wrong in Dec 2012) and the original one(ABC) still keeps the same name and is allowed to fetch/process new data for revision. Each month, it is going be persisted to a new cube, whose naming pattern follows "ABC_MonthName_Year".

    In order to make this process automatical, SSIS is used. Right now, I am using XMLA scripts to backup and restore the cube but the tough point is I stored this backup/restore XMLA scripts in the table and each month, I have to impose the new cube name and change the XML scripts by some SQL updates to change the value of the element "databaseID".

    I am looking for a native/better way to make this process happen. Like, I hope to see some native tasks in SSIS to copy cube database(or maybe SYnc or restore/backup), by that design, I can expose some dynamic elements into configuration table and every month, it gonna be easier for me to update the configuration table.

    Or maybe there are some good ideas to make this process happen easier? Any better idea?

    Thanks for all suggestions.


    Derek

    • Merged by Charlie Liao Tuesday, January 7, 2014 1:18 AM duplicate thread
    Monday, January 6, 2014 9:20 PM
  • When use the DatabaseName attribute on the XMLA Restore command, the restored database gets a new databaseID that is the same as the name.

    For example, the following command will work (I tried it on a tabular instance) and the new database will have a name and ID of "New Name And Id" (even if another database already exists with the old ID).

    <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <File>D:\test.abf</File>
      <DatabaseName>New Name And Id</DatabaseName>
    </Restore>

    An easy way to do this is to use an "Analysis Services Execute DDL Task" in SSIS.  Set the SourceType to Variable, and set the value above in the variable. You can populate the value from a config table if you wish.


    Christian Wade
    http://christianwade.wordpress.com/
    Please mark correct responses as answers!

    Monday, January 6, 2014 10:07 PM
  • You are right, Thanks for your suggestion. This appraoch is kind of being similar with my one. I expected to see something natively supported SSAS tasks in SSIS instead of to see any "RAW" scripts.

    Derek

    Tuesday, January 7, 2014 12:13 AM