Read to know how account administrators can create custom reports from Adobe Connect database.

Building custom reports using star schema views

Adobe Connect uses a database to store information about users, content, courses, and meetings. User activity populates the database. You can use tools such as Adobe® ColdFusion® Studio and Business Objects Crystal Reports to query star schema views and view the data. You can also use SQL-based tools such as SQL Query Analyzer.

The following Adobe Connect applications can output data to reports:

Adobe Connect Meeting

Meeting attendance, meeting duration, and meeting content.

Adobe Presenter

Content views, slide views, and presentation views.

Adobe Connect Training

Course management information such as course attendee statistics, content viewing statistics, and quiz results.

Märkus.

In addition, you can run reports from the Adobe Connect Central web application and either view or download them in CSV format. For more information, see Generating reports in Adobe Connect Central.

SCO fact

Column

Description

dim_sco_details_sco_id

SCO id

dim_sco_details_sco_version

SCO version

max_retries

Maximum number of retries

owner_user_id

User id of the SCO owner

disk_usage_kb

Disk usage in kilobytes

passing_score

Passing score

max_possible_score

Maximum score

views

Number of views

unique_viewers

Number of unique users who have viewed the SCO

slides

Number of slides

questions

Number of questions

max_score

Maximum score

min_score

Minimum score

average_score

Average score

average_passing_score

Average passing score

total_registered

Average failing score

total_participants

Total registered users

account_id

Total participants

SCO details dimension

Column

Description

sco_id

SCO id

sco_version

SCO version

sco_name

Name

sco_description

Description

sco_type

SCO type

sco_int_type

Integer type

is_content

Is SCO a content SCO?

url

URL

parent_name

Name of parent SCO

parent_sco_id

SCO id of parent SCO

parent_type

Type of parent SCO

date_sco_created

Date created

date_sco_modified

Date modified

sco_start_date

Start date

sco_end_date

End date

version_start_date

Version start date

version_end_date

Version end date

sco_tag_id

Tag id

passing_score

Passing score

max_possible_score

Maximum possible score

linked_sco_id

Id of a linked SCO

linked_type

Type of a linked SCO

owner_user_id

Owner user id

storage_bytes_kb

Storage bytes in kilobytes

account_id

Account id

Activity fact

Column

Description

dim_activity_details_activity_id

Activity id

score

Score

passed

Passed

completed

Completed

peak_session_users

Peak session users

number_correct

Number correct

number_incorrect

Number incorrect

number_of_questions

Number of questions

number_of_responses

Number of responses

account_id

Account id

Activity details dimension

Column

Description

activity_id

Activity id

dim_sco_details_sco_id

SCO id

dim_sco_details_sco_version

SCO version

dim_users_user_id

User id

dim_sco_details_parent_sco_id

Parent SCO id

score

Score

passed

Passed

completed

Completed

activity_type

Activity type

role

Role

date_activity_started

Date started

date_activity_finished

Date finished

dim_cost_center_id

Cost center id

cost_center_audit_id

Audit id

session_start_date

Session start date

session_end_date

Session end date

attendance_activity

Is attendance activity?

session_id

Session id

account_id

Account id

Curriculum test outs dimension

Column

Description

dim_sco_details_curriculum_sco_id

Curriculum id

dim_sco_details_curriculum_sco_version

Curriculum version

test_out_subject_sco_id

Subject SCO id

test_out_target_sco_id

Target SCO id

test_out_type

Test out type

account_id

Account id

Curriculum prerequisites dimension

Column

Description

dim_sco_details_curriculum_sco_id

Curriculum id

dim_sco_details_curriculum_sco_version

Curriculum version

pre_requisite_subject_sco_id

Subject SCO id

pre_requisite_target_sco_id

Target SCO id

pre_requisite_type

Prerequisite type

account_id

Account id

Curriculum completion requirements dimension

Column

Description

dim_sco_details_curriculum_sco_id

Curriculum id

dim_sco_details_curriculum_sco_version

Curriculum version

completion_subject_sco_id

Subject SCO id

completion_target_sco_id

Target SCO id

completion_requirement_type

Completion requirement type

account_id

Account id

Slide views fact

Column

Description

dim_slide_view_details_slide_view_id

Slide view id

dim_activity_details_activity_id

Activity id

slide_view_display_sequence

Display sequence

account_id

Account id

Slide views details dimension

Column

Description

slide_view_id

Slide view id

date_slide_viewed

Date slide viewed

slide_name

Slide name

slide_description

Slide description

account_id

Account id

Answers fact

Column

Description

dim_answer_details_answer_id

Answer id

dim_activity_details_activity_id

Activity id

dim_question_details_question_id

Question id

answer_display_sequence

Display sequence

answer_score

Score?

answer_correct

Is correct?

account_id

Account id

Answer details dimension

Column

Description

answer_id

Answer id

date_answered

Date answered

response

Response

account_id

Account id

Question fact

Column

Description

dim_sco_details_sco_id

SCO id

dim_sco_details_sco_version

SCO version

dim_question_details_question_id

Question id

number_correct

Number of correct answers

number_incorrect

Number of incorrect answers

total_responses

Total responses

high_score

High score

low_score

Low score

average_score

Average score

account_id

Account id

Question details dimension

Column

Description

question_id

Question id

question_display_sequence

Display sequence

question_description

Description

question_type

Question type

account_id

Account id

Question responses dimension

Column

Description

dim_question_details_question_id

Question id

response_display_sequence

Response display sequence

response_value

Value

response_description

Description

account_id

Account id

Groups dimension

Column

Description

group_id

Group id

group_name

Group name

group_description

Group description

group_type

Group type

account_id

Account id

User groups dimension

Column

Description

user_id

User id

group_id

Group id

group_name

Group name

account_id

Account id

User dimension

Column

Description

user_id

User id

login

Login

first_name

First name

last_name

Last name

email

Email address

user_descrription

User description

user_type

User type

most_recent_session

Most recent session date

session_status

Session status

manager_name

Manager name

disabled

Disabled

account_id

Account id

custom_field_1

Custom field 1 value

custom_field_2

Custom field 2 value

custom_field_3

Custom field 3 value

custom_field_4

Custom field 4 value

custom_field_5

Custom field 5 value

custom_field_6

Custom field 6 value

custom_field_7

Custom field 7 value

custom_field_8

Custom field 8 value

custom_field_9

Custom field 9 value

custom_field_10

Custom field 10 value

Custom field names dimension

Column

Description

dim_column_name

Custom field column name

custom_field_name

Custom field name

account_id

Account id

Cost centers dimension

Column

Description

cost_center_id

Cost center id

cost_center_name

Cost center name

cost_center_description

Cost center description

Building custom reports from legacy database views

Märkus.

Adobe Connect version 7 introduced star schema views that you can query to build custom reports. The legacy database views are still supported, but the star schema views are more standardized and robust.

Adobe Connect uses a database to store information about users, content, courses, and meetings. User activity populates the database. You can use tools such as Business Objects Crystal Reports to query the database and view the data. You can also use SQL-based tools such as SQL Query Analyzer.

The following Adobe Connect applications can output data to reports:

Adobe Connect Meeting

Meeting attendance, meeting duration, and meeting content.

Adobe Presenter

Content views, slide views, and presentation views.

Adobe Connect Training

Course management information such as course attendee statistics, content viewing statistics, and quiz results.

View relationships between database views

To see the relationships between the database views, use a diagramming tool that connects to your database.

The following are not supported:

  • views that are not identified in this document
  • altering the views that are identified in this document
  • direct access to the underlying database schema
Arrows depict the entity relationships among the seven reporting views.
Arrows depict the entity relationships among the seven reporting views.

  1. Use a diagramming tool that connects to your database to see the relationships between the database views.

EXT_TRANSACTIONS

A unique transaction ID is generated each time a user interacts with an object. The EXT_TRANSACTIONS view returns the data listed in the following table:

Column

Data type

Description

TRANSACTION_ID

INT

Unique ID for this transaction.

LOGIN

NVARCHAR

Name of user who carried out this transaction.

URL

NVARCHAR

Object that the user interacted with.

STATUS

NVARCHAR

Can be passed, failed, complete, or in-progress.

SCORE

FLOAT

How the user scored.

DATE_VISITED

DATETIME

Date this transaction was taken or viewed.

Sample query and data

The following query returns the data in the following table:

select * from ext_transactions where url = '/p63725398/' order by login, date_visited asc;

TRANSACTION_ID

LOGIN

URL

STATUS

SCORE

DATE_VISITED

10687

test1-lnagaraj@test.enang.com

/p63725398/

in-progress

0.0

2006-12-15 00:56:16.500

10688

test1-lnagaraj@test.enang.com

/p63725398/

in-progress

0.0

2006-12-15 00:56:16.500

10693

test1-lnagaraj@test.enang.com

/p63725398/

in-progress

0.0

2006-12-15 00:58:23.920

10714

test1-lnagaraj@test.enang.com

/p63725398/

in-progress

10.0

2006-12-15 01:09:20.810

10698

test2-lnagaraj@test.enang.com

/p63725398/

in-progress

10.0

2006-12-15 01:00:49.483

10723

test3-lnagaraj@test.enang.com

/p63725398/

in-progress

10.0

2006-12-15 01:11:32.153

10729

test3-lnagaraj@test.enang.com

/p63725398/

completed

20.0

2006-12-15 01:12:09.700

Query notes

The EXT_TRANSACTIONS view returns all existing transactions for a given user and training session. To view the latest transaction, check the maximum DATE_VISITED value.

You can filter on the STATUS and URL fields to get a list of passing users for a specific training session, for example:

select * from ext_transactions where url = '/p31102136/' and status = 'user-passed' order by login, date_visited asc;

Generating data

User actions that generate data in this view:

  • Attending a meeting

  • Viewing a piece of content

  • Taking a training session (a course or curriculum)

Excluded data

  • Certificate number, which does not exist in the database

  • Maximum score, which is often unavailable

EXT_TRANSACTIONS_VIEWS

The EXT_TRANSACTIONS_VIEWS view retrieves data about the slides or pages that users view.

Column

Data type

Description

TRANSACTION_ID

INT

Unique ID for this transaction (can be merged with TRANSACTION_DETAILS to summarize by URL).

PAGE

NVARCHAR

Slide or page number that was viewed.

DATE_VIEWED

DATETIME

Date this view occurred.

Sample query and data

The following query returns the data in the following table:

select * from ext_transaction_views where transaction_id = 10702 order by page asc;

TRANSACTION_ID

PAGE

DATE_VISITED

10702

0

2006-12-15 01:01:13.153

10702

1

2006-12-15 01:01:18.233

10702

2

2006-12-15 01:01:59.840

10702

3

2006-12-15 01:02:20.717

Generating data

Data is generated in this view whenever a user views content or a training session.

EXT_USERS

The EXT_USERS view lists users and associated profile attributes:

Column

Data type

Description

LOGIN

NVARCHAR

Unique user identifier.

NAME

NVARCHAR

Unique user name.

EMAIL

NVARCHAR

Unique email address.

MANAGER

NVARCHAR

The login of the manager. Manager is always set to NULL.

TYPE

NVARCHAR

User or guest. Type is always set to user.

Sample query and data

The following query returns the data in the following table:

select * from ext_users;

LOGIN

NAME

EMAIL

MANAGER

TYPE

test4-lnagaraj@test.enang.com

test4 laxmi

test4-lnagaraj@test.enang.com

NULL

user

test7-lnagaraj@test.enang.com

TEST7 laxmi

test7-lnagaraj@test.enang.com

NULL

user

Generating data

Data is updated in this view whenever a guest or user is created, updated, or deleted.

Excluded data

 

  • Password, which is not stored in plain text.

  • Time zone and language, which are not available in human readable form. For example, PST is 323.

  • Last login, as it is resource intensive to calculate. Instead, use a max(date_visited) query from the EXT_TRANSACTIONS view to retrieve this data.

  • Active session, which is data from the EXT_TRANSACTION view. Instead, use a STATUS='IN-PROGRESS' query to retrieve this data.

  • Deleted users do not appear in the EXT_USERS view. Deleted users continue to appear in the EXT_TRANSACTION view.

  • Data on groups is not included in this view.

  • Data on new and pre-defined user custom fields. This information is available for each user in the EXT_USER_FIELDS view.

EXT_USER_FIELDS

The EXT_USER_FIELDS view lists new and predefined custom fields for a specific user. It also lists custom fields for users who are converted to guests.

Column

Data type

Description

LOGIN

NVARCHAR

Unique user identifier.

NAME

NVARCHAR

Field name such as telephone number.

VALUE

NVARCHAR

Field value such as 415.555.1212.

Sample query and data

The following query returns the data in the following table:

select * from ext_user_fields where login = 'test4-lnagaraj@test.enang.com';

LOGIN

NAME

VALUE

test4-lnagaraj@test.enang.com

{email}

test4-lnagaraj@test.enang.com

test4-lnagaraj@test.enang.com

{first-name}

test4

test4-lnagaraj@test.enang.com

{last-name}

laxmi

test4-lnagaraj@test.enang.com

{x-job-title}

sw engr 4

test4-lnagaraj@test.enang.com

{x-direct-phone}

NULL

test4-lnagaraj@test.enang.com

{x-direct-phone-key}

NULL

test4-lnagaraj@test.enang.com

SSN

777

Generating data

Actions that generate data in this view: adding, creating, or updating new or predefined custom fields for one or more users.

EXT_USER_GROUPS

The EXT_USER_GROUPS view lists data about groups and associated group members. The EXT_USER_GROUPS view uses the data listed in the following table:

Column

Data type

Description

LOGIN

NVARCHAR

Name of user.

NAME

NVARCHAR

Name of group.

Sample query and data

The following query returns the data in the following table:

select * from ext_user_groups where login = 'lnagaraj@adobe.com';

LOGIN

NAME

lnagaraj@adobe.com

{admins}

lnagaraj@adobe.com

{authors}

lnagaraj@adobe.com

{everyone}

lnagaraj@adobe.com

Laxmi Nagarajan

Query notes

Nesting of multiple groups is supported in version 5.1 and later. For example, if group A contains group B, and you are in group B, you are listed as a member of A.

Built-in groups, like the Administrators group, use code names in the schema, as in the following SQL query: SELECT * FROM EXT_USER_GROUPS where group='{admins}. The code name distinguishes built-in groups from user-defined groups.

Generating data

User actions that generate data in this view:

  • Creating, updating, or deleting a group

  • Changing group membership

EXT_OBJECTS

The EXT_OBJECTS view lists all system objects (such as meetings, content, courses) and their attributes.

Column

Data type

Description

URL

NVARCHAR

Unique identifier for the object.

TYPE

NVARCHAR

Either a presentation, course, FLV file, SWF file, image, archive, meeting, curriculum, folder, or event.

NAME

NVARCHAR

Object name as it appears in the content listing.

DATE_BEGIN

DATETIME

The date on which the object is scheduled to begin.

DATE_END

DATETIME

The date on which the object is scheduled to end.

DATE_MODIFIED

DATETIME

The date this object was modified.

DESCRIPTION

NVARCHAR

Object summary information entered when creating a meeting, content, course, or other object type.

Sample query and data

The following SQL query returns the data in the following table:

select * from ext_objects order by type asc;

URL

TYPE

NAME

DATE_BEGIN

DATE_END

DATE_MODIFIED

DESCRIPTION

/p79616987/

course

test api

2006-12-08 23:30:00.000

NULL

2006-12-08 23:36:55.483

NULL

/p47273753/

curriculum

test review curric

2006-12-14 21:00:00.000

NULL

2006-12-14 21:00:30.060

NULL

/tz1/

meeting

{default-template}

2006-12-12 19:15:00.000

2006-12-12 20:15:00.000

2006-12-12 19:25:07.750

release presentation

/p59795005/

presentation

ln-QUIZ-TEST1

NULL

NULL

2006-12-15 00:43:19.797

managers meeting

Query notes

You can get all objects of a specific type by filtering on the TYPE field. For example, the following SQL query filters for courses and curriculums:

select * from ext_objects where type in ('course', 'curriculum');

Use the following SQL query to return a list of available system types:

select DISTINCT (type) from ext_objects;

Generating data

User actions that generate data in this view:

  • Creating or updating a meeting, course, or curriculum

  • Uploading or updating content

Excluded data

  • Duration, which you can use date_end- date_begin to calculate.

  • Size on disk, which exposes business rules regarding copies versus originals

  • Folder ID

  • Deleted objects do not appear in the EXT_OBJECTS view. Deleted objects do exist in the EXT_TRANSACTION view.

See töö on litsentseeritud Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported litsentsiga  Süsteemi Creative Commons tingimused ei kehti Twitter™-i ja Facebooki postitustele.

Juriidilised märkused   |   Privaatsuspõhimõtted veebis