Integrating with Profitbase Studio 6

Using the database

The solution database contains the following core tables that can be used to integrate or extract information about solutions and its data warehouse content:

pbSolution

This table contains solutions with link to connections etc.

pbSolutionContentList

This contains a list of content in a solution. It has columns for name, type, status etc. This is the main table for id/name for all content.

pbSolutionContentMap

This contains additional references between content (that are not in the hierarchical content list).

pbSolutionDwTableList

This contains the tables and views created in the data warehouse in reference to content.

pbSolutionDwTableColumnList

Not used in PS6.0. This will contain columns used in created DW tables /views. For now use the name definition table.

pbConnectionIndex

This contains the connection information (e.g. to the data warehouse).

pbNameDefinitions

This contains names of columns, types etc. used by the solution and columns in the data warehouse.

pbDataTypes

This contains data type definitions used by Name Definitions

 

 

 

 

 

 

 

 

 

 

 

 

 

The solution table contains the different solutions that are available.  From the solution id, you can use the content list to get the all the main objects. The content table has a hierarchical list of all content with name, description, type and status. When you find the content  you want, you can use the content id to find what output tables/views that are created in the data warehouse table list.  If more than one table is used, the IsResultObject can be used to pick the ObjectName (table/view-name). Use the solution table to get the connection id and then use the connection table to locate the data warehouse database.

To get the dimensions used in a module definition the content map can be used to locate these.

Integrate_Studio_Overview.PNG

 pbSolutionContentList table:

Column

Description

ContentID

Unique string ID (per solution db)

AltContentID

Unique numeric id..

ParentContentID

Ref. to the parent id (to get an hierarchical list).

SolutionID

Ref. to the solution id.

ContentType

The type of content (see below)

SubType

The sub type (see below).

Name

The name string

Description

The description string

Comments

The comment string. This can contain rich text formatting.

BaseObjectName

The base name used when tables/views are created in the data warehouse. Additional prefixes/suffixes may be added, e.g.vw_ prefix for views.

MetaTag

Contains additional meta tags to describe the objects (from 5x these was used to describe the types of data, e.g. Finans, Account etc.).

DateFrom, DateTo

What time span the data are limited to (used for solutions, modules and time dimensions).

Rows

Number of rows in the dw result (where possible).

LastProcessStarted

When last processing started.

LastProcessEnded

When last processing ended.

LastProcessedBy

Who did the processing (user id)

LastProcessType

Last processing type

LastProcessStatus

Last result

LastDataChange

When data was last changed manually.

 

Content type and sub content types:

Content type

Sub Type

Description

contentType_Solution

 

Solution Content Type

contentType_UserFolder

 

User Defined Folder

 

 

 

 

contentType_OlapDbFolder

 

MS SSAS Output Modules

contentType_OLAPDatabase

 

SSAS/OLAP Module Group Content Type

TabularSSASModule

Tabular SSAS Module

OLAPModule

Multidimensional OLAP/SSAS Module

contentType_DWOutputFolder

 

Data Warehouse Output Modules

contentType_DWOutput

 

Data Warehouse Output Content Type (WebPlan, Sim, Generic modules)

GenericModule

Generic Output Type (e.g.InVision)

PlanningModule

WebPlan Output Type - export package w/aggregation

Sim7Module

Sim7 Output Type - export package w/aggregation

contentType_ModuleSubGroup

 

Module Sub Group Content Type (Cube)

contentType_ModuleDefinition

 

Module Definition Content Type

contentType_ModuleDimLink

 

Dimensions used by a Module (shared dimensions in a ModuleGroup)

contentType_ModuleDataSourceTable

 

Data Source Table /Fact used in ModuleDefinition Content Type

contentType_ModuleAction

 

SSAS Action Content Type (used in cubes)

contentType_OLAPConsumer

 

SSAS Database Consumer Content Type

contentType_CubeConsumer

 

SSAS Cube Consumer Content Type

contentType_MeasureGroupConsumer

 

SSAS Measure Group Consumer Content Type

 

contentType_DimManagementFolder

 

Dimension Management

contentType_DimTreeFolder

 

Dimensions

contentType_DimTree

 

Dimension Content Type

contentType_SubDimTree

 

Sub Dimension Content Type

contentType_TimeDimFolder

 

Time Dimensions

contentType_TimeDim

 

Time Dimension Content Type

contentType_SharedCalendar

 

Shared Calendar for holidays etc

 

contentType_DataSourceFolder

 

Data Sources

contentType_DataSource

 

Data Source Content Type

DST_EXTERNAL

External Data Source Type

DST_INTERNAL

Internal Data Source Type

DST_LIBRARY

Internal Library Data Source Type

contentType_DataSourceTable

 

Data Source Table Content Type

Fact

Fact Table

FactOB

Fact Table for Opening Balance

FieldMetaDataSource

Field Meta Data

OtherTable

Other Table (mapping etc.)

PCDimSource

Parent/Child Dimension Table

TableMetaDataSource

Table Meta Data

TagValuesSource

Tag Values

contentType_DataSourceTable_Distinct

 

Distinct Dimension Items per Data Source Table Content Type

 

contentType_ScriptFolder

 

Script Extension Folder Content Type

contentType_ScriptExtension

 

Script Extension Content Type

 

contentType_DataFlowFolder

 

Data Flows

contentType_DataFlow

 

Data Flow Content Type

contentType_ScheduleManagement

 

Schedule and Status Management

contentType_LogViews

 

Operation and Message Log Views

 

contentType_SolutionConfigFolder

 

Solution Configuration Content Type

contentType_SolutionSettings

 

Solution Settings

contentType_SolutionPartitions

 

Solution Partitions

contentType_AccessControl

 

Access Control

 

contentType_DimKeys

 

Dimension Key Settings

contentType_DistinctKeyFolder

 

Shared Distinct Key Table Folder

contentType_DistinctKey

 

Shared Distinct Key Table

contentType_SurrogateKeyFolder

 

Surrogate Key Folder

contentType_SurrogateKey

 

Surrogate Key Definition

contentType_PlaceholderTagTableFolder

 

Solution Placeholder Tag Table Folder

contentType_PlaceholderTagTable

 

Solution Placeholder Tag Table Content Item

 

pbSolutionDwTableList table:

Column

Description

TableID

Unique table id

ContentID

Content id (from content list)

SolutionID

The solution id

TableType

Table type (see below)

ConnectionID

Connection id (ref. pbConnectionIndex)

ObjectName

The table/view name in the data warehouse database.

ObjectType

If table, view etc.

IsResultObject

If it’s the main/result table/view (when more than one result linked to each content.

DateFrom, DateTo

What time span the data are limited to (used for solutions, modules and time dimensions).

Rows

Number of rows in the dw result (where possible).

LastProcessStarted

When last processing started.

LastProcessEnded

When last processing ended.

LastProcessedBy

Who did the processing (user id)

LastProcessType

Last processing type

LastProcessStatus

Last result

LastDataChange

When data was last changed manually.

MetaConfig

Additional meta data xml (for dimensions only in PS6.0)

 

Table types (bold indicates main results often used outside Profitbase Studio):

Type

Description

tableType_DataSource_DKView

View on Data Source Table (with full info)

tableType_DataSource

Table loaded from a source

tableType_DataSource_Distinct

Internal  table per dimensionid per datasource table

tableType_DataSource_MetaFld

Internal Data Source Meta Field Info table

tableType_DataSource_MetaTbl

Internal Data Source Meta Table Info table

tableType_DataSource_Staging

Data Source Staging Temp Table

tableType_DataSource_Tag

Internal Data Source Tag table

tableType_DataSource_Temp

Data Source Temp Table

tableType_DataSource_UpdatesAndInserts

Data Source Updates and Inserts Temp Table

 

tableType_DimTree_View

Complete DimTree View with modificatoins

tableType_DimTree_Modifications

Internal DimTree Modifications (new/changed items)

tableType_DimTree_PCWide

DimTree Parent/Child to Wide

tableType_DimTree_Raw

Internal generated DimTree table without modifications

tableType_SubDimTree

Generated Sub DimTree Table

tableType_SubDimTree_Map

Internal Sub Dim Tree Mapping Table

tableType_SubDimTree_PCWide

Sub DimTree Parent/Child to Wide

tableType_SubDimTree_UserSelection

Internal Sub DimTree User Item Selection table

tableType_WideDim_View

Result WideDim View

tableType_WideDim

Internal WideDim table if modifications or >1 source

tableType_WideDim_Modifications

Internal WideDim Modification table

tableType_WideDim_SourceView

Internal Source view for WideDim

tableType_WideSubDim_UserSelection

Internal WideDim User Item Selection table

tableType_WideSubDim

Internal WideDim User Item Selection table

tableType_TimeDim

Generated Time Dimension table

tableType_Solution_Holiday

Holiday table per solution

 

tableType_ModuleDef_View

Reult ModuleDefinition Fact View

tableType_ModuleGroup_DimTree

Result Module DimTree / Sub DimTree Table

tableType_ModuleGroup_TimeDim

Result Module TimeDim View

tableType_ModuleGroup_WideDim

Internal Optional Module WideDim Table

tableType_ModuleDef_Aggregated

Aggregated ModuleDefinition Table/View based on Consolidated view

tableType_ModuleDef_BLView

Fact view for ModuleDefinitions inlcuding BL columns per DataSource Table

tableType_ModuleDef_Consolidated

Fact view for ModuleDefinitions based on all BL views

 

tableType_DistinctKey

Contains Distinct Dimension Item ID's

tableType_DistinctKey_Statistic

Contains Distinct Key Table Statistics

tableType_SurrogateKey

Contains Surrogate Key to Item map

tableType_SurrogateKey_Modifications

Contains Surrogate Key Modifications

tableType_SurrogateKey_Statistic

Contains Surrogate Key Statistics

tableType_PlaceholderTag

Contains Placeholder Tag Values

tableType_PlaceholderTagTable

Solution Placeholder Tag Value Table

tableType_Temp

Temporary table/view

 

pbConnectionIndex table:

Column

Description

ConnectionID

Unique connection id

SolutionID

The solution id

ConnectionType

Type as MSSQL, ORACLE, ODBC etc.

Name

Name

ConnectionTypeInfo

Usage type: Data Warehouse, Data Source, OLAP

ServerName

Name of database server (w/optional instance)

DatabaseName

Name of database

DefaultSchema

Schema name (e.g. dbo)

Username

User name.

Password_

Encrypted password..

DefaultTimeout

Timeout

ConnectionDetailID

Link to pbConnectionDetail table

ConnectionFileDetailID

Link to pbConnectionFileDetail table