Initializing help system before first use

Configuring How Data is Exposed to BI Software

Business Intelligence software such as Tableau expects to query data stored in a standard format. Xpress Insight holds scenario data in a proprietary, highly compressed format. The data must therefore be expanded ("mirrored") into a relational database to be consumed by a third-party BI tool. This database is referred to as the Mirror.

Xpress Insight dynamically creates and manages a MySQL database schema per copy of an app hosted by the system. The schema and content of the mirror database is also managed by Xpress Insight. Apps define the data entities that are to be mirrored in the companion file, grouped into tables.

Note:

The mirror database acts a short-lived cache of the scenario data in relational format. The data is deleted from the mirror based on configurable rules. For more information about configuring mirror eviction rules, see Configuring FICO® Xpress Insight for Tableau.
Rules for defining mirror tables:
  • Array entities in the same table must have identical index sets.
  • Scalars can only be combined with other scalars.
  • A set can be mirrored explicitly. It must be the only entity defined for the mirror table.
  • The table-name must not exceed 64 characters.
  • The column name must not exceed 64 characters.
  • The table name and column name must be a character in the Unicode range U+0001 to U+FFFF, excluding the back quote (`).
  • Column names must also be unique when case insensitive.
  • Entities of Boolean data type will be mirrored as integers with a value of 1 indicating true and a value of 0 indicating false (when labels are not applied).
  • The column names mirror_row_id and scenario_id are reserved column names.
Example of a mirror definition added to the My_Portfolio_Simulator example app: 
<database-mirror>
        <mirror-tables>
	  <!-- array(CountryValues) -->
            <mirror-table name="country_info">
                <entity name="Country_Count"></entity> <!-- of string --> 
                <entity name="Country_CurrentFraction"></entity> <!-- of real -->
                <entity name="Country_Fraction"></entity> <!-- of real -->
            </mirror-table>
 	  <!—scalars -->
            <mirror-table name="kpis">
                <entity name="TotalBudget"></entity>
                <entity name="TotalCombined"></entity>
                <entity name="TotalReturn"></entity>
                <entity name="TotalRisk"></entity>
                <entity name="TotalValue"></entity>
            </mirror-table>
	  <!-- a set of string -->
            <mirror-table name="shares_list">
                <entity name="Shares"></entity>
            </mirror-table>
        </mirror-tables>
    </database-mirror>

Results in the following schema: 

> describe country_info;
+-----------------------------------------+--------------+------+-----+---------+----------------+
| Field                                   | Type         | Null | Key | Default | Extra          |
+-----------------------------------------+--------------+------+-----+---------+----------------+
| mirror_row_id                           | bigint       | NO   | PRI | NULL    | auto_increment |
| scenario_id                             | char(36)     | NO   | PRI | NULL    |                |
| CountryValues                           | varchar(512) | NO   |     | NULL    |                |
| Number of shares per country            | int          | YES  |     | NULL    |                |
| Aggregated Current Fraction per Country | double       | YES  |     | NULL    |                |
| Aggregated Fraction per Country         | double       | YES  |     | NULL    |                |
+-----------------------------------------+--------------+------+-----+---------+----------------+

> describe kpis;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| scenario_id | char(36)     | NO   | PRI | NULL    |       |
| name        | varchar(512) | NO   | PRI | NULL    |       |
| value       | varchar(512) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

> describe shares_list;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| scenario_id | char(36)     | NO   | PRI | NULL    |       |
| Shares      | int          | NO   | PRI | NULL    |       |
| Label       | varchar(512) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
Note:
  • The database-mirror/@apply-aliases attributes determines whether array and set entity aliases are used for the column names where defined, instead of the entity names. The default is true.
  • The entity/@alias attribute overrides the choice of column name for an individual column.
  • Scalars are mirrored row-wise as name, value pairs.
  • The database-mirror/@apply-aliases end entity/@alias attributes also apply to scalar entities, where they determine if the name column is populated with the entity name or its alias.
  • The database-mirror/@apply-labels attribute determines whether set columns are populated with label values rather than set elements, where defined. The default is true.
  • A mirror table with a single set entity is a special case. Here the schema has a separate column for the set elements and the label values, if defined.

There are 3 system tables included in every schema.

insight_selection table:

The contents of this table represent a mapping of a request from a vdl-tableau component in a custom UI view to the list of scenarios in the request. The query from BI software e.g., Tableau data source must join this table to the entity table(s) to select the right scenarios. The query must also filter on username to implement the expected user access model i.e., only a user who has requested the data can see the data.

The table also supplies useful meta-data for a BI visualization to use such as the names of the app and scenarios, the type of the scenarios and the order of the scenarios on the UI shelf.

Column Type Description
selection_id char(36) UUID of the mirror request containing the tables and scenarios to mirror.
app_name varchar(255) Name of the Insight app
scenario_id char(36) UUID of the scenario. The app mirror tables should be joined to this table by the scenario id.
scenario_name varchar(255) Name of the scenario
scenario_path varchar(8192) Full path to the scenario
scenario_type varchar(255) Type of the scenario (blank if scenario types are not used)
scenario_ordinal smallint Position on the shelf
username varchar(255) Username of the Insight user who requested the data to be mirrored.
scenario_type varchar(255) The type of the scenario.

insight_authorities table:

This table exposes the list of authorities for a user.

Column Type Description
username varchar(255) Username of the Insight user who requested the data to be mirrored.
authority_name varchar(255) An authority that is assigned to the user.

insight_authority_groups

This table exposes the list of authority groups for a user.

Column Type Description
username varchar(255) Username of the Insight user who requested the data to be mirrored.
authority_group_name varchar(255) An authority group that is assigned to the user.

The mirror database also includes tables prefixed with __internal. These tables are internal to Xpress Insight, should not be used by Tableau views and may change in future releases.

Parent Topic Integrating Third-Party BI Visualizations in Custom Views

© 2001-2024 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.