Forum Discussion
New to BI. Writing SQL queries to parse data. I am obviously missing something.
Personally, as a SQL person first and foremost who is now working in PowerBI, I would have to say, I take the opposite approach and also advise others to take the opposite approach as well if performance is at all importand.
When you do all fo the filtering after you bring in the data, it has a much higher impact on the SQL server every time you refresh the data. Plus, it has to transport everything across the network just to throw it away.
My rules personally would be as follows:
For a one time data pull - not something that will be regularly refreshing OR if you can't write decent SQL code OR if you database is on a strong server and is in the MB or low GB size and not TeraBytes:
1. Connect to SQL as the source from Power BI
2. Take the easy way, click all fo the tables that you want.
3. Click Edit and make and changes that you want via M (either point and click or with programming)
4. Apply and Save
5. If you need - add additional calculated columns or measures via DAX.
6. Design away
If this is a dashboard or report that will be getting refreshed AND can write select statements with SQL
1. Open your favorite query writing tool. You can do it in PowerBI, but no Intellisense, no formatting, no easy debugging, etc.
2. Write the query and play around with it until you are happy.
3. In PowerBI, Use SQL as the source and paste the query that you copied from the other tool. This nice thing is, you can continually refresh and get new data without having to start from scratch.
4. If you need to make changes easier done in M than SQL, make them after importing the query.
5. Continue as above.
Just my thoughts. I feel sorry for the DBAs trying to maintain performance on their servers as we start hitting them with more and more UserBI apps that are pulling everything on earth just to throw it away after.
This is exacly what we have been doing with one additional step which may not be applicable for every one.
When we want to refresh/schedule the data and if we are using the first approach to import all the tables, the performance of the SQL server will be impacted.
1. I have written a query to pull all the filtered data from multiple tables with huge number of rows.
2. Schedule a job to retreive all the information and enter into a different table in a different database.
3. Schedule a refresh with in PowerBI to get the updated data.
Reasons to do this: The only SQL server we have is being used by multiple SQL developers to run reports every day. When we try to extract the data with either of these approaches, it impacts the performance of the SQL server. By doing this, the data will be retreived overnight and copied into a different database. We use this database to connect through PowerBI and providing datasets to the Finance team. It has been working so far. I am not saying this solution will work for everyone, but in order to avoid the perf, issues, we thought this might be the best way.
I would be happy to hear if anyone has any other ideas to make it more efficient. Thanks for reading!