Power BI Desktop Connector SQL Translation

1

Hi,

We are getting an out of memory error when we try to do a distinct count on an int column. When we remove the “Cast” part and run the query from the dremio sql runner it runs successfully. Obviously it’s the “Cast” part that causes the error and we don’t see why our filter is translated with it in this case when we dont need it. Can anyone help please ? We are just trying to get a TOP 10 rows with a Distinctcount :smiley: It should’nt be a big deal.

I’ve added a screenshot from our PowerBI Visual and Filter.

Thanks !

memory_error
memory_error1231×802 76.3 KB

The error we are getting and the query that the connector is translating in PowerBI :
Release:
April 2023

Product Version:
2.116.966.0 (23.04) (x64)

Error Message:
Erreur OLE DB ou ODBC: [DataSource.Error] ODBC: ERROR [HY000] [Microsoft][Drill] (1040) Drill failed to execute the query: select “C1”,
cast(count(distinct(“siret”)) as DOUBLE) + cast(max(“C2”) as DOUBLE) as “C2”
from
(
select “OTBL”.“siret”,
“ITBL”.“siren” as “C1”,
case
when “OTBL”.“siret” is null
then 1
else 0
end as “C2”
from “Sirene”.“Etablissement” as “OTBL”
left outer join “Sirene”.“UniteLegale” as “ITBL” on (“OTBL”.“siren” = “ITBL”.“siren”)
) as “ITBL”
group by “C1”
OFFSET 0 ROWS FETCH FIRST 1000001 ROWS ONLY
[30039]Query execution error. Details:[
OUT_OF_MEMORY ERROR: Query was cancelled because it exceeded the memory limits set by the administrator.Feedback Type:
Frown (Error)

2

@Hjerbi Might be a known issue, can I have the job profile from the Dremio UI for the job run from Power BI that failed with the OUT_OF_MEMORY error?

3

Hi Balaji,

Thank you for your help ! Please find below the job profile you asked for.

7c075ba3-3f77-4fe9-83de-ec3ec770c8e7.zip (65,6 Ko)

4

@Hjerbi It looks like you only have 8 GB and there are 2 queries running at the same time, Can you please try to run the below SQL from the UI and see if it completes and send us the profile?

select "C1",
    cast(count(distinct("siret")) as DOUBLE) + cast(max("C2") as DOUBLE) as "C2"
from 
(
    select "OTBL"."siret",
        "ITBL"."siren" as "C1",
        case
            when "OTBL"."siret" is null
            then 1
            else 0
        end as "C2"
    from "Sirene"."Etablissement" as "OTBL"
    left outer join "Sirene"."UniteLegale" as "ITBL" on ("OTBL"."Siren" = "ITBL"."siren")
) as "ITBL"
group by "C1"
OFFSET 0 ROWS FETCH FIRST 1000001 ROWS ONLY
1 Like
5

Hello Balaji. We found that Heap Memory and Direct Memory were too low so we allocated more. This document was used to modify the configuration. Thank you for your help !

[Dremio]

6

@Hjerbi Glad the documentation helped to resolve the issue