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 fill the DTP Error Stack with SQLScript in SAP BW

In the previous article, Two options for error handling with SAP BW and SQLScript, we presented the different scenarios of error handling within SAP BW on HANA and BW/4HANA. In this article, we will take a closer look at the first scenario.

In this scenario, the mechanisms of the DTP error handler are used. The incorrect records are written to the error stack, where they can be corrected manually afterwards. In the SQLScript transformation, the erroneous records are written to the errorTab table. In order to work with this feature on a BW/4HANA system, you need to activate the setting Allow Error Handling for HANA Routines in the transformation.

Allow error handling for HANA routines

The errorTab table consists of two fields: ERROR_TEXT and SQL__PROCEDURE__SOURCE__RECORD. In the ERROR_TEXT field you can output the description of the error. The SQL__PROCEDURE__SOURCE__RECORD field serves as an identifier for the respective record.

To illustrate how the error stack works, we use an AMDP routine that takes over all fields unchanged. At the same time, the COMP_CODE column is checked for invalid characters and the incorrect records are written to the errorTab table. To do so, we use the LIKE_REGEXPR command, which recognizes search patterns based on Perl Compatible Regular Expression (PCRE). We use the pattern [lower] to check if the field contains lowercase letters. We also use the LIKE command to find all records that begin with an exclamation mark. Finally, we also define all company codes as incorrect if they have the value # (not assigned).

outTab =
SELECT comp_code, currency, '' as recordmode, amount, record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

errorTab= SELECT 'Check field value!' AS ERROR_TEXT,
SQL__PROCEDURE__SOURCE__RECORD
FROM :intab
WHERE comp_code LIKE_REGEXPR '.*[[lower]].*'
OR comp_code LIKE '!%'
OR comp_code = '#';

The data source contains two incorrect records - the company codes (0COMP_CODE) !200 and #.

0COMP_CODE

0AMOUNT

0CURRENCY

1000

100,00

EUR

!200

200,00

EUR

#

300,00

EUR

 

Furthermore, we make the following settings in the DTP. First, error handling must be switched on. To do this, switch to the Update tab and select the option Request is set to failed, error stack is written, and valid records are updated under Error handling.

Request handling

For the error handling on the HANA database we also need to define semantic groups in the Extraction tab. These are used to determine incorrect records. For example, in case of a purchase order and purchase order item, we can define the purchase order as the key. If one item is incorrect, the entire purchase order will be marked as incorrect.

In our example it is quite simple - the company code 0COMP_CODE serves as key. Therefore, please select COMP_CODE under the Extraction Grouped By option.

Semantic key

Notice: 
Test the error handling thoroughly before productive use. In earlier service packs, despite the correct selection of the key fields, all data records ended up in the error stack.

 

As the SQL Script routine is executed, the incorrect company codes are identified. Using the SQL__PROCEDURE__SOURCE__RECORD ID, these are written to the errorTab table with the corresponding error message. 

As you can see from the screenshots, these are company codes !200 and #. Namely records 2 and 3.

Source records and error table

After the routine has been executed, these data records end up in the error stack.

Maintain error stack


Increase the performance of your BW with SQLScript

Neuer Call-to-Action


Depending on your requirements, you can extend this example to include additional fields to be checked. In practice, however, you do not need to check all fields. Experience shows that errors are more likely to occur in fields without a check table and in fields that are changed manually in the source. The table below provides an overview of the regular expressions you can use.

Expression

Description

a%

Value starts with “a”

%a

Value ends with “a”

%a%

Value has "a" at any position

_a%

Value has “a” in the second position

a_%

Value starts with “a” and is at least two characters long

a%z

Value starts with “a” and ends with “z”

[abc]

“a”, “b” or “c”

[a-z]

Lowercase letters

[A-Z]

Capital letters

[0-9]

Digit

.

Any character

[[:digit:]]

Digits 0-9, corresponds to the expression [0-9]

[[:lower:]]

Lowercase letters, corresponds to the expression [a-z]

[[:upper:]]

Capital letters, corresponds to the expression [A-Z]

[[:alpha:]]

All letters, corresponds to the expressions [a-z] and [A-Z] as well as [[: lower:]] and [[: upper:]]

[[:alnum:]]

All capital and lowercase letters and all digits. Corresponds to the expressions [:alpha:] and [:digit:], i.e. [A-Z,a-z,0-9].

[[:punct:]]

Punctuation marks, e.g.: . , " ' ? ! ; : # $ % & ( ) * + - / < > = @ [ ] \ ^ _ { } ~

[[:print:]]

All printable characters, corresponds to the expressions [: alnum:], [: punct:] and SPACE

[[:graph:]]

All printable characters without SPACE, corresponds to the expressions [: alnum:] and [: punct:]

[[:blank:]]

Contains SPACE and TAB

[[:space:]]

Contains all whitespace characters: SPACE, TAB, CR, FF, NL, VT.

[[:cntrl:]]

Contains control characters: ACK CAN CR DC1 DC2 DC3 DC4 DEL DLE EM ENQ EOT ESC ETB EXT FF IS1 IS2 IS3 IS4 LF NAK NL NUL SI SO SOH STX SUB TAB VT

[[:xdigit:]]

Contains hexadecimal digits (0-9, A-F, a-f)

[[=a=]]

Contains equivalent characters. For example all letters based on "a". Like ä, à, â, á, etc.

 

Are you planning to migrate to SQLScript and need help planning the right strategy? Or do you need experienced developers to implement your requirements? Please do not hesitate to contact us - we will be happy to assist you.

Learn more about  SAP HANA SQLScript

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