Getting started

Profitbase Studio 6 package consists of:

  • Profitbase Studio - client tool to design and operate the data warehouse.
  • Profitbase Studio Server - shared service to process the data warehouse and SSAS databases, and to keep track of users.

Profitbase Studio uses Microsoft SQL Server (2008-2017) for its databases that includes:

  • server (PBMSSrvDB) for command queues and server logs.
  • solutions (pbSol_) for solution definition.
  • data warehouses (pbDW_) for the solutions' output and logs for messages and operations.

Profitbase Studio can create SSAS databases and supports modes for Multidimensional (2008->) and Tabular (2012sp1->, 2016+ recommended).

Concepts

The main concept is to load, clean and transform dimension and fact data into usable packages for analysis and reporting. This data is stored in the data warehouse database.

Fact tables contains transactions with business facts (measures) and dimension ID's. Examples are sales order tables or financial accounting.

Dimension tables contains collections of items with descriptive and structural attributes. Examples are employees, products or customers.

Solution

A Profitbase Studio Solution contains all definitions needed to produce a Data Warehouse, and to create SSAS models (MOLAP or Tabular databases).

A solution has 3 main levels:

  • Data Source level that loads data from external or internal sources to tables in the data warehouse.
  • Dimension levels that builds dimensions (usually based on data source tables).
  • Modules that combines fact and dimensions into consistent packages that can be used by consumers (as SSAS databases, Profitbase InVision/Webplan, or MS Reporting Services).
In addition there are data flows, operation management and other settings.

Data Sources

A Data Source typically addresses a business system like an ERP, CRM, Supply Chain system where business information and transactions are stored. Examples are Microsoft Dynamic GP (Great Plains), JD Edwards One World or a custom Microsoft Access database. 

There are 2 types of Data Sources:

External  These loads data from external sources. Different source connections can be used like MS SQL, File, Excel, ODBC, IBM DB2, Oracle. Options to use MS SSIS. PS 6.2 ->: CSV, XML and JSON via MS Power Shell
Internal

Internal sources consume existing data within the data warehouse to combine data from different sources and/or to transform or add logic.

Library sources are created when data is imported through e.g. clipboard. Library sources stores offline data etc.

 

 

 

 

 

 

 

Each Data Source has one or more Data Source Table(s) to define the columns and transformations needed to load and store the data. There are diverse types of Data Source Tables:

Dimensions Source tables for wide dimensions.
Parent/Child Dimensions Source tables for Parent/Child dimensions.
Fact, Opening balance Source data for fact data (transactions)
Other  Source data like mappings etc.
Meta and Tag Tables with tag values or meta data describing the source.

 

 

 

 

 

Dimensions

Profitbase Studio supports following types of Dimensions:

Wide Wide dimensions use one or more Data Source Table. Columns from the source tables can be organized in attributes. Attributes can be used in hierarchies and have relations to other attributes.
Time Time dimensions are generated at the dimension level. Fiscal and Trade attributes can be added as options. Dimension can be extended with attributes from Data Source Tables.
Parent/Child  Parent/Child dimensions uses one or more sources to produce a hierarchical structure. It can be extended with attributes / properties from source columns.
Parent/Child Sub  Based on a Parent/Child dimension, a sub dimension can be created based on a sub set of items.
Versioned Dimension PS63 introduces Versioned Dimension. A snapshot can be taken of a Wide or P/C dimension at given time. This is e.g. useful for Slowly Changing dimensions, where one can have a copy of the dimension at given time.

 

 

 

 

 

 

 

 

 

 

Wide and Parent / Child dimensions supports user modifications. Time dimensions supports extensions.

Modules

Modules combines fact tables and dimensions. Optional dimensions are checked against fact tables to ensure consistency.

Profitbase Studio supports the following types of modules:

SSAS database
modules 

Multi dimensional Supports building data warehouse tables and apply settings and results to Multidimensional SSAS databases.
Tabular  Supports building data warehouse tables, and apply settings and result to tabular SSAS databases (SSAS 2012+).
DW output modules  Generic Builds data warehouse tables based on settings.
WebPlan Builds data warehouse tables and aggregates fact tables to month level for Profitbase WebPlan clients.
Sim 7 Builds data warehouse tables and aggregate fact tables to month level for Profitbase Sim 73 clients. Export to Sim is included via the Process dialog.

 

 

 

 

 

 

 

 

Name Definitions

A Name Definition is a definition for types and objects in Profitbase Studio and other Profitbase systems. It can e.g. be a column definition for a measure or dimension, or it can be a system name like language, module type etc.

Name Definitions consist of a unique ID (name like e.g. AccountID), a Name, a Description and a Data Type.
Items are grouped in categories for different use. Only items in some of these categories can be edited by the user, while others are for internal use.
All items Name and Description can be translated to different languages (although not all changes will be used). The language code itself has its own category and definition.
Each item can also be associated with a data type. This only applies if the item is used as a column in a data table, and when it is not a reserved system column.

Name Definitions are shared per solution database.

Templates

Templates are the starting point for creating solutions and extending them. Profitbase Studio supports local templates stored in the local solution database and online shared templates. Some templates are dependent on your license features.

Templates allow users to capture and store parts of, or a complete Solution, for future use. It is not only huge time savers when it comes to building a new solution but is a way of storing knowledge and know-how for use by anyone anytime in the future. It is also an effective way to do backups.