CUSTOMER SUPPORT QUESTIONS

The name Facts and Dimensions comes from two of the fundamental concepts used in data warehousing.

Facts are the measurable values within a dataset, such as the number of patients attending hospital, the number of prescriptions issued or the amount of money spent. They represent the information being measured.

Dimensions provide the context for these values. They describe attributes such as location, organisation or treatment type, allowing the facts to be filtered, grouped and analysed.

Together, facts and dimensions provide a structured way of organising data, making it easier to integrate, analyse and report across multiple data sources.

Maintaining large volumes of public datasets is often time-consuming and resource intensive. Publishers release data in different formats, structures and schedules, often with changes that require ongoing maintenance.

Beyond standardisation and keeping data up to date, FAD uses a rigorous ingestion and validation framework developed over many years and across a wide range of datasets and formats, including APIs and PDF sources. We apply multiple automated checkpoints and validation rules throughout the pipeline to ensure consistency, accuracy and reliability of the data delivered.

FAD removes much of the operational overhead by providing a managed service that standardises, validates and maintains these datasets on your behalf.

Key benefits include:

• Current and consistent data: standardised datasets updated as new releases become available.
• Robust ingestion and validation: automated validation processes and quality checks help ensure the accuracy and integrity of the data provided.
• Reduced maintenance, add efficiency: your team can focus on analysis, reporting and operational delivery rather than collecting, transforming and maintaining source data.
• Responsive development: new publicly available data sources can be requested and ingested as customer requirements evolve.

• A managed cloud platform containing publicly available data from thousands of sources.
• Continuously maintained and updated as publishers release new data.
• A single, consistent source of reference and analytical data for downstream systems.
• Designed for integration with existing SQL/Snowflake environments.
• Able to incorporate new datasets based on customer requirements.

• An 'out the box' analytics solution.
• A semantic layer or ontology; FAD provides structured data objects that can be incorporated into your own analytical processes.
• A replacement for subject matter expertise (users must interpret datasets appropriately and ensure correct use).

All data loaded into FAD remains subject to the original source license terms. It is your responsibility to ensure that the license permits your intended use.

To view license details for a specific dataset, please run the following:

SELECT
Licence,
Licence_Notes,
Licence_Even_More_Care_Reqd
FROM Release_Details.All_Available_Tables
WHERE
--Amend criteria to your object's schema and name
Table_Schema = 'Pop_Household_Est'
AND
TABLE_NAME = 'Census_Data_By_LAs_Wales'

FAD can be accessed via SQL Server (Azure) and Snowflake, depending on your organisation's setup. For guidance on connecting FAD within the Federated Data Platform (FDP), please see the section on 'Using FAD Within The FDP'.

Required credentials:

• FAD server name
• Username
• Password
• Database name

If you're unsure of these details, please contact us.

IP address not authorised If you receive an error referencing your IP address, your organisation's IP range may not be whitelisted. Please inform us using the dedicated IP request email address provided to you.

Named Pipes / TCP errors If you receive errors related to Named Pipes or TCP connectivity, your firewall may be blocking access to Azure. Ensure outbound access is allowed to the UK South Azure data centre. For further guidance, see Connectivity from outside of Azure

Connection Options You can connect to FAD using one of the following methods, depending on your requirements and technical setup.

You can connect directly to your FAD database using tools such as:

• SQL Server Management Studio (SSMS)
• Excel
• Power BI
• Tableau etc

For step-by-step guidance, please refer to Tutorial playlist on our channel FAD Tutorials for your chosen software.

If you are running your own Azure SQL environment, you can create External Tables in your database, that reference tables on the FAD server. For detailed setup instructions, see:

• Lesson 12: Deploy A Copy of our DB to Your Azure Server
• Lesson 13: Create External Tables In Your Own Azure SQL Database

If you use SQL Server, you can configure a Linked Server to connect your local database to FAD.

For setup instructions, see:

• Lesson 1b: Connect via Linked Server

Performance tip: using OPENQUERY() is recommended, as it executes queries on the remote server and reduces data transfer overhead. For more details, see:

• Lesson 4: Faster Queries Over Linked Server

The code to run to create linked server is within the above YouTube video and also here for ease of reference.

DECLARE @Username nvarchar(255) = 'USERNAME' --enter your username you were given
DECLARE @Password nvarchar(255) = 'PASSWORD' --enter your password you were given
DECLARE @FADServerName nvarchar(255) = 'FADAzureServerNameHere' --enter the FAD azure server name you were given
DECLARE @UserDatabaseName nvarchar(255)
DECLARE @UsernameWithDomain nvarchar(255)
 
SET @UserDatabaseName = @Username+'_UserDB'
SET @UsernameWithDomain = @Username + '@' + @FADServerName
 
EXEC master.dbo.sp_addlinkedserver @server = N'FD_UserDB', @srvproduct=N'', @provider=N'sqlncli', @datasrc=@FADServerName, @catalog=@UserDatabaseName
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FD_UserDB',@useself=N'False',@locallogin=NULL,@rmtuser=@UsernameWithDomain,@rmtpassword=@Password
EXEC master.dbo.sp_serveroption @server=N'FD_UserDB', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'FD_UserDB', @optname=N'collation compatible', @optvalue=N'true'

FD DataSync is an SSIS-based tool that synchronises FAD data into your local database.

What it does:

• Copies selected tables from the FAD Azure server
• For fact tables, uses the latest snapshot view
• Creates a staging table (_Staging) in your database
• Validates row counts before replacing existing tables
• Renames previous versions with _Old suffix

For setup guidance, see:

• Lesson 7: FD Data Sync for Syncing FAD Tables Locally

For support, please pop a post on the appropriate FAD forum board.

1. Ensure SQL Server (2017 or later) with Integration Services is installed
2. Install Visual Studio with Data Tools
3. Install SSIS Projects extension
4. Request the FD DataSync SSIS package from the FAD team
5. Open Visual Studio and create a new Integration Services Project
6. Add the FD DataSync .dtsx package
7. Update the Connection Managers:
   • Destination → your local server/database
   • Source → FAD server (use your credentials)
8. Set parameters:
   • Table_Name
   • Table_Schema
9. Run the package
10. To update multiple tables, create a new package with a For Loop task, with an Execute Package task inside it that will call the FD DataSync package and pass to it the table name you want to update. Use the table Release_Details.All_Release_Details_By_Dataset to get your list of tables that you want as that tells you when each table was last updated. See video for more details.

Note: Collation (for users of the old UKHD downloadable database)

If you previously used the UK_Health_Dimensions downloadable database, note:

• Legacy collation: Latin1_General_CI_AS / Latin1_General_CS_AS
• Azure collation: SQL_Latin1_General_CP1_CI_AS / SQL_Latin1_General_CP1_CS_AS

When using FD DataSync, tables will adopt the Azure collation. If required, you can modify the SSIS package to retain the legacy collation. You will only want to do this for tables maintained by our legacy UKHD system:

SELECT * 
FROM Release_Details.All_Available_Tables 
WHERE Information_Schema_Name = 'Information_Schema_UKHD'

Update the "Create Staging Table Script" task as follows.

Replace:

(CASE
    WHEN DATA_TYPE IN (''VARCHAR'',''nvarchar'',''CHAR'') AND COLLATION_NAME IS NULL
    THEN'' COLLATE'' + CONVERT (VARCHAR(256), SERVERPROPERTY(''collation''))
    WHEN DATA_TYPE IN (''VARCHAR'',''nvarchar'',''CHAR'') AND COLLATION_NAME IS NOT NULL
    THEN'' COLLATE'' + COLLATION_NAME
    ELSE''  ''
END)
 

With:

(CASE
    WHEN DATA_TYPE IN (''VARCHAR'',''nvarchar'',''CHAR'', ''NCHAR'') AND COLLATION_NAME = ''SQL_Latin1_General_CP1_CI_AS''
    THEN ''Latin1_General_CI_AS''
    WHEN DATA_TYPE IN (''VARCHAR'',''nvarchar'',''CHAR'', ''NCHAR'') AND WHEN COLLATION_NAME = ''SQL_Latin1_General_CP1_CS_AS''
    THEN ''Latin1_General_CS_AS''
    WHEN DATA_TYPE IN (''VARCHAR'',''nvarchar'',''CHAR'', ''NCHAR'')
    THEN ''Latin1_General_CI_AS''
    WHEN DATA_TYPE = ''varbinary''
    THEN NULL
END)

With over 8,000 objects in FAD, finding the right dataset can sometimes feel overwhelming. We are actively working on improving search and discovery tools. In the meantime, please use the approaches below.

We also recommend watching our YouTube video Lesson 2: Catalogue Of All The Data We Have for an overview of how to explore FAD.

You can browse available objects using the following:

• SQL Server Object Explorer
• The FAD catalogue: [Release_Details].[All_Available_Tables]

Note, not all objects may appear in your database and therefore may not be visible in Object Explorer, all objects will be in the [Release_Details].[All_Available_Tables] table however. Please review Lesson 3: Add New Tables From The FAD Catalogue

You can search for datasets using a general term (e.g. Breast Screening) within the FAD catalogue [Release_Details].[All_Available_Tables].

DECLARE
@SearchTerm VARCHAR(255) = 'Breast Screening'  --Please replace this string with your search phrase
SET 
@SearchTerm = LOWER(REPLACE(REPLACE(@SearchTerm, ' ', ''), '_', '')) --This SET will handle any spaces, underscores etc between words
 
SELECT *
FROM Release_Details.All_Available_Tables
WHERE 
    REPLACE(REPLACE(Table_Schema, ' ', ''), '_', '') LIKE '%' + @SearchTerm + '%'
 OR REPLACE(REPLACE(TABLE_NAME, ' ', ''), '_', '') LIKE '%' + @SearchTerm + '%'
 OR REPLACE(REPLACE(Table_Description, ' ', ''), '_', '') LIKE '%' + @SearchTerm + '%'

If you know the original data source website, you can search using the source URL.

Tip: Try different parts of the URL if the full string does not return results.

DECLARE
@URL VARCHAR (250)
SET
@URL = 'data.london.gov.uk/dataset/housing-led-population-projections'  
 
SELECT *
FROM Release_Details.All_Available_Tables
WHERE
Source_Notes LIKE '%' + @URL + '%'

Within Snowflake, you can also search across column names and values across our UKHF datasets via "Release_Details"."FAD_Search_Data". For further guidance, please see our YouTube video.

The Snowflake version includes an AI powered chat bot to use for searching.

Future improvements We are working on additional ways to search FAD, including:

• Searching by data filename
• Improved catalogue filtering and discovery tools

This section will be updated as new functionality becomes available

After searching FAD for some data, there are three possible outcomes. Below we explain these outcomes and what you may need to do.

1. You found what you were looking for: Fantastic, get developing using FAD data and we'll take care of ensuring it's up to date, accurate and maintained!

2. The data does not appear to exist in FAD and you would like to request it: In most cases, if you cannot find the data using the recommended search methods, it is likely that we do not currently hold it. However, we actively welcome new data requests.

Please submit a request via the New Data Requests board on the FAD forum, including:

• The full web link where the data is publicly accessible
• Any filename(s)
• The business use case or benefit
• Any additional context that may help

Before requesting a new data source, please check that the licence allows the data to be copied, processed and redistributed. We typically look for wording that explicitly permits reuse, such as Open Government Licence (OGL) terms or similar open-data licences.

As a general rule, the data must be publicly available. Data behind a login may still be acceptable if anyone can create an account and the licence permits reuse. However, data that is restricted to authorised users, internal NHS teams, or specific organisations is unlikely to be suitable unless the licence explicitly allows redistribution.

It is also important to check whether the licence permits commercial use. Some providers allow use of their data only after written permission has been granted, while others allow unrestricted reuse. Please remember that users of the FAD service are responsible for ensuring their use is aligned with the licensing requirements.

Before submitting a request, consider the following:

1. Does the licence allow copying, processing and redistribution?
2. Is commercial use permitted?
3. Is the data available to the general public?
4. Is there a published licence or terms of use?

Even if the licence appears to allow reuse, we will still carry out our own review and contact the data provider where appropriate before onboarding a new source. This ensures we have a record of the permissions and licensing terms associated with the dataset

Should the licensing agreement allow us to proceed, the FAD team will review your request and respond via the forum, letting you know where the newly ingested data is. We will also share updates on newly added data sources via our newsletter.

3. You can see the data, but cannot access or query it: In some cases, the dataset exists in the FAD catalogue Release_Details.All_Available_Tables but is not yet available in your environment.

If this happens, we recommend following the standard ingestion process to bring the data into your database (see our YouTube video Add New Tables From The FAD Catalogue). Given the scale of FAD (thousands of datasets across multiple schemas), not all objects are automatically deployed to every environment. If you identify a dataset that is particularly valuable for your organisation, please let us know so we can consider including it in future deployments for similar users.

By the very nature of FAD, the database is constantly being updated with new data. The below will let you know how you can check certain aspects of data updates.

The below reference table, will show for each object, the last time that it was updated.

SELECT * 
FROM Release_Details.All_Release_Details_By_Dataset
WHERE
Table_Schema = 'ODS'   --Replace with your schema
AND
TABLE_NAME = 'GP_Practices_And_Prescribing_CCs_SCD'   --Replace with your table name

Note for FD Data Sync Users: This information can also be used to trigger sync processes and ensure your local database remains up to date.

This will be dependent upon when the data publisher releases the data, publicly, for us to then ingest. Please check your specific source data publications online, to understand this for your data source. Data ingests to FAD, will normally happen on the same working day, or for some more complex datasets, the following working day.

Should we experience any delays loading data, we will let users know, via the FAD forum. When we know that data will no longer be updated (ie publications have ceased), we will mark these data sources as discontinued so you are aware.

Some objects may be marked as discontinued if:

• The original data publication has stopped, or
• The source data structure has changed significantly and has been replaced by a new object

To check if a dataset is discontinued, you can use the following query:

SELECT 
  Table_Schema
 ,TABLE_NAME
 ,Table_Description
 ,Source_Notes
 ,Discontinued                        --A value of 1 indicates the item is discontinued.
 FROM Release_Details.All_Available_Tables
 WHERE
 Table_Schema = 'AandE_Attendance'    --Replace with your schema
 AND
 TABLE_NAME = 'Age_Type_1_ECDS'	      --Replace with your table name

See Table_Description column which will include notes on why it was discontinued and what table to use instead where applicable.

If you believe a data object in FAD contains incorrect or unexpected values, the first step is to compare what you're seeing in FAD, against the published source data. This will help determine where the issue lies

If the FAD data matches the published source: where the data in FAD accurately reflects what has been published, the issue lies with the source publication itself rather than our ingestion. In these cases, as the subject matter expert in the data content, you are best placed to raise this directly with the publisher. We'd kindly ask that you share any updates or responses from the publisher on the relevant FAD forum board. This helps the whole community stay informed and gives us visibility, so we can update our ingestion if and when the publisher corrects the source data.

If the FAD data does not match the published source: where there is a discrepancy between what FAD holds and what the publisher has made available, please raise this directly with us via the forum and we will investigate as a priority.

In this section, we'll help you understand some of the FAD derived fields that we add to data objects, that allow you to use the data in different ways.

Essentially data sources that are Facts don't change over time as each publication is unique for the time period that the publication covers. If a publication is revised (i.e. due to publishing errors or on rare occasions, updated data), then the FAD system will always display the most recent data. You may see some external Fact tables that have a '1' suffix. These are in fact views that always pull the records, from the most recently published file. The underlying table holds records from all versions, with the view always providing the most recent publication. For more information, please see our YouTube video on Why some tables end in a 1

Dimension data can and often does, change over time. For example, the telephone number or parent organisation of a GP Practice, may change over time and users need to understand this timeline. Therefore our Dimension data, is often loaded into a Slowly Changing Dimension (SCD) Table. We use SCD tables when we expect data to change over time and users need to access the history. They can be really powerful to tell the user, what the data used to be, what it is now, when that change took place etc.

These SCD tables have a number of FAD generated fields to help your usage of the data and allow users to select the most recent data, or data as it was at a moment in time. Please find a brief summary of these fields below and to better your understanding, please also watch Explain system columns in FAD tables.

Import_Date: Date that we ingested the data into our FAD system.

Created_Date: Typically the date when the source data was uploaded and became available for public use.

Is_Latest: Flag that allows the user to identify the most current record where [Is_Latest = 1].

In_Source_Data: Flag indicating whether the record appears in the latest source file. i.e., if a record was in one file but does not appear in the next publication, rather than deleting it, we will set the In_Source_Data flag to 0, since the record is not included in the latest release.

Effective From: NULL for the first ever time the file was loaded, otherwise Created_Date. Note this is WHEN the record appeared in the data, this is not necessarily the same as when that row was valid for operational use.

Effective To: 3ms before the midnight of the newer version.

Within our Snowflake database, every fact table (same name as Azure, but prefixed 'fact_') also has a report view (same name but prefixed 'rpt_'). Our system auto generates those rpt views. It uses this table to know which dimensions tables to link to, and on what column to join [Release_Details].[UKHF_Recommended_Fact_Dim_Joins]. Users can review this table, to see what joins we recommend. These rpt views, are only available in our Snowflake database.

We have deployed FAD into the FDP for a number of NHS Organisations and the below details the necessary steps to do this. Should you need any support in your deployment or want to know more about our 'NHS FDP Library', please do get in touch.

Should you experience any technical errors, please review those above for e.g. IP white listing.

Before any data can be synchronised into the FDP, it is necessary to configure a connection to the FAD server. Details below are for an Azure based connection - please ensure that you have your customer database credentials available. If you're not sure of these, please get in touch.

1. Navigate to the area of the tenancy where you want to host the server. We would recommend that a folder be created as 'Source | FAD Reference Data'.
2. Within this folder, create a secondary folder where the data will be synchronised to. We would recommend this be created as 'FAD Synced Data'
3. Select New > Data Source > SQL > Continue
4. On the next screen, please ensure that in the top right 'Compute' is set to 'Agent Worker', checking with your local FDP lead what agents should be assigned
5. Enter your FAD supplied database credentials. We would recommend naming your source as 'Facts & Dimensions Ltd Microsoft SQL Server' for clarity and consistency.
6. The export and code import configuration screens can be skipped.
7. On the output folder screen, please configure this so that the default folder for syncs is the folder created above 'FAD Synced Data'
8. Configuration should now be complete and on the final 'Summary' configuration screen, click 'Explore' in the bottom right to navigate the FAD database.
9. If the user sees a Configuration Error Screen, review the details and configuration to identify and resolve the issue.

Now that a connection to the FAD database is available, we can synchronise objects from FAD, into the FDP, for use in pipe lining.

FAD would recommend that you create a meaningful folder structure within 'FAD Synced Data' so that users can easily navigate to find the data that they are looking for. Users can create these locally or if you'd like to replicate our 'NHS FDP Library' please get in touch.

1. Navigate to your FAD server within the FDP and select 'Explore Source'
2. In the left hand 'Previewing Source' pane, navigate to and select the object that you want to synchronise from FAD, into the FDP. Click the 'Add table to sync' button.
3. Ensure that the synchronisation location is within the 'FAD Synced Data' folder that we defaulted to in the server configuration, amending accordingly. We recommend retaining the original FAD object name and ingesting the full object, rather than applying SQL criteria. Naturally there may be use cases where restricting the data is recommended, however we would recommend not ingesting a FAD object more than once. Instead, ingest it once and adjust within each use case locally.
4. Click 'Create sync for 1 dataset'. Now that the sync has been created, we need to configure it as required with a suitable schedule. Please ensure that 'Allow Schema Changes' is configured so that the sync can process if eg a new column is added to the source data.

You should now have configured the FAD server and have object synchronisations set up, to sync the objects from FAD, into the FDP. If you have any queries or need some support, please contact us.

Below are key resources to help you get the most out of FAD.

• YouTube Channel. Tutorials and walkthroughs covering how to connect, sync, and use FAD. Please also see our detailed YouTube library below so you can see the range of videos available to you.
• FAD is also available in Snowflake, including support for advanced analytics and AI capabilities.
• NHS Futures For our NHS users, additional FAD resources and updates are available via NHS Futures.
• LinkedIn Follow us for high-level updates, announcements, and collaboration highlights.
• FAD Forum. A community space for updates, questions, and discussions related to FAD.
• FAD Bulletin. A monthly email bulletin, keeping you up to date with all of the FAD news and changes.
• FAD User Group. We host a quarterly MS Teams User Group so users can come together and discuss all things FAD.

Please see below the FAD YouTube videos that are available for further information. Any suggestions. would be welcomed for new content that you'd like to see. We list below the four different playlists that we have and the content of each.

Facts and Dimensions Tutorial

• Lesson 1a: Connect via SSMS
• Lesson 1b: Connect via Linked Server
• Lesson 1c: Connect via Excel
• Lesson 1d: Connect via Access and ODBC
• Lesson 1e: Connect via Tableau
• Lesson 1f: Connect via Power BI
• Lesson 1g: Connect via R
• Lesson 1h: Connect via app.powerbi.com
• Lesson 1i: Connect via Python
• Lesson 1j: Connect using Qlik Cloud to Snowflake database
• Lesson 1k: Connect using Qlik Cloud to Azure database
• Lesson 1l: Connect using DBeaver
• Lesson 1m: Connect to Snowflake from Excel Using ODBC
• Lesson 1n: Connect to Snowflake via SQL Linked Server in SSMS

• Lesson 2: Catalogue Of All The Data We Have
• Lesson 3: Add New Tables From The FAD Catalogue
• Lesson 4: Faster Queries Over Linked Server
• Lesson 5: Dynamic SQL, OpenQuery and Cursors
• Lesson 6: Explain system columns in FAD tables
• Lesson 7: FD Data Sync for Syncing FAD Tables Locally
• Lesson 8: Why some tables end in a 1
• Lesson 9: Fix External Tables Issues
• Lesson 10: Postcode and Char_8_ASCII_Index Computed Column
• Lesson 11: ODS API vs ODS schemas plus ONS Codes and NHS111s
• Lesson 12: Deploy A Copy of our DB to Your Azure Server
• Lesson 13: Create External Tables In Your Own Azure SQL Database
• Lesson 14: Dealing with source data structural changes
• Lesson 15: Data Dictionary Name Changes
• Lesson 16: How to Query the CQC Tables
• Lesson 17: Power Search Across All The Values In All The Columns In All The Tables

Facts and Dimensions Snowflake Videos - showcasing Cortex AI

• FAD Search using Snowflake AI - ready to use now!
• Does high flu vaccine uptake reduce LRTI admissions?
• Finding tables in FAD to help understand flu vacc rates and respiratory A&E admissions
• A&E Attendances and Demographic correlations
• What do wealthy GP Practices prescribe vs high deprivation practices
• What do GPs with old patients prescribe compared to young?
• CVD Prevent
• Snowflake Cortex Query Metadata

We are continuously developing additional support content. Future planned topics include:

• Key Data Source Summary and tips eg ODS schema
• Understanding the differences between FAD in Azure and Snowflake
• And more…

If there is something that you'd like us to include that isn't in the list, please do contact us.