All Analyze Direct client data is stored in views in the ANALYZE_DIRECT database and DIRECT schema. There are four types of views: DIM_, FACT_, BRG_, and SEC_. For simplicity, this document refers to these views as tables.

DIM_

Dimension tables contain supplementary data used for categorization, grouping, and filtering. These tables always have an incrementally generated surrogate key. They also have default records for key values of -1 and -2 to represent null and unknown records, respectively.

The -1 surrogate key is populated in a fact table for null foreign keys. This ensures an inner join does not exclude fact records with null foreign keys. The -2 surrogate key indicates that the fact record fails to find a surrogate key that should exist in the dimension table. If a -2 surrogate key is found in any fact tables, submit a request to Absorb Support.

List of DIM_ Tables

The following tables store dimension data in the Analyze Direct schema:

• DIM_ASSESSMENT_QUESTION
• DIM_ASSESSMENT_QUESTION_OPTION
• DIM_CATEGORY
• DIM_CHAPTER
• DIM_CLASS
• DIM_COMPETENCY_CATEGORY
• DIM_COMPETENCY_DEFINITION
• DIM_COUPON
• DIM_COURSE
• DIM_COURSE_BUNDLE
• DIM_COURSE_COMPETENCY_LEVEL
• DIM_COURSE_MESSAGE_TEMPLATE
• DIM_COURSE_RESOURCE
• DIM_COURSE_SKILL
• DIM_COURSE_UPLOAD_DEFINITION
• DIM_CURRICULUM
• DIM_CURRICULUM_GROUP
• DIM_DEPARTMENT
• DIM_ENROLLMENT_KEY
• DIM_EVALUATION_QUESTION
• DIM_EXTENSION_PRICE
• DIM_EXTERNAL_TRAINING_TEMPLATE
• DIM_GROUP
• DIM_JOB_ROLE
• DIM_LESSON
• DIM_LESSON_COMPETENCY_LEVEL
• DIM_PRICE
• DIM_ROLE
• DIM_SESSION
• DIM_SYNC_INFO
• DIM_TAG
• DIM_USER
• DIM_VENUE

FACT_

Fact tables contain data for aggregation and evaluation. In addition to measurement data, a fact table always contains a surrogate key for each of its associated dimensions. To help users count unique records, Analyze Direct includes a count field (for example, COURSE_RATING_COUNT) that contains a constant value of 1 for each record.

List of FACT_ Tables

The following tables store fact data used for reporting and aggregation.

• FACT_ATTEMPT
• FACT_ATTEMPT_SUMMARY
• FACT_ATTENDANCE
• FACT_AWARDED_CERTIFICATE
• FACT_CHECKLIST_REVIEW
• FACT_CHECKLIST_REVIEW_STEP
• FACT_COURSE_BUNDLE_ENROLLMENT
• FACT_COURSE_ENROLLMENT
• FACT_COURSE_ENROLLMENT_EXCEPTIONS
• FACT_COURSE_RATING
• FACT_COURSE_UPLOAD
• FACT_CURRICULUM_ENROLLMENT
• FACT_CURRICULUM_ENROLLMENT_EXCEPTIONS
• FACT_EVALUATION_ANSWER
• FACT_EXTERNAL_TRAINING_SUBMISSION
• FACT_LEARNING_PATH
• FACT_LESSON_ENROLLMENT
• FACT_LESSON_ENROLLMENT_EXCEPTIONS
• FACT_LOGIN
• FACT_LOGIN_SUMMARY
• FACT_QUESTION_ANSWER
• FACT_QUESTION_RESULT
• FACT_SELF_ASSESSMENT
• FACT_SELF_ASSESSMENT_SKILL
• FACT_SESSION_ENROLLMENT
• FACT_SESSION_ENROLLMENT_EXCEPTIONS
• FACT_SHOPPING_CART_ITEM
• FACT_SHOPPING_CART_TRANSACTION
• FACT_USER_COMPETENCY

BRG_

Bridge tables support many-to-many relationships.

List of BRG_ Tables

The following tables define many-to-many relationships between entities:

• BRG_CATEGORY_ANCESTOR
• BRG_COMPETENCY_CATEGORY_ANCESTOR
• BRG_COURSE_BUNDLE_COURSE
• BRG_COURSE_BUNDLE_ENROLLMENT_COURSE_ENROLLMENT
• BRG_COURSE_BUNDLE_TAG
• BRG_COURSE_TAG
• BRG_CURRICULUM_ENROLLMENT_COURSE_ENROLLMENT
• BRG_CURRICULUM_ENROLL_COURSE_ENROLL_EXCEPTIONS
• BRG_CURRICULUM_GROUP_COURSE
• BRG_CURRICULUM_TAG
• BRG_DEPARTMENT_ANCESTOR
• BRG_ENROLLMENT_KEY_COURSE
• BRG_ENROLLMENT_KEY_USER
• BRG_JOB_ROLE_SKILL
• BRG_LEARNING_PATH_COURSE
• BRG_MANAGER_HIERARCHY
• BRG_MANAGER_USER
• BRG_SESSION_INSTRUCTOR
• BRG_SHOPPING_CART_ENROLLMENT_KEY
• BRG_USER_GROUP
• BRG_USER_ROLE

SEC_

Security tables support row-level access control. For example, SEC_DEPARTMENT_ADMIN contains Admin user IDs and the list of department IDs the Admin manages. When applicable, a table can join to a security table and apply a security filter.

List of SEC_ Tables

The following tables control row-level visibility for reporting:

• SEC_COURSE_BUNDLE_VISIBILITY
• SEC_COURSE_VISIBILITY
• SEC_CURRICULUM_VISIBILITY
• SEC_DEPARTMENT_ADMIN
• SEC_ECOMMERCE_ADMIN
• SEC_GROUP_ADMIN

Common Table Columns and Concepts

The following columns appear across multiple tables and support consistent reporting and auditing.

• X_COUNT: A constant value of 1 for each fact record.
• ADDED_BY_USER: Full name of the LMS user resolved from LMS audit data. Denormalized to the record for performance and ease of use.
• EDITED_BY_USER: Full name of the LMS user resolved from LMS audit data. Denormalized to the record for performance and ease of use.
• IS_DELETED: Usually resolved from the LMS IS_DELETED field.

Changes From the LMS Schema

Analyze Direct does not exactly match LMS terminology and schema.

Course, Curriculum, and Course Bundle

In Analyze Direct, a course is only an Online Course or an Instructor Led Course. The course table contains only those types. The same is true for course enrollments, course categories, and related tables. This change was made to simplify understanding of the relationships between courses and their containers (Curricula and Course Bundles).

Ancestor Tables (Hierarchies)

Department and category hierarchies are represented in ancestor bridge tables that link back to the department or category. These tables are useful for filtering departments and sub-departments that match a given name.

Data Dictionary

Data dictionaries define the structure and contents of a dataset, allowing for greater understanding of fields and data values. You can read more about specific Analyze dictionaries in the Absorb Analyze Data Dictionaries article. Note that you must be signed in to view the support documentation.

Data Models in Analyze Direct

This section details the table contents and table relationships in Analyze Direct data models, such as course enrollments and logins.

For full details of the data model diagrams, refer to the Analyze Direct Data Models file in the Attachments section of this article. 

Course Enrollments

This diagram shows the primary tables and relationships used to report on course enrollments.

Curriculum Enrollments

This diagram shows the primary tables and relationships used to report on Curriculum enrollments.

Logins

This diagram shows the primary tables and relationships used to report on logins.

Awarded Certificates

This diagram shows the primary tables and relationships used to report on awarded Certificates.

Assessment Activity

This diagram shows the primary tables and relationships used to report on assessment activity.

Attendance

This diagram shows the primary tables and relationships used to report on attendance.

Checklist Activity

This diagram shows the primary tables and relationships used to report on checklist activity.

Course Evaluations

This diagram shows the primary tables and relationships used to report on course evaluations.

Course Ratings

This diagram shows the primary tables and relationships used to report on course ratings.

E-Commerce Transactions

This diagram shows the primary tables and relationships used to report on e-commerce transactions.

E-Commerce Transaction Details

This diagram shows the primary tables and relationships used to report on e-commerce transaction details.

Learner Competencies

This diagram shows the primary tables and relationships used to report on Learner competencies.

Helpful Scripts

In Snowflake, you can use scripts to complete tasks, including user creation and user management. A selection of these scripts is outlined below. Use the reference links to learn more on the Snowflake site.

Create New User Script

Use this script to create a new Snowflake user and grant the consumer role needed to query Analyze Direct.

Reference: https://docs.snowflake.com/en/sql-reference/sql/create-user.html

USE ROLE ANALYZE_DIRECT_ADMIN_ROLE;

-- Create a new user. The following two statements must be run to enable a user
-- to select from the Analyze Direct database.

-- 1. Create a new user
CREATE USER sample_user
  LOGIN_NAME = 'sample.user@example.com'
  FIRST_NAME = 'Sample'
  LAST_NAME = 'User'
  PASSWORD = '<REPLACE_WITH_SECURE_PASSWORD>'
  DEFAULT_ROLE = 'ANALYZE_DIRECT_CONSUMER_ROLE'
  DEFAULT_WAREHOUSE = 'ANALYZE_DIRECT_WAREHOUSE'
  EMAIL = 'sample.user@example.com'
  MUST_CHANGE_PASSWORD = true
  COMMENT = 'Sample user';

-- 2. Enable user to select from Analyze Direct
GRANT ROLE ANALYZE_DIRECT_CONSUMER_ROLE TO USER sample_user;

List Users

Use this script to view which Users are assigned the consumer role.

Reference: https://docs.snowflake.com/en/sql-reference/sql/show-grants.html

USE ROLE ANALYZE_DIRECT_ADMIN_ROLE;

-- Retrieve a list of users of the consumer role
SHOW GRANTS OF ROLE ANALYZE_DIRECT_CONSUMER_ROLE;

Get User Details

Use this script to retrieve details for a specific Snowflake user.

Reference: https://docs.snowflake.com/en/sql-reference/sql/desc-user.html

USE ROLE ANALYZE_DIRECT_ADMIN_ROLE;

-- Get full details of a user
DESC USER sample_user;

Disable or Enable User

Use this script to disable a user account or re-enable access.

Reference: https://docs.snowflake.com/en/sql-reference/sql/alter-user.html

USE ROLE ANALYZE_DIRECT_ADMIN_ROLE;

-- Disable a user
ALTER USER sample_user SET DISABLED = true;

-- Enable a user
ALTER USER sample_user SET DISABLED = false;

Delete User

Use this script to permanently delete a user account.

Reference: https://docs.snowflake.com/en/sql-reference/sql/drop-user.html

USE ROLE ANALYZE_DIRECT_ADMIN_ROLE;

-- Delete a user permanently
DROP USER IF EXISTS sample_user;

List Resource Monitors

Use this script to list Snowflake resource monitors available in the account.

Reference: https://docs.snowflake.com/en/sql-reference/sql/show-resource-monitors.html

USE ROLE ANALYZE_DIRECT_ADMIN_ROLE;

-- List resource monitors
SHOW RESOURCE MONITORS;