Microsoft BI Architecture: Power BI, Paginated Reports, Analysis Services and Cloud - what is the optimal solution?

Question

I am looking for some opinions and suggestions re Microsoft BI Architecture. Need to design a BI architecture that supports:

1. Self-Service BI - thinking of Power BI
2. Enterprise Level modelling with proper source control, data security and multi-lingual support - thinking of Analysis Services
3. Standard Reporting (paginated reports)- thinking of SSRS or Power BI Report Builder
4. Hybrid Infrastructure - both on premise and cloud data sources, including MS SQL, Azure and SAP BW and SAP HANA

So I have identified the products I need. The problem is - they are part of different services. For now it looks that we would need: Option 1) Power BI Premium to support Paginated Reports and Self-service BI and Separate Analysis Services for modelling (Power BI Premium lacks key features like multi-language support, column/object level security, enterprise level AML). Option 2) Power BI Pro for Viz and self-service BI, SSRS for Paginated Reports and Analysis Services for Modelling. However here I am not sure if SSRS is available on Azure. And how easy it would be to manage three different products, especially if data is residing on premise and in the cloud.

Anyone had to do similar exercise? What have you've chosen? For now, I would love to avoid having to purchase Power BI Premium, but I do need to support paginated reports and maximise the use of Power BI PRO licences (hundreds of users).

Thank You!

Answer 1

At the moment, the best way to combine SSRS and PBI in one portal is Power BI Premium, however you can do this in the cheaper Power BI Embedded Service. The main draw back will be for Embedded A SKU's, building your own portal to surface them. If you don't need external users, then you can use the EM version and show PBI and SSRS in Sharepoint/Teams etc. You'll also be able to reduce the cost of purchasing 100s of Pro licenses as like Premium, your free users are able to see the reports.

SSRS in not available as an Azure service, however you can spin up a VM with SQL Server (Standard & Enterprise) and host the SSRS site there. You can also Pin SSRS reports to the Power BI Service, but pinning doesn't give the best user experience.

Analysis Services (AS) will always be a better choice of the data modelling, as it overcomes the limitations of the Power BI service, as you have noted. Surfacing the data in AS, allows user to connect with Excel, PBI, SSRS and other reporting tools, so option 2 will be your best option.

Managing the different services will but time consuming, but unavailable, you can use Active Directory to limit access etc, so only the relevant users can connect, build and consume reports, other processes may have to be defined on your own requirements.

From the projects I've encountered, it tends to be option 2, customers still have some on-prem SSRS and use PBI as a complementary reporting platform. However most are moving away from SSRS to PBI and Excel. Excel is used for table based reporting and dumps of data. A number of projects have SQL Server Enterprise with Software assurance, that allows them to deploy Power BI Report Server on-prem rather than use the PBI Service.