Schema Mapping using Multi Database Containers and Cross Database

Access

In general – what is Schema Mapping

Schema mapping is essential when moving SAP HANA objects from one system to another system, say
from development system to test system, or test system to production system and the names of the
physical schemas differ. Before looking at an example let’s first clarify the different terms:

Physical schema: Is the database schema which physically groups your tables, views etc. Such schemas
are often named like your application system e.g. SAPDEV, SAPPRO etc.

Authoring schema: Is the schema which is used at design time and from which tables or views are
selected as data sources when modelling views e.g. graphical calculation views.

Schema mapping information is stored in configuration table _SYS_BI.M_SCHEMA_MAPPING resp.

Physical and authoring schema are identical if you don’t specify anything in the schema mapping
dialog.

Example

Figure 1: Overview schema mapping

On the left-hand side of Figure 1, no schema mapping is defined i.e. physical and authoring schema are
identical. The repository content can only be used in other HANA systems which have the same
physical schema.

On the right-hand side, schema mapping is defined. The repository content is useable in all HANA
systems even if the physical schema is different as long as the schema mapping ensures the
correspondence. In the target system, HANA maps the tables in the repository view to the correct
tables in the database catalog based on the schema mapping information. It is also possible to map
several authoring schemas to a single physical schema.

Schema Mapping using Multi Database Containers

A SAP HANA system installed in multiple container mode can contain more than one tenant
database. A tenant is an isolated database but it is possible to have access to data sources in other
tenants. This means that, when modeling graphical calculation views, you can add data sources from
any of the isolated databases. This mechanism is called cross database access.

Please note that this document only describes the settings for schema mapping. For settings to
enable cross database access and more information on multi database containers please refer to the
available documentation on SAP Help.

For cross database access introduced with SAP HANA SPS09, schema mapping was enhanced to also
consider the tenant name as an additional column (table
_SYS_BI.M_DATABASE_SCHEMA_MAPPING). The new columns in the schema mapping dialog are:

Physical DB: Name of the tenant in which the physical schema is located. Please note that the
physical DB name cannot be the same as the local database name.

Authoring DB: Name of the tenant which is used at design time

In the following, you find examples how to set up schema mapping for different scenarios between a
source and target system when cross database access is used. In the example an ERP and a BW
system is used. However, it should be easy to apply the underlying principles to other scenarios.

Scenario A: Source Multi DB system and target Multi DB system

In the first scenario, you build graphical calculation views in a multi database container system using
cross database access. A graphical calculation view created in the ERP tenant is used as data source
in a graphical calculation view in your BW tenant and accessed via cross database access (see Figure
2). In detail the scenario looks like this:
Figure 2: Overview system landscape with schema mapping development system

As you can see in figure 2, an authoring schema is used in both tenants and it is mapped to the
corresponding physical schema: physical schema SAPDEV mapped to authoring schema DEV_ERP in
the ERP tenant (see figure 3) resp. physical schema SAPBWD mapped to authoring schema DEV_BW
in the BW tenant (see figure 4). This schema mapping might be present in the development system
for historical reasons. For example, the views might have been developed while tables from schema
DEV_ERP have been used while now tables from schema SAPDEV should be consumed.

Figure 3: Schema mapping maintained in ERP tenant in development system

After finishing the development phase the repository objects should be transferred to the test
system. The landscape in the test system is identical to your development landscape i.e. there is a
tenant for ERP and another tenant for BW. However, the physical schemas in the test system are
different from the physical schemas in the development system. Therefore, schema mapping needs
to be set up also in your test system.

Figure 5: Overview system landscape and schema mapping test landscape

In the ERP tenant, schema mapping is set up in the classical way (for local objects only), meaning you
map the authoring schema used in the development system to the correct physical schema in the
test system. In this scenario, the authoring schema DEV_ERP is mapped to the physical schema
SAPQUA in the ERP tenant (see Figure 6).
Figure 6: Schema mapping maintained in ERP tenant in test system

The next step is to set up schema mapping in the BW tenant which uses cross database access to the
ERP tenant. Here, three entries must be maintained in the schema mapping dialog: an entry for
schema mapping of objects that are local to the BW tenant and two additional entries for remote
objects related to cross database access (see figure 7).

Figure 7: Schema mapping maintained in BW tenant in test system

The first entry is required to map the authoring schema used in the BW tenant of the development
system to the correct physical schema in the BW tenant of the test system: i.e., the authoring
schema DEV_BW is mapped to the physical schema SAPBWQ in the BW tenant.

The second entry is required to map the authoring database and schema of objects in the remote
system of the development landscape to the corresponding physical database and schema in the test
landscape. In our example, the authoring schema DEV_ERP used in the remote tenant DV1 of the
development system is mapped to the physical schema SAPQUA in the remote tenant QV1 of the test
system (see figure 3 and figure 7).

The third entry is necessary since in addition to tables also graphical calculation views are used as
data source. Runtime objects for the repository views are created and stored in the technical schema
_SYS_BIC (or in another schema in case you don’t use the default values). To use the graphical
calculation view as data source a mapping is required for schema _SYS_BIC similarly to any other
database schema. In the example, the authoring database DV1 is mapped to the physical database
QV1. As the runtime objects are stored in the same schema _SYS_BIC in both systems, it is added as
authoring schema and physical schema.

Please note: In the above example, no authoring DB name is defined in the BW Source System.
Therefore, the system name DV1 is used as default authoring DB name. In case you want to use a
generic name you can set up the schema mapping as in the following example. In the development
system of your BW tenant you define the generic name ERP as authoring DB name. Also the
authoring schema name DEV_ERP and the corresponding physical DB name and schema is defined as
show in figure 8.

Figure 8: Schema mapping using a generic authoring DB name in development system

In the target BW system you can now make use of the generic name. Instead of using the SID of the
development system you can use the generic name in the schema mapping setting like shown in
figure 9.

Figure 9: Schema mapping using a generic authoring DB name in quality system

Scenario B: Source Multi DB system and target Single DB system

In this scenario (see Figure 10), only the development system is set up as a multi database container
system whereas the test system is set up as a single DB system. So, the development system is the
same as in scenario A which means that the schema mapping is the same as shown in figure 3 and
figure 4. However, in the target single DB system, a different setup is required.
Figure 10: Overview System Landscape with schema mapping

In the single DB system used for testing, the authoring and physical schema need to be set using the
schema mapping dialog. Please note that the maintenance dialog does not show the columns
'Authoring DB Name' and 'Physical DB Name' as it is a single DB system. In this example, you map the
authoring schemas defined in the tenants of the development system to the physical schemas used
in the single DB system as shown in figure 11.

Figure 11: Example for settings in Single DB

Using the settings above, content without cross database access would work fine. However, when
using cross database access, also the tenant name is used as a qualifier in the data source name. To
transfer the graphical calculation view including objects that leverage cross database access, an entry
is necessary in table SYS_BI.M_DATABASE_SCHEMA_MAPPING for field "AUTHORING_DB".

In our example, the authoring database is filled with the tenant name in which the data source is
built originally. Authoring and physical schema must be filled with the schema in which the runtime
objects are stored (Please note that the physical database must remain empty since the test system
is a single DB system)

You cannot set the required settings via a maintenance dialog because you cannot fill the field
"AUTHORING_DB" in the dialog. Instead, use an insert statement as shown below to create the
mapping.
insert into "_SYS_BI"."M_DATABASE_SCHEMA_MAPPING" values ('DV1','_SYS_BIC', '',
'_SYS_BIC')

Figure 12: Insert and Select statement example for setting in SingleDB system