Exporting data to Powerbi
-
Anyone has any ideas on how we can get automated exports to Powerbi for better visualisation?
-
Hey @B-M, Great question.
We have 2 metrics related plugins.
https://disciple.tools/plugins/metrics-export/
and https://disciple.tools/plugins/data-reporting/I think https://disciple.tools/plugins/data-reporting/ is the one we'll want. I've forwarded your question and waiting to hear back with confirmation.
-
I'm the maintainer of the Data Reporting plugin and currently use it to send data into Google BigQuery. There is a provider for streaming data into GCP BigQuery, but I use a combination of cloud functions and cloud storage to import CSV files so that you don't incur the cost of streaming updates.
The plugin breaks out the data into tables for contacts, contact activity, groups, and group activity.
If you have a different data storage behind your Power BI that's not GCP, you can either do a similar cloud function approach (the plugin has built-in support for sending data to a webhook URL for you to process however you need) or we could look at another data provider integration.
-
@CairoCoder When you say, you can do a cloud functions approach, can you list the steps of how you carry this out in GCP and we’ll try to replicate that in Azure.
-
@B-M Here is the overview that I have in the plugin's backend settings screen:
To stay within free usage, we are going to save data to Cloud Storage and load those files into BigQuery instead of streaming data directly into BigQuery. Because of this, there are 2 different Cloud Functions that will be utilized.
As an overview, these are the steps that will be taken:
Cloud Function (HTTP): Receive JSON data from plugin. Save as JSON line-delimited file in Cloud Storage.
Cloud Storage: Bucket will temporarily hold generated data file.
Cloud Function (Storage trigger): Function is triggered when a new file is uploaded to storage bucket. Meta data will be read to know what table to load the data into, and the file will be loaded into BigQuery.
BigQuery: Holds data ready for reporting. Easy to connect to various visualization toolsLet me know if that's helpful. I could share the javascript code that I use for the functions, but I would have to remove some proprietary logic that I added to mine to support some other specific business needs.
-
@CairoCoder We will explore the same with Azure. Store the data in Azure Blob Storage and then feed it into our data lake. We’ll benefit from the JavaScript file, so please share so that we don’t reinvent the wheel
-
@B-M I've uploaded my samples to the plugin's github repository in the documentation folder: https://github.com/cairocoder01/disciple-tools-data-reporting/tree/master/documentation/cloud-functions
There are readme files that should help explain what is all happening and the different features. Let me know if anything is confusing or hard to decipher.
-
@CairoCoder thanks
