cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rein Frequent Visitor
Frequent Visitor

where to build a dataset

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 Smiley Sad. 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?

1 REPLY 1
JosefPrakljacic Established Member
Established Member

Re: where to build a dataset

Hello @rein,

 

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 182 members 1,951 guests
Please welcome our newest community members: