Joseph Michael Pesch
VP Programming

Deploying SSAS Cube from Development to Test/Production

by 23. September 2011 08:47

The easiest way to deploy the cube is to generate a create database script from within SQL Management Studio.  When generating a database script of an Analysis Server database the resulting script file will be XMLA rather than SQL script (as when generating a normal SQL database script).  The resulting XMLA file will contain a DataSources section that contains connection information about the relational database (e.g. datawarehouse database) that the SSAS cube will be reading it's data from.  Below is an example of that section where I have put in variable placeholders to replace the original values of the following items:

  • $(DWServer) - Target database server where the relational data exists.
  • $(DWDatabase) - Target database where the relational data exists.
  • $(DwReaderAccount) - User account name that has read access to the relation database.
  • $(DwReaderPassword) - Password of the reader account.

Here is a sample section of the XMLA file with the above indicated replacements:

<DataSources>
  <DataSource xsi:type="RelationalDataSource">
    <ID>IMPACDW</ID>
    <Name>IMPACDW</Name>
    <ConnectionString>Data Source=$(ImpacDWServer);Initial Catalog=$(ImpacDWDatabase);Integrated Security=True</ConnectionString>
    <ImpersonationInfo>
      <ImpersonationMode>ImpersonateAccount</ImpersonationMode>
      <Account>$(ImpacDWReaderAccount)</Account>
      <Password>$(ImpacDWReaderPassword)</Password>
    </ImpersonationInfo>
    <ManagedProvider>System.Data.SqlClient</ManagedProvider>
    <Timeout>PT0S</Timeout>
    <DataSourcePermissions>
      <DataSourcePermission>
        <ID>DataSourcePermission 1</ID>
        <Name>DataSourcePermission 1</Name>
        <RoleID>Role 1</RoleID>
        <Read>Allowed</Read>
      </DataSourcePermission>
    </DataSourcePermissions>
  </DataSource>
</DataSources>

In order to run the XMLA file through command line (rather than directly in SQL Management Studio) you will need the ASCMD.EXE utility from Microsoft.  You can download it from the SQL Server Analysis Samples page on CodePlex.com (below are links to the SQL 2005 and 2008 pages).

SQL 2005

SQL 2008

Tags:

SSAS

Comments are closed