One of the goals of self-service business intelligence (BI) solutions is to let users create their own content without the involvement of IT departments. In this blog post, I will talk about how Power Apps can be used to allow users to add and edit data on the go while using Power BI. I will describe a solution architecture that can be used to achieve this goal.
Self-service BI is crucial in every organization. In addition to being able to view data, users should be able to create their own data as well. A good example of this is for instance adding comments that can then be viewed by other report users. One can even argue that a self-service BI solution is not truly a self-service solution unless it allows write back capabilities. Power Apps is an offering from MS that allows creating applications that can be integrated with tools like PBI allowing users to create their own content whilst using PBI. What is Power Apps you may ask?
“Power Apps is a suite of apps, services, and connectors, as well as a data platform, that provides a rapid development environment to build custom apps for your business needs. Using Power Apps, you can quickly build custom business apps that connect to your data stored either in the underlying data platform (Microsoft Dataverse) or in various online and on-premises data sources (such as SharePoint, Microsoft 365, Dynamics 365, SQL Server, and so on).
Source: 30 AUG, 2021: https://docs.microsoft.com/en-in/powerapps/powerapps-overview
So the main idea is the following: We have a Power BI report that users use to look at data. As they look at the data they want to be able to add new data for other users to see later. We need a write-back functionality and Power Apps gives us that option.
PROPOSED ARCHITECTURE
The graph below shows how we could architect a solution making use of various offerings from Microsoft.
The proposed solution is made up of the following components
Power BI - this is our main data visualization tool
Power Apps - our app is used to add and edit data
Azure SQL database - data added via Power Apps is stored in our database in Azure
Azure Analysis Services (AAS) - it is used as our primary solution to store data
Dedicated SQL Pools, ADLS, on-prem SQL server or a number of other sources. or the aforementioned Azure SQL database can all be used as data sources for our tabular models hosted in AAS
Azure Data Factory (ADF) is used to process our tabular model
How does it work?
1. Power BI
This is our main data visualization tool. We create one PBI dataset that feeds data to various reports. Users have access to PBI datasets only using a live connection. Users cannot change the data model behind the dataset. They can just consume the data. The PBI dataset acts as a connection to our Azure Analysis Services tabular model which is the primary data analytics solution.
2. Power Apps
We build a canvas app that is integrated with our PBI reports using key columns. As users select data in Power BI, the associated data is displayed in the app. The app allows us to add new data or edit the existing one. 3. Azure SQL database
The data added or edited in Power Apps gets stored in an Azure SQL database. Power Apps allow different connection types including a SQL connection. (a premium offering as of now) . The Azure SQL is then one of the sources used in our Azure Analysis Services tabular model.
4. Azure Analysis Services (AAS) We build a tabular model with various data sources. One of the sources is our Azure SQL database that contains data used by our Power Apps. AAS supports a number of data sources such as Data Lake or Azure Synapse. What ever data we need, as long as it lives in one of the supported data sources; we can always add it to our model A list of supported data sources can be found here https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource 5. Azure Synapse This is a MPP system where our data warehouse could live. 6. Azure Data Factory ADF is used to process our tabular model. We can create a simple pipeline that will process either the entire model or a specific part of it only. Our ADF pipeline can be run on a schedule using triggers.
Conclusion
Being able to write data back whilst using tools like PBI is a key aspect of self-service BI solutions. Power Apps is a tool that can be easily integrated with PBI allowing users to write back data whilst using PBI reports. This blog post described a solution architecture that can be used to use Power Apps together with other MS offerings. It is just one of many architectures that could be used for self-service BI. The architecture can be changed and customized to meet specific business goals. One of the advantages it offers is that since all of the offerings are in the cloud, the solution can scale easily. All in all, I think Power Apps is a great product that will gain more and more popularity.. Using Power Apps together with other Azure offerings is a neat example showing how to work in the cloud and how to manage self-service BI solutions.