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 académiques 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.
En tant qu'outil, nous considérerons un système OLAP de la classe la plus simple et la moins chère - client OLAP * 1. A 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 la tâche de l'utilisateur qui sait mise en œuvre physique 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 un 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 aussi 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, supprimer, 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 soient 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 d'un programmeur qui possède un tel outil n'est pas le codage de routine des formulaires de rapport, mais la configuration d'un 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 de tableurs. 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 sur 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.
La première interface de tableaux croisés dynamiques, également appelée rapports croisés dynamiques, a été incluse dans Excel en 1993 (version Excel 5.0). Malgré les nombreux fonctionnalité, il n'est presque jamais utilisé par la plupart des utilisateurs d'Excel. Même les utilisateurs expérimentés entendent souvent par le terme «rapport de synthèse» quelque chose construit à l'aide de formules complexes. Essayons de vulgariser l'utilisation des tableaux croisés dynamiques dans le travail quotidien des économistes. L'article décrit les fondements théoriques de la création de rapports de synthèse, fournit des recommandations pratiques pour leur utilisation et fournit également un exemple d'accès aux données basé sur plusieurs tables.
Termes d'analyse de données multivariées
La plupart des économistes ont entendu les termes «données multidimensionnelles», «cube virtuel», «technologies OLAP», etc. Mais avec une conversation détaillée, il s'avère généralement que presque tout le monde ne sait pas vraiment quoi dans la question... Autrement dit, les gens veulent dire quelque chose de complexe et généralement pas pertinent pour leurs activités quotidiennes. En fait, ce n'est pas le cas.
Données multidimensionnelles, mesures
Il est sûr de dire que les économistes sont presque constamment confrontés à des données multidimensionnelles, mais essaient de les représenter sous une forme prédéfinie à l'aide de feuilles de calcul. La multidimensionnalité signifie ici la possibilité de saisir, de visualiser ou d'analyser les mêmes informations avec un changement d'apparence, l'utilisation de regroupements et de types de données différents. Par exemple, un plan de vente peut être analysé selon les critères suivants:
- types ou groupes de produits;
- marques ou catégories de produits;
- périodes (mois, trimestre, année);
- acheteurs ou groupes d'acheteurs;
- régions de vente
- etc.
Chacun des critères ci-dessus en termes d'analyse de données multivariées est appelé «dimension». On peut dire qu'une mesure caractérise des informations pour un certain ensemble de valeurs. Les «données» constituent un type spécial de mesure d'informations multidimensionnelles. Dans notre exemple, les données du plan de vente peuvent être:
- volume des ventes;
- prix \u200b\u200bde vente;
- remise individuelle
- etc.
En théorie, les données peuvent également être une dimension standard d'informations multidimensionnelles (par exemple, vous pouvez regrouper les données par prix de vente), mais les données restent généralement un type spécial de valeur.
Ainsi, nous pouvons dire que dans travaux pratiques les économistes utilisent deux types d'informations: données multidimensionnelles (nombres réels et prévus ayant de nombreux attributs) et des ouvrages de référence (caractéristiques ou mesures de données).
OLAP
L'abréviation OLAP (traitement analytique en ligne) se traduit littéralement par «traitement analytique en temps réel». La définition n'est pas très précise, presque tous les rapports de n'importe quel produit logiciel peuvent être résumés en dessous. Au sens d'OLAP, il s'agit d'une technologie permettant de travailler avec des rapports spéciaux, y compris des logiciels, pour obtenir et analyser des données structurées multidimensionnelles. L'un des logiciels populaires qui implémentent les technologies OLAP est serveur SQL Serveur d'analyse. Certains le considèrent même à tort comme le seul représentant de l'implémentation logicielle de ce concept.
Cube de données virtuel
«Cube virtuel» (cube multidimensionnel, cube OLAP) est un terme technique inventé par certains éditeurs de logiciels spécialisés. Les systèmes OLAP préparent et stockent généralement les données dans leurs propres structures et des interfaces d'analyse spéciales (par exemple, les rapports croisés Excel) accèdent aux données à partir de ces cubes virtuels. Dans le même temps, l'utilisation d'un tel stockage dédié n'est pas du tout nécessaire pour traiter des informations multidimensionnelles. En général, cube virtuel - il s'agit d'un tableau de données multidimensionnelles spécialement optimisées qui sont utilisées pour créer des rapports de synthèse. Il peut être obtenu à la fois grâce à des outils logiciels spécialisés et par un simple accès aux tables de la base de données ou à toute autre source, comme un tableau Excel.
Tableau croisé dynamique
Un «tableau croisé dynamique» (tableau croisé dynamique) est une interface utilisateur permettant d'afficher des données multidimensionnelles. Grâce à cette interface, vous pouvez regrouper, trier, filtrer et modifier l'emplacement des données afin d'obtenir divers échantillons analytiques. La mise à jour du rapport est effectuée par de simples moyens de l'interface utilisateur, les données sont automatiquement agrégées selon les règles spécifiées, et aucune entrée supplémentaire ou répétée de toute information n'est requise. Interface pivot feuilles de calcul Excel est peut-être le logiciel le plus populaire pour travailler avec des données multidimensionnelles. Il prend en charge à la fois les sources de données externes (cubes OLAP et bases de données relationnelles) et les plages de feuilles de calcul internes en tant que source de données. À partir de la version 2000 (9.0), Excel prend également en charge une forme graphique d'affichage de données multidimensionnelles - un tableau croisé dynamique (tableau croisé dynamique).
L'interface de tableau croisé dynamique d'Excel vous permet de positionner des dimensions de données multidimensionnelles dans une zone d'une feuille de calcul. Pour plus de simplicité, vous pouvez considérer un tableau croisé dynamique comme un rapport situé au-dessus d'une plage de cellules (en fait, il existe une certaine liaison des formats de cellule aux champs du tableau croisé dynamique). Un tableau croisé dynamique Excel comporte quatre zones pour afficher les informations: filtre, colonnes, lignes et données. Les dimensions de données sont nommées champs de tableau croisé dynamique... Ces champs ont leurs propres propriétés et format d'affichage.
Encore une fois, je voudrais attirer votre attention sur le fait que le tableau croisé dynamique Excel est destiné exclusivement à l'analyse de données sans possibilité de modifier les informations. Plus proche dans son sens serait l'utilisation omniprésente du terme «rapport pivot», et c'est ainsi que cette interface a été appelée jusqu'en 2000. Mais pour une raison quelconque, dans les versions ultérieures, les développeurs l'ont abandonné.
Modifier les tableaux croisés dynamiques
De par sa définition, la technologie OLAP n'implique en principe pas la possibilité de modifier les données sources lors de l'utilisation de rapports. Néanmoins, toute une classe s'est formée sur le marché systèmes logicielsqui mettent en œuvre les capacités d'analyse et d'édition directe des données dans des tableaux multidimensionnels. Fondamentalement, ces systèmes visent à résoudre les problèmes de budgétisation.
À l'aide des outils d'automatisation Excel intégrés, vous pouvez résoudre de nombreuses tâches non standard. Un exemple de mise en œuvre de l'édition pour les tableaux croisés dynamiques Excel basée sur les données de la feuille de calcul est disponible sur notre site Web.
Préparer des données multidimensionnelles
Allons à application pratique tableaux croisés dynamiques. Essayons d'analyser les données de vente dans différentes directions. Fichier pivottableexample.xls se compose de plusieurs feuilles. Feuille Exemplecontient des informations de base sur les ventes pour une période spécifique. Pour simplifier l'exemple, nous analyserons un seul indicateur numérique - le volume des ventes en kg. Il existe les dimensions de données clés suivantes: produit, client et transporteur (compagnie maritime). De plus, il existe plusieurs dimensions de données supplémentaires caractéristiques d'un produit: type, marque, catégorie, fournisseur et client: type. Ces données sont collectées sur la fiche Références. En pratique, il peut y avoir beaucoup plus de telles mesures.
Feuille Exemplecontient recours standard analyse de données - filtre automatique. En regardant l'exemple de remplissage du tableau, il est évident que les données sur les ventes par dates (elles sont disposées en colonnes) se prêtent à une analyse normale. De plus, à l'aide d'un filtre automatique, vous pouvez essayer de résumer les données par des combinaisons d'un ou plusieurs critères clés. Il n'y a absolument aucune information sur les marques, les catégories et les types. Il n'existe aucun moyen de regrouper des données avec une synthèse automatique par une clé spécifique (par exemple, par les clients). De plus, l'ensemble de dates est fixe et il ne sera pas possible d'afficher automatiquement les informations récapitulatives pendant une certaine période, par exemple 3 jours.
En général, avoir un emplacement de date prédéfini dans cet exemple - le principal inconvénient de la table. En disposant les dates en colonnes, nous avons en quelque sorte prédéfini la dimension de ce tableau, nous privant ainsi de l'opportunité d'utiliser l'analyse à l'aide de tableaux croisés dynamiques.
Tout d'abord, vous devez vous débarrasser de cet inconvénient - c.-à-d. supprimer l'emplacement prédéfini de l'une des dimensions des données d'origine. Exemple de tableau valide - feuille Ventes.
La table a la forme d'un journal de saisie d'informations. Ici, la date est la dimension homologue des données. Il convient également de noter que pour une analyse ultérieure dans les tableaux croisés dynamiques, la position relative des lignes les unes par rapport aux autres (en d'autres termes, le tri) est complètement indifférente. Les enregistrements des bases de données relationnelles possèdent ces propriétés. C'est sur l'analyse de gros volumes de bases de données que se concentre principalement l'interface des tableaux croisés dynamiques. Par conséquent, vous devez respecter ces règles lorsque vous travaillez avec une source de données sous la forme de plages de cellules. Dans le même temps, personne n'interdit l'utilisation des outils d'interface Excel dans le travail - les tableaux croisés dynamiques analysent uniquement les données et le formatage, les filtres, le regroupement et le tri des cellules sources peuvent être arbitraires.
Du filtre automatique au rapport de synthèse
Théoriquement, sur les données de la fiche de vente, il est déjà possible d'analyser en trois dimensions: les marchandises, les acheteurs et les transporteurs. Il n'y a pas de données sur les propriétés des produits et des acheteurs sur cette feuille, ce qui, par conséquent, ne permettra pas de les afficher dans le tableau croisé dynamique. En mode de création de tableau croisé dynamique normal pour les données source, Excel ne vous permet pas de lier des données de plusieurs tables à des champs spécifiques. Vous pouvez contourner cette limitation par programme - voir un exemple de supplément à cet article sur notre site Web. Afin de ne pas recourir à des méthodes logicielles de traitement de l'information (d'autant plus qu'elles ne sont pas universelles), des caractéristiques supplémentaires doivent être ajoutées directement au formulaire de saisie du journal - voir la fiche SalesAnalysis.
L'utilisation des fonctions RECHERCHEV permet de compléter facilement les données d'origine avec des caractéristiques manquantes. Désormais, à l'aide du filtre automatique, vous pouvez analyser les données dans différentes dimensions... Mais le problème des groupements reste non résolu. Par exemple, il est assez problématique de suivre le montant uniquement par marques pour certaines dates. Si vous vous limitez aux formules Excel, vous devez créer des sélections supplémentaires à l'aide de la fonction SUMIF.
Voyons maintenant les fonctionnalités fournies par l'interface du tableau croisé dynamique. Sur la feuille CodeAnalyseplusieurs rapports ont été créés à partir d'une plage de cellules avec des données de feuille Analyse des ventes.
Le premier tableau d'analyse est construit via l'interface Excel 2007 Ruban \\ Insérer \\ Tableau croisé dynamique (dans Excel 2000-2003 le menu Données \\ Tableau croisé dynamique).
Les deuxième et troisième tableaux ont été créés par copie et personnalisation ultérieure. La source de données pour toutes les tables est la même. Vous pouvez vérifier cela en modifiant les données d'origine, puis vous devez mettre à jour les données des rapports de synthèse.
De notre point de vue, les avantages de la clarté de l'information sont évidents. Vous pouvez permuter les filtres, les colonnes et les lignes et masquer des groupes de valeurs spécifiques pour n'importe quelle dimension, utiliser le glisser-déposer manuel et le tri automatique.
Propriétés et formatage
En plus de l'affichage direct des données, il existe un grand nombre d'options pour afficher l'apparence des tableaux croisés dynamiques. Les données supplémentaires peuvent être masquées à l'aide de filtres. Pour un seul élément ou champ, il est plus facile d'utiliser l'élément du menu contextuel Supprimer(dans la version 2000-2003 Cacher).
Il est également conseillé de définir l'affichage des autres éléments du tableau croisé dynamique non pas en formatant la cellule, mais en définissant le champ ou l'élément du tableau croisé dynamique. Pour ce faire, déplacez le pointeur de la souris sur l'élément souhaité, attendez l'apparition d'une forme spéciale de curseur (en forme de flèche), puis sélectionnez l'élément sélectionné d'un simple clic. Après la sélection, vous pouvez modifier la vue via le ruban, le menu contextuel ou appeler la boîte de dialogue de format de cellule standard:
En outre, Excel 2007 introduit de nombreux styles d'affichage de tableau croisé dynamique prédéfinis:
Notez que les filtres de contrôle et les zones de glissement sont actifs dans le graphique.
Accès aux données externes
Comme indiqué précédemment, le plus grand effet de l'utilisation des tableaux croisés dynamiques peut être obtenu lors de l'accès aux données à partir de sources externes - cubes OLAP et requêtes de base de données. Ces sources stockent généralement de grandes quantités d'informations et ont également un structure relationnelle, ce qui facilite la définition des dimensions des données multidimensionnelles (champs de tableau croisé dynamique).
Excel prend en charge de nombreux types de sources de données externes:
Le meilleur effet de l'utilisation de sources d'informations externes peut être obtenu en utilisant des outils d'automatisation (programmes VBA) à la fois pour obtenir des données et pour leur prétraitement dans des tableaux croisés dynamiques.
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 tout. entrées séparées et calcule des valeurs généralisé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 Un 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 elle 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 Collection 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 les niveaux inférieurs ou supérieurs.
Hiérarchie Une arborescence logique qui organise les membres d'une dimension de 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 détail 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 des services Microsoft SQL Serveur OLAP 7.0, Microsoft SQL Server Analysis Services 2000 et Microsoft SQL Server Analysis Services 2005, 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 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 de connexion de données Office (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 vierge 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. En utilisant 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 de 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 en tant que 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 mise en forme conditionnellequi peut être attribuée 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 Un administrateur de 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 logiciel client pour accéder aux bases de données créées avec Microsoft SQL Server olap version 7.0, Microsoft SQL Server olap version 2000 (8.0) et Microsoft SQL Server Analysis services version 2005 (9 , 0).
Fournisseurs OLAP tiers D'autres produits OLAP nécessitent l'installation de pilotes et de logiciels clients supplémentaires. 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 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 obtenir 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é.
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 disposez de plusieurs options supplémentaires 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 pas non plus 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 réorganiser manuellement les é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 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écification du nom ou de l'adresse IP serveur en direct OLAP (parfois un nombre est requis port ouvertpar exemple 192.25.25.102:80); l'authentification de domaine est utilisée:
3.2 Nous sélectionnons une base de données multidimensionnelle et un cube analytique (si vous avez les 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électionnez la connexion dans ce classeur -\u003e insérez le tableau croisé dynamique dans la cellule spécifiée.
4. Une fois connecté 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 on peut supposer que la troisième dimension est située perpendiculairement à l'écran, et on observe 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 du 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 est modifié, 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... Vous pouvez annuler les dernières opérations effectuées (Ctrl + Z) ou refaire (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 de données au niveau de 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 met dans le cache mémoire vive, 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 fréquemment utilisée 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 requête.
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 situés plus haut 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 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-en une 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 à partir de MS Excel 2010). Connectons notre Slicer aux tableaux croisés dynamiques - cliquez avec le bouton droit dans le slicer, sélectionnez dans menu contextuel de l'élément "Connexions du tableau croisé dynamique ...". 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 pour la tranche la gamme de couleurs et le style de conception à partir de l'ensemble existant (ou de créer votre propre style), de définir votre propre titre de panneau, d'attribuer une macro de programme à travers laquelle vous pouvez développer 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 un certain chiffre clé, c.-à-d. 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 cliquez sur le bouton après avoir vérifié la syntaxe de la requête et avoir les droits d'accès appropriés, la requête sera exécutée sur le serveur et le résultat sera présenté dans la feuille courante sous la forme d'un tableau plat ordinaire.
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
La possibilité de travailler avec des cubes Microsoft OLAP augmentera si vous utilisez des extensions supplémentaires, par exemple, les extensions de tableau croisé dynamique OLAP, grâce auxquelles vous pouvez utiliser recherche rapide par mesure:
