Please enable JavaScript.
Coggle requires JavaScript to display documents.
L3: End-to-End Analytics using Microsoft Fabric - Coggle Diagram
L3: End-to-End Analytics using Microsoft Fabric
DESIGNING SEMANTIC MODELS FOR SCALE
STORAGE MODES
DIRECT LAKE MODE
Default storage mode for semantic models created in Fabric. Reads delta tables directly from OneLake into memory, combining the speed of import with the freshness of directquery
Unlike
import mode
,it doesn't copy data into the model
Unlike
direct query mode
, it doesn't translate queries into source SQL
.
Direct Lake models can connect via
OneLake tables
directly (i.e. connecting to one table directly in a lakehouse or warehouse) OR via the
SQLA Endpoint
(enabling access to views, cross DB queries and security features in the SQL layer)
Choose OneLake tables when your data is one place & is straightforward; choose SQLA when you need views or cross DB queries
.
Direct Lake may fallback to DirectQuery when complex DAX, memory limits, or unsupported operations occur; configure via semantic model options (Allow fallback vs Disallow fallback) and start with Allow fallback, then monitor and optimise to reduce fallbacks over time.
IMPORT MODE
Copies data into the semantic model then stores it in a compressed, in-memory format. Queries run against the local copy of data; fastest model performance; data only as recent as last refresh
Use when you're getting data from a non-Fabric source like on-prem DB
DIRECTQUERY MODE
No data stored in the model, sends queries to the data source at query time. Suitable for real-time scenarios and very large datasets that can't be imported; Not performant as every report interaction generates a query against the source system
Use when you need real-time updates, or have very large data that can't be imported
COMPOSITE MODE
Combines import and directquery storage modes within a single model. Provides flexibility for scenarios where different tables have different performance and freshness needs
Use when you need data from both fabric and non-fabric sources in the same model or when some tables requrie real-time data while other benefit from cached performance
DESIGN STAR SCHEMA FOR SEMANTIC MODELS
Strive to use star schema within your semantic model:
One-to-many from dimension to fact tables, with filter direction flowing from the dimension to the fact table
Use single filter direction; only use bidirectional when dimension tables need to be filtered by values in the fact table (Bi-directional impacts performance)
Referential integrity: assume referential integrity setting tells the PBI engine to use inner join rather than left joins when querying across a relationship
In Direct Lake/Query mode, this significantly improves performance by reducing the # of rows that'll be processed
HANDLING SNOWFLAKE SCHEMAS
Can be done in one of two ways:
FLATTEN INTO STAR SCHEMA
Combine the normalised dim table into a single denormalised dimension table, eliminating the extra tables and relationships
Give copilot clearer paths to the right data with fewer relationships
PRESERVE THE SNOWFLAKE STRUCTURE
Do this if you need to apply RLS at specific levels in the dim hierarchy; you have multiple fact tables that share subdimension tables
Ensure that each relationship uses single-direction filtering from outermost table toward the fact table
CONFIGURE SETTINGS FOR SCALE
Use
Large semantic model storage format
to get rid of the 10GB limit. Direct lake models automatically enable this setting; its a pre-requisite for XMLA endpoint read/write access and query scaleout
XMLA endpoint read/write access:
lets external tools connect to your semantic model (e.g. DAX studio)
Query scaleout:
Distributes read queries across read-only replicas of your semantic model to share the query load. Helps when you have hundreds of concurrent users querying a model
Direct Lake Fallback Configuration:
Decide whether to allow fallback or not. Always start with fallback allowed for models at scale
OneLake Integration:
Makes the semantic model accessible as delta tables in OneLake, allowing downstream items like notebooks to access their data and be used as a shared data source across fabric
DESIGN SCALABLE CALCULATIONS
CALCULATION GROUPS
Reduce measure multiplication by allowing you to apply the same calculation pattern across multiple measures. Instead of creating measures for each variation, define the pattern once and apply it dynamically
Use calculation groups when you have >3 measures that need the same calculation pattern applied
SCALABLE DAX & IMPROVING READABILITY
Use variables in your DAX calculations, to ensure that intermediate results are re-used
Have consistent naming conventions; use abbreviations that everyone can understand; name items based on what they do
Apply information functions as a defensive pattern (e.g. ISBLANK, HASONEVALUE etc.) to prevent unexpected results when measures are used in contexts the original author didn't anticpate
Utilise aggregations (summarising tables at higher grain than the detail table e.g. holdings per client rather per instrument)
OPTIMISING SEMANTIC MODEL PERFORMANCE
Reducing cardinality
(number of unique values in a column) in the model for better performance
GUIDs or surrogate keys/unique identifiers that aren't used for relationships or reports
Timestamps with high precision
Free text or description columns
.
Apply reduction strategies
Remove unused columns
Reduce time precision
Bucket continuous values (e.g. instead of storing exact ages, create age bands)
Remove unnecessary rows (e.g. historical data no longer needed for reporting
Optimise column data types: Vertipaq engine is better at encoding numeric data rather than text/varchar data. If a column like order number is stored as text with a prefix (such as "SO123456"), consider removing the prefix and storing it as a number
.
Aggregate large fact tables where appropriate
Enable automatic aggregations for DirectQuery/Lake tables
↑ These decisions should be driven by business requirements
BEST PRACTICES FOR PERFORMANCE MEASUREMENT:
Clear the visual cache before every test
Isolate variables, i.e. test one thing at a time
Use production sized data and typical filter selections
Record timing results before and after changes so you can quantify improvement
Focus on commonly used visuals, compare relative timings across them
Identify the bottleneck category: A visual with 4,800 ms in DAX query time and 200 ms in visual display has a data model or measure issue, not a rendering issue.
OPTIMISING DAX CALCULATIONS
Use variables to eliminate repeated calculations
Avoid using FILTER on entire tables when a column predicate achieves the same result
Avoid expensive patterns:
Any functions that have to iterate over an entire table to match a specific condition
Nested CALCULATE with complex filters
Always try to move calculations to the source
PERFORMANCE ANALYSER
Diagnostic tool built to identify what is slow in a report and why
DAX optimisation tends to yield the most benefit
.
Focus on the largest contributor first:
If DAX Query time dominates, the problem is in the model or the measure
If visual display time is high, the visual itself is rendering too much data
If direct query time is large, the external data source or query folding may be the issue
TROUBLESHOOTING COMMON PERFORMANCE ISSUES
Start by identifying the issue, isolating the root cause, fixing and verifying (
Symptom -> diagnosis -> fix -> verify
worflow)
Run best practice analyser to catch common issues
Address complex visuals:
Too many measures on one visual; reduce the number of measures or split the info across multiple visuals
Apply Top N filters to limit the number of rows returned on large tables
Avoid having too many visuals on one page; use drillthrough pages and tooltips to distribute info
Avoid bidirectional cross-filtering
For directquery models, verify query folding and source performance (work with DBAs to optimise source queries)
ENFORCE SEMANTIC MODEL SECURITY
ROW LEVEL SECURITY
Restrict the
ROWS
of data individual users can see when they query a semantic model
Defined by creating roles that contain DAX filter expressions (e.g. Role 1 can only see the USA etc)
Applies to all data consumption paths (PBI, paginated reports using semantic model, Copilot etc)
In Star schema models, you create filter expressions on dim tables and relationship then propagates this filter to the fact table
Instead of creating roles for each user, you can use dynamic security with the USERPRINCIPALNAME() function which returns the mail address of the authenticated user which can then be matched against a column for filtering
If the DirectQuery source supports SSO, the source DB can enforce its own RLS without the need for it in the semantic model
You can also create a security table with user information and map that table to a dim table for filter propagation. The RLS DAX filter will be checking for any rows that contain anything fields in the security table
OBJECT LEVEL SECURITY
Restricts access to specific
TABLES
&
COLUMNS
in a semantic model
Designed for scenarios where certain data elements contain sensitive information that some users shouldn't access, e.g. PII or payroll financial data
Can't be configured in PBI desktop, have to use Tabular Editor to define OLS rules
OLS LIMITATIONS:
Measures can't be hidden directly
OLS applies only to users with Viewer permissions, Users with higher permissions have edit permission & bypass OLS
Don’t secure a table if it breaks relationship chains (e.g. A→B→C); secure its columns instead while keeping the table accessible
Error experience, PBI just says that the object can't be found rather than that you don't have access
SEMANTIC MODEL DEVELOPMENT LIFECYCLE
Lifecycle stages:
Develop
->
Validate
->
Deploy
->
Monitor
RESUSABLE ASSETS
-
Develop stage
Shared semantic models:
Think Consolidated holdings, centralised definition of business metrics
Power BI Templates:
Captures the complete structure of an existing report without including data; can pass parameters to get specific instances of a report depending on user
VERSION CONTROL
-
Develop stage
Power BI Desktop Projects:
a folder‑based, text‑first format that breaks a report/dataset into editable files (JSON/M/DAX) so you can diff, merge and version in source control—enabling branching and CI/CD workflows versus opaque .pbix binaries
Enable Git integration for a workspace:
Connect a Fabric workspace to a Git repo in ADO, changes to workspace items sync between the repo and workspace
XMLA ENDPOINT
-
Validate stage
Connect to the semantic model through the XMLA endpoint to inspect metadata, test processing and run validation checks before deployment—helping catch model issues early and support version-controlled, automated QA.
DEPLOYMENT PIPELINES
-
Deploy stage
Deployment pipelines promote content through staged workspaces (default Dev → Test → Prod; 2–10 stages) to prevent untested changes reaching users.
Pipelines show side‑by‑side comparisons of items, allow selective deployment, and copy selected items to the target workspace.
Use deployment rules (data source and parameter rules) to swap environment‑specific connections/configs; configure rules before the first deployment.
Support automation via the Fabric REST API (CI/CD patterns) and complement Git (Git = source control; pipelines = environment promotion).
MAINTAIN & MONITOR SEMANTIC MODELS
-
Monitor stage
Scheduled refresh:
The standard refresh way applies, & for complex refresh patterns, use a data factory pipeline with a semantic model refresh activity. This allows you to orchestrate multiple steps in a sequence
Use
Monitoring Hub
in the Fabric portal to see the status of all running & recent activities in the workspace, investigate refresh failures
Use
Lineage View
to troubleshoot how things in a workspace connect to each other