SSRS & PowerBI. Why and When?

I’m not a BI expert. However, I deal with BI requirements very often in my daily job.

As an architect, even that you won’t be the builder of your own architecture, you need to know your options (aka building blocks), pros & cons of each and key usage scenarios.

You either get that understanding bottom-up when it’s your area of expertise (Development, Integration, Finance, Production etc…) or you have to keep it simple and focus on tactical knowledge gain.

This my attempt here of keeping it simple, as I started this post by being an expert in SSRS due to my development background, but without any practical knowledge in PowerBI. Since we have a strong DA team in our organization whose responsible for BI projects, I only knew the marketing stuff.

Business scenario

This is a fictional scenario, coming from previous requirements while working in the Service industry. The scenario and provided data examples are all based on Microsoft’s Contoso demo data.

As a corporate finance analyst, I want to view projects profitability across companies so that profit & cost analysis is possible by company, customer & project.

Requirements journey

I have delivered SSRS reports in the past for such purposes without much visualization or analytical capabilities. The report provided all numerical data, and then the business team used it in Excel to visualize, slice and dice.

You grow in your career and you hear about those fancy BI tools (SSAS, SharePoint etc…). However, you get stuck as a non-BI expert approaching that area since it’s not that easy to learn, and the visualization part is a bit clunky to provide you with an effective dashboard that just works!

PowerBI

Then I heard about PowerBI. First time I was a little bit confused, mixing it with the Excel PowerStuff features (PowerQuery, PowerPivot etc…) and I was thinking, Come on, not another end-user fancy tool that might not deliver up to the enterprise expectations.

Finally, I met PowerBI Desktop for the first time last month, and did my first dashboard today! My purpose to learn it was twofold A) Add another building block to my architectural knowledge and B) See what is all this excitement about. Is it really something different?

Learning a BI tool in few hours to the extent of creating your first fully interactive dashboard is something different.

The rich out-of-the-box visualization with massive ease of use designing experience is another thing.

And finally, the out-of-the-box interaction between dashboard components is MAGIC!

To prove it, here is my dashboard that took me literally 2 iterations and few hours. Of course it’s not the best but it just works. Click on below images to see visualization, interactive filtering and drill-down capabilities.

PowerBI – Projects profitability all companies

PowerBI – Projects profitability filter by date and drill down

PowerBI – Projects profitability focus on customer

PowerBI – Projects profitability drill down to Project

PowerBI – Projects profitability drill down to individual records

What about SSRS?

While PowerBI is a self-service tool for not-necessarily-technical person, SSRS is a Developer tool, and it’s a tool to stay.

SSRS is used for paginated reports, which allow for exact placement of elements on a page and are capable of complex display logic for creating printed reports or online operational reports. (Reference)

Also, SSRS runs completely On-prem, compared to PowerBI being a cloud service. With few customers still preferring to keep all data local, along with Dynamics 365 Operations On-prem deployment option, A combination between SSRS & Excel might be the only option.

Summary

Microsoft Dynamics 365 Operations has great reporting and data analytics capabilities. This post is to highlight the existence of two different tools and to provide high-level guidance on which tool to use based on the business requirements. To conclude, SSRS is for paginated reports with printing and complex layout requirements, While PowerBI is for interactive reports and dashboard (Reference).