Creating a Rolling Forecast Data Input Form


Advertisements

Learning PPS Part II – Building Models and Data Load


In part I we saw how to configure all the underlying base Dimensions which will be used in Model configuration. Now the next step is to create the Models and load Actual data against those models.

Now creating models is not something complex, we just need to open the two instance of PBM and in one open the model from Source Application i.e Alpine_Ski_House and in other we will connect to our ASHPract application.

Steps:

  1. Open the Model that you need to build in Source Application and review its structure (e.g Model Type, Used Dimensions and Member Set, Alias given to each member set,  Model Name, Model label, Current Period Property etc…). Basically the model we are creating should be exact copy of Alpine_Ski_House then only in later stages we can review various forms which are available based on these models.

image

  1. Some Models will have assumption models associated with it. These assumption models can be created later and any time it can be attached to the respective models using ‘Manage Assumptions’ option.
  2. Once the models are configured you can deploy the Model Site so that the corresponding OLAP Cubes will get configured in the Analysis Services Database.

Now the next part is data load. The Model FACT table names will start with ‘MG_’. For Model Site Models it will have the Model name in the Measure Group table names. For Model SubSite th Measure Group will contain the Model SubSite name too along with the Model Name.

Steps for loading data from ASH to ASHPract will be:

  1. Synchronize the models to Staging database in both the applications.
  2. Create Label Table for the Measure Group tables in both the application’s Staging Database.
  3. Load the Label Table data from ASH to ASHPract staging Database.
  4. Run ‘bsp_DI_ConvertLabelColumnToMemberIDForModel’ to generate the ID columns in Measure Group Table.
  5. From PBM run ‘Synchronize Data from Staging Area’ option.
  6. Run Refresh Model Data from PBM.

Some models based on their type may have more than one Measure Group Table e.g Models of type ‘Exchange Rate’ will have ‘MG_XXX_MeasureGroup_default_partition’ and ‘MG_XXX_EntityExchangeRates_default_partition’ measure group tables. Similarly models of type ‘Financial Model with Shares’ will have 4 measure group tables ‘MG_XXX_MeasureGroup_default_partition’, ‘MG_XXX_ControlAndOwnership_default_partition’, ‘MG_XXX_SharesOutstanding_default_partition’ and ‘MG_XXX_SharesOwned_default_partition’.

Below are the queries I used to load the data for ‘External Corp Reporting’ model.

****************************************

–Load External Corp Reporting Data
DECLARE @return_value INT
EXEC @return_value = [dbo].[bsp_DI_CreateLabelTableForMeasureGroup]
     @ModelName = N’ASH_Corporate:External Corp Reporting’,
     @MeasureGroupTableName = N’MG_External Corp Reporting_MeasureGroup_default_partition’,
     @IncludeExistingData = N’T’
SELECT ‘Return Value’ = @return_value

INSERT INTO [MG_External Corp Reporting_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, Currency_MemberId_Label,
    InterCompany_MemberId_Label, Value, RowId, BizSystemFlag, BizValidationStatus, BizSystemErrorDetails)
SELECT
    Scenario_MemberId_Label, Time_Month_Label, Account_MemberId_Label, BusinessProcess_MemberId_Label,
    Entity_MemberId_Label, TimeDataView_MemberId_Label, Flow_MemberId_Label, Currency_MemberId_Label,
    InterCompany_MemberId_Label, Value, NULL RowId, 200 BizSystemFlag, 0 BizValidationStatus, NULL BizSystemErrorDetails
FROM
    ALPINE_SKI_HOUSE_STAGINGDB.DBO.[MG_External Corp Reporting_MeasureGroup_default_partition_Label]
WHERE
    Scenario_MemberId_Label = ‘ACTUALS’
    AND RULEID = -1

DECLARE @return_value INT
EXEC @return_value = [dbo].[bsp_DI_CreateLabelTableForMeasureGroup]
     @ModelName = N’ASH_Corporate:External Corp Reporting’,
     @MeasureGroupTableName = N’MG_External Corp Reporting_ControlAndOwnership_default_partition’,
     @IncludeExistingData = N’T’
SELECT ‘Return Value’ = @return_value

INSERT INTO [MG_External Corp Reporting_ControlAndOwnership_default_partition_Label]
    (ConsolidationMethod_MemberId_Label, Entity_MemberId_Label, Scenario_MemberId_Label, Time_Month_Label,
    [Overridden PCON], [Overridden PCTRL], [Overridden POWN], PCON, PCTRL, POWN, RowId,
    BizSystemFlag, BizValidationStatus, BizSystemErrorDetails)
SELECT
    ConsolidationMethod_MemberId_Label, Entity_MemberId_Label, Scenario_MemberId_Label, Time_Month_Label,
    [Overridden PCON], [Overridden PCTRL], [Overridden POWN], PCON, PCTRL, POWN, NULL RowId,
    200 BizSystemFlag, 0 BizValidationStatus, NULL BizSystemErrorDetails
FROM
    ALPINE_SKI_HOUSE_STAGINGDB.DBO.[MG_External Corp Reporting_ControlAndOwnership_default_partition_Label]
WHERE
    Scenario_MemberId_Label = ‘ACTUALS’

DECLARE @return_value INT
EXEC @return_value = [dbo].[bsp_DI_CreateLabelTableForMeasureGroup]
     @ModelName = N’ASH_Corporate:External Corp Reporting’,
     @MeasureGroupTableName = N’MG_External Corp Reporting_SharesOutstanding_default_partition’,
     @IncludeExistingData = N’T’
SELECT ‘Return Value’ = @return_value

INSERT INTO [MG_External Corp Reporting_SharesOutstanding_default_partition_Label]
    (Entity_MemberId_Label, Scenario_MemberId_Label, Time_Month_Label, [Total Shares Outstanding],
    [Voting Rights Outstanding], RowId, BizSystemFlag, BizValidationStatus, BizSystemErrorDetails)
SELECT
    Entity_MemberId_Label, Scenario_MemberId_Label, Time_Month_Label, [Total Shares Outstanding],
    [Voting Rights Outstanding], NULL RowId, 200 BizSystemFlag, 0 BizValidationStatus, NULL BizSystemErrorDetails
FROM
    ALPINE_SKI_HOUSE_STAGINGDB.DBO.[MG_External Corp Reporting_SharesOutstanding_default_partition_Label]
WHERE
    Scenario_MemberId_Label = ‘ACTUALS’

DECLARE @return_value INT
EXEC @return_value = [dbo].[bsp_DI_CreateLabelTableForMeasureGroup]
     @ModelName = N’ASH_Corporate:External Corp Reporting’,
     @MeasureGroupTableName = N’MG_External Corp Reporting_SharesOwned_default_partition’,
     @IncludeExistingData = N’T’
SELECT ‘Return Value’ = @return_value

INSERT INTO [MG_External Corp Reporting_SharesOwned_default_partition_Label]
    (Entity_MemberId_Label, OwnedEntity_MemberId_Label, Scenario_MemberId_Label, Time_Month_Label,
    [Has Control Via Override], [Shares Owned], [Voting Shares Owned], RowId,
    BizSystemFlag, BizValidationStatus, BizSystemErrorDetails)
SELECT
    Entity_MemberId_Label, OwnedEntity_MemberId_Label, Scenario_MemberId_Label, Time_Month_Label,
    [Has Control Via Override], [Shares Owned], [Voting Shares Owned], NULL RowId,
    200 BizSystemFlag, 0 BizValidationStatus, NULL BizSystemErrorDetails
FROM
    ALPINE_SKI_HOUSE_STAGINGDB.DBO.[MG_External Corp Reporting_SharesOwned_default_partition_Label]
WHERE
    Scenario_MemberId_Label = ‘ACTUALS’

DECLARE @return_value INT
EXEC @return_value = [dbo].[bsp_DI_ConvertLabelColumnToMemberIDForModel]
     @ModelName = N’ASH_Corporate:External Corp Reporting’,
     @OverwriteExistingData = N’T’,
     @IncludeValidation = N’T’,
     @IncludeAnnotation = N’T’
SELECT ‘Return Value’ = @return_value

****************************************

While loading the data BizSystemFlag has to be 200. Also I am loading only Actuals for RuleID = -1. I am assuming the data with RuleID other than -1 will be generated using various business rules e.g. Currency Conversion, Intercompany Elimination etc. However I still need to verify that. If my assumption is not correct please let me know.

Learning PerformancePoint Server


I am sure there will be many people like me who really want to explore PerformancePoint Server and play around all the functionality supported.

However many times we spend more time in creating underlying data that is required for verifying the functionality rather then spending the time on the functionality itself. Then my brother Ajit Singh (http://asmdx.blogspot.com/) suggested me why not use the data from Alpine Ski House as it will contain all the data needed in order to explore the functionality supported by PPS.

Now the first step was to create a new application (in my case I used ‘ASHPract’) and then created the root site with the label ‘ASH_Corporate’. Then we need to open two instance of PBM, one with Alpine_Ski_House application and one with our newly created one both connected to ‘ASH_Corporate’ site. Then create all the additional dimensions manually (don’t bother about the properties as of now). One this is done, we need to check each dimension and create all the member sets manually.

Now for loading members, first we will use the export functionality of PPSCMD.exe (C:\Program Files\Microsoft Office PerformancePoint Server\3.0\BizModeler\PPSCmd.exe). I would suggest adding it to Environment Variables so that you can directly use it from command prompt.

PPSCMD.exe will be available after the installation of Planning Business Modeler.

Now we can export the dimension members for a dimension (all member sets will be exported into multiple CSV files). You need to create a folder and specify that in command where CSV files will be exported.

Below is the sample code:

ppscmd ExportDimension /server http://localhost:46787 /app Alpine_Ski_House /site ASH_Corporate /dim Account "C:\Users\Ajay Singh\Desktop\PPS\Dimension\Account"

image

Like this we need to export all the required  dimensions. After that from PBM we need to load all these members to respective dimensions using CSV data load functionality. This will create all the necessary user defined properties defined in Alpine_Ski_House application. Also we don’t have to bother about data type of these properties as I verified that all these properties are defined as Text type in ASH.

Be careful in defining the Calendar for this application. The setup will be that Year will always end at ’12/31/2008′ and you can take 9 Yrs back and 5 Yrs forward for initial Calendar definition. And also you will need to create three calendar views Annual, Daily, Monthly and Quarterly.

With this we will be ready with the base setup. Next we will be discussing the Model creation and how to load the data from ASH to ASHPract.

Business Intelligence Studio For Microsoft Office PerformancePoint Server 2007


Venice Consulting Group (VCG) announced the release of Business Intelligence Studio (BIS) software for Microsoft Office PerformancePoint Server 2007. BIS was developed by Orange Peel Inc., a joint venture of VCG and Solver Inc.

Website: http://www.orangepeelcorp.com/pages/Home.aspx

Product Info:

http://www.orangepeelcorp.com/pages/Public/OurProducts.aspx

Screen Casts:

http://screencasts.orangepeelcorp.com/default.html