Travaux pratiques sur la technologie olap dans Excel. Cubes Olap dans Excel. Écrire une formule à partir de zéro

1 tâche (générale) (2 points).

À l'aide d'une feuille de calcul, traitez les données à l'aide de fonctions statistiques.
1. Des informations sont données sur les élèves de la classe (10 personnes), y compris les notes d'un mois en mathématiques. Comptez le nombre de cinq, quatre, deux et trois, trouvez la note moyenne de chaque élève et la note moyenne de l'ensemble du groupe. Créez un graphique illustrant le pourcentage de notes dans un groupe.

Tâche 2.1 (2 points).

Quatre amis voyagent par trois modes de transport : train, avion et bateau. Nikolai a parcouru 150 km en bateau, parcouru 140 km en train et parcouru 1 100 km en avion. Vasily a parcouru 200 km en bateau, parcouru 220 km en train et parcouru 1 160 km en avion. Anatoly a parcouru 1 200 km en avion, parcouru 110 km en train et navigué 125 km en bateau. Maria a parcouru 130 km en train, parcouru 1 500 km en avion et navigué 160 km en bateau.
Créez une feuille de calcul basée sur les données ci-dessus.

Ajoutez une colonne au tableau qui affichera le nombre total de kilomètres parcourus par chacun des gars.

Calculez le nombre total de kilomètres parcourus par les enfants en train, en avion et en bateau (sur chaque type de transport séparément).

Calculez le nombre total de kilomètres de tous les amis.

Déterminez le nombre maximum et minimum de kilomètres parcourus par des amis en utilisant tous les types de transport.

Déterminez le nombre moyen de kilomètres pour tous les types de transport.

Tâche 2.2 (2 points).

Créez un tableau « Lacs d'Europe » en utilisant les données suivantes sur la superficie (km²) et la plus grande profondeur (m) : Ladoga 17 700 et 225 ; Onega 9510 et 110 ; Mer Caspienne 371 000 et 995 ; Wenern 5550 et 100 ; Chudskoye avec Pskovsky 3560 et 14 ; Balaton 591 et 11 ; Genève 581 et 310 ; Wettern 1900 et 119 ; Constance 538 et 252 ; Mälaren 1140 et 64. Déterminez le lac le plus grand et le plus petit en superficie, le lac le plus profond et le moins profond.

Tâche 2.3 (2 points).

Créez un tableau « Fleuves d'Europe » en utilisant la longueur (km) et la superficie du bassin (milliers de km²) suivantes : Volga 3 688 et 1 350 ; Danube 2850 et 817 ; Rhin 1330 et 224 ; Elbe 1150 et 148; Vistule 1090 et 198 ; Loire 1020 et 120 ; Oural 2530 et 220 ; Don 1870 et 422 ; Sena 780 et 79 ; Tamise 340 et 15. Déterminez le fleuve le plus long et le plus court, calculez la superficie totale des bassins fluviaux, la longueur moyenne des fleuves dans la partie européenne de la Russie.

Tâche 3 (2 points).

La banque enregistre la ponctualité des remboursements des prêts accordés à plusieurs organismes. Le montant du prêt et le montant déjà payé par l'organisme sont connus. Des pénalités sont prévues pour les débiteurs : si l'entreprise a remboursé le prêt à plus de 70 pour cent, l'amende sera de 10 pour cent du montant de la dette, sinon l'amende sera de 15 pour cent. Calculez l'amende pour chaque organisation, l'amende moyenne, le montant total que la banque va recevoir en plus. Déterminer l'amende moyenne des organismes budgétaires.

Trouver du matériel pour n'importe quelle leçon,

Les outils clients OLAP sont des applications qui calculent des données agrégées (sommes, moyennes, valeurs maximales ou minimales) et les affichent, tandis que les données agrégées elles-mêmes sont contenues dans un cache dans l'espace d'adressage d'un tel outil OLAP.

Si les données sources sont contenues dans un SGBD de bureau, le calcul des données agrégées est effectué par l'outil OLAP lui-même. Si la source des données initiales est un SGBD serveur, de nombreux outils OLAP clients envoient des requêtes SQL contenant l'instruction GROUP BY au serveur et reçoivent par conséquent des données agrégées calculées sur le serveur.

En règle générale, la fonctionnalité OLAP est implémentée dans les outils de traitement de données statistiques (des produits de cette classe, les produits de StatSoft et SPSS sont largement utilisés sur le marché russe) et dans certaines feuilles de calcul. En particulier, Microsoft Excel dispose d'outils d'analyse multidimensionnelle. Avec ce produit, vous pouvez créer et enregistrer sous forme de fichier un petit cube OLAP multidimensionnel local et en afficher des coupes transversales en deux ou trois dimensions.

Modules complémentaires du package d'application Microsoft Office L'exploration de données est un ensemble de fonctions qui donnent accès aux capacités d'exploration et de traitement de données à partir des applications Microsoft Office, permettant ainsi une analyse prédictive sur ordinateur local. Grâce au fait que les services sont intégrés Plateformes Microsoft serveur SQL Grâce aux algorithmes d'exploration et de traitement des données disponibles dans l'environnement d'application Microsoft Office, les utilisateurs professionnels peuvent facilement extraire des informations précieuses à partir d'ensembles de données complexes en quelques clics seulement. Les compléments d'extraction et de manipulation de données Office permettent aux utilisateurs finaux d'effectuer des analyses directement dans Microsoft Excel et Microsoft Visio.

DANS Composition Microsoft Office 2007 comprend trois composants OLAP distincts :

1. Data Mining Client pour Excel vous permet de créer et de gérer des projets d'exploration de données basés sur SSAS à partir d'Excel 2007 ;
2. outils d'analyse de tableaux pour Applications Excel Vous permet d'utiliser les capacités intégrées d'extraction et de traitement des informations de SSAS pour analyser les données stockées dans des feuilles de calcul Excel.
3. Les modèles Visio Data Mining vous permettent de visualiser des arbres de décision, des arbres de régression, des diagrammes de cluster et des réseaux de dépendances dans des diagrammes Visio.

La première interface pour les tableaux croisés dynamiques, également appelés rapports croisés, a été incluse dans Excel en 1993 (Excel version 5.0). Malgré les nombreux utiles Fonctionnalité, il n'est pratiquement pas utilisé dans le travail 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 discute base théorique créer des rapports de synthèse, fournir des recommandations pratiques pour leur utilisation et fournir également un exemple d'accès à des données basées sur plusieurs tableaux.

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 une conversation détaillée révèle généralement que presque tout le monde n’a aucune idée de ce dont il parle. nous parlons de. Autrement dit, les gens pensent à quelque chose de complexe et généralement sans rapport avec leurs activités quotidiennes. En fait, ce n'est pas vrai.

Données multidimensionnelles, mesures

On peut affirmer sans se tromper que les économistes sont presque constamment confrontés à des données multidimensionnelles, mais ils essaient de les présenter sous une forme prédéfinie à l’aide de feuilles de calcul. La multidimensionnalité signifie ici la possibilité de saisir, visualiser ou analyser les mêmes informations avec des modifications. apparence, en utilisant divers regroupements et tris de données. Par exemple, un plan de vente peut être analysé selon les critères suivants :

• types ou groupes de marchandises;
• marques ou catégories de produits ;
• périodes (mois, trimestre, année) ;
• acheteurs ou groupes d'acheteurs;
• régions de vente
• et ainsi de suite.

Chacun des critères ci-dessus est appelé une « dimension » en termes d’analyse de données multidimensionnelles. On peut dire qu'une mesure caractérise une information selon un ensemble spécifique de valeurs. Les « données » constituent un type particulier de mesure d'informations multidimensionnelles. Dans notre exemple, les données du plan de vente pourraient être :

• volume des ventes;
• Prix ​​de vente;
• remise individuelle
• et ainsi de suite.

En théorie, les données peuvent également constituer 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 particulier de valeur.

Ainsi, on peut dire que dans Travaux pratiques les économistes utilisent deux types d’informations : données multidimensionnelles ( nombres réels et planifiés qui ont de nombreuses caractéristiques) et des ouvrages de référence (caractéristiques ou mesures de données).

OLAP

L'abréviation OLAP (Online Analytic Processing) signifie littéralement « traitement analytique en temps réel ». La définition n’est pas très précise ; presque tous les rapports sur n’importe quel produit logiciel peuvent y être résumés. Dans son sens, OLAP implique une technologie permettant de travailler avec des rapports spéciaux, notamment logiciel, pour obtenir et analyser des données structurées multidimensionnelles. L'un des produits logiciels populaires implémentant les technologies OLAP est SQL Server Analysis Server. Certains le considèrent même à tort comme le seul représentant de la mise en œuvre logicielle de ce concept.

Cube de données virtuel

"Cube Virtuel" ( cube multidimensionnel, cube OLAP) est un terme spécial proposé par certains fournisseurs 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 (telles que les rapports récapitulatifs Excel) accèdent aux données dans ces cubes virtuels. De plus, 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 ensemble de données multidimensionnelles spécialement optimisées qui sont utilisées pour créer des rapports de synthèse. Il peut être obtenu soit via un logiciel spécialisé, soit par un simple accès aux tables d'une base de données ou à toute autre source, comme une feuille de calcul Excel.

Tableau croisé dynamique

Le tableau croisé dynamique est une interface utilisateur permettant d'afficher des données multidimensionnelles. À l'aide de cette interface, vous pouvez regrouper, trier, filtrer et réorganiser les données pour obtenir différents échantillons analytiques. La mise à jour du rapport se fait par des moyens simples interface utilisateur, les données sont automatiquement agrégées selon des règles spécifiées, sans nécessiter la saisie supplémentaire ou répétée d'informations. L'interface de tableau croisé dynamique d'Excel est peut-être la plus populaire produit logiciel 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 graphique croisé dynamique.

L'interface de tableau croisé dynamique d'Excel vous permet d'organiser les dimensions des données multidimensionnelles dans une zone de 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 cellules aux champs du tableau croisé dynamique). Un tableau croisé dynamique Excel comporte quatre zones pour afficher des informations : filtre, colonnes, lignes et données. Les dimensions des données sont appelées Champs du tableau croisé dynamique. Ces champs ont leurs propres propriétés et format d'affichage.

Encore une fois, je tiens à souligner que le tableau croisé dynamique Excel est destiné uniquement à l'analyse des données sans possibilité de modifier les informations. Un sens plus proche serait l’utilisation généralisée du terme « rapport pivot », et c’est exactement ainsi que cette interface était appelée jusqu’en 2000. Mais pour une raison quelconque, les développeurs l'ont abandonné dans les versions suivantes.

Modification des tableaux croisés dynamiques

Par sa définition, la technologie OLAP n'implique en principe pas la possibilité de modifier les données source lorsque vous travaillez avec des rapports. Cependant, toute une classe s'est formée sur le marché systèmes logiciels, mettant 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.

Grâce aux outils d'automatisation intégrés d'Excel, vous pouvez résoudre de nombreux problèmes non standard. Un exemple de mise en œuvre de l'édition de tableaux croisés dynamiques Excel basés sur les données d'une feuille de calcul est disponible sur notre site Web.

Préparation de données multidimensionnelles

Venons-en à application pratique Tableaux croisés dynamiques. Essayons d'analyser les données de ventes dans différentes directions. Déposer exemple de tableau croisé dynamique.xls se compose de plusieurs feuilles. Feuille Exemple contient des informations de base sur les ventes pour une certaine période. Pour simplifier l’exemple, nous analyserons un seul indicateur numérique – le volume des ventes en kg. Les dimensions de données clés sont : le produit, l'acheteur et le transporteur (compagnie maritime). De plus, il existe plusieurs dimensions supplémentaires des données qui sont des caractéristiques du produit : type, marque, catégorie, fournisseur, ainsi que l'acheteur : type. Ces données sont collectées sur la feuille Annuaires. En pratique, il peut y avoir beaucoup plus de mesures de ce type.

Feuille Exemple contient remède standard analyse des données – filtre automatique. En regardant l'exemple de remplissage du tableau, il est évident que les données de ventes par date (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 en fonction de 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 les données avec sommation automatique par une clé spécifique (par exemple, par clients). De plus, l'ensemble des dates est fixe, et il ne sera pas possible de visualiser les informations récapitulatives pour une certaine période, par exemple 3 jours, par des moyens automatiques.

De manière générale, la présence d'un emplacement de date prédéfini dans cet exemple est le principal inconvénient du tableau. En disposant les dates en colonnes, nous avons prédéterminé la dimension de ce tableau, nous privant ainsi de la possibilité d'utiliser l'analyse par tableaux croisés dynamiques.

Premièrement, nous devons nous débarrasser de cette lacune - c'est-à-dire supprimer l'emplacement prédéfini de l'une des dimensions des données sources. Exemple de tableau correct - feuille Ventes.

Le tableau a la forme d'un journal de saisie d'informations. Ici, la date est une dimension égale des données. Il convient également de noter que pour les analyses ultérieures dans les tableaux croisés dynamiques, la position relative des lignes les unes par rapport aux autres (c'est-à-dire le tri) est totalement indifférente. Les enregistrements dans les bases de données relationnelles ont ces propriétés. L'interface des tableaux croisés dynamiques est principalement destinée à analyser de gros volumes de bases de données. Par conséquent, vous devez respecter ces règles lorsque vous travaillez avec une source de données sous forme de plages de cellules. Dans le même temps, personne n'interdit l'utilisation des outils de l'interface Excel dans son travail - les tableaux croisés dynamiques analysent uniquement les données et le formatage, les filtres, les regroupements et le tri des cellules source peuvent être arbitraires.

Du filtre automatique au rapport récapitulatif

Théoriquement, il est déjà possible de réaliser une analyse en trois dimensions à partir des données de la fiche Ventes : marchandises, clients et transporteurs. Il n'y a pas de données sur les propriétés des produits et des clients sur cette fiche, ce qui ne permettra donc pas de les présenter dans le tableau récapitulatif. En mode normal de création d'un tableau croisé dynamique pour la source Données Excel ne vous permet pas de lier les données de plusieurs tables à l'aide de certains champs. Vous pouvez contourner cette limitation logiciel– voir l’exemple de supplément à cet article sur notre site Internet. Afin de ne pas recourir à des méthodes logicielles de traitement de l'information (d'autant plus qu'elles ne sont pas universelles), vous devez ajouter des caractéristiques supplémentaires directement dans le formulaire d'écriture au journal - voir la feuille SalesAnalysis.

L'utilisation des fonctions RECHERCHEV permet de compléter facilement les données originales avec les caractéristiques manquantes. Désormais, grâce à AutoFilter, vous pouvez analyser les données dans différentes dimensions. Mais le problème des regroupements reste entier. Par exemple, suivre le montant uniquement par marque pour certaines dates est assez problématique. Si tu te limite Formules Excel, vous devez alors créer des échantillons supplémentaires à l'aide de la fonction SUMIF.

Voyons maintenant quelles fonctionnalités offre l'interface du tableau croisé dynamique. Sur une feuille RésuméAnalyse construit plusieurs rapports basés sur une plage de cellules avec des données de feuille Analyse des ventes.

Le premier tableau d'analyse a été construit via l'interface Excel 2007 Ruban\Insertion\Tableau croisé dynamique(dans le menu Excel 2000-2003 Données\Tableau croisé dynamique).

Les deuxième et troisième tables ont été créées par copie et configuration 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 sources, puis vous devez mettre à jour les données du rapport récapitulatif.

De notre point de vue, les avantages en matière de visibilité de l'information sont évidents. Vous pouvez échanger des filtres, des colonnes et des lignes et masquer certains groupes de valeurs de toutes dimensions, utiliser le glisser-déposer manuel et le tri automatique.

Propriétés et formatage

En plus d'afficher directement les données, il existe un large éventail d'options pour afficher l'apparence des tableaux croisés dynamiques. Vous pouvez masquer les données inutiles à l'aide de filtres. Pour un seul élément ou champ, il est plus facile d'utiliser l'élément de menu contextuel Supprimer(dans la version 2000-2003 Cacher).

Il est également conseillé de paramétrer l'affichage des autres éléments du tableau croisé dynamique non pas via le formatage des cellules, mais via le paramétrage d'un champ ou d'un élément du tableau croisé dynamique. Pour ce faire, vous devez déplacer le pointeur de la souris sur l'élément souhaité, attendre qu'une forme de curseur spéciale (en forme de flèche) apparaisse, puis sélectionner 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 :

De plus, Excel 2007 a introduit de nombreux styles d'affichage de tableau croisé dynamique prédéfinis :

Notez que les filtres de contrôle et les zones de déplacement sont actifs dans le graphique.

Accès aux données externes

Comme nous l'avons déjà noté, le plus grand effet de l'utilisation des tableaux croisés dynamiques peut être obtenu lors de l'accès aux données. 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 possèdent également une structure relationnelle prédéfinie 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 plus grand 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 les prétraiter dans des tableaux croisés dynamiques.

Le traitement analytique en ligne (OLAP) est une technologie utilisée pour organiser de grandes bases de données commerciales et prendre en charge la business intelligence. 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 s'adapter à la façon dont les données sont récupérées 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 aurez besoin.

Dans cet article

Qu’est-ce que l’analyse commerciale ?

Un analyste commercial souhaite souvent avoir une vue d’ensemble de l’entreprise pour visualiser les tendances plus larges sur la base de données agrégées, ainsi que les tendances décomposées en un certain nombre 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 produire des informations qui peuvent être utilisées pour prendre des décisions commerciales éclairées et entreprendre des actions. Par exemple, en utilisant OLAP et l'analyse commerciale, vous pouvez répondre aux questions suivantes sur les données commerciales.

Comment les ventes totales de tous les produits en 2007 se comparent-elles aux ventes depuis 2006 ?

Comment cela se compare-t-il à la date et à l'heure par période de prestations des cinq dernières années ?

Combien d’argent les clients ont-ils dépensé pour 35 l’année dernière et comment ce comportement a-t-il changé 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é (en pourcentage de marge et totale) par catégorie de produits ?

Recherchez les meilleurs et les moins bons 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 de business intelligence. 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 de traitement de transactions en ligne (OLTP), 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 dans des structures permettant une analyse complexe. Les données OLAP sont également organisées hiérarchiquement et stockées dans des cubes plutôt que dans des tableaux. Il s'agit d'une technologie complexe qui utilise des structures multidimensionnelles pour fournir accès rapide aux données pour analyse. Dans cette organisation, un rapport de tableau croisé dynamique ou de graphique croisé dynamique peut facilement afficher des données récapitulatives de haut niveau, telles que les totaux des ventes pour l'ensemble d'un pays ou d'une région, et également afficher des informations sur les sites où les ventes sont particulièrement fortes ou faibles.

Les bases de données OLAP sont conçues pour accélérer le chargement des données. Parce que c'est un serveur OLAP, pas Microsoft Bureau Excel, calcule des valeurs agrégées, nécessitant l'envoi de données plus petites à Excel lors de la création ou de la modification d'un rapport. Cette approche vous permet de travailler avec plus 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 agrégées.

Les bases de données OLAP contiennent deux principaux types de données : les mesures, qui sont des données numériques, des quantités et des moyennes utilisées pour prendre des décisions commerciales éclairées, et les dimensions, qui sont des catégories utilisées pour organiser ces mesures. Les bases de données OLAP vous aident à organiser les données selon plusieurs niveaux de détail, en utilisant les mêmes catégories que vous connaissez pour analyser les données.

Les sections suivantes décrivent chaque composant en détail ci-dessous.

Cubique Structure de données qui regroupe les mesures en niveaux et hiérarchies de 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 données récapitulatives telles que les ventes et les stocks. Les cubes ne sont pas des « Cubes » au sens mathématique strict, puisqu’ils n’ont pas nécessairement les mêmes côtés. Cependant, ils représentent une métaphore appropriée pour un concept complexe.

Des mesures Ensemble de valeurs dans un cube basées sur une colonne de la table de faits du cube et qui est généralement une valeur numérique. Les mesures sont les valeurs centrales d'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 dépenses.

MembreÉ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 année d'une dimension temporelle, tandis que janvier représente des membres non uniques au niveau mois car il y a plusieurs mois de janvier dans une dimension temporelle car elle contient des données sur plus d'un an.

Élément calculé Membre de dimension dont la valeur est calculée au moment de l'exécution à l'aide d'une expression. Les valeurs des membres calculés peuvent être dérivées des valeurs des autres membres. Par exemple, un élément calculé, le profit, peut être déterminé en soustrayant la valeur de l’élément, plus les coûts, de la valeur de l’élément, les ventes.

la mesure Ensemble d'une ou plusieurs hiérarchies ordonnées de niveaux de cube que l'utilisateur comprend et utilise comme base pour l'analyse des données. Par exemple, une dimension géographique peut inclure les niveaux du pays/région, de l’état/région et de la ville. De plus, une dimension temporelle peut inclure une hiérarchie avec des niveaux d'année, de trimestre, de mois et de jour. Dans un rapport de tableau croisé dynamique ou 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 Structure arborescente logique qui organise les membres d'une dimension de sorte que chaque membre ait un membre 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 temporelle contenant les niveaux trimestre, mois et jour, janvier est un enfant de Qtr1. Un élément parent est un membre de niveau inférieur dans une hiérarchie directement liée au membre actuel. La valeur parent est généralement la consolidation des valeurs de tous les éléments enfants. Par exemple, dans une hiérarchie temporelle 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 jours dans une hiérarchie temporelle.

Fonctions OLAP dans Excel

Récupération de données OLAP Vous pouvez vous connecter aux sources de données OLAP de la même manière que vous vous connectez à d'autres sources de données externes. Vous pouvez travailler avec des bases de données créées à l'aide de Microsoft SQL Server OLAP Services version 7.0, Microsoft SQL Server Analysis Services version 2000 et Microsoft SQL Server Analysis Services version 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 dans une fonction de feuille de calcul convertie à partir d'un rapport de tableau croisé dynamique, mais pas sous forme de plage de données externe. Vous pouvez enregistrer des rapports de tableau croisé dynamique et de graphique croisé dynamique dans des modèles de rapport et 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 un fichier ODC dans Excel, vous voyez un rapport de tableau croisé dynamique vierge prêt à être placé.

Création de fichiers cube pour une utilisation hors ligne Vous pouvez créer un fichier de cube autonome (.cub) avec un sous-ensemble des données de la base de données du 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 de graphique croisé dynamique que vous ne le feriez autrement et obtenir des données plus rapidement. Vous pouvez créer des fichiers de cube uniquement si vous utilisez un fournisseur OLAP, tel que Microsoft SQL Analysis Services version 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 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 et l'exploration côté serveur, mais ne prend pas en charge ses propres instructions et ensembles de données natifs.

KPI Un indicateur de performance clé est une mesure calculée spéciale définie sur le serveur qui vous permet de suivre les « indicateurs de performance clés », y compris l'état (la valeur actuelle correspond à un nombre spécifique). et tendance (valeurs au fil du temps). Lorsqu'ils sont affichés, le serveur peut envoyer des icônes correspondantes, similaires à la nouvelle icône Excel, pour s'aligner au-dessus ou en dessous des niveaux d'état (par exemple, pour une icône d'arrêt), ainsi que faire tourner une valeur vers le haut ou vers le bas (par exemple, une icône de flèche directionnelle).

Formatage sur le serveur Les administrateurs de cube peuvent créer des mesures et des membres calculés à l'aide de règles de mise en forme des couleurs, de mise en forme des polices et de mise en forme conditionnelle qui peuvent être attribuées en tant que règle métier standard de l'entreprise. Par exemple, le format du serveur pour les revenus peut être un format de chiffre monétaire, 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érieur ou égal à 30 000 et si la valeur est positive - ordinaire. supérieur ou égal à 30 000. Trouvez plus d’informations.

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 les utilisateurs qui doivent 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 les paramètres régionaux et le pays de l'ordinateur 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 Microsoft OLAP Excel comprend un pilote de source de données et un logiciel client pour accéder aux bases de données créées avec les services olap de Microsoft SQL Server version 7.0, la version 2000 (8.0) de Microsoft SQL Server olap et les services d'analyse Microsoft SQL Server 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 fonctionnalités d'Excel afin de travailler avec des données OLAP, 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, voir administrateur du système ou votre fournisseur de produits OLAP.

Bases de données serveur et fichiers 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 récupérer les données source directement à partir de celle-ci. Si vous disposez d'un fichier de cube autonome contenant des données OLAP ou d'un fichier de définition de cube, vous pouvez vous connecter à ce fichier et en extraire les données source.

Les sources de données Une 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 ligne. 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 à Excel sous forme de rapport de tableau croisé dynamique ou de graphique croisé dynamique, ou dans une fonction de feuille de calcul convertie à partir d'un rapport de tableau croisé dynamique.

Requête MicrosoftÀ l'aide de 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 les données d'un tableau croisé dynamique OLAP associé à un fichier cube. Informations Complémentaires .

Différences entre les fonctionnalités des données sources OLAP et non OLAP

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 constaterez certaines différences de fonctionnalités.

Extraction de données Le serveur OLAP renvoie de nouvelles données à Excel chaque fois que la présentation du rapport change. Avec d'autres types de sources de données externes, vous interrogez toutes les données sources en même temps ou vous pouvez spécifier les paramètres à interroger uniquement lors de l'affichage de différents éléments de champ de filtre de rapport. De plus, vous disposez de plusieurs autres options pour mettre à jour votre rapport.

Dans les rapports basés sur des données sources OLAP, les options de champ de filtre de rapport ne sont pas disponibles, la requête en arrière-plan n'est pas disponible et l'option d'optimisation de la mémoire n'est pas disponible.

Note: 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 sources 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 sources, tous les champs peuvent être utilisés dans n'importe quelle partie du 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 récapitulatives, de sorte que les enregistrements détaillés qui composent les valeurs récapitulatives peuvent ne pas être disponibles. Toutefois, le serveur peut fournir des champs de propriétés que vous pouvez afficher. Les autres types de données sources n'ont pas de champs de propriétés, mais vous pouvez afficher des informations de base sur les champs de données et les valeurs des éléments, ainsi que des éléments sans données.

Les champs de filtre de rapport OLAP peuvent ne pas avoir Tous les éléments et l'équipe Afficher les pages de filtre du rapport pas disponible.

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 sources, les éléments du nouveau rapport sont d'abord triés par nom d'élément par 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 d'autres types de données sources, vous pouvez modifier la fonction d'agrégation pour un champ de valeur et utiliser plusieurs fonctions récapitulatives 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 contenant des données source OLAP.

Sous-totaux Dans les rapports contenant des données source OLAP, vous ne pouvez pas modifier la fonction récapitulative des sous-totaux. Avec d'autres types de données sources, vous pouvez modifier les fonctions de 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 membres masqués lors du calcul des sous-totaux et des totaux généraux. Pour d'autres types de données sources, vous pouvez inclure les éléments masqués des champs de filtre de rapport dans les sous-totaux, mais les éléments masqués dans d'autres champs seront exclus par défaut.