Show TOC

Database Users and Database SchemasLocate this document in the navigation structure

Use

When a database user is created in a database management system (DBMS), the system generates a database schema of the same name. This type of database schema is a collection of database objects where tables and views are managed. The schema and the objects belong to the user. You can assign read-write authorizations to other users for the schema, tables and views.

If you want to use DB Connect to establish a connection to a database source system, you need to create a user name and password in the DBMS. In the following example, this user is referred to as the BW user. You use the BW user to work in the database schema that has been created with the name of the BW user. The tables and views containing the application data are stored in the DBMS, usually in an applications schema. Make sure that the BW user has read access to the tables and views in the application schema that are going to be transferred into the BW system. The BW user can only extract the data extraction and preview the data from the DataSource maintenance if he or she has read permission.

Data Flow with Transformation

To extract data from a DBMS, you only need one BW user and thus only one source system connection to this DBMS. When defining the DataSource, you can limit the selection of the source data by specifying a database user. If you specify a database user (application) on tab page Extraction in the DataSource maintenance, those tables and views that belong to the specified database user and that lie in the schema of this database user are displayed for selection.

The tables and views that belong to the database user but that lie in a schema of a different database schema than the one specified are also displayed. The database user cannot extract these tables and views. In this case you can gain access to the data in the application schema using a view.

Example

In some databases there might be schemas that do not correspond to any database user. If you would like to extract from a table of such a schema, you can give the BW user read permission for the table in this schema and create a view on the table in the schema of the BW user. You then define the DataSource fort he view in the schema of the BW user.

Further applications for views are described in the section below under points 3 and 4.

Data Flow with 3.x Objects

BW users who use a DataSource 3.x need permission to create views in their schema. You need these views in the schema of BW users in order to access tables and views in the application schema.

Using views, you can answer administration and authorization queries centrally in the source system.

  1. To extract data from a DBMS, you only need one BW user and his or her schema, and thus only one source system connection to this DBMS. You use views in the schema of the BW user to access data that you want to extract and that is stored in other schemas.

    Note

    If there are no views on data of the application schema in the schema of the BW user, you need an additional source system connection for which the database user is the BW user or connection user.

  2. You are able to access tables with the same technical name by creating views with different names for these tables in the BW user's schema. In this way you can generate different DataSources for tables with the same name.

    If the tables contain similar semantic content, you can control the authorizations for the database user in such a way that he or she can only access the relevant tables.

  3. You can structure the views in such a way that you are able to control access rights to the tables and restrict or reformat data as well as carry out join operations across several tables. Using views also makes it easier to localize errors.

    Recommendation

    We recommend that if you need to perform conversions, you perform as many as possible in the view. This allows you to identify any errors or problems that arise at source-system level and you can resolve them as quickly as possible.

    Recommendation

    You use conversion statements to

    • convert the names of database tables into capital letters

    • convert dates from the internal date format used in the database to the SAP date format YYYYMMDD

  4. By using views as an interface between a physical table and the BW system, you are able to use corresponding conversion statements in the view to make changes to the tables in the application schema, without this having an effect on the view itself.