Clients using the eScholar CDW ™ for PK-12 (CDW-PK12?) may occasionally find the data model does not accommodate a particular need. In such cases, the following steps should be taken:
1) Define the requirement and submit it to your account manager as an enhancement request.
-Enhancement requests will be considered for inclusion in a future release of the product.
-When submitting an enhancement request, please be careful to define the requirement rather than a design solution.
-Upon analysis, eScholar may suggest an interim workaround, or may identify functionality already available that meets your needs.
2) If it is essential that the requirement be addressed in a timeframe that falls outside of normal release schedules, ask your account manager to facilitate discussion with the product team regarding extending the data model.
Any installation of the eScholar CDW ™ data model may be extended by the client as follows:
-The data model may be extended by creating new tables (herein referred to as auxiliary tables).
-Any auxiliary tables must have a db owner other than scholwhs, schollkp, scholgov, sdbadmin, esdm, uid, ssa, or sid.
-Auxiliary tables will not be supported under the standard maintenance and support agreement.
-The creator of auxiliary tables is responsible for any integration with the eScholar data model that may be necessary, including procedures to load or update those auxiliary tables. Ask your account manager about pricing and availability of eScholar Professional Services personnel in support of this effort.
-The creator of auxiliary tables is responsible for understanding how changes to the eScholar data model, with each new release of the eScholar CDW ™, may affect such auxiliary tables, and for making any modifications necessary to maintain compatibility. Information about changes contained in any release is available in the New Features Guide for that release. New Features Guides can be found on the eScholar support site. Ask your account manager about pricing and availability of eScholar Professional Services personnel in support of this effort.
-Data model extensions implemented by a client will not be included in any reporting framework that may be provided by eScholar. The creator of such extensions will be responsible for importing and maintaining any auxiliary tables.
–eScholar will not, as a result of any upgrade procedure, alter the structure or content of any auxiliary tables.
Yes. During installation / upgrade, the SCHOLWHS.Z_ESCHOLAR_METADATA table is loaded with the latest information mapping Template column to Table column to Report column. Simply query this table for mapping information. The grid below defines each of the columns in the Z_ESCHOLAR_METADATA table.
|
Column Name |
Description |
|
TEMPLATE_NBR |
Template number |
|
FIELD_NBR |
Template field number |
|
ESCHOLAR_VERSION_NBR |
CDW Version |
|
TEMPLATE_NAME |
Template name |
|
FIELD_NAME |
Template field name |
|
DW_TABLE_NAME |
Target table name |
|
DW_COLUMN_NAME |
Target table column name |
|
DW_DATA_TYPE |
Data type of the target column |
|
POPULATION_TYPE |
This column provides a description of how the template field relates to the table column, or how the table column is populated. For example, a template field value may be 1) written to a table column; 2) used to generate a dimension key; 3) used to obtain a dimension key that is then written to a table column; 4) Used in a Lookup; 5) used to derive a value |
|
GROUP_NAME |
This column is used to group information into logical categories. The most common group is WAREHOUSE, representing all templates that load the core CDW tables. Other examples of groups include: LOOKUP ? representing the Master Lookup template and table; ASSESSMENT EXTRACT LOOKUP ? this is the primary Assessment Lookup table, used exclusively with eScholar?s proprietary assessment extraction plans covering over 130 test vendor results file formats across more than 40 high-stakes tests; ASSESSMENT ? representing other Assessment Lookup tables used exclusively with eScholar?s assessment extraction plans; REPORTING ? representing templates and tables used to drive security governing report access with eScholar Reporting ?. |
|
DW_COLUMN_LENGTH |
Definition of the maximum column length |
|
DW_COLUMN_SCALE |
Definition of column scale, if any |
|
DW_COLUMN_NULLS |
Describes whether a null value can appear in the column |
|
COLUMN_REPORT_NAME |
Defines the business name of the column in reports from eScholar Reporting?. |
The eScholar CDW ™ for PK-12 (CDW-PK12) addresses slowly changing dimensional data in one of three ways depending on the data.
1) Most dimensions slowly change by school year. For example, code sets, whether they be in the SCHOLLKP.MASTER_LOOKUP table or in dimensional tables such as SCHOLWHS.ENROLL_CODES or SCHOLWHS.PROGRAMS_CODE, slowly change by school year. Similarly, the SCHOLWHS.STUDENT table slowly changes by school year.
2) Some dimensions do not slowly change but have associated snapshot tables that allow dimensional data to slowly change by date. For example, the SCHOLWHS.STAFF table does not slowly change, but the SCHOLWHS.STAFF_SNAPSHOT table accommodates slowly changing staff attributes by including a snapshot date as part of the logical key.
3) The SCHOLWHS.STUDENT table slowly changes by school year. The corresponding snapshot table, SCHOLWHS.STUD_SNAPSHOT, facilitates capture of more frequently changing student attributes by including a snapshot date as part of the logical key, allowing attributes to change as frequently as each day.
Our approach to slowly changing dimensional data has been driven by reporting requirements rather than data requirements. We find our clients have varying needs for capturing slowly changing dimensional data. Therefore, how often a client may load, for example, the SCHOLWHS.STAFF_SNAPSHOT or SCHOLWHS.STUD_SNAPSHOT tables to record changing attributes of staff or students becomes a function of the client’s reporting needs.
Many clients need only to report on staff or student attributes at the 40-day, 80-day, and 120-day points within a school year. In this case, a client would extract staff or student data from source systems to template format on the 40th, 80th, and 120th days of the school year and load to snapshot tables using an appropriate snapshot date. Another attribute, Period Level, can be used to identify the type of snapshot date and in this case might be populated with values of 40D, 80D, or 120D as appropriate.
Student attributes may need to be captured at different intervals, as is the case with Assessment data. A client may need to know, for each administration of an assessment, each student’s attributes at the time the student took the test. In this case, the attributes of the student at the time the test was taken might be loaded to the SCHOLWHS.STUD_SNAPSHOT table using the test date as the snapshot date, and perhaps using the test acronym as the Period Level value. For example, when loading the attributes of students taking the SAT on Oct 1, 2008 the snapshot date would be 2008-10-01 and the Period Level might be ‘SAT’.
In summary, each client’s strategy with respect to slowly changing dimensional data should be understood in the context of reporting requirements. Those requirements will determine how and when slowly changing dimensional data must be captured, and will influence the schedule for loading various tables of the data warehouse.
Each time after loading data to the warehouse, load statistics should be retrieved and verified to ensure that all records in the source file are accounted for. For detailed information about retrieving and interpreting load statistics, see Reference Guide ? eScholar Utility Plans on the eScholar Support Site.
Because many dimensions in the eScholar CDW ™ for PK-12 (CDW-PK12) change by school year, before collecting any data for a new school year it is necessary to generate and load new dimension and Master Lookup records. eScholar provides a helpful utility plan to accomplish this. Please see Reference Guide – eScholar Utility Plans on the eScholar Support Site for more details.
“Base” Calendar Period records must be generated in template format using the Extract Calendar Period Template plan. Once generated, the template-formatted file should be loaded using the Load SCHOLWHS.PERIOD-Calendar load plan. Please see Reference Guide – eScholar Utility Plans on the eScholar Support Site for more details.
With the v9.0 release of the eScholar CDW ™, Student Demographic data associated with assessment results should now be stored in the Stud_Snapshot table via the Student Snapshot template with a Snapshot Date and Period Level that reflect the assessment. This provides additional flexibility to take advantage of the large number of attributes already available in Stud_Snapshot, rather than continually adding new fields to Assessment Student Demographics each release.
Student Snapshot data that represents student demographic data associated with a particular assessment should be differentiated from other Student Snapshot data by ensuring that the combination of Snapshot Date (Field #83) + Student Snapshot Period Level (Field #186) is unique to a particular administration of a particular assessment. Therefore, this Student Snapshot template-formatted data should be constructed such that:
Snapshot Date = Test Date (in ISO format)
Student Snapshot Period Level = Test Acronym
Example – for the SAT administered on January 26th, 2008:
Snapshot Date = 2008-01-26
Student Snapshot Period Level = SAT
For more information please see Assessment Student Demographics Migration Guide available on the eScholar Support Site.
Remember to first load a record to the Period table if necessary.
Before loading Student Snapshot records, a record must exist in the Period table that represents the unique combination of Snapshot Date + Student Snapshot Period Level for which snapshot data will be loaded.
Using the Calendar Period template, construct the record(s) as follows:
Period Start Date = Test Date
Period End Date = Test Date
Period Level = Test Acronym
Example – for the SAT administered on January 26th, 2008:
Period Start Date = 2008-01-26
Period End Date = 2008-01-26
Period Level = SAT