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:
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.
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):
Go ahead and try this in your system, I am looking forward to your feedback and comments!
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):
- 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.
Conclusion
Go ahead and try this in your system, I am looking forward to your feedback and comments!
- SAP Managed Tags:
29 Comments
