User manual MACROMEDIA BREEZE - MANAGING EXTERNAL BREEZE REPORTS

Managing External Breeze Reports Trademarks 1 Step RoboPDF, ActiveEdit, ActiveTest, Authorware, Blue Sky Software, Blue Sky, Breeze, Breezo, Captivate, Central, ColdFusion, Contribute, Database Explorer, Director, Dreamweaver, Fireworks, Flash, FlashCast, FlashHelp, Flash Lite, FlashPaper, Flash Video Encoder, Flex, Flex Builder, Fontographer, FreeHand, Generator, HomeSite, JRun, MacRecorder, Macromedia, MXML, RoboEngine, RoboHelp, RoboInfo, RoboPDF, Roundtrip, Roundtrip HTML, Shockwave, SoundEdit, Studio MX, UltraDev, and WebHelp are either registered trademarks or trademarks of Adobe Systems Incorporated and may be registered in the United States or in other jurisdictions including internationally. Other product names, logos, designs, titles, words, or phrases mentioned within this publication may be trademarks, service marks, or trade names of Adobe Systems Incorporated or other entities and may be registered in certain jurisdictions including internationally. Third-Party Information This guide contains links to third-party websites that are not under the control of Adobe Systems Incorporated, and Adobe Systems Incorporated is not responsible for the content on any linked site. If you access a third-party website mentioned in this guide, then you do so at your own risk. Adobe Systems Incorporated provides these links only as a convenience, and the inclusion of the link does not imply that Adobe Systems Incorporated endorses or accepts any responsibility for the content on those thirdparty sites. © 2006 Adobe Systems Incorporated. All rights reserved. This manual may not be copied, photocopied, reproduced, translated, or converted to any electronic or machine-readable form in whole or in part without written approval from Adobe Systems Incorporated. Notwithstanding the foregoing, the owner or authorized user of a valid copy of the software with which this manual was provided may print out one copy of this manual from an electronic version of this manual for the sole purpose of such owner or authorized user learning to use such software, provided that no part of this manual may be printed out, reproduced, distributed, resold, or transmitted for any other purposes, including, without limitation, commercial purposes, such as selling copies of this documentation or providing paid-for support services. Acknowledgments Editorial Manager: Stephanie Gowin Writing: Jody Bleyle Editing: Mary Ferguson Production Management: Adam Barnett Media Design and Production: Adam Barnett and John Francis Second Edition: January 2006 Adobe Systems Incorporated 601 Townsend St. San Francisco, CA 94103 Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Intended audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 What's new in external Breeze reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Guide to documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Additional resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Contact us . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Chapter 1: Understanding Breeze Reports. . . . . . . . . . . . . . . . . . . . 7 The Breeze database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 About stored data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 About reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Chapter 2: Using Breeze Reporting Views . . . . . . . . . . . . . . . . . . . 9 Database schema views used in reporting . . . . . . . . . . . . . . . . . . . . . . . . . 9 Database views and data relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Unique identifiers and data relationships . . . . . . . . . . . . . . . . . . . . . . . .11 EXT_TRANSACTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 EXT_TRANSACTION_DETAILS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 EXT_TRANSACTIONS_VIEWS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 EXT_USERS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 EXT_USER_FIELDS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 EXT_USER_GROUPS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 EXT_OBJECTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3 4 Contents Introduction Macromedia Breeze supports external reporting through a collection of read-only database views. These database views allow you to develop a wide variety of customized reports for use throughout your enterprise. Intended audience This document is for licensed Breeze customers who want to develop customized reports based on Breeze data. What's new in external Breeze reports In Macromedia Breeze 5.1, the reporting views have been created in the database. (In Breeze 4.1, you had to create the reporting views and install them on Breeze Communication Server.) Guide to documentation In addition to this manual, the following electronic manuals and online help systems are available from the Breeze Documentation Center to help you learn about Breeze: Breeze Manager User Guide describes how to use the administration, presentation, and meeting applications of Breeze. You can access this guide from the Breeze Manager home page, and from the Help link in Breeze Manager. Breeze Presenter User Guide describes how to create Breeze presentations using PowerPoint and publish them to Breeze. You can access this document from the Breeze Manager home page, or from the Breeze menu in PowerPoint by selecting Breeze > Help. 5 Breeze Meeting User Guide for Hosts and Presenters includes information about using the Breeze web application to host online real-time meetings. The documentation includes procedures that demonstrate the simplicity of adding slides, Flash SWF files, images, and live audio and video to your presentation. You can access this guide from the Breeze Manager home page, or from the Help menu within a Breeze meeting room (when you enter as a Presenter) by selecting Meeting > Help. Breeze Meeting User Guide for Participants includes information relevant to users participating in an online real-time meeting. Breeze Meeting offers meeting participants a variety of options to make their experience truly participatory, including the ability to send messages and questions to Presenters and to each other, and the ability to participate through live video and audio. You can access this guide from the Breeze Manager home page, or from the Help menu within a Breeze meeting room (when you enter as a Participant) by selecting Meeting > Help. Breeze Integration Guide includes information necessary to make application programming interface (API) calls on the Breeze server from your external system, such as a web application. It also explains how to interpret the XML results that Breeze returns. In addition, the documentation contains reference material about each API and its parameters. Additional resources Additional documentation, white papers, and articles are available at the following websites: Breeze Resource Center This site, at www.macromedia.com/resources/breeze, is updated regularly with tutorials, simulations, best-practices information, and links to procedures. Breeze Support Center This site, at www.macromedia.com/support/breeze, contains current support information, including TechNotes, helpful Breeze presentations, and support program details. Contact us For additional assistance, please contact Technical Support. 6 Introduction CHAPTER 1 Understanding Breeze Reports The Macromedia Breeze platform allows users to share online meetings, presentations, and training courses over the Internet. Reporting access to Macromedia Breeze data allows you to track learning effectiveness, participation, system usage, and content analysis. 1 The Breeze database Macromedia Breeze requires a database to store information about users, content, courses, meetings, and reports. The database is created as part of the Breeze installation and setup procedure by the Breeze database engine or Microsoft SQL Server 2000. User activity (such as viewing or submitting data) within Breeze populates the database. The collected data can then be viewed in customized reports using external tools such as Crystal Reports, Macromedia ColdFusion Studio, or SQL-based tools. About stored data The following data from Breeze sessions can be captured in Breeze reports. Breeze Presentation: content views, slide views, and presentation views Breeze Training: course management facilities such as course attendee statistics, content viewing statistics, and quiz results Breeze Meeting: meeting attendance, meeting duration, and meeting content About reports Breeze reports are available out of the box; you can also create customized reports. You can export the underlying report data in CSV format. 7 8 Understanding Breeze Reports CHAPTER 2 Using Breeze Reporting Views This chapter describes each of the database schema views used in Macromedia Breeze 5.1. The external views allow you to develop custom reports that are based on user activity within Macromedia Breeze. This chapter describes the following: 2 Column name data type Length of data contained in each column Primary keys used to map relationships Reporting data is accessible using an SQL-compliant tool. Database reporting views are intended for read-only access and usage. NO T E Database schema views used in reporting Breeze supports external reporting through a set of integrated database views. You use SQL queries to develop customized reports. The following database views are supported in Breeze 5.1: EXT_TRANSACTIONS EXT_TRANSACTION_DETAILS EXT_TRANSACTION_VIEWS EXT_USERS EXT_USER_FIELDS EXT_USER_ GROUPS EXT_OBJECTS 9 The following are not supported: Use of any other views besides the ones identified in this chapter Any alterations of these views Direct access to the underlying database schema Database views and data relationships The following entity relationship diagram shows the seven external reporting views. The arrows depict how data maps back to the view it was created from. You can view data relationships within any diagramming tool that allows you to connect to databases. 10 Using Breeze Reporting Views Unique identifiers and data relationships Relationships between views are defined by unique identifiers, which are columns that share the same name. The following table shows the columns that are mapped between views. Unique identifiers URL TRANSACTION_ID View(s) used in EXT_OBJECTS EXT_TRANSACTIONS EXT_TRANSACTIONS EXT_TRANSACTIONS _DETAILS EXT_TRANSACTIONS _VIEWS EXT_USERS EXT_TRANSACTIONS EXT_USER_GROUPS EXT_USER_FIELDS LOGIN EXT_TRANSACTIONS A unique transaction ID is generated each time a user accesses an object. The EXT_TRANSACTIONS view uses the data listed in the following table: Column name TRANSACTION_ID LOGIN URL STATUS SCORE DATE_VISITED Data type INT NVARCHAR NVARCHAR NVARCHAR FLOAT DATETIME Description Unique ID for this transaction. The name of user who accessed this transaction. The object that the user accessed. Can be passed, failed, complete, or in-progress. How the user scored. The date this transaction was taken or viewed. The following user actions generate data in this view: Attending a meeting Viewing a piece of content Taking a course Database views and data relationships 11 EXT_TRANSACTIONS sample entries TRANSACTION _ID 26 122 LOGIN URL STATUS SCORE DATE_ VISITED 12/15/2004 11:17:38 PM 12/06/ 2004 11:18:48 PM 12/05/2004 11:22:44 AM brice@adobe.com /r18595812/ completed 8.0 ccarr@adobe.com /p7096354/ inprogress lblue@adobe.com /p8145026/ userpassed 0.0 150 10.0 Constraints The EXT_TRANSACTIONS view excludes the following fields: Certificate number, which does not exist in the database Max score, which is often unavailable EXT_TRANSACTION_DETAILS The EXT_TRANSACTIONS_DETAILS view manages questions and answers per user transcript. This view retrieves data from meeting polls, user forms, and quizzes. The EXT_TRANSACTIONS_DETAILS view uses the data listed in the following table: Column name TRANSACTION_ID QUESTION RESPONSE SCORE Data type INT NVARCHAR NVARCHAR FLOAT Description Unique ID for this transaction Text of the question User's response to the question Number of points awarded for the response A multiple-answer question is returned as one row for every answer chosen. For example, when the user answers 1 and 4, there are two separate rows. One row contains the value 1 and the other row contains the value 4. Data is generated in this view whenever a user does any of the following: Answers a question in a quiz within a course Answers a poll within a meeting Adds a custom form to a meeting or course 12 Using Breeze Reporting Views EXT_TRANSACTION_DETAILS sample entries TRANSACTION_ID QUESTION 334 340 531 What is the RAM requirement? What is the RAM requirement? How many current users are there? RESPONSE SCORE 1 1 1 10.0 10.0 0.0 Constraints The EXT_TRANSACTION_DETAILS view excludes the following fields: Correct or Incorrect, which can be determined by checking for a non-zero score Date answered, which does not exist in the version 5.1 schema EXT_TRANSACTIONS_VIEWS This view manages data about the slides or pages viewed by users. The EXT_TRANSACTIONS_VIEWS view uses the data listed in the following table: Column name TRANSACTION_ID PAGE DATE_VIEWED Data type INT NVARCHAR DATETIME Description Unique ID for this transaction (can be merged with TRANSACTION_DETAILS to summarize by URL) The slide or page number that was viewed The date this view occurred Data is generated in this view whenever a user views content or a course. EXT_TRANSACTIONS_VIEWS sample entries TRANSACTION_ID PAGE 334 340 531 11 2 3 DATE_VIEWED 12/15/2004 11:17:38 PM 01/06/2004 12:17:38 PM 02/11/2004 08:17:38 AM Database views and data relationships 13 EXT_USERS This view lists users and associated profile attributes. The EXT_USERS view uses data listed in the following table: Column name LOGIN NAME EMAIL MANAGER TYPE Data type NVARCHAR NVARCHAR NVARCHAR NVARCHAR NVARCHAR Description Unique user identifier. Unique user name. User's unique email address. The login of the user's manager. Manager is always set to NULL in version 5.1. User or guest. Type is always set to user in version 5.1. Data is generated in this view whenever a user creates, updates or deletes a user. EXT_USERS sample entries LOGIN brice@adobe.com ccarr@adobe.com NAME system admin IT manager EMAIL brice@adobe.com ccarr@adobe.com MANAGER (null) (null) TYPE user user Constraints The EXT_USERS view excludes the following fields: 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, which is too resource-intensive to calculate. Instead, you can retrieve this data using a max(date_visited) query from the EXT_TRANSACTIONS view. Active session, which is data from the EXT_TRANSACTION view. Instead, you can retrieve this data by executing a STATUS='IN-PROGRESS' query. Deleted users do not appear in the EXT_USERS view. Deleted users continue to exist in the EXT_TRANSACTION view. 14 NO T E Using Breeze Reporting Views EXT_USER_FIELDS This view lists custom fields that can be used for each user. The EXT_USERS_FIELDS view uses data listed in the following table: Column name LOGIN NAME VALUE Data type NVARCHAR NVARCHAR NVARCHAR Description Unique user identifier. Field name such as telephone number. Field value such as 415.555.1212. Data is generated in this view whenever a user does any of the following: Creates, updates, or deletes a group Changes group membership attributes EXT_USER_FIELDS sample entries LOGIN brice@adobe.com ccarr@adobe.com lblue@adobe.com NAME {admins} {authors} {course-admins} VALUE 617-555-1212 415-555-1212 408-555-1212 EXT_USER_GROUPS This view lists data about groups and associated group members. The EXT_USER_GROUPS view uses the data listed in the following table: Column name LOGIN NAME Data type NVARCHAR NVARCHAR Description Name of user. Name of group. Data is generated in this view whenever a user does any of the following: Creates, updates or deletes a group Changes group membership Database views and data relationships 15 EXT_USER_GROUPS sample entries LOGIN brice@adobe.com ccarr@adobe.com lblue@adobe.com NAME {admins} {authors} {course-admins} Nesting of multiple groups is supported in version 5.1. 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 Account 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. EXT_OBJECTS This view lists all system objects (such as meetings, content, courses, and so on) and their attributes. The EXT_OBJECTS view uses object data listed in the following table: Column name URL TYPE NAME DATE_BEGIN DATE_END DATE_MODIFIED DESCRIPTION Data type NVARCHAR NVARCHAR NVARCHAR DATETIME DATETIME DATETIME NVARCHAR Description Unique identifier for the object. Either a presentation, course, flv, swf, image, archive, meeting, curriculum, folder, or event. Object name as it appears in the content listing. The date on which the object is scheduled to begin. The date on which the object is scheduled to end. The date this object was modified. Object summary information entered when creating a new meeting, content, course or other object type. Data is generated in this view whenever a user does any of the following: Creates a meeting or a course Uploads content 16 Using Breeze Reporting Views EXT_OBJECTS sample entries URL /upgrade/ /standard/ TYPE NAME DATE_ BEGIN (null) (null) DATE_ END (null) (null) (null) DATE_ DESCRIPTION MODIFIED 12/15/2004 11:10:38 PM IT presentation presentation plugin upgrade meeting plugin 11/14/2004 QA meeting 10:17:39 PM 09/11/2004 release 08:15:38 AM presentation /r18595152/ swf /r18595121/ meeting datasheet (null) .swf demo 02/15/ 02/15/ 2/12/2004 managers 2004 2004 01:17:44 PM meeting 11:10:38 PM 12:10:50 PM Constraints The EXT_OBJECTS view excludes the following fields: Duration, which can be calculated by date_end - date_begin Size on disk, which exposes business rules regarding copies versus originals Folder ID, which is often useful for users to determine the desired folder ID Deleted objects do not appear in the EXT_OBJECTS view. Deleted objects do continue to exist in the EXT_TRANSACTION view. N OT E Database views and data relationships 17 18 Using Breeze Reporting Views