Skip to Content
Technical Articles
Author's profile photo Jens Braun

Flexible date handling in SAP Analytics Cloud with BW Live Connection

When building stories with the BW Live Connection the question comes up how to handle the date dimensions?
Do I use BW query variables for the date selection? How can I offer the users flexibility regarding selection of the relevant data and allow them to do comparisons to other date periods?

In this blog post I would like to describe an approach that offers a great user experience together with a lot of flexibility for the user.

Current situation

Most BW queries that were built for Analysis for Office or other BI tools contain variables for the date selection. And those variables can of course be used in SAC, they can be added as a story filter for a better integration into the story. But you cannot use the selections to implement date filters in charts and tables.
Additionally, those BW queries very often contain restricted key figures to show KPIs for date periods like current month, year to date, previous year, … all built using variables, restricted key figures, … . Everyone who has done this knows that it’s a lot of work and not very flexible.

Wouldn’t it be nice to have an easy option to implement date comparisons and a flexible date filters with less effort? Maybe even one that the users can set up themselves? One that allows you to select the key date for a story and have all charts and widgets adjust accordingly?

Well, of course there is one, otherwise this blog would not make much sense!

New approach

The general approach is to let SAP Analytics Cloud handle all the date topics instead of the BW query. One little spoiler right at the beginning: this approach currently works for date fields only. You must have a date field in your BW query.

Next time you build a BW query just leave out the date variables and instead use the date filters in the story which are forwarded to SAP BW.
If you are worried that users might accidentally read too much data from your BW, then you can of course restrict the query to provide the last 3 years for example

The following chapters describe functions that are important for flexible data handling in SAP Analytics Cloud:

Virtual time hierarchies and date attributes in SAP BW

SAP BW allows the creation of virtual hierarchies on the content date dimensions like day, month, year, … . In my experience many customers do not use date hierarchies in their BW. Even if the feature has been around for some time (see https://blogs.sap.com/2014/05/02/virtual-time-hierarchy/ for more information). You use transaction RSRHIERARCHYVIRT to activate the hierarchies you need.

But I think that using date hierarchies makes reporting very flexible, especially when using them in charts in SAP Analytics Cloud. Being able to drill down along a hierarchy is easy and allows the user to choose the relevant date granularity.

In addition, you can activate navigational attributes for date characteristics to easily include the higher date characteristics in reporting. This simplifies modelling in BW, as you can add month, year, … to basically any date field in your composite provider without having to enhance your physical data.

Dynamic date selections and key date

The range selection for date fields in SAP Analytics Cloud allows a dynamic and flexible selection of dates. It can be used for widget filters and restricted key figures to build selections like YTD, current month, LY YTD, … you name it:

The “custom current date” function allows you to create a key date for your story that you can reference from all your time filters and that can be changed by the user while viewing the story. When you open the story, they key date is set to the current system date. But you can decide to go back in time and select the end of the last quarter or year and have all charts display the data in relation to that point in time. And all without any coding!

The custom current date is defined once per story and is identified by a story filter. It is created in the definition of a range filter for a date dimension and can be created using the drop-down box below “current date”. You can select the granularity of this key date depending on your data and how the users shall work with the story: it can be a date, month, year, …: and it is given a name to describe this to the user:

Users can now use this story filter item to select the required point in time and all charts, tables and restricted key figures in the story adapt automatically:

The creator of the story should make sure to activate the option “System current Date / Period” when saving the story, as this ensures that the story is always opened for the current date and therefore dynamically adjusts the date selections in the story:

For more information on the date selections can be found in this blog: https://blogs.sap.com/2019/12/02/last-x-months-trend-for-a-given-month/

Time comparisons

Now that the time selections and filters have been implemented, I recommend using variance charts for time to display any deviations to the previous year/period/… .

SAP Analytics Cloud has offered this feature for quite some time. And again, this is so much easier than in BW queries, because you can add the variance with a few clicks. There is a good description of this feature here: https://www.sapanalytics.cloud/resources-filtering-data-by-time/

And using variances on a date field with hierarchies brings another great possibility: the variance to the previous year automatically adjusts to the hierarchy level. Start by showing the variance to the previous year on a yearly level and then simply drill down to quarter and month to see the

This is great and allows for flexible top-down analysis by the users.

There are three important prerequisites for time-based variance on a BW Live Connection (see KBA https://launchpad.support.sap.com/#/notes/2948031):

  1. Source system must be BW 7.5 or BW/4HANA 2.0 (see https://launchpad.support.sap.com/#/notes/2715030)
  2. Active virtual hierarchy on the date dimension in the story (see chapter on virtual hierarchies)
  3. The date infoobject you use must have navigation attributes for the more general time dimensions. And they must be in the free characteristics of the BW query.

Conclusion

This is my proposal how to create flexible stories regarding dates. And when I show this to SAP BW customers in a SAP Analytics Cloud demo, they really like it, because they see the simplification and increased flexibility as opposed to what they know from BW queries.

Go ahead and try this in your system, I am looking forward to your feedback and comments!

Assigned Tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Erdem Pekcan
      Erdem Pekcan

      Hi Jens,

      Thanks for the useful blog!

      What's the required SAC version here? All conditions are met on my BW side but I don't see the "Compare to" option on my SAC tenant which is on Q3 release.

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Hello Erdem,

      thanks for your feedback! The functions are all available with the Q3 release.

      When the "Compare to" option is not shown, then one of the prerequisites is not fulfilled.

      1. have you activated virtual hierarchies for the date field in SAP BW?
      2. Are the nav. attributes für 0DATE and the used characteristic active and in the free characteristics of the BW query?
      3. is the date field in the drilldown with an active hierarchy?

      Regards,
      Jens

      Author's profile photo Erdem Pekcan
      Erdem Pekcan

      Yes, I can use hierarchies and nav. attr.s of 0CALDAY on any SAC story. I'm on 7.5 SP18.

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      And are the nav. attributes in the free characteristics of the query?

      Author's profile photo Erdem Pekcan
      Erdem Pekcan

      Yes, they are (bottom left on the image above).

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Sorry, I only noticed the mouse-over now.

      Honestly, I have no idea why it should not work, all the requirements have been set. The only difference is the BW version (you 7.5, me BW/4HANA).

      I suggest that you open an incident for this to have the situation looked at.

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Have you tried implementing note 2938327?https://launchpad.support.sap.com/#/notes/2938327

      Author's profile photo Erdem Pekcan
      Erdem Pekcan

      I've implemented it and tried again but unfortunately it didn't help.

      Author's profile photo Valentin Franke
      Valentin Franke

      Hi Erdem Pekcan,

      did you find a solution to your problem?

      Author's profile photo Erdem Pekcan
      Erdem Pekcan

      Hi Valentin Franke ,

      Not really, it was stucked at that point of time.

      Author's profile photo Michael Hamm
      Michael Hamm

      Hi Jens,

      very helpful blog post. Will this be enhanced in the future to support other variables like versions?

      BR,

      Michael

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Hello Michael,

      variances on versions are already supported. You need to have your versions in an account model with one infoobject as the version. And then you need to map the version values in the SAP Analytics Cloud model. There is a waypost in the toolbar of the model maintenance.

      Click that and you can map all the version from BW to a category in SAP Analytics Cloud:

      Actuals can only be mapped once, all other categories multiple times.

      Regards,
      Jens

      Author's profile photo Sveinar Urstad
      Sveinar Urstad

      .

      Author's profile photo Sveinar Urstad
      Sveinar Urstad

      Hi Jens,

      Very helpful and detailed blog!  Do you know how to get dynamic time handling in SAC for BW Live models that do not have 0DATE?   Example 0CALMONTH (Calendar year/month), 0CALYEAR (Calendar year),  or 0FISCPER (Fiscal year/period)?  Many models,  both actuals and planning data do not have date as a granularity level.

       

      Br,
      Sveinar

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Hello Sveinar,

      at the moment it is unfortunately not possible. You need a date dimension for this.

      One way around this would be to add the last day of the period to the model and then use this for the flexible variances. But this depends on the involved effort to enhance the model.

      Regards,
      Jens

      Author's profile photo Valentin Franke
      Valentin Franke

      Hi Jens, any idea when this will be possible? SAC doesn’t recognize our Time InfoObjects as dates which makes modeling really hard and ugly in SAC.

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Hello Valentin,

      unfortunately there is no release date yet.

      Regards,
      Jens

      Author's profile photo Valentin Franke
      Valentin Franke

      Thank you for the quick answer! We will try it with "0DATE" then. I hope our conversion routines won't be a hinderence.

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Aren't our date characteristics referencing to 0DATE?

      Author's profile photo Valentin Franke
      Valentin Franke

      I don't think so:

      So far none of our Time InfoObjects are recognized as date in SAC.

      Author's profile photo Usha Rani Desu
      Usha Rani Desu

      Valentin Franke ,Were you able to achieve it, any workaround ?

      Author's profile photo Sveinar Urstad
      Sveinar Urstad

      Hi Jens,

      Do you have any updates on when this feature (dynamic time selection for 0CALYEAR and 0FISCPER)  will become available in SAC with BW Live Connection?  It is extremely frustrating for our business end-users that they cannot do range filtering or dynamic selections on Calendar Year. We need to compensate for this everywhere by creating restricted measures (on cal.year or other time dimensions) in BW.

      Best regards,

      Sveinar

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Hello Sveinar,

      unfortunately I do not have any news on this ... I am always wishing for this feature to arrive.

      Regards,
      Jens

      Author's profile photo Grant Bennett
      Grant Bennett

      Hello Jens,

      Great article, thanks. Just checking in, as more than a year has passed since the last update to see if there has been any progress or possible release date on the (dynamic time selection for 0CALYEAR and 0FISCPER) without making the attributes navigational in BW?

      Regards

      Grant

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      What about not standard calendar support? Like retail calendar 5-4-4 (5 weeks, 4 weeks, 4 weeks - months)

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      Unfortunately not yet, for this we need to support fiscal periods from BW with a specific fiscal year variant. At least that how I think retail calendars are implemented in BW.

      This topic is on the SAP Analytics Cloud roadmap, but currently without a delivery date.

      Regards,
      Jens

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      One of my customers stopped looking on SAC implementation without retail calendar support. They are using old BPC NW were it was easy to implement custom TIME dimension according to retail calendar rules.

      In SAC we also can't use imported data with retail calendar TIME dimension...

      Author's profile photo Jens Braun
      Jens Braun
      Blog Post Author

      I can understand that and that's why this topic is being worked on. Of course that does not help your customer right now!

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      In general, we need a customizable TIME dimension in SAC like we have in BPC standard 🙂