Optimizing Direct Query for Oracle

[joshua1990]

Hi all!

this is the first time I work with Direct Query.

I have a table that conatins 20MM rows. Here I use Direct Query as a connection based on Oracle connector.

As of now I just use a simple filter like (Within Power Query not as an SQL)

SELECT * FROM XXX WHERE Type = "X"

Then I have a calendar table and a second dimensional table. On the report / visual itself I have a filter for the current year and for a specific department.

That's it.

But somehow this takes so long to display the data in the service.

What I learned so far, oracle is not the best source for direct query, right?

Furthermore, columnstore is not possible.

Any recommendations for the optimization of this approach?

[edhans]

Oracle is horrible with Power BI. It is 20M rows. Import them. It should only take a few minutes. If it takes longer, you can stage them in a Dataflow, then import into Power BI. I know several companies that use Oracle as a data warehouse, but everything is moved to dataflows before the Power BI team starts consuming them.

[joshua1990]

@edhans : Thanks! Yes, this is our current approach. But since this is a pro environment we are limited to 8 refreshes per day. Therefore it won't work for our use case