Lavoro pratico sulla tecnologia Olap in Excel. Cubi Olap in Excel. Scrivere una formula da zero

1 compito (generale) (2 punti).

Utilizzando un foglio di calcolo, elaborare i dati utilizzando funzioni statistiche.
1. Vengono fornite informazioni sugli studenti della classe (10 persone), compresi i voti di un mese in matematica. Conta il numero di cinque, quattro, due e tre, trova il punteggio medio di ogni studente e il punteggio medio dell'intero gruppo. Crea un grafico che illustri la percentuale di voti in un gruppo.

Compito 2.1 (2 punti).

Quattro amici viaggiano con tre mezzi di trasporto: treno, aereo e nave. Nikolai ha navigato per 150 km in barca, ha percorso 140 km in treno e ha volato per 1100 km in aereo. Vasily ha navigato per 200 km in barca, ha percorso 220 km in treno e ha volato 1160 km in aereo. Anatoly ha volato 1200 km in aereo, ha percorso 110 km in treno e ha navigato 125 km in barca. Maria ha percorso 130 km in treno, ha volato 1500 km in aereo e ha navigato 160 km in barca.
Costruisci un foglio di calcolo basato sui dati di cui sopra.

Aggiungi una colonna alla tabella che mostrerà il numero totale di chilometri percorsi da ciascuno dei ragazzi.

Calcola il numero totale di chilometri che i bambini hanno percorso in treno, hanno volato in aereo e navigato in barca (su ciascun tipo di trasporto separatamente).

Calcola il numero totale di chilometri di tutti gli amici.

Determina il numero massimo e minimo di chilometri percorsi dagli amici utilizzando tutti i tipi di trasporto.

Determinare il numero medio di chilometri per tutti i tipi di trasporto.

Compito 2.2 (2 punti).

Crea una tabella “Laghi d'Europa” utilizzando i seguenti dati sull'area (kmq) e sulla profondità massima (m): Ladoga 17.700 e 225; Onega 9510 e 110; Mar Caspio 371.000 e 995; Wenern 5550 e 100; Chudskoye con Pskovsky 3560 e 14; Balaton 591 e 11; Ginevra 581 e 310; Wetter 1900 e 119; Costanza 538 e 252; Mälaren 1140 e 64. Determina il lago più grande e più piccolo nell'area, il lago più profondo e quello meno profondo.

compito 2.3 (2 punti).

Crea una tabella “Fiumi d'Europa” utilizzando la seguente lunghezza (km) e area del bacino (migliaia di kmq): Volga 3688 e 1350; Danubio 2850 e 817; Reno 1330 e 224; Elba 1150 e 148; Vistola 1090 e 198; Loira 1020 e 120; Urali 2530 e 220; Don 1870 e 422; Sena 780 e 79; Tamigi 340 e 15. Determina il fiume più lungo e più corto, calcola l'area totale dei bacini fluviali, la lunghezza media dei fiumi nella parte europea della Russia.

Compito 3 (2 punti).

La banca registra la tempestività dei pagamenti dei prestiti concessi a diverse organizzazioni. L'importo del prestito e l'importo già pagato dall'organizzazione sono noti. Sono previste sanzioni per i debitori: se la società ha rimborsato il prestito oltre il 70%, la multa sarà pari al 10% dell'importo del debito, altrimenti la multa sarà pari al 15%. Calcola la multa per ciascuna organizzazione, la multa media, l'importo totale di denaro che la banca riceverà in aggiunta. Determinare la multa media delle organizzazioni di bilancio.

Trova materiale per qualsiasi lezione,

Gli strumenti client OLAP sono applicazioni che calcolano i dati aggregati (somme, medie, valori massimi o minimi) e li visualizzano, mentre i dati aggregati stessi sono contenuti in una cache all'interno dello spazio degli indirizzi di tale strumento OLAP.

Se i dati di origine sono contenuti in un DBMS desktop, il calcolo dei dati aggregati viene eseguito dallo strumento OLAP stesso. Se l'origine dei dati iniziali è un DBMS server, molti degli strumenti OLAP client inviano query SQL contenenti l'istruzione GROUP BY al server e di conseguenza ricevono dati aggregati calcolati sul server.

Di norma, la funzionalità OLAP è implementata negli strumenti di elaborazione dei dati statistici (tra i prodotti di questa classe, i prodotti StatSoft e SPSS sono ampiamente utilizzati sul mercato russo) e in alcuni fogli di calcolo. In particolare, Microsoft Excel dispone di strumenti di analisi multidimensionale. Con questo prodotto è possibile creare e salvare come file un piccolo cubo OLAP multidimensionale locale e visualizzarne sezioni trasversali bi o tridimensionali.

Componenti aggiuntivi del pacchetto applicativo Microsoft Office il data mining è un insieme di funzioni che forniscono l'accesso alle funzionalità di data mining ed elaborazione dalle applicazioni Microsoft Office, consentendo così l'analisi predittiva computer locale. Grazie al fatto che i servizi sono integrati Piattaforme Microsoft server SQL Grazie agli algoritmi di data mining ed elaborazione disponibili nell'ambiente applicativo Microsoft Office, gli utenti aziendali possono facilmente estrarre informazioni preziose da set di dati complessi con pochi clic. I componenti aggiuntivi per l'estrazione e la manipolazione dei dati di Office consentono agli utenti finali di eseguire analisi direttamente in Microsoft Excel e Microsoft Visio.

IN Composizione Microsoft Office 2007 include tre componenti OLAP separati:

1. Data Mining Client per Excel consente di creare e gestire progetti di data mining basati su SSAS da Excel 2007;
2. strumenti di analisi delle tabelle per Applicazioni Excel Consente di utilizzare le funzionalità integrate di estrazione ed elaborazione delle informazioni di SSAS per analizzare i dati archiviati nei fogli di calcolo Excel.
3. I modelli di data mining di Visio consentono di visualizzare alberi decisionali, alberi di regressione, diagrammi di cluster e reti di dipendenza nei diagrammi di Visio.

La prima interfaccia per le tabelle pivot, chiamata anche report pivot, è stata inclusa in Excel nel 1993 (versione Excel 5.0). Nonostante i tanti utili funzionalità, praticamente non viene utilizzato nel lavoro dalla maggior parte degli utenti di Excel. Anche gli utenti più esperti spesso intendono con il termine “rapporto riassuntivo” qualcosa costruito utilizzando formule complesse. Proviamo a rendere popolare l'uso delle tabelle pivot nel lavoro quotidiano degli economisti. L'articolo discute base teorica vengono forniti i report di sintesi raccomandazioni pratiche sul loro utilizzo e fornisce anche un esempio di accesso ai dati basati su più tabelle.

Termini di analisi dei dati multivariati

La maggior parte degli economisti ha sentito i termini “dati multidimensionali”, “cubo virtuale”, “tecnologie OLAP”, ecc. Ma da una conversazione approfondita di solito risulta che quasi tutti non hanno idea di cosa stanno parlando. stiamo parlando. Cioè, le persone intendono qualcosa di complesso e solitamente non rilevante per le loro attività quotidiane. In realtà, questo non è vero.

Dati multidimensionali, misurazioni

Si può dire con certezza che gli economisti si imbattono quasi costantemente in dati multidimensionali, ma cercano di presentarli in una forma predefinita utilizzando fogli di calcolo. Multidimensionalità qui significa la capacità di inserire, visualizzare o analizzare le stesse informazioni con modifiche aspetto, utilizzando vari raggruppamenti e ordinamenti di dati. Ad esempio, un piano di vendita può essere analizzato utilizzando i seguenti criteri:

• tipologie o gruppi di beni;
• marchi o categorie di prodotti;
• periodi (mese, trimestre, anno);
• acquirenti o gruppi di acquirenti;
• regioni di vendita
• e così via.

Ciascuno dei criteri di cui sopra è chiamato “dimensione” in termini di analisi dei dati multidimensionali. Possiamo dire che una misurazione caratterizza l'informazione secondo uno specifico insieme di valori. Un tipo speciale di misurazione delle informazioni multidimensionali sono i “dati”. Nel nostro esempio, i dati del piano di vendita potrebbero essere:

• volume delle vendite;
• Prezzo di vendita;
• sconto individuale
• e così via.

In teoria, i dati possono anche essere una dimensione standard di informazioni multidimensionali (ad esempio, potresti raggruppare i dati in base al prezzo di vendita), ma in genere i dati rappresentano ancora un tipo speciale di valore.

Si può quindi dire che in lavoro pratico Gli economisti utilizzano due tipi di informazioni: dati multidimensionali ( numeri effettivi e pianificati che hanno molte caratteristiche) e libri di consultazione (caratteristiche o misurazioni dei dati).

OLAP

L’abbreviazione OLAP (elaborazione analitica online) significa letteralmente “elaborazione analitica in tempo reale”. La definizione non è molto specifica; sotto di essa si può riassumere quasi ogni rapporto su qualsiasi prodotto software. Nel suo significato, OLAP implica una tecnologia per lavorare con rapporti speciali, incluso Software, per ottenere e analizzare dati strutturati multidimensionali. Uno dei prodotti software più diffusi che implementano le tecnologie OLAP è SQL Server Analysis Server. Alcuni addirittura lo considerano erroneamente l'unico rappresentante dell'implementazione software di questo concetto.

Cubo di dati virtuale

"Cubo virtuale" ( cubo multidimensionale, cubo OLAP) è un termine speciale proposto da alcuni fornitori di software specializzati. I sistemi OLAP in genere preparano e archiviano i dati nelle proprie strutture e speciali interfacce di analisi (come i report di riepilogo Excel) accedono ai dati in questi cubi virtuali. Inoltre, l'utilizzo di tale memoria dedicata non è affatto necessario per l'elaborazione di informazioni multidimensionali. Generalmente, cubo virtuale– si tratta di una serie di dati multidimensionali appositamente ottimizzati che vengono utilizzati per creare report di riepilogo. Può essere ottenuto tramite software specializzati o tramite il semplice accesso alle tabelle del database o a qualsiasi altra fonte, come un foglio di calcolo Excel.

Tabella pivot

La tabella pivot è un'interfaccia utente per la visualizzazione di dati multidimensionali. Utilizzando questa interfaccia è possibile raggruppare, ordinare, filtrare e riorganizzare i dati per ottenere diversi campioni analitici. L'aggiornamento del rapporto viene effettuato utilizzando mezzi semplici interfaccia utente, i dati vengono aggregati automaticamente secondo regole specificate, senza richiedere l'inserimento aggiuntivo o ripetuto di alcuna informazione. L'interfaccia della tabella pivot di Excel è forse la più popolare prodotto software per lavorare con dati multidimensionali. Supporta sia origini dati esterne (cubi OLAP e database relazionali) che intervalli di fogli di calcolo interni come origine dati. A partire dalla versione 2000 (9.0), Excel supporta anche una forma grafica di visualizzazione di dati multidimensionali: un grafico pivot.

L'interfaccia della tabella pivot di Excel consente di organizzare le dimensioni dei dati multidimensionali in un'area del foglio di lavoro. Per semplicità, puoi pensare a una tabella pivot come a un report che si trova sopra un intervallo di celle (in effetti, esiste un certo legame tra i formati delle celle e i campi della tabella pivot). Una tabella pivot di Excel dispone di quattro aree in cui visualizzare le informazioni: filtro, colonne, righe e dati. Le dimensioni dei dati vengono chiamate Campi della tabella pivot. Questi campi hanno le proprie proprietà e il proprio formato di visualizzazione.

Ancora una volta vorrei attirare la vostra attenzione sul fatto che la tabella pivot di Excel è destinata esclusivamente all'analisi dei dati senza la possibilità di modificare le informazioni. Un significato più preciso sarebbe l’uso diffuso del termine “report pivot”, ed è esattamente così che questa interfaccia veniva chiamata fino al 2000. Ma per qualche motivo, nelle versioni successive, gli sviluppatori lo abbandonarono.

Modifica delle tabelle pivot

Per sua 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 sistemi software, implementando le capacità sia di analisi che di modifica diretta dei dati in tabelle multidimensionali. Fondamentalmente, tali sistemi sono focalizzati sulla risoluzione dei problemi di budget.

Utilizzando gli strumenti di automazione integrati di Excel, puoi risolvere molti problemi non standard. Un esempio di implementazione della modifica per le tabelle pivot di Excel basate sui dati del foglio di lavoro può essere trovato sul nostro sito web.

Preparazione di dati multidimensionali

Veniamo a applicazione pratica Tabelle pivot. Proviamo ad analizzare i dati di vendita in varie direzioni. File pivottableexample.xlsè composto da più fogli. Foglio Esempio contiene informazioni di base sulle vendite per un determinato periodo. Per semplificare l'esempio, analizzeremo un unico indicatore numerico: volume delle vendite in kg. Le dimensioni chiave dei dati sono: prodotto, acquirente e corriere (compagnia di spedizioni). Inoltre ce ne sono diversi dimensioni aggiuntive dati che sono caratteristiche del prodotto: tipologia, marca, categoria, fornitore, nonché l'acquirente: tipologia. Questi dati vengono raccolti nella scheda Directory. In pratica, possono esserci molte più misurazioni di questo tipo.

Foglio Esempio contiene rimedio standard analisi dei dati – filtro automatico. Guardando l'esempio di compilazione della tabella, è ovvio che i dati di vendita per data (sono organizzati in colonne) si prestano ad un'analisi normale. Inoltre, utilizzando un filtro automatico, puoi provare a riepilogare i dati in base a combinazioni di uno o più criteri chiave. Non ci sono assolutamente informazioni su marchi, categorie e tipologie. Non è possibile raggruppare i dati con sommatoria automatica tramite una chiave specifica (ad esempio, per clienti). Inoltre, l'insieme delle date è fisso e non sarà possibile visualizzare le informazioni di riepilogo per un determinato periodo, ad esempio 3 giorni, utilizzando mezzi automatici.

In generale, la presenza di una data predefinita in questo esempio è il principale svantaggio della tabella. Disponendo le date in colonne, abbiamo predeterminato la dimensione di questa tabella, privandoci così della possibilità di utilizzare l'analisi tramite tabelle pivot.

Innanzitutto dobbiamo eliminare questa lacuna, ovvero rimuovere la posizione predefinita di una delle dimensioni dei dati di origine. Esempio di tabella - foglio corretta Saldi.

La tabella ha la forma di un registro delle immissioni di informazioni. Qui, la data ha la stessa dimensione dei dati. Va inoltre notato che per la successiva analisi nelle tabelle pivot, la posizione relativa delle righe l'una rispetto all'altra (in altre parole, l'ordinamento) è del tutto indifferente. I record nei database relazionali hanno queste proprietà. L'interfaccia delle tabelle pivot è principalmente finalizzata all'analisi di grandi volumi di database. 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 proprio lavoro: le tabelle pivot analizzano solo i dati e la formattazione, i filtri, i raggruppamenti e l'ordinamento delle celle di origine possono essere arbitrari.

Dal filtro automatico al report riepilogativo

Teoricamente è già possibile effettuare analisi tridimensionali utilizzando i dati della Scheda Vendite: merce, clienti e trasportatori. In questa scheda non sono presenti dati sulle proprietà dei prodotti e dei clienti, che, di conseguenza, non ne consentiranno la visualizzazione nella tabella riepilogativa. Nella modalità normale di creazione di una tabella pivot per l'origine Dati Excel non consente di collegare dati da più tabelle utilizzando determinati campi. Puoi aggirare questa limitazione Software– vedere il supplemento di esempio a questo articolo sul nostro sito web. Per non ricorrere a metodi software per l'elaborazione delle informazioni (soprattutto perché non sono universali), è necessario aggiungere ulteriori caratteristiche direttamente al modulo di registrazione prima nota - vedere il foglio Analisi delle vendite.

L'uso delle funzioni CERCA.VERT semplifica l'integrazione dei dati originali con caratteristiche mancanti. Ora, utilizzando il filtro automatico, puoi analizzare i dati in diverse dimensioni. Ma il problema dei raggruppamenti resta irrisolto. Ad esempio, monitorare l’importo solo per marca per determinate date è piuttosto problematico. Se ti limiti Formule di Excel, è necessario creare ulteriori campioni utilizzando la funzione SOMMA.SE.

Ora vediamo quali funzionalità offre l'interfaccia della tabella pivot. Su un foglio Analisi di riepilogo creato diversi report basati su un intervallo di celle con dati del foglio Analisi delle vendite.

La prima tabella di analisi è stata costruita tramite l'interfaccia Excel 2007 Barra multifunzione\Inserisci\Tabella pivot(nel menu di Excel 2000-2003 Dati\Tabella pivot).

La seconda e la terza tabella sono state create tramite copia e successiva configurazione. L'origine dati per tutte le tabelle è la stessa. Puoi verificarlo modificando i dati di origine, quindi devi aggiornare i dati del rapporto di riepilogo.

Dal nostro punto di vista i vantaggi nella visibilità delle informazioni sono evidenti. Puoi scambiare filtri, colonne e righe e nascondere determinati gruppi di valori di qualsiasi dimensione, utilizzare il trascinamento manuale e l'ordinamento automatico.

Proprietà e formattazione

Oltre alla visualizzazione diretta dei dati, esiste un'ampia gamma di opzioni per visualizzare l'aspetto delle tabelle pivot. Puoi nascondere i dati non necessari utilizzando i filtri. Per un singolo elemento o campo è più semplice utilizzare la voce del menu contestuale Eliminare(nella versione 2000-2003 Nascondere).

Si consiglia inoltre di impostare la visualizzazione degli altri elementi della tabella pivot non tramite la formattazione della cella, ma attraverso l'impostazione di un campo o elemento della tabella pivot. Per fare ciò, è necessario spostare il puntatore del mouse sull'elemento desiderato, attendere che appaia una forma speciale del cursore (a forma di freccia), quindi selezionare l'elemento selezionato con un solo clic. Dopo la selezione, puoi modificare la visualizzazione tramite la barra multifunzione, il menu contestuale o richiamare la finestra di dialogo del formato cella standard:

Inoltre, Excel 2007 ha introdotto molti stili di visualizzazione delle tabelle pivot predefiniti:

Tieni presente che nel grafico sono attivi i filtri di controllo e le aree di trascinamento.

Accesso ai dati esterni

Come già notato, forse l'effetto maggiore dall'utilizzo delle tabelle pivot può essere ottenuto quando si accede ai dati fonti esterne– Cubi OLAP e query di database. Tali origini in genere memorizzano grandi quantità di informazioni e dispongono anche di una struttura relazionale predefinita che semplifica la definizione delle dimensioni dei dati multidimensionali (campi della tabella pivot).

Excel supporta molti tipi di origini dati esterne:

L'effetto maggiore dall'utilizzo di fonti di informazione esterne può essere ottenuto utilizzando strumenti di automazione (programmi VBA) sia per ottenere dati che per preelaborarli in tabelle pivot.

L'OLAP (Online Analytical Processing) è una tecnologia utilizzata per organizzare database aziendali di grandi dimensioni e supportare la business intelligence. I database OLAP sono divisi in uno o più cubi e ogni cubo viene organizzato dall'amministratore del cubo per adattarsi al modo in cui i dati vengono recuperati e analizzati per semplificare la creazione e l'utilizzo dei rapporti di tabella pivot e di grafico pivot necessari.

In questo articolo

Che cos'è l'analisi aziendale?

Un analista aziendale spesso desidera ottenere un quadro più ampio dell'azienda per visualizzare tendenze più ampie basate su dati aggregati, nonché visualizzare le tendenze suddivise in un numero qualsiasi di variabili. La business intelligence è il processo di estrazione dei dati da un database OLAP e di analisi dei dati per produrre approfondimenti che possono essere utilizzati per prendere decisioni aziendali informate e intraprendere azioni. Ad esempio, utilizzando OLAP e l'analisi aziendale, puoi 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 per periodo di beneficio degli ultimi cinque anni?

Quanti soldi hanno speso i clienti su 35 l'anno scorso e come è cambiato questo comportamento nel tempo?

Quanti prodotti sono stati venduti in due paesi/regioni specifici questo mese rispetto allo stesso mese dell'anno scorso?

Per ciascuna fascia di età del cliente, qual è la ripartizione della redditività (sia percentuale di margine che totale) per categoria di prodotto?

Cerca i venditori, i distributori, i fornitori, i clienti, i partner e i clienti migliori e peggiori.

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 anziché per l'elaborazione delle transazioni. L'origine dati per OLAP sono i database OLTP (Online Transaction Processing), in genere archiviati in data warehouse. I dati OLAP vengono estratti da questi dati storici e combinati in strutture che consentono analisi complesse. Anche i dati OLAP sono organizzati gerarchicamente e archiviati in cubi anziché in tabelle. È una tecnologia complessa che utilizza strutture multidimensionali per fornire accesso veloce ai dati per l'analisi. In questa organizzazione, un rapporto di tabella pivot o un rapporto di grafico pivot può facilmente visualizzare dati di riepilogo di alto livello, come i totali delle vendite per un intero paese o regione, e visualizzare anche informazioni sui siti in cui le vendite sono particolarmente forti o deboli.

I database OLAP sono progettati per accelerare il caricamento dei dati. Perché è un server OLAP, non Microsoft Ufficio Excel, calcola i valori aggregati, richiedendo l'invio di dati più piccoli a Excel durante la creazione o la modifica di un report. Questo approccio consente di lavorare con più dati grezzi rispetto a quando i dati fossero organizzati in un database tradizionale, in cui Excel recupera tutti i singoli record e calcola i valori aggregati.

I database OLAP contengono due tipi principali di dati: misure, ovvero dati numerici, quantità e medie utilizzate per prendere decisioni aziendali informate, e dimensioni, ovvero categorie utilizzate per organizzare tali misure. I database OLAP ti aiutano a organizzare i dati attraverso più livelli di dettaglio, utilizzando le stesse categorie che conosci per analizzare i dati.

Le sezioni seguenti descrivono dettagliatamente ciascun componente.

Cubo Una struttura di dati che raggruppa le misure in livelli e gerarchie di ogni dimensione che desideri analizzare. I cubi combinano più dimensioni come tempo, area geografica e linee di prodotti con dati di riepilogo come vendite e inventario. I cubi non sono "cubi" in senso matematico stretto, poiché non hanno necessariamente gli stessi lati. Tuttavia, rappresentano una metafora appropriata per un concetto complesso.

Misure Un insieme di valori in un cubo basati su una colonna nella tabella dei fatti del cubo e che in genere è un valore numerico. Le misure sono i valori centrali di un cubo che vengono preelaborati, elaborati e analizzati. Gli esempi più comuni sono vendite, entrate, entrate e 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 membri univoci a livello di anno di una dimensione temporale, mentre gennaio rappresenta membri non univoci a livello di mese perché è presente più di un gennaio in una dimensione temporale poiché contiene dati per più di un anno.

Elemento calcolato Un membro della dimensione il cui valore viene calcolato in fase di esecuzione utilizzando un'espressione. I valori dei membri calcolati possono essere derivati ​​dai valori di altri membri. Ad esempio, un elemento calcolato, il profitto, può essere determinato sottraendo il valore dell'elemento, più i costi, dal valore dell'elemento, le vendite.

misurazione Un insieme di una o più gerarchie ordinate di livelli del Cubo che l'utente comprende e utilizza come base per l'analisi dei dati. Ad esempio, una dimensione geografica potrebbe includere i livelli di paese/regione, stato/regione e città. Inoltre, una dimensione temporale può includere una gerarchia con livelli di anno, trimestre, mese e giorno. In un rapporto di tabella pivot o di 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 logica che dispone i membri di una dimensione in modo che ciascun membro abbia un membro principale e zero o più elementi secondari. Un bambino è un membro di un gruppo precedente nella gerarchia direttamente correlato al membro corrente. Ad esempio, in una gerarchia temporale contenente i livelli trimestre, mese e giorno, Gennaio è un elemento figlio di Qtr1. Un elemento principale è un membro di livello inferiore in una gerarchia direttamente correlato al membro corrente. Il valore genitore è solitamente il consolidamento dei valori di tutti gli elementi figli. Ad esempio, in una gerarchia temporale contenente i livelli trimestre, mese e giorno, Qtr1 è l'elemento principale di gennaio.

Livello In una gerarchia, i dati possono essere organizzati in livelli di granularità inferiori e superiori, ad esempio anni, trimestri, mesi e livelli giornalieri in una gerarchia temporale.

Funzioni OLAP in Excel

Recupero dei dati OLAPÈ possibile connettersi alle origini dati OLAP nello stesso modo in cui ci si connette ad altre origini dati esterne. È possibile utilizzare database creati utilizzando Microsoft SQL Server OLAP Services versione 7.0, Microsoft SQL Server Analysis Services versione 2000 e Microsoft SQL Server Analysis Services versione 2005, prodotti server Microsoft OLAP. Excel può funzionare anche con prodotti OLAP di terze parti compatibili con OLE-DB per OLAP.

I dati OLAP possono essere visualizzati solo come rapporto di tabella pivot o rapporto di grafico pivot oppure in una funzione del foglio di lavoro convertita da un rapporto di tabella pivot, ma non come intervallo di dati esterno. È possibile salvare rapporti di tabella pivot e grafico pivot OLAP nei modelli di rapporto ed è possibile creare file ODC (Office Data Connection) per connettersi ai database OLAP per query OLAP. Quando apri un file ODC in Excel, viene visualizzato un rapporto di tabella pivot vuoto pronto per essere inserito.

Creazione di file cubo per l'utilizzo offlineÈ possibile creare un file cubo autonomo (.cub) con un sottoinsieme di dati dal database del server OLAP. I file del cubo offline vengono utilizzati per utilizzare i dati OLAP quando non si è connessi a una rete. Con un cubo è possibile lavorare con più dati in un rapporto di tabella pivot o di grafico pivot rispetto a quanto si farebbe altrimenti e ottenere i dati più velocemente. È possibile creare file cubo solo se si utilizza un provider OLAP, come Microsoft SQL Analysis Services versione 2005, che supporta questa funzionalità.

Azioni del server Un'azione server è una funzione facoltativa che un amministratore di cubo OLAP può definire su un server che utilizza un elemento del cubo o una misura come parametro in una query per recuperare informazioni nel cubo o per avviare un'altra applicazione, ad esempio un browser. Excel supporta URL, report, set di righe, drill-down e drill-down lato server, ma non supporta la propria istruzione nativa e il proprio set di dati.

KPI Un indicatore chiave di prestazione è una misura calcolata speciale definita sul server che consente di tenere traccia degli "indicatori chiave di prestazione", incluso lo stato (il valore corrente corrisponde a un numero specifico). e trend (valori nel tempo). Quando vengono visualizzati, il server può inviare icone corrispondenti, simili alla nuova icona di Excel, per allinearsi sopra o sotto i livelli di stato (ad esempio, per un'icona di arresto), nonché ruotare un valore verso l'alto o verso il basso (ad esempio, un'icona con una freccia direzionale).

Formattazione sul server Gli amministratori del cubo possono creare misure e membri calcolati utilizzando la formattazione del colore, la formattazione dei caratteri e le regole di formattazione condizionale che possono essere assegnate come regola aziendale standard aziendale. Ad esempio, il formato del server per il reddito potrebbe essere un formato numerico di valuta, 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 o uguale a 30.000 e se il valore è positivo - ordinario. maggiore o uguale a 30.000. Per ulteriori informazioni, vedere .

Lingua dell'interfaccia di Office L'amministratore del cubo può definire le traduzioni dei dati e degli errori sul server per gli utenti che devono visualizzare le informazioni della tabella pivot in una lingua diversa. Questa funzione è definita come una proprietà di connessione file e le impostazioni locali e il paese del computer dell'utente devono corrispondere alla lingua dell'interfaccia.

Componenti software necessari per accedere alle origini dati OLAP

Fornitore OLAP Per configurare origini dati OLAP per Excel, è necessario uno dei seguenti provider OLAP.

Fornitore OLAP Microsoft Excel include un driver dell'origine dati e un software client per l'accesso ai database creati con i servizi Microsoft SQL Server olap versione 7.0, Microsoft SQL Server olap versione 2000 (8.0) e Microsoft SQL Server Analysis Services versione 2005 (9 ,0).

Provider OLAP di terze parti Altri prodotti OLAP richiedono l'installazione di driver e software client aggiuntivi. Per utilizzare le funzionalità di Excel per lavorare con i dati OLAP, il prodotto di terze parti deve essere conforme allo standard OLE-DB per OLAP ed essere compatibile con Microsoft Office. Per informazioni sull'installazione e sull'utilizzo di un provider OLAP di terze parti, vedere amministratore di sistema o dal fornitore del prodotto OLAP.

Database server e file cubi Il software client Excel OLAP supporta le connessioni a due tipi di database OLAP. Se il database sul server OLAP è online, è possibile recuperare i dati di origine direttamente da esso. Se si dispone di un file di cubo autonomo che contiene dati OLAP o un file di definizione di cubo, è possibile connettersi a tale file e ottenere da esso i dati di origine.

Origine dei dati Un'origine dati fornisce l'accesso a tutti i dati in un database OLAP o in un file cubo offline. Dopo aver creato un'origine dati OLAP, è possibile basare i report su di essa e restituire i dati OLAP in Excel come rapporto di tabella pivot o grafico pivot oppure in una funzione del foglio di lavoro convertita da un rapporto di tabella pivot.

MicrosoftQuery Utilizzando Query, è possibile recuperare dati da un database esterno, come Microsoft SQL o accesso Microsoft. Non è necessario utilizzare una query per recuperare dati da una tabella pivot OLAP associata a un file cubo. Informazioni aggiuntive .

Differenze nelle funzionalità dei dati di origine OLAP e non OLAP

Se utilizzi rapporti di tabella pivot e grafici pivot da dati di origine OLAP e altri tipi di dati di origine, noterai alcune differenze di funzionalità.

Estrazione dati Il server OLAP restituisce nuovi dati a Excel ogni volta che cambia il layout del report. Con altri tipi di origini dati esterne, si eseguono query su tutti i dati di origine contemporaneamente oppure è possibile specificare i parametri da interrogare solo quando si visualizzano diversi elementi del campo filtro report. Inoltre, hai diverse altre opzioni per aggiornare il tuo rapporto.

Nei report basati su dati di origine OLAP, le opzioni del campo filtro report non sono disponibili, la query in background non è disponibile e l'opzione di ottimizzazione della memoria non è disponibile.

Nota: L'opzione di ottimizzazione della memoria non è inoltre disponibile per le origini dati OLE DB e i rapporti di tabella pivot basati su un intervallo di celle.

Tipi di campo Dati di origine OLAP. i campi dimensione possono essere utilizzati solo come campi righe (righe), colonne (categoria) o pagina. I campi 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.

Accesso ai dati dettagliati Per i dati di origine OLAP, il server determina i livelli di dettaglio disponibili e calcola i valori di riepilogo, pertanto i record di dettaglio 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 dispongono di campi proprietà, ma è possibile visualizzare informazioni di base per i campi dati e i valori degli elementi, nonché visualizzare elementi senza dati.

I campi filtro del report OLAP potrebbero non avere Tutto elementi e la squadra Mostra le pagine dei filtri del rapporto non disponibile.

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.

Nimi I server OLAP forniscono valori di riepilogo direttamente al report, pertanto non è possibile modificare le funzioni di riepilogo per i campi valore. Per altri tipi di dati di origine, è possibile modificare la funzione di aggregazione per un campo valore e utilizzare più funzioni di riepilogo per lo stesso campo valore. Non è possibile creare campi calcolati e membri calcolati nei report con dati di origine OLAP.

Subtotali Nei report con dati di origine OLAP non è possibile modificare la funzione di riepilogo per i totali parziali. Con altri tipi di dati di origine, è possibile modificare le funzioni di totale per i totali parziali e mostrare o nascondere i totali parziali per tutti i campi di riga e colonna.

Per i dati di origine OLAP, è possibile includere o escludere membri nascosti durante il calcolo dei totali parziali e generali. Per altri tipi di dati di origine, è possibile includere elementi nascosti dei campi filtro del report nei totali parziali, ma gli elementi nascosti in altri campi verranno esclusi per impostazione predefinita.