SQL Server MDS – Suppression des lignes désactivées d’un Model

Amis utilisateurs de MDS bonjour !

Il est bon de savoir que, pour des raisons compréhensibles d’historique et de récupération de données, MDS ne fait que désactiver les lignes supprimées via les interfaces Web et Excel. Ça soulage toujours de savoir qu’on pourra rattraper l’éventuelle grosse bêtise si elle a lieu…

Le problème vient quand un utilisateur découvre qu’un membre de l’entité qu’il gère n’est plus utilisé. Pour des raisons de lisibilités, il décide de supprimer la ligne et publie :

Côté MDS, chaque entité possède une clef primaire en la présence du champ « Code », l’enregistrement possédant ce code est ici désactivé.

Mais, si ce code doit à nouveau être utilisé, l’utilisateur rencontre le message d’erreur suivant :

Vous vous en doutez: la nouvelle ligne rentre en conflit d’unicité avec celle supprimée, préservée dans la base. Tout aurait pu être parfait si nous avions eu la possibilité de visualiser ces lignes désactivées depuis les interfaces auxquelles un utilisateur classique (non admin) aurait accès, mais ce n’est pas le cas.

A l’heure actuelle, l’utilisateur est redirigé via un message d’erreur vers un administrateur pour retirer le membre, pas vraiment self-service…
Il existe 2 solutions pour résoudre le problème :
– Restaurer la ligne depuis l’interface Web avec un compte ayant les accès à la gestion des versions :

– Supprimer les lignes depuis la table de staging :

Les tables de staging, sont des tables intermédiaires par lesquelles vous pouvez passer pour ajouter, modifier ou supprimer des lignes de vos entités. Chaque entité possédera sa propre table de staging. Vous pourrez trouver la correspondance entre vos entités et vos table de staging dans la table système “MDSDB.mdm.viw_SYSTEM_TABLE_NAME”

Pour chaque entité, MDS mettra à disposition une procédure pour transférer les données de la table de staging dont le nom sera au format “stg.udp_[MaTableStaging]”

Nous pouvons donc utiliser une procédure stockée qui comprendra 3 étapes principales :

• Insertion des données dans la table de staging avec les paramètres adéquats
• Appel de la procédure MDS pour transférer les données de la table de staging vers la table source de l’entité
• La validation des données

​CREATE PROCEDURE [mds].[DeleteUnactiveRowsFromEntity]
@EntityTableName VARCHAR(50)
, @StagingLeafName VARCHAR(50)
, @Model_Id INT
AS
BEGIN

DECLARE @BatchTagText AS VARCHAR(50) = 'Suppression Automatique du ' + CAST(CAST(getdate() AS Date) AS VARCHAR)
DECLARE @SQLCommand VARCHAR(MAX)

SET @SQLCommand =
'INSERT INTO [MDSDB].[stg].[' + @StagingLeafName + ']
([ImportType]
,[ImportStatus_ID]
,[BatchTag]
,[Code]
)
SELECT 6 AS [ImportType] --6 pour suppression permanente des données
,0 AS [ImportStatus_ID]
,'''+ @BatchTagText+ '''
,[Code]
FROM [MDSDB].[mdm].' + @EntityTableName
+ ' WHERE Status_ID = 2'

EXEC (@SQLCommand);

DECLARE @Version_ID INT
DECLARE @Version_Name VARCHAR(50)

SELECT @Version_ID = ID
, @Version_Name = Name
FROM MDSDB.mdm.viw_SYSTEM_SCHEMA_VERSION V
WHERE Model_ID = @Model_ID
AND V.ID = (SELECT MAX(ID)
FROM MDSDB.mdm.viw_SYSTEM_SCHEMA_VERSION V
WHERE Model_ID = @Model_ID)

--Lancement du lot de MàJ des données
SET @SQLCommand =
'EXEC MDSDB.[stg].[udp_' + @StagingLeafName + ']
@VersionName = ''' + @Version_Name +'''
,@LogFlag = 1
,@BatchTag = ''' + @BatchTagText + '''' ;
EXEC (@SQLCommand);

--Validation des données
DECLARE @User_ID INT

SET @User_ID = ( SELECT ID
FROM MDSDB.mdm.tblUser u
WHERE u.UserName = SUSER_NAME())
--Validation des données insérées ou modifiées dans le modèle
EXECUTE MDSDB.mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

END

Nous pourrons utiliser une procédure chapeau qui englobera la totalité des entités d’un modèle passé en paramètre :

CREATE PROCEDURE [mds].[DeleteUnactiveRowsFromModel]
@Model_Name varchar(50)
AS
BEGIN
DECLARE @Model_Id INT

--Récupération de l'ID du modèle avec la dernière version
SET @Model_ID = ( SELECT TOP 1 Model_ID
FROM MDSDB.mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @Model_Name)

DECLARE @ListEntities TABLE ( EntityName varchar(50)
, EntityTableName varchar(50)
, StagingLeafName varchar(50)
, NumLigne INT)

--Récupération de la liste d'entités du modèle
INSERT INTO @ListEntities
SELECT EntityName
, EntityTableName
, StagingLeafName
, ROW_NUMBER() OVER (ORDER BY EntityTableName) AS NumLigne
FROM MDSDB.mdm.viw_SYSTEM_TABLE_NAME
WHERE Model_ID = @Model_Id

DECLARE @NbEntities INT = (SELECT MAX(NumLigne) FROM @ListEntities)
DECLARE @Compteur INT = 1

--On boucle pour chaque entité de notre modèle
WHILE @Compteur <= @NbEntities
BEGIN
DECLARE @EntityTableName varchar(50)
, @StagingLeafName varchar(50)

SELECT @EntityTableName = EntityTableName
,@StagingLeafName = StagingLeafName
FROM @ListEntities
WHERE NumLigne = @Compteur

--Appel de notre Proc, créée plus haut, en passant l'entité courante de la boucle
EXEC mds.DeleteUnactiveRowsFromEntity @EntityTableName, @StagingLeafName, @Model_Id

SET @Compteur = @Compteur + 1
END

Un job pourra être appelé toute les 15/30/60/90min suivant la patience des utilisateurs.

Certes, il s’agit de code SQL dynamique, pas forcément élégant, mais ça a le mérite de faire le travail en redonnant (un peu) la main à l’utilisateur, avec le délai d’appel du job que vous aurez paramétré.
La procédure est à utiliser avec parcimonie, certains environnements demanderont de conserver l’historique. Par exemple, pour une entité avec de nombreux champs, il sera plus simple de restaurer un membre plutôt que de le recréer.

Le choix est à l’utilisateur.