This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Reporting Services (SSRS) versus Power BI An overview of Power BI and SSRS Loading data (SSRS and Power BI compared) (this blog) Creating reports for viewing on-screen Exporting and printing reports Total Cost of Ownership (TCO) Conclusions

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.

Reporting Services (SSRS) versus Power BI
An overview of Power BI and SSRS
Loading data (SSRS and Power BI compared) (this blog)
Creating reports for viewing on-screen
Exporting and printing reports
Total Cost of Ownership (TCO)
Conclusions

Posted by Andy Brown on 18 October 2019

In this blog

How Power BI data sources work
How SSRS data loading works

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!

Parts of this blog
Reporting Services (SSRS) versus Power BI An overview of Power BI and SSRS Loading data (SSRS and Power BI compared) (this blog) Creating reports for viewing on-screen Exporting and printing reports Total Cost of Ownership (TCO) Conclusions