Staging data into PerformancePoint Planning

 The documentation is intentionally vague on this subject.  We have implemented staging from our relational data sources (Accounting, Timesheet, Project Tracking) using SQL Server Integration Services. (SSIS)

One simple way for loading fact data is to create an SSIS Package with a Data Flow Task.  The DFT contains an OLEDB Source pointed to your data store & OLEDB Destination pointed to the fact label table within PerformancePoint's staging database for your application.  The procedure bsp_DI_CreateLabelTableForMeasureGroup is executed using a SQL Script Task after the DFT.

Here is a posting from the newsgroups that may help more. 

Hi Kurinji,
The ETL process to the Staging DB is such a custom process that
detailed steps can't really be given.  What you will have to do is to
create your dimensions, hierarchies, and models in the Business
Modeler, and then synchronize them all to the Staging Area.  Pay
special mind to the built-in dimensions, because they may have
properties you have to define, such as Account and the AccountType.
At any rate, what you'll need to do is load the following columns for
each dimension:
MemberId = NULL (This will be changed when it gets loaded to App)
Label = Your choice (This must be a unique identifier.  Be careful,
however, as the Label is used as an end-user identifier as well.
Make sure that it's both user-friendly and unique.)
Name = Your choice (Supposed to be the friendly name, so make it that
way)
Description = Your choice
SequenceNumber = NULL (PPS will take care of this)
SourceMemberId = The key of the source record (Use this to refer back
to your source table.  Usually you map the primary key to this.)
OwnerID = 0 (Tells PPS Admin owns it)
CreateDatetime = getdate()
ChangeDatetime = getdate() (Both of these are fairly
self-explanatory.)
LoadingControlID = NULL (PPS will take care of this)
BizSystemFlag = 200 (Needs to be 200 so PPS knows to add it. 300 is
update, and 400 is delete. 100 is that it's already in the database,
0 is do nothing with it and 900+ is an error code.)
BizSystemErrorDetails = NULL (PPS will flip this to the column names
where an error occurred if on did in fact occur.)
You may have a property you need to update if you've added any
dimension properties or you're using a built-in dimension.  If you're
using a built-in dimension, what you'll need to do is make a
cross-reference to the  corresponding AG_ table for its MemberId.
For example, if you wanted an Account to be an asset, for the
AccountTypeMemberId column in D_Account in Staging, you'd use (SELECT
MemberId FROM AG_AccountType WHERE Label = 'Asset').
Side note: All dimension tables are labeled D_, while measure tables
are labeled MG_.  Hierarchy tables are labeled H_.  Check the
documentation for specific details of these tables.
Also note that you should make heavy use of SSIS and the PPSCmd.exe
utility in your ETL process.  This way, you'll be able to make a
repeatable and scheduled process to do the ETL.  For PPSCmd.exe
documentation, look in the Business Modeler documentation.
As for MeasureGroup tables, you'll need to use the MemberIds of your
Dimension tables, so make sure that you load your Dimension tables
into the App DB BEFORE staging the fact data.
Your process in SSIS should look roughly like:
Sync Dimensions->Sync Models->Stage Dimensions->Load Dimensions->Stage
Hierarchies->Load Dimensions->Stage Fact->Load Fact->Deploy Model
This way, you'll have all the right IDs available when you need them.
Note that loading a dimension also loads all of its hierarchies.
Hopefully this is helpful!
Cheers,
Eric
Kurinji wrote:
> Hi all,
> Am new to PPS planning area. I would like to know the steps to
> load the staging database from the relational database(SQL Server)
> which i already have. In the documentaion, i read its a manual
> process to load the staging DB, but nowhere am able to see that
> manual process steps. Can somebody help me in setting up the staging
> db and loading it with the Relational database.
> Thx