CUSTOMER SUPPORT QUESTIONS

• Facts are the numerical values that show what happened. For example, how many patients visited a hospital, how many prescriptions were written, or how much money was spent. They represent what is being measured.
• Dimensions provide the context for those numbers. These might include location, category, treatment type, or hospital site. They help explain where, when, and who the facts relate to.

• FAD server name
• Username
• Password
• Database name

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

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

• Lesson 1b: Connect via Linked Server

• Lesson 4: Faster Queries Over Linked Server

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'

• Copies one selected table 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

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

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 FDDataSync .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 FDDataSync package and pass to it the table name you want to update. Use the table Release_Details.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.

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

(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)

(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)

• SQL Server Object Explorer
• 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 + '%'

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 + '%'

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

• 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

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

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

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

• YouTube Channel. Tutorials and walkthroughs covering how to connect, sync, and use FAD.

• FAD Forum. A community space for updates, questions, and discussions related to FAD.
• Snowflake 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.

• Overview of Facts & Dimensions
• Technical Considerations of FAD
• More detail on FAD usage and specific processes
• FAD User Group
• FAD Forum
• Key Data Source Summary eg ODS