Resolving SQL data mart issues after migration

Scenario

Alan is an administrator in an IT organization. He manages and monitors the IT infrastructure capacity by using TrueSight Capacity Optimization. His organization decides to move to BMC Helix Continuous Optimization for managing its IT infrastructure capacity. Alan now wants to migrate all data from TrueSight Capacity Optimization to BMC Helix Continuous Optimization. He performs data migration by using the out-of-the-box migration tool provided by BMC. He observes that some of the migrated custom views are not reporting any data in BMC Helix Continuous Optimization. He suspects that the underlying SQL data marts that are used by the views have issues.

Alan needs an effective solution to identify and rectify issues in the SQL data marts that are used by these custom views.

Implementation workflow

Alan migrates data by using the migration workflow as described in Migrating-to-BMC-Helix-Continuous-Optimization.

He then checks the status of custom views:

1. Log in to the BMC Helix Continuous Optimization.
2. Navigate to Views > Custom Views > <custom_view_name>.

Alan opens one of the custom views and observes that it is not reporting any data.

From the error message, Alan realizes that the Company_site data mart, which is used by the custom view, is not able to retrieve data. Alan performs the following tasks to troubleshoot this issue.

Task 1: Identify the reasons for the data mart failure

In the error message, he clicks View the data mart details.

Alan reviews the data mart details that includes the reasons for the data mart failure and possible solutions. 

In the "Why is the data mart not working" section, he clicks the Show more link to analyze the failure reasons. He notices that the Company_site data mart is dependent on the other data marts that are not working. He clicks these data mart links to analyze their details and notices that the Business_DM_001 data mart does not extract any metrics. It is rather used for filtering the entities, which can be achieved by using an entity filter. The Business_DM_012 and Business_DM_210 data marts use the tables and views that are not supported. This data can be extracted by creating a summary data mart.

Task 2: Rectify the data mart issues

Alan realizes that the following steps will resolve the issue:

1. Create an entity filter to be used as a replacement for the Business_DM_001 data mart.
2. Create a summary data mart by using the newly created entity filter in step 1 as a replacement for the Business_DM_012 and Business_DM_210 data marts.
3. Update the SQL code of the Company_site data mart to use the newly created summary data mart.

Alan performs these steps by using the guided assistance provided on the data mart details page:

1. Click the Business_DM_001 data mart.
   
   
   
   The failure reasons indicate that the data mart is using the public views that are not supported, and the suggested solution indicates that it can be replaced by an entity filter.
2. Perform these steps to create an entity filter:
   1. Click Create Entity filter.
      
      
      
      
   2. Provide a name and select the required entities. For more information, see Managing-entity-filters.
   3. Save the changes.
3. Click the Company_site data mart to view its details.
   
   
   
   
4. Perform these steps to create a summary data mart:
   
   1. Click Create Summary data mart.
      
      
      
      
   2. Click Missing Information corresponding to Entity Filter.
   3. Select the entity filter that you created in step 2, and click Next.
      
      
      
      
   4. Select the required time filter, and click Next.
      
      
      
      
   5. Review the data mart summary, and click Finish.
      
      
      
      The Company_site (Summary) data mart is created.
      
      
5. Change the SQL code of the Company_site data mart to use the newly created Company_site (Summary) data mart.
   1. Copy the ID of newly created summary data mart.
   2. On the Company_site page, click Edit SQL code.
      
      
      
      
   3. Update the SQL query used by the data mart.
      
      

1. 1. Click Finish.
2. Open the Data marts page and verify that the status of Company_site data mart is changed to OK.
3. Open the custom view and verify that the data is now visible in the view.
4. (optional) Delete the following data marts as they are no longer required: Business_DM_001, Business_DM_012, and Business_DM_210

Alan performs similar steps to resolve issues in the other custom views.

Results

Alan is now able to see data in the custom views. He can now use these custom views to investigate and troubleshoot the capacity-related issues in the IT infrastructure.

Benefits

The guided assistance to identify and fix SQL data mart issues after migration saves Alan's time and efforts to restore the custom views in BMC Helix Continuous Optimization.