Matrix Expand Times

[Anonymous]

Hey

I have a matrix visual with 8 measures. It takes about 4/5 seconds to load the visual initially which is just about fine, but everytime the user expands the matrix to view the next level down, it takes another few seconds. I understand why as it's calculating the next level, but wanted to understand if there are any tricks to improve the drill down action in a matrix. 

It's a P&L and the users generally will not want to spend the time waiting to drill down into each level and are not satisfied with the current performance. Any ideas?

[vanessafvg]

in order to see why your visual are slow, one would need to see your data model and measures.

how long does it currently take to drill down?

[Anonymous]

It takes about 3 seconds to drill down each time. I was hoping to understand if there were any tricks to perhaps for example, preload all matrices data on first load, so that navigating in and out would be fast.

I've stripped my model and measures to be as simplistic as possible to try and find the issue, but still finding the same speed issues.

My fact table (Account Balances) is about 1m rows. The has a many to one join table to my dimension table (Chart of Accounts - 600 rows), which in turn has a one to many relationship to another dimension table (account structures - 27k rows).

The chart of account table is a simple dimension table providing classifications to each account, whilst the structure table allows accounts to sit in multiple structures, for example Revenue, Profit etc.

My measures are now just variations of this: calculate(sum(value),classification="Actual") - so nothing complex, yet im still facing speed issues

[vanessafvg]

have you run performance analyzer?  maybe you can use dax studio to analyze your queries to see if will help at all if you can se where the performance is an issue.  Its hard for me to answer without seeing your model  SQLBI do a whole series in  youtube.    I dont  think you can preload with the drill down as its going to be on click, maybe having the detail explicity in a table visual might have better performance over the matrix rather than drilling down if performance is a bigger factor.

The other thing you can do maybe is create a visual tool tip that when you hover the line you interested in rather return the data in a visual via a tool tip