KB483771: How to create and use datamarts in SAP HANA using MicroStrategy 2019

What is Datamart?

A datamart is a mini data warehouse, or a subset of data derived from a primary data warehouse. Datamarts are typically stored in the same database server as the warehouse. Each datamart table is associated with one report via the Strategy Developer interface. Datamarts can be used to achieve various business needs, such as:

• Creating tables in a database for running "What-If" scenarios.
• Building tables for third-party tools.
• Building a smaller, portable database for Online Analytical Processing (OLAP) analysis.

How to Create a Datamart Database Instance

A datamart can either be stored in a warehouse or in an alternate database server. Users using the warehouse may skip to the How to Add a Datamart to a Report section of this article as the warehouse is optimized for a datamart by default. 

• Create a valid Open Database Connectivity (ODBC) DSN pointing to the SAP HANA datamart location.
• Create a SAP HANA database instance that references the intended datamart location by performing the following steps:
  
1. In Developer, in the top navigation, select Administration > Database Instance Wizard.
2. Click Next.
3. On the Database Instance Definition page, enter a name for the database instance and select SAP HANA 1.x or SAP HANA 2.x as the database type.
4. Click Next.
5. Choose the SAP HANA datamart DSN and enter your login information.
6. Click Finish.
• Validate the database instance for the datamart by performing the following steps:
  
• Right-click on your project and select Project Configuration. 
• In the Database Instance tab, select the checkbox of your newly created database instance.
  

• Click Yes on the warning message.
  

•  Click the Advanced tab on the Database Instances dialog.
  

• If the datamart is in the same database server as the warehouse or warehouse instance, select the Data mart optimization checkbox. If the datamart is in a different location from the warehouse, do not select this box.
• Click OK.

How to Add a Datamart to a Report

• Open a report in edit mode.
• In the toolbar, choose Data > Configure Data Mart. 
• In the General tab of the Report Data Mart Setup dialog, use the drop-down to select the datamart database instance.
  

• Under Table name, choose a table name. This table name must be compliant with the naming conventions supported by SAP HANA.
• Select one of the following options:
  
• Create a new table: This replaces the existing table each time the report is run.
• Append to an existing table: This adds to the resulting data from the report's previous run.
• Click OK.
• Click Save and Close.
• Run the report. You will receive a confirmation message indicating the datamart was successfully created.

For information on how to create and use a datamart in the Strategy Intelligence Server, see KB5395.