maison › PAR › Qu'est-ce que le cube Olap dans Excel. Création d'un projet SSAS. Qu’est-ce que l’analyse et pourquoi est-elle nécessaire ?

Qu'est-ce que le cube Olap dans Excel. Création d'un projet SSAS. Qu’est-ce que l’analyse et pourquoi est-elle nécessaire ?

En règle générale, les systèmes d'information d'une entreprise sérieuse contiennent des applications conçues pour une analyse complexe des données, de leur dynamique, de leurs tendances, etc. En conséquence, la haute direction devient le principal consommateur des résultats de l'analyse. Une telle analyse vise en fin de compte à soutenir la prise de décision. Et pour prendre toute décision de gestion, il est nécessaire de disposer des informations nécessaires, généralement quantitatives. Pour ce faire, il est nécessaire de collecter ces données auprès de tous systèmes d'information entreprises, les regrouper dans un format commun puis les analyser. A cet effet, des Data Warehouses sont créés.

Qu'est-ce qu'un entrepôt de données ?

Habituellement - le lieu où toutes les informations ayant une valeur analytique sont collectées. Les exigences pour de tels magasins correspondent à la définition classique d'OLAP et seront expliquées ci-dessous.

Parfois, l'entrepôt a un autre objectif : l'intégration de toutes les données de l'entreprise, afin de maintenir l'intégrité et la pertinence des informations au sein de tous les systèmes d'information. Que. le référentiel accumule non seulement des informations analytiques, mais aussi presque toutes les informations, et peut les fournir sous forme de répertoires à d'autres systèmes.

Un entrepôt de données typique est généralement différent d'une base de données relationnelle classique. Premièrement, les bases de données classiques sont conçues pour aider les utilisateurs dans leur travail quotidien, tandis que les entrepôts de données sont conçus pour la prise de décision. Par exemple, la vente de marchandises et l'émission des factures sont réalisées à l'aide d'une base de données destinée au traitement des transactions, et l'analyse de la dynamique des ventes sur plusieurs années, qui permet de planifier le travail avec les fournisseurs, est réalisée à l'aide d'un entrepôt de données.

Deuxièmement, alors que les bases de données traditionnelles sont soumises à des changements constants au fur et à mesure que les utilisateurs travaillent, l'entrepôt de données est relativement stable : les données qu'il contient sont généralement mises à jour selon un calendrier (par exemple, hebdomadaire, quotidien ou horaire, selon les besoins). Idéalement, le processus d'enrichissement consiste simplement à ajouter de nouvelles données sur une période donnée sans modifier les informations précédentes déjà présentes dans le magasin.

Et troisièmement, les bases de données classiques sont le plus souvent la source des données qui aboutissent dans l'entrepôt. De plus, le stockage peut être reconstitué par sources externes, par exemple des rapports statistiques.

Comment est construit un entrepôt de stockage ?

ETL– concept de base : Trois étapes :

Extraction – extraire des données de sources externes dans un format compréhensible ;
Transformation – transformation de la structure des données sources en structures pratiques pour construire un système analytique ;

Ajoutons une étape supplémentaire - le nettoyage des données ( Nettoyage) – le processus de filtrage des données non pertinentes ou de correction des données erronées sur la base de méthodes statistiques ou expertes. Afin de ne pas générer de rapports du type « Ventes pour 20011 » plus tard.

Revenons à l'analyse.

Qu’est-ce que l’analyse et pourquoi est-elle nécessaire ?

L'analyse est l'étude des données dans le but de prendre des décisions. Les systèmes analytiques sont appelés systèmes d'aide à la décision ( DSS).

Il convient ici de souligner la différence entre travailler avec DSS et un simple ensemble de rapports réglementés et non réglementés. L'analyse dans DSS est presque toujours interactive et itérative. Ceux. l'analyste fouille dans les données, compose et ajuste des requêtes analytiques, et reçoit des rapports dont la structure peut être inconnue à l'avance. Nous y reviendrons plus en détail ci-dessous lorsque nous discuterons du langage de requête. MDX.

OLAP

Les systèmes d'aide à la décision ont généralement les moyens de fournir à l'utilisateur des données agrégées pour divers échantillons de l'ensemble d'origine sous une forme pratique pour la perception et l'analyse (tableaux, graphiques, etc.). L'approche traditionnelle de segmentation des données sources consiste à extraire des données sources un ou plusieurs ensembles de données multidimensionnels (souvent appelés hypercube ou métacube), dont les axes contiennent des attributs et les cellules contiennent des données quantitatives agrégées. (Ces données peuvent également être stockées dans des tables relationnelles, mais dans ce cas nous parlons de l'organisation logique des données, et non de la mise en œuvre physique de leur stockage.) Le long de chaque axe, les attributs peuvent être organisés sous forme de hiérarchies, représentant différents niveaux de leur détail. Grâce à ce modèle de données, les utilisateurs peuvent formuler des requêtes complexes, générer des rapports et obtenir des sous-ensembles de données.

La technologie d'analyse de données multidimensionnelles complexes est appelée OLAP (On-Line Analytical Processing). OLAP est un élément clé de l'entreposage de données traditionnel. Le concept d'OLAP a été décrit en 1993 par Edgar Codd, chercheur renommé en bases de données et auteur du modèle de données relationnelles. En 1995, sur la base des exigences définies par Codd, le test dit FASMI (Fast Analysis of Shared Multidimensionnel Information) a été formulé, comprenant les exigences suivantes pour les applications d'analyse multidimensionnelle :

fournir à l'utilisateur les résultats de l'analyse dans un délai acceptable (généralement pas plus de 5 s), même au prix d'une analyse moins détaillée ;
la capacité d'effectuer toute analyse logique et statistique caractéristique de cette application, et en le sauvegardant sous une forme accessible à l'utilisateur final ;
accès multi-utilisateurs aux données avec prise en charge de mécanismes de verrouillage appropriés et de moyens d'accès autorisés ;
représentation conceptuelle multidimensionnelle des données, y compris plein soutien pour les hiérarchies et les hiérarchies multiples (il s'agit d'une exigence clé d'OLAP) ;
la possibilité d'accéder à toute information nécessaire, quels que soient son volume et son emplacement de stockage.

Il convient de noter que la fonctionnalité OLAP peut être implémentée différentes façons, en commençant par les outils d'analyse de données les plus simples dans les applications bureautiques et en terminant par des systèmes analytiques distribués basés sur des produits serveur. Ceux. OLAP n'est pas une technologie, mais idéologie.

Avant de parler des différentes implémentations d'OLAP, examinons de plus près ce que sont les cubes d'un point de vue logique.

Concepts multidimensionnels

Nous utiliserons la base de données Northwind incluse avec Microsoft pour illustrer les principes OLAP. serveur SQL et qui est une base de données typique stockant des informations sur les opérations commerciales d'une entreprise engagée dans la fourniture en gros de produits alimentaires. Ces données comprennent des informations sur les fournisseurs, les clients, une liste des marchandises fournies et leurs catégories, des données sur les commandes et les marchandises commandées, une liste des employés de l'entreprise.

cube

Prenons par exemple la table Invoices1, qui contient les commandes de l'entreprise. Les champs de ce tableau seront les suivants :

Date de commande
Un pays
Ville
Nom du client
Compagnie de livraison
Nom du produit
Quantité de marchandises
Prix de la commande

Quelles données globales pouvons-nous obtenir de cette vue ? Il s’agit généralement de réponses à des questions telles que :

Quelle est la valeur totale des commandes passées par les clients d’un pays particulier ?
Quelle est la valeur totale des commandes passées par les clients dans un certain pays et livrées par une certaine entreprise ?
Quelle est la valeur totale des commandes passées par les clients dans un pays particulier au cours d’une année donnée et livrées par une entreprise particulière ?

Toutes ces données peuvent être obtenues à partir de cette table à l'aide de requêtes SQL assez évidentes avec regroupement.

Le résultat de cette requête sera toujours une colonne de nombres et une liste d'attributs la décrivant (par exemple, un pays) - il s'agit d'un ensemble de données unidimensionnelles ou, en langage mathématique, d'un vecteur.

Imaginons que nous ayons besoin d'obtenir des informations sur le coût total des commandes de tous les pays et leur répartition entre les sociétés de livraison - nous obtiendrons un tableau (matrice) de chiffres, où les sociétés de livraison seront répertoriées dans les en-têtes de colonnes, les pays dans la ligne les titres, et dans les cellules, il y aura le nombre de commandes. Il s'agit d'un tableau de données bidimensionnel. Cet ensemble de données est appelé tableau croisé dynamique ( tableau croisé dynamique) ou un tableau croisé.

Si nous voulons obtenir les mêmes données, mais aussi par année, alors un autre changement apparaîtra, c'est-à-dire l'ensemble de données deviendra tridimensionnel (un tenseur conditionnel du 3ème ordre ou un « cube ») tridimensionnel.

Bien évidemment, le nombre maximum de dimensions est le nombre de tous les attributs (Date, Pays, Client, etc.) qui décrivent nos données agrégées (montant des commandes, nombre de produits, etc.).

C'est ainsi que nous arrivons au concept de multidimensionnalité et à son incarnation - cube multidimensionnel. Nous appellerons une telle table " tableau de faits" Dimensions ou axes de cube ( dimensions) sont des attributs dont les coordonnées sont exprimées par les valeurs individuelles de ces attributs présentes dans la table de faits. Ceux. par exemple, si les informations sur les commandes ont été conservées dans le système de 2003 à 2010, alors l'axe de cette année sera composé de 8 points correspondants. Si les commandes proviennent de trois pays, alors l'axe des pays contiendra 3 points, etc. Quel que soit le nombre de pays inclus dans le répertoire des pays. Les points sur un axe sont appelés ses « membres » ( Membres).

Dans ce cas, les données agrégées elles-mêmes seront appelées « mesures » ( Mesure). Pour éviter toute confusion avec les « dimensions », ces dernières sont de préférence appelées « axes ». L'ensemble des mesures forme un autre axe « Mesures » ( Mesures). Il comporte autant de membres (points) qu'il y a de mesures (colonnes agrégées) dans la table de faits.

Les membres des dimensions ou des axes peuvent être combinés par une ou plusieurs hiérarchies ( hiérarchie). Expliquons ce qu'est la hiérarchie avec un exemple : les villes des ordres peuvent être unies en districts, les districts en régions, les régions d'un pays, les pays en continents ou autres entités. Ceux. il existe une structure hiérarchique - continent- pays-région-district-ville– 5 niveaux ( Niveau). Pour une région, les données sont agrégées pour toutes les villes qui y sont incluses. Pour une région comprenant tous les districts contenant toutes les villes, etc. Pourquoi avons-nous besoin de plusieurs hiérarchies ? Par exemple, sur l'axe des dates de commande, nous souhaiterons peut-être regrouper les points (c'est-à-dire les jours) dans une hiérarchie. Année mois jour ou par Année-Semaine-Jour: dans les deux cas il y a trois niveaux. Évidemment, Semaine et Mois regroupent les jours différemment. Il existe également des hiérarchies dont le nombre de niveaux n'est pas déterministe et dépend des données. Par exemple, des dossiers sur un disque d'ordinateur.

L'agrégation des données peut s'effectuer à l'aide de plusieurs fonctions standards : somme, minimum, maximum, moyenne, nombre.

MDX

Passons au langage de requête dans les données multidimensionnelles.
Le langage SQL n'a pas été conçu à l'origine pour les programmeurs, mais pour les analystes (et possède donc une syntaxe qui ressemble au langage naturel). Mais au fil du temps, cela est devenu de plus en plus compliqué et désormais, peu d’analystes savent comment l’utiliser correctement, voire pas du tout. C'est devenu un outil pour les programmeurs. Le langage de requête MDX, qui aurait été développé par notre ancien compatriote Mosha (ou Mosha) Posumansky dans les tréfonds de Microsoft, était lui aussi initialement destiné aux analystes, mais ses concepts et sa syntaxe (qui rappelle vaguement SQL, et complètement en vain, c'est-à-dire parce que cela ne fait que confondre), encore plus compliqué que SQL. Cependant, ses bases restent faciles à comprendre.

Nous l'examinerons en détail car c'est le seul langage qui a reçu le statut de standard dans le cadre du standard général du protocole XMLA, et d'autre part parce qu'il existe une implémentation open source sous la forme du projet Mondrian de l'entreprise Pentaho. D'autres systèmes d'analyse OLAP (par exemple, Oracle OLAP Option) utilisent généralement leurs propres extensions de la syntaxe SQL, mais ils déclarent également la prise en charge de MDX.

Travailler avec des ensembles de données analytiques signifie uniquement les lire et non les écrire. Que. MDX n'a pas de clauses pour modifier les données, mais une seule clause de sélection : select.

Dans OLAP, vous pouvez créer des cubes multidimensionnels tranches- c'est à dire. lorsque les données sont filtrées selon un ou plusieurs axes, ou projections– lorsque le cube « s’effondre » selon un ou plusieurs axes, agrégeant les données. Par exemple, notre premier exemple avec le montant des commandes des pays est une projection du cube sur l'axe Pays. La requête MDX pour ce cas ressemblera à ceci :

Sélectionnez ...Enfants sur les lignes de
Qu'est-ce qu'il y a ici ?

Sélectionner– le mot clé est inclus dans la syntaxe uniquement pour la beauté.
est le nom de l'axe. Tous les noms propres en MDX sont écrits entre crochets.
est le nom de la hiérarchie. Dans notre cas, il s'agit de la hiérarchie Pays-Ville
– c'est le nom du membre de l'axe au premier niveau de la hiérarchie (c'est-à-dire le pays) Tous – c'est un méta-membre qui unit tous les membres de l'axe. Il existe un tel méta-terme dans chaque axe. Par exemple, sur l’axe des années il y a « Toutes les années », etc.
Enfants est une fonction membre. Chaque membre dispose de plusieurs fonctions disponibles. Tel que Parent. Niveau, Hiérarchie, renvoyant respectivement l'ancêtre, le niveau dans la hiérarchie et la hiérarchie elle-même à laquelle appartient le membre dans ce cas. Enfants : renvoie un ensemble de membres enfants de ce membre. Ceux. dans notre cas – les pays.
sur les lignes– Indique comment organiser ces données dans le tableau résultant. Dans ce cas, dans l'en-tête des lignes. Valeurs possibles ici : sur les colonnes, sur les pages, sur les paragraphes, etc. Il est également possible d'indiquer simplement par index, en partant de 0.
depuis– c'est une indication du cube à partir duquel la sélection est effectuée.

Et si nous n’avions pas besoin de tous les pays, mais seulement de quelques-uns en particulier ? Pour ce faire, nous pouvons spécifier explicitement dans la requête les pays dont nous avons besoin, plutôt que de tout sélectionner à l'aide de la fonction Enfants.

Sélectionnez ( ..., ... ) sur les lignes de
Les accolades dans ce cas sont la déclaration de l'ensemble ( Ensemble). Un ensemble est une liste, une énumération de membres à partir d'un axe.

Écrivons maintenant une requête pour notre deuxième exemple – sortie dans le contexte d'un livreur :

Sélectionnez ...Enfants sur les lignes .Membres sur les colonnes de
Ajouté ici :
– axe ;
.Membres– une fonction d'axe qui renvoie tous les termes qu'elle contient. La hiérarchie et le niveau ont la même fonction. Parce que Il n'y a qu'une seule hiérarchie dans cet axe, alors son indication peut être omise, car le niveau et la hiérarchie sont également les mêmes, vous pouvez alors afficher tous les membres dans une seule liste.

Je pense qu'il est déjà évident de savoir comment nous pouvons continuer cela avec notre troisième exemple détaillé par année. Mais mieux vaut ne pas analyser par année, mais filtrer – c’est-à-dire construire une tranche Pour ce faire, nous écrirons la requête suivante :

Sélectionnez ..Enfants sur les lignes .Membres sur les colonnes d'où (.)
Où est la filtration ici ?

où- mot-clé
est un membre de la hiérarchie . Le nom complet, y compris tous les termes, serait : .. , mais parce que Puisque le nom de ce membre est unique au sein de l’axe, toutes les précisions intermédiaires du nom peuvent être omises.

Pourquoi le terme de date est-il entre parenthèses ? Les parenthèses sont un tuple ( tuple). Un tuple est une ou plusieurs coordonnées le long divers axes Par exemple, pour filtrer selon deux axes à la fois, nous listons entre parenthèses deux termes de différent mesures séparées par des virgules. Autrement dit, le tuple définit une « tranche » du cube (ou un « filtrage », si cette terminologie est plus proche).

Le tuple est utilisé pour bien plus que du simple filtrage. Les tuples peuvent également se trouver dans des en-têtes de ligne/colonne/page, etc.

Ceci est nécessaire, par exemple, pour afficher le résultat d'une requête tridimensionnelle dans un tableau bidimensionnel.

Sélectionnez crossjoin(...Children, ..Children) sur les lignes .Membres sur les colonnes à partir d'où (.)
Jointure croisée est une fonction. Il renvoie un ensemble de tuples (oui, un ensemble peut contenir des tuples !) résultant du produit cartésien de deux ensembles. Ceux. l'ensemble résultant contiendra toutes les combinaisons possibles de pays et d'années. Les en-têtes de lignes contiendront ainsi une paire de valeurs : Pays-Année.

La question est de savoir où se trouve l’indication des caractéristiques numériques qui doivent être affichées ? Dans ce cas, la mesure par défaut définie pour ce cube est utilisée, c'est-à-dire Prix de la commande. Si nous voulons dériver une autre mesure, alors nous rappelons que les mesures sont membres d'une dimension Mesures. Et nous agissons exactement de la même manière qu’avec les autres axes. Ceux. filtrer une requête par une des mesures affichera exactement cette mesure dans les cellules.

Question : Quelle est la différence entre le filtrage dans où et le filtrage en spécifiant les membres de l'axe dans les lignes. Réponse : pratiquement rien. Simplement là où une tranche est indiquée pour les axes qui ne participent pas à la formation des rubriques. Ceux. même axe ne peut pasêtre présent en même temps sur les lignes, et en où.

Membres calculés

Pour plus requêtes complexes Vous pouvez déclarer des membres calculés. Membres des axes d’attribut et de mesure. Ceux. Vous pouvez par exemple déclarer une nouvelle mesure qui affichera la contribution de chaque pays au montant total des commandes :

Avec membre. comme '.CurrentMember / ..', FORMAT_STRING='0.00%' sélectionnez ...Enfants sur les lignes à partir d'où .
Le calcul s'effectue dans le contexte d'une cellule dans laquelle tous ses attributs de coordonnées sont connus. Les coordonnées (membres) correspondantes peuvent être obtenues par la fonction CurrentMember pour chacun des axes du cube. Ici, il faut comprendre que l'expression .Membre actuel/..» ne divise pas un terme par un autre, mais divise données agrégées pertinentes des tranches de cubes ! Ceux. la tranche du territoire actuel sera divisée en une tranche pour tous les territoires, c'est-à-dire la valeur totale de toutes les commandes. FORMAT_STRING – définit le format d'affichage des valeurs, c'est-à-dire %.

Autre exemple de membre calculé, mais sur l'axe des années :

Avec membre. comme '. - .'
Évidemment, le rapport ne contiendra pas une unité, mais la différence des sections correspondantes, c'est-à-dire la différence dans le montant des commandes au cours de ces deux années.

Afficher dans ROLAP

Les systèmes OLAP sont d'une manière ou d'une autre basés sur une sorte de système de stockage et d'organisation des données. Quand nous parlons de sur les SGBDR, puis ils parlent de ROLAP (on laissera MOLAP et HOLAP pour auto-apprentissage). ROLAP – OLAP sur une base de données relationnelle, c'est-à-dire décrits sous forme de tableaux bidimensionnels ordinaires. Les systèmes ROLAP convertissent les requêtes MDX en SQL. Le principal problème informatique des bases de données est l’agrégation rapide. Pour agréger plus rapidement, les données de la base de données sont généralement fortement dénormalisées, c'est-à-dire ne sont pas stockés de manière très efficace en termes d’espace disque occupé et de surveillance de l’intégrité de la base de données. De plus, ils contiennent en outre des tableaux auxiliaires qui stockent des données partiellement agrégées. Par conséquent, pour OLAP, un schéma de base de données distinct est généralement créé, qui ne reproduit que partiellement la structure des bases de données transactionnelles d'origine en termes de répertoires.

La navigation

De nombreux systèmes OLAP proposent des outils de navigation interactifs pour une requête déjà générée (et par conséquent des données sélectionnées). Dans ce cas, on utilise ce qu'on appelle le « forage » ou le « forage ». Une traduction plus adéquate en russe serait le mot « approfondissement ». Mais c’est une question de goût, dans certains milieux le mot « forage » est resté.

Percer– il s’agit d’un rapport détaillé en réduisant le degré d’agrégation des données, combiné à un filtrage selon un autre axe (ou plusieurs axes). Il existe plusieurs types de forages :

exploration– filtrage selon un des axes sources du rapport avec affichage d'informations détaillées sur les descendants au sein de la hiérarchie du membre filtrant sélectionné. Par exemple, s'il existe un rapport sur la répartition des commandes ventilées par pays et par années, alors cliquer sur l'année 2007 affichera un rapport ventilé par les mêmes pays et mois de 2007.
côté forage– filtrage sous un ou plusieurs axes sélectionnés et suppression de l'agrégation le long d'un ou plusieurs autres axes. Par exemple, s'il existe un rapport sur la répartition des commandes par pays et par années, alors cliquer sur l'année 2007 affichera un autre rapport détaillé, par exemple, par pays et fournisseurs avec un filtrage par 2007.
forage– la suppression de l'agrégation sur tous les axes et le filtrage simultané le long de ceux-ci – vous permettent de voir les données sources de la table de faits à partir de laquelle la valeur du rapport a été obtenue. Ceux. Lorsque vous cliquez sur une valeur de cellule, un rapport s'affiche avec toutes les commandes ayant donné ce montant. Une sorte de forage instantané dans les « profondeurs » mêmes du cube.

C'est tout. Désormais, si vous décidez de vous consacrer à la Business Intelligence et à OLAP, il est temps de commencer à lire de la littérature sérieuse.

Balises : ajouter des balises

Je réside à Habr depuis un certain temps, mais je n'ai jamais lu d'articles sur le thème des cubes multidimensionnels, OLAP et MDX, bien que le sujet soit très intéressant et devienne de plus en plus pertinent chaque jour.
Ce n’est un secret pour personne qu’au cours de cette courte période de développement des bases de données, de la comptabilité électronique et des systèmes en ligne, de nombreuses données elles-mêmes se sont accumulées. Aujourd’hui, une analyse complète des archives, et peut-être une tentative de prédire des situations pour des modèles similaires à l’avenir, présente également un intérêt.
D’un autre côté, les grandes entreprises peuvent accumuler de telles quantités de données, même sur plusieurs années, mois ou même semaines, que même leur analyse de base nécessite des approches extraordinaires et des exigences matérielles strictes. Il peut s'agir de systèmes de traitement de transactions bancaires, d'agents de change, opérateurs téléphoniques etc.
Je pense que tout le monde connaît bien 2 approches différentes de la conception de bases de données : OLTP et OLAP. La première approche (Online Transaction Processing - traitement des transactions en temps réel) est conçue pour une collecte efficace de données en temps réel, tandis que la seconde (Online Analytical Processing - traitement analytique en temps réel) vise spécifiquement à échantillonner et à traiter les données de la manière la plus efficace possible. chemin.

Examinons les principales capacités des cubes OLAP modernes et les problèmes qu'ils résolvent (Analysis Services 2005/2008 sont pris comme base) :

accès rapide aux données
préagrégation
hiérarchie
travailler avec le temps
langage d'accès aux données multidimensionnelles
KPI (Indicateurs Clés de Performance)
extraction de dattes
mise en cache à plusieurs niveaux
prise en charge multilingue

Examinons donc les capacités des cubes OLAP plus en détail.

Un peu plus sur les possibilités

Accès rapide aux données
En fait, un accès rapide aux données, quelle que soit la taille du tableau, constitue la base des systèmes OLAP. Puisqu’il s’agit de l’objectif principal, un entrepôt de données est généralement construit sur des principes différents de ceux des bases de données relationnelles.
Ici, le temps nécessaire pour récupérer des données simples est mesuré en fractions de seconde, et une requête dépassant quelques secondes nécessite très probablement une optimisation.

Préagrégation
En plus de récupérer rapidement les données existantes, il offre également la possibilité de pré-agréger les valeurs « les plus susceptibles d'être utilisées ». Par exemple, si nous avons des enregistrements quotidiens des ventes d'un certain produit, le système Peut être Nous pouvons également pré-agréger les montants de ventes mensuels et trimestriels, ce qui signifie que si nous demandons des données mensuellement ou trimestriellement, le système nous donnera instantanément le résultat. Pourquoi la pré-agrégation ne se produit-elle pas toujours ? Parce que les combinaisons théoriquement possibles de biens/temps/etc. il peut y en avoir un nombre énorme, ce qui signifie que vous devez avoir des règles claires pour quels éléments l'agrégation sera construite et pour lesquels non. De manière générale, le sujet de la prise en compte de ces règles et de la conception même des agrégations est assez vaste et mérite à lui seul un article à part.

Hiérarchies
Il est naturel que lors de l'analyse des données et de la construction des rapports finaux, il soit nécessaire de prendre en compte le fait que les mois sont constitués de jours, et qu'ils forment eux-mêmes des quartiers, et que les villes sont incluses dans des zones, qui à leur tour font partie de régions ou de pays. . La bonne nouvelle est que Cubes OLAP au départ, ils considèrent les données du point de vue des hiérarchies et des relations avec d'autres paramètres de la même entité, donc construire et utiliser des hiérarchies dans des cubes est une affaire très simple.

Travailler avec le temps
Étant donné que l'analyse des données s'effectue principalement dans des zones temporelles, le temps revêt une importance particulière dans les systèmes OLAP, ce qui signifie qu'en définissant simplement pour le système où nous avons du temps ici, à l'avenir, vous pourrez facilement utiliser des fonctions telles que Année à ce jour, Mois à ce jour. (la période allant du début de l'année/du mois à la date actuelle), Période Parallèle (le même jour ou mois, mais l'année dernière), etc.

Langage d'accès aux données multidimensionnelles
MDX(Expressions multidimensionnelles) - un langage de requête pour un accès simple et efficace aux structures de données multidimensionnelles. Et cela veut tout dire – vous en trouverez quelques exemples ci-dessous.

Indicateurs clés de performance (KPI)
Indicateurs clés de performance est un système de mesure financière et non financière qui aide une organisation à déterminer la réalisation de ses objectifs stratégiques. Les indicateurs de performance clés peuvent être tout simplement définis dans les systèmes OLAP et utilisés dans les rapports.

Date d'extraction
Exploration de données(Data Mining) - essentiellement, identifier des modèles ou des relations cachés entre des variables dans de grands ensembles de données.
Le terme anglais « Data Mining » n'a pas de traduction univoque en russe (data mining, data mining, information mining, data/information extraction), c'est pourquoi il est utilisé dans la plupart des cas dans l'original. La traduction indirecte la plus réussie est le terme « data mining » (DMA). Cependant, il s’agit d’un sujet distinct, non moins intéressant à considérer.

Mise en cache à plusieurs niveaux
En fait, pour garantir la vitesse d'accès aux données la plus élevée, en plus des structures de données et des préagrégations délicates, les systèmes OLAP prennent en charge la mise en cache à plusieurs niveaux. En plus de la mise en cache des requêtes simples, des parties des données lues dans le magasin, les valeurs agrégées et les valeurs calculées sont également mises en cache. Ainsi, plus vous travaillez longtemps avec un cube OLAP, plus il commence à fonctionner rapidement. Il existe également le concept de « réchauffement du cache » - une opération qui prépare le système OLAP à travailler avec des rapports, des requêtes spécifiques ou tous combinés.

Prise en charge multilingue
Oui oui oui. Au minimum, Analysis Services 2005/2008 (bien que Enterprise Edition) prend en charge nativement le multilinguisme. Il suffit de fournir une traduction des paramètres de chaîne de vos données, et le client qui a précisé sa langue recevra des données localisées.

Cubes multidimensionnels

Alors, que sont exactement ces cubes multidimensionnels ?
Imaginons un espace tridimensionnel dont les axes sont le Temps, les Produits et les Clients.
Un point dans un tel espace indiquera le fait que l'un des acheteurs a acheté un produit spécifique au cours d'un certain mois.

En fait, le plan (ou l'ensemble de tous ces points) sera le cube et, par conséquent, le Temps, les Produits et les Clients seront ses dimensions.
Il est un peu plus difficile d'imaginer (et de dessiner) un cube à quatre dimensions ou plus, mais l'essence ne change pas, et surtout, pour les systèmes OLAP, le nombre de dimensions avec lequel vous travaillerez n'a pas d'importance (dans des limites raisonnables). limites, bien sûr).

Un peu de MDX

Alors, quelle est la beauté de MDX ? Très probablement, c'est que nous devons décrire non pas la manière dont nous voulons sélectionner les données, mais Quoi exactement nous voulons.
Par exemple,

SÉLECTIONNER
( . ) SUR LES COLONNES,
( ., . ) SUR LES RANGÉES
DEPUIS
OÙ (., .)

Ce qui signifie que je veux le nombre d'iPhone vendus en juin et juillet au Mozambique.
En même temps je décris lequel ce sont les données que je veux et Comment Je veux les voir dans le rapport.
Magnifique, n'est-ce pas ?

Voici un peu plus compliqué :

AVEC MEMBRE Dépenses moyennes COMME
. / .
SÉLECTIONNER
( Dépense moyenne ) SUR LES COLONNES,
( .., .. ) SUR LES RANGÉES
DEPUIS
OÙ (.)
* Ce code source a été mis en évidence avec Source Code Highlighter.

En fait, nous déterminons d'abord la formule de calcul du « montant moyen des achats » et essayons de comparer qui (quel sexe) dépense plus d'argent en une seule visite dans l'Apple Store.

La langue elle-même est extrêmement intéressante à la fois à étudier et à utiliser, et mérite peut-être beaucoup de discussions.

Conclusion

En fait, cet article couvre très peu de concepts, même de base ; je l'appellerais un « apéritif » - une opportunité d'intéresser la communauté Habra à ce sujet et de le développer davantage. Quant au développement, il y a ici un immense champ non labouré et je me ferai un plaisir de répondre à toutes vos questions.

P.S. Ceci est mon premier article sur OLAP et la première publication sur Habré - je serais très reconnaissant pour vos commentaires constructifs.
Mise à jour: Je l'ai transféré vers SQL, je le transférerai vers OLAP dès qu'ils me permettront de créer de nouveaux blogs.

Balises : ajouter des balises

Dans le cadre de ces travaux, les problématiques suivantes seront considérées :

Que sont les cubes OLAP ?
Que sont les mesures, les dimensions, les hiérarchies ?
Quels types d’opérations peuvent être effectués sur les cubes OLAP ?

Le concept d'un cube OLAP

Le postulat principal d'OLAP est la multidimensionnalité dans la présentation des données. Dans la terminologie OLAP, le concept de cube, ou hypercube, est utilisé pour décrire un espace de données discret multidimensionnel.

cube est une structure de données multidimensionnelle à partir de laquelle un utilisateur-analyste peut interroger des informations. Les cubes sont créés à partir de faits et de dimensions.

Données- ce sont des données sur les objets et les événements de l'entreprise qui feront l'objet d'une analyse. Des faits du même type forment des mesures. Une mesure est le type de valeur dans une cellule de cube.

Des mesures- ce sont les éléments de données par lesquels les faits sont analysés. Une collection de ces éléments forme un attribut de dimension (par exemple, les jours de la semaine peuvent former un attribut de dimension temporelle). Dans les tâches d'analyse commerciale pour les entreprises commerciales, les dimensions incluent souvent des catégories telles que « temps », « ventes », « produits », « clients », « employés », « emplacement géographique ». Les mesures sont le plus souvent structures hiérarchiques, qui sont des catégories logiques par lesquelles l'utilisateur peut analyser les données réelles. Chaque hiérarchie peut avoir un ou plusieurs niveaux. Ainsi, la hiérarchie de la dimension « localisation géographique » peut comprendre les niveaux : « pays – région – ville ». Dans la hiérarchie temporelle, on peut distinguer par exemple la séquence de niveaux suivante : Une dimension peut avoir plusieurs hiérarchies (chaque hiérarchie d'une dimension doit avoir le même attribut clé de la table des dimensions).

Un cube peut contenir des données réelles provenant d'une ou plusieurs tables de faits et contient le plus souvent plusieurs dimensions. Tout cube donné a généralement un objectif d'analyse spécifique.

La figure 1 montre un exemple de cube conçu pour analyser les ventes de produits pétroliers par une certaine entreprise par région. Ce cube comporte trois dimensions (temps, produit et région) et une mesure (volume des ventes exprimé en termes monétaires). Les valeurs de mesure sont stockées dans les cellules correspondantes du cube. Chaque cellule est identifiée de manière unique par un ensemble de membres de chaque dimension, appelé tuple. Par exemple, la cellule située dans le coin inférieur gauche du cube (contient la valeur $98399) est spécifiée par le tuple [juillet 2005, Extrême-Orient, Diesel]. Ici, la valeur de 98 399 dollars indique le volume des ventes (en termes monétaires) de diesel en Extrême-Orient pour juillet 2005.

Il convient également de noter que certaines cellules ne contiennent aucune valeur : ces cellules sont vides car la table de faits ne contient pas de données les concernant.

Riz. 1. Cube avec des informations sur les ventes de produits pétroliers dans diverses régions

Le but ultime de la création de tels cubes est de minimiser le temps de traitement des requêtes qui extraient les informations requises des données réelles. Pour accomplir cette tâche, les cubes contiennent généralement des totaux précalculés appelés agrégations(agrégations). Ceux. le cube couvre un espace de données plus grand que l'espace réel - il contient des points logiques et calculés. Les fonctions d'agrégation vous permettent de calculer les valeurs des points dans l'espace logique en fonction des valeurs réelles. Les fonctions d'agrégation les plus simples sont SUM, MAX, MIN, COUNT. Ainsi, par exemple, en utilisant la fonction MAX, pour le cube donné dans l'exemple, vous pouvez identifier quand le pic des ventes de diesel s'est produit en Extrême-Orient, etc.

Une autre spécificité des cubes multidimensionnels est la difficulté d’en déterminer l’origine. Par exemple, comment définir le point 0 pour la dimension Produit ou Régions ? La solution à ce problème consiste à introduire un attribut spécial qui combine tous les éléments de la dimension. Cet attribut (créé automatiquement) ne contient qu'un seul élément - Tous. Pour les fonctions d'agrégation simples telles que sum, l'élément All est équivalent à la somme des valeurs de tous les éléments dans l'espace réel d'une dimension donnée.

Un concept important dans un modèle de données multidimensionnel est le sous-espace, ou sous-cube. Un sous-cube est une partie de l’espace complet d’un cube sous la forme d’une figure multidimensionnelle à l’intérieur du cube. Puisque l’espace multidimensionnel d’un cube est discret et limité, le sous-cube est également discret et limité.

Opérations sur les cubes OLAP

Les opérations suivantes peuvent être effectuées sur un cube OLAP :

tranche;
rotation;
consolidation;
détails.

Tranche(Figure 2) est un cas particulier de sous-cube. Il s'agit d'une procédure permettant de former un sous-ensemble d'un tableau de données multidimensionnel correspondant à une valeur unique d'un ou plusieurs éléments de dimension non inclus dans ce sous-ensemble. Par exemple, pour connaître l'évolution des ventes de produits pétroliers dans le temps uniquement dans une certaine région, à savoir l'Oural, il faut fixer la dimension « Produits » sur l'élément « Oural » et extraire le sous-ensemble (sous-cube) correspondant de l'élément « Oural ». cube.

Riz. 2. Tranche de cube OLAP

Rotation(Figure 3) - l'opération de changement d'emplacement des mesures présentées dans un rapport ou sur la page affichée. Par exemple, une opération de rotation peut impliquer de réorganiser les lignes et les colonnes d’un tableau. De plus, la rotation d'un cube de données déplace les dimensions hors tabulaires vers les dimensions présentes sur la page affichée, et vice versa.

En général, tous les spécialistes savent ce qu'est OLAP aujourd'hui. Au moins, les concepts « OLAP » et « données multidimensionnelles » sont fermement liés dans nos esprits. Néanmoins, le fait que ce sujet soit à nouveau évoqué, j'espère, sera approuvé par la majorité des lecteurs, car pour que l'idée dequelque chose ne devienne pas obsolète avec le temps, il faut communiquer périodiquement avec personnes intelligentes ou lire des articles dans une bonne publication...

Entrepôts de données (place d'OLAP dans la structure informationnelle de l'entreprise)

Le terme « OLAP » est inextricablement lié au terme « entrepôt de données » (Data Warehouse).

Voici la définition formulée par le « père fondateur » de l'entreposage de données, Bill Inmon : « Un entrepôt de données est une collection de données immuables, spécifiques à un domaine, destinées à soutenir la prise de décision de gestion. »

Les données de l'entrepôt proviennent de systèmes opérationnels (systèmes OLTP), conçus pour automatiser les processus métier. De plus, le référentiel peut être reconstitué à partir de sources externes, telles que des rapports statistiques.

Pourquoi créer des entrepôts de données - après tout, ils contiennent des informations manifestement redondantes qui « vivent » déjà dans des bases de données ou des fichiers du système d'exploitation ? La réponse peut être brève : il est impossible ou très difficile d’analyser directement les données des systèmes d’exploitation. Cela est dû à diverses raisons, notamment la fragmentation des données, leur stockage dans différents formats de SGBD et dans différents « coins » réseau d'entreprise. Mais même si une entreprise stocke toutes ses données sur un serveur de base de données central (ce qui est extrêmement rare), un analyste ne comprendra certainement pas leurs structures complexes, parfois déroutantes. L'auteur a une triste expérience en essayant de « nourrir » des analystes affamés avec des données « brutes » provenant de systèmes opérationnels - cela s'est avéré « trop pour eux ».

Ainsi, le but du référentiel est de fournir les « matières premières » à analyser en un seul endroit et dans une structure simple et compréhensible. Ralph Kimball, dans la préface de son livre "The Data Warehouse Toolkit", écrit que si, après avoir lu l'intégralité du livre, le lecteur ne comprend qu'une chose - à savoir que la structure de l'entrepôt doit être simple - l'auteur considérera son tâche terminée.

Il existe une autre raison qui justifie l'apparition d'un référentiel distinct : les requêtes analytiques complexes à informations opérationnelles ralentir travail actuel entreprises, bloquant les tables pendant longtemps et s'emparant des ressources du serveur.

À mon avis, un référentiel ne signifie pas nécessairement une gigantesque accumulation de données - l'essentiel est qu'il soit pratique pour l'analyse. D'une manière générale, il existe un terme distinct pour les petites installations de stockage - Data Marts (kiosques de données), mais dans notre pratique russe, vous ne l'entendez pas souvent.

OLAP - un outil d'analyse pratique

La centralisation et une structuration pratique ne sont pas tout ce dont un analyste a besoin. Il a encore besoin d'un outil pour visualiser et visualiser les informations. Les rapports traditionnels, même ceux construits sur un référentiel unique, manquent d'une chose : la flexibilité. Ils ne peuvent pas être « tordus », « développés » ou « réduits » pour obtenir la vue souhaitée des données. Bien sûr, vous pouvez appeler un programmeur (s'il veut venir), et il (s'il n'est pas occupé) fera un nouveau rapport assez rapidement - disons, dans l'heure (j'écris ceci et je ne crois pas moi-même - ça n'arrive pas si vite dans la vie ; donnons-lui trois heures) . Il s’avère qu’un analyste ne peut tester que deux idées par jour. Et lui (s'il est un bon analyste) peut proposer plusieurs idées de ce type par heure. Et plus l'analyste voit de « tranches » et de « sections » de données, plus il a d'idées, qui, à leur tour, nécessitent de plus en plus de « tranches » pour être vérifiées. Si seulement il disposait d’un outil qui lui permettrait d’agrandir et de réduire les données de manière simple et pratique ! OLAP agit comme un tel outil.

Bien qu'OLAP ne soit pas un attribut nécessaire d'un entrepôt de données, il est de plus en plus utilisé pour analyser les informations accumulées dans l'entrepôt.

Les composants inclus dans un référentiel typique sont illustrés à la Fig. 1.

Riz. 1. Structure de l'entrepôt de données

Les données opérationnelles sont collectées auprès de diverses sources, nettoyées, intégrées et stockées dans un magasin relationnel. De plus, ils sont déjà disponibles pour analyse à l’aide de divers outils de reporting. Ensuite, les données (en totalité ou en partie) sont préparées pour l'analyse OLAP. Ils peuvent être chargés dans une base de données OLAP spéciale ou stockés dans un stockage relationnel. Son élément le plus important sont les métadonnées, c'est-à-dire les informations sur la structure, le placement et la transformation des données. Grâce à eux, une interaction efficace des différents composants de stockage est assurée.

Pour résumer, on peut définir OLAP comme un ensemble d'outils d'analyse multidimensionnelle des données accumulées dans un entrepôt. Théoriquement, les outils OLAP peuvent être appliqués directement aux données opérationnelles ou à leurs copies exactes (afin de ne pas interférer avec les utilisateurs opérationnels). Mais nous risquons ainsi de marcher sur le râteau déjà décrit ci-dessus, c'est-à-dire de commencer à analyser des données opérationnelles qui ne se prêtent pas directement à l'analyse.

Définition et concepts de base d'OLAP

Tout d'abord, décryptons : OLAP est Online Analytical Processing, c'est-à-dire l'analyse de données opérationnelles. Les 12 principes déterminants d'OLAP ont été formulés en 1993 par E. F. Codd, « l'inventeur » des bases de données relationnelles. Plus tard, sa définition a été retravaillée dans le test dit FASMI, qui exige que l'application OLAP offre la possibilité d'analyser rapidement les informations multidimensionnelles partagées ().

Test FASMI

Rapide(Rapide) - l'analyse doit être effectuée tout aussi rapidement sur tous les aspects de l'information. Le temps de réponse acceptable est de 5 secondes ou moins.

Analyse(Analyse) - il doit être possible d'effectuer des types fondamentaux d'analyse numérique et statistique, prédéfinis par le développeur de l'application ou librement définis par l'utilisateur.

partagé(Partagé) - de nombreux utilisateurs doivent avoir accès aux données, alors qu'il est nécessaire de contrôler l'accès aux informations confidentielles.

Multidimensionnel(Multidimensionnel) est la caractéristique principale et la plus essentielle d'OLAP.

Information(Informations) - l'application doit pouvoir accéder à toutes les informations nécessaires, quels que soient leur volume et leur emplacement de stockage.

OLAP = Vue multidimensionnelle = Cube

OLAP offre des moyens pratiques et rapides d'accéder, de visualiser et d'analyser les informations commerciales. L'utilisateur reçoit un modèle de données naturel et intuitif, les organisant sous forme de cubes multidimensionnels (Cubes). Les axes du système de coordonnées multidimensionnel sont les principaux attributs du processus métier analysé. Par exemple, pour les ventes, il peut s'agir d'un produit, d'une région ou d'un type d'acheteur. Le temps est utilisé comme l’une des dimensions. Aux intersections des axes - dimensions (Dimensions) - se trouvent des données qui caractérisent quantitativement le processus - mesures (Mesures). Il peut s'agir de volumes de ventes en morceaux ou en termes monétaires, de soldes de stocks, de coûts, etc. L'utilisateur analysant les informations peut « découper » le cube en différentes directions, recevoir des informations récapitulatives (par exemple par année) ou, à l'inverse, détaillées (par semaine) et effectuer d'autres manipulations qui lui viennent à l'esprit au cours du processus d'analyse.

Comme les mesures dans le cube tridimensionnel montré sur la Fig. 2, le montant des ventes est utilisé et le temps, le produit et le magasin sont utilisés comme dimensions. Les mesures sont présentées à des niveaux de regroupement spécifiques : les produits sont regroupés par catégorie, les magasins par pays et les données de timing des transactions par mois. Un peu plus tard, nous examinerons plus en détail les niveaux de regroupement (hiérarchie).

Riz. 2. Exemple de cube

"Découper" un cube

Même un cube tridimensionnel est difficile à afficher sur un écran d'ordinateur pour que les valeurs des mesures d'intérêt soient visibles. Que dire des cubes à plus de trois dimensions ? Pour visualiser les données stockées dans un cube, on utilise généralement des vues bidimensionnelles familières, c'est-à-dire tabulaires, avec des en-têtes de lignes et de colonnes hiérarchiques complexes.

Une représentation bidimensionnelle d'un cube peut être obtenue en le « coupant » selon un ou plusieurs axes (dimensions) : on fixe les valeurs de toutes les dimensions sauf deux, et on obtient un tableau bidimensionnel régulier. L'axe horizontal du tableau (en-têtes de colonnes) représente une dimension, l'axe vertical (en-têtes de lignes) en représente une autre et les cellules du tableau représentent les valeurs des mesures. Dans ce cas, un ensemble de mesures est en fait considéré comme l'une des dimensions : soit nous sélectionnons une mesure à afficher (et nous pouvons ensuite placer deux dimensions dans les en-têtes de ligne et de colonne), soit nous montrons plusieurs mesures (et ensuite l'une des dimensions). les axes du tableau seront occupés par les noms des mesures, et les autres - les valeurs de la seule dimension « non coupée »).

Jetez un œil à la fig. 3 - voici une tranche bidimensionnelle du cube pour une mesure - Ventes unitaires (pièces vendues) et deux dimensions "non coupées" - Store (Store) et Time (Time).

Riz. 3. Tranche de cube 2D pour une mesure

En figue. La figure 4 ne montre qu'une seule dimension « non coupée » - Magasin, mais elle affiche les valeurs de plusieurs mesures - Ventes unitaires (unités vendues), Ventes en magasin (montant de la vente) et Coût du magasin (dépenses du magasin).

Riz. 4. Tranche de cube 2D pour plusieurs mesures

Une représentation bidimensionnelle d'un cube est également possible lorsque plus de deux dimensions restent « non coupées ». Dans ce cas, deux dimensions ou plus du cube « coupé » seront placées sur les axes de tranche (lignes et colonnes) - voir Fig. 5.

Riz. 5. Tranche de cube 2D avec plusieurs dimensions sur un axe

Mots clés

Les valeurs « posées » le long des dimensions sont appelées membres ou étiquettes. Les étiquettes servent à la fois à « couper » le cube et à limiter (filtrer) les données sélectionnées - lorsque dans une dimension qui reste « non coupée », on ne s'intéresse pas à toutes les valeurs, mais à un sous-ensemble d'entre elles, par exemple trois villes. sur plusieurs dizaines. Les valeurs des étiquettes apparaissent dans la vue du cube 2D sous forme d'en-têtes de ligne et de colonne.

Hiérarchies et niveaux

Les étiquettes peuvent être combinées en hiérarchies composées d'un ou plusieurs niveaux. Par exemple, les labels de la dimension Store sont naturellement regroupés dans une hiérarchie avec des niveaux :

Pays

État

Ville

Magasin.

Les valeurs agrégées sont calculées en fonction des niveaux de hiérarchie, par exemple volume des ventes pour les USA (niveau "Pays") ou pour la Californie (niveau "État"). Il est possible d'implémenter plusieurs hiérarchies dans une dimension - par exemple, pour le temps : (Année, Trimestre, Mois, Jour) et (Année, Semaine, Jour).

Architecture des applications OLAP

Tout ce qui a été dit ci-dessus à propos d'OLAP concernait essentiellement la présentation multidimensionnelle des données. La manière dont les données sont stockées, en gros, ne concerne ni l'utilisateur final ni les développeurs de l'outil utilisé par le client.

La multidimensionnalité dans les applications OLAP peut être divisée en trois niveaux :

Représentation des données multidimensionnelles : outils pour l'utilisateur final qui permettent une visualisation et une manipulation multidimensionnelles des données ; couche représentation multidimensionnelle fait abstraction de la structure physique des données et perçoit les données comme multidimensionnelles.
Traitement multidimensionnel - un outil (langage) pour formuler des requêtes multidimensionnelles (relationnel traditionnel Langage SQL s'avère ici inadapté) et un processeur capable de traiter et d'exécuter une telle demande.
Le stockage multidimensionnel est un moyen d'organiser physiquement les données qui garantit l'exécution efficace des requêtes multidimensionnelles.

Les deux premiers niveaux sont obligatoires dans tous les outils OLAP. Le troisième niveau, bien que répandu, n'est pas nécessaire, puisque les données pour une représentation multidimensionnelle peuvent être extraites de structures relationnelles ordinaires ; Dans ce cas, le processeur de requêtes multidimensionnelles traduit les requêtes multidimensionnelles en requêtes SQL exécutées par le SGBD relationnel.

En règle générale, les produits OLAP spécifiques sont soit un outil de représentation de données multidimensionnelles, un client OLAP (par exemple, les tableaux croisés dynamiques dans Excel 2000 de Microsoft ou ProClarity de Knosys), soit un SGBD de serveur multidimensionnel, un serveur OLAP (par exemple, Oracle Express Server ou Microsoft OLAP Services).

La couche de traitement multidimensionnel est généralement intégrée au client OLAP et/ou au serveur OLAP, mais peut être isolée sous sa forme pure, comme le composant Pivot Table Service de Microsoft.

Aspects techniques du stockage de données multidimensionnelles

Comme mentionné ci-dessus, les outils d'analyse OLAP peuvent également extraire des données directement des systèmes relationnels. Cette approche était plus attractive à l'époque où les serveurs OLAP n'étaient pas inclus dans les listes de prix des principaux fabricants de SGBD. Mais aujourd'hui, Oracle, Informix et Microsoft proposent des serveurs OLAP à part entière, et même les responsables informatiques qui n'aiment pas créer un « zoo » de logiciels de différents fabricants sur leurs réseaux peuvent acheter (ou plutôt faire une demande correspondante à la direction de l'entreprise) Serveur OLAP de la même marque que le serveur de base de données principal.

Les serveurs OLAP, ou serveurs de bases de données multidimensionnelles, peuvent stocker leurs données multidimensionnelles de différentes manières. Avant d’envisager ces méthodes, nous devons parler d’un aspect aussi important que le stockage des unités. Le fait est que dans tout entrepôt de données - à la fois ordinaire et multidimensionnel - à côté des données détaillées extraites des systèmes opérationnels, des indicateurs récapitulatifs (indicateurs agrégés, agrégations) sont également stockés, comme la somme des volumes de ventes par mois, par catégorie marchandises, etc. Les agrégats sont stockés explicitement dans le seul but d'accélérer l'exécution des requêtes. Après tout, d'une part, en règle générale, une très grande quantité de données est accumulée dans l'entrepôt, et d'autre part, les analystes ne s'intéressent dans la plupart des cas pas à des indicateurs détaillés, mais à des indicateurs généralisés. Et s’il fallait additionner à chaque fois des millions de ventes individuelles pour calculer le total des ventes de l’année, la rapidité serait probablement inacceptable. Par conséquent, lors du chargement des données dans une base de données multidimensionnelle, tous les indicateurs totaux ou une partie d'entre eux sont calculés et stockés.

Mais comme vous le savez, il faut tout payer. Et pour la rapidité de traitement des demandes de données récapitulatives, il faut payer pour une augmentation des volumes de données et du temps de chargement. De plus, une augmentation du volume peut devenir littéralement catastrophique - selon l'un des tests standardisés un calcul complet des agrégats pour 10 Mo de données originales nécessitait 2,4 Go, c'est-à-dire que les données ont augmenté 240 fois ! Le degré de « gonflement » des données lors du calcul des agrégats dépend du nombre de dimensions du cube et de la structure de ces dimensions, c'est-à-dire du rapport entre le nombre de « pères » et d'« enfants » à différents niveaux de mesure. Pour résoudre le problème du stockage des agrégats, des schémas parfois complexes sont utilisés, qui permettent d'obtenir une augmentation significative des performances des requêtes lors du calcul de tous les agrégats possibles.

Parlons maintenant des différentes options de stockage des informations. Les données granulaires et les agrégats peuvent être stockés dans des structures relationnelles ou multidimensionnelles. Le stockage multidimensionnel vous permet de traiter les données comme un tableau multidimensionnel, ce qui garantit des calculs tout aussi rapides d'indicateurs totaux et diverses transformations multidimensionnelles selon n'importe quelle dimension. Il y a quelque temps, les produits OLAP prenaient en charge le stockage relationnel ou multidimensionnel. Aujourd'hui, en règle générale, le même produit propose ces deux types de stockage, ainsi qu'un troisième type - mixte. Les conditions suivantes s'appliquent :

MOLAP(OLAP multidimensionnel) - les données détaillées et les agrégats sont stockés dans une base de données multidimensionnelle. Dans ce cas, la plus grande redondance est obtenue, puisque les données multidimensionnelles contiennent entièrement des données relationnelles.
ROLAP(OLAP relationnel) - les données détaillées restent là où elles « vivaient » à l'origine - dans la base de données relationnelle ; les agrégats sont stockés dans la même base de données dans des tables de service spécialement créées.
HOLAP(OLAP hybride) : les données détaillées restent en place (dans une base de données relationnelle) et les agrégats sont stockés dans une base de données multidimensionnelle.

Chacune de ces méthodes a ses propres avantages et inconvénients et doit être utilisée en fonction des conditions - le volume de données, la puissance du SGBD relationnel, etc.

Lors du stockage de données dans des structures multidimensionnelles, il existe un problème potentiel de « ballonnement » dû au stockage de valeurs vides. Après tout, si dans un tableau multidimensionnel l'espace est réservé à toutes les combinaisons possibles d'étiquettes de dimension, mais que seule une petite partie est réellement remplie (par exemple, un certain nombre de produits ne sont vendus que dans un petit nombre de régions), alors la plupart des le cube sera vide, même si l'espace sera occupé. Les produits OLAP modernes peuvent résoudre ce problème.

À suivre. À l'avenir, nous parlerons de produits OLAP spécifiques fabriqués par les principaux fabricants.

04/07/2011 Derek Comingore

Si vous avez travaillé dans un domaine lié à la technologie, vous avez probablement entendu le terme « cube » ; cependant, la plupart des administrateurs et développeurs de bases de données ordinaires ne travaillaient pas avec ces objets. Les cubes fournissent une architecture de données puissante pour agréger rapidement des informations multidimensionnelles. Si votre organisation a besoin d'analyser de gros volumes de données, alors solution idéale ce sera un cube

Qu'est-ce qu'un cube ?

Les bases de données relationnelles ont été conçues pour gérer des milliers de transactions simultanées tout en préservant les performances et l'intégrité des données. De par leur conception, les bases de données relationnelles ne sont pas efficaces pour regrouper et rechercher de grands volumes de données. Pour agréger et renvoyer de gros volumes de données, une base de données relationnelle doit recevoir une requête basée sur un ensemble, dont les informations seront collectées et agrégées à la volée. De telles requêtes relationnelles sont très coûteuses car elles reposent sur plusieurs jointures et fonctions d'agrégation; Les requêtes relationnelles agrégées sont particulièrement inefficaces lorsque vous travaillez avec de grandes quantités de données.

Les cubes sont des entités multidimensionnelles conçues pour combler cette lacune des bases de données relationnelles. En utilisant un cube, vous pouvez fournir aux utilisateurs une structure de données qui fournit une réponse rapide aux requêtes avec de grands volumes d'agrégation. Les cubes effectuent cette « magie d'agrégation » en agrégeant d'abord les données (dimensions) sur plusieurs dimensions. La pré-agrégation du cube est généralement effectuée lors du traitement. Lorsque vous traitez un cube, vous produisez des agrégations de données précalculées qui sont stockées sous forme binaire sur le disque.

Le cube est la structure de données centrale dans système opérateur Analyse des données OLAP de SQL Server Analytical Services (SSAS). Les cubes sont généralement construits à partir d'une base de données relationnelle sous-jacente appelée modèle dimensionnel, mais sont des entités techniques distinctes. Logiquement, un cube est un entrepôt de données composé de dimensions (dimensions) et de mesures (mesures). Les dimensions contiennent des fonctionnalités descriptives et des hiérarchies, tandis que les dimensions sont les faits que vous décrivez dans les dimensions. Les dimensions sont regroupées en combinaisons logiques appelées groupes de dimensions. Vous associez des dimensions à des groupes de mesures en fonction d'une caractéristique : le degré de détail.

DANS système de fichiers un cube est implémenté sous la forme d'une séquence de fichiers binaires liés. L'architecture binaire du cube facilite la récupération rapide de grands volumes de données multidimensionnelles.

J'ai mentionné que les cubes sont construits à partir d'une base de données relationnelle sous-jacente appelée modèle dimensionnel. Le modèle dimensionnel contient des tables relationnelles (fait et dimension) qui le connectent aux entités du cube. Les tableaux de faits contiennent des dimensions telles que la quantité d'un produit vendu. Les tables de dimensions stockent des attributs descriptifs tels que les noms de produits, les dates et les noms d'employés. En règle générale, les tables de faits et les tables de dimensions sont liées via des contraintes de clé étrangère primaire, les clés étrangères étant situées dans la table de faits (cette relation relationnelle est liée à l'attribut de granularité du cube évoqué ci-dessus). Lorsque les tables de dimensions sont liées directement à une table de faits, un schéma en étoile est formé. Lorsque les tables de dimensions ne sont pas directement liées à une table de faits, le résultat est un schéma en flocon de neige.

Veuillez noter que les modèles dimensionnels sont classés selon l'application. Un datamart est un modèle dimensionnel conçu pour un processus métier unique, tel que les ventes ou la gestion des stocks. Un entrepôt de données est un modèle dimensionnel conçu pour capturer les processus métier des composants afin de faciliter l'analyse des processus inter-métiers.

Logiciels requis

Maintenant que vous avez une compréhension de base de ce que sont les cubes et pourquoi ils sont importants, je vais mettre les choses en marche et vous faire découvrir étape par étape la construction de votre premier cube à l'aide de SSAS. Il y a quelques composants de base logiciel, dont vous aurez besoin, donc avant de commencer à construire votre premier cube, assurez-vous que votre système répond aux exigences.

Mon exemple de cube de ventes Internet sera construit à partir de la base de données de test AdventureWorksDW 2005. Je vais construire le cube de test à partir d'un sous-ensemble de tables trouvées dans la base de données de test qui seront utiles pour analyser les données de ventes sur Internet. La figure 1 montre la disposition de base des tables de base de données. Puisque j'utilise la version 2005, vous pouvez suivre mes instructions en utilisant SQL Server 2005 ou SQL Server 2008.

Figure 1. Sous-ensemble du magasin de données Adventure Works Internet Sales

La base de données de formation Adventure WorksDW 2005 est disponible sur le site Web CodePlex : msftdbprodsamples.codeplex.com. Recherchez le lien « Les exemples de bases de données de produits SQL Server 2005 sont toujours disponibles » (http://codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004). La base de données de formation est contenue dans le fichier AdventureWorksBI.msi (http://msftdbprodsamples.codeplex.com/releases/view/4004#DownloadId=11755).

Comme mentionné, vous devez avoir accès à une instance de SQL Server 2008 ou 2005, y compris les composants SSAS et Business Intelligence Development Studio (BIDS). J'utiliserai SQL Server 2008, vous constaterez donc peut-être des différences subtiles si vous utilisez SQL Server 2005.

Création d'un projet SSAS

La première chose à faire est de créer un projet SSAS à l'aide de BIDS. Recherchez BIDS dans le menu Démarrer puis dans le menu Microsoft SQL Server 2008/2005, sous-élément SQL Server Business Intelligence Development Studio. En cliquant sur ce bouton, vous lancerez BIDS avec l'écran de démarrage par défaut. Créer nouveau projet SSAS en sélectionnant Fichier, Nouveau, Projet. Vous verrez la boîte de dialogue Nouveau projet, illustrée par la figure 1. Sélectionnez le dossier Projet Analysis Services et définissez la description du projet sur SQLMAG_MyFirstCube. Cliquez sur OK.

Une fois le projet créé, faites un clic droit dessus dans l'Explorateur de solutions et sélectionnez menu contextuelÉlément de propriétés. Sélectionnez maintenant la section Déploiement sur le côté gauche de la boîte de dialogue SQLMAG_MyFirstCube : Pages de propriétés et examinez les paramètres du serveur cible et de la base de données, comme le montre la figure 2. Si vous travaillez dans un environnement SQL Server distribué, vous devrez vous qualifier. la propriété Target Server avec le nom du serveur sur lequel vous allez déployer. Cliquez sur OK lorsque vous êtes satisfait des paramètres de déploiement de ce projet SSAS.

Définir la source de données

Le premier objet que vous devez créer est la source de données. Un objet source de données fournit le schéma et les données utilisés pour créer les objets associés et à la base du cube. Pour créer un objet source de données dans BIDS, utilisez l'Assistant Source Données Assistant Source.

Démarrez l'Assistant Source de données en cliquant avec le bouton droit sur le dossier Source de données dans le panneau Explorateur de solutions et en sélectionnant Nouvelle source de données. Vous constaterez que la création d'objets SSAS dans BIDS a une nature développementale. L'assistant vous guide d'abord à travers le processus de création d'objet et les paramètres généraux. Ensuite, vous ouvrez l'objet SSAS résultant dans le concepteur et le personnalisez en détail si nécessaire. Une fois que vous avez dépassé l'écran d'invite, définissez une nouvelle connexion de données en cliquant sur le bouton Nouveau. Sélectionnez et créez une nouvelle connexion basée sur Native OLEDB\SQL Server Native Client 10 pointant vers celle que vous souhaitez Serveur SQL Serveur propriétaire de l'instance de base de données souhaitée. Vous pouvez utiliser l'authentification Windows ou SQL Server, en fonction des paramètres de votre environnement SQL Server. Cliquez sur le bouton Tester la connexion pour vous assurer que vous avez correctement identifié la connexion à la base de données, puis cliquez sur OK.

Viennent ensuite les informations d'emprunt d'identité qui, comme l'association de données, dépendent de la façon dont l'environnement SQL Server est structuré. L'emprunt de privilèges est le contexte de sécurité sur lequel SSAS s'appuie lors du traitement de ses objets. Si vous gérez votre déploiement sur un serveur unique principal (ou un ordinateur portable), comme je suppose que la plupart des lecteurs le sont, vous pouvez simplement sélectionner l'option Utiliser le compte de service. Cliquez sur Suivant pour terminer l'assistant de source de données et définissez AWDW2005 comme nom de source de données. Il est assez pratique d'utiliser cette méthode à des fins de test, mais dans un environnement de production réel, ce n'est pas la meilleure solution. meilleur entrainement- utiliser un compte de service. Il est préférable de spécifier le domaine Comptes pour emprunter les droits de connexion SSAS à la source de données.

Vue de la source de données

Pour la source de données que vous avez définie, l'étape suivante du processus de création du cube SSAS consiste à créer une vue de source de données (DSV). DSV offre la possibilité de séparer le schéma attendu par votre cube de celui de la base de données sous-jacente. Par conséquent, DSV peut être utilisé pour étendre le schéma relationnel sous-jacent lors de la création d'un cube. Certaines des fonctionnalités clés de DSV pour étendre les schémas de sources de données incluent les requêtes nommées, les relations logiques entre les tables et les colonnes calculées nommées.

Allons-y et cliquez avec le bouton droit sur le dossier DSV et sélectionnez Nouvelle vue de source de données pour lancer l'assistant Créer une nouvelle vue DSV. Dans la boîte de dialogue, à l'étape Sélectionner une source de données, sélectionnez une connexion à une base de données relationnelle et cliquez sur Suivant. Sélectionnez les tables FactInternetSales, DimProduct, DimTime, DimCustomer et cliquez sur le bouton fléché droit pour déplacer ces tables vers la colonne Inclus. Enfin, cliquez sur Suivant et terminez l'assistant en acceptant le nom par défaut et en cliquant sur Terminer.

À ce stade, vous devriez avoir une vue DSV située sous le dossier Vues de source de données dans l’Explorateur de solutions. Double-cliquez sur le nouveau DSV pour lancer le concepteur DSV. Vous devriez voir les quatre tableaux pour un DSV donné, comme le montre la figure 2.

Création de dimensions de base de données

Comme je l'ai expliqué ci-dessus, les dimensions fournissent des fonctionnalités descriptives des dimensions et des hiérarchies qui sont utilisées pour permettre l'agrégation au-dessus du niveau de détail. Il est important de comprendre la différence entre une dimension de base de données et une dimension de cube : les dimensions de la base de données fournissent les objets de dimension sous-jacents pour les différentes dimensions du cube qui seront utilisées pour construire le cube.

Les dimensions de base de données et de cube offrent une solution élégante à un concept connu sous le nom de « dimensions de rôle ». Les dimensions basées sur les rôles sont utilisées lorsque vous devez utiliser plusieurs fois une seule dimension dans un cube. La date est un exemple parfait dans cette instance de cube : vous allez construire une seule dimension de date et la référencer une fois pour chaque date pour laquelle vous souhaitez analyser les ventes en ligne. La date du calendrier sera la première dimension que vous créerez. Cliquez avec le bouton droit sur le dossier Dimensions dans l'Explorateur de solutions et sélectionnez Nouvelle dimension pour lancer l'assistant de dimension. Sélectionnez Utiliser une table existante et cliquez sur Suivant à l’étape Sélectionner la méthode de création. À l'étape Spécifier les informations sur la source, spécifiez la table DimTime dans la liste déroulante Table principale et cliquez sur Suivant. Maintenant, à l'étape Sélectionner les attributs de la dimension, vous devez sélectionner les attributs de la dimension temporelle. Sélectionnez chaque attribut, comme le montre la figure 3.

Cliquez sur Suivant. Comme dernière étape, saisissez Dim Date dans le champ Nom et cliquez sur Terminer pour terminer l'assistant de dimensionnement. Vous devriez maintenant voir la nouvelle dimension Dim Date située sous le dossier Dimensions dans l’Explorateur de solutions.

Utilisez ensuite l'Assistant Dimension pour créer des dimensions produit et client. Suivez les mêmes étapes pour créer la dimension de base que précédemment. Lorsque vous travaillez avec l'assistant de dimension, assurez-vous de sélectionner tous les attributs potentiels à l'étape Sélectionner les attributs de dimension. Les valeurs par défaut des autres paramètres conviennent à une instance de cube de test.

Créer un cube de vente sur Internet

Maintenant que vous avez préparé les dimensions de la base de données, vous pouvez commencer à créer le cube. Dans l'Explorateur de solutions, cliquez avec le bouton droit sur le dossier Cubes et sélectionnez Nouveau Cube pour lancer l'Assistant Cube. Dans la fenêtre Sélectionner la méthode de création, sélectionnez l'option Utiliser les tables existantes. Sélectionnez la table FactInternetSales pour le groupe de mesures à l’étape Sélectionner les tables du groupe de mesures. Décochez les cases en regard des dimensions Clé de promotion, Clé de devise, Clé de territoire de vente et Numéro de révision à l'étape Sélectionner les mesures, puis cliquez sur Suivant.

Sur l'écran Sélectionner les dimensions existantes, assurez-vous que toutes les dimensions de base de données existantes sont sélectionnées pour être utilisées comme dimensions de cube. Parce que je souhaite garder ce cube aussi simple que possible, désélectionnez la dimension FactInternetSales à l'étape Sélectionner de nouvelles dimensions. En laissant la dimension FactInternetSales sélectionnée, vous créerez ce que l'on appelle une dimension de fait ou dimension dégénérée. Les dimensions de faits sont des dimensions créées à l'aide d'une table de faits de base, par opposition à une table de dimensions traditionnelle.

Cliquez sur Suivant pour passer à l'étape Fin de l'assistant et saisissez « Mon premier cube » dans le champ Nom du cube. Cliquez sur le bouton Terminer pour terminer le processus de l'assistant de création de cube.

Expansion et traitement d'un cube

Vous êtes maintenant prêt à déployer et traiter le premier cube. Cliquez avec le bouton droit sur l’icône du nouveau cube dans l’Explorateur de solutions et sélectionnez Processus. Vous verrez une boîte de message indiquant que le contenu semble obsolète. Cliquez sur Oui pour déployer le nouveau cube sur le serveur SSAS cible. Lorsque vous déployez un cube que vous envoyez Fichier XML for Analisis (XMLA) vers le serveur SSAS cible, qui crée un cube sur le serveur lui-même. Comme mentionné, le traitement d'un cube remplit ses fichiers binaires sur le disque avec les données de la source principale, ainsi que les métadonnées supplémentaires que vous avez ajoutées (dimensions du cube, dimensions et paramètres).

Une fois le processus de déploiement terminé, une nouvelle boîte de dialogue Process Cube apparaît. Cliquez sur le bouton Exécuter pour commencer le traitement du cube, qui s'ouvre avec la fenêtre Progression du processus. Une fois le traitement terminé, cliquez sur Fermer (deux fois pour fermer les deux boîtes de dialogue) pour terminer les processus de déploiement et de traitement du cube.

Vous avez maintenant construit, déployé et traité votre premier cube. Vous pouvez afficher ce nouveau cube en cliquant dessus avec le bouton droit dans la fenêtre de l'Explorateur de solutions et en sélectionnant Parcourir. Faites glisser les dimensions vers le centre du tableau croisé dynamique et les attributs de dimension sur les lignes et les colonnes pour explorer votre nouveau cube. Remarquez la rapidité avec laquelle le cube traite diverses requêtes d'agrégation. Vous pouvez désormais apprécier la puissance illimitée, et donc la valeur commerciale, du cube OLAP.

Derek Comingore ( [email protégé]) est architecte senior chez B. I. Voyage, qui possède le statut de partenaire Microsoft dans le domaine de l'analyse commerciale. Possède le titre SQL Server MVP et plusieurs certifications Microsoft

Populaire dans la catégorie :