report slow due to incompatible data type in Direct Query mode

[Yoga]

Hi,

I am using Direct qury mode for my report using oracle. I have only two views; Sales_fact and Division_dim and fetching current month sales

Both have division id as number datatype in DB.

Sales_Fact (8000 rows)

-----------

Division_id

Sales_amount

...

Division_Dim (6 rows)

--------------

Division_id

Division_name

After I join Division_id in model, I have Division name in slicer. When I select a Division_name, Table/Pie visual which has division_name and Sale_amount ,loads in more than 8 sec.

But if I use Sales_fact.Division_id in slicer, it loads in 1 sec. After two days of effort, found a soultion of type casting the Sales_fact.Division_id with "To_Number" in view query, though it is number, which makes the report load faster.

Sales_fact.Division_id and Division.Division_id both showing as Decimal in Power BI. I cannot change the data type in Power BI in Direct Query mode.

I dont understand this behaviour. Can some one help what is going on here?

Thanks,

Yoga

[v-caliao-msft]

@Yoga,

When connecting to a data source by using "DirectlyQuery", we cannot change the data type of your filed. This is the default setting. Since if we change the data type, the data was stored differently. 

Here are two useful links about Power BI performance tips for you reference.
http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Regards,

Charlie Liao

[Yoga]

Thanks Charlie for the useful links. 

As of now below are what I learnt,

1. Top N filter (N=10) is slow. Use Rankx Measure and use visual filter less than or equal to 10. Ex: Have Distributo rank by sale amount for Sales by Distributor visual. In the visual filter add the measure and filter for top 10 sales which is faster than TopN

2. Star schema (1 fact and 7 Dims) is slower than spread the dims based on tables/views data flow.  Ex: Sales Fact->Account->Distributor->Region. Have Region as factless fact and have other Dims like Zone, Division, Country. If you have all Star scehma, it seems slow down loading each visual when user selects an interative portion of visual

3. Integers are faster than Strings. Even having String column, in case all we have is itntergers, convert to To_number (in Oracle) 

4. Noted Assume Referential Integrity generates "inner join" instead of Left Outer Join", hope that also helps

5. Installed SQL Profiler and got what queries sent to DB. This helps to fine tune peroformance. Open the trace file C:\Users\Yoga\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace98628182\Data\FlightRecorderCurrent.trc

6. Having more visual keep joining in the queries which slows down.

Still working on and will post if I realize more