Building semantic layer in PBI

[rnola16]

Currently we have a Business Objects unv ( ~900 tables) and based on that 200+ templates are created which mostly are user input parameters. We are now transitioning to PBI, which to me is a big shift, my questions:

1. Should you build a semantic model in PBI and then create these templates or should you approach each report individually ?

2. Can I use the SQL ( generated in Bus Obj Query) which are complex with multiple parameters and use it in direct query and generate a report with user input values ?

3. Can PBI handle dynamic user defined parameters in SQL ? The bulk of the load should be handled by the datasource and not PBI.

Thanks.

Appreciate your feedback.

[rajendraongole1 · Accepted solution]

Hi @rnola16  - You're right—moving from Business Objects (BOBJ) UNV to Power BI is a big shift, especially with 900 tables and 200+ templates.

Identify Core Tables & Relationships:

approach:

Start with a star schema approach rather than trying to replicate the entire UNV structure.
Flatten complex joins where possible.
Parameter Handling in Power BI

Business Objects heavily relies on parameters—you’ll need to design them differently in Power BI (e.g., slicers, DAX variables, or stored procedures).
Phase-Based Migration

Identify high-priority reports and migrate them first.
Validate KPIs, measures, and calculations.

2. Can You Use SQL (Generated in Business Objects Query) in DirectQuery and Pass User Inputs?

approach:

Convert BOBJ SQL into Database Views

Instead of embedding SQL into Power BI, create optimized views in the data source.
Power BI can then DirectQuery these views with user filters.
Stored Procedures for Complex Queries

If you need heavy filtering or dynamic calculations, consider using a stored procedure.
You can call stored procedures in Power BI using parameters (though it requires Power Query M scripting).

3. Can Power BI Handle Dynamic User-Defined Parameters in SQL?

approach:

Use Power BI Slicers for Filtering Instead of SQL Parameters

DirectQuery can filter on the fly without modifying SQL.
Keep filtering at the visual/report level rather than in SQL.
Dynamic M Query Parameters (If Supported by Your Data Source)

If your database supports M Query Parameters, you can use them for dynamic filtering in DirectQuery.
This is not as flexible as BOBJ, but it works for many cases.
Leverage Views Instead of Dynamic SQL

Create a view per major report use case with the necessary joins and aggregations.

Hope these details helps.