Prepare the staging area

Prepare the staging area

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

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

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. You can find additional details under PerformancePoint data integration help .

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'

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

Dimensions:
- Delete all rows where MemberID = -1
- 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]
```
Set the system fields and flags in the label-based tables manually (as appropriate):

Dimensions:
- Delete all rows with MemberID = -1.
- 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.
Back up the prepared staging area.

Prepare reference and fact data

See Also

Tasks

Concepts