user manual data query tool - education bureau

Copyright © 2007. Education Bureau. The Government of the HKSAR. All Rights Reserved.

USER MANUAL Data Query Tool

(Document 27e)

WebSAMS User Manual Version 1.2

Copyright © 2007. Education Bureau. The Government of the HKSAR. Page 1 All Rights Reserved.

Table of Contents

1 MODULE OVERVIEW.............................................................................................................. 2

1.1 INTRODUCTION........................................................................................................................2 1.1.1 Objective .........................................................................................................................2 1.1.2 Overview .........................................................................................................................3 1.1.3 Basic Concept .................................................................................................................4

1.2 FUNCTION CHART ...................................................................................................................6 1.3 FLOW DIAGRAM ......................................................................................................................7 1.4 INTERACTIONS WITH OTHER MODULES ...................................................................................8 1.5 USER GROUP AND ACCESS RIGHT ........................................................................................10

2 OPERATION PROCEDURES................................................................................................. 12

2.1 DATA MAINTENANCE............................................................................................................12 Package List ..............................................................................................................................12 Package Information .................................................................................................................13 Copy Package............................................................................................................................14 Delete Package..........................................................................................................................16 Import Package .........................................................................................................................17 Task Information.......................................................................................................................19 Export Retrieved Package.........................................................................................................21 Confirm Retrieved Package ......................................................................................................23 Execute Task.............................................................................................................................24 Clear Data .................................................................................................................................25 Data Manipulation ....................................................................................................................27 Complete Task ..........................................................................................................................29 Reset Package Status.................................................................................................................30 Reset Task Status ......................................................................................................................31

2.2 PACKAGE MAINTENANCE .....................................................................................................33 Package List ..............................................................................................................................33 Create Package..........................................................................................................................34 Copy Package............................................................................................................................35 Delete Package..........................................................................................................................36 Export Package .........................................................................................................................37 Package Information .................................................................................................................38 Update Package.........................................................................................................................40 Add Task...................................................................................................................................41 Task Information.......................................................................................................................42 Copy Task .................................................................................................................................44 Delete Task ...............................................................................................................................45 Mask Column............................................................................................................................46 Update Task ..............................................................................................................................47 Update Supplementary Information Sequence Number ...........................................................48 Add Supplementary Data Definition.........................................................................................49 Update Supplementary Data Definition....................................................................................51 Delete Supplementary Data Definition .....................................................................................53 Display Setup ............................................................................................................................54 Preview .....................................................................................................................................55

2.3 DATA COMMUNICATION .......................................................................................................58 Process Incoming Data..............................................................................................................58



Confirmed Outgoing Data.........................................................................................................59

3 APPENDIX................................................................................................................................. 61

3.1 APPENDIX I: CONFIRMED EDB PACKAGE FILE FORMAT......................................................61



1 Module Overview

1.1 Introduction

1.1.1 Objective

The Data Query Tool (DQT) module provides functions for the EDB and school to extract WebSAMS production data, that will be sent to EDB for statistical purpose. Besides, by making use of the module, school users can create package for its own purpose. The completed records can be stored in a csv file and attached as an outgoing message transmitted to EDB via CDS.



1.1.2 Overview

EDB School

1. Send a message with a DQT package to school via CDS

2. Receive the message and decrypt it in CDS.

3. Import the package in DQT module.

4. Complete all the tasks by executing the built-in SQL statements and / or filling in some supplementary fields

5. Confirm the package to CDS.

6. Encrypt and send the message with filled package back to EDB via CDS.



1.1.3 Basic Concept

Package Structure A package is created for containing tasks. For each task, there should be one SQL statement attached for extracting WebSAMS data. Package Status There are two types of packages, “EDB” and “School”. The status of the package could be toggled as follows:

- Masking Information

.

.

.

Package

- SQL statement 1 - Supplementary Data Definition - Display Setting

- SQL statement 2 - Supplementary Data Definition - Display Setting

Task 1

Task 2

Not Confirmed

“EDB” Type

Sent to CDS

Confirm Reset

Not Confirmed

“School” Type

Confirmed

Confirm Reset



Task Status The task could be in one of the following three status at a time.

Incomplete

Complete

Complete

Reset

Outstanding

Execute Clear Data

Validate Supplementary Data



1.2 Function Chart

Data Query Tool (DQT)

Data Maintenance

Package Maintenance

Data Communication

� Package Information

� Copy Package

� Import Package

� Delete Package

� Package Information

� Create Package

� Copy Package

� Delete Package

� Export Package

� Process Incoming Data

� Confirmed Outstanding Data



1.3 Flow Diagram



DQT

1.4 Interactions with other modules

CDS

- DQT02 Maintain DQT Data

- DQT03 Data Communication

SEC

- DQT01 Maintain DQT Package

- DQT02 Maintain DQT Data

- DQT03 Data Communication



� All DQT functions

• Security (SEC)

• Get access right from all user groups that the user belongs to

� DQT02 – Maintain DQT Data – Confirm Retrieved Package

• CDS

• Generate CDS message containing retrieved package

� DAT03 – Data Communication

• CDS

• View Incoming Message with Subject Code of DQT

• View Confirmed DQT Outgoing Message



1.5 User Group and Access Right

Users have to possess certain access rights to use the corresponding functions in Data Query Tool module. Users assigned to different user groups will have different access rights. Among the built-in user groups, only DQT_ADMIN and DQT_USER can use functions of Data Query Tool module. The pre-assigned access rights of these two built-in groups are depicted below. DQT_USER:



DQT_ADMIN:

Alternatively, user-defined groups can also be created with access rights of Data Query Tool module.



2 Operation Procedures

2.1 Data Maintenance

In Data Maintenance, user could select a package, execute the corresponding tasks, confirm the package and prepare a CDS message for submission to EDB.

Package List

� Function Description

The system will display all the packages for Data Maintenance.

� Pre-requisites

Users have to possess “Data Maintenance - Package Listing” Function access right.

� User Procedures

1. Click [Data Query Tool] > [Data Maintenance] on the left menu. 2. The system displays the DQT Package Listing page.

� Post-effects

The system displays the DQT Package Listing page.



� Notes

1. User may click the hyperlink on package name to view individual package detailed information. Refer to Data Maintenance – Package Information function.

2. User may click the “Copy” button to copy the package. Refer to Data Maintenance – Copy Package function.

3. User may click the “Delete” button to delete the package. Refer to Data Maintenance – Delete Package function.

4. User may click the “Import” button to Import the package. Refer to Data Maintenance – Import Package function.

5. There are two types of package:

- EDB: Package received through CDS

- School: Package imported or copied by user

6. Package status is defined as follows:

- Not Confirmed: The package is not frozen and its tasks can still be manipulated.

- Sent to CDS: (For EDB package only) The package is frozen and a CDS message is generated.

- Confirmed: (For school package only) The package is frozen.

7. Package size shows the file size (in KB) of a confirmed package. It will be cleared when the package is reset to Not Confirmed.

Package Information


Users can view the detail of the package.

� Pre-requisites

1. Users have to possess “Data Maintenance - Package Information” access right.

� User Procedures

1. Click [Data Query Tool] > [Data Maintenance] on the left menu.

2. Click the hyperlink under Package Name.

3. System displays the package and its task information.



� Post-effects

Package information and its task information will be displayed.

� Notes

1. User may click the hyperlink on the task name to view task information. 2. User may click the “Confirm” button to confirm the package. 3. User may click the “Export” button to export the package. 4. If all the tasks are in Complete status, “Confirm” button will be shown. 5. If the package status is “Sent to CDS” or “Confirmed”, user may click the

“Reset Status” button to reset its status to “Not Confirmed”

Copy Package


Users can copy a Package.

� Pre-requisites

1. Users have to possess “Data Maintenance - Copy Package” access right.

� User Procedures


2. Click “Copy” button in the Package Listing page



3. Select the destination as “Package Maintenance” or “Data Maintenance”, then assign the new package name and description

4. Click “Save” button

� Post-effects

1. If user chooses Package Maintenance as destination, the package is copied to Package Maintenance.

2. If user chooses Data Maintenance as destination, the package is copied to Data Maintenance. The type of the copied package will be “School”.

2



� Notes

N/A

Delete Package


User can delete Package in Data Maintenance. Once delete the Package, the corresponding task(s) and data retrieved will be deleted.

� Pre-requisites

1. Users have to possess “Data Maintenance - Delete Package” access right.

� User Procedures


2. In the Package Listing page, click the check box beside the package name

3. Click “Delete” button

4. Click Yes on Confirm delete message to alert user if he/she is going to delete the package.

2

3



5. Click Yes on Confirm message to alert user that all related retrieved data and supplementary data will be deleted if the data exists.

� Post-effects

1. The selected package and its related task(s) will be deleted. 2. All related retrieved data and supplementary data will be deleted.

� Notes

N/A

Import Package


Users can import Package to Data Maintenance. The type of the package will be classified as “School”. The package in Data Maintenance can only be exported and cannot be submitted to EDB.

� Pre-requisites

1. Users have to possess “Data Maintenance - Import Package” Execute access right.

� User Procedures


2. In the Package Listing page, click the “Import” button and access import screen.



3. Input the path and name of the package file and then click “Import” button.

4. Input the package name and description.

2



5. Click “Import” button.

� Post-effects

1. The package will be imported to the system and displayed in the package list in Data Maintenance.

2. The package will be classified as School type.

� Notes

N/A

Task Information


Users can view the Task information.

� Pre-requisites

1. Users have to possess “Data Maintenance - Task Information” View access right.

� User Procedures




2. In the Package Listing page, click the hyperlink under Package Name

3. Click the hyperlink under Task Name.

4. Package, Task and Supplementary Data Definition information will be listed.

� Post-effects

1. User may click “Execute” button to execute the SQL statement. Refer to Data Maintenance – Execute Task function.

2. User may click “Clear Data” button to clear the retrieved data and inputted supplementary data. Refer to Data Maintenance – Clear Data function.

3. User may click “Complete” button to complete the task. Refer to Data Maintenance – Complete Task function.

4. User may click “Reset Status” button to reset task status. Refer to Data Maintenance – Reset Task Status function.

5. User may click “View / Input Data” or “View Data” button to examine the retrieved data or input supplementary data. Refer to Data Maintenance – Data Manipulation function.

6. User may click “View Detail” button to view the details information of the field.

5



� Notes

“Not Confirmed” “Sent to CDS” (EDB type) / “Confirmed” (School type)

Outstanding 1. The SQL statement has not yet run, the “Execute” button is provided.

N/A

Incomplete 1. SQL statement is executed, “Complete” and “Clear Data” buttons are provided.

2. “View / Input Data” or “View Data” button will be shown depends on the existence of supplementary data.

N/A

Complete 1. “Reset Status” and “View Data” buttons are shown.

1. “View Data” button is provided.

Export Retrieved Package


Users can export the DQT Package with retrieved data and inputted data.

� Pre-requisites

1. Users have to possess “Data Maintenance - Export Retrieved Package” Execute access right.

2. Not all tasks are in outstanding status.

� User Procedures

Package Status

Task Status




2. In the Package Listing page, click the hyperlink under Package Name.

3. Click “Export” button.

4. System displays export screen with package details.

5. Choose export option.

6. Choose output format.

7. Click “Export” button.

3

5

6

7



� Post-effects

1. The result data will be exported in the chosen format. 2. All the data file(s) will be zipped in a single file and use the Package name as

file name. 3. The individual data file will use the task name as file name.

� Notes

If user selected to include non-submitted columns and remove masking, all retrieved columns and inputted supplementary data will be exported. Exported data will not be masked. Otherwise, the non-submitted columns will not be exported and the data in the masked columns will be masked.

Confirm Retrieved Package


Users can confirm the completed Packages. For EDB defined package, it will send to the CDS module automatically. The data file will be in the Unicode format.

� Pre-requisites

1. User has to possess “Data Maintenance - Confirm Retrieved Package” Execute access right.

2. User has input all the supplementary data (if any). 3. User has completed all the tasks.

� User Procedures



3. Click “Confirm” button.



� Post-effects

1. Package Status is set to “Sent to CDS” for EDB defined package, and “Confirmed” for School defined package.

2. The EDB defined Package will be automatically forwarded to CDS module. After encryption, it will be transmitted to EDB.

3. For package sent to CDS, user can click the hyperlink in the successful message and go to Data Communication Confirmed Outgoing Message to see the status.

4. The data file will be in Unicode format and use the task name as the file name.

5. Each data file will be an attachment for the CDS message. 6. If user needs to resend the package to EDB, user may click “Reset Status”

button to reset the package and confirm the package again.

� Notes

N/A

Execute Task


Users can execute task within the package.

� Pre-requisites

3



1. User has to possess “Data Maintenance - Execute Task” Execute access right.

2. The task is in an outstanding status.

� User Procedures



3. Click the hyperlink under Task Name with status “Outstanding” in package information screen.

4. Input parameter value (if any).

5. Click “Execute” button.

� Post-effects

1. The SQL statement is executed and WebSAMS data is extracted from the system.

2. Package status is set to “Incomplete”.

� Notes

1. User can input Supplementary Data, clear data or complete the task afterward.

Clear Data


5

4



Users can clear all the previous generated and inputted data.

� Pre-requisites

1. User has to possess “Data Maintenance – Clear Data” Execute access right. 2. The package status is Not Confirmed and task status is Incomplete.

� User Procedures


2. In the Package Listing page, click the hyperlink under package name with status “Not Confirmed”.

3. Click the hyperlink under task name with status “Incomplete” in package info screen.

4. Click “Clear Data” button.

� Post-effects

1. All the previous generated and entered data will be removed. 2. Task Status is reset to Outstanding.

� Notes

N/A

4



Data Manipulation


Users can examine the retrieved data or input supplementary data to package if available.

� Pre-requisites

1. User has to possess “Data Maintenance – Data Manipulation” Add access right.

2. The task status is Complete or Incomplete.

� User Procedures



3. Click the hyperlink under Task Name with status “Complete” or “Incomplete” in package information screen.

4. Click “View/Input Data” button. If the Task does not have any Supplementary Data or the Task is completed, click “View Data” button.

5. User may click the hyperlink of the Supplementary Data Column name (if any) to see the details of the field.

6. Input Supplementary data (if any).

7. User may navigate to other pages (if any).

4



8. Click “Save” button. (if there is Supplementary Data for input)

9. Click “Close” button to close the popup window.

� Post-effects

1. Supplementary Data is saved (if there is supplementary data for input).

� Notes

1. There will be 50 records displayed in a page. 2. Upon successfully saved the inputted supplementary data, the system will

automatically list the retrieved data of the next page if multiple pages of retrieved data exists.

3. User may click “View Detail” button to view the details information of the field.

8

6

5

7



Complete Task


Users can complete a task after user executed the SQL statement and inputted all the required supplementary data (if any). The system would validate the inputted supplementary data.

� Pre-requisites

1. User has to possess “Data Maintenance – Complete Task” Execute access right.

2. The task has been executed. 3. All required Supplementary data are input.

� User Procedures



3. Click the hyperlink under Task Name with status “Incomplete” in package information screen.

4. Click “Complete” button.



� Post-effects

1. Task status is set to “Complete”.

� Notes

1. The system will check if all supplementary data are inputted and saved.

Reset Package Status


Users can reset the package status from “Sent to CDS” / “Confirm” to “Not Confirmed”.

� Pre-requisites

1. User has to possess “Data Maintenance – Reset Package Status” Execute access right.

2. Package status is “Sent to CDS” (EDB Type) or “Confirmed” (School Type).

� User Procedures


4



2. In the Package Listing page, click the hyperlink under Package Name with status “Sent to CDS” (EDB Type) or “Confirmed” (School Type).

3. Click “Reset Status” button.

� Post-effects

1. Package status is set to “Not Confirmed”.

� Notes

N/A

Reset Task Status


Users can reset the task status from “Complete” to “Incomplete”.

� Pre-requisites

1. User has to possess “Data Maintenance – Reset Task Status” Execute access right.

3



2. Package status is “Not Confirmed”. 3. Task Status is “Complete”.

� User Procedures


2. In the Package Listing page, click the hyperlink under Package Name with status “Not Confirmed”.

3. Click the hyperlink under Task Name with status “Complete”

4. Click “Reset Status” button.

� Post-effects

1. Task status is set to “Incomplete”.

� Notes

N/A

3



2.2 Package Maintenance

In Package Maintenance, user could create package for its own purpose.

Package List


Users can search user-defined Packages. The matched searching result will be shown.

� Pre-requisites

Users have to possess “Package Maintenance - Package Listing” View access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button.

2

3



� Post-effects

1. System will list the packages according to the searching criteria.

� Notes

1. User may click the hyperlink of each package to view detailed information of individual package.

2. User may click the “Add” button to create package. Refer to Package Maintenance – Create Package function.

3. User may click the “Copy” button to copy the package. Refer to Package Maintenance – Copy Package function.

4. User may click the “Delete” button to delete the selected package. Refer to Package Maintenance – Delete Package function.

5. User may click the “Export” button to export the selected package. Refer to Package Maintenance – Export Package function.

Create Package


Users can create a package. After a package is created, user can attach task to the package.

� Pre-requisites

Users have to possess “Package Maintenance – Create Package” Add access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Click “Add” button. 3. Input the necessary package information. 4. Click “Save” button.



� Post-effects

1. Package Information is saved. 2. System displays the Package Information page

� Notes

1. User may proceed to attach task to the package

Copy Package


Users can copy a Package.

� Pre-requisites

Users have to possess “Package Maintenance – Copy Package” Add access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click “Copy” button beside the package to be copied. 5. Select the destination as “Package Maintenance” or “Data Maintenance”. 6. Input the necessary fields.

4



7. Click “Save” button.

� Post-effects

1. If user chooses Package Maintenance as destination, the package is copied to Package Maintenance.

2. If user chooses Data Maintenance as destination, the package is copied to Data Maintenance. The type of the copied package will be “School”.

� Notes

N/A

Delete Package


User can select one or more packages to be deleted. The entire package includes its task and supplementary data definition, display setup, etc will also be removed.

� Pre-requisites

1. Users have to possess “Package Maintenance – Delete Package” Delete access right.

2. At least one Package exists in the system

7



� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Enter search criteria. 3. Click the “Search” button. 4. Click the check box beside the package name 5. Click “Delete” button.

6. Click OK on confirm message.

� Post-effects

1. The selected package(s) will be deleted

� Notes

N/A

Export Package


User can export Package. User can only export one package each time. The package name will be used as the exported file name. The exported file will be encrypted such that it cannot be amended manually.

� Pre-requisites

4

5



1. Users have to possess “Package Maintenance – Export Package” Execute access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the check box beside the package name. 5. Click “Export” button.

� Post-effects

1. The selected package will be encrypted and exported to a data file.

� Notes

N/A

Package Information


User can view the Package information and all its related tasks details.

4

5



� Pre-requisites

1. Users have to possess “Package Maintenance – Packing Information” View access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name.

� Post-effects

1. Package information and its related tasks will be listed.

� Notes

1. User may click hyperlink under Task Name to view the detail task information.

2. User may click “Save” button to update the Package Information. 3. User may click “Add” button to attach task to the Package. Refer to Package

Maintenance – Create Package function. 4. User may click “Copy” button to copy task to other Package. Refer to

Package Maintenance – Copy Package function. 5. User may click “Delete” button to delete task from the Package. Refer to

Package Maintenance – Delete Package function. 6. User may click “Mask Column” tab to select column to mask. Refer to

Package Maintenance – Mask Column function.



Update Package


User can update the information of the selected Package.

� Pre-requisites

1. Users have to possess “Package Maintenance – Update Package” Edit access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. User may update the

fields. 6. Click “Save” button.

� Post-effects

1. The package information is updated.

6



� Notes

N/A

Add Task


User can add tasks to the selected Package. User can define the Task Name, Task Description and the SQL Statement. For the SQL statement, user must specify the database column to be selected where alias column name is accepted.

� Pre-requisites

1. Users have to possess “Package Maintenance – Add Task” Add access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click “Add” button. 7. User input following fields:

- Task Name - Task Description - SQL Statement

8. Click “Save” button.

8



� Post-effects

1. Task is attached to the package. System displays the Task Information page. 2. If user has already performed “Mask Column”, all the related data will be reset.

� Notes

1. SQL statement using wildcards such as “select * from …” is not allowed.

Task Information


User can view the task information and all its Supplementary Data Definition.

� Pre-requisites

1. Users have to possess “Package Maintenance – Task Information” View access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition.



� Post-effects

1. System displays Task Information and its Supplementary Data Definition.

� Notes

1. User may click hyperlink under Field Name to view or update Supplementary Data Definition information.

2. User may click “Save” button to update Task Information. Refer to Package Maintenance – Update Task function.

3. User may click “Preview” button to Preview the data extracted. Refer to Package Maintenance – Preview function.

4. User may click “Add” button to attach Supplementary Data Definition to the Task. Refer to Package Maintenance – Add Supplementary Data Definition function.

5. User may click “Delete” button to delete Supplementary Data Definition from the DQT Task. Refer to Package Maintenance – Delete Supplementary Data Definiation function.

6. User may click “Update” button to update the sequence of the supplementary data. Refer to Package Maintenance – Update Supplementary Information Sequence Number function.

7. User may click “Display Setup” tab to setup the presentation of filling Supplementary Data in Data Maintenance. Refer to Package Maintenance – Display Setup function.



Copy Task


User can copy the task to current or another DQT Package. All the supplementary data definition will be copied as well.

� Pre-requisites

1. Users have to possess “Package Maintenance – Copy Task” Add access right.

2. At least one task created is the selected package.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click “Copy” button of the task 7. Assign new task name, description and selects the target package 8. Click “Save” button.

� Post-effects

1. The task will be copied to the target package. 2. If user has already performed “Mask Column” on target package, all the

related data will be reset.

� Notes

N/A

8



Delete Task


User can delete the task.

� Pre-requisites

1. Users have to possess “Package Maintenance – Delete Task” Delete access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the check box beside task name 7. Click “Delete” button.

8. Confirm to delete the task.

� Post-effects

1. The selected task(s) are deleted.

7

6



� Notes

N/A

Mask Column


User can specify which column (data) to mask for data transfer or export. By default, no column will be masked and the Column’s Alias name (defined in the SQL statement) will be displayed in the “Column name” if available.

� Pre-requisites

1. Users have to possess “Package Maintenance – Mask Column” Add access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click “Mask Column” tab. 7. Package information and the column selected from all its tasks will be shown.

For the same column name, all its related task will be grouped together. 8. Select the columns going to be masked. 9. Click “Save” button.

8

9



� Post-effects

1. The masking information will be updated.

� Notes

If the column is marked as masked column, the retrieved data from the system will be masked when the data is exported in Data Maintenance or transferred to EDB.

Update Task


User can update the task information.

� Pre-requisites

1. Users have to possess “Package Maintenance – Update Task” Edit access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays Task Information and its Supplementary Data Definition. 8. Update task information. 9. Click “Save” button.



� Post-effects

1. Task information is updated. 2. If user has already performed “Mask Column” in the package, all the related

data will be reset. 3. If user has already performed “Display Setup” in the task, all the related data

will be reset. 4. If user has set default column in the supplementary data, affected

supplementary data will be reset.

� Notes

N/A

Update Supplementary Information Sequence Number


User can update the display sequence of supplementary information. This sequence will be used as the display sequence at Data Manipulation.

� Pre-requisites

1. Users have to possess “Package Maintenance – Update Task” Edit access right.

� User Procedures

9

8



1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition. 8. Change the sequence of supplementary data. 9. Click “Update” button.

� Post-effects

1. The supplementary information sequence number is updated.

� Notes

N/A

Add Supplementary Data Definition


User can add supplementary data definition to the current task. There are four field types - Text, Date, Numeric and Selection.

� Pre-requisites

9

8



1. Users have to possess “Package Maintenance – Add Supplementary Data” Add access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition. 8. Click “Add” button. 9. System displays Add Supplementary Data Definition information 10. Input Field name and Field Description 11. Select “Field Format”. 12. There are four types of format to choose, Text, Date, Numeric and Selection.

The field will display according to different field formats. a. Text

- Field Type (Mandatory) i. Text Box ii. Text Area

- Field Length (Mandatory) b. Date

- Field Range (Optional) c. Numeric

- Field Type (Mandatory) i. Integer ii. Numeric

- Field Range (Mandatory) d. Selection

- Field Type (Mandatory) i. Single ii. Multiple

- Allowable Value (Mandatory) - Allowable Value Description (Optional)

13. Input Default value (optional). 14. Select the default value as By Value or By Column defined in the SQL

Statement. If choose By Value, user can enter the default value or select the default value (Selection Field Format). If choose from column, user can select from a list which contains all the column name retrieved from SQL Statement. Thus, the default value shown in Data Manipulation will be the data of that column. Default value is “By Value”. Between “By Value” and “By Column” options, only one of their input boxes is in active mode.

15. Input the Mandatory field. 16. Click “Save” button.



� Post-effects

1. Supplementary Data Definition is added to the task.

� Notes

1. User can choose the default value as WebSAMS database Column or other value. If user selects the default value from column, user has to determine if the column type selected is matched with the supplementary definition data field type. Otherwise, in Data Manipulation, the default value may violate the checking rule. (e.g. Supplementary Field is Numeric but the default column is string). However, User can make use of the Preview function to preview the content.

Update Supplementary Data Definition


User can update supplementary data definition of current task.

� Pre-requisites

1. Users have to possess “Package Maintenance – Update Supplementary Data” Edit access right.

� User Procedures

16



1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition. 8. Click the hyperlink under the Supplementary Information Field Name. 9. System displays Supplementary Data Definition information. 10. Update Supplementary Data Definition information. 11. Select “Field Format”. 12. There are four types of format to choose, Text, Date, Numeric and Selection.

The field will display according to different field format. Refer to Package Maintenance – Add Supplementary Data Defination function.

13. Update Default value (optional). 14. Select the default value as By Value or By Column defined in the SQL

Statement. If choose By Value, user can enter the default value or select the default value (Selection Field Format). If choose from column, user can select from a list which contains all the column name retrieved from SQL Statement. Thus, the default value shown in Data Manipulation will be the data of that column. Default value is “By Value”. Between “By Value” and “By Column” options, only one of their input boxes is in active mode.

15. Update the Mandatory field. 16. Click “Save” button.

� Post-effects

1. Supplementary Data Definition is updated.

16



� Notes

N/A

Delete Supplementary Data Definition


User can delete supplementary data definition from current task.

� Pre-requisites

1. Users have to possess “Package Maintenance – Delete Supplementary Data” Edit access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition. 8. Click the check box beside Supplementary field. 9. Click “Delete” button.

9

8



� Post-effects

1. Supplementary Data Definition is deleted from the task. 2. The sequence number will be rearranged.

� Notes

N/A

Display Setup


User can specify the index field and the field to be submitted.

� Pre-requisites

1. Users have to possess “Package Maintenance – Display Setup” Edit access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition. 8. Click “Display Setup” tab. 9. System displays the Display Setup page. By default, for “Submit” column, all

boxes are checked and no columns are index field. 10. Select the columns to be index field. 11. Uncheck Submit field that is not required. 12. Click “Save” button



� Post-effects

1. Display setup is saved.

� Notes

N/A

Preview


User can preview the execution result of the SQL statement together with the Supplementary data.

� Pre-requisites

1. Users have to possess “Package Maintenance – Preview” View access right.

� User Procedures

1. Click [Data Query Tool] > [Package Maintenance] on the left menu. 2. Input search criteria. 3. Click the “Search” button. 4. Click the hyperlink under Package Name. 5. Package information and its related tasks will be listed. 6. Click the hyperlink under the Task Name. 7. System displays task Information and its Supplementary Data Definition. 8. Click “Preview” button.

12

10 11



9. Enter parameter value (if any). 10. Choose output format. 11. Click “View Result” button

� Post-effects

1. SQL statement execution result is shown. 2. For Supplementary Data, default value will be displayed (if any).

8

10

9

11



� Notes

N/A



2.3 Data Communication

In Data Communication, user could import package from incoming message, and view the confirmed outgoing message related to DQT.

Process Incoming Data


User can view the list of the decrypted CDS incoming messges related to DQT module.

� Pre-requisites

Users have to possess “Data Communication – Process Incoming Data” Execute access right.

� User Procedures

1. Click [Data Query Tool] > [Data Communication] on the left menu. 2. The system displays the DQT Process Incoming Data page. 3. Select the radio button beside the package to import. 4. Click “Import” button to import the package.

� Post-effects

1. DQT package imported to Data Maintenance.

3

4



� Notes

N/A

Confirmed Outgoing Data


Users can view the list of CDS outgoing message related to DQT module.

� Pre-requisites

Users have to possess “Data Communication – Confirmed Outgoing Data” View access right.

� User Procedures

1. Click [Data Query Tool] > [Data Communication] on the left menu. 2. Click “Confirmed Outgoing Data” tab 3. The system displays the DQT Confirmed Outgoing Data CDS Message.

� Post-effects

1. System will list the DQT Confirmed Outgoing Data CDS Message.



� Notes

N/A



3 Appendix

3.1 Appendix I: Confirmed EDB Package File Format

Each Task will have exactly one data file. The first line will be the Column Name and the supplementary data definition name. The retrieved data and the supplementary data will be listed start from line two. The file will be in Unicode format. CSV file format Column definition Column Name

Description Example

SCRN + timestamp

SCRN of School and Transaction Time (down to second and same within the whole transaction)

123456000121220050107182623

Data1 Retrieved Column 1 Column1 Data2 Retrieved Column 2 Column2 … DataN Retrieved Column N ColumnN Supp1 Supplementary Data 1 SuppData1 Supp2 Supplementary Data 2 SuppData2 … SuppN Supplementary Data N SuppDataN

[SCRN + timestamp],[data1],[data2],…….., [dataN], [Supp1], [Supp2], …. [SuppN]

user manual data query tool - education bureau

Documents