Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.