Traffic Data Analysis Power BI Report

Requirement Document for Qatar Traffic Data Analysis Power BI Report

1. Introduction

The Qatar Traffic Data Analysis Report is a Power BI dashboard that provides insights into vehicle registrations, driving licenses, and traffic violations in Qatar over multiple years. It enables stakeholders to analyze trends, assess impacts of events like COVID-19 and FIFA 2022, and make data-driven decisions.

2. Objectives

Provide monthly and yearly trends of new vehicle registrations.
Analyze quarter-over-quarter growth of vehicle registrations, traffic violations, and driving licenses.
Compare Hijri and Gregorian calendar trends.
Highlight the impact of major events like COVID-19 and FIFA 2022 on traffic patterns.
Offer insights into driving licenses issued vs. vehicle registrations.
Enable stakeholders to monitor rolling averages and make policy recommendations.

3. Data Sources

Traffic Department of Qatar
Vehicle Registration Records
Driving License Issuance Data
Traffic Violation Reports
Historical Event Data (COVID-19, FIFA 2022, etc.)

4. Report Features

4.1. Vehicle Registration Analysis

Monthly total new vehicle registrations (2018-2024).
Total registered new vehicles, categorized by:
- Private Transport
- Private Motorcycles
- Public Transport
2-Month rolling average with both Hijri and Gregorian calendar breakdowns.

4.2. Quarter-over-Quarter Growth Trends

Vehicle Registrations vs. Traffic Violations (2019 Q1 – 2023 Q3).
Driving Licenses vs. Vehicle Registrations (2019 Q1 – 2023 Q3).
Driving Licenses vs. Traffic Violations (2019 Q1 – 2023 Q3).

4.3. Driving License Analysis

Monthly new driving licenses issued (2019-2024).
Yearly trends in new licenses issued.
Comparison of new licenses vs. new vehicle registrations over time.

4.4. Impact of Major Events

COVID-19 Years (2020-2021) impact on registrations and violations.
FIFA 2022 Effect on monthly vehicle registrations.

5. Visualizations

The dashboard includes:

Line Charts for trends over time.
Bar Charts for category comparisons.
Rolling Average Graphs for smooth trend analysis.
Quarterly Growth Percentage Charts to measure relative changes.

6. Users & Stakeholders

Government Authorities (Traffic Department, Policy Makers)
Transportation Companies
Insurance Companies
Researchers & Analysts
Public & Media

7. Functional Requirements

Dynamic Filtering (year, month, event)
Drill-down Capabilities (e.g., select a year to see monthly trends)
Comparative Views (Hijri vs. Gregorian, Pre-COVID vs. Post-COVID)
Data Export Options (Excel, CSV, PDF)

8. Non-Functional Requirements

Performance: Should load within 5 seconds.
Scalability: Should handle millions of records.
Security: Role-based access control (RBAC) to restrict data access.

9. Assumptions & Constraints

Data is assumed to be accurate and updated regularly.
Historical data completeness depends on government sources.
Report usability depends on Power BI version and licensing.

Traffic Model Download

1. Data Collection & Storage

1.1 Data Sources

SQL Database (Vehicle registrations, driving licenses, traffic violations)
CSV/Excel Files (Historical data, event impact)
APIs (If available, for real-time data)

1.2 SQL Queries for Data Extraction

We use SQL to extract data from the database before loading it into Power BI.

1.2.1 Extracting Vehicle Registration Data

sqlCopySELECT 
    Registration_ID,
    Vehicle_Type,
    Registration_Date,
    Year(Registration_Date) AS Registration_Year,
    Month(Registration_Date) AS Registration_Month,
    COUNT(*) OVER (PARTITION BY Year(Registration_Date), Month(Registration_Date)) AS Monthly_Total
FROM Vehicle_Registrations
WHERE Registration_Date >= '2018-01-01';

1.2.2 Extracting Driving License Data

sqlCopySELECT 
    License_ID,
    License_Type,
    Issue_Date,
    Year(Issue_Date) AS Issue_Year,
    Month(Issue_Date) AS Issue_Month,
    COUNT(*) OVER (PARTITION BY Year(Issue_Date), Month(Issue_Date)) AS Monthly_Total
FROM Driving_Licenses
WHERE Issue_Date >= '2018-01-01';

1.2.3 Extracting Traffic Violation Data

sqlCopySELECT 
    Violation_ID,
    Violation_Type,
    Violation_Date,
    Year(Violation_Date) AS Violation_Year,
    Month(Violation_Date) AS Violation_Month,
    COUNT(*) OVER (PARTITION BY Year(Violation_Date), Month(Violation_Date)) AS Monthly_Total
FROM Traffic_Violations
WHERE Violation_Date >= '2018-01-01';

2. Data Transformation in Power Query

2.1 Loading Data into Power BI

Connect to SQL Database using DirectQuery or Import mode.
Load additional CSV/Excel files (if applicable).

2.2 Data Cleaning & Transformation in Power Query

Rename Columns for clarity (e.g., Reg_Date → Registration Date).
Convert Data Types (e.g., Date fields → Date format).
Filter Unnecessary Data (e.g., remove incomplete or duplicate records).
Add Calculated Columns (e.g., Hijri date conversion).

2.2.1 Creating a Hijri Date Column in Power Query

MCopy// Add a Hijri Year column
= Table.AddColumn(#"Previous Step", "Hijri Year", each Date.Year([Registration Date]) + 579, Int64.Type)

2.2.2 Merging Monthly Data

Merge vehicle registrations, driving licenses, and traffic violations based on year and month:

MCopy= Table.NestedJoin(VehicleData, "Month", LicenseData, "Month", "LicenseJoin", JoinKind.LeftOuter)

3. Data Modeling & Relationships

Set Relationships between tables:
- Vehicle_Registrations[Registration_Date] → Date[Date]
- Driving_Licenses[Issue_Date] → Date[Date]
- Traffic_Violations[Violation_Date] → Date[Date]
Create a Date Table (if not available).

3.1 Creating a Date Table in DAX

DAXCopyDateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2018,1,1), DATE(2024,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date])
)

4. DAX Calculations for Key Metrics

4.1 Total New Vehicle Registrations

DAXCopyTotal_Vehicles = SUM(Vehicle_Registrations[Monthly_Total])

4.2 Rolling 2-Month Average of Vehicle Registrations

DAXCopyRolling_Avg_2M = 
AVERAGEX (
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -2, MONTH),
    [Total_Vehicles]
)

4.3 Year-over-Year Growth in Vehicle Registrations

DAXCopyYoY_Growth = 
DIVIDE (
    [Total_Vehicles] - CALCULATE([Total_Vehicles], SAMEPERIODLASTYEAR(DateTable[Date])),
    CALCULATE([Total_Vehicles], SAMEPERIODLASTYEAR(DateTable[Date])),
    0
)

4.4 Quarter-over-Quarter Growth for Vehicle Registrations vs. Traffic Violations

DAXCopyQoQ_Growth = 
DIVIDE (
    [Total_Vehicles] - CALCULATE([Total_Vehicles], PREVIOUSQUARTER(DateTable[Date])),
    CALCULATE([Total_Vehicles], PREVIOUSQUARTER(DateTable[Date])),
    0
)

4.5 Comparison of New Driving Licenses vs. Vehicle Registrations

DAXCopyLicense_Vehicle_Ratio = 
DIVIDE([Total_Driving_Licenses], [Total_Vehicles], 0)

5. Report Development & Visualization

5.1 Key Visuals in Power BI

Metric	Visualization Type
Monthly Total Vehicle Registrations	Line Chart
Yearly Trends of New Licenses	Bar Chart
2-Month Rolling Average	Area Chart
Quarter-over-Quarter Growth	Column Chart with % Labels
Driving Licenses vs. Vehicle Registrations	Dual Axis Line Chart
Traffic Violations Trend	Stacked Column Chart

5.2 Dynamic Filtering & Slicers

Year & Month Filters
Vehicle Type Filter (Private, Public, Motorcycle)
Event-Based Filters (COVID-19, FIFA 2022)
Comparative Views (Hijri vs. Gregorian)

6. Deployment & Performance Optimization

6.1 Publishing & Sharing

Publish Power BI report to Power BI Service
Enable Row-Level Security (RLS) for data access control

6.2 Performance Optimization

Aggregate Large Data (Use summarized tables)
Optimize DAX Measures (Avoid excessive CALCULATE() calls)
Reduce Model Size (Remove unused columns & relationships)

7. Conclusion

This methodology ensures that the Qatar Traffic Data Analysis Report in Power BI is efficiently completed using SQL for data extraction, Power Query for transformation, and DAX for advanced analytics. The final dashboard will provide interactive, insightful, and high-performance visualizations to stakeholders.