Extension du modèle de données
Dans ce chapitre, vous apprendrez à étendre le modèle de données créé dans les chapitres précédents. L'extension d'un modèle de données comprend -
- Ajout de tableaux
- Ajout de colonnes calculées dans une table existante
- Création de mesures dans une table existante
Parmi ceux-ci, la création des mesures est cruciale, car elle implique de fournir de nouvelles informations sur les données dans le modèle de données qui permettront à ceux qui utilisent le modèle de données d'éviter les retouches et de gagner du temps lors de l'analyse des données et de la prise de décision.
Comme l'analyse des profits et des pertes implique de travailler avec des périodes et que vous utiliserez les fonctions DAX Time Intelligence, vous avez besoin d'une table de dates dans le modèle de données.
Si vous êtes nouveau dans les tables de dates, parcourez le chapitre - Comprendre les tables de dates.
Vous pouvez étendre le modèle de données comme suit -
Pour créer une relation entre la table de données, c'est-à-dire la table de données financières et la table de dates, vous devez créer une colonne calculée Date dans la table de données financières.
Pour effectuer différents types de calculs, vous devez créer des relations entre la table de données - Données financières et les tables de recherche - Comptes et emplacement géographique.
Vous devez créer diverses mesures qui vous aident à effectuer plusieurs calculs et à effectuer l'analyse requise.
Ces étapes constituent essentiellement les étapes de modélisation des données pour l'analyse des profits et pertes à l'aide du modèle de données. Cependant, il s'agit de la séquence d'étapes pour tout type d'analyse de données que vous souhaitez effectuer avec le modèle de données Power Pivot.
En outre, vous apprendrez à créer les mesures et à les utiliser dans les Power PivotTables dans les chapitres suivants. Cela vous donnera une compréhension suffisante de la modélisation des données avec DAX et de l'analyse des données avec Power PivotTables.
Ajout d'une table de dates au modèle de données
Créez une table de dates pour les périodes couvrant les exercices comme suit:
Créez un tableau avec une seule colonne avec en-tête - Date et dates contiguës allant du 01/07/2011 au 30/06/2018 dans une nouvelle feuille de calcul Excel.
Copiez le tableau d'Excel et collez-le dans la fenêtre Power Pivot. Cela créera une nouvelle table dans le modèle de données Power Pivot.
Nommez la table comme Date.
Assurez-vous que la colonne Date de la table Date est du type de données - Date (DateHeure).
Ensuite, vous devez ajouter les colonnes calculées - Année fiscale, Trimestre fiscal, Mois fiscal et Mois au tableau Date comme suit -
Exercice fiscal
Supposons que la fin de l'exercice est Juin 30 e . Ensuite, une durée de l' année fiscale de 1 er Juillet au 30 e Juin. Par exemple, la période allant du 1er Juillet st 2011 (7/1/2011) au 30 Juin e 2012 (6/30/2012) sera l'exercice 2012.
Dans le tableau Date, supposons que vous souhaitiez représenter la même chose que FY2012.
Vous devez d'abord extraire la partie exercice comptable de la date et l'ajouter à FY.
Pour les dates des mois de juillet 2011 à décembre 2011, l'exercice comptable est 1 + 2011.
Pour les dates des mois de janvier 2012 à juin 2012, l'exercice est 0 + 2012.
Pour généraliser, si le mois de fin d'exercice financier est FYE, procédez comme suit:
Integer Part of ((Month – 1)/FYE) + Year
Ensuite, prenez les 4 caractères les plus à droite pour obtenir l'année financière.
Dans DAX, vous pouvez représenter la même chose que -
DROITE (INT ((MOIS ('Date' [Date]) - 1) / 'Date' [FYE]) + YEAR ('Date' [Date]), 4)
Ajoutez la colonne calculée Année fiscale dans le tableau Date avec la formule DAX -
= "FY" & RIGHT (INT ((MONTH ('Date' [Date]) - 1) / 'Date' [FYE]) + YEAR ('Date' [Date]), 4)
Trimestre fiscal
Si FYE représente le mois de fin d'exercice, le trimestre financier est obtenu comme
Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)
Dans DAX, vous pouvez représenter la même chose que -
INT ((MOD (MOIS ('Date' [Date]) + 'Date' [FYE] -1,12) +3) / 3)
Ajoutez la colonne calculée Trimestre Fiscal dans le tableau Date avec la formule DAX -
= 'Date' [FiscalYear] & "- Q" & FORMAT (INT ((MOD (MONTH ('Date' [Date]) + 'Date' [FYE] -1,12) + 3) / 3), "0" )
Mois fiscal
Si FYE représente la fin de l'exercice, la période du mois comptable est obtenue comme
(Remainder of (Month+FYE-1)/12) + 1
Dans DAX, vous pouvez représenter la même chose que -
MOD (MOIS ('Date' [Date]) + 'Date' [FYE] -1,12) +1
Ajoutez la colonne calculée Fiscal Month dans la table Date avec la formule DAX -
= 'Date' [Exercice] & "- P" & FORMAT (MOD (MONTH ([Date]) + [FYE] -1,12) +1, "00")
Mois
Enfin, ajoutez la colonne calculée Mois qui représente le numéro du mois d'un exercice comme suit -
= FORMAT (MOD (MOIS ([Date]) + [FYE] -1,12) +1, "00") & "-" & FORMAT ([Date], "mmm")
La table Date résultante ressemble à la capture d'écran suivante.
Marquez la table - Date comme date Table avec la colonne - Date comme colonne avec des valeurs uniques comme indiqué dans la capture d'écran suivante.
Ajout de colonnes calculées
Pour créer une relation entre la table des données financières et la table des dates, vous avez besoin d'une colonne de valeurs de date dans la table des données financières.
Ajouter une colonne calculée Date dans le tableau des données financières avec la formule DAX -
= DATEVALUE ('Données financières' [Mois fiscal])
Définition des relations entre les tables dans le modèle de données
Vous avez les tableaux suivants dans le modèle de données -
- Tableau de données - Données financières
- Tables de recherche - Locn Comptes et Géographie
- Tableau des dates - Date
Pour définir les relations entre les tables du modèle de données, procédez comme suit:
Affichez les tableaux dans la vue Diagramme de Power Pivot.
Créez les relations suivantes entre les tables -
Relation entre le tableau des données financières et le tableau des comptes avec la colonne Compte.
Relation entre la table de données financières et la table de localisation géographique avec la colonne Centre de profit.
Relation entre la table de données financières et la table de dates avec la colonne Date.
Masquage des colonnes des outils client
S'il y a des colonnes dans une table de données que vous n'utiliserez pas comme champs dans un tableau croisé dynamique, vous pouvez les masquer dans le modèle de données. Ensuite, ils ne seront pas visibles dans la liste Champs de tableau croisé dynamique.
Dans le tableau des données financières, vous avez 4 colonnes - mois fiscal, date, compte et centre de profit que vous n'utiliserez pas comme champs dans un tableau croisé dynamique. Par conséquent, vous pouvez les masquer afin qu'ils n'apparaissent pas dans la liste Champs de tableau croisé dynamique.
Sélectionnez les colonnes - Mois fiscal, Date, Compte et Centre de profit dans le tableau Données financières.
Cliquez avec le bouton droit de la souris et sélectionnez Masquer dans les outils client dans la liste déroulante.
Création de mesures dans les tableaux
Vous êtes prêt pour la modélisation et l'analyse des données avec DAX à l'aide du modèle de données et des tableaux croisés dynamiques Power.
Dans les chapitres suivants, vous apprendrez à créer des mesures et à les utiliser dans Power PivotTables. Vous allez créer toutes les mesures dans le tableau de données, c'est-à-dire le tableau de données financières.
Vous allez créer des mesures à l'aide de formules DAX dans la table de données - Données financières, que vous pouvez utiliser dans n'importe quel nombre de tableaux croisés dynamiques pour l'analyse des données. Les mesures sont essentiellement les métadonnées. La création de mesures dans la table de données fait partie de la modélisation des données et leur synthèse dans les Power PivotTables fait partie de l'analyse des données.