locked
Dynamic data Source for SSAS cube from SSIS package RRS feed

  • Question

  • Hi,

    I am currently working on a SSAS project.

    I am processing the SSAS cube from a SSIS package.

    I have a SSAS package deployed in the Analysis service that has a data source pointing to a DW.

    But my data source for my cube can be any of 2 databases. I have to decide in SSIS package which database has to be used.

    I have a trigger file which has the details of which datasource the cube has to be processed.

    But I am not able to control the data source of the cube from the SSIS package.

    Is there a solution for this? Help will be appreciated.

    Thanks in advance.

     

    Wednesday, December 22, 2010 7:41 PM

Answers

  • You can't change the Datasource and Datasourceview in SSAS dynamicly.

    If only the Facttable should be changed, I would it like this:

    Create a view to fetch the data from the facttable. This view use as source for the measuergroup.
    Create a variable to hold, which DB should to be used.
    Before starting the ProcessCube Task alter the view to the needed Database.Facttable

    If all tables, fact and dimension should be changed, I would better create 2 SSAS-Databases and the User should access the Database, they need at the moment. 

    • Marked as answer by Tiffosi Tuesday, January 11, 2011 4:52 PM
    Thursday, January 6, 2011 9:12 AM

All replies

  • Just choose the connection manager you need using a script task.

    http://msdn.microsoft.com/en-us/library/ms136018.aspx

    Wednesday, December 22, 2010 8:22 PM
  • Thanks for the reply Perry!

    I am not able to get the connection string of the Analysis Service Connection manager using the above logic.

    Even if I get the it, I can edit only the connection string of the Cube to be processed.

    But, my requirement is to edit the data source of the Cube.

    I mean the OLTP data source of the cube from which the cube is getting data from.

    Let me know if you are still not clear.

    Thanks.

     

    Thursday, December 23, 2010 3:31 PM
  • Hi Tiffosi,

    Based on your description, you have a trigger file which has the details of which datasource the cube has to be processed. We can get the detailed information from the trigger file using Script task, and then use two Analysis Service Processing Tasks to process any of 2 databases in the cube.

    The general steps should be:

    1. Use a Script Task to get which database should be processed
    2. Add two Analysis Service Processing Tasks to the package. Each package processes each database
    3. USe the "Precedence Constraint" to determine which task to be processed

    For more information, please see:
    Script Task: http://msdn.microsoft.com/en-us/library/ms141752.aspx
    Precedence Constraint: http://msdn.microsoft.com/en-us/library/ms141261.aspx

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, December 27, 2010 7:27 AM
  • Thanks for the reply.

    But, I think you did not get my problem.

    I do not have 2 SSAS DB, only one. But 2 data sources from which I have to decide which fact table has to be loaded to the cube.

     

    Thanks

     


    Tiffosi!
    Thursday, January 6, 2011 1:14 AM
  • You can't change the Datasource and Datasourceview in SSAS dynamicly.

    If only the Facttable should be changed, I would it like this:

    Create a view to fetch the data from the facttable. This view use as source for the measuergroup.
    Create a variable to hold, which DB should to be used.
    Before starting the ProcessCube Task alter the view to the needed Database.Facttable

    If all tables, fact and dimension should be changed, I would better create 2 SSAS-Databases and the User should access the Database, they need at the moment. 

    • Marked as answer by Tiffosi Tuesday, January 11, 2011 4:52 PM
    Thursday, January 6, 2011 9:12 AM
  • Thanks Christa! I decided to use 2 SSAS DBs.
    Tiffosi!
    Tuesday, January 11, 2011 4:54 PM