BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Power BI improves with each monthly update. Will it reach a point where SSRS (SQL Server Reporting Services) becomes redundant? This detailed blog compares the two products, and considers the future of SSRS.
Posted by Andy Brown on 18 October 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Loading data (SSRS and Power BI compared)
This is a category which Power BI wins hands-down (indeed, it's probably the single biggest strength of Power BI as against SSRS).
How Power BI data sources work
In Power BI you can load data from a huge range of data sources:
Just a fraction of the data sources supported by Power BI (the most impressive and comprehensive list I've seen for any software I've ever used).
Not only that, but you can combine tables from different data sources - as shown by this example I've just created:
These 3 tables come from a SQL Server database, a CSV file and an Excel workbook, as the table names show.
If I hadn't have given the tables indicative names, there would be no way of knowing which data source each came from!
This works because Power BI loads the data into the report. You can refresh the tables at any point to bring in the latest copy of the data, or use something called DirectQuery to create a report which links directly to the underlying tables without an intervening data model.
You can also transform the data as you load it, using something built into Power BI called Query Editor (also built into Excel, although not enough people know this):
Here I've applied a series of modest transforms to an Excel workbook, including changing the data type of the first column.
So Power BI is a full-blown ETL (Extract, Transform and Load) package, competing with not only SSRS but also its sibling Integration Services.
How SSRS data loading works
There's much less to say about loading data in SSRS, because it can do so much less. Here I'm creating a data source:
The list of data sources supported is basically a list of databases.
Once you've created a data source, you can use it to create datasets:
You can either use the Query Designer to create an SQL query, paste one in or choose to base your dataset on a stored procedure.
And that's pretty much it!