Advice on best methodology for connecting to data source

[steveperris]

Hi !

I would greatly appreciate any suggestions on the best direction to take to build a cloud-accessible data set for use with Power BI etc. At this stage it is sales data, customers, invoices, invoice lines with costs, margins etc.

Our on-prem LOB suite is presently running on older foxpro.dbf files. The vendor has indicated they do not support integration with other apps as yet, so its on our head if we go messing with it. Naturally I decided to mess with it...

I have already tested the outcome I am looking for (a functioning BI using the data) by building a local excel powerpivot model with a Foxpro ODBC Connection linked to a *copy* of the foxpro tables from our LOB app. No problem then with writing SQL queries to pull in the necessary data into the pivot tables, and creating the outputs we wanted (slicer controls etc). .

In anticipation of building a cloud-only solution, I have already built an access dbase extract which (again with the right queries), pulls just the data we need to use, and makes that available in Access table format, and refreshes on a daily basis - thus making that Access data effectively live.

Getting to a cloud-only result seems to has the following challenges:

1. BEST DATA FORMAT

What will be the best final data format to be surfacing in BI? Will the Access tables be the optimum data format for using with BI?

2. SURFACING THE DATA

What is the fastest / safest recommended method for surfacing the data to the BI connection? What about scheduled refresh?

We presently have all the necessary configs setup to surface the sample Access tables directly into a sharepoint list or library, with a scheduled sync. Note, this is direct into a sharepoint team site location, NOT using onedrive or onedrive for business.

Can we connect to an access mdb which is stored in a sharepoint team site locations? Will it be fast enough?

Shoudl we use the BI connector and create the connection directly to the on-prem access tables? Or directly to the on-prem foxpro tables?

Thanks for any thoughts or guidance.

Steve

[gustavoleo]

The only chance that you have to work with DBF files on Power BI is

using Devart OCBC Driver here -->> https://go.helpbi.com/dbf and Power BI Dataflows

First, create a Dataflows and then on another dataflow( if you have premium per user) make all the data mashup on a reference Entities /Table.
If you don't have a premium, make your transformations on Desktop from your DBF converted to a Power BI Dataflows

[MarkJF]

Hi Steve

I have been  waging a similar battle and at present my solution is to use Sharepoint as the imtermediary stage, allowing reasonably regular refreshing of the data for reporting purposes.

If you are still interested, please get in touch.

Regards

Mark

mark@mfpl.com.au