Microsoft announced the public preview of operational databases in Microsoft Fabric at Ignite last week. This means that you can now have your operational (OLTP) workloads supported in a Software as a Service version of Azure SQL database alongside the rest of your Microsoft Fabric items and it is covered under the same capacity model for pricing! This is a huge enabler for projects that already had all their analytical and data science/engineering workloads in Fabric but had their operational SQL databases in Azure SQL DB or on-prem SQL server. Additionally, this is an excellent choice for new projects that need a cloud-based SQL store. Although Microsoft is positioning Microsoft Fabric SQL database as an operational database, I think we are going to see many projects choose Microsoft Fabric SQL database over a Fabric Lakehouse or Warehouse as their analytical layer due to its simplicity, ease of use, and feature parity with Azure SQL DB and on-prem SQL databases.
One area that this feature parity is apparent is when it comes to application development. Once your data is in a Microsoft Fabric SQL database, you can seamlessly switch an existing application to it by just switching its connection string and no other changes are required in the application. For instance, for Power Apps applications this is a seamless transition to switch an existing application. This is not the case for Fabric Warehouse where a few changes on the application side are required. See my older post on that topic here.
In this post, I will first share how to create a standalone Power Apps application that works with Microsoft Fabric SQL Database and then show embedding options for enabling writeback in Power BI and automatic page refresh to see results in near real time.
I also recently created a YouTube video on this topic as well that you can watch here:
Power Apps, Power BI, and Microsoft Fabric SQL database
Power Apps is a low-code no-code platform for creating applications. You can create Power Apps applications to update data in your Microsoft Fabric SQL database and you can optionally embed that application in a Power BI report that is based off the same database to let Power BI users update the data directly from Power BI. Then depending on the storage mode of the Power BI model (Direct Query or Direct Lake), users can see the changes in near real time when an automatic page refresh triggered from the Power Apps application.
It is worth mentioning that typically Power BI reports are not created on top of operational databases rather they are based off of reporting databases (warehouses); however, there are some use cases where the above scenario may still be applicable. For example, if you have a small operational database that is not under heavy load or if you are using Power BI reports to QA the data in your operational database then it makes sense to have Power Apps and Power BI work directly off this database. The other use case is when you may be using Microsoft Fabric SQL database as your reporting layer similar to on-prem patterns where a project uses 2 different SQL Server databases as operational/reporting layers. In this case, you could use 2 or 3 Fabric SQL databases in a medallion architecture and Power Apps and Power BI will be working off the last layer.
Part 1: Finding the right SQL Connection
Every time you create a Fabric SQL Database; you get two additional Fabric items: A SQL Analytics endpoint and a default Power BI semantic model.
You might find it confusing that you get a SQL analytics endpoint with a SQL database because it might seem redundant, but you have to remember that Microsoft Fabric SQL database is actually Azure SQL DB wrapped in a Software as a Service wrapper. It is your operational database. Then the data is automatically replicated to OneLake and provided to you for analytics under the SQL analytics endpoint. In other words, the Fabric SQL database in Microsoft Fabric is your operational (OLTP) database and the analytics endpoint is the mirrored copy for analytics.
If you are working with a small project, this can be a quick setup for getting both the operational and analytical layers. If you are working on a larger project, you may opt to move data into a higher environment (silver/gold) from either the Fabric SQL database itself or the provided mirrored analytics endpoint.
When it comes to Power Apps, depending on what your Power Apps application wants to do, you can either connect to the Fabric SQL database itself or to the SQL analytics endpoint. These two connections are different and behave differently. Fabric SQL database is read/write and can do everything (almost) that Azure SQL DB does. The SQL analytics endpoint is read only! Pointing a Power Apps to the read only replica has limited use cases but it might still be useful for cases when you want to look up details of a record or search over several tables.
To get the Microsoft Fabric SQL database connection, open database Explorer by clicking on your Fabric SQL database item in Fabric portal. Then from the Explorer menu select “Open in” –> “SQL Server Management Studio“.
This gives you the connection string for connecting from SQL Server Management Studio (SSMS) which is what you need for Power Apps as well. Notice that the name of the database is the name you give your Fabric SQL database followed by a GUID. In the above screenshots, I called my database FabricSQLDB. You need the entire name to connect to it from SSMS or other tools. Even though the Fabric portal and database explorer both show the name that you choose when you create the item, the actual database name is that name plus a GUID.
If you want to find the SQL analytics endpoint’s connection information, switch to it from the explorer then click on the settings (Gear) icon in the toolbar.
The connection information is under SQL endpoint.
This connection looks like a typical Microsoft Fabric connection. Notice the word “datawarehouse” in the name. In fact, if you have other Fabric items such as a warehouse or lakehouse, this is the same analytics endpoint which means you can write cross-database queries!
Part 2: Creating a standalone Power Apps application for Microsoft Fabric SQL Database
The easiest way to create a SQL based application from Power Apps is by going to Create hub in Power Apps studio (make.PowerApps.com) and selecting the SQL option:
This will guide you through a setup process to create a 3-screen application that knows how to show records from a table as well as how to delete or update them. As part of the setup, you need to supply connection information for the table that you want to work with. This is where you need to provide the SQL connection of the Fabric SQL database itself unless if you want to create a read-only application in which case you can use the SQL analytics Endpoint.
For the connection type, choose SQL Server and for authentication type choose Microsoft Entra ID Integrated.
Please note that Power Apps has a different licensing model than Power BI. Every user who opens a Power Apps (regardless of how they access it) must be covered by a Power Apps license and if the application is using a premium connection such as SQL Server, the user must have a premium (paid not free) license.
In the next screen enter the connection information.
IMPORTANT: When you enter the connection information, make sure you take off 1433 from the end else Power Apps is not able to connect.
When the connection is successful, you are given a list of tables to choose from. Choose the table you want to work with and click on the Connect button at the bottom right corner of the screen. If you don’t see this button, change the zoom in your browser.
Power Apps will create a multi-platform application that can update your Microsoft Fabric SQL database!
On the first screen, it randomly picks some of the columns and places them on a Gallery control. You can change these to customize your application.
This application is a great starting point if you are new to Power Apps. Note that Power FX formulas (Power App’s programming language) are highly portable meaning that it is easy to learn from this application and apply it to your custom application later on.
On the first screen there is a Gallery control that gets its data from the data source you specified. You can see this data source under the Items property. There is some extra code here that you don’t need in all applications. The extra code provides the sort and search functionality in the sample 3-screen application.
Power Apps randomly picks some fields from the table to display on. You can copy and paste one of the existing fields (controls) and use the intelligence to change it to another field.
The second screen shows details of each record and lets a user delete a record or click on the pencil icon to navigate to the last screen. Note how navigating from one screen to the other is super easy with Navigate function.
The third screen is where the application lets a user make changes to records and save them. If you click on the checkmark icon, you will see the formula it uses to save the data back to Fabric SQL Database. The formula is SubmitForm (NameOfTheForm). It cannot get simpler than this!
A Form control two important properties that once they are set, SubmitForm works magically to update the data. Keep in mind that your data source must be read/write like Microsoft Fabric SQL database itself not the SQL analytics Endpoint or a Fabric Warehouse.
Data Source: This is the connection that you created earlier. The Form control is tied to it and knows how to write data to it.
Item: The item property populates the fields in the form so that when someone gets to this screen of the application, they can see the existing values.
Once the above properties are set, Power Apps knows how to issue a SQL command to the data source for the update. You don’t see the SQL Command from Power Apps. You can see it if you trace queries on the data source. SubmitForm (and also Patch which is another Power Apps function for making updates) issues a SQL command using sp_execuresql system stored procedure. Thankfully this stored procedure is fully supported in Fabric SQL database. If you are working with a Fabric Warehouse, see the workaround in my other post here.
The following shows sample update code that Power Apps issues:
exec sp_executesql N'UPDATE [dbo].[FactSmallSales]
SET ProductCategoryName = p0
OUTPUT [INSERTED].[Column1],[INSERTED].[Column2]…
WHERE [SalesOrderNumber] = “SO123”’,
N'@p0 nvarchar(25)',@p0=N’Mountain Bikes'
Part 3: Using Power Apps to enable writeback in Power BI reports for Microsoft Fabric SQL Database
You can embed a Power Apps application in a Power BI report using the Power Apps visual in Power BI. Depending on the storage mode of the Power BI model, when the data is updated in Power Apps, users can see the data in real-time in Power BI without having to click on the page refresh button in Power BI service:
I have previously blogged about this topic for Azure SQL Database as well as Microsoft Fabric Warehouse:
Blog: Using Power Apps in Power BI reports: Enabling writeback – Shabnam Watson’s Blog
Blog: Updating Microsoft Fabric Warehouse with Power Apps visual in Power BI
Video: Updating Microsoft Fabric Warehouse with Power Apps visual in Power BI
In order for Power Apps to be able to interact with a Power BI report, you have to follow a certain order of operations:
In this post, I want to emphasize that when it comes to Microsoft Fabric SQL database, depending on the use case, there are multiple options of where a Power Apps and Power BI report can point to. Additionally, the Power BI storage mode plays a role in how quickly updates are reflected in Power BI.
Although with every Microsoft Fabric SQL database you get a SQL analytics endpoint and a default semantic model (in Direct Lake storage mode) that is based on that SQL analytics endpoint, you still have the option of creating a Power BI model either directly from the Fabric SQL database or a custom Power BI model from the SQL analytics endpoint. There are quite a few possibilities that you can choose from based on your requirements. Here I will go over two cases.
Use Case 1: Power Apps and Power BI model based on Fabric SQL database
In this use case, Power Apps is working with Fabric SQL database, and you have a Power BI model that is in Direct Query storage mode which means that changes in the backend are reflected in the Power BI report in near-real time with a Power BI page refresh. The only trick to getting this use case to work, is to create the Power Apps from Power BI service for the first time.
Once you start the application from Power BI service, you will have access to a function called PowerBIIntegration.Refresh() which you can use to refresh a Power BI page.
For a step-by-step guide to create this kind of application, please see my older post here.
Use Case 2: Power Apps based on Fabric SQL database and Power BI model based on SQL analytics endpoint
In this use case, Power Apps is working with Fabric SQL database; however, the Power BI model is built off of the SQL analytics endpoint. The implication of this design is that when someone uses the application embedded in a Power BI report to update the data, the updated results will not be shown in Power BI immediately with a manual or automatic Power BI page refresh. There is a delay in the data getting replicated from Fabric SQL database to the SQL analytics endpoint. If it is important for users to see the changes reflected in Power BI in near-real time (notice the near real-time vs. real-time), then you can add a delay in your Power Apps application that will make the user wait a certain amount before the application triggers a page refresh. I show this in my recent video on YouTube here: Using Power Apps in Power BI: Enabling writeback for Microsoft Fabric SQL Database
Here is a screenshot of a sample application. The image shows the second screen of the application where a user gets to make edits.
On the second screen, I have used a Save button instead of the save icon that comes with sample 3-screen applications. There is also a hidden timer control on this screen. The timer control is not like a typical timer that you may expect from other programming languages. It is a bit tricky to work with. You have to reset it to get it started. I learned how to do this from a video by Shane Young. The second screen also has a wait image that is initially hidden and will toggle to visible once someone clicks on the Save button. The save button itself does not call PowerBIIntegration.Refresh() immediately, instead it starts the timer, and the timer control calls PowerBIIntegration.Refresh() when its duration is done.
Note: PowerBIIntegration.Refresh() is what causes a Power BI page refresh. If you get an error when trying to use this function, most likely you have not followed the order of operations noted previously in this post. A Power Apps application must be created from Power BI service for this to work.
The following section lists the code behind the application. I have only listed the most important ones and skipped the trivial ones.
Screen 2: This code resets variables that are used by other controls anytime a user navigates to or away from screen 2.
OnVisible:
UpdateContext({ResetTimer: true});UpdateContext({ResetTimer:false});
UpdateContext({ShowProgressImage:false});
UpdateContext({SaveButtonActive: true});
UpdateContext({TimerGo: false});
OnHidden:
UpdateContext({ResetTimer: true});UpdateContext({ResetTimer:false});
UpdateContext({ShowProgressImage:false});
UpdateContext({SaveButtonActive: true});
UpdateContext({TimerGo: false});Save button: This code updates the data in Fabric SQL database then starts the timer.
OnSelect =
UpdateContext({ShowProgressImage: true});
UpdateContext({SaveButtonActive: false});
SubmitForm(Form1);
UpdateContext({ResetTimer: true});UpdateContext({ResetTimer:false});
UpdateContext({TimerGo: true});Wait Image: Initially hidden
Visble = ShowProgressImageTimer: Once the duration passes, the timer will cause a Power BI page refresh which will cause the visuals to reload based on the latest data in the data source if the model is in Direct Query or Direct Lake.
Duration = 40000
AutoPause = false
OnTimerEnd =
UpdateContext({ShowProgressImage: false});
UpdateContext({SaveButtonActive: true});
PowerBIIntegration.Refresh();
Navigate(Screen1);
Reset = TimerReset
Start = TimerGo
Visible = falseVideo
Check out my video on how to use Power Apps with Microsoft Fabric SQL database both for standalone apps and embedded in Power BI.