SQL Export to Excel via Power BI XMLA

Problem

Users want to pull data from tables in an Azure SQL database into Excel via Power Query. This situation sounds simple. However, I don’t want to provide direct access to the database for several reasons, including the potential governance and permissions nightmare. We have a Fabric workspace, and most of the data already exists in Power BI reports. How can we give users access to the data they need without providing direct access to the database for an easy SQL export to Excel?

Solution

In this article, I’ll present a solution for pulling data into Power Query using the XLMA endpoint from a Power BI workspace without connecting directly to the database. First, we’ll look at what an XMLA endpoint is and why you might use it. Then, I’ll walk through each step of the process. By the end of this article, you’ll know how to tackle this problem if it arises in your environment.

XMLA Endpoint Explained

According to Microsoft’s documentation, the Extensible Markup Language Analysis (XMLA) endpoint allows client applications to interact with the engine that manages your Power BI workspace and semantic models. With a tool like Tabular Editor, you can modify the semantic models through the endpoint, for example, adding or deleting a table. But I only care about reading from them for our use case. By default, read-only is enabled, which is one less thing we need to worry about. However, you can enable read/write access. I’m unsure if row-level security (RLS) is honored when connecting via the endpoint, but that might be a future article. Stay tuned.

For all the SQL Server users, connect to the endpoint using SQL Server Management Studio and interact with it using a language like DAX.

The Excel Power BI Connector

Why can’t people connect to the semantic model in Excel if they use Power BI? Long story short, the connect to live semantic models option only loads data into a pivot or regular table. In other words, you can’t directly pull it into Power Query. Microsoft designed Power Query to extract and transform raw data; semantic models don’t fit that category. Semantic models analyze and aggregate data instead of acting as a raw dataset.

Watch the Video

Watch the video version or follow the steps below.

Setting the Stage for a SQL Export to Excel

I’ll start by creating three tables in an Azure SQL Database: Customers, Products, and Sales. The Sales table is a fact table for the other two dimensions tables. You can use the code below to follow along.

/*
mssqltips.com
*/
 
USE [master];
GO
 
IF DB_ID('XMLAEndpointDemo') IS NOT NULL
BEGIN
    ALTER DATABASE XMLAEndpointDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE XMLAEndpointDemo;
END;
GO
 
CREATE DATABASE XMLAEndpointDemo;
GO
 
ALTER DATABASE XMLAEndpointDemo SET RECOVERY SIMPLE;
GO
 
USE XMLAEndpointDemo;
GO
 
/*
Drop tables if they exist
*/
DROP TABLE IF EXISTS dbo.Sales;
DROP TABLE IF EXISTS dbo.Products;
DROP TABLE IF EXISTS dbo.Customers;
GO
 
/*
Create Dimension Tables
*/
CREATE TABLE dbo.Products
(
    ProductId INT IDENTITY(1, 1) PRIMARY KEY,
    ProductName VARCHAR(260),
    Category VARCHAR(260),
    Price DECIMAL(18, 2)
);
GO
 
CREATE TABLE dbo.Customers
(
    CustomerId INT IDENTITY(1, 1) PRIMARY KEY,
    FirstName VARCHAR(260),
    LastName VARCHAR(260),
    Region VARCHAR(260)
);
GO
 
/*
Create Fact Table
*/
CREATE TABLE dbo.Sales
(
    SaleId INT IDENTITY(1, 1) PRIMARY KEY,
    ProductId INT,
    CustomerId INT,
    SaleDate DATE,
    Quantity INT,
    TotalAmount DECIMAL(18, 2),
    FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
    FOREIGN KEY (CustomerId) REFERENCES Customers (CustomerId)
);
GO
 
/*
Insert 1,000 Products
*/
INSERT INTO dbo.Products
(
    ProductName,
    Category,
    Price
)
SELECT TOP 1000
    'Product ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR),
    CASE
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 3 = 1 THEN
            'Video Games'
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 3 = 2 THEN
            'Sports Cards'
        ELSE
            'VHS'
    END,
    CAST(RAND(CHECKSUM(NEWID())) * 100 + 10 AS DECIMAL(10, 2))
FROM sys.all_columns s1;
GO
 
/*
Insert 1,000 Customers
*/
INSERT INTO dbo.Customers
(
    FirstName,
    LastName,
    Region
)
SELECT TOP 1000
    'CustomerFirst' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR),
    'CustomerLast' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR),
    CASE
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 4 = 1 THEN
            'North'
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 4 = 2 THEN
            'South'
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 4 = 3 THEN
            'East'
        ELSE
            'West'
    END
FROM sys.all_columns s1;
GO
 
/*
Insert 10,000 Sales
*/
INSERT INTO dbo.Sales
(
    ProductId,
    CustomerId,
    SaleDate,
    Quantity,
    TotalAmount
)
SELECT TOP 10000
    ABS(CHECKSUM(NEWID())) % 1000 + 1,
    ABS(CHECKSUM(NEWID())) % 1000 + 1,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
    ABS(CHECKSUM(NEWID())) % 5 + 1,
    (ABS(CHECKSUM(NEWID())) % 5 + 1) * (10 + ABS(CHECKSUM(NEWID())) % 90)
FROM sys.all_columns s1
    CROSS JOIN sys.all_columns s2;
GO
ERD

Business Need for SQL Export to Excel

Imagine our users are requesting access to one or more tables for reporting purposes, primarily the Customers table. I could create a Power BI report and make it available in our pre-existing workspace. However, my users live in Excel and prefer using Power Query to apply filters and perform other transformations.

Returning to the original problem, I don’t want to provide direct access to my SQL Database. The people using this will never write SQL queries, and managing the permissions is a nightmare. Additionally, this is an operational database, and it’s not a best practice to allow users to connect directly to it for reporting. A user could consume 100% of the CPU if they were so inclined. Perhaps looking into this topic could be a future article. However, I pose the question: Why do you think it’s a good idea for users to connect directly to an operational SQL database for reporting?

In Power BI desktop, I’ll create a simple report that pulls from my Azure SQL database as the source. I’m importing the three tables, ensuring the relationships exist in the model. Since the users don’t need Direct Query, a daily refresh should be fine.

Power BI Model

Next, I’ll publish the report to a workspace and select “Yes, save the changes.”

Power BI Publish

Let’s assume I already assigned appropriate permissions to users in the workspace. From my testing, it seems like you need Contributor access or above.

Finding the XMLA Endpoint

To find the XMLA Endpoint after navigating to our workspace, click on Workspace Settings. The connection will be under License Info in the Connection link.

Workspace Settings

For me, it’s powerbi://api.powerbi.com/v1.0/myorg/MSSQLTips. I’ll copy the URL and then open Microsoft Excel. Finding this setting should be available to all workspace members.

Loading into Excel

In Excel, click Data -> Get Data -> From Database -> From SQL Server Analysis Server Database (Import). I’ll populate the server’s name as in the screenshot below. For the database, I’ll use the semantic model’s name, Sales.

Excel Connection

The next step is crucial. Power Query tries to flatten out this dimensional model into one big table. That results in some sort of cross-join and millions of rows. I don’t want all those rows. The easiest way to pull only a single table is to supply the DAX EVALUATE statement below.

EVALUATE Customers

For this example, the user only wants to import the customers. So, they can use the query below to do that and then click OK. If you only want to load the data, click Load. I want to fix the column names, so I’ll choose Transform Data. While I fix the customer heading, I’ll also rename the query to Customers; it seems only fitting. Finally, I’ll finish off by clicking Close & Load.

Power Query Settings

Challenge: If you are following along, how could you refine the EVALUATE statement only to return customers from the East region?

Clean Up

After the demo, delete the database unless you want to keep it.

-- mssqltips.com
USE [master];
GO
 
IF DB_ID('XMLAEndpointDemo') IS NOT NULL
BEGIN
    ALTER DATABASE XMLAEndpointDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE XMLAEndpointDemo;
END;
GO

Wrapping Up

At this point, the user can save the file and even go back and edit the query. They don’t need to navigate back to the workspace and export or choose to analyze in Excel. They can also create another query to pull in the other tables. With this solution, the users are happy, and I don’t have to worry about them connecting directly to my SQL Database; all is good again in my world.

Key Points

  • The standard option to get data in Excel from Power BI doesn’t allow you to interact with it via Power Query because the data is not in the expected format. That’s where the XMLA endpoint comes in handy.
  • If you try to load multiple tables simultaneously into Power Query, the engine converts them into one big table. You’ll likely need to do some expensive transformation, so importing one at a time seems beneficial.
  • Several options are available if you don’t want to allow users to query a SQL database directly; this solution is one of them. However, my article assumes you can access a Power BI/Fabric workspace.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *