This article details the structure of the Reporting Database for Meisterplan which is accessed when generating internal or external reports.

• Entity Relationship Diagram
• Tables
• allocation_slices_monthly
• allocation_slices_weekly
• capacity_slices_monthly
• capacity_slices_weekly
• base_calendar_capacity_slices_monthly
• base_calendar_capacity_slices_weekly
• obs
• portfolio_project
• portfolio_resource
• Views
• businessgoal_overview
• businessgoal_project
• generalprojectcustomfields
• lookup
• milestones
• obs_project
• obs_resource
• portfolio_budget
• project_obs_levels
• project_resource_alloc_monthly
• project_resource_alloc_obs_monthly
• project_resource_alloc_obs_weekly
• project_resource_alloc_weekly
• project_resource_slicedata_monthly
• project_resource_slicedata_weekly
• projectfinance_monthly
• projectfinance_weekly
• projects
• resource_alloc_capa_monthly
• resource_alloc_capa_obs_monthly
• resource_alloc_capa_obs_weekly
• resource_alloc_capa_weekly
• resource_obs_levels
• resources
• scenarioproject_totals
• scenarioprojects
• scenarioprojects_noncutoff
• scenarios

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

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