Skip to content

README.md

Latest commit

 

History

History
247 lines (172 loc) · 17.4 KB

README.md

File metadata and controls

247 lines (172 loc) · 17.4 KB

📦 ExchangeRate.Database

SQL Server Database Project (SSDT)
Solution : EcbFxReference

🎯 Objectif du projet

ExchangeRate.Database est un projet SQL Server Data Tools (SSDT) destiné à versionner et déployer le schéma de la base ExchangeRatesDB.

Il permet de :

  • 📈 Stocker les taux de change journaliers (ECB)
  • 🧠 Calculer des indicateurs métier (dernier taux connu, taux stables, taux tarifaires)
  • ⚙️ Exposer des vues optimisées pour l’ETL SSIS et les usages analytiques

✅ Garanties apportées

  • Versionnement du schéma via Git
  • Déploiement reproductible
  • Approche Database-as-Code

🏗 Architecture du projet

Organisation par type d’objet SQL :

dbo/
 ├── Tables/
 ├── Views/
 ├── Stored Procedures/
 └── Indexes/

💽 Diagramme de base de données

Diagramme ExchangeRate

🗄 Tables principales

Les tables de référence (dimension)

  • exchange_currency_series_ref
  • exchange_rate_series

Les tables des faits (taux journaliers ou consolidés)

  • exchange_rates_daily
  • exchange_rates_tariff_monthly
Table Rôle
currency_ref Référentiel de toutes les devises ECB
exchange_currency_series_ref Référentiel de toutes les séries de taux de change exprimées contre l’euro
exchange_rate_series Paramétrage et configuration des séries suivies
exchange_rates_daily Observations journalières
exchange_rates_tariff_monthly Snapshots tarifaires mensuels

• • •

📌 Table de référentiel : exchange_currency_series_ref

La table exchange_currency_series_ref constitue le référentiel des séries de taux de change ECB exprimées contre l’euro. Elle recense les clés de séries (series_key) et leurs métadonnées associées.
Elle sert de table de référence pour garantir l’intégrité référentielle des tables dépendantes, notamment exchange_rate_series, en contrôlant les séries autorisées dans le système.
Elle représente la source de vérité des séries autorisées dans le système et assure la cohérence fonctionnelle des traitements.

📌 Table de paramétrage : exchange_rate_series

La table exchange_rate_series centralise le paramétrage des séries de taux de change suivies par le système.
Elle permet de piloter dynamiquement les chargements SSIS sans modification du code.

Chaque enregistrement définit :

  • series_key : clé de série ECB
  • currency : devise concernée
  • is_active : activation/désactivation dynamique
  • date_create, created_by, comment : métadonnées techniques

🎯 Avantages

  • Suppression du hardcoding dans SSIS
  • Pilotage dynamique des chargements
  • Ajout simplifié de nouvelles devises
  • Traçabilité minimale du paramétrage

La contrainte d’unicité (series_key, currency) garantit l’intégrité fonctionnelle.

• • •

🌍 Table de référence : currency_ref

La table currency_ref constitue le référentiel des devises utilisées dans le système.
Elle centralise les codes ISO 4217 (currency_code) ainsi que leur libellé descriptif (currency_label).

• • •

📊 Vues métier

Vue Rôle
vw_exchange_rate_series_last_rate Dernier taux connu par série
vw_exchange_rate_series_daily_summary Résumé statistique par série
vw_exchange_rate_series_stable_rates Calcul des taux stables (30/60/90 jours)
vw_exchange_rate_series_tariff_rates Taux tarifaire avec logique de fallback

✔ Logique métier encapsulée côté base → réduction de la complexité SSIS

• • •

⚙️ Procédures stockées

Procédure Rôle
usp_GetLastRateDate Retourne la dernière date disponible
usp_LoadCurrencyRef_FromXmlFile Charge les devises depuis un XML
usp_LoadTariffRatesMonthly Génère les snapshots tarifaires
usp_SeedExchangeRateSeries Initialise la table de paramétrage

👉 Ces procédures sont principalement consommées par les packages SSIS.

▶ Utilisation de dbo.usp_GetLastRateDate

La procédure dbo.usp_GetLastRateDate permet de récupérer la dernière date de taux de change enregistrée dans la table métier dbo.DEVISES_HISTO.
Elle est utilisée pour déterminer le point de reprise d’un chargement incrémental et éviter de retraiter des données déjà synchronisées.

-- Déclaration d’une variable locale pour recevoir la valeur OUTPUT
DECLARE @LastRateDateFound DATE;
-- Appel de la procédure stockée
EXEC dbo.usp_GetLastRateDate
        @SeriesKey    = N'EXR.D.USD.EUR.SP00.A',
        @LastRateDate = @LastRateDateFound OUTPUT;
-- Lecture de la valeur retournée
SELECT @LastRateDateFound AS LastRateDate

La procédure retourne la date la plus récente disponible dans l’historique des taux.

▶ Utilisation de dbo.usp_LoadCurrencyRef_FromXmlFile

La procédure dbo.usp_LoadCurrencyRef_FromXmlFile permet de charger et d’alimenter la table de référence des devises à partir d’un fichier XML SDMX (Codelist ECB).
Elle lit le fichier depuis un chemin fourni en paramètre, extrait les codes et libellés des devises, puis met à jour la table de référence en garantissant la cohérence des données.
Cette procédure est utilisée lors de l’initialisation du référentiel des devises globales.

EXEC dbo.usp_LoadCurrencyRef_FromXmlFile
     @FilePath = 'D:\SSIS\Data\In\currency_ref.xml';

Le paramètre @FilePath doit correspondre à un chemin accessible par le service SQL Server.
La procédure peut être intégrée dans un flux SSIS ou exécutée manuellement pour mettre à jour le référentiel à partir de la dernière version officielle publiée par l’ECB.

▶ Utilisation de dbo.usp_LoadTariffRatesMonthly

La procédure dbo.usp_LoadTariffRatesMonthly génère et charge un snapshot mensuel des taux tarifaires à partir de la vue métier vw_exchange_rate_series_tariff_rates.
Elle permet de figer, pour une date de référence donnée (@AsOfDate), les taux applicables aux calculs tarifaires.
Si aucune date n’est fournie, la date du jour est utilisée par défaut.
Le paramètre @Persist permet de choisir entre un mode lecture seule (0, retourne les données sans insertion) et un mode persistant (1, supprime puis recrée le snapshot pour garantir l’idempotence).

Exécution standard avec persistance :

EXEC dbo.usp_LoadTariffRatesMonthly;

Exécution pour une date spécifique :

EXEC dbo.usp_LoadTariffRatesMonthly
     @AsOfDate = '2026-01-01',
     @Persist  = 1;

Mode lecture de contrôle seule (sans insertion) :

EXEC dbo.usp_LoadTariffRatesMonthly
     @AsOfDate = '2026-01-01',
     @Persist  = 0;

▶ Utilisation de dbo.usp_SeedExchangeRateSeries

La procédure dbo.usp_SeedExchangeRateSeries initialise (seed) la table de paramétrage dbo.exchange_rate_series avec un ensemble prédéfini de séries de taux de change ECB utilisées par le système.
Elle implémente un mécanisme MERGE UPSERT idempotent basé sur la clé logique (series_key, currency) : si une série n’existe pas, elle est insérée ; si elle existe déjà, aucun doublon n’est créé et l’état courant est conservé.
Cette procédure permet de garantir la présence des séries de référence nécessaires au fonctionnement du système lors d’une première installation.
La procédure peut être exécutée plusieurs fois sans effet de duplication.

EXEC dbo.usp_SeedExchangeRateSeries;

• • •

🚀 Index de performance

Des index non clusterisés optimisent :

  • La recherche du dernier taux par série
  • Les agrégations temporelles

Exemple

CREATE INDEX IX_exchange_rates_daily_last_rate
ON dbo.exchange_rates_daily
(
    series_key,
    time_period DESC
)
INCLUDE
(
    obs_value,
    obs_status
);

🎯 Objectif : accélérer les requêtes de type “dernier taux connu”.

• • •

🔄 Intégration avec SSIS

Ce projet est consommé par :

ExchangeRate.SSIS (SQL Server 2019)

Les packages SSIS utilisent :

  • Les vues pour la lecture optimisée
  • Les procédures stockées pour la logique métier
  • Les index pour les performances d’exécution
  • La table de paramétrage pour piloter les chargements

🚀 Déploiement

Le déploiement se fait via Visual Studio 2022 avec des fichiers .publish.xml par environnement.

• • •

📊 Positionnement dans la solution EcbFxReference

EcbFxReference
 ├── ExchangeRate.Database   ← Schéma SQL versionné
 ├── ExchangeRate.SSIS       ← Orchestration ETL
 └── SalesMgmt.Database      ← Base métier cible

Ce projet constitue la couche persistence technique des taux ECB.