Skip to main content

Power BI ADK: Datasets

Reference for the data sets available through the Power BI ADK — query properties, column relationships, and API endpoints.

Overview

Once the Power BI Analytics Development Kit (ADK) is installed and connected, you can pull in any of the available data sets to build reports and dashboards. This article covers how to select a data set, how to pass query properties, and gives reference documentation for the data sets the ADK exposes.

Usage

Once you have completed the steps in Power BI ADK: Setup, go to Get Data > Online Services, select CloudCompli, and click Connect:

You'll then see a screen for selecting a data set from the Stormwater API:

Most data sets need no custom parameters, but a few do — Reports, for example, requires a report_definition_id taken from the id column of the Report Definitions data set. Query properties must be written in JavaScript Object Notation (JSON):

The reference below lists each documented data set along with its required and optional parameters.

Advanced usage: very large queries

If a very large query returns the error "Maximum query limit reached for dataset", you can raise the maximum query size beyond the default of 20 queries of 1,000 records. For Program Deficiencies on the construction program, for example:

{"program":"construction", "RecordsPerQuery":2000, "MaxQueries": 50}

⚠️ Only do this if you are genuinely exceeding the default query size, which is already fairly large. Raising it can consume significant resources on your computer, and the API may return an error if your client is flagged as overburdening it.

Available data sets

These are all 46 data sets in the connector's picker, named exactly as they appear there. Five carry a (has options…) hint, meaning they accept or require query properties:

  • BMPs

  • CASWRCB Industrial Facilities (has options, ex: {"facility_county":"Orange"})

  • CASWRCB Industrial Facilities - Counties

  • CASWRCB Industrial Facilities - Cities

  • CASWRCB Industrial Facilities - Region Boards

  • CASWRCB Industrial Facilities - SICs

  • CASWRCB Industrial Facilities - Receiving Waters

  • CASWRCB Wastewater Facility Permits (has options, ex: {"facility_county":"Orange"})

  • CASWRCB Wastewater Facility Permits - Counties

  • CASWRCB Wastewater Facility Permits - Cities

  • CASWRCB Wastewater Facility Permits - Types

  • CASWRCB Wastewater Facility Permits - Waste Types

  • CASWRCB Wastewater Facility Permits - Threat Codes

  • CASWRCB Wastewater Facility Permits - Complexity Codes

  • CASWRCB Wastewater Facility Permits - Fee Codes

  • CASWRCB Wastewater Facility Permits - Pretreatment Codes

  • CASWRCB Wastewater Facility Permits - Reclamation Codes

  • Constituents

  • Construction BMP Ineffectiveness Scores (Current)

  • Construction Deficiencies

  • Construction Locations

  • Deficiency Levels

  • Education Interactions

  • Enforcement Levels

  • Enforcements

  • Flow Meters

  • Flow Records

  • Industrial Deficiencies

  • Industrial Locations

  • Investigations

  • Location Tags

  • Monitoring Locations

  • Monitoring Records

  • Municipal Fixed Deficiencies

  • Municipal Fixed Assets

  • Municipal Field Activities Deficiencies

  • Municipal Field Activities

  • Post-Construction Deficiencies

  • Post-Construction Locations

  • Programs

  • Program Deficiencies (has options, ex: {"program":"PROGRAM_TYPE_KEY"})

  • Program Locations (has options, ex: {"program":"PROGRAM_TYPE_KEY"})

  • Report Definitions

  • Reports (has options, ex: {"report_definition_id":1})

  • Users

  • Weather Hydstra Observation Stations

💡 Depending on which features you subscribe to, some of these may not be relevant to you. Data sets without a detailed section below are available in the picker but are not individually documented yet — contact your SwiftComply representative if you need details on one of them.

⚠️ Location statuses are referenced by the location_status_id column on the location data sets, but they are not exposed as their own data set in the connector, so there is no table to join against for them.

Data set reference

BMPs

Get a table of all BMP types. These correspond to bmp_id in many other data sets.

API endpoint: /api/bmps

CASWRCB Industrial Facilities

Get a table of all active industrial facility records in the SMARTS database maintained by the California State Water Resources Control Board.

Most query properties such as facility_city and facility_county match their corresponding field (or any of sic1, sic2 and sic3 for sic). They also support a plural form — facility_cities, facility_counties, sics — where each value is separated by a comma.

latitude, longitude and distance must all three be specified together. Results are then returned within distance metres of the given latitude and longitude.

Column relationships:

  • facility_county — a value in the CASWRCB Industrial Facilities - Counties data set

  • facility_city — a value in the CASWRCB Industrial Facilities - Cities data set

  • region_board — a value in the CASWRCB Industrial Facilities - Region Boards data set

  • receiving_water_name — a value in the CASWRCB Industrial Facilities - Receiving Waters data set

  • sic1, sic2 and sic3 — each a value in the CASWRCB Industrial Facilities - SICs data set

Query properties:

  • facility_county (string) — optional

  • facility_counties (string, comma separated) — optional

  • facility_city (string) — optional

  • facility_cities (string, comma separated) — optional

  • wdid (string) — optional

  • wdids (string, comma separated) — optional

  • sic (string) — optional

  • sics (string, comma separated) — optional

  • latitude (string, degrees) — optional; requires longitude and distance

  • longitude (string, degrees) — optional; requires latitude and distance

  • distance (int, metres) — optional; requires latitude and longitude

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

Query properties examples:

{"receiving_water_name":"Aliso Creek"}
{"facility_county":"Orange", "facility_cities":"Irvine,Anaheim"}
{"latitude":"33.9487", "longitude":"-117.7158", "distance": 1000}

API endpoint: /api/data/caswrcb/industrial-facilities

Constituents

Get a table of all constituent types. These correspond to constituent_id in many other data sets.

API endpoint: /api/constituents

Construction Deficiencies

Get a table of all deficiencies with construction locations.

Program: Construction

Column relationships:

  • location_id — the id column of the Construction Locations data set

  • deficiency_level_id — the id column of the Deficiency Levels data set, if the deficiency was categorized with a deficiency level

  • resolved_by — the id column of the Users data set, if the deficiency was resolved by a user

  • bmp_id — the id column of the BMPs data set, if the deficiency was linked to a BMP

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/construction/deficiencies

Construction Locations

Get a table of all construction locations.

Program: Construction

Column relationships:

  • location_status_id — the location's status (see the note above: location statuses are not exposed as their own data set)

  • tag_ids (List) — the id column of the Location Tags data set, if the location has been assigned any tags

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/construction

Deficiency Levels

Get a table of all deficiency levels. These correspond to deficiency_level_id in many other data sets.

API endpoint: /api/deficiency-levels

Enforcement Levels

Get a table of all enforcement levels. These correspond to enforcement_level_id in many other data sets.

API endpoint: /api/enforcement-levels

Industrial Deficiencies

Get a table of all deficiencies with industrial locations.

Program: Industrial

Column relationships:

  • location_id — the id column of the Industrial Locations data set

  • deficiency_level_id — the id column of the Deficiency Levels data set, if the deficiency was categorized with a deficiency level

  • resolved_by — the id column of the Users data set, if the deficiency was resolved by a user

  • bmp_id — the id column of the BMPs data set, if the deficiency was linked to a BMP

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/industrial/deficiencies

Industrial Locations

Get a table of all industrial locations.

Program: Industrial

Column relationships:

  • location_status_id — the location's status (see the note above: location statuses are not exposed as their own data set)

  • tag_ids (List) — the id column of the Location Tags data set, if the location has been assigned any tags

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/industrial

Location Tags

Get a table of all location tags. These correspond to location_tag_id or tag_id, or to a list in location_tag_ids or tag_ids, in many other data sets.

API endpoint: /api/locations-tags

Municipal Field Activities Deficiencies

Get a table of all deficiencies with municipal field activities.

Program: Municipal Field Activities

Column relationships:

  • location_id — the id column of the Municipal Field Activities data set

  • deficiency_level_id — the id column of the Deficiency Levels data set, if the deficiency was categorized with a deficiency level

  • resolved_by — the id column of the Users data set, if the deficiency was resolved by a user

  • bmp_id — the id column of the BMPs data set, if the deficiency was linked to a BMP

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/municipal-field-activities/deficiencies

Municipal Field Activities

Get a table of all municipal field activities.

Program: Municipal Field Activities

Column relationships:

  • location_status_id — the location's status (see the note above: location statuses are not exposed as their own data set)

  • tag_ids (List) — the id column of the Location Tags data set, if the location has been assigned any tags

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/municipal-field-activities

Municipal Fixed Deficiencies

Get a table of all deficiencies with municipal fixed assets. This is the data set labeled Municipal Fixed Deficiencies in the picker.

Program: Municipal Fixed Assets

Column relationships:

  • location_id — the id column of the Municipal Fixed Assets data set

  • deficiency_level_id — the id column of the Deficiency Levels data set, if the deficiency was categorized with a deficiency level

  • resolved_by — the id column of the Users data set, if the deficiency was resolved by a user

  • bmp_id — the id column of the BMPs data set, if the deficiency was linked to a BMP

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/municipal-fixed-assets/deficiencies

Municipal Fixed Assets

Get a table of all municipal fixed assets.

Program: Municipal Fixed Assets

Column relationships:

  • location_status_id — the location's status (see the note above: location statuses are not exposed as their own data set)

  • tag_ids (List) — the id column of the Location Tags data set, if the location has been assigned any tags

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/municipal-fixed-assets

Post-Construction Deficiencies

Get a table of all deficiencies with post-construction locations.

Program: Post-Construction

Column relationships:

  • location_id — the id column of the Post-Construction Locations data set

  • deficiency_level_id — the id column of the Deficiency Levels data set, if the deficiency was categorized with a deficiency level

  • resolved_by — the id column of the Users data set, if the deficiency was resolved by a user

  • bmp_id — the id column of the BMPs data set, if the deficiency was linked to a BMP

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/postconstruction/deficiencies

Post-Construction Locations

Get a table of all post-construction locations.

Program: Post-Construction

Column relationships:

  • location_status_id — the location's status (see the note above: location statuses are not exposed as their own data set)

  • tag_ids (List) — the id column of the Location Tags data set, if the location has been assigned any tags

Query properties:

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

API endpoint: /api/postconstruction

Programs

Get a table of the programs available to you. Use the type column when specifying a program in the query properties of another data set, such as Program Deficiencies.

Query properties:

None

API endpoint: /api/programs

Program Deficiencies

Get a table of all deficiencies for the specified program type (such as construction or municipal-fixed-assets). See the Programs data set for the list of available programs.

In most cases it is better to use a program-specific deficiencies data set such as Construction Deficiencies or Industrial Deficiencies.

Column relationships:

  • location_id — the id column of the locations data set for the program you requested (for example Construction Locations when program is construction)

  • deficiency_level_id — the id column of the Deficiency Levels data set, if the deficiency was categorized with a deficiency level

  • resolved_by — the id column of the Users data set, if the deficiency was resolved by a user

  • bmp_id — the id column of the BMPs data set, if the deficiency was linked to a BMP

Query properties:

  • program (string) — required; the type value from the Programs data set

  • MaxQueries (int) — optional

  • RecordsPerQuery (int) — optional

  • ErrorOnAdditionalRecords (boolean) — optional

Required query properties example:

{"program":"construction"}

API endpoint: /api/[program]/deficiencies

Report Definitions

Get a table of all report definitions. These correspond to report_definition_id in other data sets, and are what you supply as report_definition_id in the required query property of the Reports data set.

API endpoint: /api/report-definitions

Users

Get a table of all users. These correspond to user_id — and to other columns such as created_by as documented per data set — in many other data sets.

API endpoint: /api/users

Related articles

Related Articles
Did this answer your question?