IA National Data Service – October 2020 Data Update

Economy & Employment

Claimant Count (September 2020) updated on the 14th of September. All affected reports were updated on the 16th of September.

Annual Population Survey indicators (2019) were updated on the 16th of October. A selection of measures included in this release are listed below:

      • Qualifications (GCSE and NVQ)
      • Economically inactive
      • Occupation (SOC2010)
      • Economic activity rate
      • Unemployment rate
      • Ethnic group by economic activity
      • Hours worked weekly
      • Economic activity of those with health conditions or illnesses lasting more than 12 month

Business Counts (2020) were updated on the 8th of October.

Health

Several indicators from the Public Health Outcomes Framework (PHOF) dataset have been updated on the 14th of October.

Please, find below an extract of the indicators included in this update:

    • Violent crime – violence offences per 1,000 population
    • Under 18s conception rate / 1,000
    • Under 75 mortality rate from liver disease/respiratory disease/cancer/cardiovascular disease/
    • Infant mortality rate
    • Hip fractures in people aged 65 and over
    • TB incidence
    • Excessive winter deaths index

Wellbeing indicators (2020) have been updated on the 30th of September and affected reports published in the first week of October.

Housing

Household Projections (2018) were updated on the 1st of October. Council Tax Bands and affected reports have been updated on the 15th of October.

IA National Data Service – September 2020 Data Update

Crime

Crime data (August) updated on the 28th of September.

Population

Nationality & Country of Birth data updated on the 7th of September while latest Population Estimates were updated on the 14th of September.

Economy & Employment

Claimant Count (August) updated on the 15th of September. All affected reports were updated on the 19th of September.

Health

Several indicators from the Public Health Outcomes Framework (PHOF) dataset have been updated on the 24th of September.

Please, find below an extract of the indicators included in this update:

    • Adjusted antibiotic prescription in primary care
    • Smoking in early pregnancy
    • Population vaccination coverage – Flu/Hepatitis/HPV
    • Suicide Rate
    • Low birth weight of term babies
    • Average portions of fruit and vegetables
    • Cancer screening coverage
    • Killed and seriously injured (KSI) casualties on England’s roads
    • Statutory homelessness

 

 

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 – August 2020 Data Update

Crime

Crime data (June) updated on the 30th of July. All affected reports updated by 7th of August.

Children & Education

NEET (2020) and Absence (2018-2019) data were last updated on the 12th of August and all affected reports on the 13th of the same month.

Deprivation

Fuel Poverty statistics (2018) were uploaded on the 18th of August.

Economy & Employment

Claimant Count (July) updated on the 11th of August. All affected reports were updated on the 13th.

Environment

Electricity and Gas Consumption and Carbon Emissions statistics (2018) were uploaded on the 19th and on the 24th of August, respectively. No reports affected.

Health

Several indicators from the Public Health Outcomes Framework (PHOF) dataset have been updated on the 20th of August.

Please, find below an extract of the indicators included in this update:

    • Low Birth Weight of Term Babies
    • Inequality in life expectancy at birth
    • Population vaccination coverage
    • Heart failure indicators
    • GP registered population
    • Several Cancer related indicators , including screenings and new cases
    • Breastfeeding, including initiation, first feed and prevalence
    • Fertility rates and Conception Rates for U18s and U16s

Housing

Stock of properties data (2019) and vacant dwellings data (2017-2019) were uploaded on the 4th of August. All affected reports were updated on the 13th of August.

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 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.

IA National Data Service – April 2020 Data Update

Claimant Count

Claimant Counts for March 2020 with Feb 2020 revision: Released 21 April. On system 21 April. Reports updated.

Annual Population Survey

Annual Population Survey for Jan-19 to Dec-19: Released 21 April. On system 22 April. Reports updated.

Crime

February data released at start of month, March 2020 data released at end of month. March data available in system from 1 May. Reports updated.

Health

Fingertips data has been refreshed across a number of themes. We are aiming to include the Local Health dataset in the next month.

Apprenticeships

Apprenticeship data: Updated with 2018-19 data on 15 April.

Population Estimates

We added the number and percentage of over 70s on 30 March as this is a key COVID response age grouping.

Public Health Outcomes Framework:

The PHOF dataset was updated on 24 Feb with the February data. A change to the processing has led to a number of indicators being reorded. A number of indicators have now been deprecated. These are available in the system but will no longer be updated. We have adjusted the indicator names to include the term DEPRECATED. If you have used these indicators in your reports we advise amending your reports to use the new indicators that will continue to be updated.

Wards

Following discussion with affected customers we are in the process of updating Wards (and other geographies) to the latest boundaries. As there can be a significant delay between new boundaries being released and data being made available for them we will be calculating where possible estimated values for the new areas based on the best fit of smaller geographies. The metadata for affected data will be amended to reflect that the source may be a mixture of published and derived values.

InstantAtlas Road Map Summary

Last updated: 30 March 2020

Spring 2020 Release

The spring release of InstantAtlas Data Explorer brings new charting and mapping functionality, enabling you to visualize your indicator data in time series charts, distribution charts and thematic maps. The Data Catalog Hub gains new validation functions to help you keep your data catalog in-sync with the National Data Service. In addition, both products now support deployment to ArcGIS Enterprise, enabling you to use them on-premise.

Summer 2020 Release

The summer release of InstantAtlas Report Builder is a major update, moving to a new, modern architecture that brings new charting options, performance improvements, security enhancements and the ability to deploy to ArcGIS Enterprise. Search performance and matching and data download in Data Explorer should also receive an upgrade.

Autumn 2020 Release

The final set of changes and upgrades is yet to be decided for autumn 2020 – if you have feedback or ideas for any of the InstantAtlas products, tell us now and they could be part of the next release.

National Data Service, 2020

The InstantAtlas National Data Service is updated every month with data from a variety of sources. In addition, 2020 has seen the replacement of all Health indicators with a newer up-to-date set from the PHE Fingertips API and the latest Scottish Index of Multiple Deprivation data. Planned updates include local health data from Fingertips as well as ongoing process and validation improvements, with new data coming in autumn 2020 in response to customer feedback and requests.