Path to Power BI

Sheet
08
Date
13 Oct 2026
Status
Model checked
Step
04

Power BI is workflow step 04. It starts after Report 07 closes CDE setup, and after a raw copy, a clean layer, and a SQL model exist. The reporting source for this practice project is the Snowdon issue workbook, plus a Forma files export if the account can produce one.

1. Where step 04 sits

Chart stepNameMeaning for this project
−7CDE setupCurrent step. Finish it with Report 07 before any pipeline folder is created.
01Raw data preservedAn unchanged copy of the issue workbook, and a Files log export if Forma provides one.
02Python cleaning and validationProfile, standardise, and separate raw values from derived values.
03Structured SQL databaseIssue facts at one row per issue ID. Document facts only from a real files export.
04Power BI reportingExecutive and issue pages. A document page only when document rows exist.

Chart steps 0 and the public RFI workbook belong to a separate procurement register. This path uses Snowden Towers Sample Issue Summary.xlsx. Three issue rows typed RFI remain rows in that workbook.

2. Sequence

  1. Preserve raw inputs. After the document register exists, create snowdon_analytics/data/raw/ and copy the issue workbook there unchanged. If Forma exports a files log or an issues list, save that export beside it. Record file name, retrieval time, and source platform.
  2. Clean in Python, in a separate layer. Read the Issues sheet. Keep original columns in the raw layer. Add source_file, source_sheet, and ingestion_timestamp as ingestion metadata. Standardise dates, identifiers, and blanks only in the clean layer. Write failed rows to a rejection list. Load the Overview sheet on its own. It is a cover, not 289 issue facts.
  3. Check the raw file against Report 06 before modelling. Expect 289 issue rows, 289 unique IDs, status counts from Open 148 through In review 3, and the fill rates already recorded for due date, closed date, description, root cause, and location.
  4. Model only tables the columns can fill. See the table below. Skip review-event and version facts until an export contains those histories.
  5. Build Power BI on the SQL tables. Reconcile every published total to a SQL query and to the 289 source rows.

3. Folders, when the pipeline starts

snowdon_analytics/
├── data/
│   ├── raw/
│   ├── staged/
│   └── curated/
├── scripts/
├── sql/
├── powerbi/
├── validation/
└── docs/

4. SQL tables

TableGrainInclude when
raw_issuesOne exported issue rowThe issue workbook is copied to raw.
fact_issuesOne issue per IDThe 289 IDs stay unique after cleaning.
dim_statusOne status labelStatus is present on every row.
dim_categoryOne category labelCategory is present on every row.
dim_dateOne calendar dateCreated on, and any other date that survives conversion.
raw_documents / fact_documentsOne document rowA Forma files export exists. The hand-written register can be described in the case study. It is not a substitute extract.
fact_document_versionsOne versionVersion history is exported.
fact_reviewsOne review eventReview history is exported.

5. Measures that match this workbook

Closed and Completed are separate status labels (11 rows each). Closed at is filled on 14 rows, so a resolution-time measure waits until those two fields are defined against each other. The published overdue rule is on the lifecycle page.

6. Power BI pages

The published pages are Issue Executive Overview, Issue Lifecycle & Overdue Monitoring, Issue Category, Type & Safety Analysis, and Detailed Issue Register & Data Quality. Open the overview in Power BI. Open the lifecycle page in Power BI. Open the safety page in Power BI. Open the register in Power BI. A plain-language reading of those four pages is the conclusion.

PageContents
Executive289 issues, status composition, category filter, created-on trend if the dates check out.
IssuesStatus, category, type, assignee, location, and a row-level table with the issue ID.
OverviewPublished Issue Executive Overview. Year slicer, five cards, created-on, status, and category.
LifecyclePublished Issue Lifecycle & Overdue Monitoring. Due group, overdue by status and category, and average unresolved age.
SafetyPublished Issue Category, Type & Safety Analysis. Safety cards, top categories, top types, and safety issues by month.
RegisterPublished Detailed Issue Register & Data Quality. Blank fields, closure-date exceptions, and the filterable issue table.
ConclusionPlain-language reading of the four published pages. Finished, overdue, safety, and the gaps in place and date.
DocumentsFile type and the register fields, only after a files export is in the model.
LineageSource file name, ingestion time, and the validation totals from Report 06.

7. Publish checks

8. Model check

The Power BI model has the four loaded tables and three active relationships. Each relationship is many-to-one, and the filter runs from the lookup into fact_issues. A count of id with no slicer selected reads 289.

FromToStatus
fact_issues (category_id)dim_category (category_id)Active
fact_issues (status_id)dim_status (status_id)Active
fact_issues (type_id)dim_type (type_id)Active
Power BI model view. fact_issues sits in the centre, with many-to-one lines from dim_category, dim_type, and dim_status. Each arrow points into fact_issues.
Model view. dim_category, dim_type, and dim_status each join fact_issues. The many side is the fact table.
Power BI Manage relationships. Three active relationships: fact_issues category_id to dim_category category_id, status_id to dim_status status_id, and type_id to dim_type type_id.
Manage relationships. All three pairs use the id columns, and all three are active.