As-Is Data Model: 

Image Removed

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-IsTo-BeCommentsDatabaseAccolade_SBS_PortalAccolade_SBS_PortalStored ProcedureRSP_QV_GetMergedMetricsForGroupIDRSP_QV_GetProjectMetricsForGroupIDParameterGroupID_SYSGroupID_SYSQuery/Tables SGM_CUSTOM_VIEW SGM_CUSTOM_VIEW / CRV_Initiative_V3SGM_CORE_Project DetailsSGM_CORE_Project Details / CRV_ZGetProjectDetails

GroupID_SYS:

15, 62,94,108

GroupID_SYS:
18, 46, 109, 63, 64, 65, 66, 103, 104, 69, 111, 105, 106, 110, 124

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

Tables, Stored Procedures and Data Mapping File

<comment or description of the concepts in the table>

SGM_CORE_Project Details

CRV_ZGetProjectDetails

SGM_PROJECTMETRICS_15

Exec RSP_QV_GetProjectMetricsForGroupID 15

SGM_PROJECTMETRICS_62

Exec RSP_QV_GetProjectMetricsForGroupID 62

SGM_PROJECTMETRICS_94

Exec RSP_QV_GetProjectMetricsForGroupID 94

SGM_PROJECTMETRICS_108

Exec RSP_QV_GetProjectMetricsForGroupID 108

SGM_PROJECTMETRICS_18

Exec RSP_QV_GetProjectMetricsForGroupID 18

SGM_PROJECTMETRICS_46

Exec RSP_QV_GetProjectMetricsForGroupID 46

SGM_PROJECTMETRICS_109

Exec RSP_QV_GetProjectMetricsForGroupID 109

SGM_PROJECTMETRICS_63

Exec RSP_QV_GetProjectMetricsForGroupID 63

SGM_PROJECTMETRICS_64

Exec RSP_QV_GetProjectMetricsForGroupID 64

SGM_PROJECTMETRICS_65

Exec RSP_QV_GetProjectMetricsForGroupID 65

SGM_PROJECTMETRICS_66

Exec RSP_QV_GetProjectMetricsForGroupID 66

SGM_PROJECTMETRICS_103

Exec RSP_QV_GetProjectMetricsForGroupID 103

SGM_PROJECTMETRICS_104

Exec RSP_QV_GetProjectMetricsForGroupID 104

SGM_PROJECTMETRICS_69

Exec RSP_QV_GetProjectMetricsForGroupID 69

SGM_PROJECTMETRICS_111

Exec RSP_QV_GetProjectMetricsForGroupID 111

SGM_PROJECTMETRICS_105

Exec RSP_QV_GetProjectMetricsForGroupID 105

SGM_PROJECTMETRICS_65

Exec RSP_QV_GetProjectMetricsForGroupID 106

SGM_PROJECTMETRICS_110

Exec RSP_QV_GetProjectMetricsForGroupID 110

SGM_PROJECTMETRICS_124

Exec RSP_QV_GetProjectMetricsForGroupID 124

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

KPI's Definitions 

Budget and Cost File: Budget & Cost File: 2F250000.xlsm

KPI NumberKPI NameCalculation Description        1Forecast accuracy rate (act+ planned total [year N] (-[year N]actual cost/(act+ planned total [year N])

Budget & Costs File:

(Act.+Planned CAPEX) + (Act.+Planned OTC) = Act.+Planned Total

Actual Planned Total for the current year -(Current year Actual Cost/Actual Planned Total for the current year)  

2Operational time total (in days)NETWORKDAYS(Requested Start Date (PMO Gate) -end date)

Budget & Cost File:

Number of working days between Requested Start Date (PMO Gate)  and End date

3Operational time to date vs total (in %)if(IFERROR(NETWORKDAYS(Requested Start date (PMO Gate) ;today())/operational time total)>1;1;IFERROR(NETWORKDAYS(Requested Start date (PMO Gate);today())/operational time total))

Budget & Cost File:

if the number of working days between Requested Start date (PMO Gate) and today  divided by Operational Time Total KPI (NUMBER 4). If a value is returned, display that value otherwise if it shows an error just display 1

4Burn rate budget [year N]actual cost/act+ planned total [year N]

Budget & Cost File:

Actual Cost for the current year divided by the Actual Planned Total for the current year

5Burn rate vs burn rate time (in %) burn rate/operational time to date vs total

Budget & Cost File:

Burn Rate divided by Operational Time total

KPI 4 / KPI 5

6Operational time burn rateif (burn rate vs burn rate time>50%, "1 - Ahead of Schedule >50%";IF(burn rate vs burn rate time>30%;"2 - Ahead of Schedule >30%";IF(burn rate vs burn rate time>-30%;"3 - Linear";IF(burn rate vs burn rate time>-50%;"4 - Below Schedule 30-50%";"5 - Below Schedule more than 50%")

Budget & Cost File: 

If KPI 5 is greater than 50 %  display 1 - Ahead of Schedule >50%"

If KPI 5 is greater than 30 %  display 1 - Ahead of Schedule 30%"

If KPI 5 is greater than -30 %  display "3 - Linear"

If KPI 5 is greater than -50 %  display "4 - Below Schedule 30-50%"

else display "5 - Below Schedule more than 50%"

7Project (planned, ongoing or went life) with no approved budget but with some actualson Budget&cost file (coming from Accolade): column DO "total approved" is empty whereas column CI "status" is equal to on-going or went-live

Budget and Cost File:

Display a list of projects where the Total Approved amount is BLANK and "Status" is "On-going" or "Went Live"

8Carry over not plannedon Budget&cost file: column CI status = closed whereas still increase of actuals month after month (column EG)
closed projects with actuals after the closure date

Budget and Cost File:

Display the list of projects (+ number) with an increase of the budget

9Projects not yet started with a start date in the pastIdea&prospective not migrated into project with a start date in the past10Status update frequencystatus update frequency in number of projects
if(project to be considered="no";"";if(days since Last update ="";"6.never updated";if(days since Last update <31;"1.Less than 30 days";if(days since Last update <61;"2.between 30 & 60 days";if(days since Last update <91;"3.between 2 & 3 month";if(days since Last update <181;"4.beetween 3 & 6 month";"5.more than 6 month"))))))

Budget and Cost File:

Display a histogram of projects showing how frequently their status has been updated. 

if the project to be considered field  = NO, and the days since last update is blank = 1; the status =6 Never Updated 

if the project to be considered field  = NO, and the days since last update is < 31 ; the status = 1. Less Than 30 days

if the project to be considered field  = NO, and the days since last update is < 61 ; the status = 2. Between 30 and 60 days

if the project to be considered field  = NO, and the days since last update is < 91 ; the status = 3. Between 2 and 3 months

if the project to be considered field  = NO, and the days since last update is < 181 ; the status = 4 Between 3 and 6 months

else display 5. more than 6 months

11Finance update frequencyFinance update frequency in number of projects
updated=countifs('project to be considered;"yes";'financial uptodate;"1")
not updated=countifs('project to be considered;"yes";'financial uptodate;"3")
no KFD=countifs('project to be considered;"yes";'financial uptodate;"")

Budget and Cost File:

Display the number of projects where the financial status of the project has been updated

if the project to be considered field  = yes and the financial up to date field = 1; the status = Updated

if the project to be considered field  = yes and the financial up to date fiekd = 3; the status = Not Updated

if the project to be considered field  = yes and the financial up to date filed = blank; the status = No KFD

12On BudgetProject forecasts vs approved budget
below, above, on track

Budget and Cost File:

Display a histogram of Project forecasts vs approved budget

Status: below, above, on track

If forecast budget is below ref approved: Below

If forecast budget if above approved ref:  Above

If forecast budget if equal to approved ref: On Track

13On Timeon time: =countifs('project to be considered;"yes";'on time;"1")
slightly delayed=countifs('project to be considered;"yes";'on time;"2")
delayed: countifs('project to be considered;"yes";'on time;"3")

Budget and Cost File:

Display a histogram of the number of projects that are "On Time", "Slightly Delayed" or "Delayed"

The value of On Time  = 1

The value of Slightly Delayed  = 2

The value of Delayed  = 3

14Project close to over budgetend date<month date

Budget and Cost File:

Display a histogram of the List of projects where end date<month date

15Split Capex / OTC Approved vs forecast

Budget and Cost File: 

Display a histogram of the list of projects comparing Approved vs forecast

Visualization

Data & KPI User Story Deck

Graph nameMain DashboardSBS Project Portfolio: Project DetailsFinance -

1. Split Capex / OTC
2. Finance Update Frequency
3. Burn Rate Budget
4. Forecast Accuracy Rate

Finance /OperationalBurn Rate vs Burn Rate Time Finance + OperationOperational 

1. Operational Time Total (in days)
2. Operational time to date vs total (%)
3. Operational Burn Rate
4. Project (Planned, ongoing, went live) with no approved budget but with some actuals
5. Projects not yet started with a start date in the past
6. Carryover Not Planned
7. Status Update Frequency
8. On Budget
9. On Time
10. Project Close to Over Budget

PMO Dashboard AS-IS Visualisation:

Image Removed

PMO Dashboard TO-BE Visualisation:

Main Dashboard

Image Removed

Main 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

KPI Page

Image Removed

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

Functional Requirements

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

7.0 Non-functional Descriptions 

...

Please populate the relevant section and delete those that are not applicable.

7.1 Usability

Usability is about the ease with which a User can learn to start using the solution and the ease with which they can use the system.  In addition to ease of learning and ease of use, usability also includes areas such as ease of recall, error avoidance and handling, accessibility among others e.g., 99% of metadata entry Users who have use the Maintenance Dashboard should be able to change filters, extract etc., when required.  Maintenance data will be centrally stored in the Google Cloud platform, which will be available to other applications e.g., and Dashboards if needed.

7.2 Regulatory Compliance

Software systems must comply with legal and regulatory e.g., GDPR requirements, this can change depending on country, organisation industry and / or region.  The software systems must be secure from unauthorized access.  The Maintenance Dashboard will comply with Solvay’s regulations and compliance e.g., access only granted to authorized Users.

7.3 Security

Security refers to essential aspects that assure a solution and its components will be protected against unauthorized access or malware attacks.  Important considerations related to security aspects of a system are User authentication, User authorization or User access privileges, data theft, malware attacks, data encryption, and maintaining audit trails, e.g., only Users with administrator access shall be able to create new accounts and assign data access privileges to the new accounts e.g.,

• All data will be encrypted in the dashboard
• Only authorised Users / Administrative Users will be able to access data.
• Maintenance data will be split between either SCO or ECO, and Users will only have authority to one Entity data.

7.4 Performance

Performance defines how fast a software system or a particular section of it responds to certain User actions under a certain workload.  In most cases, this metric explains how long a User must wait before the target operation happens e.g., the page renders, a transaction is processed, etc., given the overall number of Users now.  Performance requirements may describe background processes invisible to Users, e.g., backup and speed of data transfers. 

 

7.5 Reliability

Reliability is the ability of a solution or its component to perform its required functions without failure under predefined conditions for a specified time / period.  Reliability can possibly be specified in terms of average time system runs before failure occurs, percentage of operations completed successfully within a time / period, maximum acceptable failure probability, or number of failures within a period.  Reliability aspects are in reference to (but not limited to) evaluation of the system to be considered as reliable, classification of reliability defining failures vs. regular failures, and the impact of failure on business operations.  The Maintenance Dashboard will display data from the previous refresh of data.   

 

7.6 Scalability

Scalability refers to the degree to which a solution can evolve to handle increased amounts of work.  The increased amount of work could be in terms of the user base, transactions, data, network traffic, or other factors e.g., the system should be able to handle an additional load of a maximum of 5,000 Users every month for the next 6 months without any noticeable performance impacts.  

7.7 Compatibility

Interoperability is the degree to which the solution is compatible with other components.  It is a measure of how effectively the system interoperates with other software systems and how easily it integrates with external hardware devices.

Interoperability aspects to be discussed during elicitation are in reference to (but not limited to) software systems to be interfaced with along with data / messages to be exchanged and any standard data formats, hardware components to be integrated with, and any standard communication protocols to be followed e.g., Order Management system will push the order file into a secured file transfer protocol server from where it will be loaded into the system through a daily job.  To guarantee between Google Cloud platform and SAP BW Queries e.g., BW_QRY_MVPMOR01_0002, Solvay has introduced a new tool called Xtract (Xtract).

 

7.8 Availability

Availability is the degree to which the solution is operable and accessible when required. It is a measure of time during which the system is fully operational e.g., available for use and sometimes included as a Service Level Agreement (SLA) considering its criticality to the business, e.g., the system shall be at least 99% available on weekdays between 09:00 to 18:30 Central European Time (CET).

7.9 Refresh of the Data

Frequency, data, and time of the data refresh in the data product. 

Performance

 

Reliability

 

Scalability

Compatibility

 

Availability

Refresh of the Data

Storage of the Data

...