PMO Report Dashboard

Document Information

Functional Analyst

Abidemi Raji

VERSION	DATE	MODIFIED BY	DESCRIPTION
0.01	12/10/2023	Abidemi Raji	Initial draft
0.02	19/10/2023	Abidemi Raji	Updated
0.03	04/11/2023	Abidemi Raji	Updated with list of requirements and alternative tables
0.04	17 Nov 2023	Abidemi Raji	Updated scope and requirements to include only SBS project portfolio dashboard Descoped 15 additional KPIs

Overview

Application Overview

Domain: Programme Management / Finance

Main stakeholders: Service Delivery Managers (SDM), Pool Leads, Product Managers, Program Managers, PMO, Leadership Team (LT)

Business objective/value: Use data sourced from internal databases to create KPIs, determine project financial or execution status and provide decision useful information to stakeholders eliminating the need for manual intervention .

Main function: The dashboard is a visualization tool which provides authorized users with information on project financial and operational status. Various KPIs are also created using data sourced from Accolade and enriched with derived indicators to provide a complete overview to users.

The SBS Project Portfolio dashboard is used by the PMO team to monitor project deliverables, provide project financial summary and present any risks and issues. It provides a single source of information to the team and stakeholders on project KPIs, execution status, risk and issues over a specific period. e.g.

Project Status monitoring - Operational Burn Rate
Project Status monitoring - Status Update Frequency
Project Status monitoring - Carry Over not planned
Financial Status - Project Burn Rate
Financial Status - Project Close to Over Budget
Financial Status - Split Capex OTC
Financial Status - Project on Budget

Application Contact Details

Role (if applicable)	Name	Department	Email
Product Owner	Claire Bazin Dominique de Gouberville
Technical Product Owner
Users	Service Delivery Managers (SDM), Pool Leads, Product Managers, Program Managers, PMO, Leadership Team (LT)

Sign-Off / Approval

Role (if applicable)	Name	Department	Email
Product Owner	Claire Bazin
Product Owner	Dominique de Gouberville
Project Manager	Mauricio Nagahama	Project Manager • Visualization & reporting \| DATA, ANALYTICS & AI PLATFORM_ECO	mauricio.nagahama@solvay.com
Technical Sign off	Fernando Girante	Data Architect • Data & Analytics & AI Platform \| DATA, ANALYTICS & AI PLATFORM_ECO	fernando.girante@solvay.com
Technical Sign off	Matteo Menghetti	Data Engineer • Data architecture & engineering_ECO \| DATA, ANALYTICS & AI PLATFORM_ECO	matteo.menghetti@solvay.com
Technical Sign off	Maxime	Data science & AI_ECO \| DATA, ANALYTICS & AI PLATFORM_ECO	maxime.marboeuf-ext@solvay.com

Application Access Rights

Access to the PMO dashboard depends on the user's profile which ensures that the user is allowed to perform the requested action (e.g. Run reports and download results). The user profiles (“normal user” and “advanced user”) at the same time correspond to the privileges granted according to the Business Unit the user belongs to using Single Sign On functionality

Department	GBU Code	Role	Right	Comments

Business Context

Scope:

In-Scope

This document covers the requirements for the PMO Dashboard to be migrated onto Tableau

Main dashboard visualisation and filters
Access to data post corporate action (split)
Access to the dashboard

Out Of Scope

Data Sourcing requirements are out of scope of this document.

Business process:

The PMO team regularly monitors the overall financial and operational status of several programmes and projects within the organisation and provide update to stakeholders on project or programme status. The team needs the capability to extract and transform data seamlessly for the required parameters that have been pre-defined as part of the reporting process.

Specifically, the PMO team objectives are:

Portfolio Monitoring: The team requires the capability to monitor projects and programmes that are Open or Closed within a defined time period in order to determine status, be aware of risks to project delivery and provide decision useful information to stakeholders.
Data sourcing: The PMO team sources project operational and financial data from Accolade (Solvay internal DB) and vshare.
Reporting: The PMO team requires the capability to create reports from the data extracted from Accolade/vshare, highlighting several defined KPIs to provide updates on project financial and operational status.
Automation: The extraction, refresh and KPI update process are to be performed without manual intervention.

Challenge/Opportunity:

The new PMO dashboard in Tableau will provide authorised users with the capability to leverage multiple data sources within Solvay to review Financial and Operational performance of programmes and projects within a 3.5 year rolling window and have instant access to KPIs, with automated data refresh, eliminating the need for any manual interference.

Effective presentation of data: The PMO team require the capability to present KPIs and project status information in an automated fashion, showing the most impactful metrics.
Consolidated reporting: A single source pf reporting Financial and Operational status of projects and programmes including KPIs
Eliminate manual spreadsheets: The reporting process should not involve manual intervention

Business Glossary

Terminology used in this application consider to be added in -> Enterprise Data Governance

Term	Description

Application Feature List

Feature	Description	Latest update in production (DD/MM/YYYY)

Functional Description

Dashboard

Data Attributes and Mapping: https://docs.google.com/spreadsheets/d/1zaJ5Q87nML7v4CHXhNf3qcng_4EzJ0pKWVPr0OyzUCo/edit#gid=1844818474

Data Product Type

Dashboard
Report
Advanced analytics

Technologies

BW
Tableau
Qliksense
Talend
Dataiku
Others <specify which one>

Data Sources

Note: list of all applications and various environment

Accolade
SAP PF1 (Production environment)
SAP WP1
SAP PI1
BW (versions)

Data Input

As-Is Data Sources (including the nature of the data and what is it needed for):

Currently the PMO Dashboard is implemented in Qlikview. Data is sourced from Accolade_SBS_Portal using two main queries (SGM_CUSTOM_VIEW, SGM_CORE_Project Details) and a Stored Procedure ( RSP_QV_GetMergedMetricsForGroupID) which uses GROUPID_SYS as a parameter .

Data Solutions - BD&A Team > PMO report - 2 - Functional Specification > image-2023-10-18_23-41-20.png

AS-IS Tables

SGM_CUSTOM_VIEW

SGM_CORE_Project Details

SGM_PROJECTMETRICS_15

SGM_PROJECTMETRICS_62

SGM_PROJECTMETRICS_94

SGM_PROJECTMETRICS_108

As-Is Data Model:

Data Solutions - BD&A Team > PMO report - 2 - Functional Specification > image-2023-10-18_19-3-9.png

Target State Data Model:

The target state data sourcing model will re-use existing data model (queries and Stored Procedures) as well as include additional calls to the Database using GroupID_SYS as shown below.

	As-Is	To-Be	Comments
Database	Accolade_SBS_Portal	Accolade_SBS_Portal
Stored Procedure	RSP_QV_GetMergedMetricsForGroupID	RSP_QV_GetProjectMetricsForGroupID
Parameter	GroupID_SYS	GroupID_SYS
Query/Tables	SGM_CUSTOM_VIEW	SGM_CUSTOM_VIEW / CRV_Initiative_V3
	SGM_CORE_Project Details	SGM_CORE_Project Details / CRV_ZGetProjectDetails
	GroupID_SYS: 15, 62,94,108	GroupID_SYS: 15,62,103,94	Execute the stored procedure iteratively, with the different parameters to obtain the different results sets needed for the scope of the project

Target State Data Sourcing:

Business Concept	Database Object	Link to output specification	Comments
SGM_CUSTOM_VIEW	CRV_Initiative_V3	CUSTOMISED_VIEW_FILTERCON	<comment or description of the concepts in the table>
SGM_CORE_Project Details	CRV_ZGetProjectDetails	CORE_PROJECT_DETAILS_FILTERCON
SGM_PROJECTMETRICS_15	Exec RSP_QV_GetProjectMetricsForGroupID 15	SGM_PROJECTMETRICS_15_FILTERCON
SGM_PROJECTMETRICS_62	Exec RSP_QV_GetProjectMetricsForGroupID 62	SGM_PROJECTMETRICS_62_FILTERCON
SGM_PROJECTMETRICS_94	Exec RSP_QV_GetProjectMetricsForGroupID 94	SGM_PROJECTMETRICS_94_FILTERCON
SGM_PROJECTMETRICS_108	Exec RSP_QV_GetProjectMetricsForGroupID 103	SGM_PROJECTMETRICS_103_FILTERCON

Transformation Rules (for each of the data source in the previous point):

In the target state, additional filters to the data extract are required to be implemented in the result of the SGM_CORE_Project Details / CRV_ZGetProjectDetails query

1. Extraction rules and filters:
  1. Project Closed Date >= 3.5yrs
  2. Project Closed = 0
2. Exception handling rules (how do we handle when data does not come in the format we need) : Normal exception handling rules apply
3. Enrichments (normally joins)
4. Aggregation rules

Visualization

Graph name	Description	Link
Main Dashboard	SBS Project Portfolio: Project Details	SBS_Project_Portfolio_Visualisation

PMO Dashboard AS-IS Visualisation:

Data Solutions - BD&A Team > PMO report - 2 - Functional Specification > image-2023-11-17_8-38-24.png

Dashboard filters:

- Roadmap
- accountable (Business)
- Product Manager/ Service Delivery Manager
- Platform/product
- Objective
- Project Manager
- Status
- Program
- Financial Treatment
- Project prioritization
- Start Quarter
- Monthly status date
- Financial up-to-date

System view (Architecture)

The purpose of this part is to describe the physical components that supports the functionalities of the product. From that point of view, this part should capture and visualizes the physical components of the data products such as backend, front end, data providers, libraries for ML models, etc.

Data Solutions - BD&A Team > PMO report - 2 - Functional Specification > image2023-10-12_14-40-51.png

Functional Requirements

Requirement #	Requirement Bucket	Requirement Title	Description/User Story	Acceptance Criteria
Data_Sourcing_1	Data Engineer	Source validated, complete and correct data from Accolade for the PMO dashboard visualisation.	As an authorised SBS Dashboard user I want to have access to correct and complete data on the financial and operational status of SBS projects and programmes in the PMO Dashboard	Data in the current state is migrated successfully to target state including all primary and secondary data
Visualisation_Filters_2	Dashboard Filter	Implement filters to display relevant decision useful information for users	As an authorised SBS Dashboard user I want to have the capability to display data and navigate the the SBS Project Portfolio dashboard using the following filters: - Roadmap - Accountable (Business) - Product Manager/ Service Delivery Manager - Platform/product - Objective - Project Manager - Status - Program - Financial Treatment - Project prioritization - Start Quarter - Monthly status date - Financial up-to-date
Visualisation_Table_3	Dashboard Summary Table	Display summary table of financial and operational status of projects and programmes	As an authorised SBS Dashboard user I want to have a summary table which contains the financial and operational status of projects/programmes, in the dashboard.
Visualisation_Filters_4	Additional Table Dimensions	Create dimensions to filter project data	As an authorised SBS Dashboard user I want to have the capability to display data and navigate the the KPI Report Table on the SBS Project Portfolio dashboard using the following filters: Activity Applying Agile Methodologies Controlling Allocation Digital/IT Roadmap GBU Function Ranking Impact on SBS Recurrent Impact on SBS recurrent amount Impacted Teams IS Relation Manager PMO Classification Program Project Last Gate Decision SBS Lead Team Operation Secondary Activity Sponsor
Visualisation_Filters_5	Additional Table Indicators	Implement additional indicators filter for KPI Report Table	As an authorised SBS Dashboard user I want to have the capability to display data and navigate the the KPI Report Table on the SBS Project Portfolio dashboard using the following indicators: CAPEX & Year (Today()-1) CAPEX & Year (Today()) CAPEX & Year (Today()+1) CAPEX & Year (Today()+2) OTC & Year (Today()-1) OTC & Year (Today()) OTC & Year (Today()+1) OTC & Year (Today()+2) TOTAL COSTS & Year (Today()-1) ACTUAL COSTS & Year (Today()-1) TOTAL COSTS
Visualisation_Table_6	KPI Report Table	Implement colour coded visualisation display on KPI report table	As an authorised SBS Dashboard user I want to have the capability to display colour coded traffic light KPIs (Red, Amber Green) on the KPI Report Table: On Budget On Time Financials Up To Date
Visualisation_Dashboard_Main	Visualisation and User Experience	Create a Dashboard with Summary Table and Project Details (Main Dashboard)	As an authorised SBS Project Portfolio dashboard user, I need to see the overview of financial and operational details of projects using multiple filters and by dimensions	The main dashboard shall display multiple visuals with: The Programme/Project Manager Name The Programme ID, Status, Number of Projects The CAPEX by year, Total CAPEX Users shall be able to apply filters in the dashboard to see relevant year and details required

Non-functional Descriptions

This part specifies criteria that describe the operation of the data product, rather than specific behaviours.

Usability

Regulatory Compliance

The Dashboard will comply with Solvay’s regulations and compliance e.g., access only granted to authorized Users.
Maintenance data will be split between either SCO or ECO, and Users will only have authority to one Entity data

Performance

Reliability

Scalability

Compatibility

Availability

Refresh of the Data

Data shall be sourced from the Accolade database 5 times per day

Storage of the Data

The final copy of the sourced data shall be stored on a daily basis
Stored data shall be made available on request by an authorised user within the PMO team with appropriate permissions.

PMO Report Dashboard