Long loading of tables and visualisations because of high cardinality (?)

[Anonymous]

Hello community!

I have a problem with long loading of my tables and visualisations. Sometimes they don't even load.

Here is my data model

 

So:

1. the CALENDAR table filter RFP table,

2. RFP table filter RFP Uncovered table by RFP[id] - RFP Uncovered[id_rdp]

3. RFP Uncovered filter two tables I have a problem with vbi_pob_empty_chats and vbi_pob_chat_nbrs by concatenation of id_rfp and id_customer (this create a unique ID). Concatenation is done in SQL query not in Power Query or calculated column. 

Relation is one to one. These two tables are quite huge by rows so the cardinality is high. 

After publishing the report I'm waiting couple of minutes to load the simple table or line chart. If I extend date range I receive error that resources are exceeded. The table include just couple of simple measures like:

# EMPTY CHATS =

VAR _fil =

FILTER(

    vbi_pob_empty_chats,

    vbi_pob_empty_chats[chat_msg_text] = 0

)

VAR _sum =

SUMMARIZE(

    _fil,

    vbi_pob_empty_chats[id_chat])

RETURN

    COUNTROWS(_sum)

# ALL CHATS =

COUNTROWS(

    SUMMARIZE(

    vbi_pob_empty_chats,

    vbi_pob_empty_chats[id_chat])

    )

or 

% BUYER ANSWER RATE =

VAR _cfn =

SUMX(

    FILTER(

        'vbi_pob_chat_nbrs',

        'vbi_pob_chat_nbrs'[cust_response_nbr] = 1),

        'vbi_pob_chat_nbrs'[cust_response_nbr]

)

VAR _rfpr =

SUMX(

    FILTER(

        'vbi_pob_chat_nbrs',

        'vbi_pob_chat_nbrs'[rfp_first_nbr] = 1),

        'vbi_pob_chat_nbrs'[rfp_first_nbr]

)

RETURN

    DIVIDE(_cfn, _rfpr)

and couple like just only DIVIDE or SUM. Perfomance analyzer doesn't show that the measures are causing the problem (less then 1000ms, avg 500-700). I wonder how to improve data model to get loading fast. 

[Anonymous]

Hi,

Ad. 1,2 - yes, I know, but it's hard to avoid that due how the data are constructed.

ad.3 I had all functions with calculate and I switched to the filter  and now is faster (hints from Greg Decker on youtube). Also I switched from distinctcount to countrows(summarize) and perfomance analyzer shows that calculations are faster (also a trick from some blog). The problem is in data model since all tables are like 50mln of rows, so this bidrectional modelling is really bad.

[m3tr01d]

Hello,

When I see your problem, I see different things
1) Your model is not Star Schema and therefore, is probably not optimal
2) You use some bidirectional filters and they probably are not needed. You should remove them if possible
3) Pretty much all your DAX measures are using the FILTER function where you should not use it in all your examples.

For now, I would start by getting rid of all the FILTER and SUMMARIZE Functions because they are very expensive.

You should probably take a look at Alberto's Ferrari playlist on Youtube about Calculate function
https://www.youtube.com/watch?v=Tk-7gBt9CDE&ab_channel=SQLBI