Monday 4 November 2013

How To: Change the Connection String for a Single Table in Tabular SSAS

 

Imagine you're building a small cube using the new tabular model in SQL Server 2012 Analysis Services based on adventure works. You want to add the factInternetSales, dimProduct and dimDate tables. You create a single sql server connection to myserver.AdventureWorksDW2012 and select those three tables.

You build up your model; add relationships, hierarchies, custom DAX and so on but then for some reason you need to change the source for just the DimProduct table to be from a different server and/or database. FactInternetSales and dimDate must remain linked to the existing "Original" data source. This is not possible through the GUI in SSDT at the moment. Changing the existing connection string by clicking edit will modify the data source for all 3 tables. The GUI option is to add the new table by creating a new connection, then deleting the existing dimProduct table (and all related objects such as relationships/measures/dax etc), then adding the new table and rebuilding all the deleted content. Not very modular or developer friendly! Imagine doing this a few years down the line on some existing 'legacy' tabular project without breaking existing functionality. And the table properties screen offers no option to change the connection used to source the data.

The connection Name is greyed out. Changing it unfortunately involves right clicking on model.bim and selecting 'view code' :(

Step 0:
Backup your .bim file!

Step 1:
Add a new data source connection that points to the new database, unfortunately you need to add at least one table to actually add the data source so do something simple like "select top 0 1 as col"

image

Step 2:
Right click on model.bim (or whatever you've called your cube) and choose View Code. Find the section for the dummy 'Query' table (it should be right at the end of the XML

<xs:element name="Query_896ee325-f4a2-406d-9ad4-c102f04775c6" msdata:Locale="" msprop:DataSourceID="6effa2f9-0319-4b8a-a25e-ffa4d8e64a59" msprop:IsLogical="True" msprop:FriendlyName="Query" msprop:DbTableName="Query" msprop:TableType="View" msprop:Description="Query" msprop:QueryDefinition="select top 0 1 col">

find and take note of the section in red. This is what links the table in the GUI to the "New" data source.

Step 3:
Find the equivalent section of XML for the table you want to redirect to the new connection.

<xs:element name="DimProduct_885addae-df05-4c3d-97ca-c234ca5553bc" msdata:Locale="" msprop:IsLogical="True" msprop:FriendlyName="DimProduct" msprop:DbSchemaName="dbo" msprop:DbTableName="DimProduct" msprop:TableType="View" msprop:Description="DimProduct" msprop:QueryDefinition="      SELECT [dbo].[DimProduct].*   FROM [dbo].[DimProduct] ">

You'll notice that this part doesn't have an msprop:DataSourceID section, this is because (I think) the first connection added to the model is the 'default' connection and the DataSourceID is inherited from a parent XML tag :

<DataSourceViews>
  <DataSourceView>
    <ID>Sandbox</ID>
    <Name>Sandbox</Name>
    <DataSourceID>4fa7958c-7b29-404e-a89c-bdcb75dbedd9</DataSourceID>
    <Schema>
      <xs:schema id="NewDataSet" xmlns="" xmlns:xs="
http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="en-AU">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">

But that doesn't matter! If you modify

<xs:element name="DimProduct_885addae-df05-4c3d-97ca-c234ca5553bc" msdata:Locale="" msprop:IsLogical="True" msprop:FriendlyName="DimProduct" msprop:DbSchemaName="dbo" msprop:DbTableName="DimProduct" msprop:TableType="View" msprop:Description="DimProduct" msprop:QueryDefinition="      SELECT [dbo].[DimProduct].*   FROM [dbo].[DimProduct] ">

by adding the new DataSourceID

<xs:element name="DimProduct_885addae-df05-4c3d-97ca-c234ca5553bc" msdata:Locale="" msprop:DataSourceID="6effa2f9-0319-4b8a-a25e-ffa4d8e64a59" msprop:IsLogical="True" msprop:FriendlyName="DimProduct" msprop:DbSchemaName="dbo" msprop:DbTableName="DimProduct" msprop:TableType="View" msprop:Description="DimProduct" msprop:QueryDefinition="      SELECT [dbo].[DimProduct].*   FROM [dbo].[DimProduct] ">

then the table will use the new connection. Save the XML, open the model and open up the table properties

And there it is. The DimProduct table is now coming from the new connection.

6 comments:

  1. Thank you for this wonderful article. Its a life saver!

    ReplyDelete
  2. Hi yes Connection Name of table change but Connection Name of partition dont change.
    The solution is after doing step above,delete and recreate partition otherwise the process will fail

    ReplyDelete
  3. Dou's comment is key here... Note you can't delete the partition if there is only 1.. so create a new one(blank) which will generate with correct connection, then delete the original

    ReplyDelete
  4. This is great. You saved the day!

    ReplyDelete
  5. This is great. You saved the day!

    ReplyDelete
  6. It's incredible that the product doesn't support this.

    ReplyDelete