Build Capacity Tables
 		Previous Topic  Next Topic 

The building of the capacity tables is used if you want to look at capacities using a third party tool that can run SQL.  (You do NOT need to use this command if you are using DMS graphs and reports.)


The purpose of the build capacity tables command is to expand the capacity arrays into tables so that a third party tool can be used to access views and generate capacity reports.  The building of the capacity tables does not impact any other area of DMS; only the scheduler would be affected where if the option is selected, the capacity tables would be built automatically after the schedule run. 


The option to automatically build capacity tables is on the Options tab of the Scheduler.  Checking the “Build capacity tables for third party application” will create tables of monthly loads for reporting purposes automatically after a schedule run.  The horizon would be from the first day of the scheduler to the end of the schedule horizon selected by the user.


You can also build capacity tables after a schedule run by selecting the command File | Build Capacity Tables.  If you will be using third party tools to always look at capacity, it is recommended that you set the option “Build capacity tables for third party application” on the Options tab so that after each schedule run the capacity tables are built automatically for you.  If you use the command File | Build Capacity Tables, the timeframe setup on the Options tab (i.e. daily or monthly) is used for the build.


DMS builds the following tables when Build Capacity Tables is selected:


Operation Load Detail

  1. Operation Load ID
  2. Operation ID
  3. Year
  4. Month number
  5. Load in hours


Capacity Load Detail

  1. Capacity ID
  2. Resource ID
  3. Year
  4. Month number
  5. Available capacity in hours
  6. Load in hours


Two new views are created that take the above tables and add pertinent information for reporting purposes:


View Operation Load Detail

  1. Operation ID
  2. Operation Identification
  3. Order Number
  4. Job identification
  5. Part Number
  6. Resource Name
  7. Resource ID
  8. Year
  9. Month number
  10. Load in hours


View Capacity Load Detail

  1. Resource ID
  2. Resource Name
  3. Year
  4. Month number
  5. Available capacity in hours
  6. Load in hours


The following is an example SQL script to display access information in the two views by joining the views together by the Resource ID:


select *

from VWCapacityLoadDetail

left outer join VWOperationLoadDetail on VWCapacityLoadDetail.ResourceID=VWOperationLoadDetail.ResourceID

order by VWCapacityLoadDetail.ResourceName, VWCapacityLoadDetail.DayDate