Category: Tips And Tricks

In case you need to find out who did a last change or created an object such as a calculation view, you can find out in table “_SYS_REPO”.”OBJECT_HISTORY”:

1
2
3

SELECT * FROM "_SYS_REPO"."OBJECT_HISTORY" WHERE 
package_id LIKE '%myPackage%' AND
object_name LIKE '%myCV%'

The column OBJECT_SUFFIX has ‘calculationview’ for Calculation Views (surprise!), there is VERSION_ID column and then ACTIVATED_AT has the timestamp and ACTIVATED_BY the user that created or modified the object. The discussion topic Creator/Owner of Attribute/Analytical/Calculation views mentions that table.

The function IFNULL Function (Miscellaneous) in HANA allows to replace a null result with the second parameter of the function.
Table TCURC has the different currency codes like AUD, USD, JPY and the second table TCURX has the number of decimal places. This second table is an “exception” table, i.e., only currency codes that do not have 2 decimal places are in this table.

This poses a problem: values stored in transactional tables in JPY (Yen) are “divided” by 100: as the number of decimals is zero, the system stores the value as if divided by 100. So, out of SAP ECC they need to be multiplied by 10 to the power of (2 minus number of decimals). But as TCURX is an “exception” table, before doing this in a table function or other way, we may need to make sure that for every value in TCURC we have a value in TCURX:

1
2
3
4

SELECT A."WAERS", IFNULL(B."CURRDEC",'2') AS "CURRDEC" 
  FROM "myschema"."TCURC" AS A 
  LEFT OUTER JOIN "myschema"."TCURX" AS B
  ON A."WAERS" = B."CURRKEY"

The result now has all currency codes even when they are not in table TCURX.

If you cannot preview data in Calculation or Analytical views based on ECC tables that have a field for client (MANDT for example), make sure your user “Session Client” is properly set as in the image below.

How to add a filter for “yesterday” on a DATS field coming from BW in a HANA Calculation View

I searched several SCN threads, for example this one Need help to figure out simple date filter in calculation view but they did not help me. Some recommend to create a calculated column that is in SQL Date format and then apply a filter. This forces the conversion of all records prior to applying the filter so it can affect performance. Others recommend a join with M_TIME_DIMENSION, so I thought it would be helpful to show the filter I applied and learn from your comments about the pros and cons of this approach.

I just need to select from a table (a fact table in this case) all records with a date field with the date corresponding to yesterday. As the field for the calendar day in the fact table is SID_0CALDAY, an integer, I do an integer conversion to the expression, but if what you have is a VARCHAR as in any converted DATS, exclude the starting INT( as well as the last parenthesis on the expression.

What worked for me was:

1
2
3

INT(string(component(adddays(now(), -1),1)) +
rightstr('00' + string(component(adddays(now(), -1),2)),2) +
rightstr('00' + string(component(adddays(now(), -1),3)),2))

The ‘component function returns the year, month and date if you pass 1, 2 or 3 as second parameter, and the rightstr function is to pad zeros in months or days between 1 and 9.

How to change the maximum number of rows in SQL preview in HANA Studio

In HANA Studio (or eclipse) select the menu option Window -> Preferences and scroll on the list on the left pane to find ‘SAP HANA‘ and expand it to runtime → Result and click on it. Adjust the parameter Maximum number of Rows Displayed in Result, it is set to 1000 rows by default, as per SCN post “HANA is loading only 1000 records into my attribute table”

If you need to do “bucketing” i.e. to show data by quarters or years, based on different base time characteristics, for example, creation date and posting date, you could consider using time hierarchies, and there are virtual ones already available for that purpose. Other alternatives include some virtual characteristics, or some solution with a join between a reporting object and a view in a composite provider, but this is one that is an easy implementation and may solve some simple requirements.

To read about virtual time hierarchies on the online help, click on “Activating Virtual Time Hierarchies”

The virtual time hierarchy gets activated in the IMG, transaction SPRO → SAP Reference IMG → SAP Netweaver → Business Warehouse → Settings for Reporting and Analysis → General Settings for Reporting and Analysis → Set F4 Help and Hierarchies for Time Characteristics / OLAP Settings. Or you can execute transaction RSRHIERARCHYVIRT directly.Once there, click on the tab “Virtual Time Characteristics”.

In the image below you can see the hierarchies for Calendar year / month (0CALMONTH). They show as green because they are already activated. To activate, double-click on a single one, change the descriptions if required and press on “Save”. You will not be able to see these hierarchies in RSH1 as they are virtual but they are available to use in the BEX Query Designer.

For example, if you activated hierarchy 0HYEA1_QUA_MON (this hierarchy shows the first or second half of the year as first level, then the quarter/year and finally the month, you can use it for calendar month in a query by selecting the presentation hierarchy as in the image below.

And the query execution will show the data in buckets as in the image below.

I still need to figure out how this setting is transported…

When you are in the transport connection and select via filter some transformations, some or all of them do not appear in the selection result.

If that occurs to you, check the button “Source System Assignment” as in the image below and make sure you have selected all systems for which you are selecting transformations. This info is contained in table RSOPROPERTIESTAB, enter your user and as property enter “PROP_SOURCE_SYSTEM_SEL”.

One easy way to find which standard delivered InfoObject to use in a transformation for a corresponding field in ECC is to look at table RSTRFIELDSH, Shadow table: Transfer Structure Fields.

Let’s say you have a field in a DataSource from ECC named BWART. Just run SE16 for table RSTRFIELDSH and enter BWART in FIELDNM –> it will return 2 possible entries in field IOBJNM, either 0OI_BWART or 0MOVETYPE.

Make sure you search in a BW system, not in an ECC system 🙂