%20(1).svg){kind=logo}
Power BI: Connecting Unique Data Sources with Custom Connectors
Discover how custom connectors in Power BI enhance access, security, and usability for complex data sources. Get tailored authentication, smoother transformations, and connect to sources not natively supported—maximizing your Power BI integration.
Power BI is a very powerful tool when it comes to data integration. It offers a variety of connectors that allow you to extract data from all different kinds of sources. However, sometimes even those connectors don’t offer the functionality you need. That is when custom connectors step in. In this article we will explore the capabilities and limitations of custom connectors, how they can be beneficial to your organization and what to expect if you decide to create a connector for your unsupported data source.
Understanding Custom Connectors in Power BI
Why not just use a generic connector like the ODBC or the web connectors that power BI provides?
Even though these connectors offer flexibility and freedom in choosing your data source, they are still not compatible with everything. For example, the built-in Web connector, usually used to connect to a REST API, doesn’t handle OAuth2 type of authentication. One could still argue that this can be handled in Power Query queries, but generally, this is not a safe nor reliable option. Custom connector differs from this solution in the way it handles authentication. Because the connector creates a recognized data source, Power BI is able to store usernames/password or client ids/client secrets without the need to hard code them into your query or retrieve a new token on each call to the API.
Among other benefits of using a custom connector are predefined transformations. Why define the transformation process many times in your organization when you can define it in one place - your custom connector - and then use it as many times as you wish. This can be used to ease data preparation and data cleaning or other repetitive tasks your analytical team spends too much time on.
Common Usage
To better illustrate my point, here are some common scenarios where using a custom connector would be a useful solution:
Proprietary or In-House Systems
Company uses a proprietary or in-house CRM or ERP system that is not supported by Power BI’s built-in connectors.
Industry-Specific Data Sources
Organizations in industries like healthcare, finance, logistics or hospitality industry often use industry-specific platforms that do not have existing connectors.
Legacy systems
Many organizations have legacy systems that are critical to their operations. Many of them lack modern data integration capabilities. Using a custom connector allows you to tailor the solution specifically for your legacy system.
Enhanced Security and Compliance Requirements
Custom connectors can be developed to adhere to specific security standards and compliance requirements making it a good option to ensure secure and compliant data access.
Custom Authentication Mechanisms
In scenarios where unique or complex authentication mechanisms are in place, developing a custom connector makes it possible to connect to a data source with such requirements.
You see that custom connectors bring value mostly when it comes to enhanced security and niche solutions. But they are also a valuable asset in large companies where they allow Power BI non-professional to connect to sources that would require difficult transformations and data preparation. With a connector with predefined transformations everybody can visualize their data.
Prerequisites for Developing a Custom Connector
Before you decide to jump into the development of a custom connector, you should have a good understanding of M Language and Power query. The connector is created in Visual Studio Code using the Power Query SDK extension and the development process can be quite frustrating at first, especially if you have little experience with those tools. The whole process of setting up your development environment can be found here.
When developing a data connector, it's essential to have a deep understanding of the data source as well as the requirements for the final product. It should go without saying that thorough knowledge of the data source is crucial for building an effective connector.
Take the time to truly understand the data that flow through your system and analyze all possible setbacks you may encounter because of the data. Then think about what kind of data you exactly need from the source and in what form would you like the data to be retrieved. This will save you a lot of time in the development process.
Step-by-Step Guide to Creating a Custom Connector
After you finish gathering the business requirements for the new custom connector and conduct a thorough analysis of the data source you intend to use, it is time to jump straight into development. If this will be your first time developing a custom Power BI connector, it is a good idea to go through the TripPin tutorial available in the Microsoft documentation. This guide will show you the essentials of custom connector development. Because this step by step guide covers most of the concepts in this article, we will focus more on what we could call best practices of custom connector development.
Although the order generally does not matter, every connector should start with the definition of the data source.
What you can see above is the DataSource.Kind and Publish functions which define how Power BI views your connector. On the second line is a shared function that works as a main block of your connector. It is the first thing processed when the connector is initialized. This function uses Value.ReplaceType to call NavTable and Type. You can find more on data source definition in the Power Query documentation
The DataSource.Kind defines your authentication type and display label. Note that you can define multiple authentication types for your data source allowing the user to choose. More information on authentication here.
The Publish object is used to define how Power BI presents your connector in the list of connectors. You can define the category, label and source images.
The ButtonText attribute takes in a list of two values. The first one is the name displayed in the list of connectors and the second one is a tooltip that is displayed on hover.
You can see that SourceImage and SourceTypeImage attributes are referencing another object. YourCustomConnectorName.Icons is an object that contains paths to icons that the connector uses with the function Extension.Contents(). Use a software like Gimp to create the icons in the required sizes.
It is a good practice to initialize global variables early in your code. When connecting to an API, you can set up headers or include an API key for authentication now. Additionally, if you want to give users the ability to choose from various API endpoints later, you can define these endpoints now. This way, you can easily integrate them into the user interface and use them in data source queries.
The function Extension.CurrentCredential()[Key] is used to obtain the key from the authentication form.
Next up we will define the Type object. This object sets the UI for your connector using the Documentation and Formatting functions in M code.
Although there are some issues like Documentation.Description conflicts and the UI is fairly limited, you can still create a functional and easy to use UI for you connector.
The other object that we used earlier but is still not defined is the NavTable or navigation table. If you went through the TripPin walkthrough you are already familiar with the concept of navigation tables. In short it is a table that allows you to navigate in your data source using predefined transformations. In the TripPin example by Microsoft this has been used to choose between endpoints of the TripPin REST Service.
The Data column contains the actual predefined queries to your data source. Another useful note is using helper functions from Microsoft. In this case it is the Table.ToNavigationTable function. This function is not predefined and you will need to define it yourself. Luckily, Microsoft has already taken care of that part for you here.
Now that you have the necessary parts done, here comes the fun. It’s time to define the actual queries to your data source as you put them in the navigation table. It is what you are used to do in Power Query but wrapped in a function. I suggest running the queries simultaneously in power Query to troubleshoot and debug your code.
Testing your Connector
Until now all your work has been in the .pq file of your project. However, if you want to test the connector without building and importing it to Power BI the Power Query SDK gives you the option in the .query.pq file which looks something like this.
The Power Query SDK will not run the whole connector, so don’t expect to see the UI. You have to hard code the parameter for the shared function that we defined at the beginning. What you see above is what the user will see if they open the advanced editor when using your connector in a Power Query query.
To run the test you have to first set up the credentials. The SDK lets you choose the authentication method and enter your credentials. After that hit Evaluate current file. In our case you should be able to see the contents of your navigation table. More on using Power Query SDK to run tests can be found here.
Deploying your Connector
If you truly want to see your connector in action, you will have to build a .mez file. This file translates the connector for Power BI and allows you to use it in your desktop application.
Use Ctrl+Shift+B or go to section Terminal > Run Build Task… and select the MakePQX build. This will create a .mez file in /bin/AnyCPU/Debug path in your project. To run the connector from Power BI you need to copy this file inside the [[Documents]\Microsoft Power BI Desktop\Custom Connectors path.
Last thing before you will be able to use the connector in Power BI is allowing ‘Any extension to run without validation or any warning’. You will find this option in Power BI`s Options and Setting > Options > Security > Data Extensions.
Now all you have to do is restart Power BI and then choose your connector from the list of available connectors and load the data into Power Query!
Deploying to Power BI Service
Custom connectors are not supported by Power BI Service by default. To use it in Power Bi Service you have to get your connector certified by Microsoft. With this certification the Power BI Service will be able to read the custom connector through your on-premises data gateway and make refreshes through this connection. More on how to set up this connection with a certified connector can be found here.
Common Challenges and Troubleshooting Tips
Developing a custom connector can be a challenging task, especially if done for the first time. Here are some common challenges that you might encounter and tips on how to tackle them.
Performance Optimization
When dealing with large sets of data or complex queries, one might run into performance issues. Performance issues are more common among custom connectors than built in connectors.
- Optimize your queries to retrieve only necessary data.
- Implement pagination or batch processing for large amounts of data.
- Keep your queries simple.
Error Handling and Debugging
There is no easy way to debug your M code. You can test out your queries in the Advanced Editor in Power Query but other blocks of code in your connector might not be so easy to debug.
- The forum is your best friend. Don’t hesitate to ask.
- Break down the process into smaller manageable parts.
Data Source limits and quotas
You might run into a situation where for example the API you are working with won’t allow you to retrieve all the data from the source due to set limits. Exceeding the limit may in some cases result in a temporary ban or throttling.
- Optimize your queries to make the least amount of calls to the source.
- Implement a retry logic if necessary.
- Know your data source.
Compatibility and updates
Most APIs have the nasty habit of changing. Some of the minor changes might even go unnoticed and some may be able to break your connector entirely.
Keep that in mind when developing. Develop a maintenance plan to update and test the connector periodically. If the API provides versions, use them.
Write clean, easily manageable code that you can update when necessary without rebuilding the connector from the ground up.
Conclusion
Creating a custom connector in Power BI can significantly enhance your data integration capabilities, allowing you to connect to unique or proprietary data sources that are not supported by built-in connectors. While the process involves several challenges, such as managing authentication, handling various data formats and optimizing performance, these obstacles can be overcomed with careful planning and a methodical approach.
If you’re looking to develop custom connectors or need assistance with Power BI, our consulting services can provide you with the expertise and support you need. We have experience in building and deploying custom connectors that meet diverse business requirements, ensuring seamless data integration and robust analytics.
Ready to unlock the full potential of your data with custom connectors in Power BI? Contact us today to learn how we can help you develop, deploy, and maintain custom connectors that suit your specific business needs. Visit our website for more information and to schedule a consultation with our experts.
