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.
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 |