Migration of Data Mart (Pre Analysis Center 3.0)

  • 7016699
  • 21-Jul-2015
  • 03-May-2016

Environment

NetIQ Analysis Center 2.9 or before

Situation

You need to migrate a Data Mart from one SQL Server to another SQL Server

Resolution

1) Create a SQL backup of the Data Mart database that you want to move
    https://technet.microsoft.com/en-us/library/ms187510%28v=sql.110%29.aspx

2) Delete the Data source that you want to migrate
    1) Open Analysis Center Console
    2) Expand the "Analysis Center Home\Enterprise\Data Sources" in the Navigation pane
    3) Select the Data Source that you want to migrate
    4) Right-click and select "remove"

3) Run the OLAP processing job to clean out the old Data source
    1) Open SQL Management Studios
    2) Connect to the SQL Server that hosts the Analysis Center Data Warehouse
    3) Expand the "SQL Server Agent\Jobs" in the Object Explorer pane
    4) Right-click on the "Analysis Center (OLAP Processing)" SQL job and select "Start Job at Step..."
    5) Start the job at Step ID 1
   
4) Add a new Data Source for the same QDB, point the Data Mart SQL Server to the new SQL server
    1) Open Analysis Center Console
    2) Select "Add" under the Data Sources tab to launch the Add Data Source Wizard
    3) Follow the steps in the Add Data Sources Wizard

5) Allow the following SQL jobs to run/created
    (DataSource Name) Create Data Source Data Marts job
    (DataSource Name) ETL job

    NOTE: You should see both SQL jobs listed under "SQL Server Agent\Jobs" within SQL Management Studios on the new SQL server. Also you can view the history of each job in order to see if the job has completed successfully.

6) Disable the ETL job
    1) Open SQL Management Studios,
    2) Locate the "(DataSource Name) ETL" job under "SQL Server Agent\Jobs" within SQL Management Studios on the new SQL server
    3) Right-click on the ETL job and select "disable"

7) Close all open connects to the DM
    Close all Analysis Center consoles

8) Restore the backup Data Mart database over the newly created Data Mart
    https://msdn.microsoft.com/en-us/library/ms177429.aspx

9) Enable the ETL job and run the job
    1) Open SQL Management Studios
    2) Locate the "(DataSource Name) ETL" job under "SQL Server Agent\Jobs" within SQL Management Studios on the new SQL server
    3) Right-click on the ETL job and select "enable"
    4) Right-click on the ETL job and select "Start Job at Step..."
    5) Start the job at Step ID 1
   
10) Run the OLAP processing job
    1) Open SQL Management Studios
    2) Locate the "Analysis Center (OLAP Processing)" job under "SQL Server Agent\Jobs" within SQL Management Studios on the Data Warehouse SQL server
    3) Right-click on the OLAP SQL job and select "Start Job at Step..."
    4) Start the job at Step ID 1

Cause

The Data Migration Utility does not allow you to migrate just a Data Mart. It requires you to migrate the Data Warehouse and Data Mart.