Open SQL Schema

Objective

After completing this lesson, you will be able to get an insight into the database user creation and related Open SQL schema on SAP HANA Cloud. Populate the created table with product category text information and re-use the table in the SAP Datasphere modeling tools.

Contents

  • Create new Database User
  • Log in with Database User
  • Create Table and Import Data
  • View Table with Data Builder
  • Optional - Enhance the Data Model


In this lesson, we integrate data via database users and their Open SQL Schemas.

We create new database users in the SAP Datasphere spaces to write directly to the SAP HANA Cloud database on which SAP Datasphere runs. Each database user has an Open SQL schema, which is attached to a SAP Datasphere space schema.

With the SAP HANA Database Explorer, we create a table in the Open SQL Schema and insert a product category description in the new table. For that, we upload the data from a CSV file or alternatively, prompt a SQL insert command to populate the table. Afterwards, we reuse the data inside our SAP Datasphere modeling tool.

This shows, how we could use SQL-based tools, like SAP HANA Database Explorer or 3rd party tools, to load data into Open SQL schemas for modeling efforts in SAP Datasphere.

Review the Help Portal for more information at Integrating Data via Database Users/Open SQL Schemas.



Create New Database User

  1. Select Space Management in the left side navigation menu.

  2. View the list of spaces your user has access to. Your assigned space usually corresponds to your user name .

  3. Select the Edit button on your assigned space .

  4. You are now presented with the properties of your space and are able to configure certain options. Verify in the General Settings that your Space ID is shown.

We now create a new database user and an associated schema in SAP Datasphere.

  1. Navigate to the Database Users section of your space and choose Create.

In the Create Database User window popup, enter the name suffix and select the the Read/Write options to the space schema. This allows you to connect external tools to access the Open SQL schema and process data.

  1. Enter OPENSCHEMA as the Database User Name Suffix.

  2. Select the Enable Read Access (SQL) checkbox.

  3. Select the Enable Write Access (SQL, DDL & DML) checkbox.

  4. Press the Create button. A notification popup about the user creation appears.

  1. Select the Deploy option in the header, to activate these changes to your space .

    Wait until the notification popup confirms that the deployment was successful !

  2. After the successful deployment, select the information icon "i" at the right end of the database user line.

    Note the system-generated Host Name and Port for future use -- this is the database host name to connect from 3rd party tools. We will not use it further in this exercise.

  1. Choose Request New Password.

    We want to generate a new password for our database user.

  2. Choose Show Password icon.

  3. Choose Copy Password, and save your password as it will not be shown again. You will need this password in subsequent steps!

    If you have forgotten the password, request a new password again.

  4. Close the Database User Details window.



Log in with Database User

The SAP HANA Database Explorer is SAP Datasphere's built-in SQL tool that provides access to the underlying SAP HANA database. Here, we will be using the Database Explorer to interact with the new created Open SQL Schema. You could also use 3rd party DB management tools, like DBeaver, to interact with this schema. The data added to this schema is then available for modeling in SAP Datasphere Data Builder.

  1. Select the check box for your newly created user #OPENSCHEMA, and choose Open Database Explorer.

The Business Technology Platform identify provider window popup appears. We continue with the alternative option, to logon to SAP HANA Database Explorer.

  1. Enter and select Sign in with alternative identity provider.

Note

: In case an

To add your schema to the list of databases and adapt the database instance name shown in the SAP HANA Database Explorer, enter your password.

  1. Enter the previously copied password.

  2. Select the Save password checkbox.

  3. In the Display Name field, enter #OPENSCHEMA

  4. Choose OK.

  5. The SAP HANA Database Explorer tool opens.

    Review the Help Portal for more information at Getting Started with SAP HANA Database Explorer.

  1. Let’s take the first steps and verify the schema name and the database user.

    • A - Expand the Catalog browser, listing the available object types beneath.
    • B - Choose the Schemas object type, to view the schema(s) in the object list section below.
    • C - Right-click the entry #OPENSCHEMA in the object list.
    • D - Choose the Open action.
    • E - In the right pane, the schema details are displayed, showing the schema owner, the previously created database user.
    • F - Close the schema details tab in the right pane again.



Create Table and Import Data

Let’s create a table under this schema and insert some data.

  1. Right-click on your database instance #OPENSCHEMA and select Open SQL Console.

    The SQL console opens in the right pane. Alternatively, choose the SQL icon on the top left.

  2. Copy the following SQL statement:

    Code Snippet
    1234567891011
      CREATE COLUMN TABLE "PRODUCTCATEGORYTEXTS" (
        "PRODCATEGORYID" NVARCHAR(2) NOT NULL ,
        "LANGUAGE" NVARCHAR(2) NOT NULL ,
        "SHORT_DESCR" NVARCHAR(20),
        "MEDIUM_DESCR" NVARCHAR(40),
        "LONG_DESCR" NVARCHAR(10),
        PRIMARY KEY ("PRODCATEGORYID", "LANGUAGE")
  )
  UNLOAD PRIORITY 5 AUTO MERGE;

  3. Apply following steps to import the data from a CSV file:

    • A - Paste the SQL statement into the SQL console.
    • B - Check the SQL script for accuracy, and choose that Run icon.
    • C - Verify the creation statement in the Messages pane underneath the SQL console.
    • D - In the Catalog browser on the left, select the Tables object type.
    • E - Verify that the PRODUCTCATEGORYTEXTS table appears in the object list.

    This table can now be accessed from the SAP Datasphere modeling and other 3rd party tools.

In the following steps, we insert the data to the new PRODUCTCATEGORYTEXTS table. We use the import wizard to upload a CSV file with the product category information. Alternatively, the data records can be inserted as SQL statement to the table. The latter is described after the data import guidance.

  1. Download the PRODUCTCATEGORYTEXTS.CSV file and save it locally.

  2. In the Tables object list, right-click on the PRODUCTCATEGORYTEXTS table and select Import Data.

An Import Data wizard opens and guides you through the import steps:

  1. Select Import Data and choose Step 2 to continue.

  2. Choose Browse and select the PRODUCTCATEGORYTEXTS.csv file from your download location.

  3. Choose Step 3 to continue.

  4. The option Add to an existing table, the schema, and the table name are prefilled.

    If not, select the option and enter the values:

    • Schema: #OPENSCHEMA
    • Table: PRODUCTCATEGORYTEXTS

  5. Choose Step 4 to continue.

  1. Verify that the Source Column Names and the Database Column Names have been matched up.

  2. Choose Step 5 to continue.

  3. Select the default option (save all successful rows and list the errors) and choose Review.

The Import Summary lists all entered information, shows the table properties and and does a data preview of the CSV file.

  1. Choose Import Into Database function to launch the data upload.

  2. The Import Status shows the successful import of nine records from the CSV file.

  3. Right-click on PRODUCTCATEGORYTEXTS table, and select Open Data.

  4. The table content with the product category records is shown in the SAP HANA Database Explorer tool. In total, nine product categories for the bike data model are there now.

We have now inserted the product categories by uploading data from a CSV file, which is suitable for larger amounts of data.

Alternatively, we can also insert the product category records into the table using SQL statements.

  1. [Alternative] Copy the following SQL statements:

    Code Snippet
    123456789101112131415
      TRUNCATE TABLE PRODUCTCATEGORYTEXTS;
  
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('RO', 'EN', 'Road Bike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" "(PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('BX', 'EN', 'BMX');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('CC', 'EN', 'Cyclo-cross Bike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('MB', 'EN', 'Mountain Bike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('RC', 'EN', 'Racing Bike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('DB', 'EN', 'Downhill Bike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('EB', 'EN', 'eBike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('CB', 'EN', 'Cruiser Bike');
  INSERT INTO "PRODUCTCATEGORYTEXTS" (PRODCATEGORYID, LANGUAGE, SHORT_DESCR) VALUES ('HB', 'EN', 'Hybrid Bike');
  
  SELECT TOP 1000 "PRODCATEGORYID", "LANGUAGE", "SHORT_DESCR", "MEDIUM_DESCR", "LONG_DESCR" FROM "PRODUCTCATEGORYTEXTS";

  2. [Alternative] Apply following steps to insert the records:

    • A - Choose the SQL icon on the top left. A SQL console opens in the right pane.
    • B - Paste the following SQL statements into the SQL console.
    • C - Choose the Run icon.
    • D - The data records of the table are shown in the Result tab underneath the SQL console.


The SAP HANA Database Explorer is SAP Datasphere's built-in SQL tool that provides access to the underlying SAP HANA database. Here, we will be using the Database Explorer to interact with the new created Open SQL Schema. You could also use 3rd party database management tools, like DBeaver, to interact with this schema. The data added to this schema is then available for modeling in SAP Datasphere Data Builder.


View Table with Data Builder

The data added to the Open SQL Schema is now available for modeling in SAP Datasphere Data Builder. We return to the SAP Datasphere browser window (tab), to access the newly created PRODUCTCATEGORYTEXTS table inside the modeling tools, and to outline how to proceed.

  1. Select Data Builder in the side navigation area. Choose your space if necessary.

  2. Select the New Graphical View tile.

In the source tree panel on the left side of the canvas, local and shared objects are listed in the Repository tab. Alternatively objects of remote sources are available for selection in the Sources tab.

  1. Go to the Sources tab in the Source Tree Panel.

  2. Expand your #OPENSCHEMA, and drag the table PRODUCTCATEGORYTEXTS onto the canvas.

  3. Select Import and Deploy in the window popup.

The PRODUCTCATEGORYTEXTS table node is now linked to the View 1 output node in the canvas. Be patient and wait for popup, informing you that the object is imported and deployed.

  1. Select the PRODUCTCATEGORYTEXTS node on the canvas and choose the Preview Data action.

  2. The product category records are shown in the data preview pane beneath.

Normally, you add additional tables from other sources and perform transformations (join, union, projection and aggregation operations) to create an expanded/enriched view.

Optional - Enhance the Data Model

Optionally, you can add the product category text information to the existing data model yourself. This would improve the analysis, to group the bike orders by the product category name, and gain insight into how well each product category is performing. The main modeling steps are as follows:

  • Define a new view with semantic usage Text. In the new view apply a projection to the PRODUCTCATEGORYTEXTS table node and maintain the semantics of the attributes. The entity 4OV_ProductCategory Text is predefined as a reference and shared to your user.
  • In the Product Dimension view, add a text association in the properties panel, refering to the new text entity and map both key fields.
  • Open the Sales Fact entity and redeploy it.
  • Open the Sales Analytic Model entity and redeploy it, before switching to the preview perspective.

Here is an example of the analytic model preview perspective:

Optional - END


  1. Close the graphical view editor and return to the Data Builder entry site.

  2. Choose Discard in the warning popup.

  3. The Open SQL Schema table PRODUCTCATEGORYTEXTS is listed as local table in SAP Datasphere.


Congratulations!

You have effectively established a new user and an Open SQL schema within the underlying SAP HANA database. Additionally, you created a table using SQL statements and imported product category data from a CSV file. Finally, you imported the Open SQL Schema table as local table into SAP Datasphere and previewed the table records.

This demonstrates the extensibility and flexibility that SAP Datasphere provides in allowing 3rd party tools to add, access, and analyze data inside SAP Datasphere.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn