IBM TRIRIGA Version 10.4
How to Integrate Data into
Tririga Real Estate
Environmental
Sustainability Impact
Manager – Staging Tables
© Copyright International Business Machines Corporation 2014.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.
CONTENTS
List of Figures..................................................................................................................... 3
Revision History.................................................................................................................. 4
1Introduction....................................................................................................................... 5
2Overview.......................................................................................................................... 5
3cstBMSPropertyMap......................................................................................................... 5
4cstBMSSummarizedMonthly............................................................................................. 6
5cstBMSSummarizedDaily................................................................................................. 8
6cstBMSSummarizedHourly............................................................................................... 9
7Data Points..................................................................................................................... 10
7.1AVG_Gauge32_nn.......................................................................................... 10
7.2TOT_Counter64_nn........................................................................................ 12
7.3triFactEnergyUseNULAT_Short_String_nn..................................................... 12
8Product documentation................................................................................................... 13
LIST OF FIGURES
Introduction to the Gantt Scheduler
REVISION HISTORY
Date
Version
Comments
14 March 2014
1.0
Initial version.
2 April 2014
1.1
Minor updates
TRIRIGA Real Estate Environmental Sustainability: Integrating data
1
Introduction
This document will describe the sample staging tables so as to allow you to understand
how to populate a row in each of the staging tables.
There are several pieces of information that you need in order to populate a row in each of
these tables:
1) the name of the table
2) the column names
3) which columns are required and which ones are optional
4) any special instructions about the format of the value
Armed with this information, you should be able to populate the row correctly.
2
Overview
Extensibility was one of the primary design points for the sample staging tables. The column
names in the staging tables could be chosen to match the column names in the fact tables. While
this design would be simple, every time a new metric is added, the staging table, and the ETL that
moves data from the staging table to the fact table would need to be changed.
Instead of a 1-to-1 name mapping, the sample staging tables and staging table-to-fact table ETLs
were designed to use a metadata table. The staging tables contain generically named columns, for
example the generic column named AVG_Gauge32_01 can store a number. Rows in the
S_cstBMSPropertyMap table indicate which fact table column each generic staging table column
maps to. For example:
AVG_Gauge32_01 ------ TRIFACTOUTSIDEAIRTEMPC
You could also think of this as a late-binding of the columns in the staging tables. By defining this
mapping at run-time rather than design-time of the staging tables, we can add, remove, and
change mappings without needing to change the staging tables nor the staging table-to-fact table
ETLs.
3 cstBMSPropertyMap

Module: cstBMSIntegration

Business Object: cstBMSPropertyMap
TRIRIGA Real Estate Environmental Sustainability: Integrating data

Database Table Name: S_cstBMSPropertyMap
BLUE is used to highlight the required values.
Column Name
Node
Data Type
varchar(32) not null
Summary_Column_Name
varchar(400) not null
Tririga_Property
varchar(400)
Timestamp
char(16)
Example:
Description
A unique identifier. If integrating
with an ITM agent, this is the ITM
Node name, e.g. <product_code>:
<subnode>: <subnode_type>
The property/column name in the
staging table, e.g.
S_cstBMSSummarizedHourly
The property name in the BOs in
the triMetricFact module to which
this value/column maps
The time in format
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
Example Value
E1:AHU1:AHU
AVG_Gauge32_01
TRIFACTOUTSIDEAIRTEM
PC
For example Feb 12, 2013
9:31:54:123 pm would be
represented as
1130212213154123
TRIRIGA Real Estate Environmental Sustainability: Integrating data
4 cstBMSSummarizedMonthly

Module: cstBMSIntegration

Business Object: cstBMSSummarizedMonthly

Database Table Name: S_cstBMSSummarizedMonthly
Note: there is no flag/column that needs to be set to indicate that this row is complete and
ready to be processed. Instead, the sample ETL that will process this row and populate the
fact table is configured to process the row X hours after the interval the row represents.
This means the row may be created at any time during the interval, and updated as many
times as desired during the interval. Any updated to the row after the row has been
processed will be ignored. More details about this sample ETL can be found here <TBD>
BLUE is used to highlight the required values.
Column Name
SAMPLES
Data Type
decimal(10) NOT NULL
DBTMZDIFF
decimal(10) NOT NULL
DBWRITETIME
char(16) NOT NULL
Description
The number of time samples of
data included in this
summarization.
The ETL can use this number to
determine whether enough
samples were collected to make
the Average meaningful. During a
given interval, network issues,
BMS system maintenance, or
system failures may cause
interruptions in the data
collection. This allows us to be
aware of those issues. For
example, if samples are collected
every 15 minutes and this is a 31
day month, then if no data
collection errors occur, we’ll
collect 2976 samples. If we only
collect 1488 samples, you may
want to the ETL to ignore this row
entirely.
The number of seconds offset
from Greenwich Mean Time
(GMT) that the DBWRITETIME
is. Add this number of seconds to
the DBWRITETIME to get the
time normalized to GMT.
The time the data was collected.
The time in format
Example Value
2900
For example, if
you are in US
Eastern Standard
Time, GMT – 5
hrs, this column
will be 18000
For example Feb
12, 2013
TRIRIGA Real Estate Environmental Sustainability: Integrating data
TMZDIFF
decimal(10) NOT NULL
WRITETIME
char(16) NOT NULL
Node
varchar(32)
Timestamp1
Device_Name
char(16)
varchar2(400)
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
9:31:54:123 pm
would be
represented as
113021221315412
3
The number of seconds offset
from Greenwich Mean Time
(GMT) that the WRITETIME is.
Add this number of seconds to the
WRITETIME to get the time
normalized to GMT.
The starting time of the time
period that the data represents.
The time in format
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
For example, if
you are in US
Eastern Standard
Time, GMT – 5
hrs, this column
will be 18000
For example, if
the data represents
the month from
January 1, 2013
12:01 am until
January 31, 2013
11:59pm, it would
be represented as
113010000000000
0
A unique identifier. If integrating
with an ITM agent, this is the
ITM Node name, e.g.
<product_code>: <subnode>:
<subnode_type>
E1:AHU1:AHU
This is primarily for debugging,
specifically to help identify the
origin of this row of data.
The time the data was collected.
The time in format
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
This is primarily for debugging,
specifically to help identify the
origin of this row of data.
The unique name of the device
This is primarily for debugging,
For example
January 30, 2013
9:31:54:123 pm
would be
represented as
113013021315412
3
RTP-B510Floor1-AHU1
TRIRIGA Real Estate Environmental Sustainability: Integrating data
Device_Type
varchar2(400)
Device_SubType
varchar2(400)
LAT_Nameplate_I
D
varchar2(150)
TOT_Counter64_0
1
decimal(31)
MAX_Gauge32_n
n
where nn = 01 - 30
AVG_Gauge32_nn
where nn = 01 - 30
LAT_Short_String
_nn
where nn=01-04
decimal(31)
decimal(31)
varchar2(150)
specifically to help identify the
origin of this row of data.
The device type. The values that
are recognized by the current code
are as follows:
- AHU
- Chiller
- Meter
A sub-type for a device.
The value of the Nameplate ID
property in TRIRIGA to which
this data corresponds.
For a number which has not
decreased during this interval, this
is the sum of the deltas between
all of the samples.
The largest value among the
samples for this interval
The average value of the samples
for this interval
For a non-numeric value
associated with this device, for
example Economizer Mode. Only
one value can be specified, it
should be the latest/last value or
the value that most represents this
interval.
Example:
5 cstBMSSummarizedDaily
AHU
For example
“roof” might be a
sub-type of Air
Handling Unit
EQ-100001
2579845.6
27.1
25.2
true
TRIRIGA Real Estate Environmental Sustainability: Integrating data

Module: cstBMSIntegration

Business Object: cstBMSSummarizedDaily

Database Table Name: S_cstBMSSummarizedDaily
Note: there is no flag/column that needs to be set to indicate that this row is complete and
ready to be processed. Instead, the sample ETL that will process this row and populate the
fact table is configured to process the row X hours after the interval the row represents.
This means the row may be created at any time during the interval, and updated as many
times as desired during the interval. Any updated to the row after the row has been
processed will be ignored. More details about this sample ETL can be found here <TBD>
BLUE is used to highlight the required values.
Column Name
SAMPLES
Data Type
decimal(10) NOT
NULL
DBTMZDIFF
decimal(10) NOT
NULL
DBWRITETIME
char(16) NOT NULL
Description
The number of time samples of
data included in this
summarization
The ETL can use this number
to determine whether enough
samples were collected to make
the average meaningful. During
a given interval, network
issues, BMS system
maintenance, or system failures
may cause interruptions in the
data collection. This allows us
to be aware of those issues. For
example, if samples are
collected every 15 minutes,
then if no data collection errors
occur, we’ll collect 96 samples.
If we only collect 48 samples,
you may want to the ETL to
ignore this row entirely.
The number of seconds offset
from Greenwich Mean Time
(GMT) that the
DBWRITETIME is. Add this
number of seconds to the
DBWRITETIME to get the
time normalized to GMT.
The time the data was
collected. The time in format
CYYMMDDHHmmSSsss,
where
C = century, 1 = 2000’s
YY = year
MM = month
Example Value
96
For example, if you are
in US Eastern Standard
Time, GMT – 5 hrs,
this column will be
18000
For example Feb 12,
2013 9:31:54:123 pm
would be represented
as
1130212213154123
TRIRIGA Real Estate Environmental Sustainability: Integrating data
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
TMZDIFF
decimal(10) NOT
NULL
WRITETIME
char(16) NOT NULL
Node
varchar(32)
Timestamp1
Device_Name
char(16)
varchar2(400)
The number of seconds offset
from Greenwich Mean Time
(GMT) that the WRITETIME
is. Add this number of seconds
to the WRITETIME to get the
time normalized to GMT.
The starting time of the time
period that the data represents.
The time in format
CYYMMDDHHmmSSsss,
where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
For example, if you are
in US Eastern Standard
Time, GMT – 5 hrs,
this column will be
18000
A unique identifier. If
integrating with an ITM agent,
this is the ITM Node name, e.g.
<product_code>: <subnode>:
<subnode_type>
E1:AHU1:AHU
This is primarily for
debugging, specifically to help
identify the origin of this row
of data.
The time the data was
collected. The time in format
CYYMMDDHHmmSSsss,
where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
This is primarily for
debugging, specifically to help
identify the origin of this row
of data.
The unique name of the device
This is primarily for
For example, if the
data represents the day
from January 11, 2013
12:01 am until January
11, 2013 11:59pm, it
would be represented
as
1130111000000000
For example January
11, 2013 9:31:54:123
pm would be
represented as
1130111213154123
RTP-B510-Floor1AHU1
TRIRIGA Real Estate Environmental Sustainability: Integrating data
Device_Type
varchar2(400)
Device_SubType
varchar2(400)
LAT_Nameplate_ID
varchar2(150)
TOT_Counter64_01
decimal(31)
AVG_Gauge32_nn
where nn = 01 - 30
LAT_Short_String_
nn
where nn=01-04
decimal(31)
varchar2(150)
debugging, specifically to help
identify the origin of this row
of data.
The device type. The values
that are recognized by the
current code are as follows:
- AHU
- Chiller
- Meter
A sub-type for a device.
The value of the Nameplate ID
property in TRIRIGA to which
this data corresponds.
For a number which has not
decreased during this interval,
this is the sum of the deltas
between all of the samples.
The average value of the
samples for this interval
For a non-numeric value
associated with this device, for
example Economizer Mode.
Only one value can be
specified, it should be the
latest/last value or the value
that most represents this
interval.
AHU
For example “roof”
might be a sub-type of
Air Handling Unit
EQ-100001
2579845.6
25.2
true
6 cstBMSSummarizedHourly

Module: cstBMSIntegration

Business Object: cstBMSSummarizedHourly

Database Table Name: S_cstBMSSummarizedHourly
Note: there is no flag/column that needs to be set to indicate that this row is complete and
ready to be processed. Instead, the sample ETL that will process this row and populate the
fact table is configured to process the row X hours after the interval the row represents.
This means the row may be created at any time during the interval, and updated as many
times as desired during the interval. Any updated to the row after the row has been
processed will be ignored. More details about this sample ETL can be found here <TBD>
BLUE is used to highlight the required values.
TRIRIGA Real Estate Environmental Sustainability: Integrating data
Column Name
SAMPLES
Data Type
decimal(10) NOT
NULL
DBTMZDIFF
decimal(10) NOT
NULL
DBWRITETIME
char(16) NOT
NULL
TMZDIFF
decimal(10) NOT
NULL
WRITETIME
char(16) NOT
NULL
Description
The number of time samples of
data included in this
summarization
The ETL can use this number to
determine whether enough
samples were collected to make
the average meaningful. During a
given interval, network issues,
BMS system maintenance, or
system failures may cause
interruptions in the data collection.
This allows us to be aware of
those issues. For example, if
samples are collected every 15
minutes, then if no data collection
errors occur, we’ll collect 4
samples. If we only collect 1
sample, you may want to the ETL
to ignore this row entirely.
The number of seconds offset from
Greenwich Mean Time (GMT)
that the DBWRITETIME is. Add
this number of seconds to the
DBWRITETIME to get the time
normalized to GMT.
The time the data was collected.
The time in format
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
The number of seconds offset from
Greenwich Mean Time (GMT)
that the WRITETIME is. Add this
number of seconds to the
WRITETIME to get the time
normalized to GMT.
The starting time of the time
period that the data represents. The
time in format
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
Example Value
4
For example, if you
are in US Eastern
Standard Time,
GMT – 5 hrs, this
column will be
18000
For example Feb 12,
2013 9:31:54:123
pm would be
represented as
1130212213154123
For example, if you
are in US Eastern
Standard Time,
GMT – 5 hrs, this
column will be
18000
For example, if the
data represents the
hour from January
11, 2013 2:01 am
until January 11,
2013 2:59am, it
would be
represented as
1130111020000000
TRIRIGA Real Estate Environmental Sustainability: Integrating data
mm = minute
SS = second
sss = milliseconds
Node
Timestamp1
Device_Name
varchar(32)
char(16)
varchar2(400)
Device_Type
varchar2(400)
Device_SubType
varchar2(400)
LAT_Nameplate_ID
varchar2(150)
TOT_Counter64_01
decimal(31)
A unique identifier. If integrating
with an ITM agent, this is the ITM
Node name, e.g. <product_code>:
<subnode>: <subnode_type>
This is primarily for debugging,
specifically to help identify the
origin of this row of data.
The time the data was collected.
The time in format
CYYMMDDHHmmSSsss, where
C = century, 1 = 2000’s
YY = year
MM = month
DD = day
HH = hour
mm = minute
SS = second
sss = milliseconds
E1:AHU1:AHU
For example
January 11, 2013
2:31:54:123 am
would be
represented as
1130111023154123
This is primarily for debugging,
specifically to help identify the
origin of this row of data.
The unique name of the
device
This is primarily for
debugging, specifically to
help identify the origin of
this row of data.
The device type. The values
that are recognized by the
current code are as follows:
- AHU
- Chiller
- Meter
A sub-type for a device.
The value of the Nameplate
ID property in TRIRIGA to
which this data
corresponds.
For a number which has not
decreased during this
interval, this is the sum of
the deltas between all of the
samples.
RTP-B510Floor1-AHU1
AHU
For example
“roof” might
be a sub-type
of Air
Handling
Unit
EQ-100001
2579845.6
TRIRIGA Real Estate Environmental Sustainability: Integrating data
AVG_Gauge32_nn
where nn = 01 - 30
LAT_Short_String_nn
where nn=01-04
decimal(31)
varchar2(150)
7
Data Points
7.1
AVG_Gauge32_nn
The average value of the
samples for this interval
For a non-numeric value
associated with this device,
for example Economizer
Mode. Only one value can
be specified, it should be
the latest/last value or the
value that most represents
this interval.
25.2
true
The data values that need to be populated in the staging table will vary depending on what
you have available from your building management system and which analytics, reports,
and scorecards you intend to use. Refer to the product documentation XXX for information
about which fact table fields are needed for which analytics, reports, and scorecards. Also
refer to XXX for the units of measure (UOM) for each value.
Note: In general, the suffix of the field name contains the UOM. Most names in with NU
to indicate it is a number. Preceding that, the character(s) indicate the OUM: LM = liters
per minute, Pct = percentage, Amp = amps, C = degrees Celsius, JKG = Joules per
kilogram, W = watts, PPM = parts per million.
The Staging Table will include some or all AVG_Gauge32_nn values that map to the
following fact table fields:
1) triFactCoolantFlowLMNU
2) triFactCoolingValvePctNU
3) triFactEnergyUseNU
4) triFactExhaustFanCurrentAmpNU
5) triFactExhaustFanOutputPctNU
6) triFactHeatingValvePctNU
7) triFactHumidifierValvePctNU
8) triFactMixAirTempCNU
9) triFactOutsideAirDamperMinPctNU
10) triFactOutsideAirDamperPctNU
11) triFactOutsideAirTempCNU
12) triFactOutsideEnthalpyJKGNU
13) triFactOutsideHumidityPctNU
TRIRIGA Real Estate Environmental Sustainability: Integrating data
14) triFactPowerUsageWNU
15) triFactPreheatValvePctNU
16) triFactReheatValvePctNU
17) triFactReturnAirCO2PPMNU
18) triFactReturnAirTempCNU
19) triFactReturnCoolantTempCNU
20) triFactReturnFanOutputPctNU
21) triFactSteamValvePctNU
22) triFactSupplyAirTempCNU
23) triFactSupplyAirTempSPCNU
24) triFactSupplyCoolantTempCNU
25) triFactSupplyCoolantTempSPCNU
26) triFactSupplyFanCurrentAmpNU
27) triFactSupplyFanOutputPctNU
28) triFactSupplyRelHumiditySPPctNU
29) triFactZoneRelHumidityPctNU
30) triFactZoneTempCNU
7.2
TOT_Counter64_nn
The data values that need to be populated in the staging table will vary depending on what
you have available from your building management system and which analytics, reports,
and scorecards you intend to use. Refer to the product documentation XXX for information
about which fact table fields are needed for which analytics, reports, and scorecards. Also
refer to XXX for the units of measure (UOM) for each value.
Note: In general, the suffix of the field name contains the UOM. Most names in with NU
to indicate it is a number. Preceding that, the character(s) indicate the OUM: LM = liters
per minute, Pct = percentage, Amp = amps, C = degrees Celsius, JKG = Joules per
kilogram, W = watts, PPM = parts per million.
The Staging Table will include some or all TOT_Counter64_nn values that map to the
following fact table fields:
1) triFactEnergyUseNU
7.3
triFactEnergyUseNULAT_Short_String_nn
The Staging Table will include LAT_Short_String_nn values that map to the following fact
table fields:
1) triFactEconomizerModeNU (Staging table values:: true and - )
2) triFactExhaustFanStatusNU (Staging table values: Active and - )
3) triFactOccupiedCommandNU (Staging table values: No and - )
4) triFactSupplyFanStatusNU
(Staging table values: Off and -)
TRIRIGA Real Estate Environmental Sustainability: Integrating data
8
Product documentation
TBD
TRIRIGA Real Estate Environmental Sustainability: Integrating data
®
© Copyright IBM Corporation 2013
IBM United States of America
Produced in the United States of America
US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP
Schedule Contract with IBM Corp.
IBM may not offer the products, services, or features discussed in this document in other countries.
Consult your local IBM representative for information on the products and services currently
available in your area. Any reference to an IBM product, program, or service is not intended to state
or imply that only that IBM product, program, or service may be used. Any functionally equivalent
product, program, or service that does not infringe any IBM intellectual property right may be used
instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM
product, program, or service.
IBM may have patents or pending patent applications covering subject matter described in this
document. The furnishing of this document does not grant you any license to these patents. You
can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.
The following paragraph does not apply to the United Kingdom or any other country where such
provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PAPER “AS IS”
WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR
FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes may be
made periodically to the information herein; these changes may be incorporated in subsequent
versions of the paper. IBM may make improvements and/or changes in the product(s) and/or the
program(s) described in this paper at any time without notice.
Any references in this document to non-IBM Web sites are provided for convenience only and do
not in any manner serve as an endorsement of those Web sites. The materials at those Web sites
are not part of the materials for this IBM product and use of those Web sites is at your own risk.
IBM may have patents or pending patent applications covering subject matter described in this
document. The furnishing of this document does not give you any license to these patents. You
can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
4205 South Miami Boulevard
Research Triangle Park, NC 27709 U.S.A.
All statements regarding IBM's future direction or intent are subject to change or withdrawal without
notice, and represent goals and objectives only.
TRIRIGA Real Estate Environmental Sustainability: Integrating data
This information is for planning purposes only. The information herein is subject to change before
the products described become available.
If you are viewing this information softcopy, the photographs and color illustrations may not appear.
Trademarks
IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business
Machines Corp., registered in many jurisdictions worldwide. Other product and service names
might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the
Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation
in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle
and/or its affiliates.
Other product and service names might be trademarks of IBM or other companies.