KB483613: MultiSource in MicroStratetgy with SAP HANA as one of the databases

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Strategy provides an extension to Intelligence Server referred to as MultiSource Option. With the MultiSource Option feature, you can connect a project to multiple relational data sources. This lets you integrate all the information from various databases and other relational data sources into a single Strategy project for reporting and analysis purposes. All data sources included by using the MultiSource Option are integrated as part of the same relational schema for a project. 
 
Accessing multiple relational data sources in a single project can provide many benefits and reporting solutions. There is the obvious benefit of being able to integrate information from various data sources into a single project. Along with accessing data in data sources provided from a centralized server, you can also access personal relational data sources. 

Example: Adding tables from different databases to one project.

In the example below one of the tables from SAP HANA in the in the project was replaced with the same table stored in MySLQ database. This table has a different color in Architect. It is possible to connect two tables from different Databases by creating an attribute mapped to the column in both tables. 
 

1. Add tables to the Warehouse catalog. 

1. Create an attribute and map it to the column in both tables. 

1. Create a report. 

A fragment of the SQL Statements for one of the reports: 
You can see that there are many passes and every time it is written to which database the query is send. 
  
Pass 3 - [my_MySQL] 

select    a01.EMPLOYEE_ID  EMPLOYEE_ID,     a01.EMPLOYEE_DESC  EMPLOYEE_DESC from    `LU_EMPLOYEE`    a01 

CREATE LOCAL TEMPORARY TABLE #TS9BJ10XJOP001(     EMPLOYEE_ID    INTEGER,  

    EMPLOYEE_DESC    VARCHAR(300)) NO LOGGING

INSERT INTO #TS9BJ10XJOP001 VALUES (1, 'Piers Allan')

Example: Creating a Dossier with data from SAP HANA and MYSQL

It is possible not only to connect two datasets stored in memory but also Live Connect cubes. The picture below represents two cubes created with the Live Connect option, linked and displayed on a visualization. 
 

1. Create a Live Connect cube with SAP HANA data. 

1. Create a Live Connect cube with MySQL data. 

1. Link two data sets. 

1. Create a visualization. 

Example: Create a project in which data is taken from SAP HANA but temporary tables are created in another database.

In the following example we will use MySQL database. To do so: 

1. Set MySQL as a default and primary database. 

1. Change the CREATE and INSERT support VLDB setting in SAP HANA instance to Not Supported 

 
As you can see below the data is retrieved from SAP HANA, but all temporary tables are created and updated in MySQL.  
 
Pass 1 - [SAP HANA 2]  

select    a01.DAY_ID  TRAN_DATE from    "DAY"    a01 where    a01.DAY_ID between TO_DATE('2016-11-03','YYYY-MM-DD') and TO_DATE('2016-11-10','YYYY-MM-DD') 

CREATE TEMPORARY TABLE T2IJUN9YZOP000( 

    TRAN_DATE    DATE)

insert into T2IJUN9YZOP000 values ('2016-11-03')

select    a11.TRAN_DATE  TRAN_DATE,     a11.TRAN_DATE  TRAN_DATE_EST from    T2IJUN9YZOP000    a11 

where    a11.TRAN_DATE between '2016-11-03' and '2016-11-10'

[Populate Report Data]

drop table T2IJUN9YZOP000

• Connecting data sources to a project. 
• Adding data into a project. 
• You can create logical views on data sources other than the data source for the primary database instance. This technique along with steps to create logical views are described in Creating logical views. 
• You can support fact tables that are partitioned across multiple databases. Using metadata partitioning is described in Metadata partition mapping. 
• You can include MDX cube data in regular reports, which lets you include MDX cube data along with data from your relational project as well as MDX cube data from other MDX cubes. For examples of this capability and steps to create these types of reports, refer to the MDX Cube Reporting Guide. 

You may refer to: 
 https://www2.Strategy.com/producthelp/Current/ProjectDesignGuide/WebHelp/Lang_1033/Content/ProjectDesign/Accessing_multiple_data_sources_in_a_project.htm 
 
There are some recommendations for physical modeling of attribute data in projects using the multisource option in Strategy.  

1. Attribute ID forms must be mapped into all databases where the attribute is relevant. 

1. Attribute element IDs must be consistent across databases. 

1. Lookup tables in different databases for the same attribute should have the same set of attribute elements. 

1. If lookup tables share the same name across databases, their table structures should match. 

You may refer to:  
https://community.Strategy.com/s/article/KB30336-Recommendations-for-physical-modeling-of-attribute-data?language=en_US