Identifying the parameters for data population of a measure group for staging table

Populating actuals in PerformancePoint isn't necessarily trivial.

Once you have created the staging database and synchronized tables into the database, you need to modify the contents of staging to include dimensions, hierarchies, and measures.

Usually, actuals are the first thing to go into a planning application, as variance reports are important along with seeded data.

This table holds the key to parameters for the [dbo].[bsp_DI_CreateLabelTableForMeasureGroup] stored procedure.

SELECT * FROM FK_Relationships

eg.

[dbo].[bsp_DI_CreateLabelTableForMeasureGroup] 'AppRoot:FinancialWithShares', 'MG_FinancialWithShares_MeasureGroup_default_partition'

Once this table is created:

select * from MG_FinancialWithShares_MeasureGroup_default_partition_label

This shows what rows are required. The PPS Operation document has some examples of this.

Modifying our Actual population view.

alter view vActualsByCostCentre_Unpivot

as

SELECT 'ACTUAL' as [Scenario_MemberId_Label] ,[Time_Month_Label] ,[Account_MemberId_Label] ,'INPUT' as [BusinessProcess_MemberId_Label] ,'Open Text Corporation Canada' as Entity_MemberId_Label ,'PERIODIC' as TimeDataView_MemberId_Label ,'NONE' as Flow_MemberId_Label ,[Cost Centre_MemberID_Label] ,'NONE' as Department_MemberId_Label ,'NONE' as Product_MemberId_Label ,'USD' as Currency_MemberId_Label ,[Value] ,null RowID ,200 as [BizSystemFlag]

FROM(

SELECT [CostCentre] as [Cost Centre_MemberID_Label] ,[Label] as [Account_MemberId_Label] ,[July Year2007] = [Jul07] ,[August Year2007] = [Aug07] ,[September Year2007] = [Sept07] ,[October Year2007] = [Oct07] ,[November Year2007] = [Nov07] ,[December Year2007] = [Dec07] ,[January Year2008] = [Jan08] ,[February Year2008] = [Feb08] ,[March Year2008] = [Mar08] ,[April Year2008] = [Apr08]

FROM [ActualsByCostCentre]) [Value]UNPIVOT ([Value] FOR [Time_Month_Label] in ( [July Year2007] ,[August Year2007] ,[September Year2007] ,[October Year2007] ,[November Year2007] ,[December Year2007] ,[January Year2008] ,[February Year2008] ,[March Year2008] ,[April Year2008] ) ) AS UNPVT

Then

INSERT INTO MyApp_StagingDB.dbo.MG_FinancialWithShares_MeasureGroup_default_partition_label([Scenario_MemberId_Label] ,[Time_Month_Label] ,[Account_MemberId_Label] ,[BusinessProcess_MemberId_Label] ,[Entity_MemberId_Label] ,[TimeDataView_MemberId_Label] ,[Flow_MemberId_Label] ,[Cost Centre_MemberID_Label] ,[Department_MemberId_Label] ,[Product_MemberId_Label] ,[Currency_MemberId_Label] ,[Value] ,[RowID] ,[BizSystemFlag])
SELECT [Scenario_MemberId_Label] ,[Time_Month_Label] ,[Account_MemberId_Label] ,[BusinessProcess_MemberId_Label] ,[Entity_MemberId_Label] ,[TimeDataView_MemberId_Label] ,[Flow_MemberId_Label] ,[Cost Centre_MemberID_Label] ,[Department_MemberId_Label] ,[Product_MemberId_Label] ,[Currency_MemberId_Label] ,[Value] ,[RowID] ,[BizSystemFlag] FROM [MyApp_SourceDB].[dbo].[vActualsByCostCentre_Unpivot]

The last SQL step after populating the label table?

DECLARE @return_value INT

EXEC @return_value = [dbo].[bsp_DI_ConvertLabelColumnToMemberIDForMeasureGroup] @ModelName = N'MyApp:FinancialWithShares', @MeasureGroupTableName = N'MG_FinancialWithShares_MeasureGroup_default_partition', @OverwriteExistingData = N'F', @IncludeValidation = N'T',@IncludeAnnotation = N'F'

SELECT 'Return Value' = @return_value

GO

The modeler can now go into the application and Load this data from Staging Area.

TechNet Forums