Direct Query vs Direct Import

[mrcraigrooney]

Hello,

I am looking for advice on best real world approach to which data type to use when building reports in Power Bi.

I have a database with about 10 years worth of transactional data. Data size is only about 40gb.

Its a live db with about 40 users, traffic is quite high and with transactional payments for about 10 FOH workstations (chip and pin)

I am looking to do daily reports for each of our business areas, all coming from the same db.

Would you:

Use Direct Query and do a single scheduled refresh overnight?

Export to CSV for each of the business areas (around 10 reports) and do a scheduled refresh to css?

Ive built basic reports, but nothing from SQL, all from excel. 

Im trying to find a real world approach and where people have most success.

[Anonymous]

Hi @mrcraigrooney ,

Did @audreygerred @samratpbi  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[audreygerred · Accepted solution]

Hello! I would opt for an import with incremental refresh. Direct Query should only really be used in a few niche cases - the two bigs ones are: the data is so large that it exceeds the limit of your Power BI SKU or you need near-real time 'streaming' type data. Power BI import mode is great at compression (especially when following good moedling techniques like star schema, good DAX syntax, one to many relationships, using int fields for keys, etc.). Your users can sign up for subscriptions so that they receive an email every day to remind them to go to the report. If you want the data exported, I would suggest pagination.

[samratpbi]

Hi,
First of all, you may check whether you really need to show all the tables and corresponding columns which makes up around 40GB of data. If not you may consider creating DB view to have only optimal data for reporting.
Next, I think better to keep in 1 report if same user needs access to all the business unit data, so that that person can change Business Unit using a slicer.
Lastly, if you are usiing direct query, then better to create a landing page  / Home page with list of filters ( business unit and if any others) and create sync slicer in other pages. So user would land on landing page, apply filters and move to other pages. In that way data size would be limited.

Hope this helps. If it does, then please mark it as solution, Thanks!