• Article

Prepare the staging area

Updated: 2009-09-17

This process is used to prepare the source data to be loaded into the destination server. This allows both the source and destination environments to be unaffected.

Stored procedures that help with data integration can be found in the staging database. The following table contains a list of stored procedures that are included in the staging database. They will help you perform data migration.

Use the fk_relationships table to find out to which model sites the objects belong. The following script can be used to find all dimensions or models in an application:

Select distinct scope from fk_relationships where scopetype 'D'|'MG'
Label-based data preparation Description

bsp_DI_CreateLabelTableForDimension

Creates and populates a label table for a dimension.

bsp_DI_CreateHierarchyLabelTable

Creates and populates a label table for a single hierarchy.

bsp_DI_CreateHierarchyLabelTableForDimension

Creates and populates label tables for all hierarchies of a dimension.

bsp_DI_CreateLabelTableForMeasureGroup

Creates and populates a label table for a single measure group.

bsp_DI_CreateLabelTableForModel

Creates and populates a label table for all measure groups of a model.

bsp_DI_ConvertLabelColumnToMemberIdForDimension

Converts labels to IDs for a dimension.

bsp_DI_ConvertHierarchyLabelColumnToMemberId

Converts labels to IDs for a single hierarchy.

bsp_DI_ConvertHierarchyLabelColumnToMemberIdForDimension

Converts labels to IDs for all hierarchies of a dimension.

bsp_DI_ConvertLabelColumnToMemberIDForMeasureGroup

Converts labels to IDs for a single measure group.

bsp_DI_ConvertLabelColumnToMemberIDForModel

Converts labels to IDs for all measure groups of a model.

bsp_DI_ResetSystemColumnsForDimension

Resets system columns for a dimension and all its hierarchies per modelsite

bsp_DI_ResetSystemColumnsForModel

Resets system columns for all measure groups of a model.

bsp_DI_ResetSystemColumnsForAssociations

Resets system columns for all associations.

bsp_DI_ConvertLabelColumnToMemberIdForAssociation

Converts labels to Ids for associations.

Prepare reference and fact data

  1. Restore the staging database that you have already created and backed up.

  2. Create label tables for dimensions, hierarchies, and models and perform ID to label conversion for the data to prepare the staging database for migration. Use existing Microsoft SQL Server 2005 stored procedures in the staging database to perform this operation. See PerformancePoint data integration for additional information.You must also manually delete all rows where MemberID = -1 for all Dimensions except the Scenario Dimension. Do not delete any -1 or NONE MemberIDs in the Scenario Dimension. By default the Scenario Dimension does not contain any -1 or NONE MemberIDs. However, some organizations do create their own -1 or NONE MemberIDs; if this is the case, do not delete these MemberIDs.

    Syntax:

    EXEC[dbo].[bsp_DI_CreateLabelTableForDimension]
@DimensionName = <Modelsite_Label>:<Dimension_Label>,
@IncludeExistingData = T | F

EXEC[dbo].[bsp_DI_CreateHierarchyLabelTableForDimension]
@DimensionName = <Modelsite_Label>:<Dimension_Label>,
@IncludeExistingData = T | F

EXEC[dbo].[bsp_DI_CreateLabelTableForModel]
@ModelName = <Modelsite_Label>:<Model_Label>,
@IncludeExistingData = T | F

    Examples:

    EXEC[dbo].[bsp_DI_CreateLabelTableForDimension]
@DimensionName = N'Ash_Corporate:Account',
@IncludeExistingData = N'T'

EXEC[dbo].[bsp_DI_CreateHierarchyLabelTableForDimension]
@DimensionName = N'Ash_Corporate:Account',
@IncludeExistingData = N'T'

EXEC[dbo].[bsp_DI_CreateLabelTableForModel]
@ModelName = N'Ash_Corporate:Corporate Costs',
@IncludeExistingData = N'T'

  3. Set the following System fields and flags in the Label-based tables by using existing SQL Server stored procedures in the staging database.

    Dimensions:

    • Set all MemberIDs to NULL.

    • Set BizSystemFlag = 0 for all dimension label tables.

    • Set BizSystemErrorDetails = NULL for all dimension label tables.

    Hierarchies:

    • Set RowId to NULL in all hierarchy label tables.

    • Set BizSystemFlag = 0 for all hierarchy label tables.

    • Set BizSystemErrorDetails = NULL for all hierarchy label tables.

    Syntax:

    EXEC[dbo].[bsp_DI_ResetSystemColumnsForDimension]
@ModelSiteName = <Modelsite_Label>,
@DimensionName = <Modelsite_Label>:<Dimension_Label>

    Example:

    EXEC[dbo].[bsp_DI_ResetSystemColumnsForDimension]
@ModelSiteName = N'Ash_Corporate',
@DimensionName = N'Ash_Corporate:Account'

    Models:

    • Set RowId in measure group label tables to NULL.

    • Set BizSystemFlag = 0 for all measure group label tables.

    • Set BizSystemErrorDetails = NULL for all measure group label tables.

    • Set BizValidationStatus = 0 for all measure group label tables.

    Syntax:

    EXEC[dbo].[bsp_DI_ResetSystemColumnsForModel]
@ModelName = <Modelsite_Label>:<Model_Label>

    Examples:

    EXEC[dbo].[bsp_DI_ResetSystemColumnsForModel]
@ModelName = N'Ash_Corporate:Corporate Costs'

    Associations

    • Set BizSystemFlag = 0 for all measure group label tables.

    • Set BizSystemErrorDetails = NULL for all measure group label tables.

    • Set MemberIDs to NULL in the AssociationMember and AssociationDimensionScope tables.

    Example:

    EXEC[dbo].[bsp_DI_ResetSystemColumnsForAssociations]

  4. Set the system fields and flags in the label-based tables manually (as appropriate):

    Dimensions:

    • Set BizSystemFlag = 200 for all dimension tables.

    • Set BizSystemFlag = 200 for all hierarchy label tables.

    Models:

    • Set BizSystemFlag = 200 for all measure group label tables.

    Associations:

    • Set BizSystemFlag = 200 for all association tables.

  5. Back up the prepared staging area.

See Also