Were SSRS Report Models (SMDL) a better semantical layer for operational reports than Tabular Models (PBIX Power BI Dataset) ?

Question

Is a Tabular Model (in Analysis Services or Power BI/PBI Dataset) really a replacement for the (deprecated) SSRS Report Models?

Tabular Models are made for BI/dashboards/PBIX (Power BI Desktop) reports to do slice-and-dice analytics while Report Models were just a semantic layer on top of your SQL-database, which could be a normal relational database in 3NF (e.g. the AdventureWorks DB, so NOT the AdventureWorksDW DB, a star-scheme data warehouse DB ), and so a report model could be a good source for a paginated RDL-report made with Report Builder (or Power BI Report Builder).

On google I found this interesting post from Microsoft (BI) MVP Paul Turley:

https://sqlserverbi.blog/2022/02/04/when-to-use-paginated-reports-vs-interactive-power-bi-reports/

that says that RDL-reports work best with an SQL DB, so not with a Tabular Model.

But if you want to make a self-service reporting (SAAS-)solution with Power BI Report Builder as a front-end and Azure SQL DB as a back-end, then not having a semantic layer in between (as we had with Report Models) might limit the 'self-serviceness' of the solution. Power BI Report Builder has a (visual) Query Designer for no-code/drag-and-drop building of the dataset of a report and that can work OK if the source DB is built well (e.g. PK/FK relations are defined), but maybe there are some things that can't be done in this Query Designer and you need to switch to SQL, while with Report Model you could do these things (so in a no-code way).

And the reason that Tabular Model as the semantic layer for 'list-reports' is probably not the best, is that these reports can have many fields, which you would not use in a tabular model (e.g. employee's telephone number, email-address), and more-over, many rows (multi-page reports, e.g. a list of all employees contact-data with an index/document-map for the departments).

So in summary, my question is: do you think it is possible to build self-service reporting (SAAS-)solution with Power BI Report Builder as a front-end and Azure SQL DB as a back-end, so without a semantic layer but directly on a SQL Reporting-DB (so not the real source-DB, but a more user-friendly, read-optimized DB with some denormalization) ?

Answer 1

Hi Maarten van Reek,

Thank you for posting query in Microsoft Q&A Platform.

Yes, it is possible to build a self-service reporting solution with Power BI Report Builder as a front-end and Azure SQL DB as a back-end without a semantic layer. However, as you mentioned, having a semantic layer can help to simplify the reporting process and make it more user-friendly.

In the absence of a semantic layer, you can use the visual Query Designer in Power BI Report Builder to build the dataset for your report. This can work well if the source database is well-designed and has proper primary key/foreign key relationships. However, there may be some limitations to what you can do in the Query Designer, and you may need to switch to SQL for more complex queries.

To make the reporting process more user-friendly, you can create a read-optimized database with some denormalization. This can help to simplify the reporting process and improve performance. However, you should be careful not to denormalize too much, as this can lead to data inconsistencies and other issues.

In summary, while a semantic layer can be helpful for self-service reporting, it is possible to build a solution without one. You can use the visual Query Designer in Power BI Report Builder to build the dataset for your report, and create a read-optimized database with some denormalization to simplify the reporting process.

Hope this helps. Thank you.