Mock Data Generation

Skill: Mock Data Generation

Purpose

Generate realistic mock data as CSV files to validate the PBIP semantic model locally in Power BI Desktop. Use SDV probabilistic synthesis when the runtime supports it; otherwise fall back to deterministic generation.

Input / Output

Python Environment Setup

Before generating data, verify the Python virtual environment is set up:

Prerequisites

The user must have Python 3.10+ installed on their local machine.

Setup Commands

# Navigate to the repository root
cd <repository-root>

# Create virtual environment (if not already done)
python -m venv .venv

# Activate virtual environment (Windows PowerShell)
.\.venv\Scripts\Activate.ps1

# Install required packages (default cross-step baseline)
pip install -r requirements.txt

# Optional: install SDV core package only if you explicitly want probabilistic synthesis
# and you accept that some environments may still be unable to load the full SDV stack.
python -m pip install sdv==1.34.3 --no-deps

Verify Installation

python -c "import pandas; import faker; print('OK')"

Optional SDV verification:

python -c "import sdv; print('SDV core package available')"

Script Generation Rules

Technology

• Use Python with pandas and faker libraries.
• Use sdv only when the local runtime can import it successfully; otherwise the generator must continue with deterministic fallback logic.
• Use faker only to bootstrap compact seed datasets when no source extracts are available.
• Generate ONE script file: <ProjectName>/scripts/generate_mock_data.py
• Output CSV files to <ProjectName>/data/ folder.

SDV Strategy for Star Schema Models

Use a star-schema-oriented generation flow optimized for performance and referential integrity:

1. Generate deterministic seed datasets for conformed dimensions:
   • Dim_Date MUST remain fully programmatic.
   • Low-cardinality dimensions may use controlled lists or faker to create a compact bootstrap sample.
2. Build SDV metadata from the seed tables and explicitly define primary keys and fact-to-dimension relationships.
3. Prefer a hybrid SDV strategy for performance:
   • Use SDV single-table synthesizers for dimensions.
   • Use an SDV probabilistic synthesizer for each fact table with FK columns treated as categorical business keys.
4. Use HMASynthesizer only when preserving cross-table dependency structure is more important than runtime.
5. After sampling, run post-processing to enforce integer keys, decimal precision, null rules, and orphan-key repair.

This hybrid pattern is preferred for Power BI star schemas because dimensions are usually small and stable, while fact tables require faster generation at higher volumes.

Data Quality Requirements

1. Referential Integrity: ALL Foreign Keys in Fact tables MUST exist as Primary Keys in the corresponding Dimension tables.
2. Data Type Consistency: Generated data types must EXACTLY match the TMDL dataType definitions.
3. Realistic Values: Use SDV probabilistic synthesis to learn realistic value distributions from the bootstrap seed data. Use faker only to create the minimal seed sample when needed.
4. Volume: Generate sufficient data for meaningful visuals:
   • Dimension tables: 20-100 rows (depending on cardinality)
   • Fact tables: 500-2000 rows
   • Date dimension: Full fiscal year(s) — one row per day

Date Dimension Generation

The Dim_Date table MUST be generated programmatically covering the required fiscal year range:

import pandas as pd

def generate_dim_date(start_date, end_date, fiscal_year_start_month=7):
    dates = pd.date_range(start=start_date, end=end_date, freq='D')
    df = pd.DataFrame({'Date': dates})
    df['DateKey'] = df['Date'].dt.strftime('%Y%m%d').astype(int)
    df['Year'] = df['Date'].dt.year.astype(str)
    df['Quarter'] = 'Q' + df['Date'].dt.quarter.astype(str)
    df['Month'] = df['Date'].dt.month
    df['MonthName'] = df['Date'].dt.strftime('%B')
    df['DayOfWeek'] = df['Date'].dt.dayofweek + 1
    df['DayName'] = df['Date'].dt.strftime('%A')
    df['IsWeekend'] = df['DayOfWeek'].isin([6, 7])
    # Fiscal year logic
    df['FiscalYear'] = df['Date'].apply(
        lambda d: f"FY{d.year + 1}" if d.month >= fiscal_year_start_month else f"FY{d.year}"
    )
    df['FiscalMonth'] = df['Date'].apply(
        lambda d: ((d.month - fiscal_year_start_month) % 12) + 1
    )
    df['FiscalQuarter'] = 'FQ' + ((df['FiscalMonth'] - 1) // 3 + 1).astype(str)
    return df

SDV Metadata and Sampling Pattern

from sdv.metadata import MultiTableMetadata, SingleTableMetadata
from sdv.single_table import GaussianCopulaSynthesizer

def build_metadata(dim_date_seed, dim_customer_seed, dim_area_seed, fact_sales_seed):
    metadata = MultiTableMetadata()
    metadata.detect_table_from_dataframe(table_name='Dim_Date', data=dim_date_seed)
    metadata.detect_table_from_dataframe(table_name='Dim_Customer', data=dim_customer_seed)
    metadata.detect_table_from_dataframe(table_name='Dim_Area', data=dim_area_seed)
    metadata.detect_table_from_dataframe(table_name='Fact_Sales', data=fact_sales_seed)

    metadata.set_primary_key(table_name='Dim_Date', column_name='DateKey')
    metadata.set_primary_key(table_name='Dim_Customer', column_name='CustomerKey')
    metadata.set_primary_key(table_name='Dim_Area', column_name='AreaKey')
    metadata.set_primary_key(table_name='Fact_Sales', column_name='SalesKey')

    metadata.add_relationship(
        parent_table_name='Dim_Date',
        parent_primary_key='DateKey',
        child_table_name='Fact_Sales',
        child_foreign_key='DateKey',
    )
    metadata.add_relationship(
        parent_table_name='Dim_Customer',
        parent_primary_key='CustomerKey',
        child_table_name='Fact_Sales',
        child_foreign_key='CustomerKey',
    )
    metadata.add_relationship(
        parent_table_name='Dim_Area',
        parent_primary_key='AreaKey',
        child_table_name='Fact_Sales',
        child_foreign_key='AreaKey',
    )
    return metadata

def synthesize_dimension(seed_df, num_rows):
    metadata = SingleTableMetadata()
    metadata.detect_from_dataframe(data=seed_df)
    synthesizer = GaussianCopulaSynthesizer(metadata)
    synthesizer.fit(seed_df)
    return synthesizer.sample(num_rows=num_rows)

Fact Table Generation Pattern

from sdv.metadata import SingleTableMetadata
from sdv.single_table import GaussianCopulaSynthesizer

def generate_fact_sales(dim_date, dim_customer, dim_area, fact_seed, num_rows=1000):
    fact_metadata = SingleTableMetadata()
    fact_metadata.detect_from_dataframe(data=fact_seed)
    synthesizer = GaussianCopulaSynthesizer(fact_metadata)
    synthesizer.fit(fact_seed)
    df = synthesizer.sample(num_rows=num_rows)

    df['DateKey'] = df['DateKey'].where(df['DateKey'].isin(dim_date['DateKey']), dim_date['DateKey'].sample(len(df), replace=True).to_numpy())
    df['CustomerKey'] = df['CustomerKey'].where(df['CustomerKey'].isin(dim_customer['CustomerKey']), dim_customer['CustomerKey'].sample(len(df), replace=True).to_numpy())
    df['AreaKey'] = df['AreaKey'].where(df['AreaKey'].isin(dim_area['AreaKey']), dim_area['AreaKey'].sample(len(df), replace=True).to_numpy())
    df['AdjustedProfitLC'] = df[['AdjustedProfitLC', 'SalesAmountLC']].min(axis=1)

    return df

When to Use HMASynthesizer

• Use HMASynthesizer when you already have a representative relational seed dataset and must preserve multi-table correlations across the entire star schema.
• Do NOT make HMASynthesizer the default for Step 05 when the goal is fast local mock generation for Power BI validation.
• If HMASynthesizer is used, keep the seed dataset compact and sample up to the target row counts instead of fitting on large bootstrap datasets.

CSV Output

• Files go to <ProjectName>/data/ folder
• Use comma delimiter, UTF-8 encoding WITHOUT BOM, no index
• Filename convention: lowercase table name (e.g., dim_date.csv, fact_sales.csv)

df.to_csv('<ProjectName>/data/dim_date.csv', index=False, encoding='utf-8')

CRITICAL: Pandas encoding='utf-8' generates UTF-8 without BOM by default, which is the format required by Power BI Desktop for TMDL parsing. Do NOT use other tools (like PowerShell WriteAllText) to modify CSV files after generation, as they may add a BOM and cause parsing errors.

TMDL Partition Update

After generating CSVs, update each table's TMDL partition source expression to point to the correct CSV path.

The M expression should use absolute paths for local development:

source =
	let
		Source = Csv.Document(File.Contents("C:\path\to\repo\<ProjectName>\data\dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
		PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
	in
		PromotedHeaders

Alternatively, use a parameterized path via expressions.tmdl:

expression DataPath = "C:\path\to\repo\<ProjectName>\data" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

Then reference in partition source:

Source = Csv.Document(File.Contents(DataPath & "\dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None])

Lift and Shift Strategy — Transition to Production Data Source

Important: CSV files are intended as temporary mock data for local development and validation. In production, the semantic model will connect directly to a database (SQL Server, Azure SQL, Fabric Lakehouse, etc.).

Recommended Approach for Easy Migration

Option 1: Parameterized Source (Recommended)

Create a shared M parameter in expressions.tmdl that controls the data source type:

expression SourceType = "CSV" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

expression DataPath = "C:\path\to\repo\<ProjectName>\data" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

expression DatabaseConnectionString = "Server=myserver;Database=mydb" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = false]

Then in each table partition, use conditional logic:

source =
	let
		CSVSource = Csv.Document(File.Contents(DataPath & "\dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
		DatabaseSource = Sql.Database("myserver", "mydb"){[Schema="dbo",Item="Dim_Date"]}[Data],
		Source = if SourceType = "CSV" then CSVSource else DatabaseSource,
		PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
	in
		PromotedHeaders

Advantage: Switch between CSV and database by changing a single parameter value.

Option 2: Replace Partition Source (Simpler)

When ready for production, replace the entire partition source expression in each table's TMDL file:

Before (CSV mock):

source =
	let
		Source = Csv.Document(File.Contents("C:\...\<ProjectName>\data\dim_date.csv"), ...),

After (SQL Database):

source =
	let
		Source = Sql.Database("myserver.database.windows.net", "mydatabase"){[Schema="dbo",Item="Dim_Date"]}[Data],

Advantage: Simpler partition expressions (no conditional logic). Use Power BI Desktop's "Transform Data" UI to reconnect to the database, then save the TMDL changes.

Option 3: Fabric Lakehouse (Direct Lake)

For Fabric-based deployments, the partition mode changes from import to directLake:

partition Dim_Date = entity
	mode: directLake
	source
		schemaName: dbo
		tableName: Dim_Date
		expressionSource: DatabaseQuery

Note: This requires the semantic model to be published to a Fabric workspace and connected to a Lakehouse.

Best Practice Recommendation

• Use Option 1 (parameterized source) during Step 05 if you anticipate frequent switching between mock and real data during development.
• Use Option 2 (replace partition source) for simpler models where the transition happens only once before production deployment.
• For Fabric deployments, plan for Option 3 (Direct Lake) from the beginning if your target data platform is Fabric Lakehouse.

⚠️ Data Realism: Temporal Trend Requirement (CRITICAL)

Flat data will cause the report agent to fail. When generating data for time-series dashboards:

• Metrics that model business improvement over time (e.g., conversion rate, satisfaction score, adoption %) MUST show a directional trend across periods — not random noise.
• Random seeding alone produces non-monotonic variation across periods, which breaks bar charts and KPI delta visuals.

Fix pattern — enforce a target per period using weight-based assignment:

# Define target KPI% per period: directional trend P1→P4
# Replace 'Period', 'WeightColumn', 'FlagColumn' with actual column names from the model
period_col = 'Period'          # e.g. 'Quarter', 'Month', 'FiscalPeriod'
weight_col = 'WeightColumn'    # e.g. 'Amount', 'Revenue', 'Count'
flag_col   = 'FlagColumn'      # boolean flag driving the KPI numerator

targets = {
    'P1': 0.20,   # replace with business-driven target values
    'P2': 0.25,
    'P3': 0.30,
    'P4': 0.35,
}

for period, target in targets.items():
    idx = fact[fact[period_col] == period].index
    total = fact.loc[idx, weight_col].sum()
    target_value = total * target
    fact.loc[idx, flag_col] = 0  # reset
    cumsum = 0
    for row_idx, row in fact.loc[idx].sort_values(weight_col, ascending=False).iterrows():
        if cumsum < target_value:
            fact.loc[row_idx, flag_col] = 1
            cumsum += row[weight_col]
        else:
            break

Business logic rules (enforce after flag assignment):

• If your model has dependent flags (e.g., flag A implies flag B), enforce consistency after assignment
• Validate with: assert len(fact[(fact[flag_col]==1) & (fact[dependent_flag_col]==0)]) == 0

⚠️ Python Environment Fallback (Windows)

On Windows, the .venv path may not be directly accessible via PowerShell & operator if the repo path contains spaces. Use system Python as a reliable fallback:

# Preferred: system Python (avoids space-in-path issues)
$py = (Get-Command python -ErrorAction SilentlyContinue)?.Source
# Fallback chain if venv is needed:
$candidates = @(
    "$repoRoot\.venv\Scripts\python.exe",
    "C:\Users\$env:USERNAME\AppData\Local\Programs\Python\Python310\python.exe"
)
$py = $candidates | Where-Object { $_ -and (Test-Path $_) } | Select-Object -First 1

Validation Before Output

• All FK values in fact tables exist in corresponding dimension PKs
• CSV column names match TMDL sourceColumn values exactly
• Data types are consistent (integers for keys, decimals for amounts)
• Date dimension covers the full required date range
• SDV metadata declares PK/FK relationships explicitly
• Post-processing repairs any orphan FK values before CSV export
• CSV files are comma-delimited, UTF-8 encoded
• Script runs without errors in the .venv environment
• Period trend is directional (not flat/random) for all time-series KPIs
• Business flag rules enforced (dependent flags are consistent, no contradictions)
• Print validation summary: total rows, KPI% per period, flag distribution

Save all generated artifacts to disk.