Test: Disable automatic updates of Direct Lake Semantic Model

[frithjof_v]

Just sharing this step-by-step test I did to show the effect of disabling automatic refresh in Direct Lake semantic model.

1. Create star schema in Warehouse, and insert dummy data.

-- Drop existing tables if needed (optional)
DROP TABLE IF EXISTS SalesFact;
DROP TABLE IF EXISTS ProductDimension;
DROP TABLE IF EXISTS CustomerDimension;
DROP TABLE IF EXISTS StoreDimension;
DROP TABLE IF EXISTS TimeDimension;

-- Create Dimension Tables with CreatedAt column
CREATE TABLE ProductDimension (
    ProductID INT,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    LastUpdate DATETIME2(6)
);

CREATE TABLE CustomerDimension (
    CustomerID INT,
    CustomerName VARCHAR(100),
    Email VARCHAR(100),
    City VARCHAR(50),
    LastUpdate DATETIME2(6)
);

CREATE TABLE StoreDimension (
    StoreID INT,
    StoreName VARCHAR(100),
    Location VARCHAR(100),
    LastUpdate DATETIME2(6)
);

CREATE TABLE TimeDimension (
    TimeID INT,
    Date DATE,
    Year INT,
    Month VARCHAR(20),
    Quarter VARCHAR(20),
    LastUpdate DATETIME2(6)
);

-- Create Fact Table with CreatedAt column
CREATE TABLE SalesFact (
    SaleID INT,
    ProductID INT,
    CustomerID INT,
    StoreID INT,
    TimeID INT,
    SalesAmount DECIMAL(10, 2),
    Quantity INT,
    LastUpdate DATETIME2(6)
);

-- Populate Dimension Tables with Dummy Data
INSERT INTO ProductDimension (ProductID, ProductName, Category, Price, LastUpdate)
VALUES 
    (1, 'Laptop', 'Electronics', 1200.00, GETDATE()),
    (2, 'Phone', 'Electronics', 800.00, GETDATE()),
    (3, 'Tablet', 'Electronics', 600.00, GETDATE());

INSERT INTO CustomerDimension (CustomerID, CustomerName, Email, City, LastUpdate)
VALUES 
    (1, 'Alice Smith', 'alice@example.com', 'New York', GETDATE()),
    (2, 'Bob Johnson', 'bob@example.com', 'Los Angeles', GETDATE()),
    (3, 'Charlie Davis', 'charlie@example.com', 'Chicago', GETDATE());

INSERT INTO StoreDimension (StoreID, StoreName, Location, LastUpdate)
VALUES 
    (1, 'Tech Store NY', 'New York', GETDATE()),
    (2, 'Tech Store LA', 'Los Angeles', GETDATE()),
    (3, 'Tech Store CH', 'Chicago', GETDATE());

INSERT INTO TimeDimension (TimeID, Date, Year, Month, Quarter, LastUpdate)
VALUES 
    (1, '2024-01-01', 2024, 'January', 'Q1', GETDATE()),
    (2, '2024-02-01', 2024, 'February', 'Q1', GETDATE()),
    (3, '2024-03-01', 2024, 'March', 'Q1', GETDATE());

-- Populate Fact Table with Dummy Data
INSERT INTO SalesFact (SaleID, ProductID, CustomerID, StoreID, TimeID, SalesAmount, Quantity, LastUpdate)
VALUES 
    (1, 1, 1, 1, 1, 1200.00, 1, GETDATE()),
    (2, 2, 2, 2, 2, 1600.00, 2, GETDATE()),
    (3, 3, 3, 3, 3, 1800.00, 3, GETDATE());

2. Configure a custom direct lake semantic model

3. Include a DAX measure in the semantic model. This DAX measure displays the current time when rendering the report:

__Time Now = NOW()

4. Create Power BI report

5. Disable automatic updates in the semantic model's settings

6. TRUNCATE and INSERT new data in the ProductDimension table

-- Truncate the ProductDimension table
TRUNCATE TABLE ProductDimension;

-- Insert new data into ProductDimension table
INSERT INTO ProductDimension (ProductID, ProductName, Category, Price, LastUpdate)
VALUES 
    (1, 'Gaming Laptop', 'Electronics', 1500.00, GETDATE()),
    (2, 'Smartphone', 'Electronics', 950.00, GETDATE()),
    (3, 'E-Reader', 'Electronics', 250.00, GETDATE()),
    (4, 'Smartwatch', 'Electronics', 400.00, GETDATE()),
    (5, 'Wireless Earbuds', 'Accessories', 150.00, GETDATE());

7. Verify data has been updated in ProductDimension table in the Warehouse

8. Verify that Power BI report is still pointing to the previous version (previous state) of the Delta Table. We can see that the report is not displaying the new ProductDimension data, but the previous version - as expected.

Click the Refresh visuals button in the report, to make sure you re-render the report visuals. 

9. Manually refresh the direct lake semantic model, so that it will be reframed to point to the most recent version of the Delta Table.

(There is a 1-hour offset between UTC time and CET time, that's the reason why the time looks 1 hour different in the Refresh history and the other images).

10. Verify that the Power BI report now shows the current version of the Delta Table, after refreshing (reframing) the direct lake semantic model - as expected.

Summary

The results are as expected, ref. the docs:

Direct Lake overview - Microsoft Fabric | Microsoft Learn

[v-shex-msft]

Hi @frithjof_v ,

Thanks for your sharing.

Regards,

Xiaoxin Sheng

[lbendlin]

This should be a blog entry? The content is too valuable to be washed away in the forum.

[frithjof_v]

Thanks!

[AndyDDC]

Are you able to post blogs (or ask for access) in the new community blogs section?

Fabric community blogs - Microsoft Fabric Community

[frithjof_v · Accepted solution]

Thanks, I asked for access now 🙂