Exploring the Integration of Claris FileMaker and Microsoft Power BI

By /

Unleashing the Power of Data

In the ever-evolving realm of data management and analytics, companies are constantly looking for effective ways to collect, organize and understand their data. Claris FileMaker and Microsoft Power BI are two powerful tools that play a crucial role in this field. In this article, we’ll take a look at what each of these platforms has to offer, and explore the possibilities for seamless integration between Claris FileMaker and Microsoft Power BI.

Claris FileMaker: A Foundation for Custom Apps

Claris FileMaker is a versatile and user-friendly platform that empowers individuals and organizations to create custom applications without extensive coding knowledge. With its intuitive drag-and-drop interface, FileMaker enables users to design, develop, and deploy tailored solutions for managing various aspects of their business processes.

Key Features of Claris FileMaker:

  1. Rapid App Development: Create custom applications quickly and efficiently
  2. Cross-Platform Compatibility: Run applications on Windows, macOS, iOS, and the web
  3. Integration Capabilities: Connect with other systems and services seamlessly
  4. Security and Access Control: Ensure data security with robust authentication and authorization features
  5. Scalability: Grow your applications alongside your business needs

Microsoft Power BI: Transforming Data into Insights

On the other hand, Microsoft Power BI is a powerful business analytics tool that transforms raw data into meaningful insights through interactive dashboards and reports. It allows users to visualize and analyze data, share insights across the organization, and make informed decisions.

Key Features of Microsoft Power BI:

  1. Data Visualization: Create compelling visuals with a wide range of chart types and interactive elements
  2. Data Connectivity: Connect to various data sources, including databases, cloud services, and on-premises data
  3. AI-Powered Insights: Leverage AI capabilities for pattern detection, forecasting, and natural language queries
  4. Collaboration: Share reports and dashboards with stakeholders for collaborative decision-making
  5. Scalability: Scale from individual use to enterprise-wide deployments

Integrating FileMaker with PowerBI

Integrating Microsoft Power BI with Claris FileMaker can be approached in several ways, depending on the specific requirements of your project. Here are some key methods for integrating these two powerful platforms:

1. Export and Import Data:

  • Overview: Export data from FileMaker in a compatible format and import it into Power BI.
  • Integration Steps:
    • In FileMaker, create scripts or export schedules to export data in formats like CSV, Excel, or XML.
    • In Power BI, use the Power Query Editor to import data from the exported files.
    • Schedule data refresh in Power BI to keep the information up-to-date.

2. Direct FileMaker Data Connection (Using ODBC):

  • Overview: Utilize FileMaker’s support for ODBC (Open Database Connectivity) to establish a connection between FileMaker and Power BI.
  • Integration Steps:
    • Enable the ODBC sharing option in FileMaker Server and set up the communication port.
    • Set up security in the FileMaker database using FileMaker Pro.
    • Install the FileMaker ODBC driver and set up an ODBC data source on your machine that points to the FileMaker database.
    • In Power BI, use the ODBC connector to connect to the FileMaker data source.
    • Build Power BI reports and dashboards using the live data from FileMaker.

3. Power BI REST API Integration:

  • Overview: Power BI provides a REST API that allows you to programmatically interact with Power BI service resources.
  • Integration Steps:
    • Enable the Power BI REST API in your Power BI account and obtain the necessary API key.
    • In Claris FileMaker, use the “Insert From URL” script step to make HTTP requests to the Power BI REST API endpoints.

4. OData Connection:

  • Overview: Leverage FileMaker Server’s OData sharing capabilities to establish a direct connection from Power BI.
  • Integration Steps:
    • Enable the OData sharing option in FileMaker Server.
    • Setup security in the FileMaker database using FileMaker Pro.
    • In Power BI, use the native OData connector to connect to FileMaker.
    • Design reports and dashboards using live data from FileMaker.

Selecting the appropriate integration method depends on factors such as data volume, real-time requirements, and the desired level of automation. Consider the specific needs of your project to determine the most suitable approach for integrating Microsoft Power BI with Claris FileMaker.

But the last method listed, an OData connection, is intriguing and deserves a little more attention. Let’s explore FileMaker data integration with PowerBI using an OData connection.

What is OData?

As stated on the official OData website, “(Open Data Protocol) is a […] standard that defines a set of best practices for building and consuming RESTful APIs.”. OData – the Best Way to REST

OData is an open standard that is a result of collaboration within the technology industry. The protocol was initially developed by Microsoft but has since become an open standard under the guidance of the OASIS (Organization for the Advancement of Structured Information Standards) consortium.

This means that the syntax and methods you use to obtain data from FileMaker would be the same as those you would use to obtain data from another OData-supporting application, Microsoft Sharepoint for example. The results you get from these different applications would have the same data structure definition. Microsoft, SAP, and SQL server are examples of technologies that produce OData.

Using OData, you can:

  1. Request resources
  2. Make requests
  3. Create new resources
  4. Link resources
  5. Invoke functions

All of the above actions use the same syntax and methods in all supported applications.

With OData, there’s no need to install drivers, configure the data source name or open special ports in firewalls (like with an ODBC connection). OData will use the secure port 443. This greatly simplifies the configuration. The risk of a component malfunction is very low, just like the FM Data API or any other Web service API.

On the FileMaker platform, OData is supported since FileMaker Server For Linux 19.1.2 and since FileMaker Server 19.5.1 for Windows and OSX operating systems.

Transferring Data from Claris FileMaker into Power BI using the OData standard

Now that you know what is OData, let’s explore the steps to share FileMaker data with Power BI using the OData standard.

Configure FileMaker Server

  1. First, we need to enable OData sharing in the FileMaker Cloud or Server administration console. This option is available under the Connectors tab.

Configure your database

  1. Next, we need to configure our FileMaker file, which will share its data via OData. We create a new user with a complex password and limited privileges.
  2. And finally, this privilege set must have the fmodata extended privilege.

Configure PowerBI

  1. Open PowerBI and click on Get Data.
  2. Search for and select OData Feed. Then click Connect.

3. You will be prompted to enter the URL of your OData feed. The URL should look like this:

https://[hostname of your FileMaker Server]/fmi/odata/v4/[Your database filename]

4. On the next screen, you’ll need to select the authentication method. Select Basic and enter the username and password of the account that has the fmodata extended privilege. Click Connect.

5. You can now select the tables you want to load in PowerBI. You can preview each table’s data by simply clicking on the table’s name. Click on Load when you’re done.

6. The selected tables will load in PowerBI, and you’ll then be able to use them as data sources for your PowerBI charts and dashboards.

Conclusion

The integration of Claris FileMaker and Microsoft Power BI opens up a world of possibilities for companies looking for a complete data management and analytics solution. By combining FileMaker’s flexibility in creating custom applications with Power BI’s robust analytics capabilities, companies can take a streamlined and efficient approach to processing their data, enabling them to make more informed decisions.

If you have more questions about integrating Microsoft PowerBI with Claris FileMaker or want to collaborate on implementation, please get in touch with us.