Database Users and Database Schemas
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 BI user. You use the BI user to work in the database schema that was created with the name of the BI user. The tables and views containing the application data are stored in the DBMS, usually in an applications schema. Make sure that the BI user has read access to the tables and views in the application schema that are transferred into the BI system. The BI 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 BI 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.
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 BI user read permission for the table in this schema and create a view on the table in the schema of the BI user. You then define the DataSource fort he view in the schema of the BI user.
Further applications for views are described in the section below under points 3 and 4.
Data Flow with 3.x Objects
BI users who use a DataSource 3.x need permission to create views in their schema. You need these views in the schema of BI 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 BI user and his or her schema, and thus only one source system connection to this DBMS. You use views in the BI user's schema to access the data that you want to extract that is stored in other schemas.
If there are no views on data of the application schema in the schema of the BI user, you need an additional source system connection for which the database user is the BI user or connection user.
2. You can access tables with the same technical name by creating views with different names for these tables in the BI 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.
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.
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 BI system, you can use corresponding conversion statements in the view to make changes to the tables in the application schema, without affecting the view itself.