I problemi di analisi, OLAP, data warehouse sono di crescente interesse tra gli specialisti IT russi. Ad oggi, molti buoni materiali accademici su questo argomento, incluso uno introduttivo, sono stati pubblicati sulla nostra stampa da computer e su Internet. Portiamo alla tua attenzione un articolo in cui cerchiamo consapevolmente di spiegare OLAP "sulle dita", usando un esempio concreto. La pratica dimostra che tale spiegazione è necessaria per alcuni professionisti IT e in particolare per gli utenti finali.
Pertanto, OLAP * 1 come prima approssimazione, "sulle dita", può essere definito come un modo speciale per analizzare i dati e ricevere report. La sua essenza è fornire all'utente una tabella multidimensionale che riepiloghi automaticamente i dati in varie sezioni e che consenta di gestire in modo interattivo i calcoli e la forma del report. Questo articolo parlerà della tecnologia e delle operazioni di base di OLAP con l'esempio dell'analisi delle fatture di un'impresa impegnata nel commercio all'ingrosso di prodotti alimentari.
* 1. OLAP - Elaborazione analitica online, analisi dei dati online.
Un sistema OLAP della classe più semplice ed economica - il client OLAP * 1 sarà considerato come uno strumento. Ad esempio, è stato selezionato il prodotto più semplice dall'elenco dei client OLAP: "Kontur Standard" di Intersoft Lab. (Per chiarezza più avanti nell'articolo, i termini generalmente accettati OLAP saranno indicati in grassetto e accompagnati da controparti in lingua inglese.)
* 1. Maggiori dettagli sulla classificazione dei sistemi OLAP sono descritti nell'articolo "OLAP, Made in Russia" in PC Week / RE, n. 3/2001.
Quindi, iniziamo con il sistema. Per prima cosa devi descrivere l'origine dati: il percorso della tabella e dei suoi campi. Questo è il compito di un utente che conosce l'implementazione fisica del database. Per gli utenti finali, traduce il nome della tabella e i suoi campi nei termini dell'area tematica. Dietro la "fonte di dati" si trova una tabella locale, tabella o vista del server SQL o procedura memorizzata.
Molto probabilmente, in un particolare database, le fatture vengono archiviate non in una, ma in diverse tabelle. Inoltre, alcuni campi o record potrebbero non essere utilizzati per l'analisi. Pertanto, viene creato un campione (set di risultati o query), in cui sono configurate le seguenti impostazioni: un algoritmo per unire le tabelle in base ai campi chiave, le condizioni di filtro e un insieme di campi restituiti. Chiameremo il nostro campione "Invoice" e inseriremo tutti i campi dell'origine dati "Invoice". Pertanto, lo specialista IT, creando un livello semantico, nasconde l'implementazione fisica del database all'utente finale.
Quindi viene configurato il rapporto OLAP. Questo può essere fatto da uno specialista nell'area tematica. Innanzitutto, i campi di un campione di dati flat sono divisi in due gruppi: fatti (fatti o misure) e dimensioni (dimensioni). I fatti sono numeri e le dimensioni sono "tagli" in cui i fatti verranno riassunti. Nel nostro esempio, le misurazioni saranno: “Regione”, “Città”, “Acquirente”, “Merci”, “Data”, e ci sarà un fatto: il campo “Importo” della fattura. Per il fatto, è necessario selezionare uno o più algoritmi di aggregazione. OLAP è in grado non solo di sintetizzare i risultati, ma anche di eseguire calcoli più complessi, fino all'analisi statistica inclusa. Se si selezionano più algoritmi di aggregazione, verranno creati fatti calcolati virtuali. Nell'esempio, viene selezionato un algoritmo di aggregazione - "Somma".
Una caratteristica speciale dei sistemi OLAP è la generazione di misurazioni e dati sui periodi di tempo più elevati dalla data e il calcolo automatico dei totali per questi periodi. Scegliamo i periodi "Anno", "Trimestre" e "Mese", e non ci saranno dati per ogni giorno nel rapporto, ma appariranno le misurazioni generate "Anno", "Trimestre" e "Mese". Chiamare il rapporto "Analisi delle vendite" e salvarlo. Il lavoro sulla creazione dell'interfaccia dell'applicazione analitica è completato.
Ora, avviando questa interfaccia giornalmente o mensilmente, l'utente vedrà una tabella e un grafico in cui vengono riepilogate le fatture per merci, clienti e periodi.
Affinché le manipolazioni con i dati siano intuitive, gli strumenti per la gestione di una tabella dinamica sono gli elementi della tabella stessa: le sue colonne e righe. L'utente può spostarli, eliminare, filtrare ed eseguire altre operazioni OLAP. In questo caso, la tabella calcola automaticamente i nuovi totali parziali e i totali finali.
Ad esempio, trascinando ("sposta" operazione) la colonna "Prodotto" al primo posto, otterremo un rapporto di confronto - "Confronto dei volumi di vendita dei beni per un anno". Per aggregare i dati per un anno, trascina semplicemente le colonne "Trimestre" e "Mese" nella parte superiore della tabella - "area di misurazione inattiva". Le misurazioni "Trimestre" e "Mese" trasferite in quest'area verranno chiuse (operazione di "dimensione chiusa"), ovvero esclusa dal report; mentre i fatti sono riassunti nel corso dell'anno. Nonostante il fatto che le misurazioni siano chiuse, è possibile impostare anni, trimestri e mesi specifici per il filtraggio dei dati (operazione di "filtro").
Per maggiore chiarezza, cambiamo il tipo di grafico che illustra la tabella OLAP e la sua posizione sullo schermo.
L'approfondimento dei dati (operazione di "drill down") consente di ottenere informazioni più dettagliate sulle vendite del prodotto a cui siamo interessati. Facendo clic sul segno "+" di fronte al prodotto "Caffè", vedremo i suoi volumi di vendita per regioni. Dopo aver aperto la regione degli Urali, riceveremo le vendite per città della regione degli Urali, dopo aver approfondito i dati su Ekaterinburg, saremo in grado di visualizzare i dati sugli acquirenti all'ingrosso di questa città.
È possibile utilizzare le misurazioni aperte per impostare i filtri. Per confrontare la dinamica delle vendite di dolci a Mosca e Ekaterinburg, imposteremo i filtri per le misurazioni di "Merci" e "Città".
Chiudere le misurazioni non necessarie e selezionare il tipo di grafico "Linea". Nel grafico risultante, è possibile tenere traccia delle dinamiche delle vendite, valutare le fluttuazioni stagionali e il rapporto tra cadute e crescita delle vendite di beni in diverse città.
Pertanto, eravamo convinti che la tecnologia OLAP consentisse all'utente di emettere dozzine di tipi di vari report da un'unica interfaccia controllando la tabella OLAP dinamica con il mouse. Il compito del programmatore che possiede tale strumento non è la codifica di routine dei moduli di report, ma la configurazione di un client OLAP per i database. Allo stesso tempo, i metodi per la gestione del report sono intuitivamente chiari per l'utente finale.
In effetti, OLAP è una naturale continuazione e sviluppo dell'idea dei fogli di calcolo. In effetti, l'interfaccia visiva OLAP è anche un foglio di calcolo, ma dotata di una potente macchina informatica e di uno standard speciale per la presentazione e la gestione dei dati. Inoltre, alcuni client OLAP sono implementati come componente aggiuntivo in MS Excel. Pertanto, l'esercito di milioni di "colletti bianchi", che possiede con sicurezza fogli di calcolo, padroneggia rapidamente gli strumenti OLAP. Per loro, questa è una "rivoluzione di velluto", che offre nuove opportunità, ma non associata alla necessità di riapprendere.
Se il lettore, dopo aver letto questo articolo, non ha perso interesse per OLAP, può fare riferimento ai materiali citati all'inizio. Collezioni di tali materiali sono disponibili su numerosi siti Internet, incluso il sito del laboratorio Intersoft - www.iso.ru. Puoi anche scaricare una versione demo del sistema "Kontur Standard" da esso con l'esempio descritto nell'articolo.
La prima interfaccia della tabella pivot, chiamata anche report pivot, è stata inclusa in Excel nel 1993 (Excel versione 5.0). Nonostante le molte utili funzioni, non viene quasi mai utilizzato dalla maggior parte degli utenti di Excel. Anche gli utenti esperti spesso intendono con il termine "report di riepilogo" qualcosa costruito utilizzando formule complesse. Proviamo a rendere popolare l'uso delle tabelle pivot nel lavoro quotidiano degli economisti. L'articolo discute le basi teoriche della creazione di report di riepilogo, fornisce consigli pratici per il loro utilizzo e fornisce anche un esempio di accesso ai dati basato su diverse tabelle.
Termini di analisi dei dati multidimensionali
La maggior parte degli economisti ha sentito i termini "dati multidimensionali", "cubo virtuale", "tecnologie OLAP", ecc. Ma con una conversazione dettagliata, di solito si scopre che quasi tutti non capiscono davvero di cosa stanno parlando. Cioè, le persone significano qualcosa di complesso e di solito non correlato alle loro attività quotidiane. In realtà non è così.
Dati multidimensionali, misure
È sicuro affermare che gli economisti si confrontano quasi costantemente con dati multidimensionali, ma cercano di presentarli in una forma predeterminata usando fogli di calcolo. Per multidimensionalità si intende la capacità di inserire, visualizzare o analizzare le stesse informazioni con un cambiamento di aspetto, utilizzando vari gruppi e ordinamento dei dati. Ad esempio, un piano di vendita può essere analizzato secondo i seguenti criteri:
- tipi o gruppi di merci;
- marchi o categorie di prodotti;
- periodi (mese, trimestre, anno);
- acquirenti o gruppi di acquirenti;
- regioni di vendita
- eccetera
Ognuno di questi criteri in termini di analisi di dati multivariati è chiamato "misurazione". Possiamo dire che una misurazione caratterizza le informazioni per un insieme specifico di valori. Un tipo speciale di misurazione di informazioni multidimensionali sono i "dati". Nel nostro esempio, i dati del piano di vendita possono essere:
- volume delle vendite;
- prezzo di vendita;
- sconto individuale
- eccetera
Teoricamente, i dati possono anche essere una misurazione standard di informazioni multidimensionali (ad esempio, è possibile raggruppare i dati in base al prezzo di vendita), ma in genere i dati sono un tipo speciale di valore.
Pertanto, possiamo affermare che nel lavoro pratico gli economisti utilizzano due tipi di informazioni: dati multidimensionali (numeri effettivi e pianificati con molti attributi) e riferimenti (caratteristiche o misure dei dati).
OLAP
L'abbreviazione OLAP (elaborazione analitica online) nella traduzione letterale suona come "elaborazione analitica in tempo reale". La definizione non è molto specifica; quasi ogni rapporto di qualsiasi prodotto software può essere sintetizzato sotto di esso. In termini di significato, OLAP implica la tecnologia di lavorare con report speciali, incluso il software, per ottenere e analizzare solo dati strutturati multidimensionali. Uno dei prodotti software più diffusi che implementano la tecnologia OLAP è SQL Server Analysis Server. Alcuni addirittura lo considerano erroneamente l'unico rappresentante dell'implementazione software di questo concetto.
Cubo di dati virtuali
Un "cubo virtuale" (cubo multidimensionale, cubo OLAP) è un termine speciale proposto da alcuni fornitori di software specializzati. I sistemi OLAP di solito preparano e archiviano i dati nelle proprie strutture e speciali interfacce di analisi (ad esempio report di riepilogo di Excel) accedono ai dati di questi cubi virtuali. Inoltre, l'uso di un archivio così dedicato non è necessario per l'elaborazione di informazioni multidimensionali. Nel caso generale cubo virtuale - Questa è una matrice di dati multidimensionali appositamente ottimizzati che viene utilizzata per creare report di riepilogo. Può essere ottenuto sia tramite strumenti software specializzati, sia attraverso un semplice accesso alle tabelle del database o qualsiasi altra fonte, ad esempio, a una tabella Excel.
Tabella riassuntiva
Una tabella pivot (tabella pivot) è un'interfaccia utente per la visualizzazione di dati multidimensionali. Utilizzando questa interfaccia, è possibile raggruppare, ordinare, filtrare e modificare la posizione dei dati al fine di ottenere vari campioni analitici. Il report viene aggiornato tramite la semplice interfaccia utente, i dati vengono automaticamente aggregati secondo le regole specificate e non è richiesto l'inserimento aggiuntivo o ripetuto di alcuna informazione. L'interfaccia della tabella pivot di Excel è forse il prodotto di dati multidimensionale più popolare. Supporta sia origini dati esterne (cubi OLAP e database relazionali) sia intervalli interni di fogli di calcolo come origine dati. A partire dalla versione 2000 (9.0), Excel supporta anche un modulo grafico per la visualizzazione di dati multidimensionali: un grafico pivot.
L'interfaccia della tabella pivot implementata in Excel consente di posizionare dimensioni di dati multidimensionali in un'area del foglio di lavoro. Per semplicità, puoi immaginare una tabella pivot come un report che si trova sopra un intervallo di celle (in effetti, esiste un certo legame dei formati di cella con i campi della tabella pivot). Una tabella pivot di Excel ha quattro aree di visualizzazione: filtro, colonne, righe e dati. Le dimensioni dei dati sono indicate come campi della tabella pivot. Questi campi hanno le loro proprietà e il loro formato di visualizzazione.
Ancora una volta, voglio attirare l'attenzione sul fatto che la tabella pivot di Excel è destinata esclusivamente all'analisi dei dati senza la possibilità di modificare le informazioni. Più vicino nel significato sarebbe l'uso diffuso del termine "rapporto di sintesi" (Rapporto Pivot), ed è così che questa interfaccia è stata chiamata fino al 2000. Ma per qualche motivo, nelle versioni successive, gli sviluppatori l'hanno abbandonato.
Modifica delle tabelle pivot
Per definizione, la tecnologia OLAP, in linea di principio, non implica la possibilità di modificare i dati di origine quando si lavora con i report. Tuttavia, sul mercato si è formata un'intera classe di sistemi software che implementa sia l'analisi sia la modifica diretta dei dati in tabelle multidimensionali. Fondamentalmente, tali sistemi sono focalizzati su attività di budget.
Utilizzando gli strumenti di automazione di Excel integrati, è possibile risolvere molte attività non standard. Un esempio di modifica per le tabelle pivot di Excel basate sui dati del foglio di lavoro è disponibile sul nostro sito Web.
Preparazione di dati multidimensionali
Esaminiamo l'applicazione pratica delle tabelle pivot. Proviamo ad analizzare i dati di vendita in varie direzioni. file pivottableexample.xls è composto da più fogli. foglio esempiocontiene informazioni di base sulle vendite per un periodo specifico. Per semplicità, analizzeremo l'unico indicatore numerico: le vendite in kg. Sono disponibili le seguenti dimensioni dei dati chiave: prodotti, acquirente e corriere (società di trasporto). Inoltre, ci sono diverse dimensioni aggiuntive dei dati che sono indicative di un prodotto: tipo, marca, categoria, fornitore e anche acquirente: tipo. Questi dati sono raccolti sul foglio di riferimento. In pratica, tali misurazioni possono essere molto più grandi.
foglio esempiocontiene uno strumento standard di analisi dei dati - filtro automatico. Guardando un esempio di compilazione di una tabella, è ovvio che i dati di vendita per data (sono disposti in colonne) si prestano all'analisi normale. Inoltre, utilizzando il filtro automatico, puoi provare a riepilogare i dati mediante combinazioni di uno o più criteri chiave. Non ci sono assolutamente informazioni su marchi, categorie e tipi. Non è possibile raggruppare i dati con la somma automatica tramite una chiave specifica (ad esempio, dai clienti). Inoltre, il set di date è fisso e non è possibile visualizzare le informazioni di riepilogo per un determinato periodo, ad esempio 3 giorni, automaticamente.
In generale, la presenza di una posizione data predefinita in questo esempio è il principale svantaggio della tabella. Organizzando le date in colonne, abbiamo in qualche modo predeterminato la misurazione di questa tabella, privandoci così dell'opportunità di usare l'analisi usando le tabelle pivot.
In primo luogo, dobbiamo eliminare questo inconveniente, ad es. rimuovere la posizione predefinita di una delle dimensioni dei dati di origine. Esempio di tabella corretta - foglio la vendita.
La tabella ha la forma di un registro di input di informazioni. Qui, una data è una misura uguale di dati. Va inoltre notato che per le successive analisi nelle tabelle pivot, la posizione relativa delle righe l'una rispetto all'altra (in altre parole, l'ordinamento) è completamente indifferente. Queste proprietà hanno record nei database relazionali. L'analisi di grandi volumi di database si concentra principalmente sull'interfaccia delle tabelle pivot. Pertanto, è necessario rispettare queste regole quando si lavora con un'origine dati sotto forma di intervalli di celle. Allo stesso tempo, nessuno vieta l'uso degli strumenti dell'interfaccia di Excel nel lavoro: le tabelle pivot analizzano solo i dati e la formattazione, i filtri, il raggruppamento e l'ordinamento delle celle di origine possono essere arbitrari.
Dal filtro automatico al rapporto di riepilogo
Teoricamente, sui dati della scheda di vendita, è già possibile effettuare analisi in tre dimensioni: merci, clienti e corrieri. I dati sulle proprietà dei prodotti e dei clienti non sono disponibili in questo foglio, il che, di conseguenza, non consentirà loro di essere visualizzati nella tabella di riepilogo. Nella normale modalità di creazione di una tabella pivot per i dati di origine, Excel non consente di collegare i dati di più tabelle in determinati campi. È possibile aggirare questa limitazione tramite il software: vedere un esempio di aggiunta a questo articolo sul nostro sito Web. Per non ricorrere a metodi software di elaborazione delle informazioni (soprattutto perché non sono universali), è necessario aggiungere ulteriori caratteristiche direttamente al modulo di iscrizione a giornale - consultare il foglio di analisi delle vendite.
L'uso delle funzioni VLOOKUP semplifica l'integrazione dei dati di origine con caratteristiche mancanti. Ora, usando un filtro automatico, puoi analizzare i dati in varie dimensioni. Ma il problema dei raggruppamenti rimane irrisolto. Ad esempio, tenere traccia dell'importo solo per marchio per determinate date è piuttosto problematico. Se si è limitati alle formule di Excel, è necessario creare ulteriori campioni utilizzando la funzione SUMIF.
Ora vediamo quali funzionalità offre l'interfaccia della tabella pivot. Sul foglio Analisi del codicecostruito diversi report basati su un intervallo di celle con dati del foglio ProdazhiAnaliz.
La prima tabella di analisi è creata tramite l'interfaccia di Excel 2007 Nastro \\ Inserisci \\ Tabella pivot (nel menu di Excel 2000-2003 Dati \\ Tabella pivot).
La seconda e la terza tabella vengono create mediante la copia e la successiva personalizzazione. L'origine dati per tutte le tabelle è la stessa. È possibile verificarlo modificando i dati di origine, quindi è necessario aggiornare i dati dei report di riepilogo.
Dal nostro punto di vista, i vantaggi nella visibilità delle informazioni sono evidenti. È possibile scambiare filtri, colonne e righe e nascondere determinati gruppi di valori di qualsiasi dimensione, applicare il trascinamento della selezione manuale e l'ordinamento automatico.
Proprietà e formattazione
Oltre a visualizzare direttamente i dati, esiste una vasta gamma di opzioni per visualizzare l'aspetto delle tabelle pivot. I dati in eccesso possono essere nascosti utilizzando i filtri. Per un singolo elemento o campo, è più semplice utilizzare la voce di menu contestuale Elimina(nella versione 2000-2003 Nascondi).
È anche desiderabile specificare la visualizzazione di altri elementi della tabella pivot non attraverso la formattazione della cella, ma attraverso l'impostazione del campo o dell'elemento della tabella pivot. Per fare ciò, sposta il puntatore del mouse sull'elemento desiderato, attendi l'apparizione di una forma speciale del cursore (sotto forma di una freccia), quindi seleziona l'elemento con un solo clic. Dopo la selezione, è possibile modificare la vista tramite la barra multifunzione, il menu di scelta rapida o richiamare la finestra di dialogo del formato cella standard:
Inoltre, Excel 2007 ha introdotto molti stili di visualizzazione della tabella pivot predefiniti:
Si noti che i filtri di controllo e le aree di trascinamento sono attivi nel grafico.
Accesso a dati esterni
Come già notato, forse il più grande effetto dell'uso delle tabelle pivot può essere ottenuto accedendo ai dati da fonti esterne: cubi OLAP e query di database. Tali fonti di solito memorizzano grandi quantità di informazioni e hanno anche una struttura relazionale predefinita, che semplifica la determinazione delle dimensioni dei dati multidimensionali (campi nella tabella pivot).
Excel supporta molti tipi di origini dati esterne:
Il massimo effetto dell'utilizzo di fonti esterne di informazioni può essere ottenuto utilizzando strumenti di automazione (programmi VBA) sia per ottenere dati che per la loro elaborazione preliminare in tabelle di riepilogo.
Analytical Analytical Processing (OLAP) è una tecnologia che viene utilizzata per organizzare grandi database aziendali e supportare la business intelligence. I database OLAP sono divisi in uno o più cubi e ciascuno di essi è organizzato dall'amministratore del cubo in modo che corrisponda al metodo di acquisizione e analisi dei dati per facilitare la creazione e l'uso dei report delle tabelle pivot e dei report dei grafici pivot necessari.
In questo articolo
Che cos'è la business intelligence?
Un analista aziendale spesso desidera avere una visione d'insieme di un'azienda al fine di visualizzare tendenze più ampie basate su dati aggregati, nonché di visualizzare le tendenze con qualsiasi numero di variabili. La business intelligence è il processo di estrazione dei dati da un database OLAP e dell'analisi di questi dati per ottenere informazioni che possono essere utilizzate per prendere decisioni aziendali informate ed eseguire azioni. Ad esempio, con OLAP e la business intelligence, è possibile rispondere alle seguenti domande sui dati aziendali.
Come si confrontano le vendite totali di tutti i prodotti nel 2007 con le vendite dal 2006?
Come si confronta con la data e l'ora in un periodo redditizio negli ultimi cinque anni?
Quanti soldi hanno speso i clienti per 35 l'anno scorso e come è cambiato questo comportamento nel tempo?
Quanti prodotti sono stati venduti in due paesi / regioni specifici questo mese, a differenza dello stesso mese dell'anno scorso?
Per ogni gruppo di età del cliente Qual è la separazione della redditività (sia percentuale del margine che totale) per categoria di prodotto?
Cerca i migliori e più bassi venditori, distributori, fornitori, clienti, partner e clienti.
Che cos'è l'elaborazione analitica online (OLAP)?
I database OLAP (Online Analytical Processing) semplificano le query di business intelligence. OLAP è una tecnologia di database ottimizzata per query e report, non per l'elaborazione delle transazioni. L'origine dati per OLAP sono i database di elaborazione delle transazioni online (OLTP), che di solito sono archiviati in data warehouse. I dati OLAP vengono estratti da questi dati storici e combinati in strutture che consentono analisi complesse. I dati OLAP sono anche organizzati gerarchicamente e archiviati in cubi, non in tabelle. Questa è una tecnologia sofisticata che utilizza strutture multidimensionali per fornire un rapido accesso ai dati per l'analisi. In questa organizzazione, per un report tabella pivot o un report grafico pivot, è possibile visualizzare facilmente dati di riepilogo di alto livello, come i risultati delle vendite per un paese o una regione, nonché visualizzare informazioni sui siti in cui le vendite sono particolarmente alte o deboli.
I database OLAP sono progettati per accelerare il caricamento dei dati. Poiché il server OLAP e non Microsoft Office Excel calcola i valori aggregati, è necessario inviare meno dati a Excel durante la creazione o la modifica del report. Questo approccio consente di lavorare con una quantità maggiore di dati di origine rispetto a se i dati fossero organizzati in un database tradizionale, in cui Excel recupera tutti i singoli record e calcola i valori generalizzati.
I database OLAP contengono due tipi principali di dati: misure, che sono dati numerici, quantità e medie, che vengono utilizzate per prendere decisioni aziendali informate e dimensioni, che sono categorie utilizzate per semplificare tali misure. I database OLAP ti aiutano a organizzare i tuoi dati usando molti livelli di dettaglio, usando le stesse categorie che conosci per l'analisi dei dati.
Nelle sezioni seguenti, ogni componente è descritto in dettaglio di seguito.
cubico Una struttura di dati che combina misure per livello e gerarchia di ogni dimensione che si desidera analizzare. I cubi combinano diverse dimensioni, come tempo, area geografica e linee di prodotti, con totali come vendite e scorte. I cubi non sono "cubi" in senso matematico rigoroso, poiché non hanno necessariamente gli stessi lati. Tuttavia, rappresentano la metafora di Apt per un concetto complesso.
di misura Un set di valori in un cubo basato su una colonna in una tabella dei fatti del cubo e che di solito è un valore numerico. Le misure sono valori centrali a Cuba che sono pre-elaborati, elaborati e analizzati. Gli esempi più comuni sono le vendite, i ricavi, i ricavi e le spese.
membro Un elemento in una gerarchia che rappresenta una o più occorrenze di dati. Un elemento può essere unico o non unico. Ad esempio, 2007 e 2008 rappresentano elementi unici a livello di anno della dimensione temporale, mentre gennaio rappresenta elementi non unici a livello di mese, poiché la dimensione temporale ha più di un gennaio, poiché contiene dati per più di un anno.
Elemento calcolato Un membro di dimensione il cui valore viene calcolato in fase di esecuzione utilizzando un'espressione. I valori dei membri calcolati possono essere derivati \u200b\u200bdai valori degli altri membri. Ad esempio, l'elemento di profitto calcolato può essere determinato sottraendo il valore dell'articolo nonché i costi dal valore dell'articolo, dalle vendite.
misurazione Un insieme di una o più gerarchie ordinate di livelli cubo che l'utente comprende e utilizza come base per l'analisi dei dati. Ad esempio, una dimensione geografica può includere livelli per un paese / regione, stato / distretto e città. Inoltre, la dimensione temporale può includere una gerarchia con i livelli di "anno", "trimestre", "mese" e "giorno". In un report tabella pivot o report grafico pivot, ogni gerarchia diventa un insieme di campi che è possibile espandere e comprimere per mostrare livelli inferiori o superiori.
gerarchia Una struttura ad albero logico che organizza i membri della dimensione in modo che ogni membro abbia un genitore e zero o più figli. Un membro figlio è un membro di un gruppo precedente nella gerarchia direttamente associato al membro corrente. Ad esempio, in una gerarchia temporale contenente livelli di trimestre, mese e giorno, gennaio è figlio di Qtr1. Un genitore è un membro di livello inferiore nella gerarchia direttamente associato al membro corrente. Il valore principale è in genere il consolidamento dei valori di tutti gli elementi figlio. Ad esempio, in una gerarchia temporale che contiene i livelli di "trimestre", "mese" e "giorno", Qtr1 è il genitore di gennaio.
livello In una gerarchia, i dati possono essere organizzati in livelli di dettaglio sempre più alti, come anni, trimestri, mesi e livelli giornalieri in una gerarchia temporale.
Funzioni OLAP di Excel
Recupero dati OLAP È possibile connettersi a origini dati OLAP nello stesso modo in cui è possibile connettersi ad altre origini dati esterne. È possibile lavorare con database creati utilizzando Microsoft SQL Server OLAP versione 7.0, Microsoft SQL Server Analysis Services versione 2000 e Microsoft SQL Server Analysis Services versione 2005 e prodotti Microsoft OLAP server. Excel può anche funzionare con prodotti OLAP di terze parti compatibili con OLE-DB per OLAP.
I dati OLAP possono essere visualizzati solo come report tabella pivot o report grafico pivot o come funzione foglio convertita da report tabella pivot, ma non come intervallo di dati esterni. È possibile salvare i report delle tabelle pivot OLAP e i grafici pivot nei modelli di report, nonché creare file ODC (Office Data Connection) per connettersi ai database OLAP per le query OLAP. Quando si apre il file .odc in Excel, viene visualizzato un rapporto tabella pivot vuoto, pronto per il posizionamento.
Creazione di file cubi per l'utilizzo offline È possibile creare un file cubo autonomo (. Cub) con un sottoinsieme dei dati dal database del server OLAP. I file cubi offline vengono utilizzati per lavorare con i dati OLAP se non si è connessi alla rete. Utilizzando il cubo, è possibile lavorare con più dati in un report di una tabella pivot o in un report del grafico pivot rispetto ad altri e accelerare il recupero dei dati. È possibile creare file Cube solo se si utilizza un provider OLAP, ad esempio Microsoft SQL Analysis Services versione 2005, che supporta questa funzionalità.
Azione del server L'azione server è una funzione opzionale che un amministratore cubo OLAP può definire su un server che utilizza un elemento cubo o misura come parametro in una richiesta per recuperare informazioni a Cuba o per avviare un'altra applicazione, come un browser. Excel supporta URL, report, set di righe, analisi dettagliata e distribuzione di azioni dettagliate sul lato server, ma non supporta la propria istruzione e set di dati nativi.
KPI Un indicatore di prestazioni chiave è una misura calcolata speciale definita sul server che consente di tenere traccia degli "indicatori di prestazioni chiave", incluso lo stato (il valore corrente corrisponde a un numero specifico). e tendenza (valori nel tempo). Quando vengono visualizzati, il server può inviare le icone corrispondenti, simili alla nuova icona di Excel, in modo che siano allineate sopra o sotto i livelli di stato (ad esempio, per l'icona di arresto), nonché scorrere verso l'alto o verso il basso (ad esempio, l'icona della freccia di direzione).
Formattazione sul server Gli amministratori del cubo possono creare misure ed elementi calcolati utilizzando la formattazione del colore, la formattazione del carattere e la formattazione condizionale che possono essere assegnate come regola aziendale standard aziendale. Ad esempio, il formato del server per le entrate può essere un formato di valuta numerico, il colore della cella verde se il valore è maggiore o uguale a 30.000 e rosso se il valore è inferiore a 30.000 e lo stile del carattere è in grassetto se il valore è inferiore a 30.000 e se il valore è positivo - ordinario. maggiore o uguale a 30.000. Ulteriori informazioni sono disponibili.
Lingua dell'interfaccia di Office L'amministratore del cubo può definire traduzioni per dati ed errori sul server per gli utenti che devono visualizzare le informazioni sulla tabella pivot in un'altra lingua. Questa funzione è definita come una proprietà della connessione al file e le impostazioni internazionali del computer e del paese dell'utente devono corrispondere alla lingua dell'interfaccia.
Componenti software richiesti per accedere alle origini dati OLAP
Provider OLAP Per configurare le origini dati OLAP per Excel, è necessario uno dei seguenti provider OLAP.
Provider Microsoft OLAP Excel include un driver di origine dati e un software client per l'accesso ai database creati utilizzando Microsoft SQL Server olap versione 7.0, Microsoft SQL Server olap versione 2000 (8.0) e Microsoft SQL Server Analysis versione 2005 (9 , 0).
Provider OLAP di terze parti Per altri prodotti OLAP, è necessario installare driver e software client aggiuntivi. Per utilizzare Excel per lavorare con i dati OLAP, un prodotto di terze parti deve essere conforme allo standard OLE-DB per OLAP e deve essere compatibile con Microsoft Office. Per informazioni sull'installazione e l'utilizzo di un provider OLAP di terze parti, contattare l'amministratore di sistema o il fornitore del prodotto OLAP.
Database e file server Cuba Il software client OLAP di Excel supporta le connessioni a due tipi di database OLAP. Se il database sul server OLAP è disponibile sulla rete, è possibile ottenere dati grezzi direttamente da esso. Se si dispone di un file cubo autonomo che contiene dati OLAP o un file di definizione cubo, è possibile connettersi a questo file e ottenere i dati di origine da esso.
Fonti di dati Un'origine dati fornisce l'accesso a tutti i dati in un database OLAP o in un file cubo offline. Creando un'origine dati OLAP, è possibile basare i report su di essa e restituire i dati OLAP a Excel come report tabella pivot o report grafico pivot, nonché una funzione foglio convertita da un report tabella pivot.
Query Microsoft Utilizzando Query, è possibile recuperare i dati da un database esterno come Microsoft SQL o Microsoft Access. Non è necessario utilizzare una query per recuperare i dati da una tabella pivot OLAP associata a un file cubo. Ulteriori informazioni
Differenze nelle funzionalità dei dati di origine OLAP e non OLAP
Se si lavora con report di tabella pivot e grafici pivot da dati di origine OLAP e altri tipi di dati di origine, si noteranno alcune differenze nelle funzioni.
Estrazione dei dati Il server OLAP restituisce nuovi dati in Excel ogni volta che viene modificato il layout del report. Quando si utilizzano altri tipi di origini dati esterne, è necessario richiedere tutti i dati di origine alla volta oppure è possibile impostare i parametri per la query solo quando vengono visualizzati diversi elementi dei campi del filtro di report. Inoltre, hai diverse altre opzioni per l'aggiornamento del rapporto.
Nei report basati sui dati di origine OLAP, i parametri del campo filtro report non sono disponibili, la query in background non è disponibile e il parametro di ottimizzazione della memoria non è disponibile.
Nota: L'opzione di ottimizzazione della memoria non è inoltre disponibile per le origini dati OLEDB e i report delle tabelle pivot basati su un intervallo di celle.
Tipi di campo Dati di origine OLAP. i campi dimensione possono essere utilizzati solo come righe (righe), colonne (categoria) o campi pagina. I campi di misura possono essere utilizzati solo come campi valore. Per altri tipi di dati di origine, tutti i campi possono essere utilizzati in qualsiasi parte del report.
Accedi ai dettagli Per i dati di origine OLAP, il server determina i livelli di dettaglio disponibili e calcola i valori di riepilogo, pertanto i record con informazioni dettagliate che compongono i valori di riepilogo potrebbero non essere disponibili. Tuttavia, il server può fornire campi di proprietà che è possibile visualizzare. Altri tipi di dati di origine non hanno campi di proprietà, ma è possibile visualizzare informazioni di base per i valori di campi e elementi di dati, nonché elementi di visualizzazione senza dati.
I campi del filtro report OLAP potrebbero non essere disponibili tutto elementi piuttosto squadra Mostra pagine filtro report disponibili.
Ordinamento iniziale Per i dati di origine OLAP, gli elementi vengono prima visualizzati nell'ordine in cui vengono restituiti dal server OLAP. È possibile ordinare o riordinare manualmente gli elementi. Per altri tipi di dati di origine, gli elementi del nuovo report vengono prima ordinati in base al nome dell'elemento in ordine crescente.
li I server OLAP forniscono valori aggregati direttamente per il report, quindi non è possibile modificare le funzioni di riepilogo per i campi valore. Per altri tipi di dati di input, è possibile modificare la funzione di aggregazione per il campo valore e utilizzare diverse funzioni di riepilogo per lo stesso campo valore. Non è possibile creare campi calcolati ed elementi calcolati nei report con dati di origine OLAP.
totale intermedio Nei report con dati di origine OLAP, non è possibile modificare la funzione dei totali per i totali parziali. Con altri tipi di dati di origine, è possibile modificare i totali per i totali parziali e mostrare o nascondere i totali parziali per tutti i campi di righe e colonne.
Per i dati di origine OLAP, è possibile includere o escludere elementi nascosti durante il calcolo dei subtotali e dei totali complessivi. Per altri tipi di dati di origine, è possibile includere gli elementi nascosti dei campi filtro dei report nei subtotali, ma gli elementi nascosti in altri campi saranno esclusi per impostazione predefinita.
Lavora con il cubo OLAP in MS Excel
1. Ottenere l'autorizzazione per accedere al cubo OLAP di SQL Server Analysis Services (SSAS)
2. MS Excel 2016/2013/2010 deve essere installato sul tuo computer (puoi anche usare MS Excel 2007, ma non è conveniente lavorarci sopra e la funzionalità molto scarsa di MS Excel 2003)
3. Aprire MS Excel, eseguire la procedura guidata per configurare la connessione con il servizio analitico:
3.1 Specificare il nome o l'indirizzo IP del server OLAP corrente (a volte è necessario specificare il numero della porta aperta, ad esempio 192.25.25.102:80); viene utilizzata l'autenticazione del dominio:
3.2. Selezioniamo un database multidimensionale e un cubo analitico (se esistono diritti di accesso al cubo):
3.3 Le impostazioni di connessione con il servizio analitico verranno salvate in un file odc sul computer:
3.4 Selezionare il tipo di rapporto (tabella pivot / grafico) e indicare la posizione per il suo posizionamento:
Se è già stata creata una connessione nella cartella di lavoro di Excel, è possibile utilizzarla nuovamente: il menu principale "Dati" -\u003e "Connessioni esistenti" -\u003e selezionare la connessione in questa cartella di lavoro -\u003e inserire la tabella pivot nella cella specificata.
4. Connesso correttamente al cubo, è possibile avviare l'analisi dei dati interattiva:
Iniziando un'analisi interattiva dei dati, è necessario determinare quale dei campi parteciperà alla formazione di righe, colonne e filtri (pagine) della tabella pivot. Nel caso generale, la tabella pivot è tridimensionale e possiamo supporre che la terza dimensione sia perpendicolare allo schermo e osserviamo sezioni parallele al piano dello schermo e determinate da quale “pagina” è selezionata per la visualizzazione. Il filtro può essere eseguito trascinando e rilasciando gli attributi di misurazione corrispondenti nell'area del filtro del report. Il filtro limita lo spazio del cubo, riducendo quindi il carico sul server OLAP è preferibile installare prima i filtri necessari. Quindi è necessario posizionare gli attributi di dimensione nelle aree di riga, colonna e misura nell'area dati della tabella pivot.
Ogni volta che la tabella pivot cambia, un'istruzione MDX viene inviata automaticamente al server OLAP, dopo di che vengono restituiti i dati. Più grande e complessa è la quantità di dati elaborati, indicatori calcolati, maggiore è il tempo di esecuzione della query. È possibile annullare la richiesta premendo il tasto fuga. Le ultime operazioni eseguite possono essere annullate (Ctrl + Z) o restituite (Ctrl + Y).
Di norma, per le combinazioni di attributi di dimensione utilizzate più frequentemente, il cubo memorizza i dati aggregati precalcolati, quindi il tempo di risposta di tali query è di diversi secondi. Tuttavia, è impossibile calcolare tutte le possibili combinazioni di aggregazioni, poiché ciò potrebbe richiedere molto tempo e spazio di archiviazione. Potrebbero essere necessarie risorse computazionali significative del server per eseguire enormi richieste di dati a livello di dettaglio, quindi il loro tempo di esecuzione potrebbe essere lungo. Dopo aver letto i dati dalle unità disco, il server li inserisce nella cache RAM, il che consente di eseguire immediatamente tali richieste successive, poiché i dati verranno estratti dalla cache.
Se ritieni che la tua richiesta verrà utilizzata frequentemente e i suoi tempi di esecuzione non siano soddisfacenti, puoi contattare il servizio di supporto allo sviluppo analitico per ottimizzare l'esecuzione della richiesta.
Dopo aver posizionato la gerarchia nell'area riga / colonna, è possibile nascondere i singoli livelli:
Attributi chiave (meno spesso - per attributi superiori nella gerarchia) di dimensioni potrebbero esserci proprietà - caratteristiche descrittive che possono essere visualizzate sia nei tooltip che sotto forma di campi:
Se si desidera visualizzare più proprietà dei campi contemporaneamente, è possibile utilizzare l'elenco di finestre di dialogo appropriato:
Set definiti dall'utente
Excel 2010 ha introdotto la possibilità di creare in modo interattivo insiemi di membri della dimensione (definiti dall'utente):
A differenza dei set creati e archiviati centralmente sul lato del cubo, i set personalizzati vengono salvati localmente nella cartella di lavoro di Excel e possono essere utilizzati in futuro:
Gli utenti esperti possono creare set usando costrutti MDX:
Impostazione delle proprietà della tabella pivot
Utilizzando la voce "Parametri della tabella pivot ..." nel menu di scelta rapida (facendo clic con il pulsante destro del mouse nella tabella pivot) è possibile configurare la tabella pivot, ad esempio:
- scheda "Output", l'opzione "Layout classico della tabella pivot" - la tabella pivot diventa interattiva, è possibile trascinare e rilasciare i campi (Drag & Drop);
- scheda "Output", l'opzione "Mostra elementi senza dati nelle righe" - nella tabella pivot verranno visualizzate righe vuote che non contengono alcun valore indicatore per gli elementi dimensione corrispondenti;
- scheda "Layout e formato", opzione "Mantieni formattazione delle celle durante l'aggiornamento" - nella tabella pivot puoi sovrascrivere e salvare il formato delle celle durante l'aggiornamento dei dati;
Crea grafici pivot
Per una tabella pivot OLAP esistente, è possibile creare un grafico pivot: una torta, una barra, una barra, un grafico, uno scatter e altri tipi di diagrammi:
In questo caso, il grafico pivot verrà sincronizzato con la tabella pivot: quando si modifica la composizione di indicatori, filtri, misurazioni nella tabella pivot, viene aggiornata anche la tabella pivot.
Creazione di dashboard
Seleziona la tabella pivot originale, copiala negli appunti (Ctrl + C) e incolla la sua copia (Ctrl + V), in cui cambiamo la composizione degli indicatori:
Per la gestione simultanea di più tabelle pivot, inseriremo una sezione (nuova funzionalità, disponibile a partire da MS Excel 2010). Collegheremo il nostro Slicer alle tabelle pivot: fai clic con il pulsante destro del mouse all'interno della sezione, seleziona "Connessioni alla tabella pivot ..." nel menu di scelta rapida. Va notato che ci possono essere diversi pannelli fetta che possono servire contemporaneamente tabelle pivot su fogli diversi, il che consente di creare pannelli informativi coordinati (Dashboard).
I pannelli affettatrice possono essere personalizzati: è necessario selezionare il pannello, quindi vedere le voci "Dimensioni e proprietà ...", "Impostazioni affettatrice", "Assegna macro" nel menu contestuale attivato facendo clic con il tasto destro del mouse o la voce "Parametri" nel menu principale. Quindi, è possibile impostare il numero di colonne per gli elementi di sezione (pulsanti), le dimensioni dei pulsanti di sezione e il pannello, definire la combinazione di colori e lo stile del taglio dal set esistente (o creare il proprio stile), definire il titolo del proprio pannello, assegnare una macro di programma attraverso la quale è possibile espandere funzionalità del pannello.
Esecuzione di query MDX da Excel
- Prima di tutto, devi eseguire l'operazione DRILLTHROUGH su alcuni indicatori, ad es. Passare ai dati dettagliati (i dati dettagliati vengono visualizzati su un foglio separato) e aprire l'elenco delle connessioni;
- Apri le proprietà della connessione, vai alla scheda "Definizione";
- Seleziona il tipo di comando predefinito e posiziona il preparato MDX richiesta;
- Quando si preme un pulsante dopo aver verificato la sintassi corretta della richiesta e la disponibilità dei diritti di accesso appropriati, la richiesta verrà eseguita sul server e il risultato verrà presentato nel foglio corrente come una normale tabella piatta.
È possibile visualizzare il testo della query MDX generata da Excel installando il componente aggiuntivo gratuito, che fornisce anche altre funzionalità aggiuntive.
Traduzione in altre lingue
Il cubo analitico supporta la localizzazione in russo e inglese (la localizzazione in altre lingue è possibile se necessario). Le traduzioni si applicano ai nomi di dimensioni, gerarchie, attributi, cartelle, misure, nonché elementi di singole gerarchie se sono presenti traduzioni per loro dal lato dei sistemi di contabilità / data warehouse. Per cambiare la lingua, è necessario aprire le proprietà della connessione e aggiungere la seguente opzione nella stringa di connessione:
Proprietà estese \u003d "Locale \u003d 1033"
dove 1033 - localizzazione in inglese
1049 - localizzazione in russo
Estensioni di Excel aggiuntive per Microsoft OLAP
Le possibilità di lavorare con i cubi Microsoft OLAP aumenteranno se si utilizzano estensioni aggiuntive, ad esempio le estensioni di tabella pivot OLAP, grazie alle quali è possibile utilizzare una ricerca rapida mediante misurazione:
