Chapter 4. Data Sources and Connectors

Any business application needs a database, also known as a data source, to store the data. OneDrive for Business, Microsoft SQL, Excel, and Oracle are a few examples of frequently used databases. Power Apps uses Microsoft Dataverse to store data and also provides a variety of connectors that enable you to connect to various external data sources. In this chapter, you will learn the fundamentals of Power Apps data sources and connectors, their architecture, and their various types based on Power Apps types.

Introduction to Data Sources and Connectors

Data is the heart of any organization that stores information about its customers and employees. This data is needed by many business applications in your organization to make business decisions. Therefore, it needs to be stored and centralized somewhere so that various business applications can talk to it and perform various transactions like create, read, update, and delete.

The location or database where the business data is kept is referred to as a data source or database, and the channel or link via which that data source communicates with business applications is referred to as a connection. This connection between your business software and the data source is established using an API, which serves as middleware or a bridge and is referred to as a connector (see Figure 4-1).

Let’s understand this using the LinkedIn app as an example. LinkedIn is a business- and employment-focused social media platform that stores its customer and business data in the LinkedIn data source. It doesn’t let you view any data unless you provide valid credentials and log in. In the background, as soon as you log in, the LinkedIn App first makes a connection with the LinkedIn data source to validate your identity and then provides you access to view data through the LinkedIn app. The connection between the LinkedIn app and LinkedIn data source happens through APIs or middleware that takes the input from the LinkedIn app and passes it to the LinkedIn data source and then takes the response from the LinkedIn data source and passes it back to the LinkedIn app.

Figure 4-1. Connector architecture

APIs are typically used to establish communication between two different systems that are on two different platforms and don’t understand each other’s language. The most popular and well-known data formats that APIs use for data transmission are XML (Extensible Markup Language) and JSON (JavaScript Object Notation).

JSON is more widely used and accepted than XML since it is lighter in weight, has a simpler, easier-to-understand syntax, and transfers data more quickly.

Microsoft developed a new component called a connector, which is a wrapper around an API that encapsulates all API functions and provides an easy UI to send and receive data. Now you simply need to use the connector of the applicable data source, send the input data, and get the output data; you don’t need to worry about how the API is developed and operates (see Figure 4-2).

Figure 4-2. Power Apps built-in connectors

Power Apps distinguishes between primary data sources and secondary data sources. The primary data source is the main database on which any Power Apps run and where their business data is stored. For example, Dataverse is a primary database for a model-driven app. A secondary data source is an additional or external database that is often used in integration scenarios, where you need to bring external data into the primary data source. In some scenarios, there could be multiple primary databases. For example, canvas apps can use SharePoint for document management and SQL Server for contact and account data.

Data Sources for Model-Driven Apps

Model-driven apps can store data only in Microsoft Dataverse. Model-driven apps don’t need a connector to connect with Dataverse because they are tightly coupled with Dataverse (see Figure 4-3).

Figure 4-3. The primary data source of model-driven apps can only be Microsoft Dataverse

You can also integrate model-driven apps with external (secondary) data sources via various connectors like Oracle, SQL Server, Salesforce, Twitter, Facebook, Adobe, and Google. These connectors bring the data from external data sources into Microsoft Dataverse. In some cases, you don’t need to store the external data in Microsoft Dataverse, but can directly retrieve it in your business app on the fly.

Data Sources for Canvas Apps

In canvas apps, you have the flexibility to choose your data source to store your app’s data. The primary data source can be either Microsoft Dataverse or an external database such as Oracle, SQL Server, Salesforce, Twitter, Facebook, Adobe, or Google, as shown in Figure 4-4.

Data sources for canvas apps are grouped into three categories: tables, AI models, and connectors.

Figure 4-4. Primary data source of canvas app can be either Dataverse or another data source

Tables

You would choose the tables type of data source when you want to use Microsoft Dataverse as your primary data source. Tables display the list of all tables contained in Microsoft Dataverse for the environment, such as Account, Contact, Opportunity, Phone Call, Appointment. You can create a new table as well by clicking on “Create new table” from the “Select a data source” panel, as shown in Figure 4-5.

Figure 4-5. Table as data source in canvas app

Once you choose a Dataverse table from the list, such as the Account table shown in Figure 4-6, it will be added to the data source list (in the left panel). Then you can use it in your app to play around with its data.

Figure 4-6. Data source panel

You can now use the Account table as a data source for the Items property of the gallery to display the account data in the app, as shown in Figure 4-7.

AI Models

The AI Models feature gives apps AI capabilities such as a business card reader, sentiment analysis, and form processor. AI models also behave as data sources, processing the incoming data from your app to produce the appropriate results using a variety of built-in algorithms and machine learning capabilities. For instance, an AI model for sentiment analysis is trained to understand written text and translate it into appropriate sentiments. Hence, it will return positive if I write, “I had a fantastic day at the office today,” and negative if I write, “I am not well today.”

In Figure 4-8, there are lists of AI models available to use as data sources in a canvas app. I have added a “Sentiment analysis” AI model to the app for demonstration purposes.

Figure 4-8. Available AI models in a canvas app

I’ve added a new screen in the app along with a few text boxes, labels, and icons, as shown in Figure 4-9. The text boxes will allow users to enter the event’s feedback, labels will be used to display the outcome of sentiment analysis, and icons are used to display the emoticons based on the feedback.

Figure 4-9. Sample app screen designed using textbox and label controls

I have used the following Power Fx expression on the Icon property of the Icon control to change its type based on the sentiment analysis outcome, as shown in Figure 4-10:

If(
    !IsBlank(Feedback1.Text),
    Switch(
        feedbackLabel1.Text,
        "positive",
        Icon.EmojiSmile,
        "negative",
        Icon.EmojiSad,
        Icon.EmojiNeutral
    )
)

This expression says that if the user-provided input is not blank and is positive, show a smiley emoticon 😀; if negative, show a sad emoticon ☹; otherwise, show a neutral emoticon 😐.

Figure 4-10. Power Fx expression on Icon control to change its type based on sentiment analysis outcome

I have used the following Power Fx expression on the Text property of the Label control to change its label message based on the sentiment analysis outcome, as shown in Figure 4-11:

If(
    !IsBlank(Feedback1.Text),
    Lower(
        'Sentiment analysis'.Predict(
            Feedback1.Text,
            {Language: "en-us"}
        ).Document.TopSentiment.Name
    ),
    "feedback not shared"
)

This expression says that if the user-provided input is not blank, then display sentiment feeling (positive, negative, neutral, etc.); otherwise, display “feedback not shared.”

You will learn more about Power Fx expressions in Chapter 9.

Figure 4-11. Power Fx expression to change Label control based on sentiment analysis outcome

And, finally, when I run the app from Power Apps Studio on a web browser, it displays the outcome, as shown in Figure 4-12.

Figure 4-12. Canvas app output screen

Connectors

Canvas apps need connectors when they need to talk to any external data sources like Oracle, SQL Server, or Salesforce. As depicted in Figure 4-13, there are various connectors available that you can use.

Figure 4-13. Built-in connectors available in Power Apps

For this demonstration, I have used the Microsoft Entra ID connector to create a new user in a Microsoft Entra ID (formerly Azure Active Directory) data source, as shown in Figure 4-14.

Figure 4-14. Adding a Microsoft Entra ID connector to the canvas app

To create a new user in Microsoft Entra ID, I have added a new screen, along with a few controls including a text box to take the user’s input, a label to display header text, and a button to perform an action and submit data to the Microsoft Entra ID data source, as shown in Figure 4-15.

Figure 4-15. Sample screen to design a sign-up form

Then, I added a Power Fx expression to read values from the text box controls and create the user in Microsoft Entra ID, as shown in Figure 4-16.

Figure 4-16. Power Fx expression on button control to create user in Microsoft Entra ID

Figure 4-17 shows what the app’s screen looks like when I run the app through the Power Apps Studio in the web browser.

Figure 4-17. Canvas app output screen

In Figure 4-18, you can see that a new user has been created in Microsoft Entra ID with the specified details.

Figure 4-18. User created in Microsoft Entra ID

The Architecture of Power Apps Connectors

Connectors in Power Apps are SaaS-based components. That means you don’t need to bother about what things are going on in the background to get your things done; everything is managed by the service provider, which is Microsoft Azure. You only need to pass the required input parameters to the connector and receive the output from it to further process it. How it makes the connection with the data source, how it stores the connection details, how information is being passed between your app and data sources—everything is taken care of by Microsoft Azure. See Figure 4-19 for the difference between IaaS, PaaS, and SaaS service providers.

Figure 4-19. Types of cloud service providers

There are three main architectural components of connectors, as shown in Figure 4-20.

Figure 4-20. Connector architecture

Let’s review these components in more detail:

Credential and metadata store

This essentially stores the connection information between your Power Apps and data source. For instance, for Power Apps to communicate with a Salesforce data source, you must first provide the relevant connection details of Salesforce. Depending on the platform or security architecture of the specific data source, these connection details may change. Some data sources require a URL, username, and password to connect, while others require API keys, client IDs, secret keys, etc. The credential and metadata store keeps track of your data source connection information so that Power Apps can communicate with the data source without your having to repeatedly enter the same connection information.

We previously used the LinkedIn app as an example. It only asks for your credentials the first time to connect your app to the LinkedIn data source. Your login information is stored locally going forward, so you won’t have to enter it again. The credential and metadata store works in a similar fashion in the case of connector.

Azure APIM

This is the entry point of the connector. Each time a new request is made through the connector by Power Apps, the credential and metadata store is consulted to verify the connection information. If the information is accurate, it receives a connection token that Azure APIM uses to call the external data source API to execute the necessary operation in the data source. Any database will grant access to read its data based on your connection token, which is similar to a valid ID.

App service environment

By calling the external APIs, Azure APIM can execute actions directly in the relevant data sources. However, sometimes it is necessary to transform the data transferred between Power Apps and data sources to make it readable in corresponding data source formats. Additionally, there are occasions when Power Apps needs to communicate with on-premises data sources. So, there was a need to have a platform where developers can write their custom business logic and make custom web apps for the connectors (connector web apps). The result is Azure App Service, a platform as a service (PaaS) offering that provides a way for developers to:

Types of Power Apps Connectors

Connectors are divided into three categories based on their usage, license, and publishers: standard, premium, and custom.

Standard Connectors

Standard connectors are used to connect with the most commonly used data sources, such as Microsoft Entra ID, SharePoint, OneDrive, Dropbox, Google Drive, and Microsoft Teams (see Figure 4-21). Publishers of Standard connectors can be either Microsoft; a non-Microsoft entity such as Google, Adobe, or Facebook; or an individual. Some of the Standard connectors are also Premium in nature like Microsoft Dataverse. However, with all plans of Power Apps licensing, these connectors are completely free and open to use.

Figure 4-21. Standard connectors

Premium Connectors

Premium connectors are only available if you have a Power Apps Premium, Power Apps per app, Power Apps per app pay-as-you-go, or Dynamics 365 Professional and Enterprise license. Publishers of these connectors can be either Microsoft, non-Microsoft entities, or individuals, and include Microsoft Dataverse, SQL Server, Salesforce, Adobe PDF Services, DocuSign, and Amazon. In Power Apps, all the connectors are in one list, but you can identify the Premium connectors by the “Premium” stamp next to the Publisher name (see Figure 4-22).

Figure 4-22. Premium connectors

Custom Connectors

Microsoft provides a wide variety of connectors. As of writing, the count is over one thousand, but it continues to increase. What happens when the available Standard and Premium connectors don’t fit your needs? Sometimes, you might need to call an API or service or connect to a system that is not available as a prebuilt connector. In this situation, you can create your own connector, a custom connector. You can send your own connector to Microsoft for certification. If the connector satisfies certification requirements, Microsoft will examine it and give it the go-ahead for publication, making it publicly accessible for all users in Power Apps.

Note

Custom connectors are a premium feature of Power Apps. So, if you wish to develop and use one, you must have a Power Apps Premium license.

For example, I want to create an app to make use of the COVID-19 APIs that my government has made available to allow the employees of my company to schedule vaccination appointments. The first API will accept a cellphone number as an input and return a one-time passcode to register a new user. The user’s postcode will be entered into the second API, which will then display available vaccination slots nearby and reserve the appointment once the user approves the slot. To fulfill this requirement, I need to create a custom connector to call this API, as there isn’t any prebuilt connector for it (Figure 4-23). Also, after it has been reviewed and authorized by Microsoft, you can publish and make this connector publicly available for other companies.

Figure 4-23. Custom connectors

Using Connectors Versus Calling an API

You might be wondering why you need to build a connector each time you need to call an API. Why can’t you just use the API directly? There are two advantages of using connectors over directly calling an API:

Low-code, no-code capabilities

Using connectors, even citizen developers can connect Power Apps with external systems without writing a single line of code. Remember the early days, when it used to require weeks or months to test a simple API provided by your client? You first had to prepare input parameters in the appropriate format (XML or JSON), then write code to send the input parameters to the appropriate API, and finally write code to parse the API’s response, returned in either XML or JSON format, to make it available for your business apps. Now, instead of worrying about building code to parse and process the data, you can test the API using connectors and use it within business applications in less than an hour.

Reusability

Connectors are an integration component that can be used by Azure Logic Apps, Power Automate, and Power Apps. Moreover, Power Automate and Logic Apps are trigger-based components that can be triggered by other Power Platform components like Power BI, Copilot Studio, Power Pages, and Azure components like APIM and Azure Functions. This means that once the connector is created, it can be used on both the Power Platform and Azure, as shown in Figure 4-24.

Figure 4-24. Connectors with Power Apps, Power Automate, and Azure Logic Apps

Components of Power Apps Connectors

You learned that connectors are used to establish the communication between your business apps and data sources. But when the connector should talk to the data source and what transaction or operation it should perform in the data source are described by its components.

Triggers and actions are the two primary components of any connector, as shown in Figure 4-25. Trigger specifies when the data source operation should be carried out, and action specifies which operation (create, update, delete, etc.) needs to be made in the data source. For example, in Figure 4-25, “When a new contact is created in Dataverse” is a trigger of the Dataverse connector, while “Send an email to the contact” is an action of the Outlook connector.

Figure 4-25. Trigger and action in connectors

Triggers and Actions in Canvas App Power Apps

As shown in Figure 4-26, in the left navigation panel, I have added a Microsoft Dataverse connector to create a contact in Dataverse and an Office365 Outlook connector to send an email to the customer. In the middle panel, I have added a blank screen and added a form to it. In the right navigation panel, I have set Contacts as a data source in order to create data in the Contacts table in Dataverse.

Figure 4-26. Adding a form to the canvas app screen

I have added the following Power Fx expression on the OnSelect property of the Button control, as shown in Figure 4-27:

SubmitForm(ContactForm);
Office365Outlook.SendEmailV2(emailTextInput.Text,"Contact Onboarding 
  Confirmation","Dear User, Your onboarding is successfully completed");
Figure 4-27. Power FX expression on Create button to perform operation (action) in data source

So here the trigger is when the user clicks on the Create button, and the action is to create data in Dataverse and send an email to the customer.

Triggers and Actions in Power Automate

As shown in Figure 4-28, I have added a Microsoft Dataverse connector that will trigger a Power Automate flow when a new contact is created in Dataverse, and an Outlook connector will perform an action by sending an email to that contact.

Figure 4-28. Trigger and action in Power Automate

Summary

In this chapter, you learned the fundamentals of the data sources and connectors used by Power Apps, as well as how different types of Power Apps interact with different data sources using various connectors. Additionally, you gained knowledge of the components, connector types, and architectural design of the connector.

This chapter demonstrated using Power Fx expressions to write business logic in a canvas app; however, I will dive into more depth in Chapter 9.

In the next chapter, you will learn how to decide which type of Power Apps is the best fit for your business needs.

Get Learning Microsoft Power Apps now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial