Dissecting the model XML - Part 3 - GIGO

Usually your data will reside in CSV files, spreadsheets, or external databases. Dimension data will be loaded using CSV (1-time) or SSIS (maintainable and scalable).

You will probably use SSIS and the long-winded stored procedures within the staging database to create your label tables and stage the data, and some commands in the model or PPSCMD to stage the data, rinse and repeat.

It is a bit confusing for even the seasoned ETL developer. MS released a data import wizard that may help a bit.

Instead of using CSV or the wizard, I suggest you load your source dimension data into a SourceDB database. Then add tables using the Data Source tab to expose them to the Modeler application. You can even export the data back to the Source database after modifications in Modeler, using the Data Export job and a Data Export ruleset.

If you really need CSV files, you could try creating views on top of them using a file share on the SQL Server and linked server. Not sure how many compliance rules that one breaks though...

http://performancepointing.blogspot.com/2007/09/how-to-load-performancepoint-dimension.html

Data to be loaded into models (eg. Actuals, historical forecasts) requires using either the new Data Wizard or some custom packages or other methods of getting the data to the measure group staging tables. There is no way out of the box to to import fact data into the model. You could create some NativeSQL rules to run an SSIS job from a SQL Agent if you need your users to import data themselves.... hint...

Even though you're out of luck with Actuals and the Data Source, you can Load dimension members from Source and map to a table specified.

Back to the XML:

This section could be blank if you don't setup the Source and Destination data sources in the admin web.

Some self-explanitory nodes.

-
-
-
StagingDB


Your type could be Data Source View (DSV) or Database.

Database

Is it a Source or Destination?
true

Is it enabled for access by Data Administrators?
true

What's the SQL Server name?
PPSTRAINING

What's the SQL database name?
FBP1_Staging


What tables are in the database? By default, none are exposed to the Data Source.

I am using the Staging Database as a data source (probably not a good idea since it can get modified by the Modeler).

-
A_Profit And Loss_MeasureGroup
A_Profit And Loss_MeasureGroup_Label
A_Sales Assumptions_MeasureGroup
A_Sales_MeasureGroup
AG_AccountClassification
AG_AccountType
AG_DebitCredit
AG_EntityType
AG_ExchangeRateType
AG_FlowType
AG_OtherDimAggregation
AG_TimeAggregation
AG_TimeBalance
D_Account
D_BusinessProcess
D_ConsolidationMethod
D_Currency
D_Customer
D_Department
D_Division
D_Employee
D_Entity
D_ExchangeRate
D_Flow
D_Geography
D_InterCompany
D_Location
D_Product
D_Scenario
D_Time
D_TimeDataView
D_User
DBSchemaVersion
FK_Relationships
H_Account_Profit and Loss
H_BusinessProcess_Standard
H_Customer_Customers
H_Department_Departments
H_Division_Divisions
H_Employee_Employees
H_Entity_Entities
H_Geography_Geographies
H_Location_Locations
H_Product_Products
H_User_Approver
H_User_Reviewer
MG_Profit And Loss_MeasureGroup_default_partition
MG_Sales Assumptions_MeasureGroup_default_partition
MG_Sales_MeasureGroup_default_partition
ModelSiteTree
NS_Account_Profit and Loss
NS_BusinessProcess_Standard
NS_Customer_Customers
NS_Department_Departments
NS_Division_Divisions
NS_Employee_Employees
NS_Entity_Entities
NS_Geography_Geographies
NS_Location_Locations
NS_Product_Products
NS_User_Approver
NS_User_Reviewer
SystemErrorDetails
test


These tables are available to the Modeler, provided they have permissions to access.

-
MG_Profit And Loss_MeasureGroup_default_partition_Label



Here's our Destination datasource. Note that you should Create database and copy the 2 tables (BizSnapshotStatus and DBSchemaVersion) into any database you want to use as a destination, otherwise it won't get recognized as a valid destination.

I haven't created any tables in the destination yet but they would show up similar to above.

-
OutputDB

Database
false
true
PPSTRAINING
OutputDB







It would be useful if more source and target data sources could be used... perhaps ODBC or .NET providers?