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

Si les données source 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 source est un SGBD serveur, de nombreux outils OLAP clients envoient des requêtes SQL contenant la clause GROUP BY au serveur et, par conséquent, reçoivent 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 aux Marché russe Les produits StatSoft et SPSS sont largement utilisés) et dans certaines feuilles de calcul. En particulier, les moyens d'analyse multivariée ont Microsoft Excel. Avec ce produit, vous pouvez créer et enregistrer sous forme de fichier un petit cube OLAP multidimensionnel local et afficher ses sections 2D ou 3D.

Modules complémentaires du package d'application Microsoft Office pour l'extraction et le traitement des données sont un ensemble de fonctions qui donnent accès aux capacités d'extraction et de traitement des données des applications Microsoft Office, et permettent ainsi une analyse prédictive sur ordinateur local. En raison du fait que les services intégrés Plateformes Microsoft Les algorithmes d'extraction et de traitement de données SQL Server sont disponibles à partir de l'environnement d'application Microsoft Office, les utilisateurs professionnels peuvent facilement extraire des informations précieuses à partir de ensembles complexes données en quelques clics. Le complément Data Extraction and Processing Office Suite permet aux utilisateurs finaux d'effectuer des analyses directement dans les applications Microsoft Excel et Microsoft Visio.

Microsoft Office 2007 comprend trois composants OLAP distincts :

1. Le client d'extraction et de traitement de données pour Excel vous permet de créer et de gérer des projets d'extraction et de traitement de données basés sur SSAS à partir d'Excel 2007 ;
2. outils d'analyse de table pour Applications Excel vous permettre d'utiliser les fonctions intégrées d'extraction et de traitement des informations dans SSAS pour analyser les données stockées dans des feuilles de calcul Excel ;
3. Les modèles d'extraction et de manipulation de données pour l'application Visio vous permettent de visualiser des arbres de décision, des arbres de régression, des diagrammes de cluster et des réseaux de dépendance dans des diagrammes Visio.

Tableau 1.1. Produits Oracle pour OLAP et Business Intelligence

Type de fonds	Produit

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

Termes de l'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 sans rapport avec leurs activités quotidiennes. En fait, ce n'est pas le cas.

Données multidimensionnelles, dimensions

On peut dire sans risque de se tromper que les économistes traitent presque tout le temps de données multidimensionnelles, mais ils essaient de les présenter d'une manière 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 apparence, en appliquant 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
• etc.

Chacun des critères ci-dessus en termes d'analyse de données multivariées est appelé une "dimension". On peut dire qu'une dimension caractérise les informations sur un certain ensemble de valeurs. Un type spécial de mesure d'informations multidimensionnelles est les "données". Dans notre exemple, les données du plan de vente peuvent être :

• volume des ventes ;
• Prix ​​de vente;
• remise individuelle
• etc.

Théoriquement, les données peuvent également être une dimension standard des informations multidimensionnelles (par exemple, vous pouvez regrouper les données par prix de vente), mais les données sont généralement un type spécial 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 avec de nombreuses fonctionnalités) et répertoires (caractéristiques ou mesures des données).

OLAP

L'abréviation OLAP (traitement analytique en ligne) en traduction littérale ressemble à "traitement analytique en temps réel". La définition n'est pas très précise ; presque n'importe quel rapport de n'importe quel produit logiciel peut y être résumé. Par définition, OLAP implique une technologie pour travailler avec des rapports spéciaux, y compris des logiciels, pour obtenir et analyser des données structurées multidimensionnelles. L'un des produits logiciels populaires qui implémentent les technologies OLAP est SQL Server Analysis Server. 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 fournisseurs de Logiciel. 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 de synthèse Excel) accèdent aux données de ces cubes virtuels. Dans le même temps, l'utilisation d'un tel stockage dédié n'est nullement nécessaire pour le traitement d'informations multidimensionnelles. En général, cube virtuel- il s'agit d'un tableau de données multidimensionnelles spécialement optimisées qui est utilisé pour créer des rapports de synthèse. Il peut être obtenu à la fois par des outils logiciels spécialisés et par un simple accès aux tables de la base de données ou à toute autre source, telle qu'un tableur Excel.

tableau croisé dynamique

"Pivot Report" (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 changer l'emplacement des données afin d'obtenir divers échantillons analytiques. Le rapport est mis à jour à l'aide d'outils d'interface utilisateur simples, les données sont automatiquement agrégées selon les règles spécifiées, et aucune information supplémentaire ou ressaisie n'est requise. L'interface de tableau croisé dynamique Excel est peut-être le produit 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 forme graphique afficher des données multidimensionnelles - Pivot Chart.

L'interface de tableau croisé dynamique implémentée dans Excel vous permet d'organiser les dimensions des données multidimensionnelles dans la zone de la 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 d'affichage : 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é uniquement à l'analyse des données sans possibilité d'édition des informations. Plus proche dans le sens serait l'utilisation généralisée du terme "rapport pivot" (Pivot Report), et c'est ainsi que cette interface s'appelait jusqu'en l'an 2000. Mais pour une raison quelconque, dans les versions suivantes, les développeurs l'ont abandonné.

Modification des tableaux croisés dynamiques

Par définition, la technologie OLAP, en principe, n'implique pas la possibilité de modifier les données source lorsque vous travaillez avec des rapports. Cependant, toute une classe de systèmes logiciels, réalisant les possibilités d'analyse et d'édition directe des données dans des tableaux multidimensionnels. Fondamentalement, ces systèmes sont axés sur la résolution de problèmes de budgétisation.

À l'aide des outils d'automatisation intégrés d'Excel, vous pouvez résoudre de nombreuses tâches non standard. Un exemple d'implémentation de l'édition pour les tableaux croisés dynamiques Une feuille de calcul de données basée sur Excel peut être trouvée sur notre site Web.

Préparation des données multidimensionnelles

Abordons l'application pratique des tableaux croisés dynamiques. Essayons d'analyser les données de ventes dans différentes directions. Dossier 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 le seul indicateur numérique - le volume des ventes en kg. Les variables de données clés suivantes sont disponibles : produit, acheteur et transporteur ( compagnie de transport). De plus, il existe plusieurs dimensions supplémentaires de données qui sont des attributs 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 telles mesures.

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 dates (elles sont disposées en colonnes) se prêtent à une analyse normale. De plus, en utilisant le 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'est pas possible de regrouper des données avec une synthèse automatique par une clé spécifique (par exemple, par clients). De plus, l'ensemble de dates est fixe et affiche les informations récapitulatives pour une certaine période, par exemple 3 jours, par des moyens automatiqueséchouer.

En général, la présence d'un emplacement de date prédéfini dans cet exemple est le principal inconvénient de la table. En disposant les dates par colonnes, on a en quelque sorte prédéterminé la dimension de ce tableau, se 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 source. Un exemple de tableau valide est une feuille Ventes.

Le tableau se présente sous la forme d'un journal de saisie des informations. Ici, la date est une dimension de données égale. 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 (autrement dit, le tri) est totalement indifférente. Les enregistrements dans les bases de données relationnelles ont ces propriétés. C'est l'analyse de grands volumes de bases de données qui se concentre principalement sur 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 d'utiliser les outils d'interface Excel au travail - les tableaux croisés dynamiques n'analysent que les données, et le formatage, les filtres, le regroupement et le tri des cellules source peuvent être arbitraires.

Du filtre automatique au rapport de synthèse

Théoriquement, sur les données de la fiche Ventes, il est déjà possible d'analyser en trois dimensions : marchandises, acheteurs et transporteurs. Il n'y a pas de données sur les propriétés des produits et des acheteurs sur cette fiche, ce qui, par conséquent, ne permettra pas de les afficher dans le tableau récapitulatif. Dans le mode normal de création d'un tableau croisé dynamique pour les données source, Excel ne vous permet pas de lier les données de plusieurs tables par certains champs. Vous pouvez contourner cette limitation outils logiciels- cm. complément exempleà cet article sur notre site Web. Afin de ne pas recourir à des méthodes de traitement de l'information par programme (d'autant plus qu'elles ne sont pas universelles), vous devez ajouter caractéristiques supplémentaires directement au formulaire d'écriture au journal - voir la feuille SalesAnalysis.

L'utilisation des fonctions VLOOKUP permet de compléter facilement les données d'origine avec des caractéristiques manquantes. Maintenant, en appliquant le filtre automatique, vous pouvez analyser les données dans dimensions différentes. Mais le problème des regroupements reste entier. Par exemple, suivre le montant uniquement pour les marques à certaines dates est assez problématique. Si vous êtes limité aux formules Excel, vous devez créer des exemples supplémentaires à l'aide de la fonction SUMIF.

Voyons maintenant quelles fonctionnalités l'interface du tableau croisé dynamique offre. Sur la feuille Analyse de code construit plusieurs rapports basés sur 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 le menu Excel 2000-2003 Données\Tableau croisé dynamique).

Les deuxième et troisième tables sont créées 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 récapitulatifs.

De notre point de vue, les avantages dans la visibilité de l'information sont évidents. Vous pouvez échanger des filtres, des colonnes et des lignes, masquer certains groupes de valeurs de n'importe quelle dimension, utiliser le glisser-déposer manuel et le tri automatique.

Propriétés et formatage

Outre 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 de menu contextuel Effacer(dans la version 2000-2003 Cacher).

Il est également souhaitable de définir l'affichage d'autres éléments du tableau croisé dynamique non pas via le formatage des cellules, mais via la définition du champ ou de 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 de curseur spéciale (sous la forme d'une flèche), puis sélectionnez l'élément sélectionné d'un simple clic. Après la sélection, vous pouvez changer la vue à travers le ruban, menu contextuel ou appelez 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 glissement sont actifs dans le graphique.

Accès aux données externes

Comme indiqué précédemment, l'effet le plus important de l'utilisation de tableaux croisés dynamiques peut être obtenu lors de l'accès à des données provenant 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 une structure relationnelle prédéfinie, 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 plus grand effet de l'utilisation de sources d'information externes peut être obtenu en utilisant des outils d'automatisation ( Programmes VBA) tant pour l'acquisition des données que pour leur pré-traitement dans des tableaux croisés dynamiques.

Les problématiques d'analytique, d'OLAP, d'entrepôts de données intéressent de plus en plus les informaticiens russes. À ce jour, notre presse informatique et Internet ont publié beaucoup de bons documents académiques dans la manière de présenter des documents sur ce sujet, y compris des documents d'introduction. Nous portons à votre attention un article dans lequel nous essayons délibérément d'expliquer OLAP "sur les doigts", à l'aide d'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 en première approximation, "sur les doigts", peut être défini comme une manière particulière d'analyser les données et de générer des rapports. Son essence est de fournir à l'utilisateur un tableau multidimensionnel qui résume automatiquement les données dans différentes sections et vous permet de gérer de manière interactive les calculs et la forme du rapport. Cet article parlera de la technologie et des opérations de base d'OLAP en utilisant l'exemple de l'analyse des factures d'une entreprise engagée dans le commerce de gros de produits alimentaires.

*une. OLAP - Traitement analytique en ligne, analyse de données opérationnelles.

En tant qu'outil, un système OLAP de la classe la plus simple et la moins coûteuse, un client OLAP *1, sera considéré. Par exemple, le produit le plus simple parmi le nombre de clients OLAP est choisi - «Contour Standard» d'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 homologues anglais.)

*une. Pour plus d'informations sur la classification des systèmes OLAP, consultez l'article "OLAP Made in Russia" dans PC Week/RE, n° 3/2001.

Commençons donc avec le système. Vous devez d'abord décrire la source de données (source de données) - le chemin d'accès à la table et ses champs. C'est la tâche de 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 vue de table ou de serveur SQL 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, un échantillon (jeu de résultats ou requête) est alors créé, dans lequel sont configurés : l'algorithme de combinaison des tables par champs clés, les conditions de filtrage et l'ensemble des champs retournés. Appelons notre sélection « Factures » et plaçons-y tous les champs de la source de données « Factures ». Ainsi, un informaticien, en créant une 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. Tout d'abord, les champs d'un échantillon de données plat sont divisés en deux groupes - les faits (faits ou mesures) et les mesures (dimensions). Les faits sont des chiffres, et les mesures sont des "coupures" dans lesquelles les faits seront résumés. Dans notre exemple, les dimensions seront: "Région", "Ville", "Client", "Produit", "Date", et le fait sera un - le champ "Montant" de la facture. En effet, un ou plusieurs algorithmes d'agrégation doivent être sélectionnés. OLAP est capable non seulement de résumer les résultats, mais aussi d'effectuer des calculs plus complexes, jusqu'à l'analyse statistique. La sélection de plusieurs algorithmes d'agrégation créera des faits virtuels calculés. Dans l'exemple, un algorithme d'agrégation est sélectionné - "Somme".

Une particularité des systèmes OLAP est la génération de dimensions et de données pour des périodes plus anciennes à partir d'une 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. Nommons le rapport "Analyse des ventes" et enregistrons-le. Le travail de création de l'interface de l'application analytique est terminé.

Désormais, lors de l'exécution de 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 article, client et période.

Afin de rendre les manipulations avec les 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 déplacer, supprimer, filtrer et effectuer d'autres opérations OLAP. Le tableau calcule 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 obtiendrons un rapport de comparaison - "Comparaison des volumes de ventes de produits pour l'année". Pour agréger les données d'une année, faites simplement glisser les colonnes "Trimestre" et "Mois" vers le haut du tableau - "zone de mesures inactives". Les dimensions « Trimestre » et « Mois » transférées dans cette zone seront fermées (opération « fermer la dimension »), 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, vous pouvez définir des années, des trimestres et des mois spécifiques pour le filtrage des données (opération "filtre").

Pour plus de clarté, changeons le type de graphique illustrant la table OLAP et son emplacement à l'écran.

L'exploration des données (opération "drill down") vous 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 ses volumes de vente par régions. Après avoir ouvert la région de l'Oural, nous obtiendrons des volumes de ventes dans le contexte des villes de la région de l'Oural, en approfondissant les données sur Ekaterinbourg, nous pourrons afficher 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 à Iekaterinbourg, définissons des filtres pour 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 baisses et la croissance des ventes de biens dans différentes villes.

Ainsi, nous étions convaincus que la technologie OLAP permet à l'utilisateur de publier des dizaines de types de rapports divers à partir d'une seule interface, en gérant 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 déclaration, mais la mise en place 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 naturelle et un développement de l'idée des feuilles de calcul. Essentiellement, l'interface visuelle OLAP est également une feuille de calcul, mais équipée 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, la millionième armée de « cols blancs », qui font confiance aux feuilles de calcul, maîtrise également rapidement les outils OLAP. Pour eux, il s'agit d'une « révolution de velours » qui offre de nouvelles opportunités, mais n'implique pas la nécessité de se reconvertir.

Si le lecteur, après avoir lu cet article, n'a pas perdu l'intérêt pour 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 Intersoft lab - www.iso.ru. À partir de là, vous pouvez également télécharger une version de démonstration du système Kontur Standard avec l'exemple décrit dans l'article.

Dans un tableau croisé dynamique standard, les données source sont stockées sur le disque dur local. De cette façon, vous pouvez toujours les gérer et les réorganiser, même si vous n'avez pas 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 de réseau local votre tableau croisé dynamique échouera. Vous ne pourrez déplacer aucun des champs qu'il contient.

Si vous avez encore besoin d'analyser des données OLAP après être passé hors ligne, créez un cube de données hors ligne. Le cube de données hors ligne est fichier séparé, qui est un cache de tableau croisé dynamique qui stocke les données OLAP qui sont affichées après avoir été déconnectées du réseau local. Les données OLAP copiées dans un tableau croisé dynamique peuvent être imprimées, le site http://everest.ua décrit cela en détail.

Pour créer un cube de données autonome, créez d'abord un tableau croisé dynamique OLAP. Placez le curseur dans le tableau croisé dynamique et cliquez sur le bouton Outils OLAP de l'onglet contextuel Outils, qui fait partie du groupe d'onglets contextuels Outils de tableau croisé dynamique. Sélectionnez la commande OLAP hors ligne (Fig. 9.8).

Riz. 9.8. Créer un cube de données hors ligne

La boîte de dialogue Paramètres de cube hors ligne apparaîtra à l'écran. Données OLAP. Cliquez sur le bouton Créer un fichier de données hors ligne. Vous avez démarré l'assistant de création de fichier de cube de données. Cliquez sur le bouton Suivant pour poursuivre la procédure.

Vous devez d'abord 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 qui seront importées de la base de données OLAP. L'idée est de spécifier uniquement 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 ligne sera grand.

Cliquez sur le bouton Suivant pour passer à la boîte de dialogue suivante de l'assistant. Il vous donne la possibilité de spécifier des membres ou des éléments de données qui ne seront pas inclus dans le cube. En particulier, vous n'aurez pas besoin de la mesure Internet Sales-Extended Amount, elle sera donc décochée dans la liste. Une case décoché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, spécifiez 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

Au bout d'un moment, Excel enregistre 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é au 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. Principe de base indique que le cube de données hors ligne n'est utilisé que pour fonctionner lorsque le réseau local est éteint, mais il est obligatoirement mis à jour après la restauration de la connexion. Toute tentative de mise à jour d'un cube de données hors ligne après l'interruption de la connexion échouera.

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 faire partie de la base de données OLAP sur le serveur OLAP ou être générées indépendamment de la 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.

Remarque de sécurité : Soyez prudent lorsque vous utilisez ou distribuez un fichier de cube hors ligne contenant des informations sensibles ou personnelles. Au lieu d'un fichier de cube, il est recommandé de stocker les données dans un classeur afin de pouvoir contrôler l'accès aux données à l'aide de la gestion des droits. Informations Complémentaires se trouve dans 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 connexion pour copier les données source dans un fichier de cube hors connexion distinct sur votre ordinateur. La création de ces fichiers hors connexion nécessite un fournisseur de données OLAP prenant en charge cette fonctionnalité, tel que MSOLAP de Microsoft SQL Server Analysis Services, installé sur l'ordinateur.

Noter: La création et l'utilisation de fichiers de cube hors ligne à partir de Microsoft SQL Server Analysis Services sont soumises à des conditions et à une licence Installations Microsoft Serveur SQL. Passez en revue les informations de licence de l'édition SQL Server pertinentes.

Utilisation de l'assistant de cube hors ligne

Pour créer un fichier de cube hors ligne, vous pouvez sélectionner un sous-ensemble de données dans une base de données OLAP à l'aide de l'assistant de cube hors ligne, 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 dimension, 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 stockés dans le fichier hors ligne.

Transfert de 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 autonome à partir du rapport. Après cela, vous pouvez basculer le rapport entre la base de données du serveur et le fichier hors ligne à tout moment. Par exemple, si vous utilisez portable pour les déplacements à domicile et vidéo, puis reconnectez votre ordinateur au réseau.

Voici les principales étapes à suivre pour vie de la batterie avec les données, puis retransférez les données sur Internet.

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 ligne sur votre ordinateur. Au chapitre Créer un fichier de cube hors ligne à partir d'une base de données de serveur OLAP(ci-dessous dans cet article).

Déconnexion du réseau et utilisation d'un fichier de cube hors ligne.

Connectez-vous et rattachez le fichier de cube hors ligne. Consultez la rubrique Reconnexion du fichier de cube hors ligne à la base de données du serveur OLAP(ci-dessous dans cet article).

Mettez à jour le fichier de cube hors ligne avec de nouvelles données et régénérez le fichier de cube hors ligne. Consultez la rubrique mettre à jour et recréer le fichier de cube hors ligne(ci-dessous dans cet article).

BLOG

Uniquement des messages de qualité

Que sont les tableaux croisés dynamiques Excel et les cubes OLAP

Regardez la vidéo de l'article :

OLAP- c'est Anglais. traitement analytique en ligne, technologie de traitement analytique des données en temps réel. en langage clair- 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 - PivotTables.

Tableaux croisés dynamiques est une interface utilisateur pour afficher des données multidimensionnelles. En d'autres termes - un type spécial de tableaux, avec lesquels vous pouvez faire presque n'importe quel rapport.

Pour clarifier les choses, comparons le "tableau régulier" avec le "tableau croisé dynamique"

Tableau régulier :

Tableau croisé dynamique :

Différence principale Tableaux croisés dynamiques est la présence d'une fenêtre Liste des champs du tableau croisé dynamiqueà partir de laquelle vous pouvez choisir Champs obligatoires et recevez n'importe quelle table automatiquement !

Comment utiliser

Ouvrez un fichier Excel connecté à un cube OLAP, tel que "BIWEB" :

Maintenant, qu'est-ce que cela signifie et comment l'utiliser ?

Faites glisser et déposez les champs requis pour obtenir, par exemple, le tableau suivant :

« Avantages» vous permettent d'explorer le rapport. Dans cet exemple, 'Marque' est détaillé jusqu'à 'Noms abrégés' et 'Trimestre' jusqu'à 'Mois', c'est-à-dire Alors:

Fonctions analytiques dans Excel (fonctions de cube)

Microsoft ajoute constamment de nouvelles fonctionnalités à Excel en termes d'analyse et de visualisation des données. L'utilisation d'informations dans Excel peut être représentée par trois couches relativement indépendantes :

• données sources "correctement" organisées
• mathématiques (logique) de l'informatique
• présentation des données

Riz. 1. Analyse des données dans Excel : a) données brutes, b) mesure dans Power Pivot, c) tableau de bord ; Pour agrandir une image, faites un clic droit dessus et sélectionnez Ouvrir l'image dans un nouvel onglet

Télécharger la note au format Word ou pdf, exemples au format Excel

Fonctions de cube et tableaux croisés dynamiques

Les tableaux croisés dynamiques sont les moyens les plus simples et en même temps très puissants de présenter des données. Ils peuvent être créés à partir de données contenues dans : a) une feuille de calcul 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 de cube) pour générer un rapport sur une feuille Excel. Les tableaux croisés dynamiques sont plus faciles. Les fonctions de cube sont plus complexes, mais offrent plus de flexibilité, notamment dans les rapports, elles sont donc largement utilisées dans les tableaux de bord.

La discussion suivante s'applique aux formules de cube et aux tableaux croisés dynamiques basés sur le modèle Power Pivot et, dans quelques cas, basés sur des cubes OLAP.

Un moyen facile d'obtenir les fonctions des cubes

Lorsque (si) vous avez commencé à apprendre le code VBA, vous avez appris que le moyen le plus simple d'obtenir le code est d'enregistrer une macro. De plus, le code peut être modifié, des boucles, des vérifications peuvent être ajoutées, etc.. De même, le moyen le plus simple d'obtenir un ensemble de fonctions de cube consiste à convertir le tableau croisé dynamique (Fig. 2). Tenez-vous sur n'importe quelle cellule du tableau croisé dynamique, allez dans l'onglet Une analyse, cliquez sur le bouton Fonds OLAP, et appuyez sur Convertir en formules.

Riz. 2. Conversion d'un tableau croisé dynamique en un ensemble de fonctions de cube

Les nombres seront enregistrés et il ne s'agira pas de valeurs, mais de formules qui extraient des données du modèle de données Power Pivot (Fig. 3). Vous pouvez formater le tableau résultant. En particulier, 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.

Riz. 3. Tableau basé sur des formules de cube

Fonction VALEURCUBE()

C'est peut-être la fonction principale des cubes. Elle équivaut à l'aire 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 d'un tableau croisé dynamique. Cela signifie que vous n'êtes pas limité par les 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 saisie dans la cellule C10 (Fig. 4) :

Riz. 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

Petite astuce. 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 fenêtre Excel. Pour cela, cliquez sur l'icône Rouler dans la fenêtre situé dans le coin supérieur droit de l'écran. Et puis ajustez la taille de la fenêtre horizontalement. Option alternative– forcer le texte de la formule à s'enrouler nouvelle ligne. Pour cela, dans la barre de formule, placez le curseur à l'endroit où vous souhaitez effectuer un virement et appuyez sur Alt + Entrée.

Riz. 5. Réduire la fenêtre

Syntaxe de la fonction VALEURCUBE()

L'aide d'Excel est tout à fait précise et totalement inutile pour les débutants :

CUBEVALUE(connexion, [element_expression1], [element_expression2], ...)

Lien est un argument obligatoire ; une chaîne de texte représentant le nom de la connexion au cube.

expression_élément– argument facultatif; une chaîne de texte représentant un MDX qui renvoie un élément ou un tuple dans le cube. De plus, "element_expression" peut être un ensemble défini à l'aide de la fonction CUBESET. Utilisez "expression_membre" comme tranche pour déterminer la partie du cube pour laquelle vous souhaitez renvoyer une valeur agrégée. Si aucune mesure n'est spécifiée dans element_expression, la mesure par défaut de ce cube sera utilisée.

Avant de procéder à l'explication de la syntaxe de la fonction CUBEVALUE, quelques mots sur les cubes, les modèles de données et le cryptique tuple.

Quelques informations sur les cubes OLAP et les modèles de données Power Pivot

Cubes de données OLAP ( O n je ine UN analytique P rocessing - analyse de données opérationnelles) ont été développés spécifiquement pour le traitement analytique et l'extraction rapide de données à partir de celles-ci. Imaginez un espace tridimensionnel, où les axes sont des périodes de temps, des villes et des biens (Fig. 5a). Les nœuds d'une telle grille de coordonnées contiennent les valeurs de diverses mesures : volume des ventes, profit, coûts, nombre d'unités vendues, etc. Imaginons maintenant qu'il y ait des dizaines de mesures, voire des centaines... et il y a aussi un beaucoup de mesures. Ce sera le cube OLAP multidimensionnel. La création, la configuration et la mise à jour des cubes OLAP sont l'affaire des professionnels de l'informatique.

Riz. 5a. Cube OLAP 3D

Les formules analytiques Excel (formules de cube) extraient les titres 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 cubes d'être si flexibles et de s'adapter aux besoins des utilisateurs. Tableaux croisés dynamiques basés Feuilles Excel n'utilisez pas de mesures, elles ne sont donc pas aussi flexibles à des fins d'analyse de données.

Power Pivot - Relativement nouvelle fonctionnalité Microsoft. Il s'agit d'un environnement Excel intégré et quelque peu indépendant avec une interface familière. Power Pivot est de loin supérieur aux tableaux croisés dynamiques standard. Dans le même temps, le développement de cubes dans Power Pivot est relativement simple et, surtout, il ne nécessite pas la participation d'un spécialiste en informatique. Microsoft réalise son slogan : "Business analytics - to the masses !". Bien que les modèles Power Pivot ne soient pas des cubes à 100 %, ils peuvent également être appelés cubes (voir le cours d'introduction à Power Pivot de Mark Moore et l'édition plus longue de Rob Colley, Power Pivot DAX Formulas pour plus de détails).

Les principaux composants d'un cube sont les dimensions, les hiérarchies, les niveaux, les éléments (ou membres ; en anglais members) et les mesures (measures). La mesure - principale caractéristique des données analysées. Par exemple, catégorie de produit, période, géographie des ventes. Une dimension est quelque chose que nous pouvons mettre sur l'un des axes du tableau croisé dynamique. Chaque dimension sauf valeurs uniques inclut un élément qui regroupe tous les membres de cette dimension.

Les mesures sont basées sur hiérarchie. Par exemple, une catégorie de produits peut être divisée en sous-catégories, puis en modèles et enfin en noms de produits (Fig. 5b) La hiérarchie permet de créer des données de synthèse et de les analyser à différents niveaux de structure. Dans notre exemple, la hiérarchie Catégorie comprend 4 niveau.

Éléments(membres individuels) sont présents à tous les niveaux. Par exemple, le niveau Catégorie comporte 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 le volume des ventes. Les mesures dans les cubes sont stockées dans leur propre dimension, appelée (voir 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 les tranches en fonction des membres de dimension que nous plaçons sur l'axe. Ils indiquent également quelles coordonnées nous allons définir ou quel contexte de filtre nous allons définir. Par exemple, sur la fig. 5a 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 Profit (en trois coordonnées) pour Moscou en octobre sur les pommes = 63 000 roubles. La mesure peut être interprétée, et comme l'une des mesures. Par exemple, sur la fig. 5a au lieu d'un essieu Des produits, placer l'axe Les mesures avec des éléments Volume des ventes, Profit, Unités vendues. Ensuite, chaque cellule aura une certaine 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 lequel on calcule la mesure. Par exemple, sur la fig. 5a Tuple= Moscou, octobre, pommes. Un tuple valide est également Perm, pommes. Un autre est les pommes, août. Les dimensions non incluses dans le tuple sont implicitement présentes dans le tuple et sont représentées par le membre par défaut . Ainsi, une cellule d'un espace multidimensionnel est toujours définie ensemble complet coordonnées, même si certaines d'entre elles sont omises du tuple. Vous ne pouvez pas inclure deux éléments de même dimension dans un tuple, la syntaxe ne le permettra pas. Par exemple, le tuple invalide Moscou et Perm, pommes. Pour implémenter une telle expression multidimensionnelle, vous avez besoin d'un ensemble de deux tuples : Moscou et pommes + Perm et pommes.

Ensemble d'éléments– plusieurs éléments de même dimension. Par exemple, les pommes et les poires. Ensemble de tuples- plusieurs tuples, dont chacun est constitué des mêmes dimensions dans la même séquence. Par exemple, un ensemble de deux tuples : Moscow, pommes et Perm, bananes.

La saisie semi-automatique à la rescousse

Revenons à la syntaxe de la fonction CUBEVALUE. Utilisons la saisie semi-automatique. Commencez à saisir une formule dans une cellule :

Excel proposera toutes les connexions disponibles dans le classeur Excel :

Riz. 6. La connexion au modèle de données Power Pivot est toujours appelée ThisWorkbookDataModel

Riz. 7. Connexions aux cubes

Continuons à saisir la formule (dans notre cas, pour le modèle de données) :

La saisie semi-automatique proposera toutes les tables et mesures de modèle de données disponibles :

Riz. 8. Éléments disponibles du premier niveau - noms de tableau et un ensemble de mesures (en surbrillance)

Choisissez un icône Les mesures. Pointer vers:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

La saisie semi-automatique proposera toutes les mesures disponibles :

Riz. 9. Éléments disponibles du deuxième niveau dans l'ensemble de mesures

Choisissez une mesure. Ajoutez des guillemets, une parenthèse fermante, appuyez sur Entrée.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Riz. 10. Formule CUBEVALUE dans une cellule Excel

De même, vous pouvez ajouter un troisième argument à la formule :

VBA dans Excel Objet Excel.PivotTable et utilisation de tableaux croisés dynamiques et de cubes OLAP dans Excel

10.8 Travailler avec des tableaux croisés dynamiques (objet PivotTable)

Objet Excel.PivotTable, travailler par programmation avec des tableaux croisés dynamiques et des cubes OLAP dans Excel à l'aide de VBA, objet PivotCache, créer une disposition de tableau croisé dynamique

Au cours du fonctionnement de la plupart des entreprises, les données dites brutes sur les activités sont accumulées. Par exemple, pour une entreprise commerciale, les données sur les ventes de biens peuvent être accumulées - pour chaque achat séparément, pour les entreprises communications cellulaires- statistiques de charge sur stations de base etc. Très souvent, la gestion d'une entreprise a besoin d'informations analytiques générées sur la base d'informations brutes - par exemple, pour calculer la contribution de chaque type de produit au revenu de l'entreprise ou la qualité du service dans le domaine d'un domaine donné station. 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, il est de plus en plus courant que les données brutes soient résumées d'abord dans le Data Warehouse, puis dans des cubes OLAP, ce qui est très pratique pour l'analyse interactive. Il est plus facile de considérer les cubes OLAP comme des tableaux multidimensionnels, dans lesquels, au lieu des deux dimensions standard (colonnes et lignes, comme dans les tableaux ordinaires), il peut y avoir beaucoup de dimensions. Le terme "sectionnel" est couramment utilisé pour décrire les dimensions d'un cube. Par exemple, un service marketing peut avoir besoin d'informations par heure, par région, par type de produit, par canal 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, vous ne pouvez pas créer de tels cubes dans des bases de données classiques. Les cubes OLAP nécessitent des produits logiciels. 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.

Pour travailler avec de tels cubes, un client spécial est intégré à Excel. En russe ça s'appelle tableau croisé dynamique(sur l'écran graphique, il est disponible via le menu Données -> tableau croisé dynamique), et en anglais - tableau croisé dynamique. Par conséquent, l'objet que ce client représente est appelé tableau croisé dynamique. Il convient de noter qu'il peut fonctionner non seulement avec des cubes OLAP, mais également avec des données régulières dans des tableaux ou des bases de données Excel, mais de nombreuses fonctionnalités sont perdues.

Le tableau croisé dynamique et l'objet tableau croisé dynamique sont des produits logiciels de Panorama Software qui ont été acquis par Microsoft et intégrés dans Excel. Par conséquent, l'utilisation de l'objet Tableau croisé dynamique est quelque peu différente de l'utilisation d'autres objets Excel. Savoir quoi faire est souvent difficile. 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, l'automatisation est donc nécessaire dans de nombreuses situations.

À quoi cela ressemble-t-il de travailler par programmation 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. Très conditionnellement, cet objet PivotCache peut être comparé à QueryTable. Un seul objet PivotCache peut être utilisé par objet PivotTable. L'objet PivotCache est créé à l'aide de la méthode Add() de la collection PivotCaches :

Dim PC1 en tant que PivotCache

Définir PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches est une collection standard, et parmi les méthodes qui méritent une attention particulière, seule la méthode Add() peut y ê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 choisir de créer un tableau croisé dynamique basé sur une plage dans Excel, des données d'une base de données, une source de données externe, un autre tableau croisé dynamique, etc. En pratique, il est généralement logique d'utiliser OLAP uniquement lorsqu'il y a beaucoup de données - en conséquence, vous avez besoin d'un spécialiste stockage externe(par exemple, Microsoft Analysis Services). Dans cette situation, xlExternal est sélectionné.
• Données source- obligatoire dans tous les cas sauf lorsque la valeur du premier paramètre est xlExternal. À proprement parler, 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 son ensemble de propriétés et de méthodes est très similaire. Certains des plus propriétés importantes et méthodes :

• ADOConnection- la possibilité de renvoyer un objet ADO Connection qui est automatiquement créé pour se connecter à source externe Les données. Utilisé pour configurer davantage les propriétés de connexion.
• lien- fonctionne exactement de la même manière que la propriété d'objet QueryTable du même nom. Peut accepter une chaîne de connexion, un objet Recordset prêt à l'emploi, fichier texte, Requête Web. Fichier Microsoft requête. Le plus souvent, lorsque vous travaillez avec OLAP, une chaîne de connexion est écrite directement (car cela n'a pas 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 à la base de données Foodmart (exemple de base de données Analysis Services) sur le serveur LONDON peut ressembler à ceci :

PC1.Connection = "OLEDB;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000"

• Propriétés Type de commande et texte de commande décrire le type de commande qui est envoyé au serveur de base de données et le texte de la commande elle-même de la même manière. Par exemple, pour accéder au cube Ventes et le mettre entièrement en cache sur le client, vous pouvez utiliser un code tel que

• propriété LocalConnection permet de se connecter à un cube local (fichier *.cub) créé par Excel. Bien sûr, il est fortement déconseillé d'utiliser de tels fichiers pour travailler avec des volumes de données de "production" - uniquement à des fins de création de mises en page, etc.
• propriété Mémoireutilisée renvoie le nombre mémoire vive, utilisé par PivotCache. Si le tableau croisé dynamique basé sur ce PivotCache n'a pas encore été créé et ouvert, renvoie 0. Peut être utilisé pour vérifier si votre application fonctionnera sur des clients faibles.
• propriété OLAP renvoie True si le PivotCache est connecté au serveur OLAP.
• Optimiser le cache- la possibilité d'optimiser la structure du cache. Le chargement initial des données prendra plus de temps, mais la vitesse de travail peut alors augmenter. Pour les sources OLE DB ne fonctionne pas.

Les propriétés restantes de l'objet PivotCache sont les mêmes que celles de l'objet QueryTable et ne seront donc pas abordées 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 PivotTable). Cette méthode prend quatre paramètres :

• TableDestination est le seul paramètre obligatoire. Accepte un objet Range, dans le coin supérieur gauche duquel sera placé le tableau croisé dynamique.
• nom de la table- Nom du tableau croisé dynamique. S'il n'est pas spécifié, le nom du formulaire "PivotTable1" sera automatiquement généré.
• lire des données- si défini sur True, alors tout le contenu du cube sera automatiquement mis en cache. Vous devez être très prudent avec ce paramètre, car son utilisation incorrecte peut augmenter considérablement la charge sur le client.
• Version par défaut- Cette propriété n'est généralement pas spécifiée. Vous permet de spécifier la version du tableau croisé dynamique en cours de création. Par défaut, la version la plus récente est utilisée.

La création d'un tableau croisé dynamique dans la première cellule de la première feuille du classeur peut ressembler à ceci :

PC1.CréerPlage de tableau croisé dynamique ("A1")

Le tableau croisé dynamique a été créé, mais immédiatement après sa création, il est vide. Il propose quatre zones dans lesquelles vous pouvez placer des champs depuis 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 avec un bouton Disposition sur le dernier écran de l'assistant de tableau croisé dynamique) :

• zone de colonne- il contient les dimensions ("section" dans laquelle les données seront analysées), dont les membres sont moins nombreux ;
• zone de ligne- ces dimensions dont les membres sont plus nombreux ;
• zone de page- les mesures par lesquelles il suffit de filtrer (par exemple, pour ne montrer des données que pour telle ou telle région ou seulement pour telle ou telle 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 compter sur l'utilisateur pour placer correctement les éléments dans les quatre zones. De plus, cela peut prendre un certain temps. Par conséquent, il est souvent nécessaire d'organiser les données dans un tableau croisé dynamique par programmation. 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ù sera situé tel ou tel champ. Par exemple, plaçons la dimension Clients dans la zone des colonnes :

PT1.CubeFields("").Orientation = xlColumnField

Puis - la dimension Temps à la zone des chaînes :

PT1.CubeFields(""). Orientation = xlRowField

Ensuite - la dimension Produit vers la zone de page :

PT1.CubeFields(""). Orientation = xlPageField

Et enfin, l'indicateur (données numériques pour l'analyse) Unit Sales :

PT1.CubeFields("..").Orientation = xlDataField