Requirement Document for T&S Global MOC Dashboard
1. Overview
The T&S Global MOC Dashboard in Power BI is designed to track and visualize the status of Management of Change (MoC) processes across different regions, clusters, terminals, and departments. The dashboard provides insights into open, overdue, and completed MoCs, enabling better decision-making and compliance monitoring.
2. Objectives
- To monitor MoC statuses in real time.
- To track overdue and upcoming MoC actions.
- To provide regional and departmental breakdowns for better accountability.
- To analyze trends in MoC implementations over time.
- To highlight key performance indicators (KPIs) relevant to MoC management.
3. Key Features
3.1 Filters
- Region Filter: Americas, Europe/Africa, East, etc.
- Cluster/Sub-Cluster Filter: Canada, CEE, China/Hong Kong, etc.
- Terminal Filter: Individual locations such as 188P Station, Acadian River Terminal, Acetone PL, etc.
- Area Filter: Specific locations like West Virginia, Louisiana Waters.
- MoC Manager Filter: Allows filtering by MoC responsible personnel.
3.2 KPIs Displayed
- Open MoCs:
- Open Permanent MoCs
- Open Temporary MoCs
- Open Emergency MoCs
- MoCs Initiated:
- MoCs initiated during the year
- MoCs initiated during the month
- Closed MoCs:
- MoCs closed during the month
- Overdue MoCs:
- Overdue Temporary MoCs
- Overdue MoC actions
- Time-Based MoC Analysis:
- MoCs open for more than 1 year
- MoCs open for more than 180 days post-implementation
- Temporary MoCs nearing 30-day expiry
- Emergency MoCs initiated this year
- Percentage Change Analysis:
- Month-over-month change in each KPI to measure improvements or backlogs.
4. Data Refresh & Updates
- The dashboard refreshes data as of the latest available date (e.g., 30-Sep-23).
- Some filters (e.g., Department Filter) are only available for specific regions (e.g., Americas).
5. Reporting & Analysis
- Users can assess the backlog and prioritize overdue actions.
- Percentage changes help in trend analysis and performance evaluation.
- Provides visibility into regional and departmental MoC compliance.
6. Access & Security
- Restricted access based on user roles (e.g., Managers, Regional Heads).
- Users can only view MoCs relevant to their assigned regions/departments.
7. Expected Outcomes
- Improved visibility and accountability in MoC processes.
- Timely execution of MoC actions, reducing compliance risks.
- Enhanced decision-making through real-time analytics.
1. Data Extraction & Processing
1.1 SQL Queries for Data Extraction
The data is extracted from various sources such as SQL Server, Azure SQL Database, or an ERP system. The following SQL queries help extract MoC-related data:
Extracting Open MoCs
sqlCopySELECT MoC_ID, MoC_Type, Region, Cluster, Terminal, MoC_Status, Initiation_Date, Due_Date
FROM MoC_Table
WHERE MoC_Status = 'Open'
Extracting Overdue MoCs
sqlCopySELECT MoC_ID, MoC_Type, Region, Due_Date, MoC_Status
FROM MoC_Table
WHERE MoC_Status = 'Open'
AND Due_Date < GETDATE()
Extracting MoC Actions & Trends
sqlCopySELECT MoC_ID, Action_Type, Assigned_To, Completion_Status, Last_Updated
FROM MoC_Actions
WHERE Completion_Status = 'Pending'
1.2 Power Query Transformation (ETL Process)
After extracting data using SQL, Power Query is used in Power BI for data transformation, including:
- Removing Duplicates – Ensuring data integrity.
- Filtering Data – Excluding irrelevant MoCs.
- Merging Tables – Combining MoC data with MoC actions.
- Adding Custom Columns – Calculating overdue days or category classifications.
Example Power Query transformation to calculate overdue days:
MCopy= Table.AddColumn(Source, "Overdue Days", each Duration.Days(Date.From(DateTime.LocalNow()) - [Due_Date]))
2. Data Modeling in Power BI
After cleaning the data in Power Query, it is loaded into the Power BI Data Model:
- Fact Table: MoC Transactions (Contains MoC records, status, dates, etc.)
- Dimension Tables: Regions, Terminals, MoC Managers, MoC Types
Relationships are created between these tables to enable efficient reporting.
3. DAX Measures for Dashboard Visualization
DAX (Data Analysis Expressions) is used for aggregations, calculations, and KPI measures.
3.1 Count of Open MoCs
DAXCopyOpen_MoCs = COUNTROWS(FILTER(MoC_Table, MoC_Table[MoC_Status] = "Open"))
3.2 Count of Overdue MoCs
DAXCopyOverdue_MoCs = CALCULATE(COUNT(MoC_Table[MoC_ID]), MoC_Table[Due_Date] < TODAY())
3.3 Month-over-Month MoC Changes (%)
DAXCopyMoM_Change =
VAR PreviousMonth = CALCULATE([Open_MoCs], PREVIOUSMONTH(MoC_Table[Initiation_Date]))
VAR CurrentMonth = [Open_MoCs]
RETURN
IF(PreviousMonth = 0, BLANK(), (CurrentMonth - PreviousMonth) / PreviousMonth * 100)
3.4 MoCs Initiated This Year
DAXCopyMoC_ThisYear = CALCULATE(COUNT(MoC_Table[MoC_ID]), YEAR(MoC_Table[Initiation_Date]) = YEAR(TODAY()))
3.5 Actions Due in 30 Days
DAXCopyActions_Due_30Days =
CALCULATE(COUNT(MoC_Actions[MoC_ID]), MoC_Actions[Due_Date] <= TODAY() + 30)
4. Dashboard Visualization
Using Power BI, the following visualizations are created:
- Card Visuals for KPI summaries (Total Open MoCs, Overdue MoCs, MoCs Closed This Month).
- Bar Charts for MoC trends over months.
- Table View for detailed MoC records.
- Filters & Slicers for region, terminal, and MoC type selection.
5. Report Deployment & Refresh
- The dashboard is published to the Power BI Service.
- Scheduled Data Refresh is configured to ensure up-to-date reporting.
- Access Control is applied to restrict user permissions based on regions.
Conclusion
By integrating SQL Queries, Power Query transformations, and DAX calculations, the T&S Global MOC Dashboard provides real-time insights into MoC performance, overdue actions, and process trends.
Sample data files in Excel format, including:
- MoC Data – Contains information about MoCs (ID, Type, Status, Dates, etc.).
- MoC Actions – Tracks actions related to each MoC (Assigned To, Status, Due Date).
- Region & Terminal Mapping – Maps MoCs to different regions and terminals.