Known Issues
This article contains known issues related to the Datawarehouse Export Service.
Please check if your issue can be found here before contacting support.
Preparing the datawarehouse database
It is highly recommended that a newly installed datawarehouse export service is pointed towards a new and empty database. If there is a need or a scenario where the service will be ran in conjunction with an already existing or repurposed database, that database needs to be cleared of all its contents, including all tables, stored procedures and all other elements in the database.
The previously mentioned scenario also applies to the databases that were previously used for datawarehouse exporting purposes. In that case, the datawarehouse database is cleared by running the following SQL script:
Make sure to carefully look through the generated DROP commands before running them towards the datawarehouse database.
-- DROP ALL PROCEDURES
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimMissionTypeSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimMissionIdSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimResourceIdSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimStationSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimHealthTrustSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimUserSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimDispatchCodeSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimDispatchCenterSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimChiefComplaintSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimInterventionTypeSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimTransferTargetSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimDateSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimTimeSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimLocationSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimOtherMissionInformationSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimGenericNameSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimUnitSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimAdministrationSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimDoseSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimTriageColorSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimTriageScoreSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimTriageMethodSK];
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDimEssNumberSK];
-- DROP ALL TABLES
DROP TABLE IF EXISTS [dbo].[FactSats];
DROP TABLE IF EXISTS [dbo].[FactRetts];
DROP TABLE IF EXISTS [dbo].[FactNews2];
DROP TABLE IF EXISTS [dbo].[FactMission];
DROP TABLE IF EXISTS [dbo].[FactMedication];
DROP TABLE IF EXISTS [dbo].[FactIntervention];
DROP TABLE IF EXISTS [dbo].[FactIncident];
DROP TABLE IF EXISTS [dbo].[DimUser];
DROP TABLE IF EXISTS [dbo].[DimUnit];
DROP TABLE IF EXISTS [dbo].[DimTriageScore];
DROP TABLE IF EXISTS [dbo].[DimTriageMethod];
DROP TABLE IF EXISTS [dbo].[DimTriageColor];
DROP TABLE IF EXISTS [dbo].[DimTransferTarget];
DROP TABLE IF EXISTS [dbo].[DimTime];
DROP TABLE IF EXISTS [dbo].[DimStation];
DROP TABLE IF EXISTS [dbo].[DimResourceId];
DROP TABLE IF EXISTS [dbo].[DimOtherMissionInformation];
DROP TABLE IF EXISTS [dbo].[DimMissionType];
DROP TABLE IF EXISTS [dbo].[DimMissionId];
DROP TABLE IF EXISTS [dbo].[DimLocation];
DROP TABLE IF EXISTS [dbo].[DimInterventionType];
DROP TABLE IF EXISTS [dbo].[DimHealthTrust];
DROP TABLE IF EXISTS [dbo].[DimGenericName];
DROP TABLE IF EXISTS [dbo].[DimEssNumber];
DROP TABLE IF EXISTS [dbo].[DimDose];
DROP TABLE IF EXISTS [dbo].[DimDispatchCode];
DROP TABLE IF EXISTS [dbo].[DimDispatchCenter];
DROP TABLE IF EXISTS [dbo].[DimDate];
DROP TABLE IF EXISTS [dbo].[DimChiefComplaint];
DROP TABLE IF EXISTS [dbo].[DimAdministration];
DROP TABLE IF EXISTS [dbo].[__EFMigrationsHistory];
If the existing datawarehouse database is not cleared before the service is ran, the service will fail to start or exhibit other unexpected behavior.
What can I do if the datawarehouse database is not completely empty after Preparing the datawarehouse database?
If the datawarehouse database is not completely empty after Preparing the datawarehouse database, the datawarehouse export service will fail to start or exhibit other unexpected behavior. This can be fixed by generating DROP commands for the remaining tables and stored procedures in the datawarehouse database and running them towards the datawarehouse database. This can be done by running the following SQL script towards the datawarehouse database:
-- GENERATE DROP COMMANDS FOR ALL STORED PROCEDURES
SELECT 'DROP PROCEDURE IF EXISTS [' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + '];'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
-- GENERATE DROP COMMANDS FOR ALL TABLES
SELECT 'DROP TABLE IF EXISTS [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '];'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME DESC;
Error attemping to export Journals without mission type
In some rare circumstances, we can get completed journals without mission type data.
The service is unable to export data from these journals to the datawarehouse database, and will log warnings/errors for every attempt forever unless they are deleted or marked as ignored manually in the main EWA database.
To check how many journals there are without mission type:
SELECT Id FROM Journals where [Status] = 3 AND JSON_VALUE([Data], '$.MissionType') IS NULL
If there are any results from this query it is recommended to mark all the journals without mission type as ignored.
This can be done with this SQL script:
BEGIN TRANSACTION;
UPDATE JournalExportStatus
SET [Status] = 4, LastAttemptAt = GETUTCDATE(), LastSuccessAt = GETUTCDATE(), EndpointResponse = 'Missing MissionType'
WHERE ConfigurationId IN (SELECT Id FROM ExportConfigurations where [Type] = 4 AND [DeletedAt] IS NULL)
AND JournalId IN (SELECT Id FROM Journals where [Status] = 3 AND JSON_VALUE([Data], '$.MissionType') IS NULL)
INSERT INTO JournalExportStatus (ConfigurationId, JournalId, [Status], LastAttemptAt, LastSuccessAt, EndpointResponse)
SELECT EC.Id as ConfigurationId, J.Id as JournalId, 4, GETUTCDATE(), GETUTCDATE(), 'Missing MissionType'
FROM (SELECT Id FROM ExportConfigurations where [Type] = 4 AND [DeletedAt] IS NULL) as EC,
(SELECT Id FROM Journals where [Status] = 3 AND JSON_VALUE([Data], '$.MissionType') IS NULL) as J
WHERE NOT EXISTS (SELECT 1
FROM JournalExportStatus as JES
WHERE JES.ConfigurationId = EC.Id AND JES.JournalId = J.Id)
COMMIT TRANSACTION;