First off, I apologise if this shouldn’t be the right thread, What are the advatages and/or PRO/CONS to use BI Tools like PowerBI / Tableau etc… VS using MS SQL for aggregation of data / analysis / data cleaning / queries…?
I found this. It may help some (SSRS = SQL Server Reporting Service):
Here are some basic differences between Power BI and SSRS, listed below:
- Power BI is free, SSRS requires SQL Server license. However, you require purchasing Power BI Premium license for PBIRS.
- Power BI is cloud-based, SSRS is server-based.
- Power BI is more modern and graphical. SSRS is conventional and involves manual effort and time to create reports and analysis.
- Can use data from the cloud in Power BI. SSRS can only use on-premise data.
- Power BI Report Server (PBIRS) is the successor of SSRS and has more features than SSRS. For one, it can use and render both interactive PBIX and analytical XLSX reports which SSRS cannot.
- PBI now has Cortana integrated for AI-based natural language, Q&A about your data and reports in Power BI. SSRS does not have this feature.
- In SSRS, you need to purchase a license, define requirements and scope, customize/develop your software according to the requirements, deploy, develop reports in a coding interface, schedule data refreshes, etc.
- Power BI has a graphical interface with the drag-and-drop capability to create reports. You can work with unstructured data, use modern rendering, publish, integrate and collaborate easily across platforms.
Note: typical MS marketing speak*
An ‘express’ version of SQL Server is a licensed version. What you got when you combined SSRS with SSEE depended on versions of both, but it is like comparing the free version of Power BI to the Premium version of Power BI.
* In the kindest possible way. MS writes about the features of new versions as if the press office never really used the previous version. Which, unless the press office is staffed by developers and system admins, is probably true.
Guy who has been doing BI since 1996 here.
Power BI, Tableau, etc. allow you to do 2 things: prepare datasets and provide visualization. You don’t have to prepare the data in the tool, it’s just a capability.
In most circumstances we provide an enterprise certified data set in a common format via a data warehouse. We prepare a Power BI model on top and provide this to end users to create their own visuals.
Power users may take the enterprise data and mash it up with additional feeds - budgets, industry data, etc - or they may create their own data models within the tool.
Where you do the data modelling really depends on who is using it downstream, data volume, frequency of change, and skill level of the user.