Norm's PerformancePoint Server Blog

Moving fact data for Label-based tables for PPS video

Forest Dermid has provided a video on moving measure group (fact) data to PerformancePoint Planning Server (PPS). 

Click here to begin the video in Windows Media Player. Also, the following are some related files if you choose to view the same data that is being used. See my notes that accompany the video below.

Also, consider reviewing methods for moving reference data in the following posts.

SSIS and PerformancePoint Server data integration demo part I.

Notes on video demo for moving reference data.

Notes

Objective: provide an introduction "how to" lab on preparing PPS Planning label-based measure group data and load into PPS Server.

Set up requirements -and have a general understanding of these

• Have a general understanding of PPS Planning -more importantly, review the data integration docs for moving measure group data.
• SQL Server 2005 (or 2008!)
• SSAS
• SSIS

Two approaches exist for converting to label-based tables in preparation for populating PerformancePoint Server measure group tables.

• Use PPS stored procs - more of a production solution
• Use a join with source and staging and pull id's.

Here are the steps from the data integration help.

To prepare and load measure group data that have labels

1. Create a label-based table

   • For a specified measure group, use the bsp_DI_CreateLabelTableForMeasureGroup stored procedure.

   • For all measure group tables in a specified model, use the bsp_DI_CreateLabelTableForModel stored procedure.

2. Populate label-based measure group tables with data from the source database or system. This is a manual step.

3. Validate label-based measure group tables and correct any errors by using the bsp_DI_ValidateLabelTable stored procedure.

4. Repeat step 3 as required until all label-based data is valid.

5. Convert a label-based measure group table to a MemberID-based table

   • For a specified label-based measure group table, use the bsp_DI_ConvertLabelColumnToMemberIDForMeasureGroup stored procedure.

   • For all label-based measure group tables in a specified model, use the bsp_DI_ConvertLabelColumnToMemberIDForModel stored procedure.

You can use one of the following methods to validate measure groups before you load data from the staging database to the application database.

• Validate data in a specified measure group by executing the Transact-SQL stored procedure, bsp_DI_ValidateMeasureGroup.

• Validate all measure groups for a specified model by executing the Transact-SQL stored procedure, bsp_DI_ValidateMeasureGroupForModel.

Note

If you have questions about the process, don't forget to use the PerformancePoint Server forum. You can also send me a message if you are concerned about if a question is phrased correctly -I'm a writer.

Have fun !

-Norm