Skip to content
NextLytics
Megamenü_2023_Über-uns

Shaping Business Intelligence

Whether clever add-on products for SAP BI, development of meaningful dashboards or implementation of AI-based applications - we shape the future of Business Intelligence together with you. 

Megamenü_2023_Über-uns_1

About us

As a partner with deep process know-how, knowledge of the latest SAP technologies as well as high social competence and many years of project experience, we shape the future of Business Intelligence in your company too.

Megamenü_2023_Methodik

Our Methodology

The mixture of classic waterfall model and agile methodology guarantees our projects a high level of efficiency and satisfaction on both sides. Learn more about our project approach.

Products
Megamenü_2023_NextTables

NextTables

Edit data in SAP BW out of the box: NextTables makes editing tables easier, faster and more intuitive, whether you use SAP BW on HANA, SAP S/4HANA or SAP BW 4/HANA.

Megamenü_2023_Connector

NextLytics Connectors

The increasing automation of processes requires the connectivity of IT systems. NextLytics Connectors allow you to connect your SAP ecosystem with various open-source technologies.

IT-Services
Megamenü_2023_Data-Science

Data Science & Engineering

Ready for the future? As a strong partner, we will support you in the design, implementation and optimization of your AI application.

Megamenü_2023_Planning

SAP Planning

We design new planning applications using SAP BPC Embedded, IP or SAC Planning which create added value for your company.

Megamenü_2023_Dashboarding

Dashboarding

We help you with our expertise to create meaningful dashboards based on Tableau, Power BI, SAP Analytics Cloud or SAP Lumira. 

Megamenü_2023_Data-Warehouse-1

SAP Data Warehouse

Are you planning a migration to SAP HANA? We show you the challenges and which advantages a migration provides.

Business Analytics
Megamenü_2023_Procurement

Procurement Analytics

Transparent and valid figures are important, especially in companies with a decentralized structure. SAP Procurement Analytics allows you to evaluate SAP ERP data in SAP BI.

Megamenü_2023_Reporting

SAP HR Reporting & Analytics

With our standard model for reporting from SAP HCM with SAP BW, you accelerate business activities and make data from various systems available centrally and validly.

Megamenü_2023_Dataquality

Data Quality Management

In times of Big Data and IoT, maintaining high data quality is of the utmost importance. With our Data Quality Management (DQM) solution, you always keep the overview.

Career
Megamenü_2023_Karriere-2b

Working at NextLytics

If you would like to work with pleasure and don't want to miss out on your professional and personal development, we are the right choice for you!

Megamenü_2023_Karriere-1

Senior

Time for a change? Take your next professional step and work with us to shape innovation and growth in an exciting business environment!

Megamenü_2023_Karriere-5

Junior

Enough of grey theory - time to get to know the colourful reality! Start your working life with us and enjoy your work with interesting projects.

Megamenü_2023_Karriere-4-1

Students

You don't just want to study theory, but also want to experience it in practice? Check out theory and practice with us and experience where the differences are made.

Megamenü_2023_Karriere-3

Jobs

You can find all open vacancies here. Look around and submit your application - we look forward to it! If there is no matching position, please send us your unsolicited application.

Blog
NextLytics Newsletter Teaser
Sign up now for our monthly newsletter!
Sign up for newsletter
 

How to create SAP BW Transformations with SQL Script

In this previous article you got to know the advantages of SQL Script and the decision criteria for implementation of SQL Script routines. In this article, I will show you how to create SAP BW transformation routines with SQL Script in detail.

Although SQL transformations benefit from code pushdown, you have to do without predefined ABAP functions and implement the desired logic in SQL Script. Using a concrete application case, I will show you how to convert fiscal periods into calendar months.

Create Transformation Routines

The fiscal periods are in version V3. In order to derive the corresponding calendar months, we have to calculate three months back and also take  the annual shift into account.

Source data

In the table below you will find our example with the fiscal periods and the corresponding calendar months that have to be determined.

Fiscal period
Calendar month
2019001 201810
2019002 201811
2019003 201812
2019004 201901
2019005 201902
2019006 201903
2019007 201904
2019008 201905
2019009 201906
2019010 201907
2019011 201908
2019012 201909

 

We will implement this logic in form of a transformation routine. To do this, we create a transformation in the BW Modeling Tools. Please note that initially both ABAP runtime and HANA runtime are supported. You can read this information under Runtime Status.

ABAP and HANA runtime are supported

To convert fiscal periods into calendar months, we create an expert routine in which we have access to source and target fields. Please follow the menu path EditRoutinesExpert routine to create an expert routine.

Create an expert routine

You will be asked if you want to delete the existing transformation and replace it with an expert routine. Please confirm with Yes. Then you will be asked if you want to create an ABAP routine or an AMDP script. To benefit from the HANA Code Pushdown, select the option AMDP Script.

Create an AMDP Script

Afterwards you have to select an existing ABAP project or create a new project in BW Modeling Tools. Then you can implement your logic within the PROCEDURE method. The tables outTab and errorTab are used as transfer parameters. Please note that the order of the SQL statements must match the table definition. The table definition can be seen at the beginning of the class definition.

2019-09-25_14_05_52-leere-methode

In our case the logic is:

outTab =
SELECT company, fiscper, fiscvarnt,
CASE
WHEN SUBSTRING(fiscper,5,3) = 001 then CONCAT(SUBSTRING(fiscper,1,4) - 1, SUBSTRING(fiscper,5,3) - 3 + 12)
WHEN SUBSTRING(fiscper,5,3) = 002 then CONCAT(SUBSTRING(fiscper,1,4) - 1, SUBSTRING(fiscper,5,3) - 3 + 12)
WHEN SUBSTRING(fiscper,5,3) = 003 then CONCAT(SUBSTRING(fiscper,1,4) - 1, SUBSTRING(fiscper,5,3) - 3 + 12)
ELSE CONCAT(SUBSTRING(fiscper,1,5), SUBSTRING(fiscper,5,3) - 3)
END AS calmonth,
currency, recordmode, amount, record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

Increase the performance of your BW with SQLScript

Neuer Call-to-Action


If you don't want to implement error handling, you can transfer an empty errorTab table.

errorTab =
SELECT '' AS ERROR_TEXT,
'' AS SQL__PROCEDURE__SOURCE__RECORD
FROM DUMMY
WHERE DUMMY <> 'X';

After implementing the logic, please activate the AMDP class and the transformation. Note that after implementation of the AMDP routine, only the HANA runtime is supported.

Only HANA runtime is supported

You can review the HANA transformation via ExtrasDisplay Generated HANA Transformation.

Display generated HANA transformation

There you can switch to the expert mode via ExtrasExpert mode to display the calculation scenario.

Show Expert mode

There you can see the script definition as well as the XML representation of the calculation scenario.

Calculation scenario and Script Definition

If you now create a DTP, you will see that it is running in HANA.

DTP compatible with SAP HANA Execution

Please activate the DTP and execute it. The calendar months are filled according to the defined logic. You can see the result on the picture below.

Calendar months are derived correctly

 

Learn more about  SAP HANA SQLScript

Image source: Pexels, CC0 License

, ,

avatar

Chris

Chris Fidanidis has been working in the SAP BW environment since 2007. During these years he has implemented several planning projects and used various SAP tools such as SAP BSP, SAP BW-IP, SAP BPC, SAP BW Embedded BPC. He has gained experience primarily as a developer, architect, project manager and team leader. He enjoys playing basketball and barbecue whenever possible.

Got a question about this blog?
Ask Chris

Blog - NextLytics AG 

Welcome to our blog. In this section we regularly report on news and background information on topics such as SAP Business Intelligence (BI), SAP Dashboarding with Lumira Designer or SAP Analytics Cloud, Machine Learning with SAP BW, Data Science and Planning with SAP Business Planning and Consolidation (BPC), SAP Integrated Planning (IP) and SAC Planning and much more.

Subscribe to our newsletter

Related Posts

Recent Posts