To Do List for Building Maintenance

1
To Do List for Building Maintenance

• Mobile, coordinated assignments using a PDA

Jerry Samples
CS 8628, Summer 2003
2
BAS Background

• Building automation systems (BAS) monitor and
  control HVAC and other equipment
• Network of sensors, embedded controllers, and
  workstations
• Can automatically notify personnel of situations
  needing attention
• Heating, Ventilation, and Air-Conditioning

• A building automation system (BAS) can be
  programmed to notify personnel if a part of the
  system enters a predefined state, such as
  equipment failing to operate properly. Some of
  these events require building maintenance staff
  to go to the location of the faulty equipment and
  troubleshoot the problem. In a large facility,
  such as a college campus, there may be many
  events needing attention at any given time. A
  PDA can improve efficiency by allowing
  maintenance technicians to download a list of
  items currently needing attention, including the
  location of the problem and enough details to
  allow efficient resolution. With this mobile to
  do list, they can handle several problems in
  series before returning to the main office.

3
Project Description

• A quasi-realistic BAS which uses Oracle 9i and
  populates a custom table with notifications of
  (simulated) problems detected.
• The records describe problems needing human
  attention somewhere in the facility.
• Maintenance technicians use the MobileVB
  application to download these notification
  records into a PDA, which they carry around for
  reference.
• Besides the intended MobileVB aspect of the
  project, I got excellent experience using my
  companys product with Oracle, which I had not
  previously done.

• Automated Logic Corps WebCTRL Pro (pronounced
  web control) BAS software was installed and
  configured to use Oracle 9i databases and
  simulate monitoring and control of a small office
  building. In response to simulated problems, it
  populates a custom table with problem details. A
  MobileVB application transfers these records to a
  PDA, which a technician could carry around the
  facility for reference as they dealt with each
  situation. The traditional alternative is to
  print out a list of issues or log in to a
  workstation to see the current problem list.

4
Sequence of Tasks (1)

• Create VMware virtual machine (VM) capable of
  running enterprise-class database and BAS
  software
• Install Microsoft ActiveSync on VM
• Install Oracle 9i Personal Edition software
• Install Automated Logics WebCTRL Pro 2.0
  software
• Port Automated Logics sales demo system to
  Oracle 9i
• Configure WebCTRL notifications for certain
  events to insert records into a custom Oracle
  table

• Because of the great size of Oracle, WebCTRL, and
  Visual Basic, I created a VM with a virtual hard
  disk size of 15 GB. I also increased my
  computers system RAM from 512 MB to 1 GB so that
  the VM could have more RAM allocated and improve
  performance. Once Oracle and WebCTRL were
  installed, I ported the WebCTRL Sales demo
  software over to have quasi-realistic database
  files on which to operate. I learned many new
  things about WebCTRL and Oracle while setting up
  this environment.

5
Sequence of Tasks (2)

• Install Visual Basic 6.0
• Install AppForges MobileVB extensions to VB for
  creating PDA-specific VB applications
• Install Sybase SQL Anywhere 8 database system,
  including Ultralite database for PDAs and
  Mobilink synchronization server
• Generate MobileVB app to display records
• Generate sync. scripts for both directions

• Several pieces of software work together to
  maintain (1) the primary database, (2) a reduced
  but truly relational PDA database, (3) a
  middleware component for true database
  synchronization, and (4) a convenient environment
  for development of the PDA applications user
  interface and functionality.

6
WebCTRL

• WebCTRL displays graphical representations of a
  buildings floorplans and equipment showing live
  sensor data. It can also present graphs of
  collected data samples (trend data) of various
  sources. The figure here shows colors on the
  floorplans to indicate areas that are too warm
  (orange, red) and too cool (blue), On the right
  is a graph of electrical power usage over time.
  Data such as the buildings configuration, as
  well as trend data are stored in databases. An
  Internet Explorer (5.5 or higher) browser is the
  primary user interface to a running system,
  although some data can be accessed via SQL and
  XML/SOAP.

7
Port to Oracle 9i

• WebCTRL creates numerous tables in databases
  which are used for a variety of purposes. Often,
  four separate databases are created (see figure),
  so that each can have characteristics appropriate
  for a given purpose. However, a single database
  is also acceptable in most cases. The figure
  above shows part of the manual configuration of
  WebCTRL, where JDBC connection information must
  be supplied during the one-time engineering phase
  of a new system. The four databases (wcmain,
  etc.) were created prior to this step using the
  Oracle Database Configuration Assistant.

8
E-R Description

• HVAC Equipment (air handlers, fans, water
  chillers, etc.) . . .
• Is monitored by a . . .
• Building Automation System, which . . .
• Generates . . .
• Problem and Event Notifications, which are . . .
• Corrected by a . . .
• Maintenance technician

• This list identifies the key sequence of
  operations of a BAS in its role of monitoring a
  facility and automatically notifying personnel of
  situations that require human intervention. The
  cardinalities given here are specific to my
  project. The states which trigger notification,
  as well as the specific forms of notification,
  must be programmed in advance. Notifications do
  not always indicate a problem. They may also be
  used as ticklers for scheduled maintenance
  operations, such as replacing motors, fan belts,
  air filters, etc.

9
E-R Diagram

• This diagram represents the sequence given on the
  previous slide in list form. HVAC equipment may
  generate multiple notifications, but each one
  will only be dealt with by one technician.
  Important information that should be in the
  notification record includes timestamp,
  physical location of the problem source, status
  code or parameter value, and type (general nature
  or category) of the event.

10
Logical Schema

• In any given system, there is a limited number of
  event types (categories) defined. Each is
  identified by a short name, but also has
  associated with it a verbose description of the
  problem it represents. These two items are
  placed in one table which I call EVENT_TEXT. The
  table that receives the event notifications,
  ACTION_ITEMS, uses category as a short foreign
  key. This way, the lengthy string is not stored
  repeatedly for different events of the same type.
  If and when the verbose description is needed,
  it is looked up in EVENT_TEXT.

11
Physical Schema (DDL)
SQLgt create table event_text ( 2 category
VARCHAR2(50), 3 description VARCHAR2(300), 4
primary key (category) ) Table created. SQLgt
create table action_items ( 2 timestp DATE,
3 location VARCHAR2(500), 4 status
VARCHAR2(100), 5 category VARCHAR2(50), 6
primary key(timestp), 7 foreign key(category)
references event_text ) Table created.

• The data dictionary for my two tables is shown in
  the form of the SQL statements I used to create
  them in Oracles SQLPlus interactive
  environment. I chose arbitrary field lengths
  which I thought were sufficiently long for most
  realistic cases, and certainly long enough for my
  project examples.

12
Prepopulated Table

• SQLgt select from event_text
• CATEGORY
• --------------------------------------------------
  
• DESCRIPTION
• --------------------------------------------------
  ------------------------------
• Filter Dirty
• Air filter reports dirty. Check or change
  filter.
• Damper Stuck
• Damper appears stuck. Check damper operation.
• Temp Sensor
• Temperature sensor fault. Check for proper
  wiring.

• For my project, I defined three event categories.
  Their short names are in the category column,
  and the verbose descriptive text is from the
  description field. All three records are shown
  in the listing above. This table would not
  usually be altered once a system is engineered
  unless a new category was added. In rare cases,
  the descriptive text might be changed to be more
  informative.

13
Custom SQL to DB (1)

• This figure gives an idea of the WebCTRL user
  interface page, within the Internet Explorer
  browser, where the BAS administrator configures
  certain states to generate records into the
  consolidated database.

14
Custom SQL to DB (2)

• This figure shows detail from the previous slide.
  At the very bottom of the figure, you can see an
  SQL statement which is executed any time the
  specified state is entered. Replaceable
  parameters are given between dollar signs (e.g.
  Generation_Time). WebCTRL will provide the
  actual values at the time the SQL statement is
  executed. The SQL statement can not have a
  semi-colon at the end, or else an error occurs
  the semicolon is handled by WebCTRL.

15
MobileVB App

• Working name is WebCTRL Action Item Manager, or
  WebAIM for short (did I miss my calling in
  Marketing or what?)
• Synchronize with consolidated database and
  retrieve any action item entries
• Page through entries to see the problems reported
• After resolving a problem, delete the record from
  the PDAs local database
• When return to the main office, synchronize
  again. Records deleted on PDA are deleted from
  consolidated database new entries since
  previous consolidation download to PDA

• The bullet list above describes the basic steps
  that would be followed when using this
  application. The following slides show some
  screen shots of the actual PDA with real data
  synchronized from the Oracle database, which had
  been populated by a running WebCTRL 2.0 system.
  I manuallyaltered parameters of the running
  system to simulate error conditions so that the
  error reporting and database logging would occur.

16
Synchronization Script

• SQLgt select table_name from user_tables where
  table_name like 'ML_'
• TABLE_NAME
• ------------------------------
• ML_CONNECTION_SCRIPT
• ML_SCRIPT
• ML_SCRIPTS_MODIFIED
• ML_SCRIPT_VERSION
• ML_SUBSCRIPTION
• ML_TABLE
• ML_TABLE_SCRIPT
• ML_USER
• 8 rows selected.
• Custom synchronization code
• exec ml_add_table_script( 'actionitem',
  'action_items', 'upload_delete', - 'DELETE
  from action_items where timestp ?' )

• I ran the syncora.sql script provided by Sybase.
  This created 8 tables named ML_ and several
  stored procedures. The only additional
  synchronization SQL code I had to run was to
  establish the record deletion behavior. It
  needed to make sure that records marked as
  deleted on the PDA are removed from the
  consolidated (Oracle) database during
  synchronization. In general, the default sync.
  scripts worked for me.

17
Code Sample 1

• Private Sub mnuViewNext_Click()
• ' If Next was enabled, there are gt1 items, so
  we
• ' need to Enable Prev button/menu
• frmItems.mnuViewPrev.Enabled True
• frmItems.btnPrev.Enabled True
• actn_tbl.MoveNext
• ' Test for end of table and if so, disable
  Next menu/button
• actn_tbl.MoveNext
• If actn_tbl.EOF Then
• frmItems.btnNext.Enabled False
• frmItems.mnuViewNext.Enabled False
• End If
• actn_tbl.MovePrevious
• Set_Item_Fields

• This MobileVB code is called when the user clicks
  the Next button or chooses View Next from the
  menu. If the new row is the last, the Next
  options are disabled. The technique used to
  determine this is to two row advances and see if
  the second one takes us beyond the last row
  (EOF). If so, then back up one to point at the
  correct (and final) row.

18
Code Sample 2

• Private Sub Set_Item_Fields()
• Dim m_CatStr As String
• dataLocx.Caption actn_tbl.Column("location")
  .StringValue
• dataStatus.Caption actn_tbl.Column("status")
  .StringValue
• dataTimeOccur.Caption FormatDateTime(actn_tb
  l.Column("timestp").DatetimeValue, vbGeneralDate)
• ' Lookup description from event_text table
  that matches
• ' the short category string in actn_tbl
• m_CatStr actn_tbl.Column("category").StringV
  alue
• descrip_tbl.FindBegin
• descrip_tbl.Column("category").StringValue
  m_CatStr
• If descrip_tbl.FindFirst Then
• dataDescrip.Caption descrip_tbl.Column("
  description").StringValue
• End If
• End Sub

• This MobileVB code is called anytime the action
  item form is displayed or the content changed.
  This happens on Next and Prev actions. The
  timestamp, location, and status fields are taken
  from the action_items table. The long
  descriptive text is found in the event_text
  table by using category as a foreign key.

19
WebAIM UI (1)

• Entry screen upon launching the application. If
  any action items are in the PDAs local Ultralite
  database, the number will be shown. If there
  hasnt been a first synchronization yet, this
  will show 0.

20
WebAIM UI (2)

• Typical action item page. Location field
  describes where the source of the problem is
  believed to be. Description is supposed to be a
  helpful hint about what to do. Status describes
  the state triggering the event. OFF NORMAL is
  an industry term. Occurred is the time at which
  the notification was generated.

21
WebAIM UI (3)

• The Help About popup dialog.

22
Difficulties Encountered (1)

• Getting synchronization to work hard to find
  what was possible, what tables and stored
  procedures were created and how used.
• DATE vs. TIMESTAMP data types in Schema Painter.
  I thought DATE was always a high-precision
  timestamp, but Schema Painter truncates this
  type. Using TIMESTAMP in my .USM allowed me to
  use the event time as my primary key
• Not aware that Sybase provided scripts
  (syncora.sql) to prepare Oracle to work with
  Mobilink. Happened to stumble across this.
  Later, also found script to allow CustDB to work
  with Oracle (custora.sql). Once found, these
  worked great.
• By default, clicking X in upper right of
  PocketPC 2002 apps only minimizes app. Doesnt
  close it. Cant deploy updated app until closed.
  Can add a handler.
• Commit, commit, commit. During interactive
  SQLPlus, if I changed records without a commit,
  PDA syncs appeared to not work correctly. DOH!
• If dont Hide one form before Show another, both
  are visible and accessible, which could allow
  improper sequences of commands. Use Me.Hide and
  Show methods

• I did not encounter any HUGE roadblocks. Rather,
  I experienced a continuous series of many, many
  small and medium-sized obstacles. I knew what
  pieces needed to work together, but making them
  do so was a challenge at every turn. Part of the
  problem was difficulty finding adequate
  dosumentation explaining what was possible, what
  options were available, and what the APIs were.
  I ended up using about three or four different
  sets of help documentation, as well as the VB
  ObjectBrowser tool, which was in some ways the
  most helpful.

23
Difficulties Encountered (2)

• Had to go to AppForge web site and download
  30-day license before could deploy to PDA,
• Apps created only have a two-day life. Dont take
  the PDA on the road for a week of sales demos
  without a development laptop as well.
• Mobilink can only connect to one ODBC datasource
  at a time. This was not clear initially when I
  tried to run my system and the CustDB
  simultaneously.
• Disabling top-level menus (e.g. Edit) doesnt
  really disable it on PDA. Simulator behaves as
  expected. On PDA, however, need to disable all
  submenu items to keep them from being available.
• SysParms members were not completely described,
  so had to guess at some usages
• I didnt know for a long time that VB
  ObjectBrowser was available. It is not launched
  as part of Help files, so its hard to find.

24
Conclusion

• I learned a tremendous amount working all the way
  through to a completed running system
• Saw how databases from different vendors can
  interoperate effectively
• Learned what configurations need to be performed
  when starting all pieces from scratch
• Commit! Commit! Commit! when performing
  interactive SQL sessions during app development
  to avoid confusion