Importing Data to DEXMA Platform
This article includes the ways in which data can be imported to DEXMA Platform. It is divided as follows:
- Import data from an EXCEL spreadsheet
- Import historical data from a device - Join devices or import from an Excel
- Import historical quarter-hourly electrical energy from EXCEL
- Import historical degreedays from degreedays.net
- Pulse Counting (Gas, Water, etc)
1. Import data from an EXCEL spreadsheet
DEXMA Platform allows manual data entry through Excel template (.xls). Let's see how:
1.1 Main purpose:
We want to perform an energy analysis of a restaurant comparing energy consumption versus production.
The easiest way to assess this production is through daily tickets, provided in EXCEL by the restaurant's POS system.
Once January 2012 is finished, we already have a daily ticket report for the first month of the year, so that we can insert quickly to DEXMA Platform.
- Log in with a "Super Admin" or "Administrator" user role.
- Go to the "Settings" menu. Then, select the "Gateways" option on the left menu.
- Now you have to choose the Gateway which you want to edit
- Then, select the tab "Insert EXCEL"
- To upload your file, click on "New Data import"
- You can download a template if you click on "template.xls". DEXMA Platform allows you to upload multiple parameters at once. That's why we need to tell him the device and parameter you want to send.
- Device: In our case, we choose the device ID, which belongs to the "Main load". This value can be seen on Gateways > Devices. Type "12" (in this example) in the header of the first column of the Excel template. If we don't want to import data into an existing device, we could put any name in the Device blank, having no spaces. For example, we could put "context-data-device".
- Parameter: In this field, we need to introduce the parameter identifier (ID) that describes the data that is going to be uploaded. Clicking on "Parameter" will open a web browser window, with a large list of defined parameters. Towards the end of the document, you will find context parameters (commonly used in ratios - i.e: occupation, production, surface ...). As we want to insert daily tickets discretised (day by day), we choose the parameter 131 (Production) adding digit 2 (daily) and 1 (discretised). As a result, we type 13121 parameter ID in excel header cell.
- Date: below "Date" title, type the data dates (timestamps). In our example, we start in 01/01/2012 and end on 31/01/2012.
- Data: Now just type data below column "1". Excel is left as follows:
- Save it and come back to DEXMA Platform import data screen.
- Upload the file, and click on "View data" button. Check it all on screen. To import data, you click on "Import Data". It will appear a popup asking if you really want to import data. If DEXMA Platform detects any inaccuracies, it will tell us and we must modify Excel file data. (i.e. enter data where there is already, inconsistencies in dates, etc.).
- In the "Insert EXCEL" tab, it will appear all historic inserted files, available for viewing or checking data values.
- That's it! You can now configure ratios or look for correlations in "queries" page.
IMPORTANT THINGS TO NOTE:
* Your excel to import has to be a .xls file (Excel 97-2004 format)
** The maximum data to import in one single excel file is maximum 5MByte or 50.000 readings.
2. Import historical data from a device - Join devices or import from an Excel
There are two different ways of importing historical data from one device to another:
2.1 Joining devices
DEXMA Platform allows to concatenate devices and indicate which is the historical device of the other. For that, we have to follow the next steps:
1. We go to Settings > Gateways > Edit Gateways > Devices
2. We edit the main device and we select the "Historical device". Also, we have to indicate the join date of the concatenation of the devices.
In this case, we device called "Fiscal meter" has as historical data the device "ES003140_historical" and the join date of the two devices is the 31/12/2012.
3. The historical device can have another device as historical data. DEXMA Platform allows up to 5 concatenations between devices.
4. Once we have join both devices, we have the following graph. As we can see, the main device has concatenated the historical device from the join date indicated.
After the join or joins, the main device will integrate the historical data in all the menus of DEXMA Platform.
Following these steps, we have to make sure that we keep both devices because erasing the one with the historical data would mean erasing the historical data from the current one too.
2.2 Importing historical data from an Excel
To do so, it is very important to know that we have to download the data with the cumulative values (so we cannot download it from the Consumption screen).
1. First, we must go to the Queries screen and select the device from which we want to download the data, the period from which we want the data and the cumulative parameter (in case of Active Energy is just Active Energy).
2. Then, on the next screen, we have to download the Excel file with all the data.
3. Once we download it, we will have an Excel like the one shown below. We will have to change it in order to upload it again.
4. To change it in order to upload it correctly, we must download the template file that we can easily find in DEXMA Platform (Settings > Gateways > *Any Gateway* > Insert Excel > New data import ) and fill it with these values.
As we can see on the green squared area on the picture, besides copying the data from one Excel to the other we must fill these two blanks with the device ID and the parameter ID:
- Device ID: The ID of the device in which we want to add the historical data
- Parameter ID: The ID of the parameter that the data corresponds to (in case of Active Energy would be 402).
3. Import historical quarter-hourly electrical energy from EXCEL
If you have historical quarter-hourly electrical energy data probably you would like to import into your DEXMA Platform account. Let's see how to:
- Log in into DEXMA Platform with a SuperAdmin or Admin user.
- Go to "Settings" > "Gateways" and select the gateway you want to import data. If you don't have any, create a "virtual gateway" clicking on "register a new gateway"
- Go to "import Excel" tab and click on "New data import" and download the "template.xls"
- On the template, you just need to introduce the "device" identificator, the "parameter" identificator and the data you want to upload:
- The "device" identificator can be found on the "devices" tab, inside the gateway. It's on the "ID" column.
- For quarter-hourly electrical energy, the "parameter" identifier is 402, but data must be aggregated. If not, DEXMA Platform will not calculate the derived parameters (quarter-hourly, hourly, daily, weekly and monthly) properly.
- Once introduced both "device" and "parameter", it's time to introduce the readings. Simply type the timeStamp under "Date" column with the format [dd/mm/yyyy hh:mm] (01/01/2013 00:00) and the energy aggregated value under the column 1.
- If you have more than one device where to import historical data under the same gateway you can use the same spreadsheet by adding columns. See the attached Excel file to see an example.
- Then go back to the "New data import" window and select the Excel file you have generated.
- Click on "view data". DEXMA Platform will validate that there is nothing wrong on our excel and that we don't have any data already on the dates that we want to import. Remind that the EXCEL file couldn't be large than 2MB.
- If everything is Ok, click on "import data" and data will be finally imported into your devices!
4. Import historical degreedays from degreedays.net
Sometimes it's useful to analyse our energy consumption comparing with heating and cooling degree days in order to see if our systems react correctly with external conditions.
Introducing historical degree days data into DEXMA Platform it's pretty easy. Let's see how!
First of all, we need to create degree day parameters into our account and the degree day virtual meter for our location. To do that, we need to sing up a virtual weather station and to configure location reference temperatures.
Then we need to wait until next morning, when DEXMA Platform engine procedures will create our "virtual degree day meter" for our location. Now it's time to introduce historical data!
Now it's time to go to degreedays.net website and download historical data from the same weather station we selected.
Degreedays.net allows you to download until 36 months of historical data for free. Sometimes your desired weather station doesn't have enough historical data, so we advise to take Airport Weather stations if this occurs.
We need to select our weather station by writing the city or the weather ID, then select if we want Heating or Cooling degree days, Celcius or Fahrenheit, the base temperature (16ºC) and the frequency (daily). Then select the range of time we want and click on "Generate Degree Days".
Once the file is generated we can download it by pressing "download" button at the top of the page:
Now repeat the process to obtain "Cooling" degree days.
Once we have both Heating and Cooling degree days excels, it's time to put all this information into a DEXMA Platform Excel template, with the correct parameters and device identifier. You can check the parameters here and the device ID editing the gateway that has the main load, on "Devices" tab:
The device ID it's usually "DD-XXXX" and parameters are:
- 18026 - Cooling Degree Days
- 18126 - Heating Degree Days
- 18226 - Mixed Degree Days (sum of cooling and heating)
Now we simply need to copy and paste data to the template, save and upload it!
Here you can see an example of how the spreadsheet looks like:
Se how to introduce data from EXCEL spreadsheet here.
5. Pulse Counting (Gas, Water, etc)
Pulse reading is an easy method for monitoring meters based on the reading of very short duration electric or optical pulses, which are emitted by the meters in proportion to the consumption.
In this way each time the meter increases its reading in a unit, it emits a pulse that can be read by a third system for monitoring purposes. The ratio between the measured unit and the pulses depends on each meter and must be noted in order to calculate the consumption (1m3/Imp, 1kWh/imp, 10Imp/m3, etc.).
One of its most common uses is in the Gas or Water facilities, where the meters are usually equipped with Pulse emitters. To read those meters they must have a pulse emitter or the possibility of installing it. The pulse emitter model depends on the counter model and must be requested from the manufacturer or specialist distributors.
Once you have the pulse emitter, a pulse counter with Modbus communication is required to send the readings to DEXMA Platform.
For example, to read a GAS meter you need a Modbus Gateway and a compatible pulse counter that can be both wired or wireless:
When reading water, the scheme is exactly the same, but in this case, we do not have the ATEX barrier:
Some of the pulse counters compatible with DEXGate2 are, for example, Circutor's LM4I4O, the wireless ZED-TIDCI from 4-Noks or others that combine electrical and pulse measurement such as the Carlo Gavazzi EM24.
Pulse reading is also used in other applications, such as reading electrical meters, thermal energy, etc.
Optical Probes are used for the monitoring of optical pulses, where the probe transforms these optical impulses into electrical ones, or the probe can also provide Modbus communication.
Optical impulses reading using Readwatt