How to Load PerformancePoint Dimension from CSV to Source Data View to App Database

 These steps will allow you to use a linked server to a directory to access CSV files generated by PerformancePoint.  These files can be hand-edited with notepad, and then loaded into PerformancePoint as a data view.  We can even create Data Destinations to create the CSV files.  Talk about convolution!

This could be useful when you want to abstract the loading of dimension data, or query CSV files from SQL Server, or use the data in multiple locations without an ETL process, or something else that you don't want to use direct CSV files for.  Please let me know what you decide to use it for....

1. Export your PerformancePoint dimensions using PPSCMD at a command prompt.

C:

MD \Data

"C:\Program Files\Microsoft Office PerformancePoint Server\3.0\BizModeler\ppscmd.exe" exportdimension /Server http://myserver:46787 /App myApp /Site MySite C:\Data

 

2. Run these steps in your Source Database.

(If PerformancePoint exposed an ODBC connection instead of a SQL Connection for the Data Source, we wouldn't need this step.)

EXEC sp_addlinkedserver txtsrv,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\Data',NULL,'Text'
EXECUTE sp_addlinkedsrvlogin 'txtsrv' , False, NULL, NULL, NULL
EXEC sp_tables_ex 'txtsrv'

sp_columns_ex txtsrv,'DimAccountAll Members#csv'

create view vDimAccountAllMembers as
select [] as Label, F4 as P_Parent, F5 as [Description], F6 as [Name], F7 as [AccountTypeMemberID]
from txtsrv...[DimAccountAll Members#csv]
where left([],1) not in ('!')
and [] not in ('Label')

3. Modify the CSV files to your needs.

4. Open the PerformancePoint Planning Administration site at: http://ppsite:46788/DataSources.aspx

Go to Data sources.

Add a Data Source under your model site.  Add the view we just created to the data source.

5. Open Business Modeler.  Click on Account Dimension.  Check out the dimension.  Click Load Members.  Select Load Members from Source.  Specify Source Dimension view that we just created, and map the fields.  Click Finish.

Voila!  A roundabout way of importing data and accessing CSV files directly from SQL Server, without ETL.