IBM TRIRIGA Version 10.4 How to Integrate Data into Tririga Real Estate Environmental
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.
© Copyright 2024