KB483783: Utilize the SAP HANA Text Join feature in MicroStrategy

Text Join Feature in SAP HANA

SAP HANA now offers a Text Join feature. This type of join is useful when a database or BI software, like Strategy, are required to provide data and analytics in more than one language.
Text Join is used to get language-specific data. It behaves like a 1:1 cardinality left outer join, but you specify a Language Column in its definition. This is often called SPRAS in SAP systems. The Language Column in the definition is used to return session specific data. Depending on the LOCALE and LOCALE_SAP parameters of a session, only data in the relevant language is returned.
Strategy allows for tight integration with the Text Join feature on any of the following levels:

• Connection Mapping and Database Connection Object
• DSN-Less Level Configuration
• DSN Level Configuration

Creating a Dummy Calculation View in SAP HANA

SAP HANA Calculation view is a powerful information view. To create one for Text Join, use the steps below.

• Create two tables, TEXT_JOIN_PRODUCT and TEXT_JOIN_TEXT.
• Apply Text Join on the PRODUCT_ID attribute.
• Specify the LANG column as Language Column for the Text Join.
  

• The TEXT_JOIN_PRODUCT table appears as:
  

• The TEXT_JOIN_TEXT table appears as:
  

Using Text Join in Strategy via Connection Mapping

The best way to utilize Text Join capabilities in Strategy is through a feature called Connection Mapping. Use the following steps to enable Connection Mapping.
Note: You must be an administrator to perform the following steps.

• Create a database connection object for each user or user group with a different LOCALE and LOCALE_SAP field specified.
  

• The database connection object will overwrite the default values for the LOCALE and LOCALE_SAP
  parameters in order for the Text Join feature to return data corresponding to the Polish language.
  
• In Developer, right-click your project and select Project Configuration.
• Go to Database instances > Connection mapping.
• Define the connection mapping for your  project in order for specific users or user groups to use the database connection object you just made.
  

• In this example, you can see pl_user uses the TEXT_JOIN_PL_DBC database connection object. That database connection object has LOCALE and LOCALE_SAP set to pl and L (as seen in step 2).
  
  The en_user uses the TEXT_JOIN_EN_DBC database connection object. That database connection object has LOCALE and LOCALE_SAP set to the default values, which is English. 
  
• In Strategy Web, the pl_user will receive different data from the same live connected cube than the en_user.
• This is confirmed if you run the following SQL query on tables used in the Text Join Calculation View:
  select * from text_join_product a
  left outer join text_join_text b on a.product_id=b.product_id
  

Determining the Language Value

To determine which values for LOCALE and LOCALE_SAP correspond to which language, use the following SQL statement to check your values for a current session.
Note: Single quotes and uppercase letters must be used.

SELECT SESSION_CONTEXT(‘LOCALE’) FROM DUMMY;SELECT SESSION_CONTEXT(‘LOCALE_SAP’) FROM DUMMY; 

Using Text Join in Strategy via DSN-Less Level Configuration

You can use a DSN-less connection in Strategy Web to utilize Text Join in SAP HANA. To use a DSN-less connection, use the following video:
 

Using Text Join in Strategy via DSN Level Configuration 

You can also use a DSN connection to utilize Text Join in SAP HANA. To use this configuration, you must add the following parameters to the odbc.ini configuration file to specify a database connection.
Note: The follow parameter sets the language to Polish.

LOCALE=pl;LOCALE_SAP=L; 

Determining the Language Value

To determine which values for LOCALE and LOCALE_SAP correspond to which language, use the following SQL statement to check your values for a current session.
Note: Single quotes and uppercase letters must be used.

SELECT SESSION_CONTEXT(‘LOCALE’) FROM DUMMY;SELECT SESSION_CONTEXT(‘LOCALE_SAP’) FROM DUMMY;