Managing Database Views Using Service Layer API

Objectives

After completing this lesson, you will be able to:
  • Manage database views using Service Layer API
  • Manage MSSQL views using Service Layer API

Semantic Layer Views on SAP Business One for SAP HANA

In SAP HANA, Modeling is the process of refining or slicing data in database tables by creating views according to a business scenario. SAP HANA models contain attribute views, analytic views, calculation views, and procedures. The models can be used for reporting and decision making.

Note

In SAP Business One, Semantic Layer views run on top of SAP Business One Analytic Service. First, you need to install the SAP Business One Analytic Service and initialize the company database as below steps:

  1. Open the SAP Business One Analytics home page. For example, the URL will look similar to this: https://databaseserver:40000/Enablement/
  2. Navigate to the Company tab.
  3. To start the initialization, select the desired model language and choose Initialize.

Semantic Layer views fall into two categories: system built-in views and customized views. First, let’s look at built-in views.

Two categories of Semantic Layer views – built-in views and customized views - with a numbered list next to the steps for built-in views.

The system built-in views are authored by SAP. Only calculation views with the "Query" postfix in its name are allowed to be exposed in Service Layer. For example, SalesOrderDetailQuery, BalanceSheetQuery.

  1. Create View - Once the initialization is successful for the database in SAP Business One Analytics, the system built-in views should be created automatically.
  2. Deploy - The initialization process also deploys the views in content package automatically.
  3. Expose - To expose views to Service Layer, manually perform the following steps:
    1. Open SAP Business One Desktop client.
    2. Open the SAP HANA Model Management window.
    3. Select Views.
    4. Check the corresponding Service Layer Expose checkbox.
    5. Choose Update.
    6. For the changes to take effect, restart Service Layer.
  4. Authorize - To grant the view permission to a normal user, carry out the following steps:
    1. Log on to the SAP Business One Desktop client with the superuser.
    2. Open the General Authorizations window from Administration → System Initialization → Authorizations.
    3. Find the exposed view and assign Full Authorization and update.
  5. Ready to use - All exposed and authorized views can be accessed.

General format

Code Snippet
1
https://localhost:50000/b1s/v2/sml.svc/<viewname>

Access the Service Endpoint URL

Code Snippet
1
GET https://localhost:50000/b1s/v2/sml.svc/

Access the Metadata

Code Snippet
1
GET https://localhost:50000/b1s/v2/sml.svc/$metadata

All general data retrieval options are available in the Service Layer. For example, you can:

  • Get all records from a view.
  • Query one record from a view.
  • Get data using query options, such as projection, filter, and orderby.
  • Get data with aggregation.

Next, let’s look at customized views.

Two categories of Semantic Layer views – built-in views and customized views - with a numbered list next to the steps for customized views.

Customized views are authored by non-SAP. Only calculation views with the "Query" postfix in its name are allowed to be exposed in Service Layer.

  1. Create View - To create customized calculation views, refer to Create Graphical Calculation Views on the SAP Help Portal. You can use all the tables available in SAP Business One database.
  2. Package - To package the customized views (model package), refer to Creating Customized SAP HANA Model Packages on the SAP Help Portal..
  3. Deploy - To deploy the model package of customized views, open the SAP HANA Model Management window in the SAP Business One client, import the package, and choose Deploy.
  4. Expose - To expose customized views, manually perform the following steps::
    1. Start SAP Business One Desktop client.
    2. Open the SAP HANA Model Management window.
    3. Select Views.
    4. Check the corresponding Service Layer Expose checkbox.
    5. Choose Update.
    6. For the changes to take effect, restart Service Layer.
  5. Authorize - To grant the view permission to a normal user, carry out the following steps:
    1. Log on to the SAP Business One Desktop client with the superuser.
    2. Open the General Authorizations window from Administration → System Initialization → Authorizations.
    3. Find the exposed view and assign Full Authorization and update.
  6. Ready to use - All exposed and authorized views can be accessed.

General format

Code Snippet
1
https://localhost:50000/b1s/v2/sml.svc/<viewname>

Access the Service Endpoint URL

Code Snippet
1
GET https://localhost:50000/b1s/v2/sml.svc/

Access the Metadata

Code Snippet
1
GET https://localhost:50000/b1s/v2/sml.svc/$metadata

All general data retrieval options are available in the Service Layer. For example, you can:

  • Get all records from a view.
  • Query one record from a view.
  • Get data using query options, such as projection, filter, and orderby.
  • Get data with aggregation.

How to Create Customized Calculation Views in SAP HANA Studio and Expose them to Service Layer

In this video, you’ll learn how to create a calculation view using SAP HANA Studio.

For more information on graphical calculations views, refer to Create Graphics Calculations Views on the SAP Help Portal.

For more information on data modeling with SAP HANA cloud, refer to the SAP learning journey Developing Data Models with SAP HANA Cloud.

Next, package the tool based on the steps outlined in Creating Customized SAP HANA Model Packages | SAP Help Portal.

Once you’ve packaged the tool, this video will show you how to deploy and expose the calculation views for Service Layer.

SQL Views on SAP Business One (MSSQL)

In MSSQL, a view is a virtual table where the contents are defined by a query. Unlike the SQLQuery entity, that you learned about in the previous lesson, the query can be formed with all the objects available in the database.

SQL View in MSSQL with a numbered list next to the steps.
  1. Create View - You can create the view from the SQL Server Management Studio. For more information on how to create views, refer to this Create Views resource.

    For the Service Layer to recognize the views, make sure the following conditions are met:

    • The view is under the schema dbo.
    • The view name ends with B1SLQuery.
  2. Expose - To expose customized views, Service Layer brings a new entity SQLViews to help accomplish this.

    POST /b1s/v1/SQLViews('<view_name>')/Expose

    POST /b1s/v1/SQLViews('<view_name>')/Unexpose

  3. Authorize - To grant the view permission to a normal user, carry out the following steps:
    1. Log on to the SAP Business One Desktop client with the superuser.
    2. Open the General Authorizations window from Administration → System Initialization → Authorizations.
    3. Under Service Layer SQL View, assign Full Authorization for the view name exposed and update.
  4. Ready to use - All exposed and authorized views can be accessed.

    General format

    Code Snippet
    1
    https://localhost:50000/b1s/v2/view.svc/<viewname>

    View Service Endpoint

    Code Snippet
    1
    GET https://localhost:50000/b1s/v2/view.svc/

    Metadata

    Code Snippet
    1
    GET https://localhost:50000/b1s/v2/view.svc/$metadata

All general data retrieval options are available in the Service Layer. For example, you can:

  • Get all records from a view.
  • Query one record from a view.
  • Get data using query options, such as projection, filter, and orderby.
  • Get data with aggregation.

How to Expose MSSQL Views in Service Layer

In this video, you’ll learn how to expose MSSQL views in Service Layer.

Query the Views

In the exercise, you'll practice exposing and executing the MSSQL views using the SQLViews entity in the Service Layer.

Before starting this exercise, make sure you:

Steps

  1. To create a view in MSSQL, open Query Analyzer from SQL Server Management Studio and amend the sample query and execute it:

    2. Sample Query to create a view

    Code Snippet
    1234567891011
    CREATE VIEW [dbo].[B1_ItemPriceB1SLQuery] AS 

SELECT T0.[ItemCode], T0.[PriceList], T0.[UomEntry], T0.[Price], T0.[Currency], T0.[PriceType] 

FROM [dbo].[ITM1] T0 

UNION ALL 

SELECT T0.[ItemCode], T0.[PriceList], T0.[UomEntry], T0.[Price], T0.[Currency], T0.[PriceType] 

FROM [dbo].[ITM9] T0

  2. After execution, check if you’ve received "Commands completed successfully".

  3. To expose the view to Service Layer (which you created in step 1), amend the following HTTP sample request and send:

    4. Sample request

    Code Snippet
    1
    POST https://localhost:50000/b1s/v2/SQLViews('B1_ItemPriceB1SLQuery')/Expose

  4. Check the response:

    1. Check the response code - it should be 204 No Content if the HTTP request is successful.

    2. Check the response content - it should contain empty response.

  5. To check whether the view is exposed in Service Layer, amend the following HTTP sample request and send:

    6. Sample request

    Code Snippet
    1
    GET  https://localhost:50000/b1s/v2/view.svc

  6. Check the response:

    1. Check the response code - it should be 200 - OK, if the HTTP request is successful.

    2. Check the response content - it should contain the above exposed view in the list.

  7. To execute the view, amend the following HTTP sample request and send:

    8. Sample request

    Code Snippet
    1
    GET https://localhost:50000/b1s/v2/view.svc/B1_ItemPriceB1SLQuery

  8. Check the response:

    1. Check the response code - it should be 200 - OK, if the HTTP request is successful.

    2. Check the response content - it should contain the view result.

Result

You have successfully performed various operations using the SQLViews entity and checked the request output.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn