Report running extremely slow with ony two relatively small tables

[jonclay]

Hi everyone

I've created a seemingly very simple Power BI report using two tables. I'm using Direct Query mode and I'm pulling the data from a SQL Server (Dynamics CRM database).

One of the tables (contacts) contains around 300,000 records but these are filtered down in Transform Data to around 5,500 records using the following Transformations:

The other table (si_communicationpreferences) contains around 3,000,000 records and the table is linked to the contact table via an N:N Relationship as shown below:

The si_communicationpreferences table contains all the mailing preference types for all contacts. Some contacts have 0 types, some have 1, and others can have as many as 9.

In my report the relevant contacts should appear in a table visual when I choose a specific mailing preference via a slicer visual.

For some reason, which I can't work out, the report either takes forever to run, or eventually fails with the following error (this happens most times after about 5 minutes):

Can anyone help me as to why the report is running so slowly please? Could it be something to do with the fact that the si_communicationpreferences table contains so many records, many of which are not relevant for the contacts? If so, can I filter this table down so the si_communicationpreferences table only shows those contacts that have been filtered using the aforementioned transformations in the contact table?

I've also tried changing the report to Import Mode but I get the following error when trying to do so:

Many thanks for your help.
Jon

[jonclay]

Thank you @lbendlin . I decided to go for Import Mode in the end and that seems to work a lot faster.

[lbendlin · Accepted solution]

lots of records on both tables. M:M relationship likely resulting in a cartesian product.  Direct query with no indication if query folding occurs.

I would call this a recipe for trouble.  

- check if the queries fold

- check if the SQL server can handle that load

- change the relationship by introducing a bridge/dimension table

- consider using import mode