Enterprise Power BI & Power Query ETL Architecture

Engineering Dimension	Architecture & Delivery Specification
Domain & Context	Enterprise Financial Reporting & Data Pipeline Architecture
Core Deliverables	M-Code ETL Pipeline, Star Schema Normalization, DAX Measure Optimization
Turnaround Timeline	3 Business Days / Production Readiness
Pillar Alignment	5. Data Analysis & Automation

---

1. Executive Summary

Many Power BI reports suffer from memory crashes, slow report rendering, and scheduled refresh timeouts. In most business setups, these performance degradations stem from loading unnormalized flat spreadsheets directly into the data model rather than implementing a clean Star Schema with separated Fact and Dimension tables. Furthermore, unoptimized transformation steps break Query Folding, forcing Power BI to pull millions of unindexed raw rows into local memory.

This blueprint details an automated ETL pipeline using Power Query M-code, robust Star Schema architecture, and optimized DAX calculations.

---

2. High-Level Design (HLD)

[Raw Sources: Multi-Tab Excel / ERP Dumps]
                │
                │ (1. Ingestion via Power Query M-Engine)
                ▼
      [Query Folding Gate]
     ├──> Database Views: Source-Level Filtering & Aggregation
     └──> Excel/CSV: Buffered Lookups & Normalized Type Casting
                │
                │ (2. Star-Schema Dimensional Split)
                ▼
┌────────────────────────────────────────────────────────┐
│                   Power BI VertiPaq Engine             │
│                                                        │
│   [Dim_Date]      [Dim_Customer]     [Dim_Product]     │
│       │                 │                  │           │
│       └───────────┬─────┴────────────┬─────┘           │
│                   │ 1:Many (Single)  │                 │
│                   ▼                  ▼                 │
│              [Fact_Transactions / Financials]          │
└────────────────────────────────────────────────────────┘
                │
                │ (3. Optimized DAX Measures)
                ▼
[Executive Interactive Visuals & Automated Scheduled Refresh]

---

3. M-Code Optimization & Zero-VBA Parameter Ingestion

To prevent memory bloat during scheduled refresh, transformations maintain query folding when connected to relational stores, or use buffer caching for Excel flat files. Additionally, dynamic parameters replace legacy VBA macros (InputBox) with native Excel parameter tables:

let
    // Dynamic parameter lookup without VBA macros (.xlsm)
    fnGetParameter = (ParameterName as text) as any =>
    let
        ParamSource = Excel.CurrentWorkbook(){[Name="tbl_Parameters"]}[Content],
        FilteredRows = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
        ResultValue = if Table.IsEmpty(FilteredRows) then null else FilteredRows{0}[Value]
    in
        ResultValue,

    SourcePath = fnGetParameter("SourceFilePath"),
    Source = Sql.Database("production-db.internal", "AnalyticsDW", [Query="SELECT * FROM v_CleanTransactions WHERE TransactionDate >= '2024-01-01'"]),
    #"Filtered Valid Rows" = Table.SelectRows(Source, each [TransactionAmount] <> null and [TransactionAmount] > 0),
    #"Parsed Types" = Table.TransformColumnTypes(#"Filtered Valid Rows",{
        {"TransactionID", Int64.Type},
        {"CustomerID", Int64.Type},
        {"TransactionDate", type date},
        {"TransactionAmount", type number}
    }),
    #"Buffered Dim Join" = Table.Buffer(DimCustomerLookup)
in
    #"Parsed Types"

---

4. Declarative TMDL Semantic Model (Microsoft PBIP Standard)

Adhering to modern DevOps version-control standards (TMDL / Tabular Model Definition Language), models are structured as plain-text Git artifacts rather than opaque binaries:

table Dim_Date
    lineageTag: a1b2c3d4-dim-date

    column Date
        dataType: dateTime
        isKey
        formatString: yyyy-MM-dd
        sourceColumn: Date

table Fact_Transactions
    lineageTag: e5f6a7b8-fact-trans

    column TransactionID
        dataType: int64
        isKey
        sourceColumn: TransactionID

    column DateKey
        dataType: dateTime
        isHidden
        sourceColumn: DateKey

    measure 'Total Revenue YTD' =
        CALCULATE(
            SUM(Fact_Transactions[TransactionAmount]),
            DATESYTD(Dim_Date[Date])
        )
        formatString: \$#,##0.00
        displayFolder: "2. Time Intelligence"

relationship Rel_Transactions_Date
    fromColumn: Fact_Transactions[DateKey]
    toColumn: Dim_Date[Date]
    crossFilteringBehavior: singleDirection

---

5. SQLBI Best Practice Analyzer (BPA) Verification

Our automated diagnostic harness (etl_diagnostic_harness.py) executes 4 core rules against the model:

Rule ID	Heuristic Standard	Status	Technical Benefit
BPA-01	Single-Direction Relationships	Passed	Prevents ambiguous filter paths and VertiPaq memory exhaustion.
BPA-02	Hidden Foreign Keys	Passed	Fact foreign keys hidden from end-users to prevent invalid slice contexts.
BPA-03	Display Folder Organization	Passed	Measures organized into functional folders (Core Financials, Time Intelligence).
BPA-04	Zero-VBA Parameter Ingestion	Passed	Eliminates MOTW macro security blocks on modern corporate tenants.

---

6. Engineering & Architecture Consultations

• Direct Inquiries: devapenseo@gmail.com
• Engagement Model: Enterprise data architecture, Star-Schema refactoring, and automated BI pipeline consulting.