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.
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!
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/
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):
- Source system must be BW 7.5 or BW/4HANA 2.0 (see https://launchpad.support.sap.com/#/notes/2715030)
- Active virtual hierarchy on the date dimension in the story (see chapter on virtual hierarchies)
- 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.
Go ahead and try this in your system, I am looking forward to your feedback and comments!
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.
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.
Yes, I can use hierarchies and nav. attr.s of 0CALDAY on any SAC story. I'm on 7.5 SP18.
And are the nav. attributes in the free characteristics of the query?
Yes, they are (bottom left on the image above).
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.
Have you tried implementing note 2938327?https://launchpad.support.sap.com/#/notes/2938327
I've implemented it and tried again but unfortunately it didn't help.
Hi Erdem Pekcan,
did you find a solution to your problem?
Hi Valentin Franke ,
Not really, it was stucked at that point of time.
very helpful blog post. Will this be enhanced in the future to support other variables like versions?
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.
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.
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.
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.
unfortunately there is no release date yet.
Thank you for the quick answer! We will try it with "0DATE" then. I hope our conversion routines won't be a hinderence.
Aren't our date characteristics referencing to 0DATE?
I don't think so:
So far none of our Time InfoObjects are recognized as date in SAC.
Valentin Franke ,Were you able to achieve it, any workaround ?
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.
unfortunately I do not have any news on this ... I am always wishing for this feature to arrive.
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?
What about not standard calendar support? Like retail calendar 5-4-4 (5 weeks, 4 weeks, 4 weeks - months)
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.
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...
I can understand that and that's why this topic is being worked on. Of course that does not help your customer right now!
In general, we need a customizable TIME dimension in SAC like we have in BPC standard 🙂