Skip to main content

Setting up OData with Microsoft Power BI

1. Introduction​

This guide will help you set up Facilio OData with Power BI to import and analyze your data seamlessly. By following these steps, you can connect Power BI to Facilio’s OData endpoints and retrieve all data.

2. Prerequisites​

Before you begin, ensure you have the following:

  • OData License: You need an OData license for Facilio. To inquire about obtaining a license, please email support@facilio.com.
  • Power BI Desktop: Installed on your computer. You can download it from the Power BI website.

3. Generate API Key for Facilio​

To connect Power BI with Facilio, you need an API Key. Follow these steps to generate one:

  1. Log in to Facilio: Go to the Facilio web app and log in with your credentials.
  2. Navigate to My Profile: Click on your profile icon located at top right corner and click your Name card.
  3. API Clients: Under "Management & Integration", find and click on “API Clients” tab
  4. Add API Client: Click on “Add API Client” to generate a new API client.
  5. Generate API Key: Provide a name (e.g., Power BI - OData) for your API client and generate the API Key. Note down the API Key, as you will need it later.

4. Getting Your OData Endpoints​

4.1) Generate OData Endpoint for Modules​

  1. Go to Facilio > Settings > Data Administration > OData Configuration > Modules.
  2. Click "Add Module."
  3. Select your required modules and click Save.
  4. In the table, you will find the OData endpoint for each module.

For more information about generating OData endpoints for Modules, please refer to the following documentation: OData Configuration for Modules.

4.2) Generate OData Endpoint for Readings (Timeseries data)​

  1. Go to Facilio > Settings > Data Administration > OData Configuration > Readings.
  2. Click “Add Reading.”
  3. Enter the Name, Description, Reading Type, Time Period, and Aggregation.
  4. Click “Save.” You will find the saved endpoint in the table.

For more information about generating OData endpoints for Readings, please refer to the following documentation: OData Configuration for Readings.

4.3) Copy Your OData Endpoint​

Copy the generated OData endpoint, and it can be used in Power BI.

5. Getting Your Facilio Data in Power BI​

5.1) Open Power BI Desktop​

  1. Open Power BI Desktop: Launch Power BI Desktop on your computer.
  2. Get Data: Click on the Home tab and select “Get Data.”
  3. OData Feed: Choose OData Feed from the list of data sources.
  4. Enter OData Feed URL: Enter the Facilio OData endpoint URL you copied earlier and click OK.

5.2) Configure Authentication​

When prompted for authentication, select Basic Authentication. Enter your Facilio email address as the username and the generated API Key as the password. Click Connect.

note

Do not use your Facilio account password. Instead, enter the generated API Key as the password in Power BI. This ensures your Facilio account password is not exposed to external systems.

5.3) Loading the Data​

Once you have entered the credentials, you will see a list of available tables. You can select and load the data.

6. Best Practices to Follow​

Refresh Schedule: If you have refresh schedules and have published your PBIX file to your Power BI cloud account, set the refresh interval during off-peak hours, such as midnight, to avoid additional load during working hours. Four refreshes per day are recommended.

Avoid Duplicates: Avoid creating duplicate tables for the same data, as it is unnecessary.

Module View Management: Once you’ve used the module views in Power BI, avoid modifying those Facilio views, as changes can affect the Power BI table during refreshes. Use a consistent prefix in view names (e.g., "All Work Orders – Power BI") for easy identification and restrict permissions to relevant users only.

Create Relationships in Power BI: You can create and manage relationships in Power BI to streamline data management. For example, you can create a relationship between the Parent field of Asset Readings and the Assets table. This allows you to visualize reading data in combination with asset fields.

7. FAQ​

What fields are supported for filters?
Only Date and DateTime fields are supported for filters on the OData endpoint.
What should I do if I encounter issues connecting to the OData endpoint (e.g., 500/401 errors)?
Ensure you are using the correct API Key and Basic Authentication method in Power BI. Verify that your API Key is valid. If issues persist, try clearing permissions under Transform Data > Data Source Settings and reconnecting.
How frequently can I refresh my data in Power BI?
You can set up a data refresh schedule in Power BI Service based on your data update frequency and business needs. Ensure you adhere to Facilio’s API rate limits to avoid any issues. Check the limitations here: OData Usage Limitations in Facilio.
Are there any limitations on OData endpoint hits or the number of records?
Yes, there are limitations. OData hits are limited to 100 per minute, and the number of rows returned in a single OData response is capped at 10,000 for modules and 20,000 for readings. However, we support OData Server-Driven Pagination, which Power BI handles automatically by iterating through pages until the last page or 1 million rows are retrieved, whichever comes first. Therefore, the 10k/20k row limits should not be a concern.

By following these steps and considering the FAQs, you should be able to successfully set up Facilio OData with Microsoft Power BI.

In This Topic