This article details how users can utilize queries in DataViews to examine their building performance data, before checking and exporting the results.
‘DataViews’ is a feature in the Platform that enables users to query their enterprise building and energy data directly. Almost all the data contained on the Platform, from interval data point readings, to monthly utility bills, through to event comments, can be accessed in DataViews. Users can build and save custom queries that update in real time as data is received in the Platform backend. Any data returned by these queries can be exported to an Excel or CSV file, or sent directly to a third-party application via an API call.
Navigate to DataViews through:
The DataViews window contains five main regions:
1. Universal Data Model
All queries begin in the Universal Data Model tray, where all the available data on the Platform for this portfolio is located. Data is stored in a variety of tables in the Platform database and is reflected in the top-level headers in this tray (‘Details’, ‘Tags’, ‘Sensors’ etc.). Elements selected from this list form the basis of the query.
2. Filter Settings
Users can apply filters to each element of their query using the Filter Settings tray. There are several types of filtering options depending on the elements selected from the Universal Data Model.
3. Add New
The DataViews feature allows users to create and save as many queries as they like. Queries are stored in tabs along the top of the DataViews window. Users can create a new blank query by clicking 'Add New'.
4. Results Pane
This section will return data based on users selections from the Universal Data Model and Filter Settings.
5. Report Options
Here users can:
- Save a Query with
- Execute their query to return data with
- Export the results to Excel/CSV files with
- Generate an API call for their query with
Queries are created in the Universal Data Model interface built into DataViews.
Navigating the Universal Data Model
Figure 74. DataViews Table.
The Universal Data Model tray is a collapsible section allowing users to select different aspects of their portfolio data to include in a report. There are several tables to select data from, as shown above.
Each table is shown as a dropdown menu that contains different sub-tables and data fields that users can bring into their reports.
Selecting Data from the Universal Data Model
The information contained by each top-level table can be accessed by expanding the drop-down.
- Selecting any of the circle buttons to the left of the data field name will include that data field as a column in the user’s report.
- Selecting the circle next to an expandable item will auto-select each of the data fields contained by that table or sub-table.
- Selecting the 'Filter By' button to the right of a data field will include filter options for this data field in the Filter Settings. This is detailed further in the ‘Filter Settings Tray’ section below.
- Once all the desired data fields are selected, click the button in the top right portion of the screen to populate the results section.
Please note that selected data fields are highlighted in blue.
Adding Filters to Queries
Filtering can be conducted at any time on a query, and is managed through the Filter Settings tray.
Populating the Filter Settings Tray
The Filter Settings tray becomes populated with options based on two factors:
- As users select more data fields for inclusion in their report, the Filter Settings tray will populate with additional default fields to filter by.
- Selecting the 'Filter By' text next to data fields will allow users to then filter specifically by that data field.
Special Filtering Options
Some filters have additional options that are covered below:
When the filter field has a ‘List’ option, the grey rectangular search bar for that filter will provide a dropdown list with all the possible options for that filter.
Users can select multiple filter options here by simply selecting items from the drop-down list. In the example pictured above, the item 'California' has been selected for the site 'Name' filter.
When the filter has a ‘Wildcard Characters’ option, users can click the grey rectangular search bar and search for specific text strings within the filtered field. Standard SQL Server wildcards are supported here.
If, in this example, the user wanted to filter all the sites in California, they could either select ‘California’ from the list like in the image above, or they could select the wildcard character ‘%’, with zero or more characters, and search for “%cali%”, like the example below.
Null values are an additional filter that generally applies to the tags tables.
In the example below, the user filters the site data based on the 'Controls Contractor' tag. Users can filter tags using standard filter functionality, as well as with an additional option to filter by Null values.
When the 'Show Null [..] Tags' filter is set to ’TRUE’, all entries for the query show, including entries that do not have a 'Controls Contractor' tag assigned (i.e. null tags).
When the 'Show Null [..] Tags' filter is set to ’FALSE’, only entries for the query that have a 'Controls Contractor' tag assigned are shown.
The results of the query are displayed in the Results Pane. If a query has been edited after it has been run, click the button to pull the new data. If filters were added or updated after a query was run, use the 'Filter Data' button in the Filter Settings tray to update the results.
Figure 75. DataViews Results Pane.
As mentioned, users can export the data from their queries in two ways:
exports to Excel / CSV.
generates an API call to use a program of the user's choice.
Using the Switch API in Excel
In the Switch Platform:
Portfolio (left hand navigation bar) > Data Views > API Request (green button top right) > copy the URL