InstantAtlas integration with Power BI

This article describes how to use data from the InstantAtlas National Data Service in Microsoft Power BI. The National Data Service is delivered as ArcGIS Online feature layers.

The steps for using data in ArcGIS in Power BI are described here. It is possible to use the steps in the Appendix to bring data from the National Data Service into Power BI.

However, when using data from the National Data Service specifically, a better result can be achieved using a custom query in the Power BI Advanced Editor. The steps for doing this are described below.

Sign in to the Data Catalog Hub and use the Data Explorer app to search for the indicator you are interested in. Click on any of the tabs and click the button to view the item in ArcGIS Online.

IA data explorer link to ArcGIS button

In ArcGIS Online, on the item overview page, click the layer corresponding to the geography you want to display data for in Power BI. Then click the Copy button on the right to copy the URL.

ArcGIS Online copy feature layer URL button

Amend this URL (e.g. in Notepad) to add this to the end:


query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json

The URL should now look something like this:


https://services1.arcgis.com/HumUw0sDQHwJuboT/arcgis/rest/services/Suffolk_Pop_Est_Female/FeatureServer/0/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json

Open Power BI Desktop and click Get Data, Web. Paste your URL into the URL box and click OK.

Power BI URL box

You will get something like this:

Power BI data import

Click Transform Data. The Power Query Editor will open. Click Advanced Editor.

Power BI Advanced Editor

Replace the query in the Advanced Editor with the one below. Make sure you adjust the URL at the start of the query (shown in red) to the one you are actually using.


let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://services1.arcgis.com/HumUw0sDQHwJuboT/arcgis/rest/services/Suffolk_Pop_Est_Female/FeatureServer/0/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json"), null, null, 65001)}),
    #"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
    Column1 = #"Parsed JSON"{0}[Column1],
    fieldSet = Column1[fields],
    fieldNames = List.Transform(fieldSet, each Record.Field(_, "name")),
    fieldAliases = List.Transform(fieldSet, each Text.Combine({Text.Replace(Record.Field(_, "alias"), "|", " | "), " (", Record.Field(_, "name"),")"})),
    features = Column1[features],
    #"Converted to Table" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}, {"Column1.attributes"}),
    #"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", fieldNames, fieldAliases)
in
    #"Expanded Column1.attributes"

Power BI Advanced Editor new query

Click Done.

National Data Service data in Power BI

Note that the data type for the columns is text. You should change this for the data you wish to use. Select the columns in question, right-click, Change Type and choose the appropriate type.

Change data type in Power BI

Then click Close & Apply in the ribbon.

You can then create your visualisation with the data from the feature layer.

Power BI visualisation with National Data Service data

IA National Data Service – July 2020 Data Update

Crime

Crime data (May) updated 1 July. All affected reports updated by 6 July.

Children & Education

The following education data was updated in July and relative reports updated by the 29th of July:

      • 2018/19 Key stage 2
      • 2018/19 Key Stage 4
      • 2018/19 Key Stage 5
      • 2018/19 Special Educational Needs (SEN)
      • 2018/19 Free School Meals (FSM)
      • 2018/19 Pupils Characteristics

Economy & Employment

Claimant Count (June) updated during July.

April 2019 to March 2020 Annual Population Survey (APS) updated during July. Reports updated by the 22nd of July.

A detailed breakdown of statistics included within the APS is listed below:

      • Disability (Equality Act) core level by economic activity
      • Economic activity of those with health conditions or illnesses lasting more than 12 months
      • Economic activity rate
      • Economically inactive
      • Employment rate
      • Ethnic group by economic activity
      • Full-time and part-time employment
      • Hours worked weekly
      • Industry of employment (SIC 2007)
      • Nationality by white/ethnic minority
      • Occupation (SOC2010) major group of employment
      • Public and private sector employment
      • Qualifications (GCSE/NVQ)
      • Self-employed, employees and non-permanent employment
      • Unemployment rate
      • White/ethnic minority by age
      • Estimates of unemployment (model-based)

Deprivation

HMRC and DWP’s previous statistics relative to children in low income families have been replaced and deprecated to provide a more coherent picture of this category for both Relative and Absolute Low Income.

Latest data for Children in Low Income Families Data has been updated on the 21st of July, from financial year 2014/15 to 2018/19.

 

 

 

 

 

IA Data Catalog – 31 July 2020 Release Notes

The latest version of InstantAtlas Data Catalog (version 2.1.3) is now available at https://hub.instantatlas.com/.

Enhancements:

  • ArcGIS JavaScript API migrated to version 4.x
  • Web Map Builder uses smartMapping module(s) from API throughout
  • Web Map Builder now has a new option to allow proportional symbols for count data (user option). This option is available from the ellipsis menu in the top right corner
  • Inspector now shows warning messages when Item_Order is missing for an Indicator in the master table
  • Web Map Builder now handles categoric data gracefully
  • The Data Catalog Manager pre-checks for new core layers in hosted tables to ensure that updates stay in sync
  • The Data Catalog Manager disables buttons in the UI that trigger an update whilst another update is still processing
  • Minor UI cleanup around dialogs and buttons

Bug fixes:

  • Web Map Builder will now exclude data where all values are null from the map – previously this was causing an error dialog to appear
  • Web Map Builder how handles Feature Collections properly when opening an existing web map – previously this was generating a JSON error
  • Metadata page now falls back to using the standard metadata table if there is not one defined within the targeted master table

IA Data Explorer – 10 July 2020 Release Notes

The latest version of InstantAtlas Data Explorer (version 2.0.2) is now available at https://hub.instantatlas.com/.

Version 2.0.2 includes two areas of change.

View ArcGIS Online Item button

If you are signed in to ArcGIS Online (i.e. you sign in via the Data Catalog app or the Data Explorer app is launched with a ?token=xxxxxx argument) you will see a View ArcGIS Online Item button displayed under the Data, Map, Time Series or Ranked Bar Chart tabs.

IA data explorer link to item button

When clicked, the button takes you to the item in ArcGIS Online that contains the data for indicator you are viewing in the data explorer.

This button has been added in order to make it easy to obtain the endpoint of the ArcGIS feature layer containing an indicator from an InstantAtlas data catalog. There are a number of scenarios in which it might be useful to have the endpoint, one being that it enables the use of the data held in the data catalog in other third party systems, such as Microsoft Power BI for example.

Update to sharing/token behaviour

The Data Explorer has been updated so that when it is run in authorised mode (i.e. not shared with everyone) it will pass the token down to all of the feature services it uses. Furthermore, a public-shared Data Explorer will detect whether it needs a sign in for specific feature layers. If the data for the indicator searched for is stored in a feature layer that is protected, the user will be asked to sign in to ArcGIS Online in order to view the indicator in question.

How to embed InstantAtlas apps into your website

The purpose of this post is to provide the necessary information for embedding InstantAtlas apps into a website. Colour is used to explain the code below – if you have difficulty distinguishing the colours used please contact support@instantatlas.com and we will find an alternative. The actual creation of the apps in the first place is not covered in this post.

Reports

InstantAtlas Report Builder is a tool for for designing and publishing any type of report on ArcGIS Online from simple snapshots to complex, multi-page area profiles. The reports created using Report Builder can be embedded using two methods: static or dynamic. In the first case, the report must first be published as a bundle of static HTML files – this can be done within Report Builder by the author of the report. We recommend using the static embedding method to ensure the report pages load as quickly as possible.

Static method

Add this code to a web page to embed a report that has been published as static HTML files:


<div>
        <div id="iaoReportContainer" class="iao-content report-container" data-config='{"reports": [{"id": "5db1ef44afe64e499b390b57c0f012ae", "name": "Ward"}], "default_geography":"5db1ef44afe64e499b390b57c0f012ae", "urlMode": "query", "controls": { "type": "drop-down", "container": ".iao-report-controls", "reportLabel": "Select a report", "featureLabel": "Select an area"}}'>
            <script type="text/javascript" defer src="https://reports.instantatlas.com/scripts/lib/IAO.embed.min.js?container=iaoReportContainer&auto=true"></script>
        </div>
    </div>

You can change the following elements:

  • 5db1ef44afe64e499b390b57c0f012ae – this is the app ID (from ArcGIS Online) of the report you wish to embed.
  • Ward – this is the label for the report that will be displayed in the “Select a Report” drop down menu.
  • Select a report and Select an area – these are the labels for the drop down menus that will be shown above the report.

You can embed multiple reports in the following way:


<div>
        <div id="iaoReportContainer" class="iao-content report-container" data-config='{"reports": [{"id": "5db1ef44afe64e499b390b57c0f012ae", "name": "Ward"},{"id": "0647c83c53f24789b8d637c0ad6578c0", "name": "LSOA"}], "default_geography":"5db1ef44afe64e499b390b57c0f012ae", "urlMode": "query", "controls": { "type": "drop-down", "container": ".iao-report-controls", "reportLabel": "Select a report", "featureLabel": "Select an area"}}'>
            <script type="text/javascript" defer src="https://reports.instantatlas.com/scripts/lib/IAO.embed.min.js?container=iaoReportContainer&auto=true"></script>
        </div>
    </div>

The app ID and name of each additional report should simply be added as shown above in red. The report that shows by default is defined by the app ID shown above in green.

Note: for the report embedding to work, jQuery must be loaded before the report. A defer setting has been used in the embed code to make sure the report loads last.

An example of embedding reports using this method can be seen in this page of the Suffolk Observatory.

Dynamic method

Add this code to a web page to embed a report using the dynamic method.


<div id="iaoReportContainer" class="iao-content" style="width: 1000px;height: 2751px;"><script type="text/javascript" src="https://reports.instantatlas.com/embed/5db1ef44afe64e499b390b57c0f012ae/E05007163?container=iaoReportContainer"></script></div>

Simply change app ID (shown above in red) to embed a different report and optionally the feature code (shown above in blue) to load the report for a different feature.

Data Explorer

InstantAtlas Data explorer connects directly to an InstantAtlas Data Catalog in ArcGIS Online and allow your web site users to browse and download data and create graphics for documents and presentations.

Add this code to a web page to embed a data explorer app.


<link href="https://hub.instantatlas.com/data-catalog-explorer/static/css/main.css" rel="stylesheet">
<div id="root"></div>
<script src="https://hub.instantatlas.com/data-catalog-explorer/static/js/main.js"></script>
<script>window.dataCatalogExplorer.launch({ "appid": "e3d83edc76bd45548ddd0e2ee4fe4bdf", "container": "root" })</script>‎

e3d83edc76bd45548ddd0e2ee4fe4bdf – this is the app ID (from ArcGIS Online) of the data explorer app you wish to embed.

An example of embedding a data explorer app can be seen on this page of the Suffolk Observatory.

Map Explorer

The Map Explorer app is a simple-to-use app that that allows a user to select from a pre-defined set of indicators from an InstantAtlas Data Catalog and visualize them as thematic maps.


<div id="ia-map-panel" style="width: 100%; min-height: 500px; height: calc(100vh - 300px);">
<script src="https://hub.instantatlas.com/map-explorer/embed?c=ia-map-panel&appid=917b1891e5934d6baeeff23cdb0e9f38"></script>
</div>

917b1891e5934d6baeeff23cdb0e9f38 – this is the app ID (from ArcGIS Online) of the map explorer app you wish to embed.

Custom Area Reporter

The InstantAtlas Custom Area Reporter allows users to define their own areas and generate reports for them. Users select areas from a map using a range of selection tools. Once an area is defined users can produce one or more reports for the area. The report presents aggregated figures for the chosen area based on the underlying geography.

Add this code to a web page to embed a custom area reporter app.


<div>
        <div id="report-content-panel" style="width: 100%; height: 100%; border: 1px solid #ccc; position: relative;">
            <script type="text/javascript" defer src="https://reports.instantatlas.com/embed-selector?appid=1a451d1bbe3544849585ab2745c25f5d&container=report-content-panel&flush=true"></script>
        </div>‎
    </div>
    <script src="https://js.arcgis.com/3.24/"></script>‎

1a451d1bbe3544849585ab2745c25f5d – this is the app ID (from ArcGIS Online) of the custom area reporter app you wish to embed.

An example of embedding a custom area reporter app can be seen on this page of the Suffolk Observatory.

Support

If you have any difficulties embedding InstantAtlas apps in a website please contact support@instantatlas.com.

IA National Data Service – June 2020 Data Update

Crime

Crime data updated 3 June. All affected reports updated by 4 June.

Claimant count

Claimant count data updated on day of publication 16 June. All affected reports updated by 18 June.

Population estimates

The finalised population estimates were released and checked. Only metadata changes were required – see update for May 2020.

Net Housing Additions

The Net Housing Additions data for 2018-19 was added on 15 June.

Notes

With the COVID response demands reducing and a new member of staff joining the Data team we will be aiming to catch up on non-API data sources over the next couple of months.

We are continuing to add processing improvements into the system. These will be rolling out behind the scenes aimed at improving the speed of data being available in the system and the outputs.

IA National Data Service – May 2020 Data Update

Crime

March data available in system from 1 May and all affected reports updated.

Claimant count

Claimant count data updated on day of publication 19 May. Reports followed with all affected reports updated by 25 May.

Population estimates

Provisional Population Estimates for large areas for 2019 were released on 6 May. Generally we avoid provisional data to avoid changing numbers. This was an unusual step on behalf of ONS as part of measures to support the COVID-19 response. We made the data available on the 13 May and updated the affected reports by 15 May. The metadata denoted the provisional nature of the figures. ONS released the final version of the figures on 24 June. The only change from the provisional data was additional data for Northern Ireland so the English National Data Service data was unaffected beyond an update to the metadata.

Using InstantAtlas Report Builder to create a report based on the Data Catalog US example data

Overview

This resource is for someone that has read the Getting started with the InstantAtlas Data Catalog using US example data article and would now like to use Report Builder to create a report based on that data.

Creating the report

The first step is to sign in to Report Builder. From the Data Catalog Hub homepage click Report Builder (you must have taken a trial of this app from the ArcGIS Marketplace).

Once in Report Builder, select the Web Maps checkbox in the middle of the page. Then click the Web Maps (InstantAtlas Samples) folder below.

You should see a web map in this folder called Data Catalog US Demo Web Map. That starting point for every InstantAtlas report is a web map. Hover the web map with your mouse pointer and click the Page icon (the middle icon of the three) to create a report based on that web map.

Select the first layout option.

Select the layer you wish to create a report for – County, State or US.

Then select the fields from the layer that contain the IDs and the names of the features in the layer. These are:

County – CtyCode, NAME
State – StateCode, NAME
US – USCode, NAME

In the example shown below the State layer has been chosen.

The report will be created and you will be presented with a blank page.

Attaching the data catalog as a data source

You will now see how to attach a data catalog as a data source to a report in Report Builder.

Click the Report Settings and Actions button in the top left-hand corner of the screen. Then click Data Sources.

Click the Catalog button and then click Choose Table.

Click on the catalog you created with the US demo data to select it. Then click Choose and Next.

Click the Finish button.

You have now connected the data catalog to the report as a data source.

Displaying the data catalog data in the report

You can now click the Widgets button in the top left-hand corner of the page to start dragging widgets onto the report page. Once you have dragged a widget onto the page, you can edit the widget properties, click the Data tab and select indicators from the data catalog.

For information on creating a report please refer to the Report Builder help pages.

IA Data Explorer – 17 June 2020 Release Notes

The latest version of InstantAtlas Data Explorer (version 2.0.1) is now available at https://hub.instantatlas.com/.

Version 2.0.1 adds powerful new filtering, user interface improvements and bug fixes.

Enhancements

  • New functionality to filter features in the active geography using one or more fields in the feature layer. This allows you to filter to the features contained in a larger area (see example given below)
  • Modal windows now have Apply, Clear and Cancel buttons
  • Search now launches when user clicks a Go button or hits Enter
  • Adjustments to size of modal windows and mobile layout
  • Title and description removed from the list when viewing the Metadata tab (as these are already displayed at the top of the page)

Bug fixes

  • Search not accepting a space in the search term
  • Search launching too quickly when typing a term
  • Filter screen showing incorrect content when the data explorer was set to be filtered to a single root theme in the data catalog (Root Theme ID setting)
  • Modal window display in Internet Explorer 11

New filtering

Setup

A new configuration setting (Field Filters) has been added that allows the owner of the app to supply a comma-delimited list of filter fields that will be displayed in the data explorer.

Display

The list of field filters is displayed when the user clicks the Filter Settings button in the data explorer. If multiple filter fields have been defined, these will be shown in a drop down menu. If only a single filter field has been defined, this will be shown as text. If a field filter contains the same value for all of the features in the active geography, that field will automatically be hidden from the user.

IA Data Explorer field filters

The end user can select a field to filter the features displayed in the active tab. In the Map tab, the map will automatically zoom to the bounding box of the filtered features. The filter is retained when switching between tabs. In the Time Series tab, the Limit Features button/checkbox will automatically disappear if the number of filtered features is less than 10. The user has the option to perform further filtering to select specific features within the field filter by clicking the Features button.

For example, the Map tab is showing Lower Super Output Areas (LSOAs) for Suffolk County.

IA Data Explorer map showing LSOAs

The user can filter to the LSOAs in a particular district, for example Ipswich (District=Ipswich). The map automatically zooms to the filtered features.

IA Data Explorer filtered map

Switching to the Data tab retains the filter and the table (and the data download) will contain only the LSOAs in Ipswich.

IA Data Explorer filtered data tab

Modal windows

The modal windows in the data explorer have gained Apply, Clear and Cancel buttons to make them more user-friendly.

IA Data Explorer modal window