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.
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.
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:
In addition, the
USER_GROUPS field acts the same way for "System Groups."
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|
||Holds training data on individual learner activities from things like Alchemy Courses, Observations, Player courses, ILTs, etc.|
||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.|
||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
||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.
FACILITATOR_STUDENT_KEY, etc). For forward compatibility, you should retrieve student data from the
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|
||Holds the names for course categories|
||Holds the names for parent courses|
||Holds the names for Learning Plans|
||Holds the names for Qualification Plans|
The Locale ID (
filter_locale) for Alchemy UI locales are:
|Locale Name||Locale ID|
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
Note: None of the current Smart Reports include data from this table, and "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.
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' )
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
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 filted 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' )
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|
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".
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.
- 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.