The APM Data Model

Objectives

After completing this lesson, you will be able to:

Open the SQL Workbench
Identify the purpose and key fields on broker and customer tables
Identify Primary and Foreign Keys

The SQL Workbench

While direct access to the APM production database is not available, you can query the database using the SQL Workbench. Keep in mind that only SELECT statements can be used; SQL cannot be used to manipulate data in the database; for example, using INSERT, UPDATE, or DELETE. SQL statements and their results are not stored. If the SQL Statement needs to be run more than once or if the results need to be captured in an extract or report, QBQuery should be used instead of SQL Workbench.

The SQL Workbench can be opened from the Administrator portal under Tools → SQL Workbench. Query results display in a grid that can be exported to a .CSV or Excel file.

Using SQL Workbench

To create a basic query, enter the SQL query in the Input Parameters window and select Execute.

Setting a Favorite

If you have created a query that you would like to re-use in the future, you can set it as a favorite. The Favorites tab at the top of the query window contains a list of favorite queries.

History

The History tab opens the Workbench History Search screen, which displays all of the queries that have been executed in the SQL Workbench. You can also open this screen under Administrator → Tools → Workbench History.

Row and Export Record Limits

By default, the SQL Workbench returns up to 500 rows of data, and allows an export of up to 10,000 rows.

To remove the limit on query results, uncheck the Limit Results? box. This will return an unlimited number of data rows. You can also set a different limit by changing the number in the Display field.

You can also use the Export field to change the number of rows that can be exported; however, you can also use the system Options to reduce the number of records that can be exported, up to a maximum of 50,000. This limit overrides the limit set in the SQL Workbench.

To set an export record limit:

From the Administrator portal, select Configuration → Options.
Search for the option: ui.sqlworkbench.export.max.rows.nonlimitedaccess
Change the number in the Option Value field.

Variables in SQL Workbench

Using variables in SQL queries allows you to create more robust queries that can be useful for troubleshooting, setting bonuses, and other data analysis.

The image below shows an example of a query in SQL Workbench that uses a variable to set the value for the Broker Type.

Loading a QB Query in SQL Workbench

Although you can save a query as a favorite, as we saw above, the SQL Workbench does not store saved queries. As an alternative, you can create QB Queries, which store SQL queries for retrieval. We will explore QB Queries later in this course.

You can easily load the contents of a QB Query record into the SQL Workbench. To reference a QB Query:

Select the Input Parameters tab.
Select Get QB Query SQL
Search for the QB Query
Select the arrow next to the query. This will load the query into the SQL Workbench.
Select Execute.

Monitoring Database Activity

The Database Activity screen displays all SQL statements that are currently running in the system. You can view statements that are running, are idle, or are taking longer than expected to run. Once the SQL statement is done running, it is no longer displayed.

To view database activity, from the Administrator portal, select System Activity → Database Activity.

Canceling a Process

If a process has been taking longer than expected, you can cancel the process using the following steps:

Select the notifications icon in the upper right of the window.
1. This will open the RunList Detail for the process.
Select Cancel.

Execution Plans

When a SQL query is executed, the server reads the query and determines the most efficient way to retrieve the data based on the available indexes. An Execution Plan contains the details on the retrieval of data.

In some cases, the Execution Plan may not be correct, which can result in SQL processes running slowly or hanging up. If this happens, you can create an Execution Plan Request, which triggers a process that retrieves the execution plan from the server. This allows you to troubleshoot slow processes by identifying issues and fine tuning the query without the need for outside help.

To run an Execution Plan Request from the SQL Workbench:

Select Execution Plan in the lower right.
Select Submit.
Note
It takes a few minutes to run the execution plan.
To download the execution plan:
From the Administrator portal, select Tools → Execution Plan.
Select the new execution plan to open the details.
Scroll to the bottom and select Download File.

The resulting file can be viewed in a SQL editor such as SQL Studio.

Key Tables and Fields

Before you can import data, you need an understanding of the database structure, how the tables are used, and how the tables are associated and interlinked with one another.

To help understand the tables and the flow of the data between the tables, we can divide the tables into three groups:

· InFile tables are the tables to which data is loaded from outside systems.
· Configuration tables are primarily used to store data used to maintain records of transactions.
· The Transaction tables contain references to key configuration data used to process the transaction.

The diagram below shows that each configuration table has a corresponding inFile table. For example, the Broker table has a corresponding inFile table called inBroker..

Of course, there are many more tables, but you don’t need to know them all. You just need to know the key fields in some commonly used tables: Broker and BrokerDetail, Customer, Transaction Header (TranHead), Transaction History (TranHis) and BrokerHistory.

The Broker table contains information about hierarchies and the assignment of customer and policy ownership.

The Broker Detail table contains details for the Broker that can change over time, such as the expiration date and business type.

The Customer table contains the master record for each customer. An example of a customer mapped into APM would be an Employee Group, a member of an individual health plan, or a policy holder. The customer record typically only contains basic demographic information and does not contain date sensitive coverage details.

The TranHead table contains the static header information for the billing transaction.

The TranHis table holds the details for each dated transaction. It is used as the source information for computation of the commission amount.

The BrokerHistory table contains the summary of all Broker balances, including earned and paid commissions by billing period. Records in this table are generated when a transaction is posted.

To see a full list of related tables and key fields, see Lesson 1 of the Appendix to this course.

Key Fields and Table Relationships

As with any database, to ensure data integrity, each table has at least one key field that is used to uniquely identify each record. Some tables have more than one key field, but only one field is the primary key. In APM, this is referred to as the ObjectNo. For example, in the Broker table, the field would be BrokerNo.

Key fields used to associate records between tables have the suffix Val. Let’s look at an example that you’ll see often in APM; the Address Table.

The Address table is not only referenced in several other tables, such as the Broker table and the Customer table, but brokers and customers can have multiple addresses. As a result, a SELECT statement in the SQL Workbench may return a list like the one in the following image.

The AddressNo field is the primary key for the Address table. This field will always be unique in this table. On the other hand, notice that the AdrVal field, which maps each address to a Broker record, is the same on the first two records. This tells us a single broker has two addresses; one for the office and one for home.

Foreign Keys

The APM database makes extensive use of Foreign Keys to ensure data integrity. A foreign key is a field in a table that refers to the primary key in another table.

If a table contains foreign keys, the name will almost always be the same as the primary key for the associated table. For example, consider the Broker table, which has a many-to-many relationship with the Vendor table. A join table called BrokerVendor has foreign keys to both tables.

The field BrokerNo is the Primary Key for the table Broker.

The field VendorNo is the primary key for the table Vendor.

The field BrokerVendorNo is the Primary Key for the table BrokerVendor.

The table BrokerVendor has Foreign Key relationships to the Broker and Vendor tables. The fields BrokerNo and VendorNo fields in this table are foreign keys. In this example, the foreign key constraint will prevent the deletion of any Broker or Vendor record that is associated with an existing BrokerVendor record.

'Combined Key' Foreign Keys

In APM, some tables have 'Combined Key' Foreign Key relationships. Combined keys allow a record to be associated with multiple records in another table.

For example, let’s consider broker addresses. As we will see in the next unit, the same broker entity can be stored in multiple tables, including Broker, Producer, and Vendor. This means a single address can be associated with multiple records. We don’t want to store the same address more than once, so instead, the Address table has a Combined Foreign Key field that is used to join each address with a broker, producer, and vendor.

To allow addresses for multiple entities to be stored in a single Address table, the Address table contains two fields that work together: AdrVal is the foreign key for the BrokerNo, and AdrObject tells the system that the associated record is a broker.

In the example shown above, Max Martinez is both a broker and a vendor. Naturally, both records have the same address, so the Address table contains two records: one with AdrVal pointing to the BrokerNo (MM-101) and AdrObject set to Broker, and the second with AdrVal pointing to the VendorNo (MM-101V) and AdrObject set to Vendor.

To learn more, watch the video 'Overview of Key Tables in SAP SuccessFactors Agent Performance Management.'

https://video.sap.com/media/t/1_7242h8q2

Exercise: Using the Data Dictionary and the SQL Workbench

Business Example:

In this exercise, you will use the Data Dictionary to find details on the Producer table in APM, then use the SQL Workbench to query and export the data in the table.

ExerciseStart Exercise

Steps

Use the Data Dictionary to find details on the Producer table.
From the Help icon, select Data Dictionary.
Type Producer in the search bar.
Select the Producer entity.
Select the ProducerNo field and review the information about the field.
Select the Producer link to return to the Producer entity view.
Using the SQL Workbench, enter a SQL query to list all producers.
1. Select Administrator → Tools → SQL Workbench.
2. Enter the following SELECT statement to find a single record in the Producer table:
  - SELECT * from Producer.
  - WHERE ProducerNo = 16813280486430000.
  - Select Execute.
Make this query a favorite.
1. In the lower right, select Create Favorite.
2. Select OK.
3. To verify this step, select the Favorites tab at the top of the query window.
Load a QBQuery into SQL Workbench
1. Select the Input Parameters tab.
2. Select Get QB Query SQL.
3. In the QB Query Search window, enter Payout in the Name field.
4. Select Search.
5. Select the arrow next to Payout Extract.
6. Select Execute.
Export the results to an .XLSX file.
1. Select the XLSX link in the lower right.
2. Once the file downloads, open the file in MS Excel and view the data.

Continue to quiz