When first accessing Analyze Direct, you must configure your account and data before you can use this feature. This article explains how to get started with Analyze Direct, including how to connect to your data and customize your account. You need your account URL, username, and password to complete these steps. These values are provided separately.

Example Parameters

• URL or Server: abc12345.snowflakecomputing.com
• Username: FirstName_LastName
• Password: user-specified password
• Warehouse: ANALYZE_DIRECT_WAREHOUSE

How to Connect Your Data

Once your account is provisioned, there are several ways to connect to your data. Absorb supports multiple data visualization tools you can use to complete this process. While each tool is unique, most require the same connection parameters.

Power BI

Use Power BI Desktop to connect to Snowflake and build dashboards using Analyze Direct tables. Follow the steps below to connect: 

1. Open Power BI Desktop.
2. Select Get Data.
3. Search for Snowflake under certified connectors.
4. Select Connect.
5. Enter your Server (for example, abc12345.snowflakecomputing.com).
6. Enter your Warehouse (for example, ANALYZE_DIRECT_WAREHOUSE).
7. Select OK.
8. Enter your username and password.
9. Select Connect.
10. Select the tables to include in your dashboard.

Snowflake Interface

Connect directly to Snowflake in a web browser to query Analyze Direct data. Follow the steps below to connect: 

1. In your web browser, navigate to your account URL (for example, https://abc12345.snowflakecomputing.com/).
2. Log in with your username and password.
3. Open a new worksheet.
4. Run SQL queries to retrieve data.

 

More information about Snowflake UI can be found here. 

SnowSQL

Use SnowSQL, Snowflake’s command-line interface (CLI), to connect to Snowflake and query Analyze Direct data.

More information about SnowSQL can be found here. 

Popular Queries

In this section you will find a selection of popular queries that can be used to retrieve data from Analyze Direct.

Curricula and Courses Within

This script queries information about Curricula and Courses, including enrollments.

-- This script aims to show how to query for Curricula (w enrollments) and Courses
--(and enrollments) Within the Curriculum, includes some user details
SELECT DISTINCT
U.USER_KEY,
FIRST_NAME,
LAST_NAME,
U.FULLNAME,
USERNAME,
D.DEPARTMENT_KEY,
D.DEPARTMENT_NAME,
DCU.CURRICULUM_NAME,
DCO.COURSE_TYPE_NAME,
DCO.COURSE_NAME,
FCE.EN_STATUS AS "CURRICULUM STATUS",
FCOE.EN_STATUS AS "COURSE STATUS",
FCE.PROGRESS AS "CURRICULUM PROGRESS",
FCE.SCORE AS "CURRICULUM SCORE",
FCOE.SCORE AS "COURSE SCORE",
FCOE.PROGRESS AS "COURSE PROGRESS",
FCE.DATE_ADDED AS "DATE CURRICULUM ENROLLED",
FCOE.DATE_ADDED AS "DATE COURSE ENROLLED",
FCE.DATE_COMPLETED AS "DATE CURRICULUM COMPLETED",
FCOE.DATE_COMPLETED AS "DATE COURSE COMPLETED",
FROM DIRECT.FACT_CURRICULUM_ENROLLMENT FCE --starting from Curriculum enrollments
JOIN DIRECT.DIM_USER U ON U.USER_KEY = FCE.USER_KEY --Joining the users
JOIN DIRECT.DIM_CURRICULUM DCU ON DCU.CURRICULUM_KEY = FCE.CURRICULUM_KEY --Including Curriculum Details with this join
JOIN DIRECT.BRG_CURRICULUM_ENROLLMENT_COURSE_ENROLLMENT BCECE ON BCECE.CURRICULUM_ENROLLMENT_KEY = FCE.CURRICULUM_ENROLLMENT_KEY
-- This bridge connects the course enrollments with the Curriculum enrollments to ensure they were enrolled in the course through the Curriculum
JOIN DIRECT.FACT_COURSE_ENROLLMENT FCOE ON FCOE.COURSE_ENROLLMENT_KEY = BCECE.COURSE_ENROLLMENT_KEY -- This join now includes the course enrollments from said Curriculum
JOIN DIRECT.DIM_COURSE DCO ON DCO.COURSE_KEY = FCOE.COURSE_KEY --Including course details
JOIN DIRECT.DIM_DEPARTMENT D ON D.DEPARTMENT_KEY = U.DEPARTMENT_KEY
WHERE FCE.IS_DELETED = 0 --removing deleted curriculum enrollments
AND FCOE.IS_DELETED = 0 --removing deleted course enrollments
AND U.IS_DELETED = 0 --removing deleted users
AND u.EN_ACTIVE_STATUS = 'Active' --removing inactive users
AND FCE.IS_ACTIVE = 1 --removing inactive curriculum enrollments
AND FCOE.IS_ACTIVE = 1 --removing inactive course enrollments

Assessment Report - Curriculum Courses and Attempts with Assessments

This script queries data for Assessments within a Curriculum Course. 

SELECT CU.CURRICULUM_NAME,
C.COURSE_NAME,
U.USER_KEY,
FIRST_NAME,
LAST_NAME,
ATTEMPT_SCORE,
ATTEMPT_STATUS,
A.START_DATE,
USERNAME,
LESSON_NAME,
QUESTION_NAME,
FQR.IS_CORRECT,
FQA.ASSESSMENT_RESPONSE
FROM DIRECT.FACT_ATTEMPT A
JOIN DIRECT.DIM_USER U ON U.USER_KEY = A.USER_KEY
JOIN DIRECT.DIM_COURSE C ON C.COURSE_KEY = A.COURSE_KEY
JOIN DIRECT.DIM_LESSON L ON L.LESSON_KEY = A.LESSON_KEY
JOIN DIRECT.FACT_QUESTION_RESULT FQR ON FQR.ATTEMPT_KEY = A.ATTEMPT_KEY
JOIN DIRECT.FACT_QUESTION_ANSWER FQA ON FQA.ASSESSMENT_ACTIVITY_KEY = FQR.ASSESSMENT_ACTIVITY_KEY
JOIN DIRECT.DIM_ASSESSMENT_QUESTION DAQ ON DAQ.QUESTION_KEY = FQR.QUESTION_KEY
JOIN DIRECT.BRG_CURRICULUM_GROUP_COURSE BCGC ON BCGC.course_key = C.COURSE_KEY
JOIN DIRECT.DIM_CURRICULUM_GROUP CG ON CG.CURRICULUM_GROUP_KEY = BCGC.CURRICULUM_GROUP_KEY
JOIN DIRECT.DIM_CURRICULUM CU ON CU.CURRICULUM_KEY = CG.CURRICULUM_KEY

Assessments & Attempt Report (Courses and Assessments included)

This script queries data for Assessments within a Course. 

SELECT

L.LESSON_ID AS "ID",
DCO.COURSE_NAME AS "Course Name",
L.LESSON_NAME AS "Assessments Name",

SUM(FA.ATTEMPT_COUNT) AS "Attempts",
SUM(FA.PASSED_ATTEMPT_COUNT) AS "Passed",
SUM(FA.FAILED_ATTEMPT_COUNT) AS "Failed",
ROUND(AVG (FA.ATTEMPT_SCORE), 2) AS "Avg_Score"



FROM DIRECT.FACT_ATTEMPT FA
JOIN DIRECT.DIM_USER U ON U.USER_KEY = FA.USER_KEY
JOIN DIRECT.DIM_COURSE DCO ON DCO.COURSE_KEY = FA.COURSE_KEY
JOIN DIRECT.DIM_LESSON L ON L.LESSON_KEY = FA.LESSON_KEY
JOIN DIRECT.FACT_LESSON_ENROLLMENT FLE ON FLE.LESSON_ENROLLMENT_KEY = FA.LESSON_ENROLLMENT_KEY


WHERE FA.IS_DELETED = 0
AND DCO.IS_DELETED = 0
AND L.is_deleted = 0
AND FLE.IS_DELETED = 0
-- AND FCE.IS_ACTIVE = 1
-- AND FCE.IS_DELETED = 0
AND U.IS_DELETED = 0 --removing deleted users
AND U.EN_ACTIVE_STATUS = 'Active' --removing inactive users

GROUP BY ALL

More information about Analyze Direct data tables and models can be found in the Data Structures and Models in Analyze Direct article.