SSIS and PerformancePoint Server data integration demo part I
Part I includes some high level instruction on moving reference data to the stagingDB (created from the PPS Planning DB). I had to stop short, so...to be continued.
[update] I have added to part I the following:
The rest to follow might look something like the following:
- Part II - Move fact data using SSIS
- Part III - Video demo of updating
Introduction
Before data analysis and planning is available in the PPS business modeler application, you must get data from one or more combination of business data sources into the application database. PerformancePoint Server provides stored procedures and methodologies to help move data to its application databases. Data can be moved from any one of your data sources to a staging database. The data can then be validated and moved from the staging database to the application database. You could call it the data integration (DI) process.
All of this can be scripted in an SSIS package using the PPSCmd command utility (PPSCmd.exe) and SSIS. There is nothing better than making the DI process for planning modeler repeatable with the powerful tools in SQL Server 2005 and the PPS Planning Command Utility.
I should note that this post will only give you an idea of what you can do in SSIS with PPSCmd.exe and SSIS Control Flow, Data Flow & Maintenance tasks. My post will not discuss at length the tools in SSIS, the complete data integration process, or trouble shooting fixes for DI in SSIS (go to bottom for links to trouble shooting).
Before I continue, here is a simple diagram to show the process of data integration for PPS Planning.
The examples that I will provide come from an end-to-end solution using AdventureWorks and created by one of Microsoft's senior technical solutions professionals, Forrest Dermid. The complete end-to-end scenario will probably show up in a white paper later on.
Before we start, here are some more assumptions:
- The Business Modeler Planning application structure is created (models, dimensions, etc...) -the holding structure for your data.
- The staging database has been created -although you could also create it using the SSIS Execute Process Task and embed a statement similar to the following. Note: switches are case sensative.
PPSCmd.exe StagingDB /Operation CreateStaging /Server http://localhost:46787 /Path <path>
- You have created an SSIS package before and have general familiarity.
- You have read the Planning data integration documentation to understand the process that will be reflected in the SSIS package.
How to
Let's start where we must synchronize the staging database using SSIS to first copy the app db structure to the staging db.
- 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
...TO BE CONTINUED NEXT WEEK
