Notes on video demo for SSIS and PPS data integration demo part I
Here are my notes for the part I video demo that describes moving reference data from a source to the Planning app db. Good news, you can also download the SSIS package from which Forrest is working. Download the SSIS package, watch the video and try it out!
Make sure you see the setup instructions from the ReadMe folder and have the AdventureWorksDW database on SQL Server.
By the way, why so much effort on providing information for data integration? Here are some good reasons.
- You can't use the PPS applications without data (the right data).
- Integrating data can be costly. I want to make it easier by providing instruction.
- Using SSIS and a good process will help you create a repeatable method for loading data.
Video notes
The demo covers the following:
- How to prepare the Account dimension in PPS Planning Business Modeler before populating with data.
- How to synchronize the staging database in SSIS using the Business Modeler command utility, PPSCmd.
- How to Create a Data Flow Task to move data.
- How to load from app db to staging db with the PPSCMD.
- How to populate the hierarchy - using the CreateHierarchFromDim stored procedure.
How to prepare the Account dimension.
The Account dimension is a predefined dimension that acts as a starting chart of accounts for various financial models in the Planning application. Forrest adds the following two members to the Account dimension in Planning Business modeler.
- ParentName - Want to create a Member View or just view the inventory accounts.
- AccountKey - Added as a source primary key to join on between the source and planning application.
On the application database the following tables (notice the naming convention) are created.
- d_account
- h_account
- h_account_report (a Member set Forrest creates in the demo) You create a Member set to organize the dimensions, so that each of the items and members within can be identified and utilized for different purposes.
Review the structure of the application db. Also review the structure of the staging db in the topic About the structure of the staging database.
Create and populate a temp table called T_Account_Report, a replication of the hierarchy table.
How to synchronize the staging database in SSIS
Why? to bring the application db and staging database to the same state. For more information see synchronize the staging database.
- Drag and drop the Execute Process Task (Control Flow Item) onto the Control Flow area.
- Right click and select edit. The Execute Process Task Editor will open.
- Select Process in the left pane.
- Enter the location of the executable (one of the circled fields in the image).
- Enter the argument for the PPSCmd command utility.
- Click OK.
You might be safer to synchronize all dimensions. In that case, your argument would look like the following.
StagingDB /Operation SynchronizeData /Server http://localhost:46787 /Path <path> /Collection dimensions
How to Create a Data Flow Task
OLEDB Source and Destination
Right-click and edit.
How to load from app db to staging db with the PPSCMD
Review PPS data integration considerations on loading dimensions.
Also review the function BizSystemFlag plays while preparing data to load and troubleshooting.
Really, you should just read all of the Planning data integration help. 
How to populate the hierarchy - using the CreateHierarchFromDim stored procedure.
Review PPS data integration help on populating a property-based hierarchy.
Also the stored procedure signature bsp_DI_CreateHierarchyFromDimenstion.
Summary
If it seems like my notes are incomplete, it is because they are incomplete. I ran out of time. But look forward to the next part (loading fact data).
