SSAS + Query folding + change type to datetime

[ivostammis]

Hi all,

I am trying to use incremental refresh on data that I get from a SSAS cube. At this moment I am stuck because I cannot get query folding to work. 

I have added the data from the SSAS cube (Source, navigation and added items steps). Now I need a column containing a datetime in order to set up the incremental refresh. I do have a column with a date, but the format by default is text. I use Change type to change this column to Datetime (I use Change type, because Transform can break the native query).

This is where I run into a problem: up untill the Added items step I can right click and select View native query, so query folding will work (which is required for incremental refresh). When I right click on the Changed type step the View native query is greyed out, so there will be no query folding possible. 

Does anyone know how to solve this with a SSAS cube? I have found the video's / articles which explain how to use value.nativequery() for SQL server querying, but I need to use a cube, not a regular SQL query. Or should I talk to the database administrator to change the data type of my date column in the cube itself so I don't have to change it myself?

Thanks for any advice,

Ivo

[AlexisOlson · Accepted solution]

The best solution probably is changing the date column in the cube itself to the appropriate data type.

I doubt it's possible to change the type without breaking query folding (though I'd be happy to be proven wrong).

[raphael]

Hi @ivostammis 

Looks like I'm trying to solve exactly the same problem.

I managed to get query folding to work but still struggling to get IR to work with SSAS.

What I did was use the hint in the doco about converting integers to work with the DateTime parameters and extended this concept  to work with SSAS returning dates in text type.

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn

I noticed that the native query for SSAS would look like DateTime :

[Calendar].[Fiscal Month].&[2021-07-01T00:00:00]

despite the Power Query preview showing text:

Here is my M code for this function:

let
    Source = (x as datetime) => 
        let
            Day = if Text.Length(Number.ToText(Date.Day(x))) <2 then Text.Combine({"0",Number.ToText(Date.Day(x))}) else Number.ToText(Date.Day(x)),
            Month = if Text.Length(Number.ToText(Date.Month(x))) <2 then Text.Combine({"0",Number.ToText(Date.Month(x))}) else Number.ToText(Date.Month(x)),
            Year = Number.ToText(Date.Year(x))
        in
             Text.Combine({Year,"-",Month,"-",Day,"T00:00:00"})

in
    Source

and how i reference the function in the query:

let
    Source = AnalysisServices.Database("MySSAS", "My Data", [TypedMeasureColumns=true, Implementation="2.0"]),
    Model1 = Source{[Id="Model"]}[Data],
    Model2 = Model1{[Id="Model"]}[Data],
    #"Added Items" = Cube.Transform(Model2,{{Cube.AddAndExpandDimensionColumn, ....
    ........
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Calendar.Fiscal Month]) > "[Calendar].[Fiscal Month].&["&DateText(RangeStart)&"]" and Cube.AttributeMemberId([Calendar.Fiscal Month]) <= "[Calendar].[Fiscal Month].&["&DateText(RangeEnd)&"]"))
in
    #"Filtered Rows"

This all works in Power Query. The data preview is corectly filtered by the dates i set up in the RangeStart and RangeEnd parameters.

I tried a simple test that should just return one row per month and then set IR to retain 12 months of data (so a 12 row dataset) but it times out once i publish and refresh from the service. 

I've troubleshooted this to the nth degree already adn read all the docs and guy in a cube vids but still can't get it to work 😞

I suspect that QF is not actually taking place despite being able to "View Native Query" on my last step.

...stumped for now

Hopefully the above helps someone to figure this out and report back here 🙂

[raphael]

Replying to my own reply...

The above ended up working very well. My issue was trying to refresh during a busy period and getting Gateway time out errors.

Have now tried this on a much larger data set (5M rows) and it works!

If you are going to attempt this,make sure you follow all the best practise guidance and use use Tabular editor to apply the refresh policy, then SSMS to load the partitions.

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

Also this youtube video from GIAC is full of great tips:

Avoid the full refresh with Incremental Refresh in Power BI (Premium) - YouTube

[ivostammis]

Hi @raphael ,

Thanks for your explanation and taking the time to provide further info here! I am going to try this out this week, would be great if it works!

[raphael]

Let me know how you go. 
I have been trying to get this to work for the past 3 months!

My dataset has been refreshing in under 2 minutes, 4 times a day for a few days now.

[v-eqin-msft · Accepted solution]

Hi @ivostammis ,

According to my test,  not all changes in data types will break query folding.

For example:

• Datetime -> Text  and Whole Number to Text , both were possible and would not break query-folding:

• But Text type -> DateTime or Date would break query-folding.

For more information, please refer to:

Changing data types that do not break query-folding in Power Query / Power BI

So as you and @AlexisOlson mentioned, you need to contact the admin of the SSAS to change the type.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

The best solution probably is changing the date column in the cube itself to the appropriate data type.

I doubt it's possible to change the type without breaking query folding (though I'd be happy to be proven wrong).

[Anonymous]

Did you try DateTime.FromText?

--Nate

[ivostammis]

I added a custom column using DateTime.FromText([column with text]), this succesfully adds a column with date and time (datatype is any), is that what you mean? Unfortunately for this step View Native query is also greyed out.