Staging Tables Overview

Staging tables are used in PowerViews for version 6.x of EnergyCAP and in EnergyCAP Online 1.6 and greater.  They summarize meter data at all higher levels of the hierarchy - buildings, organizations, etc.  The data contained in the Group Manager is also based on the data contained in the staging tables.  They are updated through the following mechanisms.

Methods to update the Staging Tables

1. Login to EnergyCAP.  Click on Tools > Options Global.  Click on the Settings tab.  Check the box next to "Update Powerviews" and set a time for it to update.  When this checkbox is selected, the staging tables will be updated every X hours depending on the users setting.  WHen a user logs into EnergyCAP - any user - EnergyCAP will look to see when the staging tables were last updated.  It will comapre that date and time to the setting that is in the settings tab (ie every X hours).  If it has been more than X hours since the staging tables have been updated, the database will begin to update the staging tables.  Again, this is ONLY triggered when a user logs in.  This method IS NOT RECOMMENDED for users with large (greater than 700mb) databases.  These users should use option #2.
2. A DBA (Database Administrator) can schedule a task on the SQL Server to execute the three stored procedures involved with updating the staging tables. These procedures must be executed in the following order:
   
   1. CB2
   2. generateBillSummaryTables
   3. generateGHGSummaryTables
      
      The DBS should evaluate how long these take to complete and schedule accordingly to fit in with other maintenance tasks such as backup.
3. Finally, a user with Administrator permissions (ie permissions to use the Administrator.exe located in the EnergyCAP installation folder) can manually execute each of these stored procedures in order.  When logged into Administrator, go to Stored Procedures > Save/Execute.  Then select CB2 from the Execute Existing Procedure list and click OK.  When the procedure finishes, a popup message will appear indicating the procedure has completed.  Repeat the procedure for the generateBillSummaryTables procedure and then finally for the generateGHGSummaryTables procedure.  When finished, the staging tables will be refreshed.

Common Staging Table Data Problems

1. I just moved a building or meter to a new place and the Building or Organization powerviews are not showing the updated data now that I moved it.  Solution:  You need to update the Staging Tables since they have not been updated since you moved the building or meter.
2. I've tried to update my staging tables but my building, organization, etc. powerviews are still blank and where it says "Chart Data Current as of"" in the General Tab, it says "Not Available".  Solution:  Your staging tables have been interrupted in the process of being updated or are currently being updated for the first time.  Wait a few hours as the update process may still be in progress.  If after a few hours, the same message appears, have your DBA follow these instructions:

Using SQL Management Studio or SQL Management Studio Express, login as an administrator and execute these steps on the EnergyCAP database:

delete from systemdata
where fieldid = 'BillSummaryGenerationContext'

You should get ONE record affected.

Then, run

exec cb2

That one should run quickly. Then run

exec generatebillsummarytables

Keep checking the systemdata table to see if the BillSummaryGenerationContect field id is still there or if it disappears. When the process finishes, it will be replaced with a fieldid value of 'BillSummaryCurrentAsOf' and will have a timestamp as the value. After a few HOURS when the bill staging table process has finished, run

exec generateghgsummarytables

Keep checking the systemdata table to see if the GHGSummaryGenerationContect field id is still there or if it disappears. When the process finishes, it will be replaced with a fieldid value of 'GHGSummaryCurrentAsOf' and will have a timestamp as the value.

Technical Details about the staging table update process

You can tell if the staging table update is in progress or not by looking at the staging table. (select * from systemdata). If you see a fieldid value called BillSummaryGenerationContext you know there is an update in progress. And if you see a GHGSummaryGenerationContext fieldid then the GHG staging table is in progress. If the systemdata table has a fieldid called BillSummaryCurrentAsOf, then the process has completed updating the Bill Summary staging tables.  If the systemdata table has a fieldid called GHGSummaryCurrentAsOf, then the process has ompleted the GHG Summary staging tables.

Created by Ryan T. Ohlson last modified 09/01/2009  EnergyCAP Enterprise Rel. 6.0 and greater