Build reports on HANA database

[rpinxt]

So does anybody have experience with this?

I have a connection to several universes in a HANA database and can import data from there.

All our data is there so this is good, however the downside is that it is huge.

Not really a way to control what you import after you import it and do filtering in Query editor.

Makes it really slow.

Now I found that Direct Query work much better and faster.

I has all the fields but with good filtering in the filterpane of Power Bi desktop it only brings in the data that I need.

Much faster then first importing all and then filtering.

Big downside here is that you apparently can only direct query 1 universe.

Every thing is greyed out when I direct query.

Cannot enter data manually, cannot get new data and direct query a 2nd universe.

Am I doing something wrong? Is it blocked on a higher level by my company IT department or something?

Or is it just not possible?

If so is there something that makes it possible to get data from several sources that you direct query and combine them.

Maybe a dataflow?

[rpinxt]

Thanks @Anonymous for this clear answer.

Like I thought it was expected behaviour then.

And there are not really workarounds except a composite model.

So maybe I need to look into that.

Actually my main reason to look into this is that I wanted to add an autocalendar to my direct query source!

Would not think that would be this complicated.😂

[Anonymous · Accepted solution]

Hi, @rpinxt 

DirectQuery mode in Power BI allows you to connect directly to your data source without importing the data into the Power BI model. While it offers benefits like real-time data access and reduced memory usage, it does come with certain limitations and considerations.
Here are some points to address your situation:
1. DirectQuery Limitations:
- Single Universe: As you've observed, DirectQuery mode typically allows querying only one data source (or "universe") at a time. When you enable DirectQuery, other options like manual data entry or adding new data become unavailable.
- Greyed-Out Options: The greyed-out options you're seeing are expected behavior in DirectQuery mode. It restricts certain operations to maintain the connection to the data source.
- Performance Considerations: While DirectQuery can be faster for specific scenarios, it also imposes a different workload on both the Power BI environment and the underlying data source. Optimizations may be needed at both ends to achieve good performance results.
2. Possible Approaches:
- Dataflow: You mentioned dataflows, and they can indeed help. Dataflows allow you to transform and combine data from multiple sources before loading it into Power BI. You can create a dataflow that combines data from different universes and then use that dataflow as a single source in your Power BI report.
- Composite Models: Although not directly related to DirectQuery, composite models allow you to combine data from both DirectQuery and imported sources within a single report. However, this approach has its own considerations and limitations.

The choice between DirectQuery and other modes depends on your specific use case, data volume, and performance requirements. It's essential to strike a balance between real-time access and efficient querying. Here are some links that may be helpful to you:

Use composite models: Use composite models in Power BI Desktop - Power BI | Microsoft Learn

DataFlow: Creating a dataflow - Power BI | Microsoft Learn
DirectQuery model guidance: DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

DirectQuery in Power BI: DirectQuery in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Jianpeng Li

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