Configuring Your HANA Database

1. Business Application Studio (BAS): An integrated development environment provided by SAP for efficiently building applications, including those involving HANA database projects.
2. Calculation View: A powerful information view in SAP HANA that allows for complex calculations, data transformations, and data modeling using various SQL operations.
3. APM Subscription: Refers to the subscription to SAP Asset Performance Management within the BTP Cockpit, necessary to use the APM software.
4. APM Instance + Service Key: A provisioned instance of SAP APM that includes a service key, which provides credentials for accessing and managing the database and its associated services.
5. Remote Source: In SAP HANA, a remote source refers to an external data source that can be connected to the HANA system to access and manipulate external data as if it were within HANA.
6. Schema: The structure that contains descriptions of objects created by a user, such as base tables, views, and privileges granted to access these objects.
7. User Provided Service (UPS): A Cloud Foundry concept where users can provide credentials or other configuration details for services not available in the marketplace.
8. hdbgrants File: A configuration file in SAP HANA projects that specifies the privileges granted to users for accessing database objects.
9. HDl Container: Stands for HANA Deployment Infrastructure container, which is used to manage and encapsulate the lifecycle of HANA database artifacts.
10. Cloud Foundry: An open-source platform as a service (PaaS) that provides a choice of clouds, developer frameworks, and application services for application development.
11. Aggregation Node: In calculation views, this node is used to perform aggregations on data, such as summing up values or calculating averages.
12. Join Node: A part of the calculation view that allows for combining data from different tables based on a related column.

Here in this lesson Jessica will gain the expertise to set up a HANA database tailored to the unique needs of SAP Asset Performance Management, focusing on seamless data integration and the dependability of the system for CRT Manufacturing's operations.

 To utilize the Analytics Dashboards within APM, certain preliminary steps must be taken to ensure this is possible. One of the necessary steps is to ensure that the HANA Database that holds the information is properly set up and configured. This will be set up via the SAP HANA Database Explorer. The HANA Database Explorer is a way of managing your HANA Database. We also will be using Business Application Studio (BAS) to create a calculation view. Calculation views are a way of taking the information from one or more tables present within your data instance and modifying them via different SQL operators, clauses, and functions (join, union, rank, etc.). You can view these calculation views with HANA Database Explorer once deployed there. This lesson does not cover the creation of a SAP HANA Cloud instance and assumes that you already have a working HANA instance in good order and can establish remote connections from it.

During the onboarding process, you should have already created an APM service instance and generated a service key. You will need the contents of the service key for the connection of your HANA DB to the APM Analytics database.

We cam now access the HANA Database Explorer and create our own remote source within there. To access our HANA Database Instance, we will go to the Spaces tab on the left sidebar within BTP Cockpit. After selecting the space we are using for our Database Instance, we are going to choose the SAP S/4HANA Cloud tab on the left sidebar within our space. Note that when inside of a space, the contents of the sidebar will change accordingly. Check to see if there is a HANA Database Instance within our space already. If so, choose the Actions drop down in the SAP S/4HANA Database Instances section near the bottom of the page and choose Open in SAP HANA Database Explorer.

If a Database Instance isn't already created, choose the blue Create dropdown near the top right of the page and select SAP HANA database.

Once in the HANA Database Explorer, open the drop down with the name of your instance on it, and then open the Catalog folder dropdown. In this section, right click on the Remote Sources icon and choose Add Remote Source.

The Add Remote Source pop up should now appear. For most of the fields that need to be entered here, the corresponding values for these fields will be found within the key that is located within our APM instance. That is why we needed to confirm that the instance and key were available beforehand. Below is a mapping of all the fields needed to create the Remote Source and where to find those values. Any values not specified should either be left blank or to their default value. Choose Create when finished.

*An easy way to remove "\n" from the certificate field value is to copy it into Notepad, open the find command, put\n in the Find field, leave the Replace field empty, and choose the Replace all button.

Enter the data:

With that completed, the Remote Source will show up in a new tab of the HANA Database Explorer.

With the Remote Source created, the next steps are to create a schema as well as the virtual tables that we will use within the calculation views that will eventually be made. To create a new schema within the HANA Database Explorer, simply choose the Open SQL Console button on the top left of the page and enter the query CREATE SCHEMA <schema_name>;. The title given for the schema here was APM_SCHEMA but this can be named however you would like to name it.

To check the schema, go to the Schema section under the Catalog folder of the database instance you are working with. From there, either scroll down or use the search bar to confirm that the schema is within your instance.

From here, we are going to create the Virtual Tables for our Remote Source. Virtual tables are in a sense a way to remotely access the contents of a table from a separate application. Instead of having to copy the contents of a table from one application to another, we can use virtual tables as an API of sorts to view and manage the contents of your database tables. The virtual tables that will be created here will be based on the tables of the analytics database. Here is a list of the tables that exist within the analytics database.

• com.sap.apm.analytics.timeseries::Aggregates
• com.sap.apm.analytics.transactional::Alerts
• com.sap.apm.analytics.masterdata::TechnicalObjects
• com.sap.apm.analytics.masterdata::Indicators
• com.sap.apm.analytics.masterdata::IndicatorThresholds
• com.sap.apm.analytics.masterdata::IndicatorPosition
• com.sap.apm.analytics.masterdata::IndicatorCategory
• com.sap.apm.analytics.masterdata::IndicatorAlertType

To create the Virtual Tables (also referred to as Virtual Objects within HANA Database Explorer), we need to create them based off a Remote Object in our Remote Source. The Remote Objects that we will be referencing here will be the ones present in the 12C78DB7F9A14379B3636AF17957FCC7 schema of the Remote Object. To access these Remote Objects, have the Remote Source open on the page. Choose the Schema drop down and select the 12C78DB7F9A14379B3636AF17957FCC7 schema from the list. Now choose the Search button towards the right of the page.

Scrolling down on this page, we can see all the Remote Objects for this page. Apart from one DUMMY Object, there are a list of views that relate to the tables present in the analytics database. There are some additional views unrelated to the ones listed as well though. To create a Virtual Object for one of the views, check off one of the Remote Objects in the list and choose the Create Virtual Object(s) button above the list on the right side of the page.

An Object Name will pre-populate based on the view that was chosen to create a Virtual Object on. If multiple objects are chosen to make Virtual Objects on instead of one, you can add and optional prefix. The default schema will be DBADMIN. Change this schema to the one just created. Once finished, choose the Create button on the bottom right of the Create Virtual Object pop up. Repeat this process for all additional views listed previously.

With our HANA Database configured, now we will be setting up a HANA Database Project within Business Application Studio (BAS). Within this project is where we will be creating the calculation views that will be used for the Analytics Dashboard within APM. Before going ahead with that however, there is one thing we will need to do within the HANA Database Explorer. What we will be doing here is creating some roles within our data instance and creating a user to assign those roles to.

Open the SQL console within HANA Database Explorer and copy paste the following into the console. Make sure to modify the schema name in here to whatever you named your schema in here.

-- create SQL roles

create role "genericRoleForOO";

create role "genericRoleForAP";

-- assign privileges that these roles should grant

grant select on schema <schema_name> to "genericRoleForOO" with grant option;

grant select on schema <schema_name> to "genericRoleForAP";

-- create a database user that should assign these privileges

create user UPS_GRANTOR password "DnATBG!1" NO FORCE_FIRST_PASSWORD_CHANGE;

-- allow UPS_GRANTOR to grant the respective roles

grant "genericRoleForOO" to UPS_GRANTOR WITH ADMIN OPTION;

grant "genericRoleForAP" to UPS_GRANTOR WITH ADMIN OPTION;

This will be used later within our HANA Database Project we are creating. The purpose of it is to allow us access to the virtual tables we made previously. Now with that done, we will create the HANA Native Application Dev Space if not done so already. To do this, go to the My Dev Spaces area of BAS and choose the Create Dev Space button at the top right of the page. Then choose SAP HANA Native Application has the application type and enter a valid name for the dev space. Once finished, choose the Create Dev Space button at the bottom right of the page.

The Dev Space will now start up. Wait for about a minute or two for the newly created dev space to change from the Started status to the Running Status. Once it is on the Running Status, choose the name of the Dev Space. It will now have blue text that signals it can be opened. Once entered, on Get Started tab in Business Application Studio, choose the Start from template option on the page.

Within the Project from Template tab, choose the SAP HANA Database Project button and then choose the Start button on the bottom of the page.

For the next few steps, you will be configuring the preliminary information to be used for the HANA project being created here. When each step is complete, choose the Next button at the bottom of the page to continue adding in all the necessary information. First, enter a valid project name in the field presented. Choose Next to continue.

The module name can be left as db, although you can change it if preferred.

The default values here can be left as is.

The final step is to select a Cloud Foundry Organization and Space so this HANA project knows that it needs to bind to the HANA Deployment Infrastructure (HDI) we have set up in HANA Database Explorer. Before doing this however, we will need to sign into our Cloud Foundry account. Otherwise, we will not be able to access the list of orgs and spaces that we are a part of. Enter in the email address and password associated with cloud foundry. Then choose the Login button on the right side of the password field to sign in.

Once signed in, select the Cloud Foundry Org and Space to use for the HANA Project. Make sure this is the same org and space as the HDI used in HANA Database Explorer. Change the preset org and space if necessary. Leave all other values as default, although the service instance name can be changed if desired. Choose the Finish button on the bottom page once complete. It will take a few minutes to complete the creation of the HANA Database Project.

Now with the main project setup, we will need to do some minor configurations before creating and deploying the calculation view that we are going to use. If not done so already, the users and roles that were created within the SQL console in HANA Database Explorer should be generated. This will be used in a later step involving the creation of the hdbgrants file, a file which determines the privileges as to which owners can access the database and which cannot.

Before making the hdbgrants file, we will first need to create a User Provided Service (UPS). A UPS allows us to send our credentials within our HANA Database Project to HANA Database Explorer. This UPS will be used in the hdbgrants file that will be shortly created.

To create a UPS, there are one of two ways to do so. First, you can either go within the space inside of BTP Cockpit you want to create the UPS in and create it there. Second, you can create it directly within the HANA Database Project. We will show how to do the latter option. To do so within BAS, open the SAP HANA Projects dropdown to see this list of HANA Database Projects within this dev space. There should only be one project for now, which is the one we created just recently.

Choose the dropdown next to the project here and hover over the Database Connections folder. A plus icon in a square should appear with the caption add database connection. Choose this to create the UPS for our project.

On the Add Database Connection tab, we are going to add the details for our UPS. Because we are creating the UPS from BAS, the connection type Create user-provided service instance will be selected here. If the UPS was created instead within BTP Cockpit, the option Existing user-provided service instance would be selected instead. Choose Create user-provided service instance from the Select connection type dropdown.

Now enter in the rest of the fields. The service instance name needs to be a valid name that is not shared with other instances already created. The page will let you know if a name is invalid, however. Keep the Connect to database field as Use deployment target container database. The user name and password here will be the same user name and password that were used when creating the user and roles within the SQL console within HANA Database Explorer. Assuming the values that you entered were not different compared to what was provided, the user name should be UPS_GRANTOR and the password should be DnATBG!1. Leave the rest of the fields as is and choose the Add button at the bottom of the page once finished.

Once finished, the UPS will now show up under the Database Connections folder under the SAP HANA Projects dropdown on the bottom right of the page.

Now it's time to create the hdbgrants file. This file will used in determining privileges as to which users can access the data in our data instance and what they are allowed to do with that data. To create the hdbgrants file, go to the left side bar on the page and choose the first icon that is in the shape of three horizontal lines. From there, choose View, then Command Palette to open the Command Palette. Alternatively, use Ctrl + Shift + P to open it as well.

Inside the Command Palette, type in artifact to pull quickly find the option, SAP HANA: Create SAP HANA Database Artifact. Select this option once found.

Inside of the Create SAP HANA Database Artifact tab, specify the artifact type as well as the name of the artifact. The folder path for the artifact and the database version will be left as is. The artifact type will be the Grants (hdbgrants) type and the name will be whatever you decide to name it. Once finished, choose the Create button at the bottom of the page.

The hdbgrants file will now be generated with some mock data present within it. This data will need to be set for our purposes, however. We will assign the genericRoleForOO in the Object Owner section and the genericRoleForAP in the Application User sections of the hdbgrants file respectively. The other fields will be deleted for our purposes. Also, the top of the file will need to have the generic UPS placeholder value replaced with the UPS created just earlier. In the end, the hdbgrants file will look something like this.

12345678910111213141516171819
{
"<Enter UPS name here>": {
"object_owner": {
"global_roles": [
{
"roles": ["genericRoleForOO"]
}
]
},
"application_user": {
"global_roles": [
{
"roles": ["genericRoleForAP"]
}
]
}
}
}

Now it is time to deploy our project and confirm the connection between our HANA Database Project and HANA Database Explorer. On the bottom right of the page under the SAP HANA Projects dropdown, choose the Deploy icon next to the name of the HANA project. This icon is shaped like a rocket ship and will not be visible unless hovering over the name of the project.

The project will now deploy, and the terminal will go through the process of deployment. Once finished, a message at the top of the terminal whether it was successful or not. If not, look at the contents of the terminal to determine how to fix the issue.

Now with all the preliminary steps taken to set up the HANA Database Project, we can create a calculation view within the project and send that back into HANA Database Explorer. One thing that we should do though before going ahead with the creation of the calculation view is to open our project's HDI container within HANA Database Explorer. To do this, go to the SAP HANA Projects section on the bottom right of BAS and hover over the project name. Choose the icon shaped with the cubes and cylinders on it to open the project within HANA Database Explorer. Choose Open on the pop up to proceed there.

In HANA Database Explorer, you will see a new HDI container based on the HANA Database Project created in BAS. It will also share the same service instance name that we set when we were initially creating the project. Within the Column Views folder inside of your HDI container is where the calculation views within BAS are going to sit.

To create a calculation view, we need to create a HANA artifact, just like how we created our hdbgrants file. Again, Open the command palette by using the command Ctrl + Shift + P or by choosing the icon with the three horizontal lines and selecting View → Command Palette. Because we just used this command, it should show up at the top of the command palette list in the recent category. If necessary, type out "artifact" to have the command show up. Select SAP HANA: Create SAP HANA Database Artifact to begin creating the calculation view.

Choose the artifact type in the dropdown as hdbcalculation view. Provide a valid name for the artifact. Once finished, choose the Create button at the bottom of the page to move into the artifact editor.

Now your calculation view is ready to be configured. What you can do with calculation views is extensive, and outside the scope of this lesson. For now, what we will see here is the process about how to go about a relatively simple calculation view.

What we will show is a simple join between two tables. A join will take columns from two tables and but it together, with a shared column that binds the two tables together. To perform a join, choose the join node icon on the left end of the page of the calc view tab. The icon for Join is of a Venn Diagram. To see the names of each node, choose the double arrow icon on the left side of the page view. Choose Join, then move your cursor below the aggregation node on the page.

To add tables to our Join node, choose the node. A list of icons should appear on screen. Choose the + icon to add data sources to the node.

In the Services field, change the service we have selected here to the user provided service we created earlier. Choose the dropdown, select the UPS we made, and deselect the Target Container Service option.

To find the tables that you created within HANA Database Explorer, use the search bar to locate them easier. Now, select two or more tables to be used for the join. If a synonym hasn't already been created, you will need to create one for the table. Once finished, choose the Create Synonym button at the bottom of the pop up.

A default Synonym Name will be provided. Change the synonym name if desired. Choose the Finish button once ready. You do not need to generate an hdbgrants file or create a synonym configuration file for our purposes.

Once the terminal runs successfully, you will see the tables present within the join node on the calculation view page. If they don't appear, close the terminal, and change the scaling of the page until they do. The next action would be to define the Join Definition between the two to know which fields match with each other in the table. Choose the join node to have the Join Definition tab appear. Drag one of the fields from one table to the other to know which field will join the two tables together.

Now switch to the Mapping tab to define the mapping of the join node. This will show which columns will appear within our output. In the mapping tab, drag each column over to determine which columns are going to be a part of the new view.

Close out of the panel. Connect the Join node to the Aggregation Node by choosing the Join Node and choosing and holding the Connect button. Place the connection from the Join Node directly under the Aggregation Node.

Choose the Aggregation Node and go to the Mapping tab. Drag over all the columns you want to use as output columns. Make sure that at least one of the columns is a numeric type, which will be denoted by the ruler icon next to the output column name. If none of these columns are of a numeric type, add in an additional column inside of the Join Node to do so until at least one column here shows up as numeric.

Choose the Deploy button at the top right of the page to deploy the calculation view. The Deploy button is in the shape of a rocket ship. The terminal should say the run was successful once completed.

Open the HDI container for our HANA project by choosing on the button in the SAP HANA Projects dropdown on the bottom left of the page. Hover over the name of the HANA project for the icon to appear.

Within HANA Database Explorer, open the HDI Container for the HANA Database Project we are working with. Choose the Column Views tab to see the calculation view we just created. Choose the Calculation view within the list to view the contents of the calculation view.

We've created our calculation view, but now we need to make sure that when we try to access the details of the calculation view within SAP Analytics Cloud (SAC). To do this, we will need to allow other users to have the ability to see these calculation views. The first step in getting this accomplished is to go back into BAS and create a role via a new HANA Database Artifact. Either use Ctrl + Shift + P or go use the manual way by going into the hamburger icon and going to View - > Command Palette.

In the Create SAP HANA Database Artifact page, select the artifact type as Role and enter a name for the artifact. Once finished, choose the Create button at the bottom of the page.

Within our hdbrole file, we can specify the privileges that users can have when accessing our schema. We need to define these roles for our schema so we can eventually access this data later in SAC. Switch to the Schema Privileges tab and choose the dropdown in the Privileges column.

Check both the SELECT and EXECUTE privileges within the dropdown.

Deploy the hdbrole file by choosing the deploy button at the top right of the page. The deploy icon is in the shape of a rocket.

Once the role file is successfully deployed, now deploy the main project again. The deploy button for that should be in the SAP HANA Projects dropdown on the bottom right of the page.

After successfully deploying your project, go back into HANA Database Explorer. From here, go into the HDI container that contains your calculation views that we want to allow access to within SAC. Choose the Column Views section to show all the calculation views you've made (which is likely just one). Right click on a calculation view and choose the option Generate SELECT Statement.

This will generate a SQL query that retrieves all the fields specified within our calculation view. We will not actually be executing this query. Rather, what we are looking for here is the information within the FROM clause. The FROM clause contains two parts which are separated by a dot (.). The part to the left is the schema name, while the part to the right is the calculation view name. Copy the schema name within the quotes on the left. We will be using this information when we shortly create a new user that and grant them access to our database instance where our calculation view is located.

Open a new SQL console within your HDI container that contains the calculation view. Either right click the HDI container and choose Open SQL Console or choose the SQL button at the top left of the page. Enter the following query into the console and execute the query.

CREATE USER report PASSWORD <your_password> NO FORCE_FIRST_PASSWORD_CHANGE set usergroup default;

Make sure that you are logged in as a database user that has the ability to create users and grant roles. DBADMIN will have those roles and likely is the user you are already signed in as. The user we are creating will be named report. The password field needs to be set to a string of your choice.

Once the user was successfully created, now it's time to grant the role to our new report user. Enter the following query into the SQL console. And execute the query.

GRANT <SCHEMA_NAME>."<NAME_OF_HDBROLE_FILE_IN_BAS>" to report;

The schema name is the one we obtained from the SQL Query earlier that retrieved the fields from the calculation view. The name of the hdbrole file can be located within BAS for reference. The name of my hdbrole file for instance was called PublicAccessSchema, although yours may be named differently.

Now our user named report has been created and they have access to the roles allocated within our hdbrole file. We can now use this to go ahead into SAC.

Enabling the Analytics Infrastructure for Custom Capabilities

Reflect on the process of setting up a HANA Database for SAP Asset Performance Management. Consider the complexities of integrating various data sources and the importance of ensuring data reliability.

How might understanding this setup process influence your approach to managing large datasets and making strategic decisions based on analytics within your organization?

Expert Response to Personal Reflection Question:

"Setting up a HANA Database for SAP APM is a multifaceted process that requires careful planning and a solid understanding of both the technical and strategic aspects of data management. Integrating various data sources into a coherent and reliable system is crucial for accurate analytics. As a consultant, this process reinforces the importance of meticulous configuration, such as ensuring that virtual tables accurately reflect remote data and that roles and privileges are correctly defined to maintain data security and integrity.

Understanding the intricacies of this setup process can have a profound impact on how one manages large datasets. It emphasizes the need for robust data structures that can support complex calculations and analyses. This can lead to more informed strategic decisions, as data reliability directly affects the insights derived from analytics. The ability to predict asset failures, optimize performance, and enhance reliability is greatly improved when the underlying data is comprehensive and accurate.

Furthermore, this process can encourage a proactive mindset towards continuous improvement of asset management strategies. It highlights the dynamic nature of data and the need to adapt to new information as it becomes available. By mastering the setup and management of a HANA Database, a consultant is better equipped to leverage the power of SAP APM, driving their organization towards operational excellence and strategic foresight."

Overview:

• Utilizing Analytics Dashboards in APM requires proper setup and configuration of the HANA Database, managed through SAP HANA Database Explorer.
• The process involves creating calculation views using Business Application Studio (BAS) and connecting to APM.

Ensure APM Subscription and APM Instance + Service Key:

• Confirm subscription to APM in BTP Cockpit and check for SAP Asset Performance Management in the list of applications.
• If not present, navigate to the Service Marketplace page, search for "apm," and create the application with a specific instance and service key.

Access HANA Database Explorer & Create a New Remote Source:

• Access HANA Database Explorer from BTP Cockpit, open the Catalog, and add a Remote Source for APM data.
• Populate the fields using information from the APM instance key.
• The Remote Source will appear in HANA Database Explorer.

Create Schema and Virtual Tables:

• Create a schema for APM within the HANA Database Explorer.
• Generate virtual tables (Virtual Objects) based on remote objects, representing tables in the analytics database.
• Virtual tables include timeseries aggregates, alerts, technical objects, indicators, and more.

Create HANA Database Project:

• Set up a HANA Database Project in Business Application Studio (BAS) to create calculation views for APM Analytics Dashboards.
• Create roles and a user within HANA Database Explorer, which will be used in the HANA Database Project.

Configure HANA Database Project:

• Set up a HANA Database Project in BAS, including the creation of roles and a user.
• Create a User Provided Service (UPS) to send credentials from the HANA Database Project to HANA Database Explorer.
• Generate an hdbgrants file to determine privileges for accessing virtual tables.
• Deploy the HANA Database Project, ensuring the binding to the HANA Deployment Infrastructure (HDI).

Create Calculation View and Deploy to HANA Database Explorer:

• Open the project's HDI container in HANA Database Explorer and create a calculation view using BAS.
• Configure a simple join between two tables in the calculation view.
• Deploy the calculation view to the HDI container.
• Confirm the existence of the calculation view within HANA Database Explorer.

Grant Access to Calculation Views:

• Create a role via a new HANA Database Artifact in BAS to define privileges.
• Define SELECT and EXECUTE privileges within the hdbrole file.
• Deploy the hdbrole file and then deploy the main project.
• In HANA Database Explorer, generate a SELECT statement for the calculation view.
• Create a new user and grant them access to the specified schema and role.
• The user is now ready to access the data for use in SAP Analytics Cloud (SAC).