Optimizing workflow data load and execution

[abhisekm]

Hi All,

We are using powerbi desktop for dashboarding and using excel for data loading, appending. The data file we are loading is quite big in size and also it takes lot of time to load. For ex if 50 million records its taking 5 hours to load, and also if there are some data mistakes in the excel file again a lot of time is taking to reload it.

We were thinking of a solution - install sql server express on local system, create the table there and load the data from excel to the sql table and put condition to check if there is any data issue or not like integer data value is getting loaded into integer columns. And then connect this sql server to powerbi dashboard. 

The data to excel will load to sql server and then powerbi automatically refreshes it.

Now question is - Will this be robust process and ensure we save time in overall process? 

[Adamtall]

Hi,

You will save alot of time with SQL express.

In powerBI you can use Direct Query for reading the table from the database and you will have almost zero refresh time. (When you filter in PowerBI it will look in the database and give you the result direct from the database.)

/Adam