Le traitement analytique analytique (OLAP) est une technologie utilisée pour organiser les bases de données des grandes entreprises et prendre en charge l'intelligence d'affaires. Les bases de données OLAP sont divisées en un ou plusieurs cubes et chaque cube est organisé par l'administrateur du cube pour correspondre à la manière dont les données sont extraites et analysées afin de faciliter la création et l'utilisation des rapports de tableau croisé dynamique et de graphique croisé dynamique dont vous avez besoin.
Dans cet article
Qu'est-ce que la Business Intelligence?
Un analyste commercial souhaite souvent avoir une vue d'ensemble d'une entreprise afin de visualiser des tendances plus larges basées sur des données agrégées, ainsi que de visualiser les tendances ventilées en un nombre quelconque de variables. La Business Intelligence est le processus d'extraction de données d'une base de données OLAP et d'analyse de ces données pour fournir des informations qui peuvent être utilisées pour prendre des décisions commerciales éclairées et agir. Par exemple, avec OLAP et Business Intelligence, vous pouvez répondre aux questions suivantes sur les données d'entreprise.
Comment les ventes totales de tous les produits en 2007 se comparent-elles aux ventes de 2006?
Comment cela se compare-t-il à la date et à l'heure d'une période favorable au cours des cinq dernières années?
Combien d'argent les clients ont-ils dépensé pour 35 au cours de l'année écoulée et comment ce comportement a-t-il évolué au fil du temps?
Combien de produits ont été vendus dans deux pays / régions spécifiques ce mois-ci par rapport au même mois l'année dernière?
Pour chaque tranche d'âge des clients Quelle est la répartition de la rentabilité (pourcentage de marge et total) par catégorie de produits?
Recherchez les meilleurs et les plus bas vendeurs, distributeurs, fournisseurs, clients, partenaires et clients.
Qu'est-ce que le traitement analytique en ligne (OLAP)?
Les bases de données OLAP (Online Analytical Processing) simplifient les requêtes décisionnelles. OLAP est une technologie de base de données optimisée pour les requêtes et les rapports plutôt que pour le traitement des transactions. La source de données pour OLAP est constituée de bases de données OLTP (Online Transaction Processing), qui sont généralement stockées dans des entrepôts de données. Les données OLAP sont extraites de ces données historiques et combinées en structures permettant une analyse complexe. Les données OLAP sont également organisées de manière hiérarchique et stockées dans des cubes plutôt que dans des tables. C'est une technologie complexe qui utilise des structures multidimensionnelles pour fournir accès rapide aux données pour analyse. Dans cette organisation, pour un rapport de tableau croisé dynamique ou de graphique croisé dynamique, vous pouvez facilement afficher des données récapitulatives de haut niveau, telles que les ventes totales dans un pays ou une région, et afficher des informations sur les sites où les ventes sont particulièrement élevées ou faibles.
Les bases de données OLAP sont conçues pour accélérer le chargement des données. Étant donné que le serveur OLAP, et non Microsoft Office Excel, calcule les valeurs résumées, moins de données doivent être envoyées à Excel lorsque vous créez ou modifiez un rapport. Cette approche vous permet de travailler avec une plus grande quantité de données brutes que si les données étaient organisées dans une base de données traditionnelle, où Excel récupère tous les enregistrements individuels et calcule les valeurs résumées.
Les bases de données OLAP contiennent deux principaux types de données: des mesures, qui sont des données numériques, des quantités et des moyennes, qui sont utilisées pour prendre des décisions commerciales éclairées, et des dimensions, qui sont des catégories utilisées pour organiser ces mesures. Les bases de données OLAP vous aident à organiser vos données avec de nombreux niveaux de détail, en utilisant les mêmes catégories que vous connaissez pour l'analyse des données.
Les sections suivantes décrivent chaque composant en détail ci-dessous.
Cubique Une structure de données qui regroupe les mesures par niveau et hiérarchie pour chaque dimension que vous souhaitez analyser. Les cubes combinent plusieurs dimensions telles que le temps, la géographie et les gammes de produits avec des totaux tels que les ventes et l'inventaire. Les cubes ne sont pas des «cubes» au sens mathématique strict, car ils n'ont pas nécessairement les mêmes côtés. Cependant, ils représentent la métaphore d'Apt pour un concept complexe.
Des mesures Ensemble de valeurs dans un cube basé sur une colonne de la table de faits du cube, qui est généralement une valeur numérique. Les mesures sont des valeurs centrales dans un cube qui sont prétraitées, traitées et analysées. Les exemples les plus courants sont les ventes, les revenus, les revenus et les coûts.
Membre Un élément dans une hiérarchie qui représente une ou plusieurs occurrences de données. Un élément peut être unique ou non unique. Par exemple, 2007 et 2008 représentent des membres uniques au niveau de l'année de la dimension de temps, tandis que janvier représente des membres non uniques au niveau du mois, car il y a plus d'un janvier dans la dimension de temps, car il contient des données pour plus d'un an.
Membre calculé Membre de dimension dont la valeur est calculée au moment de l'exécution à l'aide d'une expression. Les valeurs de membre calculées peuvent être dérivées d'autres valeurs de membre. Par exemple, un «bénéfice» d'article calculé peut être déterminé en soustrayant la valeur de l'article ainsi que le coût de la valeur de l'article, les ventes.
dimension Ensemble d'une ou plusieurs hiérarchies de niveau de cube ordonnées que l'utilisateur comprend et utilise comme base pour l'analyse des données. Par exemple, une dimension géographique peut inclure les niveaux de pays / région, état / district et ville. En outre, la dimension de temps peut inclure une hiérarchie avec les niveaux année, trimestre, mois et jour. Dans un rapport de tableau croisé dynamique ou un rapport de graphique croisé dynamique, chaque hiérarchie devient un ensemble de champs que vous pouvez développer et réduire pour afficher des niveaux inférieurs ou supérieurs.
Hiérarchie Une arborescence logique qui organise les membres d'une dimension de telle sorte que chaque membre ait un parent et zéro ou plusieurs enfants. Un enfant est membre d'un groupe antérieur dans la hiérarchie qui est directement lié au membre actuel. Par exemple, dans une hiérarchie de temps contenant les niveaux trimestre, mois et jour, janvier est un enfant de Qtr1. Un parent est un membre à un niveau inférieur de la hiérarchie qui est directement lié au membre actuel. La valeur parent est généralement la consolidation des valeurs de tous les enfants. Par exemple, dans une hiérarchie de temps contenant les niveaux «trimestre», «mois» et «jour», Qtr1 est le parent de janvier.
Niveau Dans une hiérarchie, les données peuvent être organisées en niveaux de granularité inférieurs et supérieurs, tels que les années, les trimestres, les mois et les niveaux quotidiens dans la hiérarchie temporelle.
Fonctions OLAP dans Excel
Récupération des données OLAP Vous pouvez vous connecter aux sources de données OLAP de la même manière que vous pouvez vous connecter à d'autres sources de données externes. Vous pouvez travailler avec des bases de données créées avec Microsoft SQL Server OLAP 7.0, Microsoft SQL Server Analysis Services 2000 et Microsoft SQL Server Analysis Services 2005 et les produits serveur Microsoft OLAP. Excel peut également fonctionner avec des produits OLAP tiers compatibles avec OLE-DB pour OLAP.
Les données OLAP peuvent uniquement être affichées sous forme de rapport de tableau croisé dynamique ou de rapport de graphique croisé dynamique, ou sous forme de fonction de feuille de calcul convertie à partir d'un rapport de tableau croisé dynamique, et non en tant que plage de données externe. Vous pouvez enregistrer des rapports de tableau croisé dynamique OLAP et de graphique croisé dynamique dans des modèles de rapport, et vous pouvez créer des fichiers Office Data Connection (ODC) pour vous connecter aux bases de données OLAP pour les requêtes OLAP. Lorsque vous ouvrez le fichier .odc dans Excel, un rapport de tableau croisé dynamique vide s'affiche prêt à être placé.
Création de fichiers de cube pour une utilisation hors ligne Vous pouvez créer un fichier de cube autonome (. Cub) avec un sous-ensemble de données d'une base de données de serveur OLAP. Les fichiers de cube hors ligne sont utilisés pour travailler avec des données OLAP lorsque vous n'êtes pas connecté à un réseau. Avec un cube, vous pouvez travailler avec plus de données dans un rapport de tableau croisé dynamique ou un rapport de graphique croisé dynamique que vous ne le feriez autrement, et accélérer la récupération des données. Vous ne pouvez créer des fichiers Cube que si vous utilisez un fournisseur OLAP tel que Microsoft SQL Analysis Services 2005 qui prend en charge cette fonctionnalité.
Actions du serveur Une action serveur est une fonction facultative qu'un administrateur de cube OLAP peut définir sur un serveur qui utilise un élément de cube ou une mesure comme paramètre dans une requête pour récupérer des informations dans le cube ou pour lancer une autre application, telle qu'un navigateur. Excel prend en charge les URL, les rapports, les ensembles de lignes, l'exploration vers le bas et l'exploration vers le bas sur les actions de serveur détaillées, mais ne prend pas en charge son propre opérateur et ensemble de données natifs.
KPI Un KPI est une mesure calculée spéciale, définie sur le serveur, qui suit les «KPI», y compris l'état (la valeur actuelle est un nombre spécifique). et tendance (valeurs au fil du temps). Lorsqu'elles sont affichées, le serveur peut envoyer des icônes appropriées, similaires à la nouvelle icône Excel, pour s'aligner au-dessus ou en dessous des niveaux de statut (par exemple, pour une icône d'arrêt), et faire défiler la valeur vers le haut ou vers le bas (par exemple, une icône de flèche directionnelle).
Formatage du serveur Les administrateurs de cube peuvent créer des mesures et des membres calculés à l'aide de la mise en forme des couleurs, de la mise en forme des polices et des règles de mise en forme conditionnelle, qui peuvent être affectées en tant que règle métier standard de l'entreprise. Par exemple, le format côté serveur pour les revenus peut être un format de devise numérique, la couleur de la cellule est verte si la valeur est supérieure ou égale à 30 000, et rouge si la valeur est inférieure à 30 000 et le style de police est en gras si la valeur est inférieure à 30 000 et si la valeur est positive. - habituel. est supérieur ou égal à 30 000. Information additionnelle peut être trouvé.
Langue de l'interface Office L'administrateur du cube peut définir des traductions pour les données et les erreurs sur le serveur pour que les utilisateurs puissent afficher les informations du tableau croisé dynamique dans une autre langue. Cette fonction est définie comme une propriété de connexion de fichier, et paramètres régionaux l'ordinateur et le pays de l'utilisateur doivent correspondre à la langue de l'interface.
Composants logiciels requis pour accéder aux sources de données OLAP
Fournisseur OLAP Pour configurer des sources de données OLAP pour Excel, vous avez besoin de l'un des fournisseurs OLAP suivants.
Fournisseur OLAP Microsoft Excel comprend un pilote de source de données et un client logiciel pour accéder aux bases de données créées avec Microsoft SQL Server olap 7.0, Microsoft SQL Server olap 2000 (8.0) et Microsoft SQL Server Analysis services 2005 (9.0).
Fournisseurs OLAP tiers Pour les autres produits OLAP, des pilotes et des logiciels clients supplémentaires doivent être installés. Pour utiliser les capacités de données OLAP d'Excel, le produit tiers doit être conforme à la norme OLE-DB pour OLAP et être compatible avec Microsoft Office. Pour plus d'informations sur l'installation et l'utilisation d'un fournisseur OLAP tiers, contactez votre administrateur système ou votre fournisseur de produit OLAP.
Bases de données serveur et fichiers de cube Le logiciel client Excel OLAP prend en charge les connexions à deux types de bases de données OLAP. Si la base de données sur le serveur OLAP est en ligne, vous pouvez en extraire des données brutes directement. Si vous disposez d'un fichier de cube hors connexion contenant des données OLAP ou un fichier de définition de cube, vous pouvez vous connecter à ce fichier et en extraire des données brutes.
Source d'information La source de données permet d'accéder à toutes les données d'une base de données OLAP ou d'un fichier de cube hors connexion. Une fois que vous avez créé une source de données OLAP, vous pouvez baser des rapports sur celle-ci et renvoyer des données OLAP vers Excel sous forme de rapport de tableau croisé dynamique ou de graphique croisé dynamique et de fonction de feuille convertie à partir d'un rapport de tableau croisé dynamique.
Requête Microsoft Avec Query, vous pouvez récupérer des données à partir d'une base de données externe telle que Microsoft SQL ou Microsoft Access. Vous n'avez pas besoin d'utiliser une requête pour récupérer des données à partir d'un tableau croisé dynamique OLAP associé à un fichier de cube. Information additionnelle .
Différences entre les fonctionnalités OLAP et non OLAP des données source
Si vous travaillez avec des rapports de tableau croisé dynamique et des graphiques croisés dynamiques à partir de données source OLAP et d'autres types de données source, vous verrez certaines différences de fonctionnalités.
Récupération des données Le serveur OLAP renvoie de nouvelles données à Excel chaque fois que la mise en page du rapport change. Avec d'autres types de sources de données externes, vous interrogez toutes les données source en une seule fois, ou vous pouvez spécifier des paramètres pour la requête uniquement lors de l'affichage de différents éléments des champs de filtre de rapport. De plus, vous avez plusieurs autres options pour mettre à jour le rapport.
Dans les rapports basés sur des données brutes OLAP, les options de champ de filtre de rapport ne sont pas disponibles, la requête d'arrière-plan n'est pas disponible et l'option d'optimisation de la mémoire n'est pas disponible.
Remarque: L'option d'optimisation de la mémoire n'est également pas disponible pour les sources de données OLEDB et les rapports de tableau croisé dynamique basés sur une plage de cellules.
Types de champs Données source OLAP. Les champs de dimension ne peuvent être utilisés que sous forme de lignes (lignes), de colonnes (catégorie) ou de champs de page. Les champs de mesure ne peuvent être utilisés que comme champs de valeur. Pour les autres types de données source, tous les champs peuvent être utilisés n'importe où dans le rapport.
Accès aux données détaillées Pour les données source OLAP, le serveur détermine les niveaux de détail disponibles et calcule les valeurs de synthèse, de sorte que les enregistrements de détail qui composent les valeurs de synthèse peuvent ne pas être disponibles. Cependant, le serveur peut fournir des champs de propriété que vous pouvez afficher. D'autres types de données source n'ont pas de champs de propriété, mais vous pouvez afficher des informations de base pour les champs de données et les valeurs de membre, et afficher les membres non-données.
Les champs de filtre de rapport OLAP peuvent ne pas avoir tout éléments et la commande Afficher les pages de filtre de rapport indisponible.
Ordre de tri initial Pour les données source OLAP, les éléments sont d'abord affichés dans l'ordre dans lequel ils sont renvoyés par le serveur OLAP. Vous pouvez trier ou modifier manuellement l'ordre des éléments. Pour les autres types de données source, les nouveaux éléments de rapport sont d'abord triés par nom d'élément dans l'ordre croissant.
Nimi Les serveurs OLAP fournissent des valeurs récapitulatives directement au rapport, vous ne pouvez donc pas modifier les fonctions récapitulatives des champs de valeur. Pour les autres types de données source, vous pouvez modifier la fonction d'agrégation du champ de valeur et utiliser plusieurs fonctions de synthèse pour le même champ de valeur. Vous ne pouvez pas créer de champs calculés et de membres calculés dans des rapports avec des données source OLAP.
Sous-totaux Dans les rapports contenant des données brutes OLAP, vous ne pouvez pas modifier la fonction de synthèse des sous-totaux. Avec d'autres types de données source, vous pouvez modifier les fonctions de sous-total pour les sous-totaux et afficher ou masquer les sous-totaux pour tous les champs de ligne et de colonne.
Pour les données source OLAP, vous pouvez inclure ou exclure des éléments masqués lors du calcul des sous-totaux et des totaux généraux. Pour les autres types de données source, vous pouvez inclure des éléments masqués dans les champs de filtre de rapport dans les sous-totaux, mais les éléments masqués dans d'autres champs seront exclus par défaut.
Travailler avec un cube OLAP dans MS Excel
1. Obtenez l'autorisation d'accéder au cube OLAP SQL Server Analysis Services (SSAS)
2. MS Excel 2016/2013/2010 doit être installé sur votre ordinateur (MS Excel 2007 est également possible, mais il n'est pas pratique d'y travailler et la fonctionnalité de MS Excel 2003 est assez médiocre)
3. Ouvrez MS Excel, lancez l'assistant de configuration de la connexion au service analytique:
3.1 Spécifiez le nom ou l'adresse IP du serveur OLAP actuel (vous devez parfois spécifier le numéro de port ouvert, par exemple 192.25.25.102:80); l'authentification de domaine est utilisée:
3.2 Sélection d'une base de données multidimensionnelle et d'un cube analytique (si vous disposez des droits d'accès au cube):
3.3 Les paramètres de connexion au service d'analyse seront enregistrés dans un fichier odc sur votre ordinateur:
3.4 Sélectionnez le type de rapport (tableau croisé dynamique / graphique) et indiquez l'emplacement de son placement:
Si une connexion a déjà été créée dans le classeur Excel, elle peut être réutilisée: menu principal "Données" -\u003e "Connexions existantes" -\u003e sélectionner une connexion dans ce classeur -\u003e insérer le tableau croisé dynamique dans la cellule spécifiée.
4. Vous vous êtes connecté avec succès au cube, vous pouvez démarrer l'analyse interactive des données:
Avant de commencer l'analyse interactive des données, il est nécessaire de déterminer lequel des champs participera à la formation des lignes, colonnes et filtres (pages) du tableau croisé dynamique. En général, le tableau croisé dynamique est tridimensionnel, et nous pouvons supposer que la troisième dimension est située perpendiculairement à l'écran, et nous observons des sections parallèles au plan de l'écran et déterminées par quelle «page» est sélectionnée pour l'affichage. Le filtrage peut être effectué en faisant glisser et en déposant les attributs de dimension correspondants dans la zone de filtre du rapport. Le filtrage limite l'espace de cube, réduisant la charge sur le serveur OLAP, donc il est préférable d'installer d'abord les filtres nécessaires... Ensuite, vous placez les attributs de dimension dans les zones de ligne, de colonne et de mesure de la zone de données du tableau croisé dynamique.
Chaque fois que le tableau croisé dynamique change, une instruction MDX est automatiquement envoyée au serveur OLAP et renvoie les données. Plus la quantité de données traitées, les indicateurs calculés sont importants et complexes, plus le temps d'exécution de la requête est long. Vous pouvez annuler l'exécution de la demande en appuyant sur Échapper... Les dernières opérations effectuées peuvent être annulées (Ctrl + Z) ou refaites (Ctrl + Y).
En règle générale, pour les combinaisons d'attributs de dimension les plus couramment utilisées, le cube stocke des données agrégées précalculées, de sorte que le temps de réponse pour ces requêtes est de plusieurs secondes. Cependant, il est impossible de calculer toutes les combinaisons d'agrégations possibles, car cela peut prendre beaucoup de temps et d'espace de stockage. L'exécution de requêtes massives sur des données au niveau de la granularité peut nécessiter des ressources informatiques serveur importantes, de sorte que leur temps d'exécution peut être long. Après avoir lu les données des lecteurs de disque, le serveur les place dans le cache RAM, ce qui permet d'exécuter instantanément de telles demandes ultérieures, car les données seront extraites du cache.
Si vous pensez que votre demande sera utilisée fréquemment et que le temps d'exécution n'est pas satisfaisant, vous pouvez contacter le service de support au développement analytique pour optimiser l'exécution de la demande.
Après avoir placé la hiérarchie dans la zone des lignes / colonnes, il est possible de masquer des niveaux individuels:
Attributs clés (moins souvent - pour les attributs plus élevés dans la hiérarchie) les dimensions peuvent avoir des propriétés - caractéristiques descriptives qui peuvent être affichées à la fois dans des info-bulles et sous la forme de champs:
Si vous devez afficher plusieurs propriétés de champ à la fois, vous pouvez utiliser la liste de dialogue correspondante:
Kits définis par l'utilisateur
Excel 2010 offre la possibilité de créer de manière interactive vos propres ensembles (définis par l'utilisateur) à partir des membres de dimension:
Contrairement aux ensembles créés et stockés de manière centralisée côté cube, les ensembles personnalisés sont stockés localement dans le classeur Excel et peuvent être utilisés ultérieurement:
Les utilisateurs avancés peuvent créer des ensembles à l'aide de constructions MDX:
Définition des propriétés du tableau croisé dynamique
Grâce à l'élément "Paramètres du tableau croisé dynamique ..." du menu contextuel (clic droit dans le tableau croisé dynamique), vous pouvez personnaliser le tableau croisé dynamique, par exemple:
- onglet "Sortie", paramètre "Disposition classique du tableau croisé dynamique" - le tableau croisé dynamique devient interactif, vous pouvez faire glisser les champs (Drag & Drop);
- l'onglet "Sortie", le paramètre "Afficher les éléments sans données en lignes" - les lignes vides seront affichées dans le tableau croisé dynamique qui ne contiennent aucune valeur d'indicateur pour les éléments de dimension correspondants;
- Onglet "Disposition et format", paramètre "Conserver le formatage des cellules lors de la mise à jour" - dans le tableau croisé dynamique, vous pouvez remplacer et enregistrer le format des cellules lors de la mise à jour des données;
Créer des graphiques croisés dynamiques
Pour un tableau croisé dynamique OLAP existant, vous pouvez créer un graphique croisé dynamique - camembert, barre, barre, graphique, nuage de points et autres types de graphiques:
Dans ce cas, le tableau croisé dynamique sera synchronisé avec le tableau croisé dynamique - lorsque vous modifiez la composition des indicateurs, des filtres, des dimensions dans le tableau croisé dynamique, le tableau croisé dynamique est également mis à jour.
Création de tableaux de bord
Sélectionnez le tableau croisé dynamique d'origine, copiez-le dans le presse-papiers (Ctrl + C) et collez sa copie (Ctrl + V), dans lequel nous modifierons la composition des indicateurs:
Pour gérer plusieurs tableaux croisés dynamiques en même temps, insérez une tranche (nouvelle fonctionnalité disponible depuis MS Excel 2010). Connectons notre Slicer aux tableaux croisés dynamiques - faites un clic droit dans la tranche, sélectionnez "Connexions du tableau croisé dynamique ..." dans le menu contextuel. Il est à noter qu'il peut y avoir plusieurs panneaux croisés dynamiques qui peuvent servir des tableaux croisés dynamiques sur différentes feuilles en même temps, ce qui permet la création de tableaux de bord coordonnés (Dashboards).
Les panneaux Slicer peuvent être personnalisés: vous devez sélectionner le panneau, puis voir les éléments "Taille et propriétés ...", "Paramètres de tranche", "Attribuer une macro" dans le menu contextuel, activé par le clic droit de la souris, ou l'élément "Options" du menu principal. Ainsi, il est possible de définir le nombre de colonnes pour les éléments (boutons) de la tranche, les tailles des boutons de tranche et du panneau, de définir la gamme de couleurs et le style de conception de la tranche à partir de l'ensemble disponible (ou de créer votre propre style), de définir votre propre titre de panneau, d'attribuer une macro de programme à travers laquelle fonctionnalité du panneau.
Exécution d'une requête MDX à partir d'Excel
- Tout d'abord, vous devez effectuer l'opération PERÇAGE sur une touche, c'est-à-dire descendez aux données détaillées (les données détaillées sont affichées sur une feuille séparée), et ouvrez la liste des connexions;
- Ouvrez les propriétés de connexion, allez dans l'onglet "Définition";
- Sélectionnez le type de commande par défaut et, dans le champ de texte de la commande, placez le MDX demande;
- Lorsque vous appuyez sur le bouton, une fois que la syntaxe de la requête est correcte et que les droits d'accès appropriés sont disponibles, la requête sera exécutée sur le serveur et le résultat sera présenté dans la feuille actuelle sous la forme d'un tableau plat normal.
Vous pouvez afficher le texte de la requête MDX générée par Excel en installant un module complémentaire gratuit qui fournit également d'autres fonctionnalités supplémentaires.
Traduction dans d'autres langues
Le cube analytique prend en charge la localisation en russe et en anglais (si nécessaire, la localisation dans d'autres langues est possible). Les traductions s'appliquent aux noms des dimensions, des hiérarchies, des attributs, des dossiers, des mesures, ainsi qu'aux éléments des hiérarchies individuelles s'il existe des traductions pour eux du côté des systèmes comptables / du stockage des données. Pour changer la langue, vous devez ouvrir les propriétés de connexion et ajouter l'option suivante à la chaîne de connexion:
Propriétés étendues \u003d "Locale \u003d 1033"
où 1033 est la localisation en anglais
1049 - localisation en russe
Extensions Excel supplémentaires pour Microsoft OLAP
Les possibilités de travailler avec les cubes Microsoft OLAP augmenteront si vous utilisez des extensions supplémentaires, par exemple, les extensions de tableau croisé dynamique OLAP, grâce auxquelles vous pouvez utiliser une recherche rapide par dimension:
Dans un tableau croisé dynamique standard, les données source sont stockées sur le disque dur local. Ainsi, vous pouvez toujours les gérer et les réorganiser, même sans avoir accès au réseau. Mais cela ne s'applique en aucun cas aux tableaux croisés dynamiques OLAP. Dans les tableaux croisés dynamiques OLAP, le cache n'est jamais stocké sur le disque dur local. Par conséquent, immédiatement après la déconnexion du réseau local, votre tableau croisé dynamique deviendra inefficace. Vous ne pouvez y déplacer aucun champ.
Si vous devez encore analyser les données OLAP après vous être déconnecté du réseau, créez un cube de données hors ligne. Un cube de données hors connexion est un fichier distinct qui est un cache de tableau croisé dynamique et stocke les données OLAP qui sont affichées après la déconnexion du réseau local. Les données OLAP copiées dans le tableau croisé dynamique peuvent être imprimées; le site Web http://everest.ua est décrit en détail.
Pour créer un cube de données hors connexion, créez d'abord un tableau croisé dynamique OLAP. Placez le curseur dans le tableau croisé dynamique et cliquez sur le bouton Outils OLAP dans l'onglet contextuel Outils, qui fait partie du groupe d'onglets contextuels Outils de tableau croisé dynamique. Sélectionnez la commande OLAP hors ligne (Figure 9.8).
Figure: 9.8. Créer un cube de données hors ligne
La boîte de dialogue Paramètres du cube de données OLAP hors ligne s'affiche. Cliquez sur le bouton Créer un fichier de données hors ligne. Vous avez lancé l'assistant Nouveau fichier de cube de données. Cliquez sur le bouton Suivant pour continuer la procédure.
Tout d'abord, vous devez spécifier les dimensions et les niveaux qui seront inclus dans le cube de données. Dans la boîte de dialogue, vous devez sélectionner les données à importer de la base de données OLAP. L'idée est de ne spécifier que les dimensions qui seront nécessaires après la déconnexion de l'ordinateur du réseau local. Plus vous spécifiez de dimensions, plus le cube de données hors connexion sera grand.
Cliquez sur le bouton Suivant pour passer à la boîte de dialogue suivante de l'assistant. Il vous permet de spécifier des membres ou des éléments de données qui ne seront pas inclus dans le cube. En particulier, vous n'avez pas besoin de la mesure Internet Sales-Extended Amount, la case à cocher correspondante sera donc désactivée dans la liste. Une case à cocher désactivée indique que l'élément spécifié ne sera pas importé et occupera de l'espace supplémentaire sur le disque dur local.
Dans la dernière étape, indiquez l'emplacement et le nom du cube de données. Dans notre cas, le fichier cube s'appellera MyOfflineCube.cub et se trouvera dans le dossier Work.
Les fichiers de cube de données ont l'extension .lionceau
Après un certain temps, Excel enregistrera le cube de données hors ligne dans le dossier spécifié. Pour le tester, double-cliquez sur le fichier, ce qui générera automatiquement un classeur Excel contenant un tableau croisé dynamique associé au cube de données sélectionné. Une fois créé, vous pouvez distribuer le cube de données hors ligne à tous les utilisateurs intéressés qui travaillent en mode LAN hors ligne.
Une fois connecté à votre réseau local, vous pouvez ouvrir le fichier de cube de données hors ligne et le mettre à jour ainsi que la table de données correspondante. Le principe principal est qu'un cube de données autonome est utilisé uniquement pour fonctionner lorsque le réseau local est déconnecté, mais il doit être mis à jour une fois la connexion rétablie. Tenter de mettre à jour le cube de données hors ligne après avoir déconnecté la connexion entraînera un échec.
Travailler avec des fichiers de cube hors ligne
un fichier de cube hors ligne (. Cub) stocke les données sous la forme d'un cube OLAP (Online Analytical Processing). Ces données peuvent représenter une partie d'une base de données OLAP sur un serveur OLAP, ou elles peuvent être générées indépendamment d'une base de données OLAP. Utilisez un fichier de cube hors connexion pour continuer à travailler avec les rapports de tableau croisé dynamique et de graphique croisé dynamique lorsque le serveur n'est pas disponible ou lorsque vous êtes hors connexion.
Note de sécurité: Soyez prudent lorsque vous utilisez ou distribuez un fichier de cube hors ligne contenant des données sensibles ou personnelles. Au lieu d'un fichier de cube, nous vous recommandons d'enregistrer les données dans un classeur afin de pouvoir contrôler l'accès aux données à l'aide de Rights Management. Pour plus d'informations, consultez Gestion des droits relatifs à l'information dans Office.
Lorsque vous travaillez avec un rapport de tableau croisé dynamique ou de graphique croisé dynamique basé sur les données source du serveur OLAP, vous pouvez utiliser l'Assistant Cube hors ligne pour copier les données source dans fichier séparé un cube hors ligne sur un ordinateur. Ces fichiers hors connexion nécessitent un fournisseur de données OLAP pour prendre en charge cette fonctionnalité, comme MSOLAP de Microsoft SQL Server Analysis Services installé sur l'ordinateur.
Remarque: La création et l'utilisation de fichiers de cube hors connexion à partir de Microsoft SQL Server Analysis Services sont soumises à des conditions et à une licence installations Microsoft Serveur SQL. Consultez les informations relatives aux licences de version de SQL Server.
Utilisation de l'assistant de cube autonome
Pour créer un fichier de cube hors connexion, vous pouvez sélectionner un sous-ensemble de données dans la base de données OLAP à l'aide de l'assistant de cube hors connexion, puis enregistrer ce sous-ensemble. Le rapport n'a pas besoin d'inclure tous les champs inclus dans le fichier, ni de sélectionner l'un d'entre eux et les champs de données disponibles dans la base de données OLAP. Pour conserver le fichier au minimum, vous pouvez inclure uniquement les données que vous souhaitez voir apparaître dans le rapport. Vous pouvez omettre toutes les dimensions et, pour la plupart des types de dimensions, vous pouvez également exclure les informations de niveau inférieur et les éléments de niveau supérieur que vous ne souhaitez pas afficher. Pour tous les éléments que vous incluez, les champs de propriété disponibles dans la base de données pour ces éléments sont également enregistrés dans un fichier hors connexion.
Transférer des données vers mode hors-ligne et leur connexion inverse
Pour ce faire, vous devez d'abord créer un rapport de tableau croisé dynamique ou de graphique croisé dynamique basé sur la base de données du serveur, puis créer un fichier de cube hors connexion à partir du rapport. Après cela, vous pouvez basculer le rapport entre la base de données du serveur et le fichier hors connexion à tout moment. Par exemple, si vous utilisez un ordinateur portable pour vos déplacements à la maison et en vidéo, reconnectez l'ordinateur au réseau.
Voici les étapes de base à suivre pour travail autonome avec les données, puis remettez les données en ligne.
Créez ou ouvrez un rapport de tableau croisé dynamique ou de graphique croisé dynamique basé sur les données OLAP auxquelles vous souhaitez accéder hors connexion.
Créez un fichier de cube hors connexion sur votre ordinateur. Dans la section Créer un fichier de cube hors ligne à partir d'une base de données de serveur OLAP (plus loin dans cet article).
Déconnectez-vous du réseau et travaillez avec le fichier de cube hors ligne.
Connectez-vous et reconnectez le fichier cube hors ligne. Consultez la section Reconnexion du fichier de cube hors ligne à la base de données du serveur OLAP (ci-dessous dans cet article).
Mise à jour du fichier de cube hors ligne avec de nouvelles données et recréation du fichier de cube hors connexion. Consultez la section mise à jour et recréation du fichier de cube hors ligne (plus loin dans cet article).
BLOG
Seuls les messages de haute qualité
Que sont les tableaux croisés dynamiques Excel et les cubes OLAP
Regardez la vidéo de l'article:
OLAP Est anglais. traitement analytique en ligne, technologie analytique pour le traitement des données en temps réel. En termes simples - un stockage avec des données multidimensionnelles (Cube), encore plus simple - juste une base de données à partir de laquelle vous pouvez obtenir des données dans Excel et les analyser à l'aide de l'outil Excel - Tableaux croisés dynamiques.
Tableaux croisés dynamiques Est une interface utilisateur pour afficher des données multidimensionnelles. En d'autres termes, un type spécial de tableaux qui peuvent être utilisés pour créer presque tous les rapports.
Pour être clair, comparons le "tableau normal" avec le "tableau croisé dynamique"
Table régulière:
Sommaire:
La principale différence Tableaux croisés dynamiques Est la présence d'une fenêtre " Liste des champs du tableau croisé dynamique", À partir duquel vous pouvez sélectionner les champs requis et obtenir automatiquement n'importe quelle table!
Comment utiliser
Ouvrez le fichier Excel connecté au cube OLAP, par exemple "BIWEB":
Maintenant qu'est-ce que cela signifie et comment l'utilisez-vous?
Faites glisser et déposez les champs requis pour obtenir, par exemple, une table comme celle-ci:
« Signes plus»Vous permet d'explorer le rapport. Dans cet exemple, "Marque" est détaillé en "Noms abrégés" et "Trimestre" en "Mois", c'est-à-dire Alors:
Fonctions analytiques dans Excel (fonctions cubes)
Microsoft ajoute constamment de nouvelles fonctionnalités à Excel en termes d'analyse et de visualisation des données. Travailler avec des informations dans Excel peut être représenté comme trois couches relativement indépendantes:
- Données sources organisées "correctement"
- mathématiques (logique) du traitement des données
- présentation des données
Figure: 1. Analyse des données dans Excel: a) données source, b) mesure dans Power Pivot, c) tableau de bord; pour agrandir l'image, faites un clic droit dessus et sélectionnez Ouvrir l'image dans un nouvel onglet
Téléchargez la note au format Word ou pdf, des exemples au format Excel
Fonctions de cube et tableaux croisés dynamiques
Les tableaux croisés dynamiques sont l'outil de présentation de données le plus simple et en même temps très puissant. Ils peuvent être créés sur la base de données contenues: a) dans une feuille Excel, b) un cube OLAP ou c) un modèle de données Power Pivot. Dans les deux derniers cas, en plus du tableau croisé dynamique, vous pouvez utiliser des fonctions analytiques (fonctions cubes) pour générer un rapport sur une feuille Excel. Les tableaux croisés dynamiques sont plus simples. Les fonctions des cubes sont plus complexes, mais elles offrent plus de flexibilité, notamment dans la conception des rapports, elles sont donc largement utilisées dans les tableaux de bord.
Ce qui suit concerne les formules de cube et les tableaux croisés dynamiques basés sur modèles de puissance Pivotez et, dans quelques cas, cubes OLAP.
Un moyen facile d'obtenir des fonctions de cubes
Lorsque (si) vous avez commencé à apprendre le code VBA, vous avez découvert que le moyen le plus simple d'obtenir le code consiste à enregistrer une macro. Ensuite, vous pouvez éditer le code, ajouter des boucles, des vérifications, etc. De même, le moyen le plus simple d'obtenir un ensemble de fonctions de cubes est de transformer le tableau croisé dynamique (Fig. 2). Placez-vous sur n'importe quelle cellule du tableau croisé dynamique, allez dans l'onglet Une analyse, cliquez sur le bouton Installations OLAPet appuyez sur Convertir en formules.
Figure: 2. Conversion d'un tableau croisé dynamique en un ensemble de fonctions de cube
Les nombres seront conservés, pas les valeurs, mais les formules qui extraient des données du modèle de données Power Pivot (Figure 3). Vous pouvez formater le tableau résultant. Y compris, vous pouvez supprimer et insérer des lignes et des colonnes à l'intérieur du tableau. La tranche reste et affecte les données de la table. Lorsque les données d'origine sont mises à jour, les chiffres du tableau sont également mis à jour.
Figure: 3. Tableau basé sur des formules de cube
Fonction CUBEVALUE ()
C'est peut-être la fonction principale des cubes. C'est l'équivalent de la zone Les valeurs tableau croisé dynamique. CUBEVALUE récupère les données d'un cube ou d'un modèle Power Pivot et les affiche en dehors du tableau croisé dynamique. Cela signifie que vous n'êtes pas limité aux limites du tableau croisé dynamique et que vous pouvez créer des rapports avec d'innombrables possibilités.
Écrire une formule à partir de zéro
Vous n'avez pas besoin de convertir le tableau croisé dynamique terminé. Vous pouvez écrire n'importe quelle formule de cube à partir de zéro. Par exemple, la formule suivante est entrée dans la cellule C10 (Fig.4):
Figure: 4. La fonction CUBEVALUE () dans la cellule C10 renvoie les ventes de vélos pour toutes les années, comme dans le tableau croisé dynamique
Un petit truc. Pour faciliter la lecture des formules de cube, il est souhaitable qu'un seul argument soit placé sur chaque ligne. Vous pouvez réduire la taille de la fenêtre Excel. Pour ce faire, cliquez sur l'icône Réduire à la fenêtresitué à droite coin supérieur écran. Et puis ajustez la taille de la fenêtre horizontalement. Une autre option consiste à envelopper de force le texte de la formule nouvelle ligne... Pour ce faire, dans la barre de formule, placez le curseur à l'endroit où vous souhaitez transférer et appuyez sur Alt + Entrée.
Figure: 5. Réduisez la fenêtre
Syntaxe de la fonction CUBEVALUE ()
Excel Help est absolument précis et totalement inutile pour les débutants:
CUBEVALUE (connexion; [expression_membre1]; [expression_membre2]; ...)
Lien - argument requis; une chaîne de texte représentant le nom de la connexion au cube.
Expression_membre - argument optionnel; une chaîne de texte représentant une expression MDX qui renvoie un élément ou un tuple dans un cube. De plus, "expression_membre" peut être un ensemble défini avec la fonction CUBESET. Utilisez member_expression comme tranche pour identifier la partie du cube pour laquelle vous souhaitez renvoyer une valeur agrégée. Si aucune mesure n'est spécifiée dans member_expression, la mesure par défaut pour ce cube sera utilisée.
Avant de passer à l'explication de la syntaxe de la fonction CUBEVALUE, quelques mots sur les cubes, les modèles de données et le cryptique cortège.
Quelques informations sur les cubes OLAP et les modèles de données Power Pivot
Cubes de données OLAP ( On line UNEnalytique Ptraitement - analyse des données opérationnelles) ont été développés spécifiquement pour le traitement analytique et l'extraction rapide des données. Imaginez un espace tridimensionnel où les périodes de temps, les villes et les biens sont tracés le long des axes (Figure 5a). Les nœuds d'une telle grille de coordonnées contiennent les valeurs diverses mesures: volume des ventes, profit, coûts, nombre d'unités vendues, etc. Imaginez maintenant qu'il y ait des dizaines, voire des centaines de dimensions ... et il y a aussi beaucoup de mesures. Ce sera le cube multidimensionnel OLAP. La création, la configuration et la mise à jour des cubes OLAP sont l'affaire des professionnels de l'informatique.
Figure: 5a. Cube 3D OLAP
Les formules analytiques Excel (formules de cube) extraient les noms des axes (par exemple, Temps), noms des éléments sur ces axes (août, septembre), valeurs des mesures à l'intersection des coordonnées. C'est cette structure qui permet aux tableaux croisés dynamiques basés sur des cubes et aux formules de cube d'être si flexibles et réactifs aux besoins des utilisateurs. Les tableaux croisés dynamiques basés sur des feuilles de calcul Excel n'utilisent pas de mesures, ils ne sont donc pas aussi flexibles à des fins d'analyse de données.
Power Pivot est une fonctionnalité relativement nouvelle de Microsoft. Il est intégré dans Excel et dans un environnement quelque peu indépendant avec une interface familière. Power Pivot est largement supérieur aux tableaux croisés dynamiques standard. Cependant, le développement de cubes dans Power Pivot est relativement facile et, surtout, ne nécessite pas de professionnel de l'informatique. Microsoft met en œuvre son slogan: «Business Intelligence to the Masses!». Bien que les modèles Power Pivot ne soient pas des cubes à 100%, ils peuvent également être appelés cubes (pour plus d'informations, consultez le cours d'introduction de Mark Moore. Power Pivot et l'édition plus grande Rob Colley. Formules DAX pour Power Pivot).
Les principaux composants d'un cube sont les dimensions, les hiérarchies, les niveaux, les membres (ou membres; en anglais membres) et les mesures. La mesure - la caractéristique principale des données analysées. Par exemple, catégorie de produit, période, géographie des ventes. La dimension est ce que nous pouvons placer sur l'un des axes du tableau croisé dynamique. Chaque dimension comprend, en plus des valeurs uniques, un membre qui regroupe tous les membres de cette dimension.
Les mesures sont tracées en fonction de hiérarchies... Par exemple, une catégorie de produits peut être décomposée en sous-catégories, puis en modèles et enfin, en noms de produits (Figure 5b). La hiérarchie vous permet de créer des données récapitulatives et de les analyser à différents niveaux de la structure. Dans notre exemple, la hiérarchie Catégorie comprend 4 Niveau.
Les éléments (membres individuels) sont présents à tous les niveaux. Par exemple, au niveau Catégorie, il y a quatre éléments: Accessoires, Vélos, Vêtements, Composants. Les autres niveaux ont leurs propres éléments.
Les mesures Sont des valeurs calculées telles que les ventes. Les mesures cubées sont stockées dans leur propre dimension appelée (voir la figure 9 ci-dessous). Les mesures n'ont pas de hiérarchies. Chaque mesure calcule et stocke une valeur pour toutes les dimensions et tous les membres, et est découpée en fonction des membres de dimension que nous plaçons sur l'axe. Ils indiquent également quelles coordonnées seront définies ou quel contexte de filtre sera défini. Par exemple, sur la Fig. Dans chaque petit cube, la même mesure est calculée - Profit. Et la valeur renvoyée par la mesure dépend des coordonnées. Sur la droite de la figure 5a, il est montré que Bénéfice (en trois coordonnées) pour Moscou en octobre sur les pommes \u003d 63 000 r. La mesure peut être interprétée comme l'une des dimensions. Par exemple, sur la Fig. 5a au lieu de l'axe Des produits, placer l'axe Les mesures avec des éléments Volume de ventes, Profit, Unités vendues... Ensuite, chaque cellule aura une valeur, par exemple, Moscou, septembre, volume des ventes.
Tuple - plusieurs éléments de dimensions différentes, spécifiant les coordonnées le long des axes du cube, dans lesquels nous calculons la mesure. Par exemple, sur la Fig. 5a Tuple \u003d Moscou, octobre, pommes. Perm, pommes est également un tuple valide. Un autre est les pommes, août. Les dimensions non incluses dans un tuple sont implicitement présentes dans un tuple et sont représentées par un membre par défaut. Ainsi, une cellule dans un espace multidimensionnel est toujours définie par un ensemble complet de coordonnées, même si certaines d'entre elles sont omises dans le tuple. Vous ne pouvez pas inclure deux membres de la même dimension dans un tuple, la syntaxe ne le permet pas. Par exemple, un cortège invalide Moscou et Perm, pommes. Pour mettre en œuvre un tel MDX, vous avez besoin d'un ensemble de deux tuples: Moscou et pommes + Perm et pommes.
Ensemble d'éléments - plusieurs éléments d'une dimension. Par exemple, les pommes et les poires. Un ensemble de tuples - plusieurs tuples, dont chacun est composé des mêmes dimensions dans la même séquence. Par exemple, un ensemble de deux tuples: Moscou, pommes et Perm, bananes.
Auto-complétion pour aider
Revenons à la syntaxe de la fonction CUBEVALUE. Utilisons l'auto-complétion. Commencez à taper la formule dans la cellule:
Excel proposera toutes les connexions disponibles dans le classeur Excel:
Figure: 6. La connexion au modèle de données Power Pivot est toujours appelée ThisWorkbookDataModel
Figure: 7. Connexions aux cubes
Continuons à entrer la formule (dans notre cas pour le modèle de données):
La saisie semi-automatique suggérera tous les tableaux et mesures disponibles du modèle de données:
Figure: 8. Éléments de premier niveau disponibles - noms de table et ensemble de mesures (mis en évidence)
Sélectionnez l'icône Les mesures... Mettez un point:
CUBEVALUE ("ThisWorkbookDataModel"; ".
L'auto-complétion suggérera toutes les mesures disponibles:
Figure: 9. Éléments de deuxième niveau disponibles dans l'ensemble de mesures
Choisissez une mesure. Ajoutez des guillemets, fermez les parenthèses, appuyez sur Entrée.
CUBEVALUE ("ThisWorkbookDataModel"; ".")
Figure: 10. Formule CUBE dans la cellule Excel
De même, vous pouvez ajouter un troisième argument à votre formule:
VBA dans Excel Excel.PivotTable Object et utilisation des tableaux croisés dynamiques et des cubes OLAP dans Excel
10.8 Utilisation des tableaux croisés dynamiques (objet Tableau croisé dynamique)
Objet Excel.PivotTable, travail de programme avec des tableaux croisés dynamiques et des cubes OLAP dans Excel à l'aide de VBA, objet PivotCache, création d'une disposition de tableau croisé dynamique
Au cours du fonctionnement de la plupart des entreprises, des données dites brutes sur les activités sont accumulées. Par exemple, les données sur les ventes de biens peuvent être accumulées pour une entreprise commerciale - pour chaque achat séparément, pour les entreprises communication cellulaire - statistiques de la charge sur les stations de base, etc. Très souvent, la direction d'une entreprise a besoin d'informations analytiques générées à partir d'informations brutes - par exemple, pour calculer la contribution de chaque type de produit aux revenus de l'entreprise ou à la qualité de service dans la zone d'une station donnée. Il est très difficile d'extraire de telles informations à partir d'informations brutes: vous devez exécuter des requêtes SQL très complexes qui prennent beaucoup de temps et interfèrent souvent avec le travail en cours. Par conséquent, de nos jours, de plus en plus souvent, les données brutes sont d'abord consolidées dans l'entrepôt de données - le Data Warehouse, puis - dans des cubes OLAP, qui sont très pratiques pour l'analyse interactive. La façon la plus simple de penser aux cubes OLAP est de les utiliser sous forme de tables multidimensionnelles, dans lesquelles au lieu des deux dimensions standard (colonnes et lignes, comme dans les tables ordinaires), il peut y avoir beaucoup de dimensions. Le terme «coupe» est couramment utilisé pour décrire les dimensions d'un cube. Par exemple, le service marketing peut avoir besoin d'informations en termes de temps, en termes régionaux, en termes de types de produits, en termes de canaux de vente, etc. En utilisant des cubes (par opposition aux requêtes SQL standard), il est très facile d'obtenir des réponses à des questions telles que «combien de produits de ce type ont été vendus au quatrième trimestre de l'année dernière dans la région du Nord-Ouest par l'intermédiaire de distributeurs régionaux.
Bien entendu, de tels cubes ne peuvent pas être créés dans des bases de données ordinaires. Les cubes OLAP nécessitent un logiciel spécialisé. SQL Server est livré avec une base de données OLAP de Microsoft appelée Analysis Services. Il existe des solutions OLAP d'Oracle, IBM, Sybase, etc.
Un client spécial est intégré à Excel pour travailler avec de tels cubes. En russe, il s'appelle Tableau croisé dynamique (sur l'écran graphique, il est accessible via le menu Les données -> Tableau croisé dynamique), et en anglais - Tableau croisé dynamique... Par conséquent, l'objet que ce client représente est appelé un tableau croisé dynamique. Il convient de noter qu'il sait travailler non seulement avec des cubes OLAP, mais aussi avec des données ordinaires dans feuilles de calcul Excel ou bases de données, mais de nombreuses opportunités sont perdues.
PivotTable et PivotTable sont des produits Panorama Software qui ont été acquis par Microsoft et intégrés dans Excel. Par conséquent, l'utilisation de l'objet de tableau croisé dynamique est quelque peu différente de l'utilisation d'autres objets Excel. Il est souvent difficile de deviner ce qui doit être fait. Par conséquent, il est recommandé d'utiliser activement l'enregistreur de macros pour recevoir des conseils. Dans le même temps, lorsqu'ils travaillent avec des tableaux croisés dynamiques, les utilisateurs doivent souvent effectuer les mêmes opérations répétitives, de sorte que l'automatisation est nécessaire dans de nombreuses situations.
À quoi ressemble le travail programmatique avec un tableau croisé dynamique?
La première chose que nous devons faire est de créer un objet PivotCache qui représentera l'ensemble des enregistrements récupérés à partir de la source OLAP. De manière très conventionnelle, cet objet PivotCache peut être comparé à un QueryTable. Un seul objet PivotCache peut être utilisé pour chaque tableau croisé dynamique. L'objet PivotCache est créé à l'aide de la méthode Add () de la collection PivotCaches:
Dim PC1 comme PivotCache
Définir PC1 \u003d ActiveWorkbook.PivotCaches.Add (xlExternal)
PivotCaches est une collection standard, et parmi les méthodes qui méritent un examen détaillé, seule la méthode Add () peut être nommée. Cette méthode prend deux paramètres:
- Type de Source - obligatoire, définit le type de source de données pour le tableau croisé dynamique. Vous pouvez spécifier de créer un tableau croisé dynamique basé sur une plage dans Excel, des données d'une base de données, dans une source de données externe, un autre tableau croisé dynamique, etc. En pratique, il est généralement judicieux d'utiliser OLAP uniquement lorsqu'il y a beaucoup de données - par conséquent, vous avez besoin d'un stockage externe spécialisé (par exemple, Microsoft Analysis Services). Dans ce cas, la valeur xlExternal est sélectionnée.
- Données source - obligatoire dans tous les cas, sauf ceux où la valeur du premier paramètre est xlExternal. Strictement parlant, il définit la plage de données sur la base de laquelle le tableau croisé dynamique sera créé. Prend généralement un objet Range.
La tâche suivante consiste à configurer les paramètres de l'objet PivotCache. Comme déjà mentionné, cet objet est très similaire à QueryTable et possède un ensemble très similaire de propriétés et de méthodes. Certaines des propriétés et méthodes les plus importantes:
- ADOConnection - la possibilité de renvoyer un objet ADO Connection qui est automatiquement créé pour se connecter à une source de données externe. Utilisé pour la configuration supplémentaire des propriétés de connexion.
- Lien - fonctionne de la même manière que la propriété du même nom de l'objet QueryTable. Peut accepter une chaîne de connexion, un objet Recordset prêt à l'emploi, fichier texte, Demande Web. fichier Microsoft Requete. Le plus souvent, lorsque vous travaillez avec OLAP, une chaîne de connexion est écrite directement (car il n'a guère de sens de recevoir un objet Recordset, par exemple, pour modifier des données - les sources de données OLAP sont presque toujours en lecture seule). Par exemple, la définition de cette propriété pour se connecter à une base de données Foodmart (exemple de base de données Analysis Services) sur un serveur LONDON peut ressembler à ceci:
PC1.Connection \u003d "OLEDB; Fournisseur \u003d MSOLAP.2; Source de données \u003d LONDON1; Catalogue initial \u003d FoodMart 2000"
- propriétés CommandType et CommandText décrivez de la même manière le type de commande envoyée au serveur de base de données et le texte de la commande elle-même. Par exemple, pour accéder au cube Sales et le placer entièrement dans le cache sur le client, vous pouvez utiliser du code comme
- propriété LocalConnection vous permet de vous connecter à un cube local (fichier * .cub) créé par Excel. Bien entendu, il n'est pas recommandé d'utiliser de tels fichiers pour travailler avec des volumes de données «de production» - uniquement dans le but de créer des mises en page, etc.
- propriété Mémoire utilisée retourne la quantité mémoire viveutilisé par PivotCache. Renvoie 0 si un tableau croisé dynamique basé sur ce PivotCache n'a pas encore été créé et ouvert. Peut être utilisé pour vérifier si votre application fonctionnera sur des clients faibles.
- propriété OLAP renvoie True si PivotCache est connecté à un serveur OLAP.
- OptimizeCache - la possibilité d'optimiser la structure du cache. Le chargement initial des données prendra plus de temps, mais plus tard, la vitesse de travail peut augmenter. Pour les sources OLE DB ne fonctionne pas.
Le reste des propriétés de l'objet PivotCache sont les mêmes que celles de l'objet QueryTable et ne seront donc pas abordés ici.
La méthode principale de l'objet PivotCache est la méthode CreatePivotTable (). À l'aide de cette méthode, l'étape suivante est effectuée: la création d'un tableau croisé dynamique (objet de tableau croisé dynamique). Cette méthode prend quatre paramètres:
- TableDestination est le seul paramètre requis. Prend un objet Range dans le coin supérieur gauche duquel le tableau croisé dynamique sera placé.
- Nom de la table - le nom du tableau croisé dynamique. S'il n'est pas spécifié, un nom de type "PivotTable1" sera automatiquement généré.
- ReadData - s'il est défini sur True, tout le contenu du cube sera automatiquement mis en cache. Vous devez être très prudent avec ce paramètre, car son application incorrecte peut considérablement augmenter la charge sur le client.
- DefaultVersion - cette propriété n'est généralement pas spécifiée. Détermine la version du tableau croisé dynamique en cours de création. La version la plus récente est utilisée par défaut.
La création d'un tableau croisé dynamique dans la première cellule de la première feuille du classeur peut ressembler à ceci:
Plage PC1.CreatePivotTable ("A1")
Nous avons créé un tableau croisé dynamique, mais immédiatement après sa création, il est vide. Il fournit quatre zones dans lesquelles vous pouvez placer des champs à partir de la source (sur l'écran graphique, tout cela peut être configuré soit à l'aide de la fenêtre Liste des champs du tableau croisé dynamique - il s'ouvre automatiquement, ou à l'aide du bouton Disposition sur le dernier écran de l'assistant de création de tableau croisé dynamique):
- zone de colonne - il contient les dimensions ("coupe" dans lesquelles les données seront analysées), dont les membres sont moins nombreux;
- zone de ligne - ces dimensions avec plus de membres;
- zone de page - les mesures qui doivent seulement être filtrées (par exemple, afficher les données uniquement pour une certaine région ou seulement pour une certaine année);
- zone de données - en fait, la partie centrale de la table. Ces données numériques (par exemple, le montant des ventes), que nous analysons.
Il est difficile de se fier à l'utilisateur pour placer correctement les articles dans les quatre zones. En outre, cela peut prendre un certain temps. Par conséquent, il est souvent nécessaire d'organiser par programme les données dans le tableau croisé dynamique. Cette opération est effectuée à l'aide de l'objet CubeField. La propriété principale de cet objet est Orientation, elle détermine où tel ou tel champ sera situé. Par exemple, plaçons la dimension Clients dans la zone de colonne:
PT1.CubeFields ("") .Orientation \u003d xlColumnField
Puis - mesurer le temps dans la zone des chaînes:
PT1.CubeFields ("") .Orientation \u003d xlRowField
Ensuite - mesure du produit dans la zone de page:
PT1.CubeFields ("") .Orientation \u003d xlPageField
Enfin, la mesure (données numériques pour analyse) Ventes unitaires:
PT1.CubeFields ("."). Orientation \u003d xlDataField
Les problèmes d'analyse, d'OLAP, d'entrepôts de données sont d'un intérêt croissant parmi les informaticiens russes. À ce jour, dans notre presse informatique et sur Internet, de nombreux supports de présentation de qualité académique sur ce sujet, y compris des documents d’introduction, ont été publiés. Nous portons à votre attention un article dans lequel nous essayons délibérément d'expliquer OLAP «sur les doigts», en utilisant un exemple précis. La pratique montre qu'une telle explication est nécessaire pour certains professionnels de l'informatique et en particulier pour les utilisateurs finaux.
Ainsi, OLAP * 1 dans la première approximation, «sur les doigts», peut être défini comme une manière spéciale d'analyser les données et d'obtenir des rapports. Son essence consiste à fournir à l'utilisateur un tableau multidimensionnel qui résume automatiquement les données dans différentes sections et permet un contrôle interactif des calculs et sous la forme d'un rapport. Cet article parlera de la technologie et des opérations de base d'OLAP à l'aide de l'exemple d'analyse des factures d'un grossiste en alimentation.
*1. OLAP - Traitement analytique en ligne, analyse de données en ligne.
Un système OLAP de la classe la plus simple et la moins chère, le client OLAP * 1, sera considéré comme un outil. À titre d'exemple, le produit le plus simple parmi le nombre de clients OLAP a été choisi - «Kontur Standard» par Intersoft Lab. (Pour plus de clarté, plus loin dans l'article, les termes OLAP généralement acceptés seront indiqués en gras et accompagnés de leurs équivalents en anglais.)
*1. Plus de détails sur la classification des systèmes OLAP sont décrits dans l'article "OLAP Made in Russia" dans PC Week / RE, №3 / 2001.
Alors, commençons à travailler avec le système. Tout d'abord, vous devez décrire la source de données - le chemin vers la table et ses champs. C'est à l'utilisateur qui connaît l'implémentation physique de la base de données. Pour les utilisateurs finaux, il traduit le nom de la table et ses champs en termes de domaine. Derrière la «source de données» se trouve une table locale, une table ou une vue SQL Server ou une procédure stockée.
Très probablement, dans une base de données particulière, les factures ne sont pas stockées dans une, mais dans plusieurs tables. De plus, certains champs ou enregistrements peuvent ne pas être utilisés pour l'analyse. Par conséquent, une sélection (jeu de résultats ou requête) est alors créée, dans laquelle les éléments suivants sont configurés: l'algorithme de jonction des tables par champs clés, conditions de filtrage et ensemble de champs renvoyés. Appelons notre sélection «Factures» et y mettons tous les champs de la source de données «Factures». Ainsi, l'informaticien, créant la couche sémantique, cache l'implémentation physique de la base de données à l'utilisateur final.
Ensuite, le rapport OLAP est configuré. Cela peut être fait par un expert en la matière. Premièrement, les champs d'échantillonnage de données plates sont divisés en deux groupes - faits (faits ou mesures) et dimensions (dimensions). Les faits sont des nombres et les dimensions sont des «coupes» qui résumeront les faits. Dans notre exemple, les dimensions seront: «Région», «Ville», «Client», «Produit», «Date», et il y aura un fait - le champ «Montant» de la facture. Pour un fait, vous devez sélectionner un ou plusieurs algorithmes d'agrégation. OLAP est capable non seulement de résumer les résultats, mais également d'effectuer des calculs plus complexes, jusqu'à l'analyse statistique. Le choix de plusieurs algorithmes d'agrégation générera des faits virtuels calculés. Dans l'exemple, un algorithme d'agrégation est sélectionné - «Sum».
Une particularité des systèmes OLAP est la génération de dimensions et de données pour des périodes plus anciennes à partir de la date et le calcul automatique des totaux pour ces périodes. Sélectionnons les périodes «Année», «Trimestre» et «Mois», alors qu'il n'y aura pas de données pour chaque jour dans le rapport, mais les dimensions générées «Année», «Trimestre» et «Mois» apparaîtront. Appelons le rapport «Analyse des ventes» et sauvegardons-le. Le travail de création de l'interface de l'application analytique est terminé.
Désormais, en lançant cette interface sur une base quotidienne ou mensuelle, l'utilisateur verra un tableau et un graphique dans lesquels les factures sont résumées par marchandises, clients et périodes.
Afin de rendre les manipulations avec des données intuitives, les outils de gestion d'un tableau dynamique sont les éléments du tableau lui-même - ses colonnes et ses lignes. L'utilisateur peut les déplacer, les supprimer, les filtrer et effectuer d'autres opérations OLAP. Le tableau calculera automatiquement les nouveaux sous-totaux et les totaux finaux.
Par exemple, en faisant glisser (opération «déplacer») la colonne «Produit» à la première place, nous recevrons un rapport de comparaison - «Comparaison des ventes de produits pour l'année». Pour agréger les données pour une année, faites simplement glisser les colonnes «Trimestre» et «Mois» vers le haut du tableau - «Zone des dimensions inactives». Les dimensions "Trimestre" et "Mois", transférées dans cette zone, seront clôturées (opération "dimension proche"), c'est-à-dire exclues du rapport; les faits seront résumés pour l'année. Malgré le fait que les dimensions sont fermées, elles peuvent être utilisées pour spécifier des années, des trimestres et des mois spécifiques pour filtrer les données (opération "filtre").
Pour plus de clarté, modifions le type de graphique illustrant la table OLAP et son emplacement à l'écran.
Creuser dans les données (opération "drill down") nous permet d'obtenir des informations plus détaillées sur les ventes du produit qui nous intéresse. En cliquant sur le signe «+» en face du produit «Café», nous verrons le volume de ses ventes dans le contexte des régions. Après avoir ouvert la région de l'Oural, nous obtiendrons les volumes de ventes dans le contexte des villes de la région de l'Oural, en explorant les données sur Ekaterinbourg, nous pourrons voir les données sur les acheteurs en gros de cette ville.
Vous pouvez également utiliser des mesures ouvertes pour définir des filtres. Pour comparer la dynamique des ventes de bonbons à Moscou et à Ekaterinbourg, définissons des filtres sur les dimensions «Produit» et «Ville».
Fermez les mesures inutiles et sélectionnez le type de graphique «Ligne». Sur le graphique résultant, vous pouvez suivre la dynamique des ventes, évaluer les fluctuations saisonnières et la relation entre les chutes et la croissance des ventes de biens dans différentes villes.
Ainsi, nous nous sommes assurés que la technologie OLAP permet à l'utilisateur de publier des dizaines de types de rapports différents à partir d'une seule interface, en manipulant une table OLAP dynamique avec la souris. La tâche du programmeur qui possède un tel outil n'est pas le codage de routine des formulaires de rapport, mais la configuration du client OLAP pour les bases de données. Dans le même temps, les méthodes de gestion du rapport sont intuitives pour l'utilisateur final.
En effet, OLAP est une extension et un développement naturels de l'idée des feuilles de calcul. En réalité, interface visuelle OLAP est aussi un tableur, mais équipé d'un puissant moteur de calcul et d'une norme spéciale pour la présentation et la gestion des données. De plus, certains clients OLAP sont implémentés en tant que compléments à MS Excel. Par conséquent, une armée de cols blancs d'un million de personnes, maîtrisant avec confiance les feuilles de calcul, maîtrise très rapidement les outils OLAP. Pour eux, il s'agit d'une «révolution de velours» qui offre de nouvelles opportunités, mais qui n'est pas associée à la nécessité de se recycler.
Si le lecteur, après avoir lu cet article, ne s'est pas désintéressé d'OLAP, il peut se référer aux matériaux mentionnés au début. Des collections de ces matériaux sont publiées sur un certain nombre de sites Internet, y compris le site du laboratoire Intersoft - www.iso.ru. Vous pouvez également télécharger une version de démonstration du système Kontur Standard avec l'exemple décrit dans l'article.
