Removing zero padding in HANA output

[Former Member]

Hi all,

In SAP table MARA the material/article number is zero padded as the field is 18 characters long. In display in SAP source system you dont necessarily see the zero padding. However, in HANA the table output shows the zero padding.

I have a requirement to export this data without the zero padding. I know you can create Virtual columns in the SQL logic of the database table and then use this as the display in the output but I really dont want to have to create manual code at the database level which isnt even transportable.

Is there a way to strip the zeros in the output using SQL syntax in Stored Procedures? I have tried to specify the output data types as different types such as integer but this doesnt work.

Any knowledge around this area for outputing the same material number less the padding?

Any help would be greatly appreciated. As always im interested in minimising performance impact as much as possible!

Kind regards,

Danielle

[former_member184768 · Accepted solution]

Hi Danielle,

Can you please provide some sample values of the Article no. If it is alphanumeric, then to_number() will not work. Hence it will help if you can provide sample data values.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

IE if the output from the attribute view for this field is 18 characters values of:

000000000000000001

000000000000000012

000000000000000123

000000000000001234

000000000000012345

000000000000123456

000000000001234567

000000000012345678

000000000123456789

000000001234567890

000000012345678901

000000123456789012

000001234567890123

000012345678901234

000123456789012345

001234567890123456

012345678901234567

123456789012345678

I only want to export with a stored procedure script the following:

1

12

123

1234

12345

123456

1234567

12345678

123456789

1234567890

12345678901

123456789012

1234567890123

12345678901234

123456789012345

1234567890123456

12345678901234567

123456789012345678

This is the material number in SAP source system

[former_member184768 · Accepted solution]

May be I missed something else also, but why couldn't you use LTRIM() function ?

Regards,

Ravi

[Former Member · Accepted solution]

I think I can use the syntax LTRIM (str [, remove_set]) but my SQL isnt great and Im not sure how to do a simple SQL statement which gives me both lines.

EG Select "MATNR" FROM "SCHEMA"."MARA"

and create a new column where I can use the following variable:

MATERIAL = Select "MATNR" FROM "SCHEMA"."MARA"

LTRIM (:MATERIAL, '0') "ltrim" FROM "SCHEMA"."MARA" AS NEWCOLUMN

[Former Member · Accepted solution]

Yes I found that too. But now the issue is actually how to write it.

[former_member184768 · Accepted solution]

May be something like

Select "MATNR", LTRIM("MATNR", '0') as "NEWCOLUMN" FROM "SCHEMA"."MARA";

Also, if the Article contains ONLY number and not something like "00000A4327" or "000003456A", then you can also try to use to_number("MATNR") as "NEWCOLUMN" in the select statement.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi,

That syntax works well and I can use that to further manipulate the additional logic I need.

Kind regards,

Danielle

[former_member184969 · Accepted solution]

Hi all

Facing the same issue, we tried ltrim.

One thing we observed was VERY significant losses in terms of performance.

Is it something known.

Do you have any workaround ideas ?

Regards

[lbreddemann · Accepted solution]

I'd say this is what you will inherently end-up with for all kind of mass-data type-conversions.

That's especially true for potentially messy data types like strings.

Having said that, when dealing with the ABAP NUMC data type, it's typically sufficient to simply use the conversion to a number, instead of producing a VARCHAR that is stripped off from the leading zeros.

Statement 'SELECT salesorderid, to_bigint(salesorderid) SAL_ID_BIGINT FROM "SAP_HANA_DEMO"."HEADER"'

successfully executed in 328 ms 331 µs  (server processing time: 26 ms 684 µs)

successfully executed in 327 ms 829 µs  (server processing time: 26 ms 78 µs)

successfully executed in 329 ms 185 µs  (server processing time: 27 ms 505 µs)

Statement 'SELECT salesorderid, ltrim(salesorderid, '0') SAL_ID_VARCHAR FROM "SAP_HANA_DEMO"."HEADER"'

successfully executed in 339 ms 493 µs  (server processing time: 34 ms 93 µs)

successfully executed in 332 ms 526 µs  (server processing time: 30 ms 909 µs)

successfully executed in 333 ms 767 µs  (server processing time: 32 ms 93 µs)

Even this very quick test reveals, that the string operations are quite a lot slower.

Also, the result set will require less memory for the TO_BIGINT() version, as the numbers don't need to be represented as strings.

Since you report VERY significant losses it's also VERY likely that the conversion in your model is placed at a VERY unfortunate level and needs to be applied to a lot of data.

- Lars

[former_member184969 · Accepted solution]

You are right it needs to be applied to a lot of data .

We are applying it to Material Codes ( and as some of them are literal, we cannot just convert to numeric values). We happen to have a significant number of materials.

Fact is that we have to make a join with an external (i.e. non SAP) source , where materials don't have trailing 0s.

What we observe is that since we added ltrim, process time has been multiplied by 2 to 4...

[lbreddemann · Accepted solution]

Ok, so you have the requirement for a data conversion of those leading zeroes in order to mix it up with data from a different data domain (without leading zeroes).

And you have a performance requirement (it should be fast).

So why do you do the conversion over and over? You could e.g. put the data transformation into your data loading process.

Or you could add a calculated column that does the conversion when the data is inserted/updated.

As always you have to trade space for speed at some point.

- Lars

[former_member184969 · Accepted solution]

Aligned with this.
And that is basically what we are now planning to do, running a small unix script on the input file before integration. That's another way to leverage the power of the appliance 😉

Yet, a better solution in HANA would have been of some interest.( and there your proposal to add a column at integration seems more than relevant)

And I do think the last point raised by Krishan is also of some importance.

SAP is currently announcing that attribute and analytic views will go away. Yet is it always the best choice, to systematize dimension calculation views ?

[Former Member · Accepted solution]

In addition, i am puzzled why the left outer join stops working and it creates a cross join when we use LTRIM. Is it a product bug?

[lbreddemann · Accepted solution]

Open a new thread for that with a reproducible example please.

[lbreddemann · Accepted solution]

I'd recommend not to do this with a unix script.

For this little cleanup you introduce a lot of additional complexity into your solution and you can perform the type transformation easily in SAP HANA.

Also, SAP HANA assumes that it's the only main resource user on the system - putting on additional software, like your script, can lead to performance issues due to resource (CPU, memory) shortage.

Concerning the view types the general recommendation is in the developer guide - all model types are available for development and none of them are "marked for deprecation".

When you take the position that it's nicer to only have calculation views or if you develop with the WEB IDE exclusively then doing everything in calculation views is the way to go.

Since you can convert analytic and attribute views to calculation views I'd say you don't loose anything with either option.

- Lars

[Former Member · Accepted solution]

Hello Lars,

I have seen this behavior consistently by different consultants in my team.

Just by replacing attribute views with dimension calculation views, there is a significant performance degradation.

As a simple representation the code will be  like

Select

BOM.MATNR,

BOM.WERKS,

MARC.SOBSL,

MARC.BESKZ

FROM (SELECT MATNR,WERKS FROM CV_BOM_VIEW) AS BOM

LEFT OUTER JOIN

(SELECT MATNR, WERKS,SOBSL, BESKZ FROM AT_MARC_VIEW) AS MARC

ON

BOM.MATNR = MARC.MATNR

AND

BOM.WERKS = MARC.WERKS

When the same code is written with dimension calculation views, its just that AT_MARC_VIEW would be replaced by CV_MARC_VIEW.

Both CV_MARC_VIEW and AT_MARC_VIEW are just a direct projection of underlying MARC table.

Just by this simple change, I saw a performance degradation of atleast 2 times.

[Former Member]

Hello Experts,

We have a requirement to use data from various systems and even with flat files.

the problem is due to different data standards. eg. MATNR.

Its an 18 length field.

In some systems I values like

000000006400750200

6V90045100

Z24387

AIMS04-16-002

While in another the values will be

6400750200

6V90045100

Z24387

AIMS04-16-002

As can be seen i have problem if I join directly on material numbers. Thus I use LTRIM to remove all leading zeroes.

However, we are facing a lot of performance issue when using LTRIM in our scripted views.

Sometimes even the memory is crashing. After analyzing the plan we found that the records after using LTRIM is raising to billions of data consuming a high memory.
COuld you please look the below two views. One is created using attribute views having LTRIM in calculated column MATNR. And teh other without LTRIM using the same MATNR .

Eg As per the Plan the no pf records without LTRIM are 159718.

Now with LTRIM

The only difference is in the attribute views built on top of SLT layer.

First one, directly uses the matnr from source tables like (MARA, MARC,etc)

Second one, has a calculated field which is a LTRIM("MATNR",0)

Additionally I have another observation.

As of SP9, SAP recommends using Calculation views as dimensions instead of attribute views.

I even tried that and I am afraid to say the performance is even pathetic.

Code with Attribute views without LTRIM takes 50 seconds

Code with Attribute Views with LTRIM takes 2 minutes but with huge memory footprint.

Code with Calculation Views as dimensions runs for 6 minutes and then gives out of memory exception

I ll really appreciate your pointers as this is a high priority issue for us.

Regards

Krishan

[lbreddemann]

Ok, this actually doesn't seem like an inherent calc. engine issue, but rather a modelling issue.

As we don't know what your scripted view looks like it's hard to tell, where things go wrong.

However, you might want to look into using the conversion to a number (as mentioned here in the thread already).

Also, when speed is actually your priority, don't perform the conversion over and over. Instead handle this during data loading time or create a generated column.

All we can tell up to hear is that your current model seems to force a intermediate materialization on a huge amount of records. Pretty sure the 10^18 result set is due to a false join condition.

- Lars

[Former Member]

To the point of performance difference between LTRIM and non LTRIM attribute view

I do not agree it to be a modelling issue As the only change in the two models is the use of a calculated field as LTRIM(MATNR,0)

As a simple representation the code will be  like

Select

BOM.MATNR,

BOM.WERKS,

MARC.SOBSL,

MARC.BESKZ

FROM (SELECT MATNR,WERKS FROM CV_BOM_VIEW_WITHOUT_LTRIM) AS BOM

LEFT OUTER JOIN

(SELECT MATNR, WERKS,SOBSL, BESKZ FROM AT_MARC_VIEW_WITHOUT_LTRIM) AS MARC

ON

BOM.MATNR = MARC.MATNR

AND

BOM.WERKS = MARC.WERKS

When the same code is written with LTRIM, its just that AT_MARC_VIEW_WITHOUT_LTRIM would be replaced by AT_MARC_VIEW_WITH_LTRIM.

and CV_BOM_VIEW_WITHOUT_LTRIM is replaced by CV_BOM_VIEW_WITH_LTRIM

Both just have one added calculated as MATNR = LTRIM(MATNR_OLD,0)

[lbreddemann]

Sorry, but this is a modelling issue!

You're joining the result of two views - even of different type - within a SQL statement.

Basically this is mixing execution engines all over the place.

I made a few tests now and as long as I perform the conversion and the join in the same calc. view, I don't see any issue.

Maybe you want to look into using the EXECUTE IN SQL ENGINE setting for the calc view, too - in my test the calc view actually got executed in the Join Engine - which is the engine for attribute views.

- Lars

[Former Member]

Hi Krishan,

As SAP best practices of HANA model, We should avoid to use calculated column in Attribute views and use calculated column in calculation views.

I would suggest you use RTRIM in calculation views and put in EXECUTE IN SQL ENGINE then check performance.

and one more i have experienced in EXECUTE IN SQL ENGINE sometime might be through exception as "column store error" then put as blank it''s because of volume of data with more calculated columns.

[lbreddemann]

Shambhu Nath wrote:

and one more i have experienced in EXECUTE IN SQL ENGINE sometime might be through exception as "column store error" then put as blank it''s because of volume of data with more calculated columns.

Never saw anything like that. If you actually face such an issue, that would be a bug and should be handled via a support incident.

The 'execute in SQL engine' makes a structural difference in how the query is going to be processed not a quantitative. The amount of data really doesn't change a thing in this regard.

- Lars