Finding the best Data Source in Power BI – Part IV
If you haven’t already, make sure to start with Part I – III where I explore OneDrive, Live Connect, and Dataverse and set the baseline on how to measure “best” connector type in Power BI.
Part I- https://bilingualanalytics.com.au/data-connectors-onedrive/
Part II- https://bilingualanalytics.com.au/data-connectors-live-connect-power-bi-dataset
Part III – https://bilingualanalytics.com.au/data-connectors-dataverse/
SQL Database
Without getting too technical here, SQL, or Structured Query Language, is a data management language used to handle data in relational databases. While it is a language, in many cases tech people use the phrase “SQL Database” to refer to a database that can be queried with that language.
It is essentially the “backend” of the enterprise, where all the valuable data sits.
There are multiple tools that users can use, namely MySql, SQLite or even Microsoft SQL Server Management Studio (SSMS).
So far I have only been exposed to SSMS, so I used that in my test.
Why use SQL Server?
I know, I know… The question should be why not, right?
For many Power BI report creators, SQL database is THE source of data. However, it is important to realize that most of the “business users” cannot use SQL.
Additionally, in many companies gaining access to SQL database is limited to IT department and maybe some Analysts.
And I am one of those. In my current day job, I do not have access to our SQL Server, hence why I used Excel imports. I can export data from SQL using a GUI, but that’s not the same when it comes to Power BI report creation.
The simple answer on why to use it is SPEED.
Not just speed, BLAZING FAST speed!
Report refresh
While my Excel-based report took 40 seconds to refresh, the same report using SQL Server only took 25 seconds.
It is almost a 40% improvement! At first, it may not feel like a lot, however, imagine if you need to refresh reports frequently.
The number of reports with the refresh frequency could easily save you a couple of hours per month…
Data availability
From one perspective, it is actually better to connect straight to the data source. It can eliminate any confusion on who is using which version, whether that version of the report is updated with the latest data or not.
On the other hand, as I flagged above, most of the business users are going to lose the capability of troubleshooting. Why? How many business users do you know who can use SQL?
I thought so…
Moreover, does your company give SQL access to all employees?
I highly doubt that…
Things to keep in mind
There are a couple of best practices to follow when it comes to using SQL Server as a Power BI data source.
People much smarter than me wrote them, so allow me to link them here.
Roche’s Maxim: https://ssbipolar.com/2021/05/31/roches-maxim/
What is Query Folding: https://data-mozart.com/what-is-a-query-folding-in-power-bi-and-why-should-i-care/
With all of this knowledge, the only thing left is to watch the video below and decide for yourself if migrating to SQL database is something that you want to explore.
Roland
🙏Thanks for watching and if you liked this video please hit the 👍 button.
📢 Also be sure to subscribe to the channel so you never miss a new video.
0 Comments