Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
3.1.15
-
None
-
None
-
None
Description
Hello,
I have a PowerBI model composed of 2 tables connected to MariaDB:
- DIM_DATE - Import mode
- FACT_TRAN - DirectQuery mode
The 2 tables have a relation using Date_SK, which is of int datatype in both tables.
I've added a slicer with a date range, using [Date] column (datatype is date) from DIM_DATE table.
When I select a single date in the slicer, the query submitted to the fact table does a CAST() in the WHERE clause which prevents usage of the index in the fact table.
select count(1) as `C1`
from
(
select `TRAN_ID`
from `FACT_TRAN`
where cast(`TRAN_DATE_SK` as INTEGER) = 20220303
) as `IT
However, when I select a date range greater than 1 date the CAST() isn't applied anymore.
select count(1) as `C1`
from
(
select `TRAN_ID`
from `FACT_TRAN`
where `TRAN_DATE_SK` in (20220303, 20220304, 20220305, 20220306, 20220307, 20220308)
) as `ITBL`
You can easily reproduce this case.
I'm using latest version of Power BI Desktop and MariaDB Connector for PowerBI.
Please can you fix this bug so that when selecting a single date in the slicer there will be no cast in the WHERE clause anymore, which prevents index use,
Thanks.
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
@Lawrin, it's with single date when the SQL goes wrong (with a cast) and multiple days when it's Ok (without the cast).
I've tested the same case with SQL Server and the cast is not done, which highlights more that it's a connector issue.
Not that I am completely convinced, but we need to continue anyway.
Could you please provide `FACT_TRAN` table structure, and in particular I am interested in the exact type of the TRAN_DATE_SK field.
Also ODBC trace for creating of slicer and running it with both date ranges could help.
The ticket is not closed, that means that there is no solution so far. I asked also for ODBC trace. Or, maybe, alternatively some more detailed instructions how to repeat the issue or point me to some documents that can help - I am not very familiar with PowerBI.
Thank you for your report. Are you sure, that the problem is in the connector, and not in the Power BI? Since it works fine on single day, it feels like metadata provided by the connector is correct, but on multiple dates the query, generated by Power BI is wrong. The connector itself doesn't do that - we only provide information on tables, fields, types. And that information used by Power BI to create queries. It's still can be some wrong metadata from our side, but atm I can't think of anything, since, again, with single date it works fine.