Tableau – How to create a Data Source from the clipboard (Excel)

Share Button

Instead of connecting to Excel, for a quick test, just copy and paste

Share Button
Share Button

  1. Suppose you have a table in Excel and want to show it in a visualization in Tableau
Small table in Excel

Go ahead and copy the data (select the data with left-click mouse hold or with the keyboard) and copy

copy the data from excel (CTRL-C)

3. In a Tableau desktop session, select the Sheet and do CTRL-V (paste). Depending on the pasted data size, it will show a “Processing Request” pop-up window for a while

Paste in Tableau Sheet 1

4. Now the data is showing in Tableau (voilá!)

Same table in Tableau

5. If you click on the Data Source (lower left corner), you see the Clipboard_<> Data Source

Tableau Data Source from c

In this link in the Tableau online help, you have more details about it

Create a Data Source or Add a New Connection with Clipboard Data

Share Button

How to check the user that created a HANA Calculation View (_SYS_REPO.OBJECT_HISTORY)

Share Button

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.

Share Button

HANA SQL function IFNULL used to get currency code number of decimals from TCURC and TCURX

Share Button

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"

TCURC TCURX
TCURC and TCURX

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

Share Button

Methods to get DTP information in Start or End Routine

Share Button

In order to get for example the request ID during the start routine execution, you can enter the following code

data: lv_requid type rsbkrequid.
lv_requid = p_r_request->get_requid().

This will return the numeric request, there is also a similar one to get the 30 characters request id. Debugging you can find that the exact method executed is IF_RSBK_REQUEST_ADMINTAB_VIEW~GET_REQUID (CL_RSBK_REQUEST_PROXY). And by looking at the list of methods in class CL_RSBK_REQUEST_PROXY, you have a good list of methods and you can also find about the parameters data definitions.
Here is a complete list of those methods (I removed the prefix IF_RSBK_REQUEST_ADMINTAB_VIEW~)

GET_REQ_PROCESSMODE
GET_ONLY_CHECK_SIDS
GET_DTP
GET_DTPTEXT
GET_LINES_READ
GET_LINES_TRANSFERRED
GET_REQUID
GET_REQUID30
GET_TH_RANGE
GET_TSTATE
GET_TSTMP_FINISH
GET_TSTMP_START
GET_UNAME
GET_UPDMODE
GET_USTATE
GET_SRC
GET_SRCTP
GET_TGT
GET_TGTTP
GET_LOGSYS
GET_DATASOURCE
GET_T_PSA
GET_USE_NEW_DS
GET_T_ISOURCE
GET_ERRORCOUNT
GET_PREVIOUS_RECORDS
GET_ANALYSED
Share Button

Cannot preview ECC tables in HANA Calculation views

Share Button

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.

User Session Client
User Session Client

Share Button

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

Share Button

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.

Share Button

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

Share Button

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”

Maximum Number of Rows
Maximum Number of Rows

Share Button

Virtual Hierarchies for Time Characteristics in SAP BI

Share Button

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”.Path in SPRO

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.

TR RSRHIERARCHYVIRT

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.

Hierarchy selection for a BEX query in the query designer

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

 

 

 

 

 

Time hierarchy during query execution

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

 

 

 

Share Button

Why some transformations do not show up when selected in Transport Connection

Share Button

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”.

Source System Assignment ShiftF7
Source System Assignment ShiftF7

Share Button

How to find a Standard InfoObject to map to an ECC field in BW

Share Button

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 🙂

Share Button