Supercharging SQL Analysis: Data Files to SQL Tables with Power BI Datamarts
Converting Excel, CSV, JSON, and more into SQL tables to speed up analysis
Every once in a while, we have to analyze data outside the cozy realm of a database. Dealing with a small Excel sheet? Easy peasy. But what about those massive files, JSON jumbles, SharePoint lists, or OData feeds? Wouldn't life be easier if you could magically turn these files into SQL tables and flaunt your SQL skills? But reality check — who just casually has an SQL database lying around? How much time would you spend converting those files to tables?
In this article, we will discuss datamarts in Power BI and how to convert data files to SQL tables in minutes. We will also discuss the different interfaces and options datamarts provide for SQL analysis. This article is perfect for anyone who wants to learn how to convert data files to database tables to speed up their analysis.
Please read our terms of use before proceeding with this article.
Imagine you have a fully managed and optimized Azure SQL database where you can toss in data from any file or source without writing a single line of code. There is no need to tweak or optimize. You can create or obliterate it whenever you feel like it. The best part is that it doesn't cost extra with the proper license. Datamarts in Power BI is your data daydream that has come true.
Datamarts in Power BI
Datamarts are instances of a fully managed Azure SQL database that enable us to store and explore data in relational tables. They come with Power Query online to provide a simple and optionally no-code experience for ingesting data from different data sources, performing transformations, and then loading (ETL) it into an Azure SQL database that's fully managed and requires no tuning or optimization.
Datamarts provides SQL support, a no-code visual query designer, row-level security (RLS), and an auto-generated semantic model (formerly dataset) for report creation. It supports a data volume of up to 100 GB and requires no additional tools or setup time, making it perfect for ad hoc analysis.
Availability and licensing
At the time of writing, datamarts are in preview and not yet available in all regions worldwide and across all Power BI SKUs. You can check out the latest availability and other limitations here.
Datamarts is a premium offering in Power BI. Thus, datamarts can only be created in a premium workspace. The following types of workspaces support datamarts:
- Power BI Premium workspace (EM SKUs are not supported)
- Premium Per User (PPU) workspace
- Fabric workspace (including workspaces on trial capacities)
You need a Power BI Pro license and should have an admin, member, or contributor role in the workspace.
From files to tables
Let's take an example to see how datamarts can help us speed up our analysis by enabling us to use an SQL-oriented approach. We have a CSV file with our IT department's spending details and a JSON file containing all the cost elements. Our task is to analyze the money spent on "Conferences" over time.
Head to your Premium workspace, select + New, and then choose Datamart (Preview) to create a datamart. Enter a name and wait for Power BI to provision a datamart. It can take up to a few minutes.
On the Home page of your datamart, select Get data to start sourcing your data to the datamart.
On the Choose data source window, you can see and explore all supported data sources or upload the files from your local machine. For this example, we will upload the files from the local machine. Drag and drop your files one by one to the Upload file or use the Browse button. Alternatively, you can upload the files to SharePoint or OneDrive and then ingest the data into the datamart.
Files uploaded from local machines are uploaded to your OneDrive for Business account.
On the Connect to data source window, you can specify how datamart can connect to your data source, including a connection URL, a connection name, data gateway details, authentication details, and data source privacy level. Most of these details are automatically filled in when you upload a file.
Ensure that the Privacy Level is set to Organizational if you're going to mash data with your organization's data sources, such as a database, OneDrive, SharePoint, etc. If you encounter a firewall error, read our article on data privacy levels to resolve it.
The Preview file data window shows a preview of the data from your data source. You can also specify your file settings, such as file encoding, delimiter, and auto-detection of data types.
You can perform any desired transformations in the Power Query editor window and mash up data from various sources.
Use the Get data to add the rest of the files or data from other sources. Perform the required data cleaning, transformation, and mashup activities and select Save. Wait for the data to be loaded; depending on the volume of your data, it can take a while.
Your data is now ready to be analyzed with SQL. You can preview your data by selecting the tables in the Explorer pane.
Analyze with SQL
There are two ways to use SQL to analyze data in the datamarts.
Datamarts only support DQL statements (DDL & DML are not supported)
SQL Query Editor Online
Datamarts comes with a built-in online SQL query editor. The SQL Query Editor provides a text editor to write queries using T-SQL. The SQL Query editor supports IntelliSense, code completion, syntax highlighting, client-side parsing, and validation.
To start writing your queries, switch to the Query tab at the bottom of the datamart editor window or select New SQL query from the Home ribbon. Select Run to execute your T-SQL query. The queries are automatically saved every few seconds. You can rename your queries to identify them quickly in the future.
From the Results section, you can export your query results to Excel. Select your query and click Open in Excel. Follow the prompts to download an Excel file connected to your datamart and populate the data with the T-SQL query.
You can also visualize your query results in the query editor. Select the query and click Explore this data (preview). Select the fields from your query results to build a visual of your choice and an accompanying matrix. You can save the visualizations as an Exploration or a Power BI report.
Alternatively, you can also select the query and choose Visualize results to visualize and save the results as a Power BI report.
At the time of writing, Visualize results do not support SQL queries with an ORDER BY clause.
External Client Tools
Datamarts support connections from external SQL client tools such as SQL Server Management Studio (SSMS) and Azure Data Studio (ADS). Connecting a datamart to external SQL tools is a two-step process.
- Get the T-SQL connection string: Datamarts provides an SQL endpoint to enable connection from external client tools. The easiest way is to copy it from the datamarts' More options (…) context menu. Alternatively, copy it from the datamart settings > server settings or the datamart information page.
2. Authenticate and Connect: Use the latest version of your favorite SQL client tool and authenticate using Microsoft Entra ID or MFA.
SQL Server Management Studio (SSMS)
To use SSMS, you need version 18.0 or above. When you open SSMS, the Connect to Server window appears. Alternatively, you can open it manually by selecting Object Explorer > Connect > Database Engine. On the Connect to Server > Login prompt, set the following:
- Serer type: choose Database Engine
- Server name: paste the datamart's connection string
- Authentication: choose Microsoft Entra MFA
- User name: your organizational user principal name (UPN)
- Encryption: choose Optional
You can leave the database name blank (on the Connection Properties tab). Follow the prompts and authenticate your credentials.
Datamarts only support Microsoft Entra ID and do not support managed identities or service principals at this time
Object Explorer displays the SQL database connected to your datamart with the respective tables and views when the connection has been established. The files we ingested and converted to tables in the datamart are available as Views with the default schema model.
That's all for this article on converting files to SQL tables. However, for the sake of completeness of your knowledge, let's take a quick look at the sharing and access control options available with datamarts.
Access Control
A datamart can be shared with other users who can use it to query or build Power BI reports. Sharing the SQL connection string is not enough; the users should also have access to the datamart. You can share access with others by providing them with a workspace role or datamart-level permissions. You can find detailed steps on how to share datamart-level permissions here.
An important distinction regarding workspace roles is that users added to the Admin, Member, or Contributor workspace roles get the admin role to the database. In contrast, users added to the Viewer role in the workspace get a viewer role in the database. You can read about the distinction between these roles and restrictions on the viewers here.
You can also use row-level security (RLS) with datamarts to restrict data access for specific users. RLS in datamarts functions in a similar way to Power BI semantic models. Additionally, datamart RLS automatically applies to downstream items, including the auto-generated semantic models and reports. You can read the details here.
Conclusion
In this article, we examined how to use Power BI datamarts to convert files and other data sources into SQL tables. We used the datamart's online query editor to query the sample data and connected SSMS to the Azure SQL database that backs up the datamart. With this innovative use of datamarts, we can now speed up our analysis by utilizing our SQL expertise with any data source.
Like this post? Connect with Dhyan
Stay connected and updated! You can find me on LinkedIn
Don’t forget to subscribe to
and join our Power BI community
