Direct Query to transactional database (Why its not a good idea)

[DebbieE]

Im trying to help someone at the moment set up proper power BI reporting. they are currently connecting straight into their transactional database via Direct Query.

Ive recommended adding a reporting database between the transactional one because you shouldnt connect to the OLTP for lots of reasons. they remain unconvinced with what Im saying.

Can anyone think of a really good explanation on why you wouldnt connect to OLTP and drag in hundreds of tables to work with in Power BI?

Also why Direct Query isnt real time analytics, even though the user can click refresh when ever they want? 

[v-alq-msft · Accepted solution]

Hi, @DebbieE 

When you connect to OLTP and drag in hundreds of tables to work with in Power BI with DirectQuery, the overall performance depends much on the performance of the underlying data source. When users open a shared report, and each dashboard tile that refreshes, sends at least one query per visual to the underlying source. This fact requires that the source can handle such a query load, while still maintaining reasonable performance.

When connecting to an OLAP source like SAP Business Warehouse, no transformations can be defined at all, and the entire external model is taken from the source. For relational sources, like SQL Server, it's still possible to define a set of transformations per query, but those transformations are limited for performance reasons.

Any such transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they're limited to those transformations that can reasonably be translated into a single native query. If you use a transformation that is too complex, you receive an error that either it must be deleted or the model switched to import.

For further information, you may refer to the following link. It may help.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @DebbieE 

When you connect to OLTP and drag in hundreds of tables to work with in Power BI with DirectQuery, the overall performance depends much on the performance of the underlying data source. When users open a shared report, and each dashboard tile that refreshes, sends at least one query per visual to the underlying source. This fact requires that the source can handle such a query load, while still maintaining reasonable performance.

When connecting to an OLAP source like SAP Business Warehouse, no transformations can be defined at all, and the entire external model is taken from the source. For relational sources, like SQL Server, it's still possible to define a set of transformations per query, but those transformations are limited for performance reasons.

Any such transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they're limited to those transformations that can reasonably be translated into a single native query. If you use a transformation that is too complex, you receive an error that either it must be deleted or the model switched to import.

For further information, you may refer to the following link. It may help.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Connecting direct query into the transactional database is like having the customers go directly into the kitchen to collect their food.

Not only is it slower for the customer, because of the layout, but it slows down the kitchen staff who have to now fight with the customers for access to the physical space.

A reporting database is like a collection area.  The orders can be placed in a way that is most convenient for customers to collect their meal.  Also if a queue forms for collecting the food, the chefs are uneffected.

[DebbieE]

This is a perfect way of describing things. Hopefully this will make them realise that they need to sort it out now to avoid massive headaches in the future

[mwegener]

Hi @DebbieE ,

I had to think of that sentence ...

"Letting others fail is a Principal-level behavior."

https://ssbipolar.com/2020/01/14/tough-love-in-the-data-culture/

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.