Planification du magasin de données pour les scénarios et les solutions de planification d’aide à la décision

Article
03/04/2017

S’applique à : SharePoint Server 2010 Enterprise

Dernière rubrique modifiée : 2016-11-30

Dans cet article :

Schéma et organisation
Modèles pour la solution
Création de tables de dimension
Création de tables de hiérarchie
Création de tables de faits

Schéma et organisation

Le magasin de données sera créé à l’aide d’un serveur relationnel SQL Server 2008 R2, qui fera office de point d’enregistrement unique pour les travailleurs de l’information. De la sorte, toutes les données seront stockées de façon très contrôlée dans le magasin de données. Le magasin de données sera utilisé par le serveur SSAS comme source de données centrale pour la totalité des cubes, des dimensions et des hiérarchies. Trois types de tables seront nécessaires pour la prise en charge du modèle de données SSAS et le stockage des dimensions, des hiérarchies et des faits.

Notes

Un cube peut utiliser plusieurs tables de faits. Pour obtenir une configuration de ce type, vous pouvez fractionner le groupe de mesures et également utiliser plusieurs groupes de mesures dans le cube.

Nous allons créer les modèles suivants pour prendre en charge les modèles de données requis pour le processus de planification. Ces modèles détermineront le nombre de tables que comportera notre magasin de données.

Modèles pour la solution

Forecast : ce modèle permettra essentiellement de capturer les entrées de données relatives aux prévisions de chiffre d’affaires et de dépenses opérationnelles pour les périodes prévisionnelles.
Account : la dimension de compte contiendra le plan comptable, qui indique les postes de recettes et de dépenses pour la prévision.
Scenario : la dimension de scénario répartit les données en données réelles et en données prévisionnelles.
Time : la dimension de temps déterminera les périodes fiscales pour la prévision.
Geography : la dimension de géographie permettra de contrôler le processus d’entrée de données par chaque travailleur de l’information. Les travailleurs de l’information de chaque dimension de géographie effectueront les opérations d’entrée de données dans leurs propres devises.
Product : la dimension de produit permet de représenter la liste complète des produits actifs et disponibles. Le chiffre d’affaires sera extrapolé par produit.
HR Budget : ce modèle permettra aux cadres hiérarchiques de budgétiser les effectifs prévus pour l’exercice. Les travailleurs de l’information interagiront avec ce modèle en entrant les heures de travail attendues et le niveau de salaire pour chaque ressource. Une analyse de scénarios sera exécutée pour que soit déterminé le budget nécessaire pour la masse salariale en fonction de différentes hypothèses.
Metric : cette dimension stockera les membres tels que les heures de travail, le niveau de salaire, la rémunération globale, etc.
Geography : lieu d’accueil de la ressource.
Time : budget pour les effectifs à réaliser au niveau de l’année.
Employee : liste des ressources existantes et des espaces réservés aux nouvelles recrues.
Pay Rates : ce modèle permet de définir les salaires de base pour l’année. Les informations relatives aux salaires seront intégrées au modèle du budget des ressources humaines comme données d’hypothèse de base à utiliser dans les calculs.
Time : les salaires sont entrés au niveau de l’année.
Pay Grade : liste des niveaux de salaire qui déterminera le salaire de base.
Exchange Rates : le modèle de taux de change permet de déterminer les taux de conversion de devise à utiliser par mois lors de la conversion de données depuis le modèle prévisionnel vers le modèle de consolidation financière. Dans la mesure où les données sont entrées dans chaque dimension de géographie dans la devise locale, la table de taux de change sera utilisée pour les règles de conversion de devise et les packages de flux de données.
Time : les taux de change sont entrés par mois.
SourceCurrency : devise source d’une conversion.
DestinationCurrency : devise cible d’une conversion.
Financial Consolidation : le modèle de consolidation financière sert à la création de rapports financiers utilisant une seule devise pour toutes les dimensions de géographie.
Account : plan comptable consolidé.
Scenario : contiendra «Actual» (données réelles) et «Forecast» (données prévisionnelles).
Time : le niveau de granularité le plus bas sera le mois.
Geography : toutes les dimensions de géographie comportant des résultats.
Currency Type : il est possible d’afficher les données dans la devise de création de rapports (EUR) ou dans la devise locale, auquel cas la devise est déterminée par la dimension de géographie actuellement sélectionnée dans le filtre.

Une fois assimilés les modèles nécessaires, nous pouvons configurer le magasin de données. En l’occurrence, nous allons utiliser cinq tables de faits, ainsi que les tables de dimension et de hiérarchie appropriées. Ces tables seront organisées selon un schéma en étoile dont le centre sera occupé par les tables de faits et les extrémités des branches par les tables de dimension et de hiérarchie. En définissant les relations à l’aide de clés étrangères entre les tables de faits, les dimensions et la hiérarchie dans le magasin de données, nous pouvons générer rapidement la vue de la source de données dans SSAS lors de la création des cubes et des dimensions.

Création de tables de dimension

Les dimensions sont les blocs de construction de toute base de données multidimensionnelle. Le regroupement d’un ensemble de dimensions forme la base générale d’un cube. Les tables de dimension stockent ensemble des données d’un type particulier. Par exemple, une table de dimension permet de stocker ensemble tous les membres de compte, chaque ligne de la table représentant un membre de compte unique de la dimension. Les tables de dimension peuvent également stocker ensemble toutes les propriétés connexes via des colonnes de table. Par exemple, la dimension Account contient une colonne appelée « AccountType » qui stocke le type de compte particulier du membre de dimension.

MemberId	MemberLabel	MemberName	SortOrder	AccountType	ExpenseType
1	3100	Sales Revenue	100	Income	Non applicable
2	3200	Other Operating Revenue	200	Income	Non applicable
3	8100	Interest Revenues	300	Income	Non applicable

MemberId	MemberLabel	MemberName	SortOrder	Input Currency	Target Currency
1	SEA	Seattle	100	USD	USD
2	OLY	Olympia	200	USD	USD
3	SPK	Spokane	300	USD	USD

Recommandation

Il est recommandé de créer les champs suivants pour une table de dimension :

ID : il est recommandé d’utiliser des clés de dimension de type entier (TinyInt, SmallInt, Int, BigInt) plutôt que de tout autre type pour optimiser les performances. Pour plus d’informations, voir la section relative aux performances. En outre, utilisez le type de données le plus approprié pour le dimensionnement de la dimension.

Étiquette : utilisez un code unique pour l’affichage de la légende ou du nom d’un membre de dimension. Grâce à cette unicité, vous pourrez créer des règles basées sur le cube en utilisant une syntaxe MdxScript lisible plutôt qu’une spécification de membre avec une notation de valeur de clé, telle que « &[clé] ».

Nom : en règle générale, les utilisateurs finaux souhaitent que les membres d’une dimension apparaissent sous un nom convivial plutôt que sous un code d’étiquette. Par exemple, dans notre solution, nous utilisons des codes de compte dans les étiquettes, ce qui est pertinent pour la création de règles de calcul, mais pas pour l’affichage dans un tableau croisé dynamique. La création de cette propriété vous permet de mettre à jour facilement les noms d’affichage sans affecter la logique sous-jacente des définitions des règles.

Tri : il est recommandé de créer une colonne de tri qui permette de déterminer le tri des membres dans la dimension.

En règle générale, les tables de dimension des scénarios de planification ne comportent pas plus de 200 000 membres. Si vous utilisez des dimensions qui ont atteint une taille très volumineuse, il est recommandé de réduire la dimension. Toutes les données associées à des membres de dimension tronqués peuvent être agrégées et déplacées vers d’autres membres de dimension. En principe, plus les dimensions sont petites, meilleures sont les performances globales des cubes de planification.

Notes

Les colonnes de dimension et les propriétés des membres sont étroitement liées.

Création de tables de hiérarchie

Des tables de hiérarchie sont nécessaires lorsque vous utilisez la fonctionnalité de hiérarchie parent/enfant dans SSAS. La hiérarchie parent/enfant doit utiliser trois colonnes : la colonne clé (membre de la dimension de la hiérarchie), la colonne clé parente (autre membre de la même dimension) et la colonne tri pour le tri des membres dans un niveau de la hiérarchie. La plupart des hiérarchies parent/enfant sont prises en charge par ces 3 colonnes, sauf si une agrégation personnalisée des membres est nécessaire. Par exemple, dans le cas d’un plan comptable, il sera nécessaire de définir une agrégation personnalisée. Les agrégations de comptes sont déterminées par le type de compte de chaque membre de compte et leur membre de compte parent respectif. Pour prendre en charge les hiérarchies nécessitant une agrégation personnalisée, nous devons créer une quatrième colonne. Cette colonne, Unary Operator (opérateur unaire), contiendra les valeurs +, - et ~ , + signifiant agrégation au parent, - soustraction du parent et ~ agrégation au parent ignorée.

ID	Parent Id	Sort Order	Unary Operator	Label	Name
1	102	100	+	3100	Sales Revenue
2	102	200	+	3200	Other Operating Revenue
3	103	300	+	8100	Interest Revenues
4	103	400	+	9100	Gain on Sale of Assets

ID	Parent Id	Sort Order	Label	Name
1	4	500	SEA	Seattle
2	4	700	OLY	Olympia
3	4	600	SPK	Spokane

Les hiérarchies basées sur un niveau sont créées en fonction des colonnes définies sur une dimension. Les colonnes d’une table relationnelle peuvent construire des hiérarchies d’attribut dans SSAS. La définition de relations entre les hiérarchies d’attribut vous permettra de créer des hiérarchies basées sur un niveau efficaces. En attendant, nous allons nous contenter d’inclure toutes les propriétés liées d’une dimension sous forme de colonnes dans la table de dimension au moment de traiter les hiérarchies basées sur un niveau dans SSAS.

Pour plus d’informations, voir Hiérarchies et niveaux.

Création de tables de faits

Les tables de faits contiennent toutes les données numériques d’un cube. Le nombre de colonnes dans une table de faits dépend du nombre de dimensions associées au cube. Par exemple, le cube Forecast possède 7 colonnes, dont six représentent chaque dimension liée au cube des prévisions et une comporte la valeur numérique. Cette dernière est appelée « mesure ». Dans notre solution, nous utilisons une seule mesure pour la table de faits.

Chaque colonne autre que la colonne de mesure est liée à une dimension via sa clé. Dans l’exemple suivant, le modèle HR Budget possède cinq dimensions liées à la table de faits, et chaque ligne de la table de faits représente un enregistrement de faits spécifique. Il est conseillé d’éviter les doublons de valeurs dans les enregistrements de faits sur les clés de dimension. Ce serait par exemple le cas si la même valeur apparaissait dans toutes les clés de dimension sur plusieurs lignes de faits. Si une situation de ce type se présente, combinez la valeur dans une ligne unique.

Rowld	Metric ID	Geography ID	EmployeeID	TimeID	Value
1	2	15	1010	20100000	2000
2	2	15	1009	20100000	2000
3	2	15	1008	20100000	2000

Rowld	Account ID	TimeID	ScenarioID	Geography ID	ProductID	VersionID	Value
1151	1	20100200	1	1	232	1	1391
1153	1	20100400	2	1	232	1	1124
1155	1	20100600	2	1	232	1	1322