The APM Data Model

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.

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.

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.

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.