cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jribs
Helper I
Helper I

SAP BW, Dates as Text, and Incremental Refresh

SAP BW (non-HANA) and Incrementally Refreshing has been giving me a lot of trouble for the past few months. On a positive note it has taught quite a lot.

 

Here is the issue:

  • In order to incrementally refresh, query folding must be enabled
  • When importing from SAP BW, ALL fields come across as text, so I can't apply incremental refresh here
  • As soon as I convert a date field in the format mm/dd/yyyy, query folding is no longer possible, making incremental refresh no longer work

Things I have tried:

  • Ensuring autodetect data types is enabled
  • Ensuring I am on SAP BW connector v2.0

Has anyone found a way to get incremental refresh working on SAP BW or get the dates to auto-format from either the SAP BW or Power BI side? 

 

Here are some screenshots of what I am talking about:

 

After selecting the query from infoprovider (notice yellow cube, making query folding possible)

jribs_1-1620048509041.png

After converting to Date/Time (yellow cube disappears)

jribs_2-1620048568235.png

 

Here is a similar issue posted on the forum without a resolution posted, but it was some time ago

 

Any help or suggestions would be greatly appreciated

8 REPLIES 8
mahesh_powerbi
Frequent Visitor

Quick update,

 

I tried writing a function to convert DateTime parameters (RangeStart and RangeEnd) in SAP BW format "DD.MM.YY". Using this I could manage query folding (cube icon visible). Configured incremental refresh. However, after deployed to Power BI service, the refresh takes forever and fails eventually! I've potentially figured out another way using Power BI Dataflow. I will post here again soon.


Here is the code:

//Code start
let
source = (x as date) =>
if Date.Day(x) < 10 and Date.Month(x) < 10
then "0" & Text.From(Date.Day(x)) & "." & "0" & Text.From(Date.Month(x)) & "." & Text.From(Date.Year(x))
else
if Date.Day(x) < 10
then "0" & Text.From(Date.Day(x)) & "." & Text.From(Date.Month(x)) & "." & Text.From(Date.Year(x))
else
if Date.Month(x) < 10
then Text.From(Date.Day(x)) & "." & "0" & Text.From(Date.Month(x)) & "." & Text.From(Date.Year(x))
else Text.From(Date.Day(x)) & "." & Text.From(Date.Month(x)) & "." & Text.From(Date.Year(x))
in
source

//Code end

 

Cheers!

 

 

mahesh_powerbi
Frequent Visitor

Hi @jribs ,

 

I am facing the same issue from past few weeks and desparately looking for a solution.

 

Please check this microsoft article about importing "key" along with the date column that apparently gets imported as Date. Although, the solution didn't work for me. 


Also check this post on another thread about same issue and try your luck. 

 

image.png

 

 

Please please let me know if you find a solution to perform incremental refresh or another workaround. My email ID is: mahesh.sohoni@gmail.com

 

I will surely post here in case I find a solution.

 

Thank you,

Mahesh

Hey @mahesh_powerbi ,

 

Apparently, there is configuration to be done on the SAP BW side to dictate how fields are delivered. So whatever configuration you have in the BW for Calendar Day.Calendar Day Level 01.Key has to be mimicked for Posting Date. I am not knowledgeable enough in what that change is to have an intelligent conversation about it.

 

We didn't have resources who could do this BW change, so I took a different approach:

 

  1. Manually Exported all records prior to arbitrary date (2018)
  2. Uploaded those records to Teams Sharepoint as historical record
  3. Set up SAP BW query to bring in new records (2018+)
  4. Appended the queries in Query Editor

That isn't exactly incremental refresh, but it has frozen pre-2018 while refreshing everything 2018+. If it gets to be too much and we run into memory errors, I simply move the needle to 2019 for both datasources. Hope that helps, but also hope you have the resources to help out on the BW, since that is the more robust solution.

 

Regards,

Josh

Hi @jribs ,

 

Thanks for the response and sharing the work around. Unfortunately, I dont know the configuration of Calendar Day Key column. It was someone else's post.

 

Regards,

Mahesh 

edhans
Super User
Super User

Unless someone has better knowledge on this, I don't think this will be possible using a table. I tried using SQL Server to convert a text based date "01/01/2020" to an integer based date - 20200101 - which you can use in Incremental Refresh. However, while I can get the text to work, converting to a number breaks folding, just like converting text to a date does. 

 

Number.From(Text.End([TextDate], 4))

This was the code just to get the year to a number. I didn't bother with the rest as that broke folding.

 

I think the best approach here is to work with your DBA and create a view of the table that converts the text date to a true date, then use that for incremental refresh instead of connecting to the table. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Agreed. I wish I was working with SQL so it was a bit more straight forward. But I'm not sure how it is done in SAP BW or if it is even working. Hoping someone who worked with SAP BW can help out

Icey
Community Support
Community Support

Hi @jribs ,

 

I'm curious if this methond, creating a function that converts the date/time value in the parameters to match the text surrogate key of the data source table, could work in your scenario. 🤔

 

 

Best Regards,

Icey

 

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

Icey
Community Support
Community Support

Hi @jribs ,

 

There is a scenario of "date columns of integer surrogate keys in the form of yyyymmdd". And based on the official document, we can create a function that converts the date/time value in the parameters to match the integer surrogate key of the data source table. For detailed steps, please refer to this document:

Configure incremental refresh for Power BI datasets - Power BI | Microsoft Docs.

 

Therefore, with reference to this method, we may also be able to achieve your needs. I don't have SAP BW, so I can't test it. Please check if it works in your scenario.

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Kudoed Authors