Integration how-to: Power BI

A few customers have been interested in powering up their analytics with tools such as Tableau, Qlik or Power BI.

agileBase has built in charting which is great for quickly analysing and answering questions about data in your system, but specialist business intelligence apps can be particularly useful if you want to join data from multiple sources (perhaps an accounting package or marketing automation system too) in one place.

Thanks to one customer, Lewis Pies, we now have a great how-to guide on integrating with Power BI using our easy to use API. They’ve done all the work of investigating the Power BI side of things! It’s a great example of how to use the API in general to integrate with third party software.

powerbi

Setting up

Without further ado, here’s how an admin can set up the integration. It looks like lots of steps but that’s just because we’ve split it up into very small, simple parts to make it as easy as possible to follow.

Step 1: set up a view in agileBase to use as a data source

Tip: use a view bringing in as many possible variables into one place. For example, if you want to analyse sales, you might include totals by customer, product, salesperson, region, category, price banding and date. That way you have plenty of dimensions to analyse in multiple ways later on.

Step 2: turn on the API for the view to make data available

1 ab

Under the admin interface of AB select the manage option on the view and then the ‘send’ option.

Select ‘send data to a third party system using the API’ in the dropdown list and make sure you tick ‘allow access from third party software using an API key’.

Refresh the view to show your API key needed for the next step.

1 ab

Step 3: within Power BI press ‘get data’ and select ‘Web’ as the data source

2 pbi

Step 4: enter the sync details provided by AB into Power BI

Select the advanced option and fill in the following URL parts provided from the AB view setup in the first point. These are the parameters to add:

?get_report_json=true
&c=[your ‘c’ value here from AB]
&t=[your ‘t’ value here from AB]
&r=[your ‘r’ value here from AB]
&json_format=json
&simple_format=true
&row_limit=250000 (or however many rows you need)

3 pb

Step 5: enter the authorisation key into Power BI

Select ‘authorization’ from the dropdown options under ‘HTTP request hear parameters (optional)’ and press OK. The authorisation key is provided in the Agilebase view.

4 pb api key

Step 6: make data visible in Power BI

Once the data has pulled through and the connection is set up you will need to convert the dataset into a table.

5 pbi convert to table

Step 7: expand the colums

Once converted to a table you will then need to expand the data into columns and your view will be presented to manipulate.

6 pbi expand

Step 8: go wild with visualisations!

powerbi

 

 

 

 

 

Advertisements

2 Replies to “Integration how-to: Power BI”

  1. Ah yes, I see what you mean. I’ve just updated the theme, they’re now a bit bigger. Still a bit squinty, but if you right click the images and say ‘open in new tab’, or just zoom in, you can see them a bit better

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s