Do Aggregate support Analyze via Odata like Central?

Dear all,
I did get data from ODK Central to Power Bi via Odata, it work very well, data is live transferred . Now I'm trying to find a way to do that with ODK Aggregate. Pls help.

Hi @dmtk41

The answer is no, ODK Aggregate doesn't work with OData.

1 Like

Hi @Grzesiek2010,
so is there any way to get live data from ODK aggregate to Power bi

As @Grzesiek2010 indicates, there is no direct linkage to OData from Aggregate, but you could perhaps stream (almost) live data from Aggregate to a custom-written service, which in turn feeds your PowerBI. See Getting Data Out of Aggregate. You basically just have to setup a custom webservice to receive the data feed out of Aggregate, at which point your service can do anything it wants with the raw data.

Hi dmtk41
Yes there is a way, but not as slick as with OData and the work is mainly on the PowerBI/SQL side.

Depending on where your data is and if you are using PowerBI service or desktop version will dictate the connection method. I have Aggregate on premise running on an MS-SQL database and am using the PowerBI service. To connect to the database I had to set up a Microsoft PowerBI "Gateway" (and add the appropriate firewall rules) that allows the PowerBI service to traverse to my internal network securely. To do this you will need to provide the gateway with an appropriate SQL account with which to connect.

If you are using the desktop client and have direct account access to the Agreggate database you can use these details as a data source.

Once you have done this you can use aggregate tables as data sources for PowerBI.

As stated, nowhere near as skick as OData and you are connecting to database tables so will need to understand the datbase structure of your forms; but it can work.

Hope this helps.

1 Like

Yup. If you have the necessary access permissions, pulling data directly out of the Aggregate database tables is certainly another means to 'integrate' with other data analysis tools. But please be aware that there is no particular guarantee that the internal database structure (of Aggregate, Central, ...) will remain unchanged between releases... Unlike, say, the ODK XForms Spec, which is a formal specification document against which you can reliably write compatible tooling, the internal database structure isn't formally specified, and may change as needs arise; certainly Central's current database table structure is subject to change.

1 Like

Another option (and I haven't tried this, so caveat emptor) is to use Briefcase's command line interface to pull the data from Aggregate, export to CSV, then connect PowerBI to those CSVs.

1 Like