I am trying to migrate SSRS report to Power BI report. current SSRS report is sourcing data from SQL Table Ex. Table_A, Table_A is populated by joining SQL view (View_B) and SQL table (Table_C). Now View_B is made up of UNION ALL of 10 SQL queries, each individual sql query is made up of joining 6 tables. 4 tables out of 6 tables are repeating in all 10 sql statements except 2 tables which are differing. these 10 individual sql statements have case statements with different logic in select query to calculate certain columns which are used as filters in final report. There are also where conditions mentioned in some sql statements.

so my question is, is it recommended to create a power bi data model by converting 6 tables used in joins of View_B then join with Table_C and then create calculated columns and measures on top of it?

or simply using View_B as it is as a data source in power bi and then join Table_C would be correct?

I am confused if I can convert sql view into power bi data model successfully because sql view has complex sql statements with case statements, multiple joins and union all statements.

I am looking for solution which way is better, converting joins of sql view into power bi data model or use sql view directly as a datasource?

2 Answers

The choice of approach depends on different factors, like how complex the SQL view is, the need for optimization and flexibility, and the expertise available for data modeling in Power Query Editor. If the SQL view is stable and performs well, using it directly as a data source can be a simpler option. On the other hand, if you want more control and optimization, it's advisable to recreate the logic in the Power BI data model, even though it may require more effort.

1

This actually depends on why you are trying to "migrate SSRS report to Power BI report". You can continue to use the paginated report in Power BI. So what is the intent of the migration?

In general you should build a proper star schema for your Power BI data model. So if that's what you've already got in the database, then this might be easy.

But if you just want to recreate one legacy report, and not build a reusable Power BI data model, designing a new Power BI Data model might be overkill, and you should just import (not Direct Query) the existing view. Using complex views with Direct Query is a recipe for disaster.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.