Forum Discussion
Datasource questions for PowerBI
I'm not an authority on performance but I strongly suspect that your best option could be to have the devices spit the data into plain vanilla CSV files in a folder without doing anything fancy at all, and then just import the files using the "Folder" method. That's right, don't even store the files in a DB unless there is some other reason why you would do that. I've had some pretty surprising results in this regard. If it is easy to test, this is what I would do.
hello erik_tarnvik
The raw data requires some cleaning and processing ( calculations ) before its in usable format for powerbi hence the route through the sql database. The data actually comes in xml format. I have considered doing all the cleaning and calculations in powerbi but decided against it because I thought shifting all that to the server side is better for performance.
After I clean up the data, my options are:
- Use directquery
- dump the output of the server, hourly, in a csv format into an Azure blob for powerbi to access
- import the entire db directly into powerbi hourly ( doesnt sound efficient )
I am hoping someone can point me in the right direction of whats best / optimal
I recently did some testing on a reasonably large dataset (>3m lines) and found Power BI to be quite efficient at reading CSV files, although there was not much cleaning involved in my dataset. I also had some non-intuitive results. For example, I would have expected reading one CSV file with >3m lines to be more effecient than reading 500 files with the same total amount of data split among those 500 files.
That was not the case, Power BI read 500 files in about half the time it took to read the exact same amount of data in one file. My conclusion would be that there is no real substutute to testing various approaches before selecting one that works for you. I would go ahead and test your method 2 and see if you get adequate performance that way. I would be interested to hear whqt you learn if you do, I have some similar questions of my own and your input would be interesting!