This article details the structure of the Reporting Database for Meisterplan which is accessed when generating internal or external reports.
Entity Relationship Diagram (ERD)
The following diagram shows the general structure of Meisterplan's reporting database (right-click and open in new tab for a zoomable, full-size version). Please note that the diagram doesn't contain every reporting view:
Tables
allocation_slices_monthly
This table contains information about how many hours a resource is allocated on a project per month.
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id). |
| project_code |
character varying(2048) |
Link to Project (reporting.projects.code / reporting.scenarioprojects.projectid) |
| slice_date |
date |
First day of a month the allocation occurs |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| project_role |
character varying(2048) |
Link to the role (reporting.resources.code) |
| allocation |
double |
Allocation in hours |
allocation_slices_weekly
This table contains information about how many hours a resource is allocated on a project per week.
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id). |
| project_code |
character varying(2048) |
Link to Project (reporting.projects.code / reporting.scenarioprojects.projectid) |
| slice_date |
date |
First day of a week (Monday) the allocation occurs |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| project_role |
character varying(2048) |
Link to the role (reporting.resources.code) |
| allocation |
double |
Allocation in hours |
capacity_slices_monthly
This table contains information about how many hours a resource is available per month.
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id). |
| slice_date |
date |
First day of a month the capacity occurs |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| capacity |
double |
Capacity in hours |
capacity_slices_weekly
This table contains information about how many hours a resource is available per week.
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id). |
| slice_date |
date |
First day of a week the capacity occurs |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| capacity |
double |
Capacity in hours |
base_calendar_capacity_slices_monthly
This table gives information about work time of a full time resource in a specific month. It can be used to calculate the FTE.
| Column |
Type |
Description |
| slice_date |
date |
First day of a month the capacity occurs |
| capacity |
double |
Capacity in hours |
base_calendar_capacity_slices_weekly
This table gives information about the work time of a full time resource in a specific week. It can be used to calculate the FTE.
| Column |
Type |
Description |
| slice_date |
date |
First day of a month the capacity occurs |
| capacity |
double |
Capacity in hours |
obs
This table contains data of the flat representation of each organizational unit (level 1 to level10)
| Column |
Type |
Description |
| fqn |
character varying(4096) |
Unique code of the obsunit (full path of the codes concatenated by "/") |
| type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| Level1 |
character varying(2048) |
Name of the unit on level 1 |
| Level2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| Level10 |
character varying(2048) |
Name of the unit on level 10 |
| obsid |
integer |
Unique id of the obs unit |
portfolio_project
This table contains the link between projects and portfolios to determine which portfolio contains which projects
| Column |
Type |
Description |
| portfolio_id |
integer |
Link to the portfolio (table portfolio.internalid) |
| project_id |
character varying(4096) |
Link to the project (reporting.projects.code) |
portfolio_resource
This table contains the link between resources and portfolios to determine which portfolio contains which resources
| Column |
Type |
Description |
| portfolio_id |
integer |
Link to the portfolio (table portfolio.internalid) |
| resource_id |
character varying(4096) |
Link to the resource (reporting.resources.code) |
Views
businessgoal_overview
The view contains data about project, allocation, cost, benefit, etc. related to business goals per month.
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| businessgoal |
character varying(2048) |
Name of the business goal |
| businessgoal_color |
character varying(2048) |
Color of the business goal |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| startdate |
date |
Project start date |
| finishdate |
date |
Project finish date |
| slice_date |
date |
First day of a month |
| alllocation |
double |
Allocation in hours |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| benefit |
double |
Expected benefit in this month for this project |
| capex |
double |
CAPEX in this month for this project |
| opex |
double |
OPEX in this month for this project |
| prj_count |
integer |
Number of projects for this business goal in this month |
| scenario_name |
text |
Name of the scenario |
businessgoal_project
This view contains the link between projects and business goals. One project can only be linked to one business goal.
| Column |
Type |
Description |
| businessgoal_code |
character varying(2048) |
Link to the portfolio (table portfolio.internalid) |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| businessgaol_title |
character varying(2048) |
Name of the business goal |
| businessgoal_color |
character varying(2048) |
Color of the business goal |
generalprojectcustomfields
This view contains the values of the custom fields of the projects. The view gets automatically extended with new custom fields as soon as they are created.
| Column |
Type |
Description |
| projectid |
character varying(2048) |
Link to the project (reporting.projects.code) |
| cust_customfieldname |
data type of custom field |
Value of custom field |
lookup
This view contains the details of the values of the custom lookup fields.
| Column |
Type |
Description |
| code |
character varying(2048) |
code of the column of the custom field |
| value_code |
character varying(2048) |
id of the lookup value |
| value |
character varying(2048) |
value of the lookup item |
| description |
text |
description of the lookup value |
| color |
character(6) |
color of the lookup value (hex code) |
milestones
This view contains milestone data by scenario.
| Column |
Type |
Description |
| milestoneid |
character varying(2048) |
Unique ID of the milestone |
| title |
character varying(2048) |
Title of the milestone |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| milestonetype |
character varying(2048) |
Project phase |
obs_project
This view contains the link between obs unit and project.
| Column |
Type |
Description |
| obs_fqn |
character varying(4096) |
Unique code of the obsunit (full path of the codes concatenated by "/") |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
obs_resource
This view contains the link between obs unit and resource.
| Column |
Type |
Description |
| obs_fqn |
character varying(4096) |
Unique code of the obsunit (full path of the codes concatenated by "/") |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
portfolio_budget
| Column |
Type |
Description |
| portfolio_id |
integer |
Link to the portfolio (table portfolio.internalid) |
| slice_date |
date |
First day of a month
|
| amount_key |
character varying(255) |
Finance type (CAPEX, OPEX or BENEFIT) |
| amount |
double |
Respective amount regarding the finance type |
project_obs_levels
| Column |
Type |
Description |
| type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| fqn |
character varying(4096) |
Unique code of the obsunit (full path of the codes concatenated by "/")
|
| unit_id |
integer |
Link to the obs unit (reporting.obs.obsid) |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| level_1 |
character varying(2048) |
Name of the unit on level 1 |
| level_2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| level_10 |
character varying(2048) |
Name of the unit on level 10 |
project_resource_alloc_monthly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario
|
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| primaryrole_name |
character varying(2048) |
Name of the primary role |
| projectrole |
character varying(2048) |
Link to the project role (reporting.resources.code) |
| projectrole_name |
character varying(2048) |
Name of the project role |
| slice_date |
date |
First day of a month |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| project_name |
character varying(2048) |
Name of the project |
project_resource_alloc_obs_monthly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario
|
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| project_name |
character varying(2048) |
Name of the project |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| primaryrole_name |
character varying(2048) |
Name of the primary role |
| projectrole |
character varying(2048) |
Link to the project role (reporting.resources.code) |
| projectrole_name |
character varying(2048) |
Name of the project role |
| slice_date |
date |
First day of a month |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| obs_type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| obs_level_1 |
character varying(2048) |
Name of the unit on level 1 |
| obs_level_2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| obs_level_10 |
character varying(2048) |
Name of the unit on level 10 |
project_resource_alloc_obs_weekly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario
|
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| project_name |
character varying(2048) |
Name of the project |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| primaryrole_name |
character varying(2048) |
Name of the primary role |
| projectrole |
character varying(2048) |
Link to the project role (reporting.resources.code) |
| projectrole_name |
character varying(2048) |
Name of the project role |
| slice_date |
date |
First day of the week (Monday) |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| obs_type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| obs_level_1 |
character varying(2048) |
Name of the unit on level 1 |
| obs_level_2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| obs_level_10 |
character varying(2048) |
Name of the unit on level 10 |
project_resource_alloc_weekly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario
|
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| project_code |
character varying(2048) |
Link to the project (reporting.projects.code) |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| primaryrole_name |
character varying(2048) |
Name of the primary role |
| projectrole |
character varying(2048) |
Link to the project role (reporting.resources.code) |
| projectrole_name |
character varying(2048) |
Name of the project role |
| slice_date |
date |
First day of the week (Monday) |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| project_name |
character varying(2048) |
Name of the project |
project_resource_slicedata_monthly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| resource_code |
character varying |
Link to the resource (reporting.resources.code) |
| project_code |
character varying |
Link to the project (reporting.projects.code) |
| project_role |
character varying |
Link to the role (reporting.resources.code) |
| slice_date |
timestamp without time zone |
First day of a month |
| allocationhours |
double |
Allocation in hours |
| capacityhours |
double |
Capacity in hours |
| financetype |
character varying |
CAPEX, OPEX or BENEFIT |
| cost |
double |
Respective amount regarding the finance type |
project_resource_slicedata_weekly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| resource_code |
character varying |
Link to the resource (reporting.resources.code) |
| project_code |
character varying |
Link to the project (reporting.projects.code) |
| project_role |
character varying |
Link to the role (reporting.resources.code) |
| slice_date |
timestamp without time zone |
First day of the week (Monday) |
| allocationhours |
double |
Allocation in hours |
| capacityhours |
double |
Capacity in hours |
| financetype |
character varying |
CAPEX, OPEX or BENEFIT |
| cost |
double |
Respective amount regarding the finance type |
projectfinance_monthly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| projectid |
character varying(2048) |
Link to the project (reporting.projects.code) |
| finance_date |
timestamp without time zone |
The point in time of the financial event |
| amount |
double |
Respective amount regarding the finance type |
| financetype |
character varying(255) |
CAPEX, OPEX or BENEFIT |
| financecategory |
character varying(2048) |
Finance category |
projectfinance_weekly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| projectid |
character varying(2048) |
Link to the project (reporting.projects.code) |
| finance_date |
timestamp without time zone |
The point in time of the financial event |
| amount |
double |
Respective amount regarding the finance type |
| financetype |
character varying(255) |
CAPEX, OPEX or BENEFIT |
| financecategory |
character varying(2048) |
Finance category |
projects
| Column |
Type |
Description |
| code |
character varying(2048) |
Unique ID |
| name |
character varying(2048) |
Name of the project |
| manager_code |
character varying(2048) |
Link to the project manager (reporting.resources.code) |
| manager_name |
character varying(2048) |
Name of the project manager |
| status |
character varying(2048) |
Status of the project |
resource_alloc_capa_monthly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| role |
boolean |
True if the given entity is a role (False if resource) |
| slice_date |
date |
First day of a month |
| capacity |
double |
Capacity in hours |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| capacity_cost |
double |
Cost per hour multiplied by the capacity in hours |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
resource_alloc_capa_obs_monthly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| role |
boolean |
True if the given entity is a role (False if resource) |
| slice_date |
date |
First day of a month |
| capacity |
double |
Capacity in hours |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| capacity_cost |
double |
Cost per hour multiplied by the capacity in hours |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| obs_type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| obs_level_1 |
character varying(2048) |
Name of the unit on level 1 |
| obs_level_2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| obs_level_10 |
character varying(2048) |
Name of the unit on level 10 |
resource_alloc_capa_obs_weekly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| role |
boolean |
True if the given entity is a role (False if resource) |
| slice_date |
date |
First day of the week (Monday) |
| capacity |
double |
Capacity in hours |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| capacity_cost |
double |
Cost per hour multiplied by the capacity in hours |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| obs_type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| obs_level_1 |
character varying(2048) |
Name of the unit on level 1 |
| obs_level_2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| obs_level_10 |
character varying(2048) |
Name of the unit on level 10 |
resource_alloc_capa_weekly
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| scenario_name |
text |
Name of the scenario |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| resource_name |
character varying(2048) |
Name of the resource |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| role |
boolean |
True if the given entity is a role (False if resource) |
| slice_date |
date |
First day of the week (Monday) |
| capacity |
double |
Capacity in hours |
| allocation |
double |
Allocation in hours |
| costtype |
character varying |
CAPEX / OPEX |
| capacity_cost |
double |
Cost per hour multiplied by the capacity in hours |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
resource_obs_levels
| Column |
Type |
Description |
| type |
character varying(2048) |
Name of the Organizational Breakdown Structure (OBS) |
| fqn |
character varying(4096) |
Unique code of the obsunit (full path of the codes concatenated by "/") |
| unit_id |
integer |
Link to the obs unit (table obs.obsid) |
| resource_code |
character varying(2048) |
Link to the resource (reporting.resources.code) |
| level_1 |
character varying(2048) |
Name of the unit on level 1 |
| level_2 |
character varying(2048) |
Name of the unit on level 2 |
| ... |
... |
... |
| level_10 |
character varying(2048) |
Name of the unit on level 10 |
resources
| Column |
Type |
Description |
| code |
character varying(2048) |
Unique ID |
| name |
character varying(2048) |
Name of the resource |
| primaryrole |
character varying(2048) |
Link to the primary role (reporting.resources.code) |
| role |
boolean |
True if the given entity is a role (False if resource) |
| costperhour |
double |
Cost per hour of the resource |
| costtype |
character varying |
CAPEX / OPEX |
| country |
character varying(255) |
Country of the resource |
| city |
character varying(255) |
City of the resource |
| postalcode |
character varying(255) |
Postal code of the resource |
scenarioproject_totals
| Column |
Type |
Description |
| id |
integer |
Link to the scenarioproject (table scenarioproject.id) |
| projectid |
character varying(2048) |
Link to the project (reporting.projects.code) |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| allocation_cost |
double |
Cost per hour multiplied by the allocation in hours |
| allocation_hours |
double |
Allocation in hours |
| finance_cost |
double |
Allocation independent costs |
scenarioprojects
| id |
integer |
Link to the scenarioproject (table scenarioproject.id) |
| projectid |
character varying(2048) |
Link to the project (reporting.projects.code) |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| startdate |
timestamp without time zone |
Project start date |
| finishdate |
timestamp without time zone |
Project finish date |
| abovemusthave |
boolean |
True if project is above the must have line |
| belowcutoff |
boolean |
True if project is below the cutoff line |
| priority |
bigint |
Project priority |
| programtitle |
character varying(2048) |
Title of the program containing this project |
| programid |
integer |
Id of the program containing this project |
scenarioprojects_noncutoff
| Column |
Type |
Description |
| scenario_id |
integer |
Link to the scenario (reporting.scenarios.id) |
| id |
integer |
Link to the scenarioproject (table scenarioproject.id) |
| projectid |
character varying(2048) |
Link to the project (reporting.projects.code) |
scenarios
| Column |
Type |
Description |
| id |
integer |
Unique ID |
| name |
text |
Name of the scenario |
