Snowflake Direct Access – Alchemy Systems Help Center

Note: This article explains the structure and use of the Alchemy Reporting Data Models. Snowflake Direct access will give you additional reporting capabilities outside of Alchemy Manager for those who use business intelligence (BI) or business analytics (BA) operations. If you are interested in using Snowflake, please contact your Alchemy Account Manager for pricing information. To learn more about Smart Reports, see What are Smart Reports?

About Snowflake Direct Access

The Alchemy Training System platform uses Snowflake, an extremely capable data warehouse solution. For clients with sophisticated business intelligence (BI) or business analytics (BA) operations, Alchemy offers the ability for customers to connect directly to Snowflake, enabling organizations to directly view, query, and analyze all their training data.

Snowflake Direct Access allows unique analytics and reporting on the training that Intertek Alchemy helps you deliver.

Efficiently build out your analytics
Easily combine Alchemy data with data sets from other systems
Uncover valuable insights and trends

Clients can access the Alchemy Manager Snowflake data cloud in one of two ways:

Customer account: this option is for customers who have their own Snowflake account and billing relationship. This option requires the customer to have their data stored in the AWS us-east-1 region.
Alchemy Reader Account: this option is where Alchemy creates and manages a Reader account for the customer, and all billing is handled by Alchemy.

Whether you use one of the Snowflake web interfaces or a direct network connection to Snowflake, your access to the data is performed with SQL, so you will need someone knowledgeable with using it. The level of SQL expertise you need will be determined by what you are trying to accomplish, but it could easily require the use of multiple JOINs, multiple WHERE clauses, and an extensive list of SELECT fields.

Data Models

The Data Models (DMs) are the views into your Alchemy data to which we provide direct access. Your Smart Reports and dashboards utilize these DMs to generate the data. They provide a version of the application data that simplifies retrieval and performs very well when analyzing the data.

The "key" fields are used to identify the main objects in the database. They can be used to identify unique objects and are also used with a "join" to pull in additional data.

DM_STUDENT_DETAIL

This table contains extensive data about each student. You can query it directly like this:

    select user_full_name, user_id, user_worksite, date_of_hire, user_groups
    from dm_student_detail
    order by date_of_hire desc nulls last
    limit 20;

The Group List Columns

Alchemy Manager can be configured to support up to 13 "Student Classifications." Each classification has a field in DM_STUDENT_DETAIL that has the name of the group (or groups) for which that student is a member. Multiple group names will be separated by spaces and the pipe symbol (|). Only the first 100 group names will be listed because this is intended to be displayed "as is" rather than manipulated or searched.

The columns are named as follows:

STATUS_GROUPS
PAY_TYPE_GROUPS
TIME_TYPE_GROUPS
EMP_TYPE_GROUPS
REGION_GROUPS
SUB_REGION_GROUPS
DIV_GROUPS
BUSS_UNIT_GROUPS
DEPT_GROUPS
JOB_CODE_GROUPS
JOB_TITLE_GROUPS
JOB_CLASS_GROUPS
SHIFT_GROUPS

In addition, the USER_GROUPS field acts the same way for "System Groups."

DM_*_REPORT

These are the main report tables. In general, each Smart Report in Alchemy Manager uses one report table, and each row in the table represents one row of output in a Smart Report. The simplest cases will probably use one report table and the DM_STUDENT_DETAIL table, a query like this would work:

    select s.user_full_name, tr.training_end_date, tr.activity_code, tr.activity_title
    from dm_tr_report tr
    inner join dm_student_detail s
      on tr.student_key = s.student_key
    where date(tr.training_end_date) = current_date();

The tables are:

Report Table	Data Provided
`DM_TR_REPORT`	Holds training data on individual learner activities from things like Alchemy Courses, Observations, Player courses, ILTs, etc.
`DM_LP_REPORT`	Holds details on the status of learner's subscriptions to learning plans. For each learner's subscription to a learning plan, it has a row for each activity required for completing that learning plan and the learner's status for that requirement.
`DM_OBSERVATION_REPORT`	Holds details of Observations such as the summary comments, the observer's info, root cause, reinforcement and corrective actions. Note that images are not available and individual answer details require joining with `DM_ANSWER_DETAILS`.
`DM_QP_STATUS_REPORT`	Holds the status of learner's assignments to qualification plans. This can include manual qualifications or expirations.

Note that we are removing the student fields from the report tables to simplify new reports and get details for multiple students (i.e. OBSERVED_BY_KEY, FACILITATOR_STUDENT_KEY, etc). For forward compatibility, you should retrieve student data from the DM_STUDENT_DETAIL table.

DM_LOCALE_*

These tables contain the various translations for objects in Alchemy Manager that support it. Each table represents the translations that are available for a single text field. Each row in the table represents the text that would be displayed for a single locale. (Currently, the locale field contains an obtuse number. This will be changed later.)

Note that a translation is present in each of our supported UI locales even if none was provided. The "en_US" translation will be used in this case.

Course content may be available in a wide variety of languages, but the parent title is in DM_LOCALE_PARENT and only in the supported UI locales. For instance, there are courses in Swahili, Vietnamese, etc., with non-English names, but their parent title would have an English translation, such as "Electrical Safety Swahili," and there might not be a Spanish translation of the parent title, so in dm_locale_parent it would be stored with the same English title.

As an example, to get "es-US" parent names (we'll get "en-US" where no "es-US" is available) for recent training, a query like this would work:

    select s.user_full_name, tr.training_end_date, tr.activity_code, p.name, tr.activity_title
    from dm_tr_report tr
    inner join dm_student_detail s
      on tr.student_key = s.student_key
    inner join dm_locale_parent p
      on p.parent_key=tr.parent_key and p.filter_locale=160
    where date(training_end_date) = current_date() limit 20;

The tables are:

Translation Table	Object Key	Data Provided
`DM_LOCALE_CATEGORY_GROUP`	`COURSECAT_KEY`	Holds the names for course categories
`DM_LOCALE_PARENT`	`PARENT_KEY`	Holds the names for parent courses
`DM_LOCALE_LP_GOAL`	`GOAL_KEY`	Holds the names for Learning Plans
`DM_LOCALE_QUALIFICATION`	`QUALIFICATION_PLAN_KEY`	Holds the names for Qualification Plans

The Locale ID (filter_locale) for Alchemy UI locales are:

Locale Name	Locale ID
en_US	1
es_US	160
fr_CA	186
my_MM	343
so_SO	442
en_GB	116
pa_IN	380
zh_CN	538
vi_VN	522
de_DE	85
es_ES	149
pt_BR	389
es_MX	153
fr_FR	194
es_419	140

DM_ANSWER_DETAILS

This table contains the details of a students' responses to a test, an evaluation, or an observation. Some fields are populated or not depending on the type of object. This will be joined to the report table by using TEST_SESSION_KEY.

Note: "Anonymous Evaluations" are anonymous in the UI but not in the data. To preserve anonymity, any use of the data needs to obscure the details.

DM_STUDENT_GROUP

This table includes all groups associated with a student. It is intended to be used only for filtering. This is accomplished by adding a WHERE clause such as:

    where student_key in (
        select distinct student_key from dm_student_group
        where group_name='A Status Name'
        and group_type='Status'
    )

DM_STUDENT_SECURITY

This table allows you to enforce Alchemy Manager's supervisor-style security. Manager's security is entirely based on access to a student. The rows in this table should be filtered by ADMIN_STUDENT_KEY and joined back to the report table using the STUDENT_KEY. The result is to eliminate rows that are not accessible to the admin. To obtain the right ADMIN_STUDENT_KEY, look up the STUDENT_KEY for the admin in the DM_STUDENT_DETAIL table. Here is an example to filter the results for the admin with USER_ID='000001':

    where student_key in (
        select distinct student_key from dm_student_security
        where admin_student_key=(
            select student_key from dm_student_detail where user_id='000001'
        )
    )

Note: The table can also be filtered by UNIQUEADMINID which is made up of the StudentID and your DB_NAME. It looks something like this:

    where student_key in (
        select distinct student_key from dm_student_security
        where uniqueadminid='12345@alchemy_clientname_live'
    )

Incremental Load

If you are mirroring the report data into a system of your own, reloading the data on a regular basis might be too slow or too expensive. The standard solution to this problem is to only load what's changed. This is called an incremental load or sometimes called delta loading. There are several difficulties with this plan:

Identifying the changed rows can be difficult
The changed rows have to be merged with the old data
There's no way to know about rows that need to be deleted

The easiest way to handle a big data load is to do incremental loads each day but then do a full reload once a week. In other words, use an incremental load as a "best effort" to have up-to-date data, but use a full reload to fix any things that were missed or need to be deleted.

We are working to make it easier to identify rows that are changed. Currently, each table has a CHANGE_DATE field, but you may find that this changes too often. We are adding a DM_LAST_MODIFIED field to indicate only when important changes have happened. If you are using any joins then you should also honor changes in those tables. To be thorough, you should look for any dates greater than the largest date from the previously loaded data.

To illustrate a strategy to keep a local copy of DM_TR_REPORT, this block of pseudocode assumes that your "local db" works exactly like another Snowflake account. The point of this is to minimize the amount of data transferred from Alchemy to the local db.

    -- Transfer changed rows from Alchemy to Local DB
       SET last_previous_date = (
         SELECT COALESCE(MAX(DM_LAST_MODIFIED), '1980-01-01')
         FROM localdb.DM_TR_REPORT );
       CREATE OR REPLACE localdb.changes AS
       SELECT your_columns FROM ALCHEMY.DM_TR_REPORT
       WHERE DM_LAST_MODIFIED > $last_previous_date;
    -- Locally update existing rows where there are matches
       UPDATE localdb.DM_TR_REPORT SET your_columns
       FROM localdb.changes
       WHERE localdb.DM_TR_REPORT.transcript_key = localdb.changes.transcript_key;
    -- Insert rows where there are no matches
       INSERT INTO localdb.DM_TR_REPORT (your_columns)
       SELECT your_columns FROM localdb.changes
       LEFT JOIN localdb.DM_TR_REPORT
         ON localdb.DM_TR_REPORT.transcript_key = localdb.changes.transcript_key
       WHERE localdb.DM_TR_REPORT.transcript_key IS NULL;

Note that this still won't handle deleted rows.

Here's a list of the DM tables and what key (or keys) should be used to identify unique rows.

DM Table	Identifying keys
`DM_TR_REPORT`	`TRANSCRIPT_KEY`
`DM_LP_REPORT`	`SUBSCRIPTION_KEY`, `COURSE_REQ_KEY`
`DM_STUDENT_DETAIL`	`STUDENT_KEY`
`DM_STUDENT_SECURITY`	`ADMIN_TO_STUDENT_KEY` or `ADMIN_STUDENT_KEY`,`STUDENT_KEY`
`DM_STUDENT_GROUP`	`GROUP_TO_STUDENT_KEY` or `GROUP_KEY`,`STUDENT_KEY`
`DM_ANSWER_DETAILS`	`TEST_ANSWER_KEY`
`DM_LOCALE_CATEGORY_GROUP`	`CATEGORY_GROUP_LOCALE_FILTER_KEY`
`DM_LOCALE_LP_GOAL`	`LP_GOAL_LOCALE_FILTER_KEY`
`DM_LOCALE_PARENT`	`PARENT_LOCALE_FILTER_KEY`
`DM_LOCALE_QUALIFICATION`	`QUALIFICATION_PLAN_LOCALE_FILTER_KEY`
`DM_OBSERVATION_REPORT`	`TRANSCRIPT_KEY`
`DM_QP_STATUS_REPORT`	`ASSIGNMENT_KEY`

At the current time, only DM_TR_REPORT has a DM_LAST_MODIFIED field. The other tables have a CHANGE_DATE field which can be used in the same way, but it won't be as effective at reducing the records considered to be "new".

Advanced reports

Some of our reports have additional complexity on top of the normal tables. You will be unable to recreate the output of these reports without reproducing this additional complexity.

For instance:

The Learning Plan Subscription Report uses grouping in order to simplify the results when users are subscribed to the same plan multiple times
The "Matrix" reports use multiple rows and performs a "pivot" operation to convert those extra rows into columns.
The Dashboard Charts available in Manager aggregate the rows in one or two dimensions and then provide the data to a charting library.

Articles and Videos in Reports