There are many different analytic platforms that our customers can use to visualize their Fulcrum data. Outlined below are three ways you can connect your Fulcrum data to Power BI.
Data shares are publicly available URLs that provide access to your Fulcrum data. Using a data share link you can get your apps data into Power BI. You can access your apps data shares by clicking on the name of your app from the Fulcrum homepage and clicking on the data shares icon.
Once there you will want to enable data shares if you have not already done so by clicking on the enable data shares button. Next, navigate to the CSV tab and copy the link that is displayed.
In Power BI you will want to click on the Get Data drop down and press the Web connection button. Here you can place your data share URL in the URL field and choose basic as your connection type.
After pressing OK you will be presented with a preview of your data. You can keep the options of “65001: Unicode (UTF-8)” as your File Origin and “Comma” as your Delimiter and then press the Load button. Your data has been added to Power BI and you can create your dashboard.
In the event that you want to connect more apps than you have data shares on your Fulcrum plan you can connect your Fulcrum data to Power BI through the use of our Query API. The Query API allows you to connect a specific set of data through a web link. The Query API web link has a few different parts to it. The base URL https://api.fulcrumapp.com, the endpoint /api/v2/query and the parameters of token, format, and query. When you piece them all together you get something like this https://api.fulcrumapp.com/api/v2/query?token=abc123&q=SELECT * FROM "Building Inspections"&format=csv
This is the type of link that you will use to connect to Power BI. You can get your API token from the Fulcrum web app by clicking on the API link on the navigation bar. From there you will click the create new token button and retrieve your token.
Creating your query to pull in your data could be as simple as getting all the data from your app with a select all statement like this SELECT * FROM “App Name”. These queries can also get complex if you are trying to combine data from different apps within Fulcrum. Our professional services team can assist you with creating more complex queries if needed.
In Power BI you can use this link by clicking on the Get Data menu and select Web. You will select Basic and place your link into the URL box then press OK.
On the next screen you will select “65001: Unicode (UTF-8)” as your File Origin and “Comma” as your Delimiter and press the Load button.
You have loaded your Fulcrum data as a data source in Power BI and can now create your dashboard.
In the event that your queries are exceeding the 10 second processing limit on the Query API you can use Fulcrum Desktop to synchronize your Fulcrum data to a local database and connect that local database to Power BI.
To setup Fulcrum Desktop see our blog article. https://www.fulcrumapp.com/blog/fulcrum-desktop-with-docker/
After you have Fulcrum Desktop synchronized to a local database you will press the Get Data button to open up the full Get Data window. Click on the Database filter and then select the type of database that you have Fulcrum Desktop Synced to. After you press the connect button you will be prompted to enter in your server and database details.
After filling out the server information you will be prompted to enter your username and password credentials.
Once your database is connected you can select the tables you want to add as your data source and press the load button.
Your data has now been loaded into Power BI from your local Fulcrum database.
1. Get Data from Web
Paste Fulcrum Shared View JSON link as ‘Basic’
2. Click into the rows List
3. Convert the List of Records to a Table
4. Click the Columns to Expand button. Uncheck ‘Use original column name as prefix’. Click OK. Close & Apply.
5. Have fun with your Fulcrum data in PowerBI!