Example: Add a Dynamics 365 Sales table to a report

Important

Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.

This example shows how to add a new Dynamics 365 Sales table to Nonprofit data solutions and then expose it in the Fundraising Intelligence reporting layer.

The scenario in this article adds the Dynamics 365 Sales goal table and surfaces it as DimGoal.

Scenario

This example uses these source columns from the bronze goal table.

Bronze column	Silver or gold column	Type	Purpose
goalid	GoalId	String (GUID)	Primary key
title	Name or GoalName	String	Goal name
goalstartdate	StartDate	Timestamp	Goal start
goalenddate	EndDate	Timestamp	Goal end
targetmoney	TargetAmount	Decimal	Target amount

This example follows this data flow:

Dynamics 365 Sales goal
-> Bronze
-> Silver Goal
-> Gold DimGoal
-> Semantic model
-> Report

Architecture overview

Nonprofit data solutions uses a medallion architecture with three layers.

┌─────────────────────────────────────────────────────────────────────┐
│                         BRONZE LAYER                                │
│  Dataverse Link -> goal, contact, msnfp_transaction, ...            │
│  (Raw data from Dynamics 365 Sales)                                 │
└─────────────────────────────────────────────────────────────────────┘
                                    │
                    Fundraising_D365_Transform.Notebook
                    (Watermark-based incremental sync)
                                    ▼
┌─────────────────────────────────────────────────────────────────────┐
│                         SILVER LAYER                                │
│  Goal, Contact, Transaction, Campaign, ...                          │
│  (Cleaned, typed, standardized with GUIDs)                          │
│  + SourceSystemIdMapping (Bronze ID -> Silver GUID)                 │
│  + WatermarkState (incremental sync tracking)                       │
└─────────────────────────────────────────────────────────────────────┘
                                    │
                    Fundraising_SL_GD_Enrichment.Notebook
                    (CDF-based incremental sync)
                                    ▼
┌─────────────────────────────────────────────────────────────────────┐
│                          GOLD LAYER                                 │
│  DimGoal, DimConstituent, FactDonation, ...                         │
│  (Star schema: Dimension + Fact tables)                             │
│  + DateKey columns (FK to DimDate)                                  │
│  + Surrogate keys (xxhash64-based)                                  │
└─────────────────────────────────────────────────────────────────────┘
                                    │
                         Semantic Model (Direct Lake)
                                    ▼
┌─────────────────────────────────────────────────────────────────────┐
│                           REPORT                                    │
│  Power BI visuals using DimGoal                                     │
└─────────────────────────────────────────────────────────────────────┘

Key concepts for this pattern include:

• Silver tables use natural primary keys such as GoalId.
• Gold tables use surrogate keys such as GoalKey.
• *DateKey columns join to DimDate for time intelligence.
• SourceKey joins to DimSource for multi-source traceability.
• Bronze-to-silver processing uses watermark-based incremental sync.
• Silver-to-gold processing uses Change Data Feed (CDF).

Important

Before this table appears in the bronze lakehouse, add it to Azure Synapse Link for Dataverse and make sure Dataverse change tracking is enabled for the table.

Add the silver schema

Open the Fundraising_SL_CreateSchema notebook and add a new schema method.

@staticmethod
def get_goal_schema() -> StructType:
    return StructType([
        StructField("GoalId", StringType(), nullable=False, metadata={ "primaryKey": True, "pkType": "guid" }),
        StructField("Name", StringType()),
        StructField("StartDate", TimestampType()),
        StructField("EndDate", TimestampType()),
        StructField("TargetAmount", DecimalType(16, 8)),
        StructField("CreatedDate", TimestampType()),
        StructField("ModifiedDate", TimestampType()),
        StructField("SourceId", StringType(), nullable=False),
        StructField("SourceSystemId", StringType())
    ])

Always include CreatedDate, ModifiedDate, SourceId, and SourceSystemId in new silver tables. The framework uses these fields as part of the standard sync pattern.

Register the table in get_entities().

def get_entities(self) -> list[tuple[StructType, str, bool]]:
    return [
        # ... existing tables ...
        (NonprofitSilverModel.get_goal_schema(), "Goal", True),
    ]

Set the third parameter to True so the table supports CDF processing in the downstream gold layer.

If you use the optional tables_to_delete pattern for preproduction schema resets, add Goal there as well.

Note

The enable_delete_tables pattern is intended only for preproduction schema experiments. Schema notebooks create new tables automatically on existing deployments, so manual table creation isn't required.

Add the gold schema

Open the Fundraising_GD_CreateSchema notebook and add a schema method for DimGoal.

@staticmethod
def get_dimgoal_schema() -> StructType:
    return StructType([
        StructField("GoalKey", LongType(), nullable=False, metadata={ "primaryKey": True }),
        StructField("GoalId", StringType()),
        StructField("GoalName", StringType()),
        StructField("TargetAmount", DecimalType(18, 4)),
        StructField("StartDateKey", LongType()),
        StructField("EndDateKey", LongType()),
        StructField("CreatedDateKey", LongType()),
        StructField("ModifiedDateKey", LongType()),
        StructField("SourceKey", LongType()),
        StructField("SourceSysGoalId", StringType())
    ])

Gold tables generally use surrogate keys, date keys, and source traceability columns instead of reusing the operational shape from silver.

Common gold patterns in this solution are:

• *Key columns for surrogate keys
• *DateKey columns for DimDate joins
• SourceKey for DimSource
• SourceSys*Id for source-system traceability

Register the table in get_entities().

def get_entities(self) -> list[tuple[StructType, str, bool]]:
    return [
        # ... existing tables ...
        (NonprofitGoldModel.get_dimgoal_schema(), "DimGoal", True),
    ]

If you use the optional tables_to_delete pattern for preproduction schema resets, add DimGoal there as well.

Note

Keep enable_delete_tables turned off in production. When enabled, it drops and recreates tables, which deletes existing data.

Add the bronze-to-silver transformation

Open the Fundraising_D365_Transform notebook and add a new transformation block.

from pyspark.sql import DataFrame
from pyspark.sql.functions import col
from pyspark.sql.types import DecimalType, TimestampType

def transform_goal(df: DataFrame) -> DataFrame:
    return df.select(
        col("Id"),
        col("title").alias("Name"),
        col("goalstartdate").cast(TimestampType()).alias("StartDate"),
        col("goalenddate").cast(TimestampType()).alias("EndDate"),
        col("targetmoney").cast(DecimalType(16, 8)).alias("TargetAmount"),
        col("createdon").cast(TimestampType()).alias("CreatedDate"),
        col("modifiedon").cast(TimestampType()).alias("ModifiedDate")
    )

data_sync.sync_table(
    source_table="goal",
    source_primary_key="Id",
    source_columns=[
        "Id", "title", "goalstartdate", "goalenddate", "targetmoney",
        "createdon", "modifiedon"
    ],
    target_table="Goal",
    target_primary_key="GoalId",
    transform_func=transform_goal
)

Key parameters in this pattern:

• source_table is the bronze table name.
• source_primary_key is the source system primary key, typically Id for Dynamics 365 Sales.
• source_columns defines the bronze fields the sync reads.
• target_table is the silver table name.
• target_primary_key is the natural key column used in silver.
• transform_func converts the bronze shape into the silver schema.

Add the silver-to-gold transformation

Open the Fundraising_SL_GD_Enrichment notebook and add a new section for DimGoal.

from pyspark.sql.functions import col, expr, xxhash64

def EnrichDimGoal(df: DataFrame) -> DataFrame:
    dim_source = get_gold_table("DimSource").select("SourceId", "SourceKey")
    dim_date = get_gold_table("DimDate").select("Date", "DateKey")

    date_start = dim_date.select(
        col("Date").alias("StartDate_lookup"),
        col("DateKey").alias("StartDateKey")
    )
    date_end = dim_date.select(
        col("Date").alias("EndDate_lookup"),
        col("DateKey").alias("EndDateKey")
    )
    date_created = dim_date.select(
        col("Date").alias("CreatedDate_lookup"),
        col("DateKey").alias("CreatedDateKey")
    )
    date_modified = dim_date.select(
        col("Date").alias("ModifiedDate_lookup"),
        col("DateKey").alias("ModifiedDateKey")
    )

    return (
        df
        .join(dim_source, on="SourceId", how="left")
        .join(date_start, expr("cast(StartDate as date) = StartDate_lookup"), "left")
        .join(date_end, expr("cast(EndDate as date) = EndDate_lookup"), "left")
        .join(date_created, expr("cast(CreatedDate as date) = CreatedDate_lookup"), "left")
        .join(date_modified, expr("cast(ModifiedDate as date) = ModifiedDate_lookup"), "left")
        .withColumn("GoalKey", xxhash64("GoalId", "SourceSystemId").cast("bigint"))
        .select(
            "GoalKey",
            "GoalId",
            col("Name").alias("GoalName"),
            col("TargetAmount").cast("decimal(18,4)"),
            "StartDateKey",
            "EndDateKey",
            "CreatedDateKey",
            "ModifiedDateKey",
            "SourceKey",
            col("SourceSystemId").alias("SourceSysGoalId")
        )
    )

goalTable = CdfTable(
    source_table_name="Goal",
    source_primary_key="GoalId",
    target_table_name="DimGoal",
    columns=[
        "GoalId", "Name", "TargetAmount", "StartDate", "EndDate",
        "CreatedDate", "ModifiedDate", "SourceId", "SourceSystemId"
    ],
    merge_sql_template=f"""
    MERGE INTO {gold_lakehouse_name}.DimGoal AS target
    USING latestSnapshot_Goal AS source
    ON target.GoalId = source.GoalId
    WHEN MATCHED THEN UPDATE SET
        GoalName = source.GoalName,
        TargetAmount = source.TargetAmount,
        StartDateKey = source.StartDateKey,
        EndDateKey = source.EndDateKey,
        CreatedDateKey = source.CreatedDateKey,
        ModifiedDateKey = source.ModifiedDateKey,
        SourceKey = source.SourceKey,
        SourceSysGoalId = source.SourceSysGoalId
    WHEN NOT MATCHED THEN INSERT (
        GoalKey, GoalId, GoalName, TargetAmount, StartDateKey, EndDateKey,
        CreatedDateKey, ModifiedDateKey, SourceKey, SourceSysGoalId
    ) VALUES (
        source.GoalKey, source.GoalId, source.GoalName, source.TargetAmount,
        source.StartDateKey, source.EndDateKey, source.CreatedDateKey,
        source.ModifiedDateKey, source.SourceKey, source.SourceSysGoalId
    )
    """,
    source_lakehouse=silver_lakehouse_name,
    target_lakehouse=gold_lakehouse_name,
    enrich_func=EnrichDimGoal,
    hard_delete=True
)

ProcessCdfTable(goalTable)

Key patterns used here:

• xxhash64() creates a deterministic surrogate key.
• CdfTable defines how Change Data Feed processing should work.
• ProcessCdfTable() executes the incremental silver-to-gold sync.
• hard_delete=True removes rows from gold when they're deleted in silver.

Run the pipeline

After you complete the notebook changes, run the Fundraising_Orchestration pipeline so that the schema, transformation, and enrichment notebooks execute in sequence.

Verify the data

Verify the silver table

1. Open the silver lakehouse.
2. Open the Goal table.
3. Confirm the columns exist and that data is loaded.

Verify the gold table

1. Open the gold lakehouse.
2. Open the DimGoal table.
3. Confirm that GoalKey, the *DateKey columns, and SourceKey are populated.

Add the table to the semantic model

The semantic model doesn't automatically import new lakehouse tables.

1. Open Power BI Desktop.
2. Select Home > OneLake Catalog > Lakehouse.
3. Connect to the gold lakehouse.
4. Select DimGoal.
5. Load the table by using DirectQuery mode.
6. If needed, create relationships in Model view.
7. Publish the updated semantic model to your workspace.
8. Overwrite the existing Fundraising_Intelligence_Semantic model.

If you don't complete this step, reports can't use the new DimGoal table because the semantic model doesn't automatically discover new lakehouse tables.

Use the table in reports

You can now use DimGoal for report filters, measures, and visuals.

Example measures:

Total Goal Target = SUM(DimGoal[TargetAmount])

Active Goals =
VAR TodayKey = VALUE(FORMAT(TODAY(), "yyyymmdd"))
RETURN
COUNTROWS(
    FILTER(
        DimGoal,
        DimGoal[StartDateKey] <= TodayKey &&
        DimGoal[EndDateKey] >= TodayKey
    )
)

Example visuals:

• Goal progress cards
• Goals by time period
• Goal comparison bar charts

Choose the right table pattern

Use the existing table patterns in Nonprofit data solutions as a guide.

Table type	Silver name	Gold name	Typical use case
Dimension	Goal	DimGoal	Reference/lookup data (goals, campaigns, and similar entities)
Fact	Transaction	FactDonation	Transactional or event data with measures
Bridge	CampaignChannel	DimCampaignChannelBridge	Many-to-many relationships
Data mart	Not applicable	dm_Constituent	Preaggregated analytical output

Add foreign keys when needed

If the new source table contains foreign keys, add them through the fk_mappings pattern.

For example, if goalownerid should resolve to a silver Contact row, update both the transform function and the sync configuration.

def transform_goal(df: DataFrame) -> DataFrame:
    return df.select(
        col("Id"),
        col("title").alias("Name"),
        col("goalstartdate").cast(TimestampType()).alias("StartDate"),
        col("goalenddate").cast(TimestampType()).alias("EndDate"),
        col("targetmoney").cast(DecimalType(16, 8)).alias("TargetAmount"),
        col("goalownerid_SilverRecordId").alias("OwnerId"),
        col("createdon").cast(TimestampType()).alias("CreatedDate"),
        col("modifiedon").cast(TimestampType()).alias("ModifiedDate")
    )

data_sync.sync_table(
    source_table="goal",
    source_primary_key="Id",
    source_columns=[
        "Id", "title", "goalstartdate", "goalenddate", "targetmoney",
        "goalownerid", "createdon", "modifiedon"
    ],
    target_table="Goal",
    target_primary_key="GoalId",
    transform_func=transform_goal,
    fk_mappings={
        "goalownerid": "Contact"
    }
)

The framework resolves foreign keys by reading the bronze foreign key, looking it up in SourceSystemIdMapping, and then exposing the resolved value with the _SilverRecordId suffix for your transform function.

For polymorphic lookups, follow the existing helper patterns in Fundraising_D365_Transform, such as resolve_polymorphic_lookup() and resolve_constituent_id().

Troubleshoot common issues

The table doesn't appear in gold

Check these items:

• The silver Goal table contains rows, for example by checking SELECT * FROM silver_lakehouse.Goal LIMIT 10.
• The WatermarkState table contains an entry for goal, for example by checking SELECT * FROM silver_lakehouse.WatermarkState WHERE source_table_name = 'goal'.
• You reran the orchestration pipeline after the notebook changes.

The merge fails with an unresolved expression

The target gold table is missing a column. Add the missing column before rerunning the pipeline.

spark.sql(f"ALTER TABLE {gold_lakehouse_name}.DimGoal ADD COLUMN NewColumn STRING")

No data flows to silver

Verify these items:

• The Dataverse table is enabled in Azure Synapse Link for Dataverse.
• Dataverse change tracking is enabled for the source table.
• The bronze table contains rows.
• The bronze column names used in source_columns match the Dataverse Link output, which is typically lowercase.
• The source_columns list includes the required source columns.

See also

• Extend Nonprofit data solutions
• Extend data pipelines from one layer to another
• Customize Dynamics 365 data ingestion
• Example: Add a Dynamics 365 Sales column to a report
• Example: Add a Dynamics 365 Sales optionset to a report