Set Up a SQL Backend as Fact Store

You can use a SQL database to store facts and qualifiers for your Fact Store.

Answer Server can connect to any RDBMS that supports SQL. You specify how to connect to the database by setting the appropriate connection string in the ConnectionString configuration parameter in the FactBank configuration. The most fully tested options are:

The SQL Fact Store has two required tables, one for facts, and one for qualifiers. The facts table stores the values of entity properties. The qualifiers table stores the names and values of particular sets of qualifiers associated with the properties. Each table includes qualifier combination values, which link the properties to the associated qualifiers.

In addition, there are two optional tables, one for sources and one for security types. The sources table stores information about the sources of your facts, including optional security information to allow you to restrict the facts by user permissions. The security types table stores the security configuration information for your security types.

Your Answer Server installation includes PostgreSQL schema files for these tables, and a utility script that you can use to apply these schemas to a database by using the psql utility. These files are available in the /factbank/schemas/postgresql directory in your installation.

The following sections describe the tables in more detail, and provide some best practices for how to organize your fact stores.

Manage Your Tables

HPE recommends that you organize your tables by creating a separate database for each set of facts and qualifiers. In this case, each database is the backend for its own Fact Bank system, which optimizes the database queries required for an Ask action.

For example, if you have a collection of facts about company sales histories, and a collection of facts about the products that a company offers, you might create a sales database and a products database. Each database is a separate Fact Bank instance in your Answer Server setup, and you can easily query one or both, as required.

Facts Table

The facts table stores the values of entity properties.

This table must have the name facts. The facts table has four required columns, and one optional column, described in the following table.

Column Type Description
entity_code text The code for the entity that this row is about, from your entity_to_code.txt coding file.
property_code text The code for the property that this row is about, from your property_to_code.txt coding file.
property_value text The value of this property for the specified entity, in the associated qualifier combination.
qualifier_combination integer The reference value for the rows in the qualifiers table that contain qualifiers that apply to a particular property value. This value corresponds to the values in the qualifier_combination column in the qualifiers table.

Answer Server uses this column to find the appropriate property, entity, or property value when a question contains a particular qualifier. If this value does not correspond to a value in the qualifiers table, Answer Server treats it as having no qualifiers. HPE recommends that you reserve a value to use for properties that do not have qualifiers (usually 0).
source_id integer (Optional) The reference value for the row in the sources table that contains the source for this fact. This value corresponds to the values in the id column in the sources table.

Answer Server uses this column to find and return the source for a particular fact. If this value is missing, or does not correspond to a value in the sources table, Answer Server returns the source as SQLDB. HPE recommends that you reserve a value to use for properties that do not have source information (usually 0).

You can optimize the performance of the facts table by creating indexes on each column. For example, in a PostgreSQL instance, HPE recommends that you create a btree index on each column.

Qualifiers Table

The qualifiers table stores the codes and values for qualifiers, and the qualifier combination reference that links a qualifier to a row in the facts table.

The table must have the name qualifiers. The qualifiers table has three columns, described in the following table.

Column Type Description
qualifier_combination integer

The qualifier combination reference that identifies qualifiers that are associated with a particular property value for an entity.

qualifier_code text The code for the property that this qualifier is about, from your property_to_code.txt file
qualifier_value text The value of the qualifier in this qualifier combination

Most data sets will have multiple rows with the same qualifier combination. For example, if your facts table contains the ares of different types of land in a country over time, you might have something like the following table for qualifiers.

qualifier_combination qualifier_code qualifier_value
1 LANDTYPE Farmland
1 YEAR 2015
2 LANDTYPE Forest
2 YEAR 2015
3 LANDTYPE Farmland
3 YEAR 2016
4 LANDTYPE Forest
4 YEAR 2016

In this case, the qualifier combination 1 relates to farmland in 2015, qualifier combination 2 relates to forest in 2015, and so on.

You can optimize the performance of the qualifiers table by creating indexes on each column. For example, in a PostgreSQL instance, HPE recommends that you create a btree index on each column.

Sources Table

The sources table is an optional table to allow you to store the sources for your facts. You can use this option with the source_id column in the facts table to store the details of the fact sources. Answer Server returns the source information with the fact when it returns in an Ask action.

The table must have the name sources. The sources table has two required columns and two optional columns, described in the following table.

Column Type Description
id integer A primary key integer ID value for the source.
source text The name of the source. Answer Server returns this value in the Ask action when it returns a fact that has the associated source ID.
acl text (Optional) The Access Control List (ACL) for this source. You can use this option if you want to use user security for your facts.
security_type_id integer

(Optional) The reference value for the row in the security_types table that contains the security type for this source. This value corresponds to the values in the id column in the security_types table. You can use this option if you want to use user security for your facts.

Answer Server uses this column to find and return the security type for a particular source. If this value does not correspond to a value in the security_types table, Answer Server treats it as having no security. You can set security_type_id to NULL, or you might want to reserve a value to use for properties that do not have security (usually 0).

Security_Types Table

The security types table is an optional table to allow you to store the security type information for your sources. You can use this option with the security_type_id column in the sources table to store the details of the security types.

The table must have the name security_types. The security types table has two columns, described in the following table.

Column Type Description
id integer A primary key integer ID value for the security type. This value corresponds to the IDs that you use in the sources table.
friendly_name text The name of the security type. This value must correspond to the security type configuration section in your Answer Server configuration file.

_HP_HTML5_bannerTitle.htm