Tool to convert dataflows from 5x to 6x format

Not all dataflow step types from Profitbase Studio 5x is supported in PS6x. These are marked as obsolete and get a pink color in the dataflow editor.

The obsolete steps can be executed but there is no guarantee that they will work.

Following PS5x steps are marked as obsolete:

  • SSAS/OLAP tasks (against Microsoft Analysis Services)
    • Backup OLAP Databases
    • Restore OLAP Databases
    • Delete OLAP Databases
    • Build OLAP Cubes
    • Load or Update Cube Dimensions
    • Remove OLAP Database Dimensions
    • Process OLAP Cubes
    • Process OLAP Databases
  • Module tasks (against result from modules in the data warehouse)
    • Load/Update Module Fact data
    • Load/Update Module Dimensions
    • Aggregate Data Warehouse Facts
    • Create friendly named Module Views

A tool has been made to convert these 5x steps to corresponding 6x steps. The tool can also check command connections and solution id. A PS5x command may result in multiple PS6x commands. The commands in PS6x are based on a single module vs PS5x that can contain more than one module.

Each step in a dataflow contains a data command stored in an xml command. The tools make it easier to substitute that content vs a SQL or PowerShell script.

Getting and installing the tool

Click here to download the PS6 Dataflow Converter V3.zip file.

Unzip the files to eg. C:\temp\PS6 Dataflow Converter

Click on PS6 Dataflow Converter.exe to start the program.

Using the tool

Click on [Connect Server] to select server and optional user/password. On first startup, a connection to a server must be established. Next time the tool opens, this connection is used.

Then select a solution / solution database from the dropdown box.

There are then 3 tabs to choose from:

  • Manual - convert step by step and get detailed information.
  • Auto - select dataflows and optional convert steps and/or fix connections.
  • Log - simple log with conversion info etc.
 

PSDFConverter_Manual.PNG

For manual conversion, select a dataflow from the dropdown. A list of obsolete dataflow steps is listed to the left.

Select a step from the list, and the type and expression is shown right of the list. 

Click on [Try convert Dataflow Step] to convert it. The result is shown below. The original step may result in multiple converted steps (1 per module). Select result step from the 'Steps conv.to' dropdown. 

Click on [Save] to store the converted step in the solution database. 

You need to reopen the Solution to view result in Profitbase Studio.

 

 PSDFConverter_Auto.PNG

For automatic conversion select options 'Substitute obsolete commands' and/or 'Check command connection parameters'.

Check the dataflows in the list. Right-click to select/deselect all.

Click on [Convert selected dataflows] to check, convert and store dataflows.

You need to reopen the Solution to view result in Profitbase Studio.

 

PSDFConverter_Log.PNG

 The log contains a simple log of the operations done. Right-click to copy to clipboard or clear the list.

 

Notes

  • The tool does not fix scripts (SQL, SSIS and XML).
  • If username/password is used in connection, password is saved unencrypted in the configuration file.
Releases:
  • First version released 24. Nov. 2016.
  • Second release: 8. Dec. 2016 with fixes:
    • "Aggregate Sim / WebPlan DWH Fact' command fixed. If you already have converted dataflows, run this SQL to fix errors:
      UPDATE pbBatchStep SET Expression = REPLACE(SUBSTRING(Expression,1,DATALENGTH(Expression)), 'ModuleDimension', 'ModuleDefinition') WHERE  CLRType = 'ModuleCommand' AND Expression like '%Aggregation%'
  • Third release: 15. Dec. 2016:
    • Conversion from 'Load Source Data' to 'Reload Source Data' (mode All) added. Checkbox to skip/use conversion added for automatic update.