Tutorial: Build a semantic view

In this tutorial, you build a semantic view to analyze sales by region. You load sample sales, store, and product data, describe three tables and two relationships, add revenue metrics, and query the results.

Important

This feature is in preview.

In this tutorial, you learn how to:

  • Upload sample CSV files and load them into lakehouse tables.
  • Create a semantic view in a lakehouse.
  • Describe your tables and relationships in a schema file.
  • Define DAX metrics in a metrics file.
  • Connect reports and agents to the generated model.
  • Query the model to verify your sales metrics.

Prerequisites

In this preview, use a workspace without deployment pipeline, Git, or Power Platform solution connections and without workspace-level private links enabled. Check the workspace restrictions before preparing the sample data.

Prepare the sample data

Use the following CSV files from the Microsoft Fabric samples repository. You don't need a notebook, pipeline, or dataflow.

Sample file Destination table in the dbo schema Rows
FactSales.csv factsales 6
DimStore.csv dimstore 3
DimProducts.csv dimproducts 3
  1. Open each sample file link and select Download raw file to save the CSV file.
  2. Open lakehouse_sales. Under Files, create a folder named semantic-view-samples.
  3. Select the folder's ellipsis (...), select Upload > Upload files, and upload the three CSV files.
  4. For each uploaded file, open its context menu and select Load to tables > New table.
  5. Select the dbo schema and enter the destination table name from the preceding table. Select Column header, use a comma (,) as the separator, and select Load.
  6. Under Tables, expand dbo and confirm that factsales, dimstore, and dimproducts exist. Preview each table and check the row counts and columns.

For more information about these steps, see Upload a CSV file to a lakehouse and Load to Delta Lake tables.

The examples use these columns:

Dataset name in YAML Columns used in this tutorial
sales SaleId, StoreId, ProductId, Units, RevenueUSD
stores StoreId, Region
products ProductId, Category

Note

If you use your own data instead, substitute your table and column names throughout the tutorial. The expected results in this article apply only to these sample files.

Step 1: Create the view

  1. Open lakehouse_sales.

  2. On the ribbon, select New semantic view.

    Screenshot of the lakehouse ribbon with New semantic view highlighted.

  3. Enter sales in Semantic view name, and then select Create.

The Semantic Views folder and the sales folder are created at Context/Semantic Views/sales/. The process adds a starter file named sales_semantic_view.yaml with commented examples.

Screenshot of the generated sales_semantic_view.yaml file with required properties and commented examples.

Step 2: Describe your tables

In the Explorer pane, right-click the starter file, select Rename, and rename it to schema.yaml. Open the file and replace its contents with the following definition. It declares three datasets and the two relationships that connect them.

Screenshot of the Rename file dialog with schema.yaml entered.

For supported YAML properties, see Semantic view YAML syntax reference.

version: 0.2.0.dev0
name: sales
description: Sales semantic view tutorial.
datasets:
  - name: sales
    source: dbo.factsales
    description: One row per sales transaction line.
  - name: stores
    source: dbo.dimstore
    description: One row per store, including its region.
  - name: products
    source: dbo.dimproducts
    description: One row per product, including its category.
relationships:
  - name: sales_to_stores
    from: sales
    to: stores
    from_columns:
      - StoreId
    to_columns:
      - StoreId
  - name: sales_to_products
    from: sales
    to: products
    from_columns:
      - ProductId
    to_columns:
      - ProductId

Select Save.

Screenshot of schema.yaml defining the sales, stores, and products datasets and their relationships.

Saving writes the YAML file only. Publishing validates the view's definitions and creates or updates the semantic model that reports and agents query. You publish after adding the metrics in this tutorial. For details, see Publish a semantic view.

Step 3: Define your metrics

In DAX, use each dataset's YAML name, not its underlying source. For example, reference 'sales'[RevenueUSD] for the dataset named sales, whose source is dbo.factsales.

Create a local file named metrics.yaml with the following content, and then upload it to the same view folder. Publishing merges the schema and metrics files into one view.

This tutorial keeps schema and metrics in separate files so you can maintain table and relationship definitions separately from calculations. A single file is also supported. To share schema and metrics independently through OneLake shortcuts, place them in separate subfolders. See Organize the view folder for layouts and sharing options.

This example keeps the model-level description in the schema file. See Document and model for rules when combining files.

version: 0.2.0.dev0
name: sales
metrics:
  - name: Total Revenue
    description: Total sales revenue in US dollars.
    expression:
      dialects:
        - dialect: DAX
          expression: "SUM('sales'[RevenueUSD])"
  - name: Sale Count
    description: Number of distinct sales transactions.
    expression:
      dialects:
        - dialect: DAX
          expression: "DISTINCTCOUNT('sales'[SaleId])"
  - name: Units Sold
    description: Total number of units sold.
    expression:
      dialects:
        - dialect: DAX
          expression: "SUM('sales'[Units])"

Uploaded files are saved automatically.

Screenshot of metrics.yaml defining Total Revenue, Sale Count, and Units Sold.

Step 4: Publish the view

  1. In the Explorer pane, expand Context > Semantic Views.

  2. Right-click the sales folder, and then select Publish.

    Screenshot of the sales folder with schema.yaml and metrics.yaml and the Publish command highlighted.

  3. In the confirmation dialog, select Publish.

Publishing combines both files and creates the sales semantic model.

The first publish might take a while. Wait for the publish result before continuing.

Screenshot of the Semantic view published dialog confirming that the generated output and read-only model are available.

If publishing fails, manually correct the reported errors in the source files, save, and retry Publish. Review any warnings before continuing. For details, see Check the result.

Step 5: Check the model

Inspect the generated model. Opening web modeling requires model Write permission. The model inherits this permission from the lakehouse Write permission required for this tutorial. The model opens for inspection, not editing.

  1. Expand SystemContext > Semantic Views.

  2. Right-click the sales folder, and then select Open in model view.

    Screenshot of the generated sales folder menu with Validate in DQV and Open in model view highlighted.

Confirm three tables, two relationships, and a Metrics table that contains your three measures.

Screenshot of the sales model with sales, stores, and products tables, two relationships, and three measures in the Metrics table.

To test a metric in DAX query view (DQV), select Validate in DQV on the same folder and run a query:

EVALUATE
SUMMARIZECOLUMNS(
    'stores'[Region],
    "Revenue", [Total Revenue],
    "Sales", [Sale Count],
    "Units", [Units Sold]
)
ORDER BY 'stores'[Region]

Confirm that the results match the sample data.

Region Revenue Sales Units
France 302 2 56
Germany 290 2 53
Netherlands 263 2 49

Screenshot of the DAX query and matching revenue, sales, and units results for France, Germany, and Netherlands.

To check the product relationship, replace 'stores'[Region] with 'products'[Category] in both the grouping and the ORDER BY clause. The expected results are:

Category Revenue Sales Units
Ice Cream 747 5 140
Sorbet 108 1 18

Screenshot of the DAX query grouped by product category with matching results for Ice Cream and Sorbet.

If results don't match, check the loaded row counts, numeric column types, and relationship columns before continuing.

Step 6: Query the view

The generated semantic model is what reports and agents connect to.

  • From a data agent, select Add data source, then select the sales semantic model.
  • From Power BI, create a live connection to the model and build a report on the published metrics.

For details on both paths, see Consume semantic views.

Step 7: Change a metric

Add another metric and publish the updated definition:

  1. Open metrics.yaml in the view folder and add the following entry under the existing metrics list:

      - name: Average Revenue per Sale
        description: Revenue in US dollars divided by the number of sales.
        expression:
          dialects:
            - dialect: DAX
              expression: "DIVIDE([Total Revenue], [Sale Count])"
    

    Screenshot of metrics.yaml with the new Average Revenue per Sale metric highlighted.

  2. Select Save.

  3. Right-click the view folder under Context, select Publish, then confirm Publish in the dialog.

Confirm that Average Revenue per Sale appears in the model. Select Validate in DQV and run:

EVALUATE
SUMMARIZECOLUMNS(
    "Average Revenue per Sale", [Average Revenue per Sale]
)

With no filters, its value is 142.5 (855 divided by 6). Reports and agents connected to this model can now use it.

Screenshot of a DAX query returning 142.5 for Average Revenue per Sale.

Clean up resources

If you don't want to keep what you built, delete both pieces:

  1. Delete the sales folder under Context/Semantic Views.
  2. Delete the sales semantic model from the workspace.

Deleting the folder alone doesn't delete the model or immediately remove its generated output in SystemContext. For generated-output cleanup and the limitation when deleting the last view in a lakehouse, see Delete a semantic view.

If you created lakehouse_sales only for this tutorial, delete the lakehouse when you're finished. If you used an existing lakehouse instead, delete only the sample tables and the semantic-view-samples folder you created for this tutorial. Don't delete tables or files used by other workloads.