Data Source for Process Cube

Is the source for the data used to process the cube actually embedded in the cube itself? I.e. I have source data which could move around. How can I programatically modify the source connection string before processing the cube using SSIS?

Why do you want to change the datasource?
You can have several partitions in a cube and each partition with different datasource.
If you want to update data in a datasource you have to proccess only this partition.
That's all|||Because I need to create approximately 2300 unique cubes for different customers that are all sourced off 2300 different SQL databases residing on different servers throughout the data center.

All the source databases use the same schema. I am using the same ETL process to create 2300 warehouses. Now I need to create the unique cubes off those warehouses. Seperate "database"/cubes are required for compliance reasons.

Thanks,

Chris|||Then what you need is a different cube for each warehouse. You can have 1 cube with 1 partition. Because when you proccess full the partition you overwrite data.
If I where you I try to make a client dimension or something like this and then filter data with dimension.
All the data in a unique database/table, and the cube reads this table.
Are you thinking to give access with Excel? If you are using Excel use instead multiple partitions.|||You still don't get it. I need to figure out how to change the Data Source information that is embedded in the cube at the time I do the processing. i.e. I need to figure out how to run an XMLA query against the Analysis Services database that will allow me to change the connection string for the data source so that when I subsequently process the cube I am guaranteed it will find the source database.

The source database could move in the data center for load balancing reasons, etc.

Thanks,

Chris|||Hi Chris,

You can do this with an XMLA query (as you suggested) that looks like the one I posted below. This runs an alter command on the data source for the "Adventure Works DW" Analysis Services database. So, by changing the "Data Source" and "Initial Catalog" settings in the connection string, you update the data source in the deployed database.

Hope that is what you are looking for. BTW, I created this XMLA script by going into SQL Server Management Studio, connecting to Analysis Services, then drilling down within a deployed version of the "Adventure Works DW" database, selecting the data source, then right-clicking on it and selecting "Script Object As", then "ALTER to", then "New Query Editor Window". Great way to capture various XMLA scripts for automating changes to a deployed AS..

Dave Fackler

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

<DataSourceID>Adventure Works DW</DataSourceID>

</Object>

<ObjectDefinition>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">

<ID>Adventure Works DW</ID>

<Name>Adventure Works DW</Name>

<ConnectionString>Provider=SQLNCLI.1;Data Source=newserver;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=newdatabase</ConnectionString>

<ImpersonationInfo>

<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

</ObjectDefinition>

</Alter>

|||You are brilliant. I knew about the scripting feature however I wasn't thinking about the data source being surfaced in the Management Console for analysis services. Thanks!