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 icone corrispondenti, simili alla nuova icona di Excel, in modo che si allineino al di sopra o al di sotto dei livelli di stato (ad esempio, per l'icona di arresto), nonché per 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à di connessione a un 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;
- la scheda "Layout e formato", l'opzione "Salva formattazione delle celle durante l'aggiornamento" - nella tabella pivot è possibile 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:
Nella tabella pivot standard, i dati di origine sono memorizzati sul disco rigido locale. Pertanto, è sempre possibile gestirli e riorganizzarli, anche senza accesso alla rete. Ma ciò non si applica in alcun modo alle tabelle pivot OLAP. Nelle tabelle pivot OLAP, la cache non viene mai memorizzata sul disco rigido locale. Pertanto, immediatamente dopo la disconnessione dalla rete locale, la tabella pivot perderà l'operabilità. Non puoi spostare alcun campo al suo interno.
Se è ancora necessario analizzare i dati OLAP dopo la disconnessione dalla rete, creare un cubo di dati autonomo. Un cubo di dati offline è un file separato, che è una cache della tabella pivot e memorizza i dati OLAP visualizzati dopo la disconnessione dalla rete locale. I dati OLAP copiati nella tabella pivot possono essere stampati, il sito Web http://everest.ua è descritto in dettaglio al riguardo.
Per creare un cubo di dati autonomo, creare innanzitutto una tabella pivot OLAP. Posizionare il cursore all'interno della tabella pivot e fare clic sul pulsante Strumenti OLAP della scheda contestuale Strumenti, che fa parte del gruppo di schede contestuali Strumenti tabella pivot. Selezionare il comando OLAP offline (OLAP offline) (Fig. 9.8).
Fig. 9.8. Creare un cubo di dati autonomo
Viene visualizzata la finestra di dialogo delle impostazioni per il cubo di dati OLAP autonomo. Fare clic sul pulsante Crea file di dati offline. È stata avviata la Creazione guidata del file del cubo di dati. Fare clic sul pulsante Avanti per continuare la procedura.
Innanzitutto è necessario specificare le dimensioni e i livelli che verranno inclusi nel cubo di dati. Nella finestra di dialogo, è necessario selezionare i dati che verranno importati dal database OLAP. L'idea è di specificare solo quelle dimensioni che saranno necessarie dopo aver disconnesso il computer dalla rete locale. Più dimensioni specifichi, più grande sarà il cubo di dati autonomo.
Fare clic sul pulsante Avanti per passare alla finestra di dialogo della procedura guidata successiva. In esso, hai la possibilità di specificare membri o elementi di dati che non saranno inclusi nel cubo. In particolare, non sarà necessaria la misura di importo esteso delle vendite Internet, quindi la casella di controllo corrispondente non sarà selezionata nell'elenco. Una casella deselezionata indica che l'elemento specificato non verrà importato e occuperà spazio aggiuntivo sul disco rigido locale.
Nell'ultimo passaggio, specificare la posizione e il nome del cubo di dati. Nel nostro caso, il file del cubo si chiamerà MyOfflineCube.cub e si troverà nella cartella Lavoro.
I file del cubo di dati hanno l'estensione cub
Dopo qualche tempo, Excel salverà il cubo di dati offline nella cartella specificata. Per provarlo, fai doppio clic sul file, che genererà automaticamente una cartella di lavoro Excel che contiene una tabella pivot associata al cubo di dati selezionato. Dopo la creazione, è possibile distribuire un cubo di dati autonomo tra tutti gli utenti interessati che lavorano in modalità LAN disconnessa.
Dopo la connessione alla rete locale, è possibile aprire il file del cubo di dati offline e aggiornarlo, nonché la tabella di dati corrispondente. Il principio principale afferma che un cubo di dati autonomo viene utilizzato solo per il funzionamento quando la rete locale è disconnessa, ma è obbligatorio aggiornarlo dopo il ripristino della connessione. Il tentativo di aggiornare un cubo di dati autonomo dopo una disconnessione comporta un errore.
Lavora con file cubi offline
un file cubo offline (. Cub) memorizza i dati sotto forma di cubo OLAP (Online Analytical Processing). Questi dati possono rappresentare parte di un database OLAP su un server OLAP o possono essere creati indipendentemente da un database OLAP. Utilizzare il file cubo offline per continuare a lavorare con i report delle tabelle pivot e dei grafici pivot se il server non è disponibile o quando si è offline.
Nota sulla sicurezza: Prestare attenzione quando si utilizza o si distribuisce un file cubo offline che contiene dati sensibili o personali. Invece di un file cubo, si consiglia di salvare i dati in un libro in modo da poter controllare l'accesso ai dati utilizzando la funzione di gestione dei diritti. Per ulteriori informazioni, vedere Gestione dei diritti delle informazioni di Office.
Quando si lavora con un report Tabella pivot o Grafico pivot in base ai dati di origine di un server OLAP, è possibile utilizzare la Procedura guidata cubo autonoma per copiare i dati di origine in un file cubo autonomo separato sul computer. Per creare questi file offline, il fornitore di dati OLAP deve supportare questa funzionalità, ad esempio MSOLAP da Microsoft SQL Server Analysis Services installato sul computer.
Nota: La creazione e l'uso di file cubi autonomi da Microsoft SQL Server Analysis Services sono regolati dal termine e dalla licenza dell'installazione di Microsoft SQL Server. Rivedere le informazioni sulla licenza pertinenti per la versione di SQL Server.
Lavorare con la Creazione guidata cubo autonomo
Per creare un file cubo autonomo, è possibile selezionare un sottoinsieme dei dati nel database OLAP utilizzando la procedura guidata del cubo autonomo, quindi salvare il sottoinsieme. Non è necessario che il report includa tutti i campi inclusi nel file, nonché selezionare da essi uno qualsiasi di essi e campi dati disponibili nel database OLAP. Per salvare il file come minimo, è possibile includere solo i dati che devono essere visualizzati nel report. È possibile omettere tutte le dimensioni e, per la maggior parte dei tipi di dimensioni, è anche possibile escludere informazioni di livello inferiore ed elementi di livello superiore che non devono essere visualizzati. Per tutti gli elementi inclusi, anche i campi delle proprietà disponibili nel database per questi elementi sono memorizzati in un file autonomo.
Trasferimento dati offline e connessione inversa
A tale scopo, è innanzitutto necessario creare una tabella pivot o un report del grafico pivot basato sul database del server, quindi creare un file cubo autonomo dal report. Successivamente, è possibile alternare il rapporto tra il database del server e il file offline in qualsiasi momento. Ad esempio, se si utilizza un computer portatile per viaggi domestici e video, ricollegare il computer alla rete.
Di seguito sono riportati i passaggi di base che è necessario adottare per lavorare offline con i dati, quindi trasferire nuovamente i dati su Internet.
Creare o aprire una tabella pivot o un rapporto grafico pivot in base ai dati OLAP a cui si desidera accedere offline.
Crea un file cubo offline sul computer. Nella sezione Creare un file cubo autonomo da un database del server OLAP (più avanti in questo articolo).
Disconnettersi dalla rete e lavorare con il file cubo offline.
Connettiti alla rete e riconnetti il \u200b\u200bfile cubo offline. Dai un'occhiata alla sezione Ricollegamento del file cubo offline al database del server OLAP (più avanti in questo articolo).
Aggiornamento del file cubo offline con nuovi dati e ricostruzione del file cubo offline. Dai un'occhiata alla sezione aggiorna e ricrea il file cubo offline (più avanti in questo articolo).
BLOG
Solo post di qualità
Cosa sono le tabelle pivot di Excel e i cubi OLAP
Guarda il video per l'articolo:
OLAP - questo è inglese. elaborazione analitica online, tecnologia di elaborazione dei dati analitici in tempo reale. In un linguaggio semplice - un repository con dati multidimensionali (cubo), ancora più semplice - solo un database da cui è possibile ottenere dati in Excel e analizzare utilizzando lo strumento Excel - Tabelle pivot.
Tabelle riassuntive - Questa è un'interfaccia utente per la visualizzazione di dati multidimensionali. In altre parole, un tipo speciale di tabelle con le quali è possibile effettuare quasi tutti i report.
Per chiarire, confrontiamo la "tabella normale" con la "tabella pivot"
Tavolo normale:
Tabella riassuntiva:
Differenza principale Tabelle pivot - questa è la presenza della finestra " Elenco campi tabella pivot", Da cui è possibile selezionare i campi necessari e ottenere automaticamente qualsiasi tabella!
Come usare
Aprire il file Excel collegato al cubo OLAP, ad esempio "BIWEB":
Ora, cosa significa questo e come usarlo?
Trascina i campi richiesti per ottenere, ad esempio, una tabella come questa:
« segno più»Consentire di eseguire il drill down in un report. In questo esempio, "Marchio" è dettagliato in "Nomi abbreviati" e "Quarto" in "Mese", ad es. in questo modo:
Funzioni analitiche in Excel (Funzioni cubo)
Microsoft aggiunge costantemente nuove funzionalità a Excel per quanto riguarda l'analisi e la visualizzazione dei dati. Lavorare con le informazioni in Excel può essere rappresentato sotto forma di tre livelli relativamente indipendenti:
- Dati di origine organizzati "correttamente"
- matematica (logica) del trattamento dei dati
- presentazione dei dati
Fig. 1. Analisi dei dati in Excel: a) dati di origine, b) misura in Power Pivot, c) dashboard; per ingrandire l'immagine, fare clic con il tasto destro su di essa e selezionare Apri l'immagine in una nuova scheda
Scarica la nota in formato Word o pdf, esempi in formato Excel
Funzioni cubo e tabelle pivot
I mezzi più semplici e allo stesso tempo molto potenti per presentare i dati sono le tabelle pivot. Possono essere costruiti sulla base dei dati contenuti: a) su un foglio di lavoro Excel, b) un cubo OLAP o c) un modello di dati Power Pivot. Negli ultimi due casi, oltre alla tabella pivot, è possibile utilizzare funzioni analitiche (funzioni cubo) per generare un report su un foglio di lavoro di Excel. Le tabelle pivot sono più facili. Le funzioni dei cubi sono più complicate, ma offrono una maggiore flessibilità, soprattutto nei report, quindi sono ampiamente utilizzate nei dashboard.
Ulteriori discussioni riguardano formule cubiche e tabelle pivot basate sul modello Power Pivot e, in alcuni casi, basate su cubi OLAP.
Un modo semplice per ottenere le funzioni del cubo
Quando (se) hai iniziato a studiare il codice VBA, hai appreso che è più semplice ottenere il codice utilizzando un record di macro. Inoltre, il codice può essere modificato, aggiungere loop, controlli, ecc. Allo stesso modo, è più facile ottenere un set di funzioni cubo trasformando una tabella pivot (Fig. 2). Stare su qualsiasi cella della tabella pivot, andare alla scheda analisi dellefai clic sul pulsante fondi OLAPe fare clic Converti in formule.
Fig. 2. Convertire una tabella pivot in un set di funzioni cubo
I numeri verranno salvati e questi non saranno valori, ma formule che estraggono i dati dal modello di dati Power Pivot (Fig. 3). È possibile formattare la tabella risultante. Inoltre, è possibile eliminare e inserire righe e colonne nella tabella. La sezione rimane e influisce sui dati nella tabella. Quando si aggiornano i dati di origine, verranno aggiornati anche i numeri nella tabella.
Fig. 3. Tabella basata su formule cubiche
Funzione CURVAL ()
Questa è forse la funzione principale dei cubi. È equivalente all'area significato tabella pivot. KUZVALUETS recupera i dati da un cubo o modello Power Pivot e li riflette all'esterno della tabella pivot. Ciò significa che non sei limitato ai limiti della tabella pivot e puoi creare report con innumerevoli possibilità.
Scrivere una formula da zero
Non è necessario convertire la tabella pivot finita. Puoi scrivere qualsiasi formula del cubo da zero. Ad esempio, la seguente formula viene inserita nella cella C10 (Fig. 4):
Fig. 4. La funzione del valore CUB () nella cella C10 restituisce le vendite di biciclette per tutti gli anni, come nella tabella di riepilogo
Piccolo trucco. Per rendere più conveniente la lettura delle formule dei cubi, è auspicabile che venga inserito un solo argomento in ciascuna riga. È possibile ridurre la finestra di Excel. Per fare ciò, fai clic sull'icona Riduci a icona a finestrasituato nell'angolo in alto a destra dello schermo. E quindi regola le dimensioni della finestra in orizzontale. Un'alternativa è forzare il testo della formula su una nuova riga. Per fare ciò, posiziona il cursore nella barra della formula nel punto in cui desideri effettuare il trasferimento e premi Alt + Invio.
Fig. 5. Ridurre a icona la finestra
La sintassi della funzione VALORE ATTUALE ()
La Guida di Excel è assolutamente accurata e completamente inutile per i principianti:
KUZVALUE (connessione; [element_expression1]; [element_expression2]; ...)
collegamento - argomento richiesto; Una stringa di testo che rappresenta il nome della connessione al cubo.
Vyrazhenie_elementa - un argomento facoltativo; Una stringa di testo che rappresenta un'espressione multidimensionale che restituisce un elemento o una tupla in un cubo. Inoltre, "element_expression" può essere un set definito utilizzando la funzione CUBESET. Utilizzare element_expression come slice per determinare la parte del cubo per cui si desidera restituire il valore aggregato. Se non viene specificata alcuna misura nell'argomento element_expression, verrà utilizzata la misura predefinita per questo cubo.
Prima di continuare a spiegare la sintassi della funzione CORRENTE, alcune parole su cubi, modelli di dati e criptico tuple.
Alcune informazioni sui cubi OLAP e sui modelli di dati Power Pivot
Cubi di dati OLAP ( On line lanalytical Processing (analisi dei dati operativi) sono stati sviluppati appositamente per l'elaborazione analitica e la rapida estrazione di dati da essi. Immagina uno spazio tridimensionale in cui periodi, città e merci sono tracciati lungo gli assi (Fig. 5a). I nodi di tale griglia sono i valori di varie misure: volume delle vendite, profitto, costi, numero di unità vendute, ecc. Ora immagina che ci siano dozzine, o addirittura centinaia, di misure ... e ci sono anche molte misure. Questo sarà il cubo OLAP multidimensionale. Creare, configurare e mantenere aggiornati i cubi OLAP è compito dei professionisti IT.
Fig. 5a. Cubo 3D OLAP
Le formule analitiche di Excel (formule cubiche) recuperano i nomi degli assi (ad es. tempo), i nomi degli elementi su questi assi (agosto, settembre), i valori delle misure all'intersezione delle coordinate. È questa struttura che consente alle tabelle pivot basate sul cubo e alle formule del cubo di essere così flessibili e adattarsi alle esigenze degli utenti. I fogli di calcolo Excel basati su fogli di lavoro Excel non utilizzano misure, quindi non sono così flessibili per l'analisi dei dati.
Power Pivot è una funzionalità relativamente nuova di Microsoft. Si tratta di un ambiente Excel incorporato e parzialmente indipendente con un'interfaccia familiare. Power Pivot supera significativamente le tabelle pivot standard. Allo stesso tempo, lo sviluppo di cubi in Power Pivot è relativamente semplice e, soprattutto, non richiede la partecipazione di uno specialista IT. Microsoft sta implementando il suo slogan: "Business intelligence - per le masse!". Sebbene i modelli Power Pivot non siano cubi al 100%, puoi anche parlarne come cubi (per maggiori informazioni consulta il corso introduttivo di Moore. Power Pivot e il volume più grande Rob Collie. Formule DAX per Power Pivot).
I componenti principali di un cubo sono dimensioni, gerarchie, livelli, elementi (o membri; membri in inglese) e misure. Misura - caratteristica principale dei dati analizzati. Ad esempio, categoria di prodotto, periodo di tempo, geografia delle vendite. Una dimensione è ciò che possiamo mettere su uno degli assi della tabella pivot. Oltre ai valori univoci, ogni dimensione include un elemento che aggrega tutti gli elementi di questa dimensione.
Le misure si basano su gerarchia. Ad esempio, una categoria di prodotti può essere suddivisa in sottocategorie, quindi modello e infine nomi di prodotti (Fig. 5b) La gerarchia consente di creare dati di riepilogo e analizzarli a diversi livelli della struttura. Nel nostro esempio, la gerarchia categoria include 4 livello.
elementi (singoli membri) sono presenti a tutti i livelli. Ad esempio, a livello di categoria, ci sono quattro elementi: accessori, biciclette, abbigliamento, componenti. Altri livelli hanno i loro elementi.
misure Questi sono valori calcolati, ad esempio vendite. Le misure in cubi sono memorizzate nella loro dimensione, chiamata (vedi sotto figura 9). Le misure non hanno gerarchie. Ogni misura calcola e memorizza il valore per tutte le dimensioni e tutti gli elementi e viene tagliata in base agli elementi delle dimensioni che posizioniamo sull'asse. Dicono anche quali coordinate impostiamo o quale contesto di filtro impostiamo. Ad esempio, in fig. 5a, in ogni piccolo cubo viene calcolata la stessa misura - Profitto. E il valore restituito dalla misura dipende dalle coordinate. A destra nella Figura 5a è mostrato che il profitto (in tre coordinate) per Mosca ad ottobre sulle mele \u003d 63.000 p. Una misura può essere interpretata come una delle dimensioni. Ad esempio, in fig. 5a anziché asse merceposizionare l'asse misure con elementi Volume delle vendite, profitto, Unità vendute. Quindi ogni cella avrà un valore, ad esempio, Mosca, settembre, le vendite.
treno - diversi elementi di dimensioni diverse che specificano le coordinate lungo gli assi del cubo in cui calcoliamo la misura. Ad esempio, in fig. 5a treno \u003d Mosca, ottobre, mele. Tupla consentita anche - Perm, mele. Un altro è mele, agosto. Le dimensioni non incluse nella tupla sono implicite in essa e sono rappresentate dal membro predefinito. Pertanto, la cella dello spazio multidimensionale è sempre determinata dall'insieme completo di coordinate, anche se alcune di esse sono omesse nella tupla. Non puoi includere due elementi della stessa dimensione in una tupla; la sintassi non lo consentirà. Ad esempio, un corteo invalido di Mosca e Perm, mele. Per realizzare un'espressione così multidimensionale, è necessario un set di due tuple: Mosca e mele + Perm e mele.
Insieme di elementi - diversi elementi di una dimensione. Ad esempio mele e pere. Set di tuple - più tuple, ciascuna delle quali consiste nelle stesse misurazioni nella stessa sequenza. Ad esempio, un set di due tuple: Mosca, mele e Perm, banane.
Aiuto completamento
Ritorniamo alla sintassi della funzione del valore CUB. Useremo il completamento automatico. Inizia a inserire la formula nella cella:
Excel offrirà tutte le connessioni disponibili nella cartella di lavoro di Excel:
Fig. 6. La connessione a un modello di dati Power Pivot è sempre chiamata ThisWorkbookDataModel.
Fig. 7. Collegamenti cubi
Continuiamo a inserire la formula (nel nostro caso, per il modello di dati):
Il completamento offrirà tutte le tabelle e le misure disponibili del modello di dati:
Fig. 8. Elementi disponibili del primo livello: nomi delle tabelle e una serie di misure (evidenziate)
Seleziona icona misure. Metti un punto:
KUZVALUE ("ThisWorkbookDataModel"; ".
Il completamento offrirà tutte le misure disponibili:
Fig. 9. Articoli di secondo livello disponibili in una serie di misure
Scegli una misura Aggiungi le virgolette per chiudere la parentesi, premi Invio.
KUZVALUE ("ThisWorkbookDataModel"; ".")
Fig. 10. La formula del valore CUB nella cella di Excel
Allo stesso modo, puoi aggiungere un terzo argomento alla formula:
VBA in Excel Oggetto Excel.PivotTable e utilizzo di tabelle pivot e cubi OLAP in Excel
10.8 Utilizzo delle tabelle pivot (oggetto tabella pivot)
Oggetto Excel.PivotTable, programma funziona con tabelle pivot e cubi OLAP in Excel utilizzando VBA, oggetto PivotCache, creando un layout di tabella pivot
Nel corso del lavoro della maggior parte delle imprese, vengono accumulati i cosiddetti dati grezzi sull'attività. Ad esempio, per una società commerciale, i dati sulle vendite di beni possono essere accumulati - per ogni acquisto separatamente, per le imprese mobili - statistiche sul carico sulle stazioni base, ecc. Molto spesso, la gestione aziendale necessita di informazioni analitiche generate sulla base di informazioni non elaborate, ad esempio per calcolare il contributo di ciascun tipo di prodotto al reddito dell'azienda o la qualità del servizio nell'area di una determinata stazione. È molto difficile estrarre tali informazioni da informazioni non elaborate: è necessario eseguire query SQL molto complesse che richiedono molto tempo e spesso interferiscono con il lavoro corrente. Pertanto, sempre più spesso ora i dati grezzi vengono ridotti prima al data warehouse di archivio - Data Warehouse e poi ai cubi OLAP, che sono molto convenienti per l'analisi interattiva. Il modo più semplice di immaginare i cubi OLAP è come tabelle multidimensionali, in cui invece delle due dimensioni standard (colonne e righe, come nelle tabelle normali), possono esserci molte dimensioni. In genere, il termine "sezionale" viene utilizzato per descrivere le dimensioni in un cubo. Ad esempio, l'ufficio marketing potrebbe aver bisogno di informazioni in tempo, nel contesto regionale, nel contesto dei tipi di prodotto, nel contesto dei canali di vendita, ecc. Utilizzando i cubi (al contrario delle query SQL standard) è molto facile ottenere risposte a domande come “quanti prodotti di questo tipo sono stati venduti nel quarto trimestre dello scorso anno nella regione nord-occidentale attraverso distributori regionali.
Naturalmente, nei normali database non è possibile creare tali cubi. Per lavorare con i cubi OLAP, sono richiesti prodotti software specializzati. Microsoft SQL viene fornito con SQL Server chiamato Analysis Services. Esistono soluzioni OLAP di Oracle, IBM, Sybase, ecc.
Per lavorare con tali cubi, in Excel è integrato un client speciale. In russo si chiama Tabella riassuntiva (sullo schermo grafico è disponibile tramite il menu dati -> Tabella riassuntiva) e in inglese - Tabella pivot. Di conseguenza, l'oggetto rappresentato da questo client è chiamato tabella pivot. Va notato che è in grado di lavorare non solo con cubi OLAP, ma anche con dati ordinari in tabelle o database Excel, ma molte opportunità vengono perse.
Tabella pivot e tabella pivot sono prodotti Panorama Software acquisiti da Microsoft e integrati in Excel. Pertanto, lavorare con l'oggetto tabella pivot è leggermente diverso dal lavorare con altri oggetti Excel. Indovinare ciò che deve essere fatto è spesso difficile. Pertanto, si consiglia di utilizzare attivamente il registratore di macro per ottenere suggerimenti. Allo stesso tempo, quando si lavora con tabelle pivot, gli utenti devono spesso eseguire le stesse operazioni ripetitive, quindi l'automazione è necessaria in molte situazioni.
Come funziona il programma con la tabella pivot?
La prima cosa che dobbiamo fare è creare un oggetto PivotCache che rappresenterà il set di record ricevuti dall'origine OLAP. In modo molto condizionale, questo oggetto PivotCache può essere confrontato con QueryTable. È possibile utilizzare un solo oggetto PivotCache per ciascuna tabella pivot. La creazione di un oggetto PivotCache viene eseguita utilizzando il metodo Add () della raccolta PivotCaches:
Dim PC1 come PivotCache
Set PC1 \u003d ActiveWorkbook.PivotCaches.Add (xlExternal)
PivotCaches è una raccolta standard e dei metodi che meritano un'attenta valutazione, solo il metodo Add () può essere menzionato in esso. Questo metodo accetta due parametri:
- SourceType - obbligatorio, definisce il tipo di origine dati per la tabella pivot. È possibile specificare la creazione di una tabella pivot basata su un intervallo in Excel, dati da un database, un'origine dati esterna, un'altra tabella pivot, ecc. In pratica, di solito ha senso usare OLAP solo quando ci sono molti dati - di conseguenza, è necessario un archivio esterno specializzato (ad esempio, Microsoft Analysis Services). In questa situazione, xlExternal è selezionato.
- SourceData - obbligatorio in tutti i casi, tranne quelli in cui il valore del primo parametro è xlExternal. È un dato di fatto, definisce l'intervallo di dati in base al quale verrà creata la tabella pivot. Di solito prende un oggetto Range.
Il prossimo compito è configurare i parametri dell'oggetto PivotCache. Come già accennato, questo oggetto è molto simile a QueryTable e il suo set di proprietà e metodi è molto simile. Alcune delle proprietà e dei metodi più importanti:
- ADOConnection - la possibilità di restituire un oggetto Connessione ADO, che viene creato automaticamente per connettersi a un'origine dati esterna. Utilizzato per configurare ulteriormente le proprietà della connessione.
- collegamento - funziona esattamente allo stesso modo della proprietà con lo stesso nome oggetto QueryTable. Può accettare una stringa di connessione, un Recordset finito, un file di testo, una richiesta Web. File di Microsoft Query. Molto spesso, quando si lavora con OLAP, la stringa di connessione viene registrata direttamente (poiché non è molto utile ottenere un oggetto Recordset, ad esempio, per modificare i dati - le origini dati OLAP sono quasi sempre di sola lettura). Ad esempio, l'impostazione di questa proprietà per connettersi al database Foodmart (database di training di Analysis Services) sul server LONDON potrebbe apparire così:
PC1.Connection \u003d "OLEDB; Provider \u003d MSOLAP.2; Origine dati \u003d LONDON1; Catalogo iniziale \u003d FoodMart 2000"
- proprietà CommandType e CommandText descrivere esattamente il tipo di comando che viene trasmesso al server di database e il testo del comando stesso. Ad esempio, per accedere al cubo Vendite e inserirlo interamente nella cache sul client, è possibile utilizzare un codice del modulo
- proprietà LocalConnection consente di connettersi a un cubo locale (file * .cub) creato da Excel. Naturalmente, non è consigliabile utilizzare tali file per lavorare con volumi di dati di "produzione" - solo allo scopo di creare layout, ecc.
- proprietà MemoryUsed restituisce la quantità di RAM utilizzata da PivotCache. Se una tabella pivot basata su questa cache pivot non è stata ancora creata e non è aperta, restituisce 0. Può essere utilizzata per i controlli se l'applicazione verrà eseguita su client deboli.
- proprietà OLAP restituisce True se PivotCache è connesso al server OLAP.
- OptimizeCache - la capacità di ottimizzare la struttura della cache. Il caricamento iniziale dei dati richiederà più tempo, ma potrebbe aumentare la velocità di lavoro. Per le fonti, OLE DB non funziona.
Le restanti proprietà dell'oggetto PivotCache sono le stesse di QueryTable e pertanto non verranno prese in considerazione qui.
Il metodo principale dell'oggetto PivotCache è il metodo CreatePivotTable (). Utilizzando questo metodo, viene eseguito il passaggio successivo: la creazione di una tabella pivot (oggetto Tabella pivot). Questo metodo accetta quattro parametri:
- TableDestination è l'unico parametro richiesto. Accetta un oggetto Range, nell'angolo in alto a sinistra del quale verrà posizionata una tabella pivot.
- TableName è il nome della tabella pivot. Se non specificato, viene automaticamente generato un nome del modulo "SummaryTable1".
- ReadData - se impostato su True, l'intero contenuto del cubo verrà automaticamente memorizzato nella cache. È necessario prestare molta attenzione a questo parametro, poiché un uso improprio di esso può aumentare notevolmente il carico sul client.
- DefaultVersion - questa proprietà di solito non è specificata. Consente di determinare la versione della tabella pivot creata. Per impostazione predefinita, viene utilizzata la versione più recente.
La creazione di una tabella pivot nella prima cella del primo foglio di un libro può essere simile alla seguente:
PC1.CreatePivotTable Range ("A1")
È stata creata una tabella pivot, ma immediatamente dopo la creazione è vuota. Ha quattro aree in cui è possibile posizionare i campi dalla sorgente (sullo schermo grafico, tutto ciò può essere configurato utilizzando la finestra Elenco campi tabella pivot - si apre automaticamente o tramite il pulsante disposizione nell'ultima schermata della procedura guidata per la creazione della tabella pivot):
- area della colonna - quelle misurazioni (“sezione” in cui verranno analizzati i dati), i cui membri sono meno numerosi;
- area di riga - quelle dimensioni, i cui membri sono più grandi;
- area della pagina - le misurazioni con le quali è necessario effettuare solo il filtraggio (ad esempio, mostrare i dati solo per tale regione o solo per un tale anno);
- area dati - in effetti, la parte centrale del tavolo. I dati numerici (ad esempio, la quantità di vendite) che analizziamo.
È difficile fare affidamento sull'utente che posizionerà correttamente gli elementi in tutte e quattro le aree. Inoltre, ciò potrebbe richiedere del tempo. Pertanto, è spesso necessario disporre i dati in una tabella pivot a livello di codice. Questa operazione viene eseguita utilizzando l'oggetto CubeField. La proprietà principale di questo oggetto è Orientamento, determina dove si troverà questo o quel campo. Ad esempio, inseriamo la dimensione Clienti nell'area della colonna:
PT1.CubeFields ("") .Orientation \u003d xlColumnField
Quindi - la dimensione Tempo nell'area della riga:
PT1.CubeFields ("") .Orientation \u003d xlRowField
La prossima è la dimensione del prodotto nell'area della pagina:
PT1.CubeFields ("") .Orientation \u003d xlPageField
E infine, l'indicatore (dati numerici per l'analisi) Vendite unitarie:
PT1.CubeFields ("."). Orientamento \u003d xlDataField
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.
