Do you need to report based on fiscal periods, and require KPI's such as a rolling fiscal year, or fiscal Year to Date? Does your fiscal year deviate from a calendar year? If you are using SAP Datasphere with SAP Analytics Cloud or a 3rd party reporting tool, then this blog series will help you realise those requirements. Today, there is no out of the box feature to provide this, but this blog series provides you with generators for fiscal calendars, and several SQL templates to build your fiscal period based KPI's.
This blog post is the first of the following series:
Below a screenshot of what is achieved in the 3rd blog post.
To report with KPI's based on fiscal year, fiscal periods, or even fiscal weeks, you basically need two things:
To fulfil the requirements of KPIs such as rolling fiscal year or fiscal YTD, we first need a fiscal calendar in SAP Datasphere. That is what is handled in this blog post. The logic to calculate KPIs in handled in subsequent blog posts.
This blog post and the subsequent post will each outline a method to generate a fiscal calendar:
Just so that you know how this looks like, let's look up a fiscal period configuration in an actual SAP ABAP-based source system. Below is an example of a fiscal period configuration in an SAP ABAP-based source system, which can be found under transaction OB29. It's likely that in your case, the settings here have already been made and there's no reason to make any changes. For this blog, I'd just like to show this screenshot so that you know what input I'm using for the result you will see later on. As you can see, we have a fiscal year that works with full calendar months, but which starts in April.
Tables T009 and T009B in an ABAP source system contain the metadata of the fiscal calendars in your source system. First, these tables need to be replicated to SAP Datasphere, before we can run a procedure to generate the full calendar. Below two screenshots of the content of these tables.
Table T009 and T009B need to be imported into SAP Datasphere from ECC/S4, e.g., using the SAP Datasphere ABAP connection. You can get these tables into SAP Datasphere in any way you prefer, it's just about having the tables and the data available.
The generator procedure that creates the fiscal calendar data can only be called from the Open SQL schema. Therefore, to run the procedure using the T009 and T009B tables, these first need to be exposed to the Open SQL schema. At time of writing, tables cannot be exposed, only views. So we first need to create a wrapper view around each of the tables. In my case, I have called those LV_T009 and LV_T009B.
If you do not have an Open SQL schema created yet, create a database user in the space management. Enable Read Access (SQL) and Enable Write Access (SQL).
We need a target table for the procedure to write the data to. Below the code for such table. For your reference, the structure of this table is derived from table _SYS_BI.M_FISCAL_CALENDAR, which already exists in the HANA database underneath your SAP Datasphere instance. However, Datasphere does not provide access to this table, hence we create the table ourselves.
Before you can run the procedure, you need to create copy tables with the data from the exposed views, and you need to create synonyms pointing to those tables. This seems kinda random, but the reason for this is that the procedure to generate the sample data is very specific with regards to its input requirements. Usually, this procedure is called from a UI, such as from SAP HANA Studio, or from SAP Business Application Studio, where these pre-requisites are already met and hidden from the end user.
Now the procedure can be run. The procedure UPDATE_FISCAL_CALENDAR is already residing in the HANA database underneath SAP Datasphere, and you should be able to execute it directly using your Open SQL schema user. Use the below code, and adjust the parameters where necessary.
Now, the fiscal period data should be generated in the target table in the Open SQL schema, and you should be able to consume this data back again in SAP Datasphere, as you can see in below screenshot.
In case the procedure generation does not work as expected, you can run a trace for diagnosing so-called "built-in procedures". The below two statements denote turning on and turning off such trace. The trace can then be found in the indexserver.trc file.
While writing this blog, I first ran the procedure without using synonyms to point to the source tables T009 and T009B. The below error was thrown, which pointed out the issue.
This blog explained how to generate fiscal period data, using a procedure that is delivered already with SAP Datasphere. This is the first step in working with fiscal reporting.
In the next blog post, you can learn how to generate a fiscal calendar without SAP ABAP source system, straight from a single view in SAP Datasphere. This approach also provides you with a week-based calendar option, which the approach in the current blog post does not provide. Then, in subsequent blog posts I will run through examples on how to build KPIs on this.
This blog post is the first of the following series:
- Fiscal calendar generation for SAP Datasphere using built-in procedure (this post)
- Standalone and week-based fiscal calendar generation for SAP Datasphere
- Running Year to Date (YTD) on fiscal periods with SAP Datasphere
Below a screenshot of what is achieved in the 3rd blog post.
Figure 1: Fiscal Year to Date calculation based on this blog series
To report with KPI's based on fiscal year, fiscal periods, or even fiscal weeks, you basically need two things:
- A fiscal calendar that allows you to join your transaction data with fiscal years and periods. Such calendar is basically a table that contains dates which map to fiscal year, fiscal period and optionally fiscal week. At time of writing, in SAP Datasphere, only a standard (Gregorian) calendar can be generated, but not fiscal calendars. If your fiscal year equals a regular (Gregorian) calendar, then you're fine, but otherwise you need an alternative.
- Logic to calculate KPI's based on fiscal year and periods. SAP Analytics Cloud has such functionality, but, at time of writing, for SAP Datasphere it only works on the Gregorian calendar views generated in a space, not on custom (fiscal) calendars.
To fulfil the requirements of KPIs such as rolling fiscal year or fiscal YTD, we first need a fiscal calendar in SAP Datasphere. That is what is handled in this blog post. The logic to calculate KPIs in handled in subsequent blog posts.
Alternative fiscal calendar generators
This blog post and the subsequent post will each outline a method to generate a fiscal calendar:
- The current blog post describes how to leverage an SAP source system fiscal calendar using an existing built-in procedure in SAP Datasphere.
- The next blog post describes a SQLScript-based approach to generate a fiscal calendar, which you can use without the need for any SAP source system. This calendar will also support somewhat different requirements, such as a week-based fiscal calendar.
Sample fiscal period configuration in source system
Just so that you know how this looks like, let's look up a fiscal period configuration in an actual SAP ABAP-based source system. Below is an example of a fiscal period configuration in an SAP ABAP-based source system, which can be found under transaction OB29. It's likely that in your case, the settings here have already been made and there's no reason to make any changes. For this blog, I'd just like to show this screenshot so that you know what input I'm using for the result you will see later on. As you can see, we have a fiscal year that works with full calendar months, but which starts in April.
Figure 1: Fiscal Period configuration in source system
Step 1: Import fiscal calendar metadata into SAP Datasphere
Tables T009 and T009B in an ABAP source system contain the metadata of the fiscal calendars in your source system. First, these tables need to be replicated to SAP Datasphere, before we can run a procedure to generate the full calendar. Below two screenshots of the content of these tables.
Figure 2: Table T009 sample data (PERIV=K1 is used throughout this blog)
Figure 3: Table T009B sample data (PERIV=K1 is used throughout this blog)
Table T009 and T009B need to be imported into SAP Datasphere from ECC/S4, e.g., using the SAP Datasphere ABAP connection. You can get these tables into SAP Datasphere in any way you prefer, it's just about having the tables and the data available.
Figure 4: Import fiscal variant tables from SAP source system
Step 2: Expose fiscal calendar metadata to Open SQL schema
The generator procedure that creates the fiscal calendar data can only be called from the Open SQL schema. Therefore, to run the procedure using the T009 and T009B tables, these first need to be exposed to the Open SQL schema. At time of writing, tables cannot be exposed, only views. So we first need to create a wrapper view around each of the tables. In my case, I have called those LV_T009 and LV_T009B.
Figure 5: Expose table T009 to the Open SQL schema as view LV_T009
If you do not have an Open SQL schema created yet, create a database user in the space management. Enable Read Access (SQL) and Enable Write Access (SQL).
Figure 6: Create database user under Space Management
Step 3: Create fiscal calendar table and run built-in procedure
We need a target table for the procedure to write the data to. Below the code for such table. For your reference, the structure of this table is derived from table _SYS_BI.M_FISCAL_CALENDAR, which already exists in the HANA database underneath your SAP Datasphere instance. However, Datasphere does not provide access to this table, hence we create the table ourselves.
CREATE COLUMN TABLE "OT_FISCAL_CALENDAR_SAP"(
"CALENDAR_VARIANT" NVARCHAR(2),
"DATE" NVARCHAR(8),
"DATE_SQL" DATE,
"FISCAL_YEAR" NVARCHAR(4),
"FISCAL_PERIOD" NVARCHAR(3),
"CURRENT_YEAR_ADJUSTMENT" NVARCHAR(2),
PRIMARY KEY INVERTED INDIVIDUAL(
"CALENDAR_VARIANT",
"DATE"
)
);Before you can run the procedure, you need to create copy tables with the data from the exposed views, and you need to create synonyms pointing to those tables. This seems kinda random, but the reason for this is that the procedure to generate the sample data is very specific with regards to its input requirements. Usually, this procedure is called from a UI, such as from SAP HANA Studio, or from SAP Business Application Studio, where these pre-requisites are already met and hidden from the end user.
CREATE TABLE OT_T009 AS (SELECT * FROM SEFANSFISCALSTUFF.LV_T009);
CREATE TABLE OT_T009B AS (SELECT * FROM SEFANSFISCALSTUFF.LV_T009B);
CREATE SYNONYM OT_T009_SYN FOR OT_T009;
CREATE SYNONYM OT_T009B_SYN FOR OT_T009B;Now the procedure can be run. The procedure UPDATE_FISCAL_CALENDAR is already residing in the HANA database underneath SAP Datasphere, and you should be able to execute it directly using your Open SQL schema user. Use the below code, and adjust the parameters where necessary.
CALL "UPDATE_FISCAL_CALENDAR"(
T009_SYNONYM_NAME => 'OT_T009_SYN'/*<NVARCHAR(256)>*/,
T009B_SYNONYM_NAME => 'OT_T009B_SYN'/*<NVARCHAR(256)>*/,
T009_SCHEMA_NAME => 'SEFANSFISCALSTUFF#FISCAL_CALENDAR_SAP'/*<NVARCHAR(256)>*/,
FISCAL_VARIANT => 'K1'/*<NVARCHAR(3)>*/,
FROM_YEAR => '2022'/*<NVARCHAR(4)>*/,
TO_YEAR => '2023'/*<NVARCHAR(4)>*/,
CLIENT => '100'/*<NVARCHAR(3)>*/,
TARGET_SCHEMA_NAME => 'SEFANSFISCALSTUFF#FISCAL_CALENDAR_SAP'/*<NVARCHAR(256)>*/,
TARGET_TABLE_NAME => 'OT_FISCAL_CALENDAR_SAP'/*<NVARCHAR(256)>*/,
WITH_LEADING_ZEROS => 0 /*<TINYINT>, use 0 or 1 */
);Now, the fiscal period data should be generated in the target table in the Open SQL schema, and you should be able to consume this data back again in SAP Datasphere, as you can see in below screenshot.
Figure 7: Data preview of the generated data, imported into SAP Datasphere
Troubleshooting: trace statement for internal procedures
In case the procedure generation does not work as expected, you can run a trace for diagnosing so-called "built-in procedures". The below two statements denote turning on and turning off such trace. The trace can then be found in the indexserver.trc file.
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') SET ('trace', 'BuiltinProc') = 'debug' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') UNSET ('trace', 'BuiltinProc') WITH RECONFIGURE;While writing this blog, I first ran the procedure without using synonyms to point to the source tables T009 and T009B. The below error was thrown, which pointed out the issue.
[2509]{213222}[3089/-1] 2022-12-08 14:16:12.709533 d BuiltinProc FiscalCalendar.cpp(00052) : UpdateFiscalCalendarInHDI: theSynonym is not a synonym: T009_COPY
[2509]{213222}[3089/-1] 2022-12-08 14:16:12.709562 d BuiltinProc BP_UpdateFiscalCalendar.cpp(00106) : BuiltinProcedure_UPDATE_FISCAL_CALENDAR finished with status: 1. Invalid synonym parameter specified: 'T009_COPY'Conclusion
This blog explained how to generate fiscal period data, using a procedure that is delivered already with SAP Datasphere. This is the first step in working with fiscal reporting.
In the next blog post, you can learn how to generate a fiscal calendar without SAP ABAP source system, straight from a single view in SAP Datasphere. This approach also provides you with a week-based calendar option, which the approach in the current blog post does not provide. Then, in subsequent blog posts I will run through examples on how to build KPIs on this.
- SAP Managed Tags:
5 Comments
