Enabling Dynamic Fiscal filtering in SAC (BW Live)
At time of writing, SAP Analytics Cloud (Q4, 2021.20) does offer dynamic calendar-based time filtering. This enables users to quickly analyse data for periods such as Current Year, Quarter and Month to Date. The same functionality is also exposed when working with SAP BW Live models containing a
My organisation needed a solution which allowed Self Service users a way to easily build Stories which rely heavily on Financial Year time periods (July – June FY in Australia). The solution had to be:
- As simple as possible for end users to understand and use
- Flexible, so that users could define their own dynamic ranges (we couldn’t predict every scenario, so proliferating measures in models was not viable)
- Dynamic, so that users would not need to manually update filters in Stories as time moved on
- Rapid to implement from a development perspective
- Low maintenance from a BW perspective – no additional staging of data or loads
- Easy to roll out to all relevant existing Composite Providers and queries.
Below I describe an architected solution enabling users to create dynamic Restricted Measures or just add dynamic time filtering to Stories in SAC.
Step 1: Establishing the Building blocks
To start off, I first needed to address another age-old limitation which is that the delivered BW time characteristics cannot have custom attributes added to their master data. The solution here was simply to create two new standard InfoObjects which would carry the attributes that could not be added natively to
- Two new InfoObjects are created in BW, named
FISCYEAR is created as NUMC(4), with Master Data and also Texts (optional) selected:
Six attributes are then added. The first four are a simple flag and so are typed as CHAR(1). The last two are CHAR(5) to contain some offset values which will be calculated. All are marked as Navigational.
Not all of these are critical for the solution, in fact only the last two will be exposed to SAC later in the demo. The other flags we have in use for specific unrelated modelling requirements.
FISCPER3 is created as CHAR(3) to mimic
0FISCPER3. It just has Master Data defined.
Four attributes are defined, in this case all are CHAR(1) just containing a flag.
Step 2: Adding the Master Data
In order to provide dynamic master data to these new objects without requiring any additional data loads or persistence, a choice was made to provide virtualised master data sourced from a HANA View.
We can natively read an SAP HANA Calculation View to provide master data for an InfoObject, but in this situation I wanted to script the logic so began with two Table Functions which were then exposed through Calculation views.
SAP HANA Table Functions
The Table Function for
FISCYEAR master data provides a generated list of Financial years, determining all the required attributes in addition to the Display Text used in other reporting:
FUNCTION "_SYS_BIC"."ZBW.Functions::TF_FISCYEAR_MASTER" ( ) RETURNS TABLE ( FISCYEAR VARCHAR(4), CURR_YEAR VARCHAR(1), LAST_YEAR VARCHAR(1), CURR_YEAR_LP VARCHAR(1), LAST_YEAR_LP VARCHAR(1), YEAR_OFFSET VARCHAR(5), YEAR_OFFSET_LP VARCHAR(5), TXTSH VARCHAR(20)) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER DEFAULT SCHEMA SAPHBW AS BEGIN DECLARE CURR_YR INT = YEAR(CURRENT_DATE); DECLARE CURR_YR_LP INT = YEAR(ADD_MONTHS(CURRENT_DATE,-1)); IF MONTH(CURRENT_DATE) > 6 THEN CURR_YR = CURR_YR + 1; END IF; IF MONTH(ADD_MONTHS(CURRENT_DATE,-1)) > 6 THEN CURR_YR_LP = CURR_YR_LP + 1; END IF; RETURN SELECT ABAP_NUMC(GENERATED_PERIOD_START, 4) AS FISCYEAR, CASE GENERATED_PERIOD_START WHEN :CURR_YR THEN 'X' ELSE '' END AS CURR_YEAR, CASE GENERATED_PERIOD_START WHEN :CURR_YR - 1 THEN 'X' ELSE '' END AS LAST_YEAR, CASE GENERATED_PERIOD_START WHEN :CURR_YR_LP THEN 'X' ELSE '' END AS CURR_YEAR_LP, CASE GENERATED_PERIOD_START WHEN :CURR_YR_LP - 1 THEN 'X' ELSE '' END AS LAST_YEAR_LP, CASE WHEN GENERATED_PERIOD_START - :CURR_YR = 1 THEN 'NEXT' WHEN GENERATED_PERIOD_START - :CURR_YR = 0 THEN 'CURR' WHEN GENERATED_PERIOD_START - :CURR_YR = -1 THEN 'LAST' WHEN GENERATED_PERIOD_START - :CURR_YR < -1 THEN '-' || ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR,2) ELSE ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR,2) END AS YEAR_OFFSET, CASE WHEN GENERATED_PERIOD_START - :CURR_YR_LP = 1 THEN 'NEXT' WHEN GENERATED_PERIOD_START - :CURR_YR_LP = 0 THEN 'CURR' WHEN GENERATED_PERIOD_START - :CURR_YR_LP = -1 THEN 'LAST' WHEN GENERATED_PERIOD_START - :CURR_YR_LP < -1 THEN '-' || ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR_LP,2) ELSE ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR_LP,2) END AS YEAR_OFFSET_LP, ABAP_NUMC(GENERATED_PERIOD_START - 1, 4) || '/' || ABAP_NUMC(GENERATED_PERIOD_START,2) AS TXTSH FROM "PUBLIC"."SERIES_GENERATE_INTEGER" (1, 1970, 2100); END;
The above function returns several flags based on the determination of the current Financial Year, derived from the current system date.
It also calculates offset values from the Current Determined Financial Year which are the ones critical to this solution.
You will notice two variations in the flags and offsets determined above. One set will support reporting where the user needs to include derivation from the current open Month, and the second set calculate values based on the last Period.
Within my organisation most financial reporting takes place against completed periods, so the current open period is generally excluded. Some reporting requirements however do need to reference the current month. Providing both sets allows flexibility for end users while building their SAC Stories.
It is worth noting that the above code assumes the Australian Financial Year, beginning in July and ending June the following calendar year. Within my organisation we do not have a requirement to support international reporting for different Financial Year Variants. If there was a requirement to do so, then the likely approach would be to compound
0FISCVARas per standard BW, and enhance the code above to provide determinations for the different Fiscal Year Variants.
Noting also that this function leverages
SERIES_GENERATE_INTEGER to provide a generated input list of Years. I chose to provision data for a range of 1970 – 2100. This range could be enlarged or reduced by updating the parameters, or even dynamically determined using the current year to provide a sliding window.
For the ‘Year Offset’ values, I chose to provide unique text values for the Current, Next and Last Financial Years to improve the SAC user experience, but these could be omitted and just left as numerical values depending on preference.
The function outputs a list of values similar to the following:
The Table Function for
FISCPER3 master data provides a generated list of Financial Periods from 000 – 012, determining all the required attributes for both contexts of based on current open period, or last completed period.
FUNCTION "_SYS_BIC"."ZBW.Functions::TF_FISCPER3_MASTER" ( ) RETURNS TABLE ( FISCPER3 NVARCHAR(3), CURR_PERD NVARCHAR(1), LAST_PERD NVARCHAR(1), YTD_L_PER NVARCHAR(1), YTD_C_PER NVARCHAR(1)) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER DEFAULT SCHEMA SAPHBW AS BEGIN -- Generate initial list of periods using SERIES_GENERATE_DATE -- The years of the dates passed to SERIES_GENERATE_DATE are not critical as we just need one record for the 1st of each month -- Initial DUMMY SELECT starts table with record for period '000' -- Current and Last Period flags derived from current system date, so are calendar based PeriodTab = SELECT '000' FISCPER3,'' CURR_PERD,'' LAST_PERD FROM DUMMY UNION SELECT ABAP_NUMC(ELEMENT_NUMBER,3) FISCPER3, CASE WHEN MONTH(GENERATED_PERIOD_START) = MONTH(CURRENT_DATE) THEN 'X' ELSE '' END CURR_PERD, CASE WHEN MONTH(GENERATED_PERIOD_START) = MONTH(ADD_MONTHS(CURRENT_DATE,-1)) THEN 'X' ELSE '' END LAST_PERD FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH', '2000-07-01', '2001-07-01'); -- Return statement uses initial generated table plus two extra columns derived for the Current and Last Period -- The inline nested SELECT statements look inefficient but performance is actually better than assigning Scalars up front RETURN SELECT FISCPER3, CURR_PERD, LAST_PERD, CASE WHEN FISCPER3 <= (SELECT FISCPER3 FROM :PeriodTab WHERE LAST_PERD = 'X') THEN 'X' ELSE '' END AS YTD_L_PER, CASE WHEN FISCPER3 <= (SELECT FISCPER3 FROM :PeriodTab WHERE CURR_PERD = 'X') THEN 'X' ELSE '' END AS YTD_C_PER FROM :PeriodTab; END;
SERIES_GENERATE_DATE is used to supply a source list of dates which are processed for the attributes. The generation start and end Years are not important as long as the months align with the start and end months for the required Financial Year construct.
Again if you needed to support multiple fiscal year variants then the object could be compounded to
0FISCVAR and the function updated accordingly.
The function outputs a list of values similar to the following:
Graphical Calculation Views
Now that we have the two HANA Table Functions in place, two graphical Calculation Views are created to just present the output directly. Since there is no additional logic contained in the Calculation views, I have not included additional detail for these. They are configured with a Data Category of ‘Dimension, and just use a single Projection node to map all the Table Function fields to the Semantics.
Populating the Master Data
Mapping the output of the Calc Views to the InfoObjects is performed via configuration of the Read Access for the InfoObjects on the Master Data/Texts tab for the InfoObjects.
The results are now visible in the Master Data view for the InfoObjects:
Step 3: Adding Dimensions to the Composite Provider
With the new InfoObjects now available, they need to be added in to the Composite Providers as required. This can be done just with a field mapping in the Target scenario
Once the target fields are created and associated with the new InfoObjects, then the existing source fields for
0FISCPER3 can be directly assigned.
Using the new Dimensions
In their simplest form the new dimensions can be directly added to any Story in SAC. The example below is for illustration purposes to see how the dynamic dimensions come through.
Note that for the flag based dimensions, short texts have been maintained in BW just for presentation purposes.
Probably the main purpose for the new Dimensions however would be to create dynamic Restricted Measures for use in charting and reporting. The benefit being that as time moves forward, the parameters for the Restricted Measures do not need to be updated as the values in the Dimensions will change according to the date.
For example, creating a Restricted Measure for YTD Budget values (not including the Current Period):
Although still not as elegant as the native dynamic time selection baked into the SAC UI for Calendar based date ranges, the above approach will enable very flexible selection options. End users will be able to self-serve when configuring dynamic Financial based time periods and they will not need to constantly update filters to include the next period or roll the year forward.
The development approach within HANA and BW is minimally disruptive and the solution can be rapidly deployed to any existing standard Composite Provider and Query without revisiting the underlying persisted data model (assuming
0FISCPER3 already exist in the Composite Provider).
I hope this idea helps anyone else looking for a temporary solution until SAP add native Fiscal support to SAC for BW Live Models.