Serveur d'impression

10 Méthodes conseillées pour la conception d'entrepôts de données SQL Server (première partie) – Serveur d’impression

Le 20 juin 2019 - 11 minutes de lecture

Samedi dernier, j’ai eu le plaisir de prendre la parole lors de la conférence SQL Saturday n ° 552 ici dans la belle ville de Jacksonville, en Floride. Mon bon ami, Mitch Pearson (blog | gazouillement) et j’ai présenté notre session, Conception d’un entrepôt de données à partir de la base. Nous avons eu une grande foule et beaucoup de bonnes questions du public!

En ce qui concerne la conception d’un entrepôt de données et les meilleures pratiques, j’ai pensé prendre quelques instants pour noter certaines de mes réflexions sur les meilleures pratiques et les éléments à prendre en compte lors de la conception de votre entrepôt de données. Ci-dessous, vous trouverez les cinq premières des dix meilleures pratiques de conception d’entrepôt de données qui méritent d’être examinées. Cette liste n’est pas destinée à être la Dix «meilleures pratiques» à suivre et ne sont pas dans un ordre particulier. Bien entendu, chaque scénario de conception est différent et vous pouvez donc constater que certaines des meilleures pratiques répertoriées ici ne sont pas optimales dans votre situation spécifique.

1. Utilisez des attributs de dimension descriptifs.

Les meilleurs attributs de dimension sont ceux qui sont descriptifs par nature. Les attributs descriptifs sont plus faciles à comprendre du point de vue de l’utilisateur, car les attributs de dimension sont utilisés pour décrire, filtrer, contrôler, trier et fournir un contexte aux mesures quantitatives. Les attributs descriptifs permettent aux utilisateurs d’exploiter pleinement la valeur des métriques.

Prenez l’ensemble de graphiques suivant, par exemple. À gauche, un rapport est créé à l'aide de champs d'ID et à droite, le même graphique créé à l'aide d'attributs descriptifs. Même si vous comprenez les champs d'identification, l'utilisateur doit tout de même «décoder» les identifiants. Et pour les utilisateurs dont les identifiants ne sont pas mémorisés, les diagrammes à gauche sont inutiles.

Bien que les identifiants puissent utiliser un type de données plus petit et que de nombreux utilisateurs puissent "décoder" les identifiants, les attributs descriptifs fourniront une meilleure expérience utilisateur du point de vue de la génération de rapports. Cela ne signifie pas que les champs d'identifiant ne doivent pas être stockés dans un entrepôt de données, mais se fier uniquement aux identifiants pour la génération de rapports serait une erreur. Ce que nous ne voulons pas, c’est que les utilisateurs doivent mémoriser des scores d’ID ou conserver des notes pour définir les ID de produit, par exemple.

2. Stockez les mesures additives dans l'entrepôt de données.

Le meilleur type de mesures à stocker dans l’entrepôt de données est celui qui peut être entièrement agrégé. Une mesure qui peut être entièrement agrégée est une mesure qui peut être résumée par n'importe quelle dimension ou toutes les dimensions tout en restant significative. Par exemple, une mesure du montant des ventes peut être résumée par produit, date, géographie, etc., tout en offrant de précieuses informations au client.

Les mesures semi-additives peuvent également être stockées dans l'entrepôt de données. Un exemple de mesure semi-additive serait une mesure comme un solde de compte ou une quantité en stock. Ces mesures sont généralement stockées dans des tables de faits d'instantané. Une mesure telle que le solde du compte est considérée comme semi-additive car le solde du compte chaque jour du mois ne peut pas être additionné pour calculer le solde du compte du mois. Le solde du compte le dernier jour du mois est utilisé pour représenter avec précision le solde mensuel du compte. La logique permettant de calculer le solde le dernier jour d'une période (mois, trimestre, année, etc.) peut être intégrée dans une requête, un rapport ou un modèle sémantique, tel que SQL Server Analysis Services ou Power BI.

Les mesures qui ne peuvent pas être entièrement agrégées, telles que les ratios ou d'autres calculs de type de pourcentage, doivent être gérées dans le modèle sémantique ou dans l'outil de génération de rapports. Par exemple, une mesure telle que la marge bénéficiaire en pourcentage stockée dans une table ne peut pas être agrégée correctement. Une meilleure option serait de stocker les mesures additives constituant la base de la marge bénéficiaire en pourcentage, telles que Revenu, Coût, Marge, etc. Ces mesures de base peuvent être utilisées pour calculer le ratio dans une requête, un modèle sémantique ou un outil de génération de rapports. .

3. Utilisez les plus petits types de données possibles.

Utilisez toujours le type de données le plus petit possible. Cela signifie que nous ne devrions jamais utiliser un type de données chaîne lorsqu'un entier pourrait être utilisé. L'utilisation du type de données le plus petit possible optimisera le stockage des données, le traitement ETL (extraction – transformation – chargement), les rapports et le modèle sémantique.

Par exemple, dans une base de données SQL Server, une colonne avec un type de données entier utilise toujours 4 octets de stockage, quel que soit le nombre stocké. Un type de données varchar utilisera la longueur de la valeur plus deux octets. Ainsi, par exemple, une valeur de 1000000 occupera 4 octets de stockage lors de l'utilisation du type de données Int. Mais la même valeur stockée en tant que varchar utilisera 9 octets de stockage! Cela représente plus du double de la quantité de stockage requise lors de l’utilisation du type de données entier! Pour plus d'informations sur les types de données, utilisez ce lien: https://msdn.microsoft.com/en-us/library/ms187752.aspx?f=255&MSPPError=-2147217396

Cela peut également affecter considérablement les performances d'un outil ETL tel que SQL Server Integration Services (SSIS). Mémoire tampon SSIS dans un ensemble d'enregistrements en mémoire pour la transformation et le chargement dans la destination. La largeur (taille basée sur les types de données) de la ligne détermine le nombre d'enregistrements pouvant être chargés en mémoire. Si un ensemble de données utilise des types de données très volumineux, moins d'enregistrements peuvent être chargés en mémoire, ce qui entraîne l'exécution du package plus longtemps que nécessaire. Des types de données plus petits peuvent améliorer considérablement les performances d'un package SSIS.

Les types de données ont également une incidence importante sur les performances des cubes SQL Server Analysis Services (SSAS). Les types de données de chaîne sont stockés dans un fichier séparé spécial dans SSAS, ce qui signifie que l'utilisation de trop nombreuses colonnes de type de données de chaîne a un impact négatif sur le traitement des requêtes et du traitement des cubes. Pour plus d'informations sur les meilleures pratiques SSAS en matière de conception de dimensions, utilisez ce lien: //sqldusty.com/2015/07/17/3-ssas-dimension-design-best-practices-to-live-by/

4. Utilisez des clés de substitution.

Les clés de substitution sont des clés de base de données utilisées pour relier les tables de dimension aux tables de faits. Les clés de substitution (SK) n'ont aucune signification pour l'entreprise ni aucune signification intrinsèque. Les SK sont généralement affectés au moment où un enregistrement est chargé dans la table des dimensions et sont généralement gérés via le processus ETL. Les SK sont généralement utilisés comme clé primaire dans une table de dimension donnée et sont différents de la clé commerciale. Prenez cette table dans la base de données Adventure Works DW, par exemple:

La colonne SalesTerritoryKey est la clé de substitution de la table et la colonne SalesTerritoryAlternateKey est la clé commerciale. La clé commerciale est utilisée pour associer les enregistrements de dimension aux enregistrements source et la clé de substitution est utilisée comme clé primaire dans la table de dimension. Cela signifie également que dans la table des faits, aucune clé métier n'est stockée. La table de faits est généralement utilisée pour stocker uniquement les clés de substitution des dimensions auxquelles elle est liée et toutes les mesures.

L'utilisation de clés de substitution présente plusieurs avantages. Les clés de substitution nous permettent de suivre l'historique des enregistrements de dimension. Par exemple, imaginons que nous avons une dimension client et que nous souhaitons suivre l’histoire de la résidence de nos clients. Si la clé primaire de la table de dimension est l'ID client, nous ne pouvons avoir qu'un seul enregistrement par client. Toutefois, si nous créons une clé de substitution client, nous pouvons insérer plusieurs enregistrements par client, ce qui nous permet de visualiser facilement l'historique de chaque client.

Des champs de clé de substitution peuvent également fournir des performances supérieures à celles d'une clé métier, ce qui pourrait utiliser un type de données chaîne par exemple. Lorsque nous créons la table de dimension, utilisez un type de données entier. Cela permettra un meilleur stockage des données et de meilleures performances lors de l'écriture de requêtes utilisant des jointures sur les clés de substitution.

5. Utilisez un schéma en étoile lorsque cela est possible.

Un schéma en étoile fait référence à la conception de l'entrepôt de données. La conception s'appelle une «étoile» en raison de la forme souvent prise par le diagramme, comme le montre la capture d'écran ci-dessous.

L'utilisation d'une conception en forme de schéma en étoile offre quelques avantages par rapport à d'autres conceptions de base de données plus normalisées. Tout d'abord, la conception d'un schéma en étoile est très facile à comprendre. Les modèles de données normalisés sont souvent très déroutants, mais une conception dénormalisée telle qu'un schéma en étoile est très simple et nécessite très peu de jointures pour produire une requête significative.

Deuxièmement, en raison de la simplicité du modèle et de l'absence de jointures, un schéma en étoile facilite souvent les requêtes plus performantes qu'un modèle normalisé. L'objectif d'un entrepôt de données est de fournir de gros volumes de données à un utilisateur pour la création de rapports analytiques. Un schéma en étoile simple et optimisé nous aide à atteindre cet objectif.

En outre, une conception de schéma en étoile fonctionne très bien avec SQL Server Analysis Services. SSAS fonctionne mieux avec un entrepôt de données à schéma en étoile en raison de la simplicité des relations entre les objets et de l'optimisation des opérations de lecture à hautes performances. Une conception en flocon de neige peut parfois être nécessaire, mais cela peut poser des problèmes pour SSAS, sur lequel vous pouvez en savoir plus ici.

Ressources

Voici quelques ressources supplémentaires que vous jugerez utiles.

Si vous êtes dans la conception d’entrepôts de données, si vous faites partie d’une équipe de conception d’entrepôts de données ou si vous comptez entreprendre ultérieurement un projet d’entrepôt de données, vous avez sans doute besoin de ces deux livres:

  1. Le Data Warehouse Toolkit de Ralph Kimball et Margy Ross
  2. Star Schema The Complete Reference de Christopher Adamson

Regardez l'enregistrement d'un webinaire que j'ai réalisé sur la conception d'un entrepôt de données.

Retour d'information

J'espère que vous avez trouvé cela utile. Laissez-moi savoir ce que vous pensez! Que pensez-vous de ces conseils? Que devrais-je avoir inclus dans la liste. Laissez-moi un commentaire en bas et laissez-moi savoir.

Restez également attentifs au suivi de cet article de blog pour connaître les cinq conseils de conception d'entrepôt de données restants. Merci d'avoir lu!

Commentaires

Laisser un commentaire

Votre commentaire sera révisé par les administrateurs si besoin.