Migrating Smart Reporting data

After you perform the data migration for BMC Helix ITSM, perform the Smart Reporting version 21.3 data migration.

Before you begin

• Make sure that you have downloaded the 21.05 migration packs for Smart Reporting.
  To download the migration packs, see Downloading-the-installation-files.
  For information about the available Smart Reporting migration packs, see Available-migration-packs.
• Make sure that you have migrated your BMC Helix ITSM data.

To migrate data

Perform the following steps to migrate Smart Reporting data:

1. In the target system, perform onboarding and content import.
    
2. If your source Smart Reporting database has an active Smart Reporting Repository Database as Datasource, perform the following steps:
   1. In the target system, log in to Smart Reporting.
   2. Click Administration > AdminConsole > Data Sources > Add.
   3. Select Database and enter the name as Smart Reporting Repository DB.

   4. Select Database Type as Yellowfin Repository Database.

      Important

      If you do not see Yellowfin Repository Database in the Database Type, run the following query to add Yellowfin Repository Database:

      update Configuration set ConfigData = 'BMCACTIONREQUEST,METADATADB'
      where IpOrg='1' and ConfigTypeCode = 'SYSTEM' and ConfigCode = 'AVAILABLESOURCES'
   5. Enter the Smart Reporting target system username and password.
      The following image shows an example connection:
      
   6. To test the connection, click Test Connection.
   7. Click SAVE AND CLOSE.
       

3. Stop the Smart Reporting instances on the target system to start the migration activities.

   Important

   If you encounter errors due to maximum data present in some columns in source tables, such as the following:

   Table: ReportFilter' Field: ValueDataone ; Table : 'reportfieldtemplate' Field: ValidationFunctionName;

   Perform the following steps:

   In the source system, find the maximum length of the field.
   Example query to find the field length:

   select max(len(ValueDataone)) from ReportFilter; 

   In the target system, update the field size.
   Example query to update the field size:

   ALTER TABLE reportfilter alter valuedataone type character varying (9856);

4. Register the source and target systems.

   System	Steps to register the system
   Smart Reporting source system	Register a source system. Important: For Microsoft SQL Server database, make sure that you specify the migration mode as JDBC while registering the source system. For Oracle database, the migration mode is set to JDBC by default. Create a BMC Helix Data Manager user. Test system connectivity and get database character set details. Discover the source system.
   Smart Reporting target system	Register a target system. Important: For Microsoft SQL Server database, make sure that you specify the migration mode as JDBC while registering the source system. For Oracle database, the migration mode is set to JDBC by default. Create a BMC Helix Data Manager user. Test system connectivity and get database character set details. Discover the target system.

5. Import the Template Smart Reporting Migration migration pack:
   1. Copy the downloaded Template Smart Reporting Migration migration pack and CMT files into the metadata directory of your BMC Helix Data Manager Engine installation.
      In a default Windows installation, this is C:\Program Files\BMC Software\Helix Data Manager\metadata.
   2. Select Configuration > Import HDM Meta Data from the navigation bar.
   3. To trigger the BMC Helix Data Manager Engine to read the files in the metadata directory, select Scan.
   4. For each migration pack you want to import, perform the following actions:
      1. Select the file from the list.
      2. Click the Import Meta Data button.
         The Import page displays the progress of the import.
          

6. Run Pre-Migration Script - Smart Reporting.cmt on the target system:

   Important

   Run the Pre-Migration Script - Smart Reporting only if you are migrating to Smart Reporting target version 21.3.

   1. In BMC Helix Data Manager, select Discovery & Analysis > System Analysis & Administration, and select the target system.
   2. Navigate to the Data Updates tab and click Available Data Updates.

   1. On the Available Data Updates tab, select the Pre-Migration Script - Smart Reporting script, and click Run Selected Script.
       
7. Copy the Template Smart Reporting Migration pack to the source and target data dictionaries:
   1. In BMC Helix Data Manager, select Data Migration > Migration Packs.
   2. Select the Template Smart Reporting Migration pack.
   3. Select Action > Copy.
   4. In the Source Data Dictionary field, select the data dictionary for the source system.
   5. In the Target Data Dictionary field, select the data dictionary for the target system.
   6. Click Copy Migration Pack.
       
8. Perform the following checks on the copied Migration Pack in the Smart Reporting source database:

   1. In Smart Reporting source database, run the following query:

      select sourceName, count(1) from reportviewsource where
      iporg in (select iporg from Organisation) and accesscode='UNSECURE' group by sourceName having count(1)>1;

      If the result set is greater than 1, find the active AR System data source ID and apply data filter by following step (c). The active AR System data source ID is the valid data source connection from the number of source connections created during AR System onboarding.

   2. Run the following query to check for deleted source and JDBC connection method:

      select * from reportviewsource where accesscode='DELETED' or connectionmethodcode='JDBC-LOAD';

      After running the query, if we get any records in the result, do not add the source IDs for these records in step (c).

   3. Apply the data filters for the records obtained in step (a) and do not add data filters for records obtained in step (b).
      Perform the following steps to add data filter:
      1. Log in the BMC Helix Data Manager.
      2. Select Data Migration > Migration Packs > Select right Migration Pack > Export table.

      3. Check for Reportviewsource and apply a data filter by using the following qualification:

         sourceid = <AR System data source> or sourceid = <Smart Reporting Repository database source>

         For example, sourceid = 60541 or sourceid = 2536767

         Important

         Do not add the source IDs for the records obtained in step 2.

         The following image shows an example:
         
          

   4. Check your organisation table for any duplicate organisations.
      If you have any duplicate organisations, perform the following steps:
      1. Identify the correct iporg value by comparing the iporg value in reportviewsource table.
         Reportviewsource table has iporg and sourceid columns.
      2. Identify the correct iporg value corresponding to the correct sourceid value in the table.
      3. Apply the following qualification in BMC Helix Data Manager tool for Organisation table:
         1. Log in to BMC Helix Data Manager.
         2. Navigate to Data Migration > Migration Packs > Select right Migration Pack > Export table > Check for Organisation > Apply Data filter.
         3. Enter the qualification.
            For example, iporg = 1 or iporg = 13001 (In this example, 1 is 'default' organisation and 13001 is right organisation).
             
9. Export the Smart Reporting data from the source system:
   1. In BMC Helix Data Manager, select Data Migration > Migrate Data.
   2. On the Migrate Data page, in the Migration Pack field, select the Template Smart Reporting Migration pack.
   3. Complete the fields and click Next.
   4. On the Migrate Data - Confirm page, in the Migration Job Name field, specify the migration job, and click Confirm Migration.
   5. On the Migration View Job page, click Start Export.
       
10. Import the Smart Reporting data to the target system:
    1. After the data export is complete, on the Migration View Job page, navigate to the Import tab.
    2. On the import tab, click Create import Job.
    3. Complete the fields and click Confirm Data Import.
    4. On the Migration View Job page, to start the import job, click Start.
        
11. Run the data update job:
    1. In BMC Helix Data Manager, select Discovery & Analysis > System Analysis & Administration, and select the target system.
    2. Navigate to the Data Updates tab.
    3. In the Available Data Updates tab, select the data update script and click Run Data Updates.
    4. In the Create Data Update Job dialog box, specify the job details and click Create New Data Update Job.
    5. In the data update job, select the data update script to be executed and click Run Selected Script.
        
12. In the target database, run queries to update the AR Server host and port:

    1. To update the AR Server host, run the following query:

       Update SmartReportingConfig set value='<Target AR Server Host>' where key_name = 'onboarding.arserver';

       where <Target AR Server Host> value is the name of the AR Server host. 

    2. To update the AR port, run the following query:

       Update SmartReportingConfig set value= '<Target AR Server Port>' where key_name = 'onboarding.arserverport';

       where <Target AR Server Port> value is the port number of the AR Server host. 
        

13. Start the Smart Reporting instances.
     

14. In the AR System Configuration Generic UI form, update the following parameter values:

    1. Open the AR System Administration Console, and click System > General > Centralized Configuration.
    2. In the AR System Configuration Generic UI form, from the Component Name list, select the Mid Tier component.
    3. Click Add.
    4. Enter the values for the following parameters:
       • arsystem.radminpass—Specify the user password.
       • arsystem.radminuser—Specify user as siadmin.
       • arsystem.reportingURL—Specify the URL to access Smart Reporting.
       • arsystem.rtenantid—Specify the customer name.
    5. Click Apply
    6. Click Close.

15. In Mid Tier Configuration Tool, in the Report settings, update the reporting customer name and siadmin password details.

    For more information see Configuring report setting for mid tier.

16. In the SmartReportingServer_Info form, update the reporting customer name and siadmin password details.
     

Related topic

Running-your-migration-project