Calling the Fabric Lakehouse SQL Analytics Endpoint from Power BI Desktop: Connecting, Writing Measures, and Publishing a Report Without Direct Lake | Wicked Smart Data
Calling the Fabric Lakehouse SQL Analytics Endpoint from Power BI Desktop: Connecting, Writing Measures, and Publishing a Report Without Direct Lake
Learn how to connect Power BI Desktop to a Fabric lakehouse SQL Analytics Endpoint, build a star schema with DAX measures, and publish a production-ready report — no Direct Lake required. This lesson explains every step from finding the connection string to scheduling refreshes, with clear guidance on when to use this approach over Direct Lake mode.
🌱 Foundation21 min readSep 22, 2026Updated Sep 22, 2026
Calling the Fabric Lakehouse SQL Analytics Endpoint from Power BI Desktop: Connecting, Writing Measures, and Publishing a Report Without Direct Lake
Introduction
Here's a scenario that comes up constantly in real Fabric projects: you've built a beautiful lakehouse with well-structured Delta tables, your data is clean, your pipelines are running on schedule — and now someone from the business side opens Power BI Desktop and asks "so how do I connect to this thing?" The answer is almost always the SQL Analytics Endpoint, and understanding why that answer is correct, and exactly how to execute it, will save you hours of confusion.
The SQL Analytics Endpoint is a read-only SQL interface that Fabric automatically generates for every lakehouse. It exposes your Delta tables as if they were tables in a SQL Server database, which means Power BI Desktop can connect using the familiar SQL Server connector. You don't need to set up any additional infrastructure, you don't need Direct Lake mode, and you don't need to be a Spark expert. You just need the endpoint URL, a few well-chosen DAX measures, and a solid understanding of how this connection behaves at query time.
By the end of this lesson, you'll be able to connect Power BI Desktop to a Fabric lakehouse's SQL Analytics Endpoint, import or query data in DirectQuery mode, write meaningful DAX measures on top of that data, and publish a finished report to a Fabric workspace. You'll also understand the trade-offs of this approach versus Direct Lake — so you can make an informed choice for your own projects.
What you'll learn:
What the SQL Analytics Endpoint is and how to find its connection string in Fabric
How to connect Power BI Desktop using both Import and DirectQuery mode
How to build a useful semantic model with relationships and DAX measures
How to publish the report back to a Fabric workspace
When to choose this approach over Direct Lake mode
Power BI Desktop installed on your machine (the most recent version from the Microsoft Store or the Power BI download page).
A basic familiarity with the Power BI Desktop interface — you've seen the Report, Data, and Model views before.
Understanding the SQL Analytics Endpoint
Before you click a single button, it's worth understanding what you're actually connecting to.
When you load data into a Fabric lakehouse — whether through a pipeline, a Spark notebook, or a Dataflow Gen2 — the data lands as Delta tables stored on OneLake. Delta is a storage format, not a database engine. By itself, it doesn't speak SQL. The SQL Analytics Endpoint is Fabric's way of bridging that gap: it's a serverless SQL engine that sits in front of your Delta tables and translates T-SQL queries into file reads against OneLake.
Think of it like this: your Delta tables are books in a library. OneLake is the library building. The SQL Analytics Endpoint is the librarian — you tell the librarian what you want in plain language (SQL), and the librarian fetches the right pages from the right books and returns them to you. You never have to know how the books are physically organized on the shelves.
This endpoint is distinct from the Fabric Data Warehouse, which is a full read-write SQL engine. The SQL Analytics Endpoint for a lakehouse is read-only, which is exactly what Power BI needs. You can learn more about where each fits in your architecture in Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload.
Key insight
The SQL Analytics Endpoint uses the same SQL Server protocol (TDS) that Power BI Desktop already knows how to speak. That's why the connection process feels familiar — you're not doing anything exotic. You're just pointing a SQL Server connection at a Fabric-hosted endpoint instead of a traditional server.
The endpoint is automatically available for every lakehouse in Fabric. You don't provision it, you don't pay extra for it specifically, and you don't maintain it. It just exists.
Finding Your SQL Analytics Endpoint Connection String
The first practical step is getting the connection string from Fabric. Here's exactly how to do it.
Open your Fabric workspace in a browser and click on your lakehouse to open it. By default, you'll land in the Lakehouse view, which shows the Files and Tables sections in the left pane. Look at the top of the screen — there's a dropdown or toggle that says Lakehouse (or a mode selector). Click it and switch to SQL analytics endpoint. The view changes: you now see a familiar database-style explorer on the left with your tables listed under a schema, usually dbo.
In the top-right area of the SQL analytics endpoint view, look for a button or icon that says Copy SQL connection string, or navigate through the settings icon. In some versions of the Fabric UI, you'll find it by clicking the gear or settings icon, or by looking for an information panel. What you're looking for is a string that looks roughly like this:
<workspace-id>.datawarehouse.fabric.microsoft.com
Note
The connection string format for lakehouses uses .datawarehouse.fabric.microsoft.com as the server suffix, even though you're connecting to a lakehouse. This surprises people the first time they see it — but it's correct. Both lakehouses and warehouses use the same TDS endpoint protocol and the same server suffix.
Copy this string and paste it somewhere handy — a Notepad document, a sticky note, anywhere. You'll need it in the next step. You'll also need to know the name of your lakehouse, because that becomes the database name in the connection.
Connecting Power BI Desktop to the SQL Analytics Endpoint
Open Power BI Desktop. If you're greeted with the splash screen, close it to reach the main canvas. Now follow these steps:
Click Home in the ribbon, then click Get data. The Get Data dialog opens. In the search box at the top, type "SQL Server" and select SQL Server database from the results. Click Connect.
A dialog appears with two fields: Server and Database. Paste your connection string into the Server field. In the Database field, type the exact name of your lakehouse as it appears in Fabric.
Now — and this is a decision that matters — you need to choose a data connectivity mode. At the bottom of this dialog, there's a Data Connectivity mode dropdown with two options: Import and DirectQuery.
Import mode copies the data from your Delta tables into Power BI's in-memory engine (VertiPaq). Queries are lightning fast because everything runs locally, but your data is only as fresh as your last refresh. You'll need to schedule refreshes to keep it current.
DirectQuery mode sends every user interaction as a live SQL query back to the SQL Analytics Endpoint. Your data is always current, but query performance depends on the serverless SQL engine and your network. For large datasets that change frequently, DirectQuery is often the right call.
Tip
For most foundational reporting scenarios — daily sales dashboards, weekly KPI reports, operational summaries — Import mode is the right default. It's faster for end users, easier to optimize, and more forgiving of endpoint latency. Switch to DirectQuery only when data freshness requirements make scheduled imports impractical.
Click OK. Power BI will prompt you for credentials. Select Microsoft account on the left side of the authentication dialog, then click Sign in and authenticate with the same account you use to access Fabric. Once signed in, click Connect.
The Navigator window opens, showing the schema structure of your lakehouse endpoint. You'll see your lakehouse name as the database, with a schema (typically dbo) containing all your Delta tables that have been registered in the lakehouse metastore.
Selecting Tables and Shaping Your Model
In the Navigator, you'll see checkboxes next to each table. For this lesson, let's say you're building a sales performance report and your lakehouse has the following tables: sales_orders, products, customers, and date_dim. Check the boxes next to all four.
Before you click Load, click Transform Data instead. This opens Power Query Editor, which gives you a chance to inspect and optionally reshape your data before it enters your model.
In Power Query Editor, click through each table and look at the column names, data types, and a few rows of data. Here's what to pay attention to:
Data types are critical. If a date column came in as text instead of date, fix it now by clicking the column header's type icon and selecting the correct type. DAX time intelligence functions will silently fail if date columns aren't typed correctly.
Column naming is worth cleaning up at this stage. Column names like cust_id, ord_dt, and prd_ctgry_nm are survivable in a data lake but confusing in a Power BI report. This is your chance to rename them to Customer ID, Order Date, and Product Category. Select the column, double-click the header, and type the new name.
Unnecessary columns should be removed. If your sales_orders table has internal audit columns like _delta_log_sequence or technical batch IDs, remove them. They add weight to your model and confuse report builders.
Warning
In DirectQuery mode, Power Query transformations are limited. Complex transformations like pivots or custom functions may not fold back to SQL, and Power BI will either error or pull all the data into memory to complete the transformation — defeating the purpose of DirectQuery. In DirectQuery, keep your transformations simple: rename columns, change types, filter rows by a constant value.
When you're satisfied, click Close & Apply. Power BI loads the data (in Import mode) or validates the connection (in DirectQuery mode) and takes you back to the main canvas.
Building the Data Model: Relationships and a Star Schema
You're now in Power BI Desktop with four tables loaded. Click the Model view icon on the left rail — it looks like three rectangles connected by lines. You'll see your four tables laid out as cards. Power BI may have auto-detected some relationships based on column names, but you should verify or build them manually.
For our sales example, the relationships should be:
sales_orders[product_id] → products[product_id] (Many to One)
sales_orders[customer_id] → customers[customer_id] (Many to One)
sales_orders[order_date] → date_dim[date_key] (Many to One)
To create a relationship, drag a column from one table and drop it onto the matching column in another table. A line appears connecting the tables. Double-click the line to open the relationship dialog and confirm the cardinality and cross-filter direction. For a standard star schema, use single cross-filter direction (from the dimension table to the fact table) for all relationships.
Key insight
If your lakehouse was built using a medallion architecture with gold-layer tables designed for reporting, your relationships are probably already clean. If you're connecting directly to raw or silver-layer tables, you may find duplicates in what should be dimension keys — which breaks the Many-to-One cardinality assumption. Address data quality upstream, ideally in your Spark notebooks or transformations, rather than working around it in the Power BI model.
This star schema structure — one fact table surrounded by dimension tables — is the foundation of an efficient Power BI semantic model. It makes DAX measures simpler to write and makes the query engine's job easier.
Writing DAX Measures
Now the interesting part. Click the Data view icon (the table icon on the left rail) and select your sales_orders table. In the ribbon, click New measure. A formula bar appears at the top.
Let's write a set of measures that a sales analyst would actually use.
Total Revenue:
Total Revenue =
SUMX(
sales_orders,
sales_orders[quantity] * sales_orders[unit_price]
)
SUMX is an iterator — it goes row by row through sales_orders, multiplies quantity by unit price, and sums the results. You could also store line_total as a pre-calculated column in your lakehouse and use SUM(sales_orders[line_total]), which would be slightly faster. Either approach is valid.
Order Count:
Order Count = DISTINCTCOUNT(sales_orders[order_id])
Average Order Value:
Average Order Value =
DIVIDE(
[Total Revenue],
[Order Count],
0
)
Notice the DIVIDE function instead of the / operator. DIVIDE handles division by zero gracefully, returning the third argument (0) instead of an error. In a real report, dividing by zero would produce a blank visual, which is confusing. Using DIVIDE gives you explicit control.
Here we use BLANK() as the fallback for divide-by-zero because a blank value in a percentage column is more meaningful than a zero — it correctly signals "no prior year data" rather than "zero growth."
Tip
Create a dedicated Measures table to keep your measures organized. In the Home ribbon, click "Enter data," create a table with one column called "Placeholder" and one row with a space character, then click Load. Rename this table to "Measures" in the Model view. Now create all your measures in this table rather than scattered across fact tables. It makes the model dramatically easier to navigate.
Format your measures as you go: right-click each measure, select "Format," and choose the appropriate format — Currency for revenue, Whole number for order count, Percentage for growth rates.
Building the Report Canvas
Switch to Report view (the bar chart icon on the left rail). Now you build the actual report pages.
For a sales performance report, a useful first page might include:
A card visual showing Total Revenue (drag the measure onto the canvas, then click the card visual type in the Visualizations pane)
A card visual for Order Count
A line chart with Order Date on the X axis and Total Revenue on the Y axis, filtered to the current year
A bar chart showing Total Revenue broken down by product category
A slicer on Customer region or product category to let users filter interactively
To add a slicer, drag a dimension column (like customers[region]) to the canvas and click the slicer visual type. Now every other visual on the page will respond when a user selects a region.
Connect your date_dim table's year and month columns to the slicer for time-based filtering. If your date dimension has a year_month column formatted as an integer like 202401, you might want to add a calculated column in Power BI that converts it to a proper date for sorting — or, better, ensure the date dimension in your lakehouse already has a proper date column.
Warning
If your line chart shows months out of order (November, February, August instead of January through December), the issue is almost always a text-formatted month name without a sort column. Fix this by adding a numeric month number column to your date dimension and using "Sort by column" in the Column tools ribbon to sort the month name by that number.
Publishing the Report to a Fabric Workspace
Once your report looks solid, it's time to publish. In Power BI Desktop, click File in the top-left corner, then Publish, then Publish to Power BI.
A dialog asks you to choose a destination workspace. Select the same Fabric workspace where your lakehouse lives. Click Select. Power BI Desktop uploads the report and the semantic model (the data model and all its measures).
After publishing, open your browser and navigate to the Fabric workspace. You'll now see two new items: the report (the visual canvas) and the semantic model (formerly called a dataset — the data model with all the measures, relationships, and table definitions). These are separate items in Fabric, which means you can build additional reports on top of the same semantic model without duplicating the data or the measure logic.
If you published in Import mode, set up a scheduled refresh. Click the semantic model in the workspace, then click Schedule refresh in the context menu. You can configure it to refresh up to 48 times per day (with a Premium or Fabric capacity), choosing the days and times that make sense for your data pipeline schedule.
Tip
Schedule your Power BI refresh to run 15–30 minutes after your data pipeline completes, not at the same time. If your pipeline runs at 6:00 AM and sometimes takes 20 minutes, scheduling the Power BI refresh for 6:30 AM gives your data time to fully land before Power BI tries to read it. You can coordinate this more precisely using Fabric Data Pipelines that trigger the semantic model refresh as a final activity.
SQL Analytics Endpoint vs. Direct Lake: Making the Informed Choice
Now that you've seen how the SQL Analytics Endpoint connection works, it's worth explicitly comparing it to Direct Lake mode, which is the other major option for connecting Power BI to a Fabric lakehouse.
Direct Lake mode reads Delta table Parquet files directly from OneLake into memory, bypassing the SQL translation layer entirely. It combines the query speed of Import mode with the data freshness of DirectQuery — which sounds like a clear winner. So why would you ever use the SQL Analytics Endpoint instead?
A few legitimate reasons:
Compatibility: Direct Lake has specific requirements. Your tables must be Delta format, you need to use a Power BI semantic model created within Fabric (not just published from Desktop), and some DAX features behave slightly differently. The SQL Analytics Endpoint connection works exactly like any other SQL Server connection — no surprises.
Transformation complexity: If you need to do significant transformations in Power Query — joining tables from different sources, pivoting data, applying complex filters — the SQL Analytics Endpoint connection handles this through query folding to T-SQL. Direct Lake doesn't support Power Query transformations at all.
Cross-source models: If your semantic model needs to combine data from your Fabric lakehouse with data from, say, an Azure SQL Database or a SharePoint list, you must use Import or DirectQuery. Direct Lake is a single-source mode. The SQL Analytics Endpoint lets you mix and match.
Governance and RLS: Row-level security defined at the SQL Analytics Endpoint layer using T-SQL is fully respected when Power BI connects through this method. You can implement RLS policies once in SQL and have them enforced consistently. This is covered in detail in the article on implementing row-level security in a Fabric warehouse and lakehouse SQL analytics endpoint.
The SQL Analytics Endpoint approach is not a fallback or a second-class option. It's the right tool for a wide range of reporting scenarios.
Hands-On Exercise
Work through these steps using your own Fabric lakehouse. If you don't have one ready, create a simple one using the guide in Building Your First Lakehouse in Microsoft Fabric and load it with a few CSV files.
Find the SQL Analytics Endpoint connection string for your lakehouse. Write it down exactly.
Connect Power BI Desktop to the endpoint using Import mode. Select at least two tables — ideally a fact table and one dimension table.
In Power Query Editor, rename at least three columns to be more human-readable and correct any data types that loaded incorrectly.
In Model view, create at least one relationship between your tables. Verify the cardinality is correct (it should be Many to One from fact to dimension).
Write three DAX measures: a sum or count, a ratio (using DIVIDE), and one that uses CALCULATE to modify filter context (for example, total revenue for a specific category using CALCULATE([Total Revenue], products[category] = "Electronics")).
Build a one-page report with at least one card visual, one chart that uses a dimension on the axis, and one slicer.
Publish the report to your Fabric workspace. Confirm you can see both the report and the semantic model as separate items in the workspace.
Bonus: If you published in Import mode, configure a scheduled refresh. If you published in DirectQuery mode, open the published report in the browser and verify that slicers respond with current data.
Common Mistakes & Troubleshooting
"I can't authenticate — Power BI says my credentials don't work."
Make sure you're signing in with a Microsoft account that has at least Viewer access on the Fabric workspace. If your organization uses conditional access policies, you may need to authenticate on a corporate device or VPN. Try signing out of Power BI Desktop completely (File → Account → Sign out) and signing back in fresh.
"My tables aren't showing in the Navigator."
The SQL Analytics Endpoint only shows tables that have been registered in the lakehouse metastore — meaning they appear in the Tables section of the lakehouse view, not just as loose files in the Files section. If you uploaded a CSV and it's sitting in Files but not promoted to a Delta table, it won't appear. Load it as a Delta table using a Dataflow Gen2 or a Spark notebook first.
"My time intelligence measures aren't working."
Time intelligence DAX functions like SAMEPERIODLASTYEAR require a continuous date table marked as a Date table. Right-click your date dimension table in Model view, select "Mark as date table," and specify the date column. Also confirm the date column is typed as Date (not DateTime or Text).
"The report refreshes slowly in DirectQuery mode."
This is often caused by measures that generate many small SQL queries instead of one efficient one. Look for measures that call other measures in a chain — each call can become a separate roundtrip. Also check whether the SQL Analytics Endpoint is under load from other users. The serverless engine scales automatically, but under heavy concurrent use, you may see latency. For high-traffic reports, consider Import mode with frequent refreshes instead.
"I published the report but the semantic model isn't refreshing on schedule."
Check the semantic model's data source credentials in the workspace settings. After publishing, Fabric needs stored credentials to access the SQL Analytics Endpoint on your behalf. Navigate to the semantic model item, click the three-dot menu, choose Settings, and under Data source credentials, click "Edit credentials" and sign in again. Once credentials are stored, scheduled refresh will work.
Summary & Next Steps
You've now walked through the complete journey: finding the SQL Analytics Endpoint connection string in Fabric, connecting Power BI Desktop with the right authentication and mode, shaping your tables in Power Query, building a star schema with correct relationships, writing DAX measures that go beyond simple sums, and publishing a finished report to a Fabric workspace.
The SQL Analytics Endpoint is one of the most practical bridges in the Fabric ecosystem. It takes your Delta tables — which are fundamentally just files on OneLake — and makes them instantly queryable from any SQL-aware tool, including Power BI Desktop, Azure Data Studio, or SSMS. You don't need to move data, transform it into a different format, or manage a separate database server.
From here, you have several natural paths forward depending on your goals:
If your reports need sub-second query performance on very large datasets and you're working entirely within Fabric, explore Direct Lake mode — it's the performance ceiling for Power BI on Fabric lakehouses.
The connection you built today isn't just a report — it's a reusable semantic model that your whole team can build on. That's the real value of understanding the mechanics: you're not just getting data into a chart, you're building infrastructure for better decisions.
