Power BI

Getting started

You can learn more about getting started with Power BI by looking at the Power BI dataflow documentation.

• Projects list, project components, and project funding tables are available

• Dataset documentation

Dataflow queries

Database views

Database views built into the Moped database can be connected to and navigated to through the Power BI dataflow. These are useful for views that are usable across workgroups and should be documented in the dataset documentation. It is also part of the release cycle to consider these views when modifying their definition.

Custom queries

It is also possible to define custom queries on the Power BI side by providing a SQL query instead of navigating to a controlled view. This is useful for one-off queries that are designed for a specific dashboard or workgroup. See an example of the config for a query utilized by a AAA Network dashboard.

Example

The AAA Network AGOL story map embeds this Power BI visualization that track the network progress over time. It uses the following custom query that lives on the Power BI side.

WITH
	distinct_dates AS (
		SELECT DISTINCT
			project_development_status_date
		FROM
			component_arcgis_online_view
		WHERE
			component_tags ILIKE '%AAA Network%'
			AND project_development_status IS NOT NULL
	),
	distinct_statuses AS (
		SELECT DISTINCT
			project_development_status
		FROM
			component_arcgis_online_view
		WHERE
			component_tags ILIKE '%AAA Network%'
			AND project_development_status IS NOT NULL
	),
	daily_projects AS (
		SELECT
			project_development_status_date,
			project_development_status,
			sum(length_miles_total) AS length_miles_total
		FROM
			component_arcgis_online_view
		WHERE
			component_tags ILIKE '%AAA Network%'
			AND project_development_status IS NOT NULL
		GROUP BY
			project_development_status_date,
			project_development_status
	),
	all_date_status_pairs AS (
		SELECT
			d.project_development_status_date,
			s.project_development_status
		FROM
			distinct_dates d
			CROSS JOIN distinct_statuses s
	)
SELECT
	ads.project_development_status_date,
	ads.project_development_status,
	coalesce(dp.length_miles_total, 0) AS length_miles_total,
	sum(coalesce(dp.length_miles_total, 0)) OVER (
		PARTITION BY
			ads.project_development_status
		ORDER BY
			ads.project_development_status_date ROWS BETWEEN unbounded preceding
			AND current ROW
	) AS running_total
FROM
	all_date_status_pairs ads
	LEFT JOIN daily_projects dp ON ads.project_development_status_date = dp.project_development_status_date
	AND ads.project_development_status = dp.project_development_status
ORDER BY
	ads.project_development_status,
	ads.project_development_status_date;