User Defined Attributes (UDAs)

UDAs are stored in the user_defined_attribute table that is described in the Meta Model Definition Spreadsheet. Each UDA value is stored in a separate row in the table. The table has the following columns:

Column Name	Field Type	Description
name	Varchar(128)	Name of the UDA
date_value	Timestamp(3)	Date stored here if the UDA is a date field
numeric_value	Double	Number stored here if the UDA is a number
string_value	Varchar(1024)	String value stored here if the UDA is a string
scenario_facts_id	bigint(20)	Pointer to the scenario fact that this UDA is logically part of

The table acts like a name/value pair list for each UDA that gets stored in aP Analytics. The “name” field stores the name of the UDA while the value is stored in the date, numeric, or string field depending on the UDA’s type. The rest of the fields are links to other tables (fact and dimensions tables).

In the Ad Hoc environment it would be very difficult for the Ad Hoc Editor to make sense out of the name/value pairs in the UDA table. For example, the column heading is stored in the “name” field and the value could be in one of three fields. To make these fields appear as regular columns in the scenario facts table (so they look like regular attributes of a scenario) we have created a view table called Scenario_Facts.

Scenario_Facts is a duplicate of the scenario_facts_base table with additional columns representing each of the UDAs that have been defined in your aPriori environment. The following SQL code is run against the MySQL Reporting database whenever a new UDA is defined in aPriori. In your deployment, you add a new line for each UDA field.

Note:

This snippet serves only as an example. Do not cut and paste the following lines. Doing so from this document may introduce unwanted characters in your code, and you may not be using the most current code. The most up-to-date version of this code is in your apriori-platform installation directory:

<drive>:\apriori-platform\distribution\<version>\datamart\samples\custom-fields\<db_type>\UserDefinedAttributes\create-scenario-uda-views.sql

create-scenario-uda-views.sql

    DROP VIEW IF EXISTS scenario_facts;
    DROP VIEW IF EXISTS scenario_facts_ext;
    CREATE SQL SECURITY INVOKER VIEW scenario_facts_ext AS
    SELECT scenario_facts_id,
           MAX(if (name = 'ACMECategory', string_value, null)) AS acme_category,
           MAX(if (name = 'ACMEPriority', numeric_value, null)) AS acme_priority,
           MAX(if (name = 'ACMETargetDate', date_value, null)) AS acme_target_date
      FROM user_defined_attribute
     GROUP BY scenario_facts_id;
    CREATE SQL SECURITY INVOKER VIEW scenario_facts AS
    SELECT scenario_facts_base_ext.*,
           scenario_facts_ext.acme_category,
           scenario_facts_ext.acme_priority,
           scenario_facts_ext.acme_target_date
      FROM scenario_facts_base_ext
      LEFT JOIN scenario_facts_ext
        ON scenario_facts_ext.scenario_facts_id = scenario_facts_base_ext.id;

In the above example, udaString1, udaString2, udaNumber1, and udaDate1 are intended to represent the names of the UDAs defined in your aPriori deployment.

After running this SQL script (a step your DBA should most likely handle for you), you will want to adjust your domain to recognize these new fields. You should make a copy of the aPriori domain and extend the copy.  The steps for modifying the domain are relatively simple (please contact your aPriori Professional Services specialist if you need help with this step).