Profitbase Studio 2023

From 2023 we change versioning to reflect year (and optional quarter).

This is based on, and replaces version 6. New version includes:

• native support for SQL server 2022 (sql user login recomended).
• upgrade usage of MS Azure features.

Profitbase Studio 6

Profitbase Studio 6 replaces Profitbase Studio 5.2x versions.

Profitbase Studio 6 is an efficient and transparent tool to produce consistent Data Warehouses, Data Marts and MS SSAS databases with Azure support.

Available main versions are:

• Profitbase Studio 6.5 - released December 6. 2019
  • Latest is 6.5.6. from February 2021
• Profitbase Studio 6.4 - released August 24, 2018.
• Profitbase Studio 6.3 - released November 22, 2017.
• Profitbase Studio 6.2 - released October 27, 2016.
• Profitbase Studio 6.1 - released July 4, 2016.
• Profitbase Studio 6.0 - released November 11, 2015.

Profitbase Studio 6.5

New features include:

• Microsoft SQL 2019 support.
  • Native client support (SMO, SSIS, Analysis).
  • SSAS Tabular models with compatibility level 1500.
• Support for SSAS Tabular Calculation Group Tables in models. 
• Added option to paste more than one measure group to SSAS Tabular model.
• Module Processing dialog: Resize and splitters added.
• "Create DW database" function available from Target DW Connection page.
• Auto Update for data warehouse fact tables.
• Improved staging table implementation (e.g. result object is table name without suffix).
• Full PoSh script support for Excel/MS Access.
• Support Space as Column Delimiter in Csv files.

This release has the following fixes:

• Auto Update rewritten for better performance and corrections.
• SSAS Multidimensional: Last processed date from partitions is used instead of the measure group itself (default partition used if no other partitions settings are used). This will ensure correct date comparison checks in Auto Process.
• SSAS Multidimensional: Role playing dimension accepts same column binding on different instances of same dimensions.
• Dimension and SSAS processing: Adjustments on case sensitivity on Attribute and Column names (e.g. having FontFlags in name definitions and getting Fontflags from Templates).
• Dimensions Modification tables: Correction of datatypes changed in name definitions, also for the key.
• P/C Dimensions: Validation of circular reference changed/fixed.
• Wide Dimensions: Translation columns are now formatted as specified for Name.
• Dimension Templates: Copy of modification tables with different resulting column names.
• Solution Templates: Merge of a solution template to existing template is fixed, along with reference replacement dialog.
• Server Login: Set to top to ensure not hidden behind the startup splash panel.
• Data Flow: Fix Internal Data Source Table Ordering.
• Data Source: Custom PoSh script for MSSQL Linked Server and SSIS.
• Data Source: Oracle PoSh Force Uniqueness.
• Data Source: Staging Mode available in "New Data Source Table" wizard.
• Data Source: Staging Tables; recreation of PK's and indexes on Save.
• Data Source: Improved clustered columnstore index support; avoid recreating index on every reload + allow for creating primary keys and indexes on columnstore tables.
• Data Source: RELOADSOURCEFILTER tag supported in PoSh scripts.
• Data Source: Auto Update Filter support.
• Data Source: Fix multifile csv import failing with error of "table already created".
• Data Source: Support parallel execution when multiple tag values for Csv PoSh.
• Data Source: Add Text Qualifier to file name ensuring support for column delimiters in file name (CSV PoSh).

Notes

• No need for database upgrade, same as for PS6.4.
• New %appdata% folder for configuration files (e.g. %appdata%\Roaming\Profitbase\PBMS\6.5.0.0).
• Microsoft .Net 4.6.2 required.

Profitbase Studio 6.4

New features include:

• Azure support:
  • Solution and data warehouse databases in Azure.
  • Create tabular Analysis models.
  • Import/export of Azure data (SQL Databases, Blob Storage, Data Lake Store).
• Improved Tabular model support:
  • Conversion of Multi Dimensional SSAS Measure Groups to Tabular SSAS Models.
  • Better default handling when creating new tables.
• Export to:
  • Table in Azure SQL Database.
  • Csv file.
  • Csv file in Azure Data Lake Store.
  • Csv file in Azure Blob Storage.
• Data source table modes:
  • Staging
  • Linked data
• PowerShell as connection method:
  • Integrated for MSSQL and Oracle sources
    • no need for installing SSIS or oracle client software. 
    • no need to setup linked server or TNS name beforehand.
  • Explicit for all data source tables
    • improved flexibility.
• Excel Data Source support for:
  • Import of multiple worksheets into one table.
  • Utilizing worksheet row numbers.
• Support for indexes and primary keys on clustered columnstore index tables.
• Solution placeholder tag support in data source tables.
• Improved Slowly Changing Dimension Type 2 support.
• New dataflow steps to override status.

Profibase Studio 6.3

New features include:

• Microsoft SQL 2017 support including:
  • Native client support (SMO, SSIS, Analysis).
  • Tabular models with compatibility level 1400.
• Better Surrogate Key and Slowly Changing support.
• Versioned Dimension support - take a static copy with given conditions.
• Better Concurrency support (notifications, edit check out, refresh).
• Support of multiple data warehouse databases. Data Sources or Modules can be stored in different dw databases,
• Allow for grouping Data Sources in folders.
• Coloring in script and expression editors.
• Scripts embedded into other scripts.
• New search dialog with navigation support.
• Solution mode: Test vs Production with todo list.
• Logging to Microsoft Application Insights (server + dataflow step).
• General bug fixes and improvements.

Profitbase Studio 6.2

New features include:

• Microsoft SQL 2016 support including:
  • Native client support (SMO, SSIS, Analysis).
  • Memory-optimized tables.
  • Tabular models with compatibility level 1200 including new Perspective editor.
• Microsoft PowerShell script support.
• New file based Data Source types with support for XML, JSON or CSV via PowerShell.
• General bug fixes and improvements - check the release information.

Profitbase Studio 6.1

New features include:

• Auto Update functionality - only process what is needed.
• Incremental Data Source tables that can be used directly in SSAS with 'Add' processing.
• Built-in Excel Data Source driver. Excel import not requiring Integration Services and architecture sensitive ACE/Jet Providers.
• Improved reload mechanisms. (i.e. Enhanced Force Uniqueness, Maintain RecordID, included index columns and Skip Distinct Scan per DimensionID). 
• Edit all Object Naming from Solution Settings.
• Create Wide dimensions based on an existing Time Dimension to manually change attributes, relations and hierarchies.
• Specify SSAS dimension type (PC and Wide) and attribute types (Wide).
• Export from Sim-modules to Profitbase Sim 73 databases.
• Improved multi user environment.
• General bug fixes and improvements to 6.0.

Profitbase Studio 6.0

New features include:

• Copy/Paste (copy/paste items like modules, dimensions or data sources both within and across solutions).
• Time and Sub Dimensions as fully functional Dimensions.
• T-SQL and XAML script support.
• User folders.
• Access control (within Studio) and Check Out/In editing.
• Surrogate/distinct keys (includes usage overview and analyzer tool for dimension keys).
• Slowly changing dimensions (type 2 historical attributes).
• SSAS Actions (in Multidimensional OLAP).
• SSAS Tabular model.
• Support for SQL Server 2014 (in addition to 2008 and 2012).

Improved features include:

• Improvements Module:
  • More effective data warehouse operations including member/reduce scan and fact handling.
  • Improved module dimension usage functionality including support for more than one time dimension, role playing dimension, decide how dimensions are linked and select item used in drill-through dimension.
  • More module storage and SSAS options. Improved partitioning support including uses of sources directly (direct query) and a movable 'current' partition.
  • Advanced SSAS settings including error handling.
• Improvements Dimension:
  • Time Dimensions have been moved from Module layer to Dimension layer and are now being handled like another dimension. Same Time Dimension can be used in one or more modules.
  • Time Dimensions can be extended / joined with other source tables. 
  • Time Dimension column names and resulting attributes has been normalized - e.g. name columns: nn_txt -> nn_name, fiscal prefix: FYnnn -> FI_nnn, trade prefix: TRnnn -> TR_nnn
  • Date format and translations in Time Dimensions allows for custom build formats (according to c# date format string).
  • Sub-dimensions are now being generated as fully functional dimensions.
  • Extended Wide dimension attribute editing and modification editing (as for Parent/Child dimensions).
  • Wide dimensions can be produced from Parent/Child dimensions (or sub dimensions).
  • Meta data, that describes the dimensions, is added for consumers.
  • Support for Unknown member handling.
• Improvements Data Sources and Data Source Tables:
  • A more unified way of handling source tables. Fact, dimension, tag and meta data tables are defined and processed the same way.
  • Editing and Archive functionality.
  • Customized transfer of data (i.e. skip datatype cast, use script extensions for loading source and/or data warehouse).
  • New Source Metadata system. Now metadata tables are placed in data warehouse database instead of solution database.
  • New Source tag system. Now tag value tables are placed in data warehouse database instead of solution database.
  • Page Compression support.
  • Clustered and nonclustered columnstore index support.
  • Improved reload mechanisms. (i.e. Force Uniqueness, Abort if rows decrease, TABLENAME and RELOADVALUE tags).
  • Support for custom index definitions. (Single column indexes are the new default, but Studio also allows maintaining primary keys and indexes via script extensions).
  • Drag and drop support for joining and unioning data warehouse objects (i.e. dim tree tables, module views).
  • Support for CTE (WITH) expression and DECLARE statements in source query.
  • Support for multiple instances of the SQL Query Designer.
  • Support for multiple instances of the Process dialog.
  • Context sensitive preview of data via shortcut key (F5 key).
  • Automap source columns functionality.
• Better status information:
  • Content list contains a status updated while processed.
  • Operation log in Data Warehouse database contains log of process operations applied to an item (e.g. Dimension or Data Source Table).
  • Message log in Data Warehouse database contains information, error and warning messages collected both on client and server.
  • Server log will now only contain what commands that are processed and other server core messages. 
  • Active users on same server are listed in clients.
• UI improvements:
  • A more unified User Interface.
  • Common operations are moved to navigation tree (new, delete, browse, process).
  • Rich Text support in comments (user can add formatted text w/images).
  • Easier administration of partitions (i.e. Split into many, Merge into One).
  • Support for Shortcut Keys.
  • Stop/Abort Processing button.
• Data Warehouse improvements:
  • Better and faster DW table model w/schema support.
  • Better control of data types, table names, schema etc.
  • DW upgrade dialog to help transition from 5x to 6.
  • Initial size and grow size options on create.
  • Script Extensions for SQL and XMLA. Map scripts to process before, after or instead of built in processing steps.
• New template model:
  • Content based templates.
  • Templates can consist of complete solutions or only e.g. a single dimension.
  • Support for data set distribution (e.g. data source table data etc.).

Discontinued features:

• Dimension Keys - combine columns/tags in Data Source mapping.
• Multi-Column Primary Keys on Parent/Child and Dimension Tables. 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).
• 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 - replaced by improved Data Source functionality and script support.
• Level based Parent/Child Dimension Trees (Level Pivot, Level Dimension, P/C Level)- create a new wide dimension or use 'P/C to wide'.
• Template level / separate template editing.
• SQL Server 2005 is no longer supported.
• All existing Data Flow Activities for Module and OLAP are now obsolete. The "Module command(s)" and "SSAS command(s)" data flow activities now cover them all. For source data "Load Tag Values" and "Load Source Data" are now obsolete. The "Reload Source Data" data flow activity covers them both. Click here for more on upgrade tool for dataflows.

Internal changes:

• DW processing and Meta model has a clearer layer separation; Data Source, Dimension and Module w/consumers like SSAS.
• Replacement of Meta Data layer.
• Incremental Reload uses MERGE statement instead of separate DELETE and INSERT INTO statements.
• Use of generic meta model w/content list++.
• A major code cleanup/reduction/simplification - refactoring.
• Same base model as for InVision.
• Business Logic for Dynamic Sets in SSAS DB: In previous versions, dynamic sets were implemented as static sets in the SSAS DB. This is corrected in Studio 6.