Using Ouvvi Apps to Feed Data into Power BI

A little while ago, when we first released Ouvvi Apps we mentioned that they could be used to feed data into Power BI Reports. So we thought it was about time we showed you how you can actually do this with an example.

This example uses CSV files as the source dataset but it could make use of any of our connectors. Normally you would connect this to your internal system which might be a SQL tables, SharePoint Lists or Dynamics Entities.

Our sample data is split over multiple files, separate sales data with a linking customer number and customer details.

This whole scenario will work best (due to credentials and permissions) when Ouvvi is exposed on an endpoint in your network. Ideally you would also apply an SSL Certificate to keep the connection secure.

Data Cleaning and Transformation

We like to clean and transform our data before using it, and used Data Sync to combine, transform and extract what we need to ultimately be passed to Power BI.

To combine the two files, we used a lookup to gather the customer information to go with the sales data. We have then used calculated columns to combine the customer name into one field and also generated a sent status column so then we can know which data has been added on each run of the project. This makes sure that the data passed to Ouvvi Apps is in the format we want, and there's less work for you to do in Power BI.

Configuration

The whole project is contained within our internal Demo Ouvvi site that is exposed on a public endpoint in our Network with an SSL certificate. Our Ouvvi Project is made up of a few steps (some are optional).

1. Data Sync Project to tidy the data and sync it to an Ouvvi App
2. A Data Sync Project to set the SentFlag to true (this is only needed if you want a report on the data that changes.)
3. An Ouvvi Email Step to notify users that the report has been updated and need to be refreshed.
4. A Report Email Step to summarise the data changes. You will need to include the SentFlag Update step for this.

We make use of Ouvvi Apps due to the capability of exporting the data in multiple formats, for this example we use the XML feed to input the data to the Power BI Report.

If you haven't used Ouvvi Apps before, check out our documentation to see how to get started.

Get the data into Power BI

Once you have your data into your Ouvvi App, you can import it in to Power BI Desktop using the XML export option.

Get the XML export URL (you can remove the limit filter) and open Power BI. This URL will look similar to: https://demo.lab.simego.com/apps/xml/demosalesdata

Got to Get Data , choose the Web connector and enter in the URL.

You will then be prompted to enter your credentials to your Ouvvi site so that the Data can be accessed, choose the correct type (this might be windows or basic auth). In the next window click Transform Data, and then click Table to open the data into a Table. You can now move on to building your report.

Report

Our sample Sales Report looks like this:

We have a series of KPIs with some conditional formatting on the background so then you know if targets are being met, surpassed or are falling behind.

We have a breakdown of the sales by product (as a pie chart and in table format), a breakdown of sales by location (on a map and in table format), and a graphical representation of the sales over the last 12 months.

Email Notifications

If you use the Email Step when records are added to the data or the data is changed, an email will be sent out to notify the recipients. They can then refresh the model to see the changes.

Another step to consider, if your data changes are not too large, is to have an additional email step sent out with the data that has been added and/or changed (fourth step). This is where the second step (updating the sync flag) comes in, as it marks the items that have been added as sent.

Using the report email step you can add the URL to the HTML output from your App with a filter applied to only return the items with the sent flag set to false (the new data). So your URL output would look like https://demo.lab.simego.com/apps/html/demosalesdata?limit=1000&pivot=1&filter=IsSent%20eq%200.

Schedule

Now you need to schedule your project so that it either runs at a certain time interval or on an event (when data is changed). For example run it every hour (during business hours).

If you have set the run condition of the steps below the first Data Sync project to run on SuccessDataChanges (do this by selecting the option from the dropdown) then you will only receive the email notification when there are updates or additions to the data.

This scenario might be useful to your current integrations. Why not extend your current Ouvvi projects to include these steps and get updated Power BI reports and reports of data changes.

Send us an email if you have any questions and we can help get you started.