Education Data Warehouse User Guide Version

							Education Data Warehouse User Guide – Version 1.0                                                                 Overview and Login


Chapter 1 Overview and Login Table of Contents
Introduction.................................................................................................................................... 1
   What Is A Data Warehouse?....................................................................................................... 1
   Why Do We Need A Data Warehouse? ...................................................................................... 2
   What Data Is Available in the Education Data Warehouse?....................................................... 2
    Figure 1 – Education Data Warehouse Data Sources ......................................................... 3
Getting Started............................................................................................................................... 3
   District Planning Team ................................................................................................................ 3
Logging In ...................................................................................................................................... 5
   Security Role for Basic Access ................................................................................................... 5
   Security Role for Intermediate Access ........................................................................................ 6
   Security Role for Advanced Access ............................................................................................ 6
Communication and Support ....................................................................................................... 7
EDW Contents................................................................................................................................ 7
   Welcome Page ............................................................................................................................ 7
    Figure 2 – Cognos® Connection EDW Welcome Page ....................................................... 7
   Public Folders.............................................................................................................................. 8
    Figure 3 – Contents of Public Folders .................................................................................. 9
    Table 1 – Comparison of Reports and Cubes ...................................................................... 9
Web Browser Support for Cognos 8.2 ...................................................................................... 10



Introduction
The Education Data Warehouse (EDW) is a collaborative effort of the Massachusetts
Department of Elementary and Secondary Education and local school districts to
centralize K-12 educational performance data into one state coordinated data repository
hosted by the Department. The purposes of the EDW are to
      •     place the use of robust, timely performance data at the core of educational
            decision-making;
      •     reduce district burden and streamline data practices;
      •     improve district data capabilities by providing resources and technical assistance;
            and
      •     provide data for planning, policy, and management at the state and local levels.
The project’s long-term goal is to provide every district and school with the ability to
easily query and analyze their organization's state-maintained data (SIMS, MCAS/
MEPA, and eventually Educator data), and to provide districts with the option to load and
analyze their own data.
In 2006, Massachusetts purchased, from Cognos Inc. (now an IBM company), the
licenses for lifetime access for every educator in every school district in the state to use
Cognos’ suite of web-based data warehousing software. Districts may choose to spend
funds to train their staff, employ additional staff to write custom reports, or to contract
with an SIS vendor for assistance in loading local data, but there is no cost to districts to
access the EDW’s state-maintained data and run predefined reports.

What Is A Data Warehouse?
A data warehouse is a repository of data from many sources. Unlike a typical
transactional database used for day-to-day operations, a data warehouse is structured to

Massachusetts Department of Elementary and Secondary Education                                                   Chapter 1, page 1 of 10
Education Data Warehouse User Guide – Version 1.0                        Overview and Login


maintain large amounts of related, historical data for analysis and reporting. A data
warehouse provides for easy reconstruction of “snapshots” of historical data, as well as
the ability to link such snapshots over time using certain criteria.

Why Do We Need A Data Warehouse?
Improving educational performance and accountability depends on understanding the
relationships among areas such as curricula, assessments, special programs, teacher
qualifications, program spending, discipline incidents, and attendance. With our current
systems it is difficult to cross-reference student-to-teacher, student-to-course, or student-
to-program information. Without the ability to link data, state and local decision-makers
cannot leverage the full potential of the information we collect and process.
A data warehouse is a “longitudinal data system” capable of linking student, teacher, and
financial information over multiple years, across multiple schools and districts. Using the
EDW, Department and district decision-makers can take key metrics from multiple areas
and analyze them in a single view. For example:
   •   What are the results of program participation in terms of achievement?
   •   Which programs targeted at which student population have the greatest impact?
   •   How is a particular cohort of students performing over time and across multiple
       schools and districts?

What Data Is Available in the Education Data Warehouse?
The Department has loaded MCAS and SIMS data from 2002 to the present into the
warehouse. A number of districts have loaded local data, including student, course, staff,
grades, schedules, and local assessments. The EDW may also be expanded to include
financial and program data.
The key difference between a data warehouse and day-to-day transactional systems is that
a data warehouse captures historically consistent snapshots from multiple systems and
integrates these snapshots into a single database supporting longitudinal analysis and
reporting. The primary sources of warehoused data will be school/district administrative
systems, including SIS, Gradebook, HR, and finance systems from which districts can
periodically upload data to the warehouse as needed. The Department loads MCAS data
annually and SIMS data three times a year. Districts may choose to load local data
annually or more frequently depending on the data and the district’s need for analysis.




Massachusetts Department of Elementary and Secondary Education          Chapter 1, page 2 of 10
Education Data Warehouse User Guide – Version 1.0                       Overview and Login




   Figure 1 – Education Data Warehouse Data Sources

Getting Started
District Planning Team
Accessing the Education Data Warehouse’s wealth of data is easy but using it effectively
can be a challenge, even for districts already immersed in the process of data-driven
decision making to improve student learning. The Department recommends that districts
form a district planning team and develop a project plan prior to rolling out the EDW
across a district. In developing the plan, it’s important to understand that there are three
levels of access and participation in the EDW and you do not have to navigate them all
for the warehouse to be useful to your district.
The district planning team should define district-specific goals, strategies, tasks, and
measurements of success, and explain how each level of access will be integrated into the
plan, if at all. Several chapters in this document correspond to the various levels of
access:
   •   Chapter 3, Basic Access – District uses predefined reports and analysis cubes
       containing state-loaded data. This chapter also explains the process of “student
       claiming.” Claiming students enables districts to view data for all students who
       are currently enrolled in the district, not just those who were present for the
       MCAS test, as is the default for viewing MCAS data, or those who were enrolled
       during the SIMS collection, as is the default for viewing SIMS data. This chapter
       should be read by all district users.


Massachusetts Department of Elementary and Secondary Education         Chapter 1, page 3 of 10
Education Data Warehouse User Guide – Version 1.0                       Overview and Login


   •   Chapter 4, Intermediate Access – District develops custom reports using state-
       loaded data. In addition to accessing the predefined reports and analysis cubes,
       districts have the option of developing their own custom reports using
       IBM/Cognos Report Studio, a web-based report authoring tool. Report authors are
       typically members of the district’s information technology staff, and are required
       to attend advanced training.
   •   Chapter 5, Advanced Access – District uploads local data and produces custom
       reports using both state-loaded and local data. As with a district’s first rollout of
       the EDW, loading district data requires planning time, data preparation, and much
       trial and error before extract files will be successfully loaded and error free. This
       chapter should be read thoroughly by all individuals responsible for uploading
       data. The upload process is explained in four steps:
           •   Step 1: Data Review and Discovery
           •   Step 2: Data Extraction, Transforming, and Cleansing
           •   Step 3: Loading Data Extracts
           •   Step 4: Warehouse Testing and Validation
The members of the district planning team should include one or more district/school
administrators, a technology specialist who has knowledge of your district’s databases
(particularly important if you plan to upload local data), a curriculum and instruction
specialist, and a local directory administrator. Within the team, districts should choose a
project coordinator and one or more report authors as your district progresses to the
intermediate access level.
If your district has a commercial Student Information System (SIS), consider your vendor
as part of the team. They will be responsible for creating the extract files your district
plans to upload.
Planning team responsibilities should include the following:
   •   Planning how your district will use the data in the EDW in the form of analysis
       queries and reports.
   •   Managing and monitoring your local tasks as detailed in the local project plan.
   •   Determining who will have access to the data.
   •   Ensuring district users have the proper access and roles to access the EDW, and
       that they understand and sign the district’s data use policy.
   •   Assess training needs and plan for user training.
At the advanced access level, if your district plans to upload local data, the planning team
will also be responsible for
   •   deciding which data your district wants to upload,
   •   communicating with your SIS vendor,
   •   creating and uploading your extract files, and
   •   specifying and building local data reports.

Massachusetts Department of Elementary and Secondary Education         Chapter 1, page 4 of 10
Education Data Warehouse User Guide – Version 1.0                       Overview and Login


Your planning team should be created as your first step and should meet regularly during
the EDW rollout phase to check the progress according to the project plan and as each
new level of access is begun. Factors to consider when developing the project plan:
   •   What areas of curriculum, instruction, and student achievement is the district
       planning to address with data analysis?
   •   How will data review and discovery be carried out?
   •   How many and what type of EDW users are expected, and what are their training
       needs?
A district’s level of participation depends on the district’s goals for data analysis and
reporting, resources available to devote to the project, and district data readiness.
Districts should take their time progressing through the levels and rolling out the EDW to
their entire district. Preparation and training are paramount to the successful use of data.

Logging In
The Education Data Warehouse is accessible to every public school district and charter
school through the Department of Elementary and Secondary Education’s Security Portal
on the Department’s website. As with many of the applications on the portal, access is
granted locally through the Directory Administration application.
Teachers, principals, and other district personnel who want to gain access to the EDW
must contact their district’s Directory Administrator. To find a list of Directory
Administrators in your district or charter school, visit the Department’s website for the
list of Directory Administrators. Roles and functions available for districts to assign to
users relate to the levels of participation they will engage in.
NOTE: Because of the confidential information available in the EDW and the laws
regulating access to such information, Directory Administrators should consult Chapter 2,
Security and Training, on the EDW website before assigning EDW security roles.

Security Role for Basic Access
There are two security roles for the basic access user. These roles are mutually exclusive
in that only one role should be assigned to an individual and the role will depend on the
user’s organization association (district or school) in Directory Administration.
   •   DW – (210) District User has access to confidential, state loaded and district
       loaded data for their districts. They can also view the analysis cubes using the
       IBM/Cognos PowerPlay Web application.
   •   DW – (211) School User has access to confidential, state-loaded and district-
       loaded data for their schools. They can also view the analysis cubes using the
       IBM/Cognos PowerPlay Web application.
   •   DW – (212) Teacher User (not yet available) will have access to confidential,
       state-loaded and district-loaded data for their classrooms.
NOTE: Although DW – (209) District Report Author is available for district users, it
should never be selected in addition to role 210 and should only be selected instead of


Massachusetts Department of Elementary and Secondary Education          Chapter 1, page 5 of 10
Education Data Warehouse User Guide – Version 1.0                       Overview and Login


role 210 for trained report authors. See Security Role for Intermediate Access section
below for more information on assigning role 209.
All basic access security roles above have access to state loaded public data without
restrictions, including aggregate data for other schools and districts. When assigning
basic access level roles for district users, the only EDW role that can be assigned in
addition to role 210 is the Data Warehouse File Exchange Drop Box role.
   •   Data Warehouse File Exchange Drop Box: This role is assigned to the
       person who will send the district’s “student claim file” (see Chapter 3, Basic
       Access). It is available at the district level only and should be assigned in addition
       to role 210 for users with basic level access. The drop box role will become useful
       again for advanced access (see below).

Security Role for Intermediate Access
   •   DW – (209) District Report Author has access to confidential, state loaded and
       district loaded data for their districts. This role provides access to the Report
       Studio tool for creating school and district reports using public and confidential
       district data. Report authors can also view the analysis cubes using the
       IBM/Cognos PowerPlay Web application. However, it is not possible to create
       new cubes at the local level.

Security Role for Advanced Access
   •   Data Warehouse File Exchange Drop Box: This person has access to the
       Warehouse File Exchange drop box in Drop Box Central on the Department’s
       Security Portal. It is through this drop box that districts upload local data to the
       EDW. At the advanced access level, it should be assigned in conjunction with the
       209 security role for reviewing the reports to validate and approve the uploaded
       file. (For more information on the Warehouse File Exchange, see Chapter 5,
       Advanced Access.)

The steps to assign all security roles are as follows:
   1. Add users to the appropriate organizations (school or district) if they have not
      been added already.
   2. Assign only one data warehouse security role (210 or 211) to users who will have
      basic access. Access issues can arise from having both roles assigned.
   3. Assign both the Data Warehouse File Exchange Drop Box role and the 210
      security role to the user who will be uploading a student claiming file. (See
      Chapter 3, Basic Access, for more information on student claiming.)
   4. Ensure that all users read and agree to the district’s data access policy as required
      and explained in Chapter 2, Security and Training.
   5. Inform users that access will be available the next day since it is an overnight
      process to populate the EDW with new user information.




Massachusetts Department of Elementary and Secondary Education         Chapter 1, page 6 of 10
Education Data Warehouse User Guide – Version 1.0                        Overview and Login



Communication and Support
The Department’s EDW team is working to improve functionality, create new statewide
reports, enroll more districts, and respond to daily requests from district users across the
state. To facilitate communication, the EDW team has created the Date Warehouse
Contact function in Directory Administration. The person to whom this function is
assigned is the main liaison to the Department. District rollout of the EDW should be
organized so that requests for user support are directed within the district first and only
passed to the EDW team via the Data Warehouse Contact when it is determined that the
issue cannot be resolved locally.
Several email lists have been created to which all users can subscribe and unsubscribe
themselves. These lists are two-way so that all users can email the group for support,
ideas, and information. Please visit the EDW website for information and to subscribe.

EDW Contents
Welcome Page
A user logging in to the Education Data Warehouse for the first time will see in the title
bar of the web browser, “Welcome – Cognos Connection.” As explained above,
IBM/Cognos owns the web-based software upon which the EDW is built and Cognos®
Connection is the web portal used to access the software. (Figure 2)




   Figure 2 – Cognos® Connection EDW Welcome Page


Massachusetts Department of Elementary and Secondary Education          Chapter 1, page 7 of 10
Education Data Warehouse User Guide – Version 1.0                         Overview and Login


If the Welcome page fails to display, there may be a problem with your EDW
authorization. Contact your local Data Warehouse Contact who will notify the EDW
team at the Department if the problem cannot be resolved locally.
Under the Help section are several Cognos-developed user guides for the Cognos
applications used by the EDW, and the Department’s Education Data Warehouse training
curriculum located at the “Training Materials” link and outlined in detail in Chapter 2,
Security and Training.

Public Folders
The options displayed on the warehouse portal are dependent on your authorization level.
All users see three portal tabs:
   •   Welcome – the welcome page created by the Department
   •   Public Folders – contain public reports and analysis cubes
   •   My Folders – where users can save reports and files
The EDW’s Welcome page contains introductory text and helpful links to get you started.
Public Folders contain files of interest to many users, including predefined reports and
analysis cubes. My Folders are personal folders that can be organized according to
individual preferences. The content of these folders is different for every username and
password. The subfolders under Public Folders are as follows (Figure 3):
   •   District Data Upload Validation folder contains predefined reports used by districts
       to validate and accept uploaded local data. This folder is visible for district level
       access only.
   •   District Folders contains a folder for each district, with access restricted to district
       members. District EDW administrators can create additional subfolders that are
       restricted by username.
   •   ESE Portlets folder contains portal pages created by the Department’s EDW team.
       This is where the welcome page resides. Any page in this folder can be made a
       portal tab like the welcome page.
   •   ESE Cubes folder contains PowerPlay Web analysis cubes for exploring state-
       loaded data.
   •   ESE Enrollment and Indicators folder contains predefined SIMS reports provided
       by the Department. These reports are similar to student enrollment and indicator
       reports found on School/District Profiles on the Department’s website, but also
       show multi-year trends.
   •   ESE MCAS Reports folder contains predefined MCAS reports provided by the
       Department. These reports are similar to MCAS reports on School/District Profiles
       on the Department’s website, but also include student-level reports, including a
       student’s MCAS history across multiple districts.
   •   Training folder should be used for all trainings and demonstrations given in the
       district as it contains reports written against a smaller database containing
       fictional data created especially for the trainings. It should also be used by report
       authors when writing and testing new reports.
Massachusetts Department of Elementary and Secondary Education           Chapter 1, page 8 of 10
Education Data Warehouse User Guide – Version 1.0                            Overview and Login


   •   The blue folders are reporting packages for use by report authors; most users
       can ignore these folders.




   Figure 3 – Contents of Public Folders
The predefined reports found in the ESE Enrollment and Indicators and ESE MCAS Reports
folders are created using Report Studio. Only the DW – (209) Report Author role has
access to Report Studio to design custom reports (see Chapter 4, Intermediate Access).
The role should not be assigned to someone who has not been trained to use Report
Studio. Predefined reports are viewed through the Report Viewer application.
When users access the cubes, they are using the PowerPlay Web application. District and
school level users have access to PowerPlay Web to produce ad-hoc reports against
analysis cubes. Analysis cubes contain data summarized multi-dimensionally at the
school and district levels. From well-designed cubes, you can create reports in minutes
(rather than days or weeks).
PowerPlay Web has more limited formatting capabilities than Report Studio, but supports
flexible, multi-dimensional analysis with drill-down. The PowerPlay Web analysis cubes
also support drill-through to student detail reports in the full data warehouse.
   Table 1 – Comparison of Reports and Cubes

                 Predefined Reports                  Analysis Cubes
 Data source     • Detail records in the full data   • Pre-aggregated school and district level
                   warehouse
 Strengths       • Formatting and business rule      •   Speed
                   encoding                          •   Flexible, multi-dimensional analysis
                 • Student test item detail          •   Drill-up/down
                 • Most recent data                  •   Drill-through to student detail in the
                                                         full warehouse


Massachusetts Department of Elementary and Secondary Education              Chapter 1, page 9 of 10
Education Data Warehouse User Guide – Version 1.0                     Overview and Login



Web Browser Support for Cognos 8.2
Internet Explorer is the preferred browser, however, Firefox is also supported for running
reports and accessing analysis cubes. Internet Explorer is required for authoring custom
reports with Report Studio.

 COGNOS CONNECTION and             OPERATING SYSTEM              SUPPORT
 REPORT VIEWER
 Internet Explorer 7.0             Windows 2000/2003/ XP         Active
 Internet Explorer 6 SP1           Windows 2000/2003             Compatible
 Internet Explorer 6 SP2           Windows XP                    Compatible
 Netscape 7.2                      Windows                       Active
 Firefox 1.5                       Windows                       Active
 Firefox 1.5                       Macintosh                     Compatible
 Firefox 1.5.02                    Linux/UNIX                    Compatible
 Firefox 1.0.4+                    Windows                       Compatible
 Firefox 1.0.4+                    Macintosh/                    Compatible
                                   Linux/UNIX
 Note: MS Office integration is only supported on Windows platforms (i.e., export
 to Excel)
 REPORT STUDIO                     OPERATING SYSTEM              SUPPORT

 Internet Explorer 7.0             Windows 2000/2003/ XP         Active
 Internet Explorer 6 SP1           Windows 2000/2003             Compatible
 Internet Explorer 6 SP2           Windows XP                    Compatible




Massachusetts Department of Elementary and Secondary Education      Chapter 1, page 10 of 10