COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
A comparison of SQL Server Reporting Services and Power BI
Part three of a seven-part series of blogs

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.

  1. Reporting Services (SSRS) versus Power BI
  2. An overview of Power BI and SSRS
  3. Loading data (SSRS and Power BI compared) (this blog)
  4. Creating reports for viewing on-screen
  5. Exporting and printing reports
  6. Total Cost of Ownership (TCO)
  7. Conclusions

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:

Power BI 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:

Different sources

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):

Transforming a table

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:

SSRS 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:

Creating a dataset

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!

This blog has 0 threads Add post