Forum Discussion

aangers's avatar
aangers
Regular Visitor
2 years ago

Direct Query + Snowflake resulting in queries with cast as date for every unique date

When generating reports using DirectQuery with a Snowflake Connector, I am encountering badly transposed queries in Snowflake for queries where the data is filtered on a Date or Date/Time range. The queries will have a CAST as DATE for every unique Date, or a TO_TIMESTAMP for every unique Date/Time entry. Since our data covers many years, the queries get very long and complex for filtered date ranges. Yet, the date variable in my data is set to Date format in Power BI as well as in Snowflake.

 

Is there any way to optimize the query and make sure that the date variable is well integrated in the query?

 

 

 

Need Help

9 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    great question.  Please read about Value.NativeQuery  (and its folding options).  BUT - you will also want to change your query from   "[Date] IN {Range}  " to "[Date] >= MinDate && [Date]<= MaxDate"

     

    What you have hit upon is a frequent issue in composite models where the join column cardinality is too high. They use a similar set based query which can result in truly ginormous queries - I had a case where the query text (just the text!!!) was 6GB.  FOR. EVERY. SINGLE. QUERY. 

  • aangers's avatar
    aangers
    Regular Visitor
    2 years ago

    Thanks for the response.

    I've read about Value.NativeQuery and tried to apply this to the Power Query configuration of my Snowflake connexion. Here's the full code in the Advanced Editor. I have no other steps, all other data processing steps are made in DAX calculated measures.

     

    let

    Source = Snowflake.Databases("XXXX","WH_XXXX"),
    DB_XXXX = Source{[Name="DB_XXXX",Kind="Database"]}[Data],
    SCHEMA_XXXX = DB_XXXX{[Name="SCHEMA_XXXX",Kind="Schema"]}[Data],
    MY_TABLE = SCHEMA_XXXX{[Name="MYTABLE",Kind="Table"]}[Data],
    SqlStatement = "SELECT * FROM DB_XXXX.SCHEMA_XXXX.MYTABLE",
    NativeQuery = Value.NativeQuery(MY_TABLE, SqlStatement, null, [EnableFolding=true])

    in

    NativeQuery

     

    However, when I apply this, I get the following error 

    Expression.Error: Native queries aren't supported by this value.
    Details:

    [Table]

     

    Do you know what would cause this error?

     

    Secondly, I also checked all my DAX measures so that when dates are filtered, it is done with ">=" instead of using "IN {range}". None of my DAX measures had an "IN {range}" statement.

     

    Here is one of my DAX measures, maybe I'm not seeing something?

     

    my_measure =
    VAR metric = SELECTEDVALUE(Table[ID])

    VAR ThisDate = SWITCH(TRUE(),
        metric = "LASTWKPCT", MAX(Series1[EndDate]),
        metric = "LASTMTHPCT", MAX(Series2[StartDate]),
        metric = "WKYOYPCT", MAX(Series1[EndDate]),
        metric = "MTHYOYPCT", MAX(Series2[StartDate])
    )

    VAR my_list = DISTINCT(SELECTCOLUMNS(FILTER(Series1, Series1[EndDate] = ThisDate && NOT ISBLANK(Series1[Count])), "SiteID", [SiteID]))
    VAR my_list_m= DISTINCT(SELECTCOLUMNS(FILTER(Series2, Series2[StartDate] = ThisDate && NOT ISBLANK(Series2[Count])), "SiteID", [SiteID]))

    VAR Vol_ThisDate = SWITCH(TRUE(),
        metric = "LASTWKPCT", CALCULATE(SUM(Series1[Count]), Series1[Categ]=1),
        metric = "LASTMTHPCT", CALCULATE(SUM(Series2[Count]), Series2[Categ]=1),
        metric = "WKYOYPCT", CALCULATE(SUM(Series1[Count]), Series1[Categ]=1),
        metric = "MTHYOYPCT", CALCULATE(SUM(Series2[Count]), Series2[Categ]=1)
    )

    VAR Vol_PrevDate = SWITCH(TRUE(),
        metric = "LASTWKPCT", CALCULATE(SUM(Series1[Count2]), Series1[Categ]=1 && Series1[SiteID] IN my_list),
        metric = "LASTMTHPCT", CALCULATE(SUM(Series2[Count2]), Series2[Categ]=1 && Series2[SiteID] IN my_list_m),
        metric = "WKYOYPCT", CALCULATE(SUM(Series1[Count2]), Series1[Categ]=1 && Series1[SiteID] IN my_list),
        metric = "MTHYOYPCT", CALCULATE(SUM(Series2[Count2]), Series2[Categ]=1 && Series2[SiteID] IN my_list_m)
    )

    RETURN SWITCH(TRUE(),
        ThisDate < [DATE_PrevDate] || ThisDate > [DATE_ThisDate], BLANK(),
        Vol_ThisDate > 0 && Vol_PrevDate > 0, DIVIDE(Vol_ThisDate - Vol_PrevDate, Vol_PrevDate)
    )
    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      You need to run your native query against the database, not the table.

      • aangers's avatar
        aangers
        Regular Visitor
        2 years ago

        Sorry I misunderstood how to apply the Value.NativeQuery. Here is how I managed to bypass this error : 

         

        = Value.NativeQuery(Snowflake.Databases("XXXX","WH_XXXX"){[Name="DB_XXXX"]}[Data], "SELECT * FROM ""SCHEMA_XXXX"".""MYTABLE""", null, [EnableFolding=true])

         

        However, the problem persists, the queries sent to snowflake still have the "cast as date"... Do you know how to control the queries that are created and sent to Snowflake from DAX calculated measures?

  • sofia_pc's avatar
    sofia_pc
    Regular Visitor
    2 years ago

    I'm having the same issue. Did you find a solution?

  • enjoytheride's avatar
    enjoytheride
    Frequent Visitor
    1 year ago

    I'm running into the same problem, did you happen to find a solution?

    • aangers's avatar
      aangers
      Regular Visitor
      1 year ago

      Nope haven't found a solution unfortunately