One thing that you may not be aware of is that Predixion Insight for Excel has a VBA API that allows you to not only interact with Predixion Insight objects, but with PowerPivot objects as well. Using this API, I can easily create a simple VBA macro that executes a Data Profile for each attached PowerPivot table.
The first thing you need to do is to enable the developer ribbon in Excel since it’s hidden by default. You can do this by going to File/Options and selecting “Customize the Ribbon” and checking the “Developer” option in the “Main Tabs” tree control. Then switch to the developer ribbon and click the “Visual Basic” icon, which, in a throwback to turn of the century user interfaces, launches the VBA editor.
To have a place to write some code, simply select the Insert menu and choose “Module”, which will create an editor that you can write code. The code is very straightforward, you create your PowerPivot and Data Exploration objects from the Predixion VBA library, connect to PowerPivot to get the list of tables, and then get a data profile for each table that is returned. You can copy the code below into the VBA editor as is.
Sub ProfileAllPowerPivotTables(AdvancedProfile As Boolean) Dim PowerPivot As New PredixionVBA.PowerPivot Dim DataExploration As New PredixionVBA.DataExploration PowerPivot.ConnectToPowerPivot TableList = PowerPivot.GetPowerPivotTables() For Each Table In TableList Application.StatusBar = "Profiling PowerPivot Table: " + _ Table DataExploration.GenerateDataProfileForPowerPivot Table, "", _ AdvancedProfile, "" Next Application.StatusBar = "" End Sub Sub ProfileAllPowerPivotTablesAdvanced() ProfileAllPowerPivotTables (True) End Sub
Sub ProfileAllPowerPivotTablesBasic() ProfileAllPowerPivotTables (False) End Sub
As I stated, the code is fairly straightforward. The other parameters for GenerateDataProfileForPowerPivot are an optional table filter and an optional list of columns, which you probably don’t need to get a quick profile of all the tables in a first shot. Running either of the top level functions on the Predixion Sample Data workbook by clicking on the green “play” button generates all the profiles in about a minute with an index to boot.