Upgrading from 5x to 6x versions

If you already have solution databases from earlier versions of Profitbase Studio (e.g.5.2), you will need to upgrade the solution and data warehouse databases.

Profitbase Studio 6x is a major update from 5x versions. This means that some functionality is discontinued, and that the resulting data warehouse tables and views are changed. After an upgrade, you will need to process dimensions and modules.

Upgrades can be done from PS5.2. For PS5.0 or PS5.1 versions, it is recommended that these are upgraded to PS5.2 first to ensure correct upgrade.

If you upgrade between 6.x versions, you may also need to update databases (solution and server). This is usually smaller updates that do not affect results in the data warehouse database.

Step 1 - before you upgrade

Check against the feature/what's new list for alternatives to functionality that are no longer supported.

Following features are no longer supported:

Dimension Keys - instead combine columns in Data Source mapping. The dimension key feature is replaced by the surrogate key functionality. From PS6.2 release 5, source mapping will contain replacement for dimension keys (e.g. DepartmentID = DepartmentID + '.C' + CompanyID).
Multi-Column Primary Keys on Parent/Child and Dimension Tables. Instead use of CompanyID as part of Primary Key must be rewritten to make for a unique single-column Primary Key (i.e. concatenate CompanyID and ItemID with an infix in between). This type of natural key can in most cases benefit from being replaced with a surrogate key.
Parent/Child source tables will not automatically have UnaryOperator or MemberFormula columns if this not included in source mappings. This may affect custom SQL expressions in Data Warehouse source tables.
Data Warehouse processing - this is replaced by improved Data Source functionality and script support.
Level based Parent/Child Dimension Trees (Level Pivot, Level Dimension, P/C Level) - instead use wide or P/C to wide.
Template system has been replaced and there is no support for the old template model.
Check data types and Name Definitions for reserved types/names - click here for list. An upgrade may change data types and names so take a snapshot and correct after upgrade.

Profitbase Studio 6 allows for new naming of tables and views in the data warehouse database (described here). A normal solution upgrade will keep naming scheme from Profitbase Studio 5. To utilize the new naming scheme, and whenever you want to do a total workover to rebuild your solution, you can do the following:

Run a normal Upgrade as described below.
Create a new empty solution.
Copy from the upgraded solution to the new solution and use the Properties dialog on the objects to ensure proper naming in the new solution.

Warning: The sequence above is a much bigger effort than just running the normal solution Upgrade.

Note 1: Always take a backup of solution database (and data warehouse database if possible), before upgrading.

Note 2: You can install Profitbase Studio 6.x Client and server on same machine as Profitbase Studio 5.2 (in parallel).

Step 2 - upgrade the solution database

To start an upgrade, log on and select either [Upgrade Solution Database] from the Navigators Start section, or from the main menu select Tools | Database Tasks | Upgrade Solution Database.

From the dialog, select/check the database, and click on [Upgrade Database(s)]. You can expand the log by clicking on the label [View Log Details].

Click on [Close] when upgrade is completed.

Step 3 - upgrade the solution content and data warehouse database

When you open this solution in Profitbase Studio the first time after an upgrade from 5.2x versions, the following dialog will show:

You can select from a list of options, on what you want to keep/upgrade:

Update local items; (or internal items), is always selected. It will preserve modifications, pasted content in library sources etc.
Update data source tables; if checked, the data source tables will be kept, but some system columns will be removed from table and the view on top of it (with these columns), will be recreated. If you do not select this option, the tables must be recreated (do a process/load from UI or dataflow).
Remove obsolete tables/views; if checked, all unused or those that are unusable due to changes, will be removed.
Reload Tag values; if check, tag values from sources will be reloaded to new format.
Add time dimensions to dataflow; If checked the time dimensions that in this version is moved from module to dimension layer, will be added to existing dimension processing steps in existing dataflows.

It is recommended that that you keep these options checked.

Click on [Next] to continue to next page from where you do the upgrade.

Select [Start upgrade process] and the solution content and data warehouse will be upgraded.

Check the log:

for errors and warnings.
to get an understanding what is done.

To fix errors you will need to go to the affected item and do changes there. If e.g. a Data Source Table failed. Go to the editing part and check settings and then reload data to update it.

Step 4 - Fix, validate and process

When the the steps above are completed and the solution is opened, take the time to validate and process.

Some issues has to be fixed manually:

Module Group /SSAS database:

Name of the module group is different from the physical SSAS database name. Check both names.
Check that the data warehouse callback connection is working (the connection SSAS uses for reading data from the dw). Adjust timeout.
Turn on member scan (and optionally reduce). This cannot be done on time dimensions. Do not turn on reduce scan for TimeFunction, KFR, Reports or other dimensions based on a calculated fact dimension in a measure group.
If the SSAS reports query timeouts when processing, adjust the query timeout setting under Advanced SSAS settings section.

Module sub groups / Cubes:

PS 5x added a 'do not report error' thinking. This is changed in PS6x so use 'Advanced Cube Settings' to set your preferred SSAS processing settings.

Module Definition / Measure Group:

Before filters on TransDate was set automatically. Now these are set in the filter section for Fact Source tables. If there is no TransDate in the source, remove the filter 'TransDate >= convert(datetime,<>,112) AND TransDate>,112)' or change TransDate to another date/datetime column.
Also, if there is no date field, remove the auto added time dimension. If the time dimension is not connected to TransDate, You may need to add it / map it manually.
If there is more than one date field connected to the time dimension, you must manually change the Dimension Usage for other date fields than TransDate to be a Role Playing Dimension. E.g. if there is TransDate and OrderDate, connect TransDate to a time dimension, and then connect OrderDate to same time dimension but also check 'Use this as cube dimension with different name' and enter a usable name. The same shared time dimension in the SSAS database is used, but a new instance is created in the cube. Otherwise the aggregation in measure groups will fail.
Check partitions settings under 'Storage and Partition Settings' section. The 'Partition Definition' for SSAS must be set to specific partition or 'None'. If this is empty/blank, select None.
Business Logic for sets: Be aware that previous Version implemented a Dynamic set as static set in the SSAS DB (defined as "CREATE SET..." in SSAS Cube Calculations instead of "CREATE DYNAMIC SET ..."). Hence you should consider changing from Dynamic (default) to Static to maintain the same behavior. (This is due to a defect in previous Studio versions (52x)).
In 5.x versions, linked dimensions were added behind the scenes. PS6 supports all SSAS possibilities, but You have to define dimension usage yourself.

Also check that

data flows contain the right steps and content. Replace obsolete data flow steps marked with red/pink.
data flow steps that contain processing of multiple data source tables are doing so in the preferred order. Check details of the following log item: "The data source tables will be transferred in the following order (see details).. "
data sources have correct connections that can be opened. Also check/set timeout.
data source tables can be browsed, optionally reload the content. Check columns, keys, source expression and filters.
time dimensions have a usable name (since moved from SSAS/OLAP module) and process them (from UI or data flow). Some column names in the generated time dimension table is changed from previous version. If you have custom code adjusting the time Dimension table you may have to adjust to the new columns names (e.g. Day_Txt_En is changed to Day_Name_En, Fiscal from FY_nn to FI_nn etc.).
parent/child dimensions have the correct definition, optionally add level names and process them.
parent/child sub dimensions have the user selections, and process to create them in dw.
modules have the right fact sources, dimensions and business logic. Process the data warehouse result and optionally the SSAS database.
solution settings with time span, data warehouse connection with timeout etc.
Data Types and Name Definitions. PS6 will override types and name definitions. Check reserved types/names here.

Note - check/add schedules for running data flows. SQL Agent is running scheduled jobs, and Profitbase Studio does not keep track of these. Depending on how the solution is restored and upgraded, you may have to add the schedules again.