Load the prepared data

Updated: 2009-09-17

In this article:

  • Migrating prepared reference data

  • Migrating prepared fact data

This process is used to load the prepared data into the application on the destination server. Before beginning this process review the PerformancePoint Server data integration process topic.

Important

At the conclusion of each step in the following procedures, verify the BizSystem flags in the ID and label tables to ensure that the step was successful.

Migrating prepared reference data

Migrate the prepared reference data

  1. Restore the staging database on the destination server by using SQL Server Management Studio.

  2. Register the staging database by using Planning Administration Console if it is not already registered. If you need to register the staging database, it will be necessary to take the application Online. Once the staging database is registered, the application must be Locked prior to migrating data.

  3. Synchronize the staging database for each site by using Planning Business Modeler or Planning Command Utility. Do this for all dimensions and models.

    Syntax:

    ppscmd stagingdb /operation synchronizedata /server http:// <servername> :46787 /path <application_label> . <modelsite_label> /collection dimensions | models

    Examples:

    ppscmd stagingdb /operation synchronizedata /server https://localhost:46787 /path alpine_ski_house.ash_corporate /collection dimensions 
    ppscmd stagingdb /operation synchronizedata /server https://localhost:46787 /path alpine_ski_house.ash_corporate /collection models
    
  4. Populate the dimension ID tables by converting labels to IDs for all dimension by using the stored procedures available in the staging database

    Syntax:

    EXEC[dbo].[bsp_DI_ConvertLabelColumnToMemberIdForDimension]
    @DimensionName = <modelsite_Label>:<Dimension_Label>,
    @ModelSiteName = <Modelsite_label>,
    @OverwriteExistingData = N'T'
    

    Example:

    EXEC[dbo].[bsp_DI_ConvertLabelColumnToMemberIdForDimension]
    @DimensionName = N'Ash_Corporate:Account',
    @ModelSiteName = N'Ash_Corporate',
    @OverwriteExistingData = N'T'
    

    Important

    You must convert all labels and then load all dimensions for each of your sites in the correct order. Converting the labels or loading the dimensions in a different order can cause unexpected load failure. See the About loading dimensions topic for the correct dimension loading order.

    You can perform steps 4 and 5 together, that is, you can convert the labels and load the dimensions in pairs. Or you can perform these steps separately. Regardless, remember that you must follow the correct dimension loading order.

  5. Load all dimensions for each site by using Planning Business Modeler or Planning Command Utility. Repeat as required.

    Syntax:

    ppscmd stagingdb /operation loaddatafromstaging

    /server <planning server url> /path <application_label> . <modelsite_label> .dimension: <dimension_label>

    /currentsite <application_label> . <modelsite_label>

    Example:

    ppscmd stagingdb /operation loaddatafromstaging /server https://localhost:46787 /path alpine_ski_house.ash_corporate.dimension:account /currentsite alpine_ski_house.ash_corporate
    
  6. Synchronize all dimensions for each site. Note that when you synchronize the dimensions for any site, the ID Tables or Non-Label Tables are rebuilt. After the tables are rebuilt all Error information is removed.

    Syntax:

    ppscmd stagingdb /operation synchronizedata /server http:// <servername> :46787 /path <application_label> . <modelsite_label> /collection dimensions

    Example:

    ppscmd stagingdb /operation synchronizedata /server https://localhost:46787 /path alpine_ski_house.ash_corporate /collection dimensions
    
  7. Perform label to ID conversion for all hierarchies. Use existing Microsoft SQL Server 2005 stored procedures in the staging database to perform this operation. For more information, see PerformancePoint data integration.

    Syntax:

    EXEC [dbo].[bsp_DI_ConvertHierarchyLabelColumnToMemberIdForDimension]
    @DimensionName = <modelsite_Label>:<Dimension_Label>,
    @OverwriteExistingData = T | F
    

    Example:

    EXEC [dbo].[bsp_DI_ConvertHierarchyLabelColumnToMemberIdForDimension]
    @DimensionName = N'Ash_Corporate:Account',
    @OverwriteExistingData = N'T'
    
  8. Load all dimensions for hierarchies for each site by using Planning Business Modeler and Planning Command Utility. Repeat as required. This loads the hierarchies for each dimension.

    Syntax:

    ppscmd stagingdb /operation loaddatafromstaging

    /server <planning server url> /path <application_label>.<modelsite_label> .dimension: <dimension_label>

    /currentsite <application_label> . <modelsite_label>

    Example:

    ppscmd stagingdb /operation loaddatafromstaging  /server https://localhost:46787 /path alpine_ski_house.ash_corporate.dimension:account /currentsite alpine_ski_house.ash_corporate
    
  9. Verify the BizSystem flags in the ID and label tables.

Migrating prepared fact data

Fact data migration only supports migrating valid fact data from the source to the destination environment. Fact data that is generated by rules and calculations is considered invalid.

If there is a business need to migrate this data, the /novalidation flag must be used.

Migrate the prepared fact data

  1. Synchronize all dimensions for each site. This synchronizes all the hierarchies as well.

    Note

    Migration of data generated by rules in the source system is not allowed.

    Syntax:

    ppscmd stagingdb /operation synchronizedata

    /server <planning server url> /path <application_label> . <modelsite_label>

    /collection dimensions

    Example:

    ppscmd stagingdb /operation synchronizedata /server https://localhost:46787 /path alpine_ski_house.ash_corporate /collection dimensions
    
  2. Perform label to ID conversion for all models. Use existing SQL Server stored procedures in the staging database to perform this operation. For more information, see PerformancePoint data integration.

    Syntax:

    EXEC [dbo].[bsp_DI_ConvertLabelColumnToMemberIdForModel]
    @ModelName = N’<modelsite_label>:<model_label>’,
    @OverwriteExistingData = T | F,
    @IncludeValidation = T | F,@IncludeAnnotation = T | F
    

    Example:

    EXEC[dbo].[bsp_DI_ConvertLabelColumnToMemberIdForModel]
    @ModelName = N'Ash_Corporte:Corporate Costs',
    @OverwriteExistingData = N'T',
    @IncludeValidation = N'T',
    @IncludeAnnotation = N'F'
    
  3. Load all the models for each site by using Planning Business Modeler and Planning Command Utility. Repeat as required.

    Syntax:

    ppscmd stagingdb /operation loaddatafromstaging

    /server <server url>

    /path <application_label> . <modelsite_label>

    Example:

    ppscmd stagingdb /operation loaddatafromstaging /server https://localhost:46787 /path “alpine_ski_house.ash_corporate.model:corporate costs”
    
  4. Synchronize the staging database for each site by using Planning Business Modeler and Planning Command Utility for all models.

    Syntax:

    ppscmd stagingdb /operation synchronizedata

    /server <server url>

    /path <application_label> . <modelsite_label>

    /collection models

    Example:

    ppscmd stagingdb /operation synchronizedata /server https://localhost:46787 /path alpine_ski_house.ash_corporate /collection models
    

Migrate associations, metadata and reference data

  1. Convert labels to IDs for associations.

    EXEC[dbo].[bsp_DI_ConvertLabelColumnToMemberIdForAssociation]
    
  2. Load associations by using Planning Command Utility.

    Syntax:

    ppscmd stagingdb /operation loaddatafromstaging

    /server <planning server url>

    /path <application_label>

    /collection associations

    Example:

    ppscmd stagingdb /operation loaddatafromstaging /server https://localhost:46787 /path alpine_ski_house /collection associations
    

Loading considerations

The following are some considerations to keep in mind during load. For more information about loading, see PerformancePoint data integration.

Dimensions

Linked dimensions

  • Always load linked dimensions after loading the dimension they depend on. This means that linked dimension data should remain in the label table until the dependent dimensions are loaded into the application database.

  • Some dimensions might be linked to themselves. The best way to handle this situation is to first load the dimension with the linked member property set to NULL. After the dimension is loaded into the application database, update the linked member property with correct values and load again.

Site-specific loading

  • Shared dimensions can have members that are owned by different model sites. An incremental process must be taken to load a dimension with members that belong to different model sites. First, convert labels to IDs for the one model site by using existing stored procedures. Then, load those members from that model site. Go back and convert labels to IDs for another model site, and then load for that model site. Repeat this process for each model site that contains members for these dimensions.

Models

While migrating fact data, the related annotations can also be migrated. All the stored procedures that deal with models also work on annotations. When you synchronize or load models, annotations are also synchronized and loaded. When you create label tables for models, label tables are also created for annotations. When you convert labels to IDs, you have the option to also convert annotation labels to IDs.

Associations

The associations load operation is performed at the application level. The user performing the load operation must have application-level data administrator permissions to perform this operation.

Associations that are successfully loaded will have the BizSystemFlag column updated from 200 to 100. Associations that were valid within the staging area but not loaded will retain the BizSystemFlag value of 200. This might be due to the association already existing in the application.

See Also