where to build a dataset

[Anonymous]

I am looking for a neat way to build a dataset. We are recently using power bi, so I am in a search to create a sustainable environment for our data future

I am working at a production site, and want to build a report to detect flaws in production. We are using NAV. Due to policy, I cannot use OData streams . However I can use NAV data, and create views on NAV data. 

The report is getting data from 4 tables:

• prod_order line
• production batch line
• prod order component
• item

in the two years we are using NAV, we have made over 500.000 products. Each produyct is build up from 10 to 20 components. Each component has to be cut to the right measurements.

Production is using the report regularly, so I will use direct-query.

I think I have the following options to create a dataset.

• get data from the NAV DB, use the power query editor to make joins.
• make a view and make joins at the DB server. use this view as a single dataset
• other? 

Wat is the best way to get the data fast?

[JosefPrakljacic]

Hello @Anonymous,

I would advise you to keep the joins in the database.

Why? ( Shortened)

If you use PowerQuery, PowerQuery will try to get a similar query as you would write in the DB, but it may not be able to do that.

Now you would have the problem that the indexes that are in your DB are not used e.g.

So I would always keep everything possible in the DB and then access the views. Your DBA also has more possibilities regarding performance optimizations.

If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others

Have a nice day!

BR,
Josef
Graz - Austria