Moving to a Centralized Database for Surveys in

Moving to a Centralized Database for Surveys in Blaise at NASS Roger Schou National Agricultural Statistics Service IBUC XII Riga, Latvia

NASS – 46 Field Offices

NASS – 6 Data Collection Centers Montana Kentucky Wyoming Virginia Oklahoma Arkansas

Distributed Environment • HQ-developed instrument typically distributed to 43 field offices • 43 Blaise datasets • Physical transfer of forms from Data Collection Centers to Client States • Survey management done in individual field offices

Centralization Idea • Blaise 4. 8. 1 – Storing data in generic tables in a central database • Enable the Blaise data collection processes to be centralized • Splash of an idea to centralize one or two applications (CASIC and Survey Management System)

Centralization Idea • Current Status of Development: – Too many stovepipe applications – Development vacuums – Lack of sharing code • Duplication of effort – Duplication of functionality – Management has the “I want it now” mentality

Centralization Idea • Tidal wave - Centralization on an enterprise level – Blaise – Survey Management System – ELMO – Sample Design – EDR/EDC – PRISM – Analysis/Summary

Database Decision • Choosing a database – Cost – Support – Commonality • NASS databases – – – Sybase Redbrick Fox. Pro Oracle My. SQL ? Blaise

Generic BOI Files • Blaise creates 8 tables – Blaise_Case – Blaise_Data – Blaise_Dictionary – Blaise_Form – Blaise_ID – Blaise_Key – Blaise_Open – Blaise_Remark

Generic BOI Files • All surveys will be stored in a fixed, predefined structure • Translation tool to extract the data to our PRISM database (Sybase) will work for all Blaise data PRISM Blaise data Translation Tool

Versioning • We plan on using Blaise versioning • Management made a requirement that original reported data must be preserved

Folder Structures • At the onset we have to plan on allowing all field offices to run CATI • Currently planning to continue to use the Blaise call scheduler • The. BTR, . BOI, . BMI, . BDM, and. BXI files will be stored in unique folders for each field office (instrument and externals) – It would be nice to store the instrument files (and externals) in one common folder* hqappscasicsurveyname datacasicsurveynamefo6

User Access Rights • • Who can view and/or update the data? Distributed – Physical Boundaries Centralized – User Access Controls Roles – HQ Statistician – Field Office Statistician – Supervisor Interviewer – Interviewer

User Access Rights • A policy has been written and management approved • A team has been assembled to discuss the details and prepare for implementation

Additional CASIC Tables • Information Tables – Survey Information Table – FIPS Allocation Table – User Information Table • Sample Master Table • Error Limits Table • Previously Reported Data Table

Initializing the Database • Three Steps: – Survey Setup (HQ) • Folder creation – Initialize Preparation (FO) • Name & address files validated – Initialize (CRON / HQ) • Populate the database • Easily monitored by Survey Administrators • Problems easier to isolate

Testing Plans • Scheduler Testing – Make sure that we did not introduce issues with the Blaise scheduler across field offices • Load Testing – Must support a maximum of 600 users simultaneously • Process Testing – What are the locking issues, if any, between processes

Benefits • Real-time reporting of survey progress • Eliminating physical movement of forms • Bringing survey processes together – Seamless communication between systems – Eliminate “stove-pipe” applications • Sharing of code

Challenges • Implementing user access rights • Dealing with Blaise datamodel changes • Coordinating – Interviewers across the country • Routing cases across call centers – Multiple modes of data collection – Other processes being centralized • Bridging the gap between applications during transition

Conclusion • Exciting time of development • Positive attitude towards centralization – Anticipating the benefits to be gained • Management is realizing the large scope – Stepping back and realizing the development process (less of “I want it now”) • Communication is open and working well • Many processes evolving together

Questions