Lavoro pratico sulla tecnologia olap in excel. Introduzione alle basi dell'OLAP. Proprietà e formattazione

In una tabella pivot standard, i dati di origine vengono archiviati sul disco rigido locale. In questo modo puoi sempre gestirli e riorganizzarli, anche se non hai accesso alla rete. Ma questo non si applica in alcun modo alle tabelle pivot OLAP. Nelle tabelle pivot OLAP, la cache non viene mai archiviata sul disco rigido locale. Pertanto, subito dopo la disconnessione da rete locale la tua tabella pivot fallirà. Non sarai in grado di spostare nessuno dei campi al suo interno.

Se è ancora necessario analizzare i dati OLAP dopo essere andato offline, creare un cubo di dati offline. Il cubo di dati offline è file separato, che è una cache di tabella pivot che archivia i dati OLAP che vengono visualizzati dopo essere stati disconnessi dalla rete locale. I dati OLAP copiati in una tabella pivot possono essere stampati, il sito http://everest.ua lo descrive in dettaglio.

Per creare un cubo di dati autonomo, creare prima una tabella pivot OLAP. Posizionare il cursore all'interno della tabella pivot e fare clic sul pulsante Strumenti OLAP nella scheda contestuale Strumenti, che fa parte del gruppo di schede contestuali Strumenti tabella pivot. Selezionare il comando OLAP offline (Fig. 9.8).

Riso. 9.8. Crea un cubo di dati offline

Viene visualizzata la finestra di dialogo Impostazioni cubo di dati OLAP offline. Fare clic sul pulsante Crea file di dati offline. È stata avviata la Creazione guidata file del cubo di dati. Fare clic sul pulsante Avanti per continuare la procedura.

Innanzitutto è necessario specificare le dimensioni ei 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 le dimensioni che saranno necessarie dopo la disconnessione del computer dalla rete locale. Più dimensioni specifichi, il taglia più grande avrà un cubo di dati offline.

Fare clic sul pulsante Avanti per passare alla finestra di dialogo successiva della procedura guidata. Ti dà la possibilità di specificare membri o elementi di dati che non verranno inclusi nel cubo. In particolare, non avrai bisogno della misura dell'importo esteso per le vendite su Internet, quindi sarà deselezionata nell'elenco. Una casella di controllo 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 di lavoro.

I file del cubo di dati hanno l'estensione .cucciolo

Dopo un po', Excel salva il cubo di dati offline nella cartella specificata. Per testarlo, fare doppio clic sul file, che genererà automaticamente una cartella di lavoro di Excel che contiene una tabella pivot associata al cubo di dati selezionato. Una volta creato, è possibile distribuire il cubo di dati offline a tutti gli utenti interessati che lavorano in modalità LAN offline.

Una volta connesso alla rete locale, è possibile aprire il file del cubo di dati offline e aggiornarlo, nonché la tabella dati corrispondente. Il principio principale è che il cubo di dati offline viene utilizzato solo per il lavoro quando la rete locale è disconnessa, ma è obbligatorio aggiornarlo dopo il ripristino della connessione. Il tentativo di aggiornare un cubo di dati offline dopo l'interruzione della connessione avrà esito negativo.

Lavorare con i file cubo offline

Un file cubo offline (.cub) archivia i dati sotto forma di cubo OLAP (Online Analytical Processing). Questi dati possono far parte del database OLAP sul server OLAP o possono essere generati indipendentemente dal database OLAP. Utilizzare un file cubo offline per continuare a utilizzare i report di tabella pivot e grafico pivot quando il server non è disponibile o quando si è offline.

Nota di sicurezza: Prestare attenzione quando si utilizza o si distribuisce un file cubo offline che contiene dati personali o sensibili. Invece di un file cubo, si consiglia di archiviare i dati in una cartella di lavoro in modo da poter controllare l'accesso ai dati utilizzando la gestione dei diritti. Informazioni aggiuntive può essere trovato in Information Rights Management in Office.

Quando si lavora con un report di tabella pivot o grafico pivot basato sui dati di origine del server OLAP, è possibile utilizzare la Creazione guidata cubo offline per copiare i dati di origine in un file cubo offline separato sul computer. La creazione di questi file offline richiede un provider di dati OLAP che supporti questa funzionalità, ad esempio MSOLAP di Microsoft Services server SQL Analysis Services installato nel computer.

Nota: La creazione e l'utilizzo di file cubo offline da Microsoft SQL Server Analysis Services sono soggetti a termini e licenze Installazioni Microsoft Server SQL. Esaminare le informazioni relative alla licenza dell'edizione SQL Server.

Utilizzo della procedura guidata Cubo offline

Per creare un file cubo offline, è possibile selezionare un sottoinsieme di dati in un database OLAP utilizzando la procedura guidata del cubo offline e quindi salvare quel sottoinsieme. Il report non deve includere tutti i campi inclusi nel file, né selezionare nessuno di essi e i campi dati disponibili nel database OLAP. Per ridurre al minimo il file, puoi includere solo i dati che desideri vengano visualizzati nel rapporto. Puoi omettere tutte le dimensioni e, per la maggior parte dei tipi, puoi anche escludere dettagli e membri di livello inferiore. livello superiore che non devono essere visualizzati. Per tutti gli elementi che includi, anche i campi delle proprietà disponibili nel database per tali elementi vengono archiviati nel file offline.

Trasferimento dati a modalità offline e la loro connessione inversa

A tale scopo, è innanzitutto necessario creare un report di tabella pivot o grafico pivot basato sul database del server, quindi creare un file cubo autonomo dal report. Dopodiché, puoi cambiare il rapporto tra il database del server e il file offline in qualsiasi momento. Ad esempio, se usi PC portatile per i viaggi a casa e video, quindi ricollegare il computer alla rete.

Di seguito sono riportati i passaggi principali da seguire durata della batteria con i dati, quindi trasferirli nuovamente su Internet.

Crea o apri un rapporto di tabella pivot o grafico pivot in base ai dati OLAP a cui desideri accedere offline.

Crea un file cubo offline sul tuo computer. Nella sezione Creare un file cubo offline da un database del server OLAP(sotto in questo articolo).

Disconnessione dalla rete e utilizzo di un file cubo offline.

Vai online e ricollega il file del cubo offline. Scopri la sezione Riconnessione file cubo offline nel database del server OLAP(sotto in questo articolo).

Aggiorna il file del cubo offline con nuovi dati e rigenera il file del cubo offline. Scopri la sezione aggiornare e ricreare il file cubo offline(sotto in questo articolo).

BLOG

Solo post di qualità

Cosa sono le tabelle pivot e i cubi OLAP di Excel

Guarda il video dell'articolo:

OLAP- questo è inglese. elaborazione analitica online, tecnologia di elaborazione dei dati analitici in tempo reale. In parole povere - un archivio con dati multidimensionali (Cubo), ancora più semplice - solo un database da cui è possibile ottenere dati in Excel e analizzarli utilizzando lo strumento Excel - Tabelle Pivot.

Tabelle pivotè un'interfaccia utente per la visualizzazione di dati multidimensionali. In altre parole, un tipo speciale di tabelle, con cui puoi creare quasi tutti i rapporti.

Per chiarire, confrontiamo la "Tabella regolare" con la "Tabella pivot"

Tavola normale:

Tabella pivot:

Differenza principale Tabelle pivotè la presenza di una finestra Elenco campi tabella pivot”, da cui puoi selezionare i campi desiderati e ottenere automaticamente qualsiasi tabella!

Come usare

aprire file Excel, che è connesso al cubo OLAP, ad esempio "BIWEB":

Ora cosa significa questo e come usarlo?

Trascina e rilascia i campi richiesti per ottenere, ad esempio, la seguente tabella:

« vantaggi» consentono di approfondire il report. In questo esempio, 'Marca' viene analizzato in dettaglio in 'Nomi abbreviati' e 'Trimestre' in 'Mese', ad es. Così:

Funzioni analitiche in Excel (funzioni cubo)

Microsoft aggiunge costantemente nuove funzionalità a Excel in termini di analisi e visualizzazione dei dati. Il lavoro con le informazioni in Excel può essere rappresentato come tre livelli relativamente indipendenti:

dati di origine "correttamente" organizzati
matematica (logica) dell'elaborazione dei dati
presentazione dei dati

Riso. 1. Analisi dei dati in Excel: a) dati grezzi, b) misura c Pivot di potenza, c) cruscotto; per ingrandire l'immagine cliccaci sopra fare clic con il tasto destro mouse e selezionare Apri immagine in una nuova scheda

Scarica la nota in formato Word o pdf, esempi in formato Excel

Funzioni del cubo e tabelle pivot

Il mezzo più semplice e allo stesso tempo molto potente per presentare i dati sono le tabelle pivot. Possono essere costruiti dai dati contenuti in: a) 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 le funzioni analitiche (funzioni cubo) per generare un report su un foglio Excel. Le tabelle pivot sono più facili. Le funzioni del cubo sono più complesse, ma offrono maggiore flessibilità, soprattutto nei rapporti, quindi sono ampiamente utilizzate nei dashboard.

La discussione seguente si applica alle formule dei cubi e alle tabelle pivot basate sul modello Power Pivot e, in alcuni casi, sui cubi OLAP.

Un modo semplice per ottenere le funzioni dei cubi

Quando (se) hai iniziato a imparare il codice VBA, hai imparato che il modo più semplice per ottenere il codice è registrare una macro. Inoltre, il codice può essere modificato, possono essere aggiunti loop, controlli, ecc.. Allo stesso modo, il modo più semplice per ottenere un insieme di funzioni del cubo è convertire la tabella pivot (Fig. 2). Posizionati su qualsiasi cella della tabella pivot, vai alla scheda Analisi, fare clic sul pulsante Strutture OLAP e premere Converti in formule.

Riso. 2. Conversione di una tabella pivot in un set di funzioni cubo

I numeri verranno salvati e questi non saranno valori, ma formule che estraggono dati dal modello dati Power Pivot (Fig. 3). È possibile formattare la tabella risultante. In particolare, puoi eliminare e inserire righe e colonne all'interno della tabella. La sezione rimane e influisce sui dati nella tabella. Quando i dati originali vengono aggiornati, verranno aggiornati anche i numeri nella tabella.

Riso. 3. Tabella basata su formule del cubo

VALORECUBO()

Questa è forse la funzione principale dei cubi. È equivalente alla zona I valori tabella pivot. CUBEVALUE recupera i dati da un cubo o un modello Power Pivot e li visualizza all'esterno di una tabella pivot. Ciò significa che non sei limitato dai 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, nella cella C10 viene inserita la seguente formula (Fig. 4):

Riso. 4. La funzione CUBEVALUE() nella cella C10 restituisce le vendite di biciclette per tutti gli anni, come nella tabella pivot

Piccolo trucco. Per semplificare la lettura delle formule del cubo, è auspicabile che su ciascuna riga venga inserito un solo argomento. Puoi ridurre la finestra di Excel. Per fare ciò, fare clic sull'icona Rotola verso la finestra situato a destra angolo superiore schermo. E quindi regolare le dimensioni della finestra in orizzontale. Un'opzione alternativa consiste nel forzare il testo della formula su cui andare a capo nuova linea. Per fare ciò, nella barra della formula, posiziona il cursore nel punto in cui desideri effettuare un trasferimento e premi Alt + Invio.

Riso. 5. Riduci a icona la finestra

Sintassi della funzione VALORECUBO()

L'aiuto di Excel è completamente accurato e completamente inutile per i principianti:

CUBEVALUE(connessione, [espressione_elemento1], [espressione_elemento2], ...)

Connessioneè un argomento obbligatorio; una stringa di testo che rappresenta il nome della connessione al cubo.

elemento_espressione– argomento facoltativo; una stringa di testo che rappresenta un MDX che restituisce un elemento o una tupla nel cubo. Inoltre, "element_expression" può essere un insieme definito utilizzando la funzione CUBESET. Usa "member_expression" come porzione per determinare la parte del cubo per la quale vuoi restituire un valore aggregato. Se non viene specificata alcuna misura in element_expression, verrà utilizzata la misura predefinita per quel cubo.

Prima di procedere alla spiegazione della sintassi della funzione CUBEVALUE, qualche parola su cubi, modelli di dati e criptico tupla.

Alcune informazioni di base sui cubi OLAP e sui modelli di dati Power Pivot

Cubi di dati OLAP ( o n l ine UN analitico P processing - analisi dei dati operativi) sono stati sviluppati appositamente per l'elaborazione analitica e l'estrazione rapida dei dati da essi. Immagina uno spazio tridimensionale, dove gli assi sono periodi di tempo, città e merci (Fig. 5a). I nodi di tale griglia di coordinate contengono i valori varie misure: volume delle vendite, profitto, costi, numero di unità vendute, ecc. Immagina ora che ci siano decine di dimensioni, o addirittura centinaia ... e ci sono anche molte misure. Questo sarà il cubo OLAP multidimensionale. Creare, configurare e tenersi aggiornati con i cubi OLAP è compito dei professionisti IT.

Riso. 5a. Cubo OLAP 3D

Le formule analitiche di Excel (formule del cubo) estraggono i titoli degli assi (ad esempio, Volta), nomi di elementi su questi assi (agosto, settembre), valori di misure all'intersezione di coordinate. È questa struttura che consente alle tabelle pivot e alle formule cubiche basate su cubi di essere così flessibili e di adattarsi alle esigenze degli utenti. Le tabelle pivot basate su fogli Excel non utilizzano misure, quindi non sono così flessibili per scopi di analisi dei dati.

Power Pivot è una funzionalità Microsoft relativamente nuova. Questo è un ambiente Excel integrato e in qualche modo indipendente con un'interfaccia familiare. Power Pivot è di gran lunga superiore alle 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 realizza il suo slogan: "Business analytics - to the masse!". Sebbene i modelli Power Pivot non siano cubi al 100%, possono anche essere indicati come cubi (vedi il corso introduttivo di Power Pivot di Mark Moore e l'edizione più lunga di Rob Colley, Power Pivot DAX Forms per maggiori dettagli).

I componenti principali di un cubo sono dimensioni, gerarchie, livelli, elementi (o membri; in inglese membri) e misure (misure). Misurazione - la caratteristica principale dei dati analizzati. Ad esempio, categoria di prodotto, periodo di tempo, area geografica delle vendite. Una dimensione è qualcosa che possiamo mettere su uno degli assi della tabella pivot. Ogni dimensione a parte valori unici include un elemento che aggrega tutti i membri di questa dimensione.

Le misurazioni si basano su gerarchia. Ad esempio, una categoria di prodotto può essere suddivisa in sottocategorie, poi in modelli e infine in nomi di prodotto (Fig. 5b) La gerarchia consente di creare dati di riepilogo e di analizzarli a vari livelli di struttura. Nel nostro esempio, la gerarchia Categoria include 4 livello.

Elementi(singoli membri) sono presenti a tutti i livelli. Ad esempio, il livello Categoria ha quattro elementi: Accessori, Biciclette, Abbigliamento, Componenti. Altri livelli hanno i propri elementi.

le misure sono valori calcolati, come il volume delle vendite. Le misure in cubi vengono memorizzate nella loro dimensione, chiamata (vedere la Figura 9 di seguito). Le misure non hanno gerarchie. Ciascuna misura calcola e archivia un valore per tutte le dimensioni e tutti i membri e le sezioni in base ai membri della dimensione posizionati sull'asse. Dicono anche quali coordinate imposteremo o quale contesto di filtro imposteremo. Ad esempio, in fig. 5a in ogni cubetto si calcola la stessa misura - Profitto. E il valore restituito dalla misura dipende dalle coordinate. A destra nella Figura 5a è mostrato che Profitto (in tre coordinate) per Mosca in ottobre sulle mele = 63.000 rubli. La misura può essere interpretata e come una delle misure. Ad esempio, in fig. 5a invece di un asse Merce, posizionare l'asse Le misure con elementi Volume delle vendite, Profitto, Unità vendute. Quindi ogni cella avrà un valore, ad esempio Mosca, settembre, volume delle vendite.

Tupla- più articoli dimensioni diverse, definendo le coordinate lungo gli assi del cubo, in cui calcoliamo la misura. Ad esempio, in fig. 5a Tupla= Mosca, ottobre, mele. Anche una tupla valida è Perm, mele. Un altro sono le mele, agosto. Le dimensioni non incluse nella tupla sono implicitamente presenti nella tupla e sono rappresentate dal membro predefinito . Pertanto, una cella di uno spazio multidimensionale è sempre definita set completo coordinate, anche se alcune di esse sono omesse dalla tupla. Non puoi includere due elementi della stessa dimensione in una tupla, la sintassi non lo consente. Ad esempio, la tupla non valida Mosca e Perm, mele. Per implementare un'espressione così multidimensionale, è necessario un insieme di due tuple: Mosca e mele + Perm e mele.

Insieme di elementi– più elementi della stessa dimensione. Ad esempio, mele e pere. Insieme di tuple- più tuple, ciascuna delle quali consiste delle stesse dimensioni nella stessa sequenza. Ad esempio, un insieme di due tuple: Mosca, mele e Perm, banane.

Completamento automatico in soccorso

Torniamo alla sintassi della funzione CUBEVALUE. Usiamo il completamento automatico. Inizia a inserire una formula in una cella:

Excel suggerirà tutte le connessioni disponibili nella cartella di lavoro di Excel:

Riso. 6. La connessione al modello di dati di Power Pivot è sempre denominata ThisWorkbookDataModel

Riso. 7. Collegamenti ai cubi

Continuiamo ad inserire la formula (nel nostro caso, per il modello dati):

Il completamento automatico suggerirà tutte le tabelle disponibili e le misure del modello di dati:

Riso. 8. Elementi disponibili del primo livello - nomi delle tabelle e una serie di misure (evidenziate)

Scegli un'icona Le misure. Indicare:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Il completamento automatico suggerirà tutte le misure disponibili:

Riso. 9. Elementi disponibili di secondo livello nell'insieme delle misure

Scegli una misura. Aggiungi virgolette, parentesi chiusa, premi Invio.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Riso. 10. Formula CUBEVALUE in una 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, lavorare a livello di codice con tabelle pivot e cubi OLAP in Excel utilizzando VBA, oggetto PivotCache, creare un layout di tabella pivot

Durante il funzionamento della maggior parte delle imprese, vengono accumulati i cosiddetti dati grezzi sulle attività. Ad esempio, per un'impresa commerciale, i dati sulle vendite di beni possono essere accumulati - per ogni acquisto separatamente, per le imprese comunicazione cellulare- carica le statistiche su stazioni base eccetera. Molto spesso, la gestione di un'impresa necessita di informazioni analitiche generate sulla base di informazioni grezze, ad esempio per calcolare il contributo di ogni tipo di prodotto al reddito dell'impresa o la qualità del servizio nell'area di un determinato stazione. È molto difficile estrarre tali informazioni da informazioni grezze: è necessario eseguire query SQL molto complesse che richiedono molto tempo e spesso interferiscono con il lavoro in corso. Pertanto, è sempre più comune che i dati grezzi vengano riassunti prima nel Data Warehouse e poi nei cubi OLAP, che sono molto convenienti per l'analisi interattiva. È più facile pensare ai cubi OLAP come a tabelle multidimensionali, in cui, al posto delle due dimensioni standard (colonne e righe, come nelle tabelle normali), possono esserci molte dimensioni. Il termine "sezionale" è comunemente usato per descrivere le dimensioni in un cubo. Ad esempio, un reparto marketing potrebbe aver bisogno di informazioni in base all'ora, alla regione, al tipo di prodotto, al canale di vendita e così via. 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 tramite distributori regionali.

Naturalmente, non è possibile creare tali cubi in database normali. I cubi OLAP richiedono prodotti software specializzati. SQL Server viene fornito con un database OLAP di Microsoft 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 pivot(sul schermo graficoè disponibile attraverso il menu Dati -> tabella pivot), e in inglese - tabella pivot. Di conseguenza, l'oggetto rappresentato da questo client è denominato tabella pivot. Va notato che può funzionare non solo con i cubi OLAP, ma anche con i dati normali nelle tabelle o nei database di Excel, ma molte funzionalità vanno perse.

La tabella pivot e l'oggetto tabella pivot sono prodotti software di Panorama Software acquisiti da Microsoft e integrati in Excel. Pertanto, l'utilizzo dell'oggetto tabella pivot è leggermente diverso dall'utilizzo di altri oggetti di Excel. Capire cosa fare è spesso difficile. Pertanto, si consiglia di utilizzare attivamente il registratore di macro per ricevere suggerimenti. Allo stesso tempo, quando si lavora con le tabelle pivot, gli utenti devono spesso eseguire le stesse operazioni ripetitive, quindi l'automazione è necessaria in molte situazioni.

Che aspetto ha lavorare a livello di codice con una tabella pivot?

La prima cosa che dobbiamo fare è creare un oggetto PivotCache che rappresenterà l'insieme di record recuperati dall'origine OLAP. In modo molto condizionale, questo oggetto PivotCache può essere confrontato con QueryTable. È possibile utilizzare un solo oggetto PivotCache per oggetto tabella pivot. L'oggetto PivotCache viene creato utilizzando il metodo Add() della raccolta PivotCache:

Dim PC1 come PivotCache

Imposta PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches è una raccolta standard e dei metodi che meritano una considerazione dettagliata, è possibile nominare solo il metodo Add(). Questo metodo accetta due parametri:

SourceType- obbligatorio, definisce il tipo di origine dati per la tabella pivot. È possibile scegliere di creare una tabella pivot basata su un intervallo in Excel, dati da un database, un'origine dati esterna, un'altra tabella pivot e così via. In pratica, di solito ha senso utilizzare OLAP solo quando sono presenti molti dati, di conseguenza è necessaria una memoria esterna specializzata (ad esempio Microsoft Analysis Services). In questa situazione, viene selezionato xlExternal.
SourceData- obbligatorio in tutti i casi tranne quando il valore del primo parametro è xlExternal. A rigor di termini, definisce l'intervallo di dati in base al quale verrà creata la tabella pivot. Di solito prende un oggetto Range.

L'attività successiva consiste nel configurare i parametri dell'oggetto PivotCache. Come già accennato, questo oggetto è molto simile a QueryTable e il suo insieme di proprietà e metodi è molto simile. Alcune delle proprietà e dei metodi più importanti sono:

Connessione ADO- la possibilità di restituire un oggetto ADO Connection creato automaticamente per connettersi a un'origine dati esterna. È usato per altre impostazioni proprietà di connessione.
connessione- funziona esattamente come la proprietà dell'oggetto QueryTable con lo stesso nome. Può accettare una stringa di connessione, un oggetto Recordset pronto, file di testo, Richiesta Web. File Microsoft interrogazione. Molto spesso, quando si lavora con OLAP, viene scritta direttamente una stringa di connessione (poiché non ha molto senso ricevere un oggetto Recordset, ad esempio per modificare i dati - le origini dati OLAP sono quasi sempre di sola lettura). Ad esempio, impostando questa proprietà per la connessione al database Foodmart ( base di allenamento dati di Analysis Services) sul server di LONDRA potrebbe avere il seguente aspetto:

PC1.Connection = "OLEDB;Provider=MSOLAP.2;Origine dati=LONDRA1;Catalogo iniziale = FoodMart 2000"

proprietà Tipo di comando e testo del comando descrivere allo stesso modo il tipo di comando che viene inviato al server del database e il testo del comando stesso. Ad esempio, per accedere al cubo Sales e averlo interamente memorizzato nella cache del client, puoi utilizzare codice come

proprietà Connessione Locale consente di connettersi a un cubo locale (file *.cub) creato da Excel. Naturalmente, è altamente sconsigliato utilizzare tali file per lavorare con volumi di dati di "produzione", solo allo scopo di creare layout, ecc.
proprietà Memoria Usata restituisce il numero memoria ad accesso casuale, utilizzato da PivotCache. Se la tabella pivot basata su questa PivotCache non è stata ancora creata e aperta, restituisce 0. Può essere utilizzato per verificare se l'applicazione funzionerà su client deboli.
proprietà OLAP restituisce True se PivotCache è connesso al server OLAP.
Ottimizza cache- la capacità di ottimizzare la struttura della cache. Il caricamento iniziale dei dati richiederà più tempo, ma la velocità di lavoro potrebbe aumentare. Per le origini OLE DB non funziona.

Le restanti proprietà dell'oggetto PivotCache sono le stesse dell'oggetto QueryTable e pertanto non verranno discusse qui.

Il metodo principale dell'oggetto PivotCache è il metodo CreatePivotTable(). Con l'aiuto di questo metodo, viene eseguita la fase successiva: la creazione di una tabella pivot (oggetto tabella pivot). Questo metodo richiede quattro parametri:

Destinazione tabellaè l'unico parametro richiesto. Accetta un oggetto Range, nell'angolo in alto a sinistra del quale verrà posizionata la tabella pivot.
nome della tabella- Nome della tabella pivot. Se non specificato, verrà generato automaticamente il nome del modulo "PivotTable1".
leggere i dati- se impostato su True, tutto il contenuto del cubo verrà automaticamente memorizzato nella cache. È necessario prestare molta attenzione con questo parametro, perché il suo utilizzo errato può aumentare notevolmente il carico sul client.
Versione predefinita- Questa proprietà di solito non è specificata. Consente di specificare la versione della tabella pivot in fase di creazione. Per impostazione predefinita, viene utilizzata la versione più recente.

La creazione di una tabella pivot nella prima cella del primo foglio della cartella di lavoro potrebbe essere simile a questa:

PC1.CreatePivotTable Range ("A1")

La tabella pivot è stata creata, ma subito dopo la creazione è vuota. Prevede quattro aree in cui è possibile inserire i campi dalla sorgente (nella videata grafica tutto questo è configurabile sia tramite la finestra Elenco campi tabella pivot- si apre automaticamente o con un pulsante Disposizione nell'ultima schermata della procedura guidata tabella pivot):

area della colonna- contiene quelle dimensioni (“sezione” in cui verranno analizzati i dati), i cui membri sono minori;
area della linea- quelle dimensioni i cui membri sono di più;
area della pagina- quelle misurazioni per le quali è solo necessario filtrare (ad esempio, mostrare i dati solo per tale regione o solo per tale anno);
area dati- infatti, la parte centrale del tavolo. Quei dati numerici (ad esempio, l'importo delle vendite) che analizziamo.

Affidarsi all'utente per posizionare correttamente gli elementi in tutte e quattro le aree è difficile. Inoltre, potrebbe volerci 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, mettiamo la dimensione Clienti nell'area della colonna:

PT1.CubeFields("").Orientation = xlColumnField

Quindi - la dimensione Tempo nell'area delle stringhe:

PT1.CubeFields(""). Orientation = xlRowField

Quindi - la dimensione Prodotto nell'area della pagina:

PT1.CubeFields(""). Orientation = xlPageField

E infine, l'indicatore (dati numerici per l'analisi) Unit Sales:

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

La prima interfaccia della tabella pivot, chiamata anche report pivot, è stata inclusa in Excel nel 1993 (Excel versione 5.0). Nonostante molti utili funzionalità, praticamente non viene utilizzato nel lavoro della maggior parte degli utenti di Excel. Anche gli utenti esperti spesso intendono con il termine "relazione di riepilogo" qualcosa costruito utilizzando formule complesse. Proviamo a rendere popolare l'uso delle tabelle pivot nel lavoro quotidiano degli economisti. L'articolo discute le basi teoriche per la creazione di rapporti di sintesi, fornisce raccomandazioni pratiche per il loro utilizzo e fornisce anche un esempio di accesso ai dati basato su diverse tabelle.

Termini di analisi dei dati multivariati

La maggior parte degli economisti ha sentito i termini "dati multidimensionali", "cubo virtuale", "tecnologie OLAP", ecc. Ma con una conversazione dettagliata, di solito si scopre che quasi tutti non sanno davvero cosa in questione. Cioè, le persone intendono qualcosa di complesso e di solito non rilevante per le loro attività quotidiane. In realtà non lo è.

Dati multidimensionali, dimensioni

È sicuro dire che gli economisti si occupano quasi sempre di dati multidimensionali, ma cercano di presentarli in un modo predefinito utilizzando fogli di calcolo. Multidimensionalità qui significa la capacità di inserire, visualizzare o analizzare le stesse informazioni con una modifica aspetto esteriore, applicando vari raggruppamenti e ordinamenti di dati. Ad esempio, un piano di vendita può essere analizzato secondo i seguenti criteri:

tipi o gruppi di merci;
marchi o categorie di prodotti;
periodi (mese, trimestre, anno);
acquirenti o gruppi di acquirenti;
regioni di vendita
eccetera.

Ciascuno dei criteri di cui sopra in termini di analisi dei dati multivariati è chiamato "dimensione". Si può dire che una dimensione caratterizza l'informazione su un certo insieme di valori. Un tipo speciale di misurazione delle informazioni multidimensionali sono i "dati". Nel nostro esempio, i dati del piano di vendita possono essere:

volume delle vendite;
Prezzo di vendita;
sconto individuale
eccetera.

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

Pertanto, possiamo dire che nel lavoro pratico, gli economisti utilizzano due tipi di informazioni: dati multidimensionali ( numeri effettivi e programmati con molte caratteristiche) e directory (caratteristiche o misurazioni di dati).

OLAP

L'abbreviazione OLAP (elaborazione analitica in linea) nella traduzione letterale suona come "elaborazione analitica in tempo reale". La definizione non è molto specifica; quasi tutti i report di qualsiasi prodotto software possono essere riassunti sotto di essa. Per definizione, OLAP implica una tecnologia per lavorare con report speciali, incluso il 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 tecnico coniato da alcuni fornitori di Software. I sistemi OLAP di solito preparano e archiviano i dati nelle proprie strutture e speciali interfacce di analisi (come i report di riepilogo di Excel) accedono ai dati da questi cubi virtuali. Allo stesso tempo, l'uso di tale memoria dedicata non è affatto necessario per l'elaborazione di informazioni multidimensionali. Generalmente, cubo virtuale- si tratta di un array di dati multidimensionali appositamente ottimizzati che viene utilizzato per creare report di riepilogo. Può essere ottenuto sia attraverso strumenti software specializzati che attraverso il semplice accesso alle tabelle del database o qualsiasi altra fonte, come un foglio di calcolo Excel.

tabella pivot

"Report pivot" (tabella pivot, tabella pivot) è un'interfaccia utente per la visualizzazione di dati multidimensionali. Utilizzando questa interfaccia, è possibile raggruppare, ordinare, filtrare e modificare la posizione dei dati per ottenere vari campioni analitici. Il report viene aggiornato utilizzando semplici strumenti di interfaccia utente, i dati vengono aggregati automaticamente secondo le regole specificate e non è richiesta alcuna aggiunta o reimmissione di alcuna informazione. Interfaccia pivot Tabelle Excelè forse il prodotto software più popolare per lavorare con dati multidimensionali. Supporta sia origini dati esterne (cubi OLAP e database relazionali) sia 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 implementata in Excel consente di disporre le dimensioni dei dati multidimensionali nell'area del foglio di lavoro. Per semplicità, puoi pensare a una tabella pivot come a un report che giace sopra un intervallo di celle (in effetti, esiste una certa associazione dei formati di cella ai campi della tabella pivot). Una tabella pivot di Excel ha quattro aree di visualizzazione: filtro, colonne, righe e dati. Le dimensioni dei dati sono denominate 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. Più vicino nel significato sarebbe l'uso diffuso del termine "rapporto pivot" (rapporto pivot), ed è così che questa interfaccia è stata chiamata fino all'anno 2000. Ma per qualche motivo, nelle versioni successive, gli sviluppatori lo hanno abbandonato.

Modifica 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, un'intera classe di sistemi software, realizzando le possibilità 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 molte attività non standard. Un esempio di implementazione della modifica per le tabelle pivot di Excel in base ai dati del foglio di lavoro è disponibile sul nostro sito Web.

Preparazione di dati multidimensionali

Andiamo a applicazione pratica tabelle pivot. Proviamo ad analizzare i dati di vendita in diverse direzioni. File esempio pivottable.xlsè composto da più fogli. Lenzuolo Esempio contiene informazioni di base sulle vendite per un determinato periodo. Per semplicità dell'esempio, analizzeremo l'unico indicatore numerico: il volume delle vendite in kg. Sono disponibili le seguenti dimensioni dei dati chiave: prodotto, acquirente e vettore (azienda di trasporto). Inoltre, ci sono diverse dimensioni aggiuntive dei dati che sono attributi del prodotto: tipo, marca, categoria, fornitore, nonché acquirente: tipo. Questi dati sono raccolti nella scheda Directory. In pratica, potrebbero esserci molte più misurazioni di questo tipo.

Lenzuolo Esempio contiene rimedio standard analisi dei dati - filtro automatico. Osservando l'esempio di compilazione della tabella, è ovvio che i dati di vendita per data (sono disposti in colonne) si prestano ad analisi normali. Inoltre, utilizzando il filtro automatico, puoi provare a riassumere i dati tramite combinazioni di uno o più criteri chiave. Non ci sono assolutamente informazioni su marchi, categorie e tipi. Non è possibile raggruppare i dati con il riepilogo automatico per 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, in modo automatico.

In generale, la presenza di una posizione di data predefinita in questo esempio è il principale inconveniente della tabella. Disponendo le date per colonne, abbiamo in qualche modo predeterminato la dimensione di questa tabella, privandoci così dell'opportunità di utilizzare l'analisi utilizzando le tabelle pivot.

Innanzitutto, dobbiamo sbarazzarci di questa mancanza, ad es. rimuovere la posizione predefinita di una delle dimensioni dei dati di origine. Un esempio di tabella valida è un foglio I saldi.

La tabella ha la forma di un registro di immissione di informazioni. Qui, la data è una dimensione di dati uguale. Va anche notato che per l'analisi successiva nelle tabelle pivot, la posizione relativa delle righe l'una rispetto all'altra (in altre parole, l'ordinamento) è completamente indifferente. I record nei database relazionali hanno queste proprietà. È l'analisi di grandi volumi di database che si concentra principalmente sull'interfaccia delle tabelle pivot. Pertanto, è necessario rispettare queste regole quando si lavora con un'origine dati sotto forma di intervalli di celle. Allo stesso tempo, nessuno vieta di utilizzare gli strumenti dell'interfaccia di Excel nel tuo lavoro: le tabelle pivot analizzano solo i dati e la formattazione, i filtri, il raggruppamento e l'ordinamento delle celle di origine possono essere arbitrari.

Dal filtro automatico al rapporto di riepilogo

Teoricamente, sui dati del Foglio di vendita, è già possibile analizzare in tre dimensioni: merce, acquirenti e corrieri. Non ci sono dati sulle proprietà dei prodotti e degli acquirenti in questo foglio, il che, di conseguenza, non consentirà loro di essere mostrati nella tabella riepilogativa. Nella modalità normale di creazione di una tabella pivot per l'originale dati excel non consente di collegare dati da più tabelle su determinati campi. Puoi aggirare questa limitazione strumenti software- vedere un esempio-aggiunta a questo articolo sul nostro sito web. Per non ricorrere a metodi programmatici di elaborazione delle informazioni (soprattutto perché non universali), è necessario aggiungere ulteriori caratteristiche direttamente al modulo di inserimento del registro - vedere il foglio SalesAnalysis.

L'uso delle funzioni VLOOKUP semplifica l'integrazione dei dati originali con le caratteristiche mancanti. Ora, applicando il filtro automatico, puoi analizzare i dati in dimensioni diverse. Ma il problema dei raggruppamenti rimane irrisolto. Ad esempio, tenere traccia dell'importo solo per i marchi in determinate date è piuttosto problematico. Se sei limitato alle formule di Excel, devi creare campioni aggiuntivi utilizzando la funzione SOMMA.SE.

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

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

La seconda e la terza tabella vengono create mediante copiatura e successiva personalizzazione. L'origine dati per tutte le tabelle è la stessa. Puoi verificarlo modificando i dati originali, quindi è necessario aggiornare i dati dei rapporti di riepilogo.

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

Proprietà e formattazione

Oltre alla visualizzazione diretta dei dati, è disponibile un ampio set di opzioni per visualizzare l'aspetto delle tabelle pivot. I dati extra possono essere nascosti usando i filtri. Per un singolo elemento o campo, è più semplice utilizzare la voce del menu contestuale Eliminare(nella versione 2000-2003 Nascondere).

È anche desiderabile impostare la visualizzazione di altri elementi della tabella pivot non tramite la formattazione della cella, ma impostando il campo o l'elemento della tabella pivot. Per fare ciò, sposta il puntatore del mouse sull'elemento desiderato, attendi la comparsa di una forma speciale del cursore (a forma di freccia), quindi seleziona l'elemento selezionato con un solo clic. Dopo la selezione, è possibile modificare la visualizzazione tramite la barra multifunzione, il menu di scelta rapida o richiamare la finestra di dialogo del formato cella standard:

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

Si noti che i filtri di controllo e le aree di trascinamento sono attivi nel grafico.

Accesso a dati esterni

Come già notato, forse l'effetto maggiore dall'uso delle tabelle pivot può essere ottenuto quando si accede a dati da fonti esterne: cubi OLAP e query di database. Tali fonti di solito memorizzano grandi quantità di informazioni e hanno anche un predefinito struttura relazionale, 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'uso di fonti di informazione esterne può essere ottenuto utilizzando strumenti di automazione (programmi VBA) sia per ottenere dati che per la loro elaborazione preliminare in tabelle pivot.

Incredibile vicino...

Nel corso del lavoro mi è capitato spesso di dover realizzare relazioni complesse, ho sempre cercato di trovare qualcosa in comune in esse per comporle in modo più semplice e universale, ho anche scritto e pubblicato un articolo su questo argomento “L'albero di Osipov”. Tuttavia, hanno criticato il mio articolo e hanno affermato che tutti i problemi che ho sollevato sono stati risolti da tempo in MOLAP.RU v.2.4 (www.molap.rgtu.ru) e hanno consigliato di guardare le tabelle pivot in EXCEL.
Si è rivelato essere così semplice che, dopo averci attaccato le mie piccole mani ingegnose, ho ottenuto molto circuito semplice per lo scarico dei dati da 1C7 o qualsiasi altro database (di seguito, 1C indica qualsiasi database) e l'analisi in OLAP.
Penso che molti schemi di caricamento OLAP siano troppo complicati, scelgo la semplicità.

Caratteristiche :

1. Per funzionare è necessario solo EXCEL 2000.
2. L'utente stesso può progettare report senza programmazione.
3. Caricamento da 1C7 in un semplice formato di file di testo.
4. Per le scritture contabili già disponibili elaborazione universale per lo scarico, funzionante in qualsiasi configurazione. Per scaricare altri dati, ci sono l'elaborazione del campione.
5. È possibile pre-progettare moduli di report e quindi applicarli a dati diversi senza riprogettarli.
6. Carina buona performance. Nella prima fase lunga, i dati vengono prima importati in EXCEL da un file di testo e viene creato un cubo OLAP, quindi qualsiasi report può essere creato abbastanza rapidamente in base a questo cubo. Ad esempio, i dati sulle vendite di merci in un negozio per 3 mesi con un assortimento di 6000 merci vengono caricati in EXCEL in 8 minuti su Cel600-128M, la classificazione per merci e gruppi (report OLAP) viene ricalcolata in 1 minuto.
7. I dati vengono scaricati integralmente da 1C7 per il periodo specificato (tutti i movimenti, per tutti i magazzini, aziende, conti). Durante l'importazione in EXCEL, è possibile utilizzare filtri che caricano solo i dati necessari per l'analisi (ad esempio, da tutti i movimenti, solo vendite).
8. Attualmente sono stati sviluppati metodi per analizzare movimenti o residui, ma non movimenti e residui insieme, sebbene ciò sia possibile in linea di principio.

Cos'è l'OLAP : (www.molap.rgtu.ru)

Supponiamo di averlo rete commerciale. Lascia che i dati sulle operazioni di trading vengano caricati in un file di testo o in una tabella del modulo:

Data - data della transazione
Mese - mese di funzionamento
Settimana - settimana di funzionamento
Tipo: acquisto, vendita, restituzione, cancellazione
Controparte - organizzazione esterna partecipando all'operazione
Autore - la persona che ha emesso la fattura

In 1C, ad esempio, una riga di questa tabella corrisponderà a una riga della fattura, alcuni campi (Appaltatore, Data) sono presi dall'intestazione della fattura.

I dati per l'analisi vengono solitamente caricati nel sistema OLAP per un certo periodo di tempo, dal quale, in linea di principio, è possibile distinguere un altro periodo utilizzando i filtri di carico.

Questa tabella è la fonte per l'analisi OLAP.

Rapporto	misurazioni	Dati	Filtro
Quante merci e per quale importo vengono vendute al giorno?	Data, prodotto	Quantità, Importo	Visualizza="vendita"
Quali controparti hanno fornito quali beni per quale importo al mese?	Mese, appaltatore, prodotto	Somma	Visualizza="acquista"
Per quale importo gli operatori di fatture di che tipo hanno scritto per l'intero periodo del rapporto?		Somma

L'utente stesso determina quali dei campi della tabella saranno Dimensioni, quali Dati e quali Filtri applicare. Il sistema stesso crea un report in una forma tabellare visiva. Le dimensioni possono essere inserite nelle intestazioni di riga o di colonna di una tabella di report.
Come puoi vedere, da una semplice tabella puoi ottenere molti dati sotto forma di vari rapporti.

Come usare da solo :

Decomprimere i dati dal pacchetto di distribuzione esattamente nella directory c:\fixin (per un sistema di trading è possibile c:\reports) . Leggi readme.txt e segui tutte le istruzioni in esso contenute.

Per prima cosa devi scrivere un'elaborazione che carichi i dati da 1C in un file di testo (tabella). È necessario definire la composizione dei campi che verranno caricati.
Ad esempio, l'elaborazione universale pronta che funziona in qualsiasi configurazione e scarica le transazioni per un periodo per l'analisi OLAP scarica i seguenti campi per l'analisi:

Laddove sotto i prefissi Dt (Kt) sono presenti sottoconto di Debito (Credito), Gruppo è un gruppo di questo sottoconto (se presente), Sezione è un gruppo di un gruppo, Classe è un gruppo di sezioni.

Per un sistema di trading, i campi possono essere i seguenti:

Per l'analisi dei dati vengono utilizzate le tabelle "Analisi di movimenti.xls" ("Analisi di contabilità.xls"). Quando li apri, non disabilitare le macro, altrimenti non sarai in grado di aggiornare i report (vengono attivati da macro in linguaggio VBA). Questi file prendono i loro dati iniziali dai file C:\fixin\motions.txt (C:\fixin\buh.txt), altrimenti sono gli stessi. Pertanto, potrebbe essere necessario copiare i dati in uno di questi file.
Affinché i tuoi dati vengano caricati in EXCEL, seleziona o scrivi il tuo filtro e fai clic sul pulsante "Genera" nella scheda "Condizioni".
I fogli dei rapporti iniziano con il prefisso "Da". Vai al foglio del rapporto, fai clic su "Aggiorna" e i dati del rapporto cambieranno in base agli ultimi dati caricati.
Se non sei soddisfatto dei report standard, c'è un foglio OtchTemplate. Copialo in un nuovo foglio e personalizza la visualizzazione del report lavorando con una tabella pivot su questo foglio (ulteriori informazioni sull'utilizzo delle tabelle pivot - in qualsiasi libro su EXCEL 2000). Ti consigliamo di impostare i rapporti per piccolo insieme dati e quindi eseguirli su un array di grandi dimensioni, perché non è possibile disabilitare il ridisegno della tabella ogni volta che cambia il layout del report.

Note tecniche :

Quando si caricano i dati da 1C, l'utente seleziona la cartella in cui caricare il file. L'ho fatto perché è probabile che nel prossimo futuro verranno caricati diversi file (avanzi e spostamenti). Quindi, facendo clic sul pulsante "Invia" --> "All'analisi OLAP in EXCEL 2000" nell'Explorer, i dati vengono copiati dalla cartella selezionata nella cartella C:\fixin. (perché questo comando appaia nell'elenco del comando "Invia", è necessario copiare il file "For OLAP analysis in EXCEL 2000.bat" nella directory C:\Windows\SendTo) Quindi caricare i dati dando subito i nomi ai file motions.txt o buh.txt.

Formato file di testo:
La prima riga del file di testo contiene le intestazioni di colonna separate da "|", le righe rimanenti contengono i valori di queste colonne separati da "|".

Microsoft Query viene utilizzato per importare file di testo in Excel ( componente EXCEL) per il suo funzionamento è necessario avere un file shema.ini nella directory di importazione (C:\fixin) contenente le seguenti informazioni:

ColNameHeader=Vero
Formato=Delimitato(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=Vero
Formato=Delimitato(|)
MaxScanRows=3
CharacterSet=ANSI

Spiegazione: motions.txt e buh.txt è il nome della sezione, corrisponde al nome del file importato, descrive come importare un file di testo in Excel. I restanti parametri indicano che la prima riga contiene i nomi delle colonne, il separatore di colonna è "|", il set di caratteri è Windows ANSI (per DOS - OEM).
Il tipo di campo viene determinato automaticamente in base ai dati contenuti nella colonna (data, numero, stringa).
L'elenco dei campi non deve essere descritto da nessuna parte: EXCEL e OLAP determineranno autonomamente quali campi sono contenuti nel file in base alle intestazioni nella prima riga.

Attenzione, controlla le tue impostazioni regionali "Pannello di controllo" --> "Impostazioni regionali". Nella mia elaborazione, i numeri vengono caricati con una virgola separata e le date sono nel formato "GG.MM.AAAA".

Quando si fa clic sul pulsante "Genera", i dati vengono caricati nella tabella pivot sul foglio "Base" e tutti i report sui fogli "Return" prendono i dati da questa tabella pivot.

Capisco che gli amanti di MS SQL Server e basi potenti i dati inizieranno a brontolare che tutto è troppo semplificato per me, che la mia elaborazione sarà piegata su un campione annuale, ma prima di tutto voglio dare i vantaggi dell'analisi OLAP alle organizzazioni di medie dimensioni. Posizionare questo prodotto come strumento di analisi annuale per i grossisti, analisi trimestrale per i rivenditori e analisi operativa per qualsiasi organizzazione.

Ho dovuto armeggiare con VBA in modo che i dati fossero presi da un file con qualsiasi elenco di campi ed fosse possibile preparare in anticipo i moduli di rapporto.

Descrizione del lavoro in EXCEL (per gli utenti):

Istruzioni per l'utilizzo dei rapporti:
1. Inviare i dati scaricati per l'analisi (verificare con l'amministratore). Per fare ciò, fai clic con il tasto destro del mouse sulla cartella in cui hai caricato i dati da 1C e seleziona il comando "Invia", quindi "All'analisi OLAP in EXCEL 2000".
2. Aprire il file "Motion Analysis.xls".
3. Seleziona il valore Filtro, i filtri di cui hai bisogno possono essere aggiunti nella scheda "Valori".
4. Fare clic sul pulsante "Genera" e i dati scaricati verranno caricati in EXCEL.
5. Dopo aver caricato i dati in EXCEL, è possibile visualizzare vari report. Per fare ciò, fai semplicemente clic sul pulsante "Aggiorna" nel rapporto selezionato. I fogli dei rapporti iniziano con Rep.
Attenzione! Dopo aver modificato il valore del filtro, è necessario fare nuovamente clic sul pulsante "Genera" in modo che i dati in EXCEL vengano ricaricati dal file di caricamento in base ai filtri.

Elaborazione dalla demo:

L'elaborazione di motionsbuh2011.ert è l'ultima versione delle transazioni di scarico da Accounting 7.7 per l'analisi in Excel. Ha una casella di controllo "Aggiungi al file", che ti consente di caricare i dati in parti per periodi, allegandoli allo stesso file e non caricandoli nuovamente sullo stesso file:

L'elaborazione di motionswork.ert carica i dati di vendita per l'analisi in Excel.

Segnala esempi:

Scacchi pubblicando:

Carico di lavoro degli operatori per tipologia di fatture:

PS :

È chiaro che secondo uno schema simile è possibile organizzare lo scarico dei dati da 1C8.
Nel 2011 sono stato contattato da un utente che aveva bisogno di perfezionare questa elaborazione in 1C7 in modo che potesse caricare grandi quantità di dati, ho trovato un outsourcer e ho fatto questo lavoro. Quindi lo sviluppo è abbastanza rilevante.

L'elaborazione di Motionsbuh2011.ert è stata migliorata per gestire i caricamenti di dati di grandi dimensioni.

I problemi di analisi, OLAP, data warehouse sono di crescente interesse per gli specialisti IT russi. Ad oggi, la nostra stampa informatica e Internet hanno pubblicato molto di buono, accademico nel modo di presentare materiali su questo argomento, compresi quelli introduttivi. Portiamo alla vostra attenzione un articolo in cui cerchiamo volutamente di spiegare OLAP “sulle dita”, usando un esempio specifico. La pratica dimostra che tale spiegazione è necessaria per alcuni professionisti IT e soprattutto per gli utenti finali.

Quindi, OLAP *1 in prima approssimazione, "sulle dita", può essere definito come un modo speciale di analizzare i dati e generare report. La sua essenza è fornire all'utente una tabella multidimensionale che riepiloga automaticamente i dati nelle varie sezioni e consente di gestire in modo interattivo i calcoli e la forma del report. Questo articolo parlerà della tecnologia e delle operazioni di base di OLAP utilizzando l'esempio dell'analisi delle fatture per un'impresa impegnata nel commercio all'ingrosso di prodotti alimentari.

*uno. OLAP - On-Line Analytical Processing, analisi dei dati operativi.

Come strumento, verrà preso in considerazione un sistema OLAP della classe più semplice ed economica, un client OLAP *1. Ad esempio, viene scelto il prodotto più semplice dal numero di client OLAP: "Contour Standard" di Intersoft Lab. (Per chiarezza, più avanti nell'articolo, i termini OLAP generalmente accettati saranno indicati in grassetto e accompagnati da controparti inglesi.)

*uno. Per ulteriori informazioni sulla classificazione dei sistemi OLAP, vedere l'articolo "OLAP Made in Russia" in PC Week/RE, No. 3/2001.

Quindi iniziamo con il sistema. Per prima cosa devi descrivere l'origine dati (origine dati): il percorso della tabella e i suoi campi. È compito dell'utente che sa implementazione fisica Banca dati. Per gli utenti finali, traduce il nome della tabella e dei suoi campi in termini di dominio. Dietro l'"origine dati" c'è una tabella locale, una tabella o una vista del server SQL o una procedura memorizzata.

Molto probabilmente, in un determinato database, le fatture non vengono archiviate in una, ma in più tabelle. Inoltre, alcuni campi o record potrebbero non essere utilizzati per l'analisi. Viene quindi creato un Sample (result set o query) in cui vengono configurati: l'algoritmo per la combinazione delle tabelle per campi chiave, le condizioni di filtraggio e l'insieme dei campi restituiti. Chiamiamo la nostra selezione "Fatture" e inseriamo in essa tutti i campi dell'origine dati "Fatture". Pertanto, uno 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 un esperto in materia. Innanzitutto, i campi di un campione di dati piatti sono divisi in due gruppi: fatti (fatti o misure) e misurazioni (dimensioni). I fatti sono numeri e le misurazioni sono "tagli" in cui i fatti verranno riassunti. Nel nostro esempio, le dimensioni saranno: "Regione", "Città", "Cliente", "Prodotto", "Data" e il fatto sarà uno: il campo "Importo" della fattura. Di 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 ad analisi statistiche. La selezione di più algoritmi di aggregazione creerà fatti virtuali calcolati. Nell'esempio, è selezionato un algoritmo di aggregazione: "Sum".

Una proprietà speciale dei sistemi OLAP è la generazione di dimensioni e dati per periodi di tempo precedenti a partire da una data e il calcolo automatico dei totali per questi periodi. Selezioniamo i periodi “Anno”, “Trimestre” e “Mese”, mentre nel report non ci saranno dati per ogni giorno, ma appariranno le dimensioni generate “Anno”, “Trimestre” e “Mese”. Denominiamo il rapporto "Analisi delle vendite" e lo salviamo. Il lavoro sulla creazione dell'interfaccia dell'applicazione analitica è completato.

Ora, quando si esegue questa interfaccia su base giornaliera o mensile, l'utente vedrà una tabella e un grafico in cui le fatture sono riepilogate per articolo, cliente e periodo.

Per rendere intuitive le manipolazioni con i dati, gli strumenti per la gestione di una tabella dinamica sono gli elementi della tabella stessa: le sue colonne e righe. L'utente può spostare, eliminare, filtrare ed eseguire altre operazioni OLAP. La tabella calcola automaticamente i nuovi totali parziali e finali.

Ad esempio, trascinando (operazione "sposta") la colonna "Prodotto" al primo posto, otterremo un rapporto di confronto: "Confronto dei volumi di vendita dei prodotti per l'anno". Per aggregare i dati per un anno, trascina le colonne "Trimestre" e "Mese" nella parte superiore della tabella - "area misurazioni inattive". Le dimensioni “Trimestre” e “Mese” trasferite in tale area verranno chiuse (operazione di “chiudi dimensione”), ovvero escluse dal report; i fatti saranno riassunti per l'anno. Nonostante le dimensioni siano chiuse, è possibile impostare anni, trimestri e mesi specifici per il filtraggio dei dati (operazione "filtro").

Per maggiore chiarezza, cambiamo il tipo di grafico che illustra la tabella OLAP e la sua posizione sullo schermo.

Il drill down dei dati (operazione "drill down") consente di ottenere informazioni più dettagliate sulle vendite del prodotto di nostro interesse. Facendo clic sul segno "+" di fronte al prodotto "Caffè", vedremo i suoi volumi di vendita per regione. Avendo aperto la regione degli Urali, otterremo volumi di vendita nel contesto delle città della regione degli Urali, approfondendo i dati su Ekaterinburg, saremo in grado di visualizzare i dati sugli acquirenti all'ingrosso di questa città.

Puoi anche utilizzare misurazioni aperte per impostare i filtri. Per confrontare le dinamiche delle vendite di caramelle a Mosca ed Ekaterinburg, impostiamo filtri per le dimensioni "Prodotto" e "Città".

Chiudere le misurazioni non necessarie e selezionare il tipo di grafico "Linea". Sul grafico risultante è possibile tracciare la dinamica delle vendite, valutare le fluttuazioni stagionali e il rapporto tra cali e crescita delle vendite di beni nelle diverse città.

Eravamo quindi convinti che la tecnologia OLAP permettesse all'utente di rilasciare decine di tipi di report diversi da un'unica interfaccia, gestendo una tabella OLAP dinamica con il mouse. Il compito di un programmatore che possiede un tale strumento non è la codifica di routine dei moduli di segnalazione, ma la creazione di un client OLAP per i database. Allo stesso tempo, le modalità di gestione del report sono intuitive per l'utente finale.

In effetti, OLAP è una naturale estensione e sviluppo dell'idea di fogli di calcolo. Infatti, interfaccia visiva OLAP è anche un foglio di calcolo, ma dotato di macchina potente informatica e uno standard specifico per la presentazione e la gestione dei dati. Inoltre, alcuni client OLAP vengono implementati come componenti aggiuntivi di MS Excel. Pertanto, anche il milionesimo esercito di "colletti bianchi", che hanno fiducia nei fogli di calcolo, sta rapidamente padroneggiando gli strumenti OLAP. Per loro si tratta di una “rivoluzione di velluto” che offre nuove opportunità, ma non comporta la necessità di riqualificarsi.

Se il lettore, dopo aver letto questo articolo, non ha perso interesse per OLAP, può fare riferimento ai materiali citati all'inizio. Le raccolte di tali materiali sono pubblicate su numerosi siti Internet, incluso il sito Intersoft lab - www.iso.ru. Da esso puoi anche scaricare una versione demo del sistema Kontur Standard con l'esempio descritto nell'articolo.

Lavorare con un 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 (è possibile anche MS Excel 2007, ma non è conveniente lavorarci e MS Excel 2003 ha funzionalità molto scarse)
3. Aprire MS Excel, avviare la procedura guidata per configurare una connessione al servizio analitico:

3.1 Specificare il nome o l'indirizzo IP del server OLAP corrente (a volte è necessario specificare il numero porta aperta, ad esempio, 192.25.25.102:80); l'autenticazione del dominio viene utilizzata:

3.2 Selezionare un database multidimensionale e un cubo analitico (se si dispone dei diritti di accesso al cubo):

3.3 Le impostazioni per la connessione al servizio analitico verranno salvate in un file odc sul tuo computer:

3.4 Selezionare il tipo di report (tabella pivot/grafico) e specificare la posizione per il suo posizionamento:

Se è già stata creata una connessione nella cartella di lavoro di Excel, è possibile riutilizzarla: dal menu principale "Dati" -> "Connessioni esistenti" -> selezionare la connessione in questa cartella di lavoro -> inserire la tabella pivot nella cella specificata.
4. Collegato correttamente al cubo, è possibile avviare l'analisi interattiva dei dati:

Quando si avvia l'analisi interattiva dei dati, è necessario determinare quale dei campi sarà coinvolto nella formazione di righe, colonne e filtri (pagine) della tabella pivot. In generale, la tabella pivot è tridimensionale e possiamo pensare alla terza dimensione come perpendicolare allo schermo e vediamo sezioni parallele al piano dello schermo e determinate da quale "pagina" è selezionata per la visualizzazione. È possibile filtrare trascinando gli attributi di dimensione appropriati nell'area dei filtri del report. Il filtraggio limita lo spazio del cubo, riducendo il carico sul server OLAP, quindi è preferibile installare prima i filtri necessari. Quindi inserire gli attributi di dimensione nelle aree di riga e colonna e le misure nell'area dati della tabella pivot.

Ogni volta che la tabella pivot cambia, un'istruzione MDX viene inviata automaticamente al server OLAP ei dati vengono restituiti quando vengono eseguiti. Più grande e complesso è il volume dei dati elaborati, indicatori calcolati, più lungo è il tempo di esecuzione della query. È possibile annullare l'esecuzione della richiesta premendo il tasto Fuga. Le ultime operazioni eseguite possono essere annullate (Ctrl+Z) o ripristinate (Ctrl+Y).

Di norma, per le combinazioni di attributi di dimensione utilizzate più di frequente, il cubo memorizza dati aggregati precalcolati, quindi il tempo di risposta di tali query è di alcuni secondi. Tuttavia, è impossibile calcolare tutte le possibili combinazioni di aggregazioni, poiché ciò può richiedere molto tempo e spazio di archiviazione. Richieste massicce di dati a livello di dettaglio possono richiedere notevoli risorse di elaborazione del server, quindi il loro tempo di esecuzione può essere lungo. Dopo aver letto i dati dalle unità disco, il server li inserisce nella cache della RAM, che consente l'esecuzione istantanea di tali richieste successive, poiché i dati verranno recuperati dalla cache.

Se ritieni che la tua richiesta verrà utilizzata frequentemente e il suo tempo di esecuzione non è soddisfacente, puoi contattare il servizio di supporto allo sviluppo analitico per ottimizzare l'esecuzione della richiesta.

Dopo aver posizionato la gerarchia nell'area righe/colonne, è possibile nascondere i singoli livelli:

Per gli attributi chiave(meno spesso - per gli attributi più in alto nella gerarchia) le dimensioni possono avere proprietà - caratteristiche descrittive che possono essere visualizzate sia nelle descrizioni comandi che come campi:

Se si desidera visualizzare più proprietà di campo contemporaneamente, è possibile utilizzare l'elenco di finestre di dialogo corrispondente:

Set definiti dall'utente

Excel 2010 introduce la possibilità di creare in modo interattivo set personalizzati (definiti dall'utente) dai membri della dimensione:

A differenza dei set creati e archiviati centralmente sul lato del cubo, i set personalizzati vengono archiviati localmente in una cartella di lavoro di Excel e possono essere utilizzati in seguito:

Gli utenti avanzati possono creare set utilizzando i costrutti MDX:

Impostazione delle proprietà della tabella pivot

La voce "Opzioni tabella pivot..." del menu contestuale (fare clic con il pulsante destro del mouse all'interno della tabella pivot) offre la possibilità di personalizzare la tabella pivot, ad esempio:
- Scheda "Output", opzione "Layout tabella pivot classica" - la tabella pivot diventa interattiva, i campi possono essere trascinati (Drag&Drop);
- Scheda "Output", parametro "Mostra elementi senza dati nelle righe": la tabella pivot visualizzerà righe vuote che non contengono alcun valore indicatore per gli elementi dimensione corrispondenti;
- Scheda "Layout e formato", parametro "Mantieni la formattazione della cella durante l'aggiornamento" - nella tabella pivot, puoi sovrascrivere e salvare il formato della cella durante l'aggiornamento dei dati;

Creazione di grafici pivot

Per una tabella pivot OLAP esistente, puoi creare un grafico pivot: torta, barra, istogramma, grafico, dispersione e altri tipi di grafici:

In questo caso, il grafico pivot verrà sincronizzato con la tabella pivot: quando modifichi la composizione di indicatori, filtri e dimensioni nella tabella pivot, anche la tabella pivot viene aggiornata.

Creazione di dashboard

Seleziona la tabella pivot originale, copiala negli appunti (Ctrl + C) e incolla la sua copia (Ctrl + V), in cui cambieremo la composizione degli indicatori:

Per gestire più tabelle pivot contemporaneamente, inseriamo uno slicer (nuova funzionalità disponibile a partire da MS Excel 2010). Colleghiamo il nostro Slicer alle tabelle pivot: fare clic con il pulsante destro del mouse all'interno dell'affettatrice, selezionare in menù contestuale Elemento "Connessioni alla tabella pivot...". Va notato che possono esserci più pannelli slicer che possono servire tabelle pivot su fogli diversi contemporaneamente, consentendo di creare dashboard coordinati.

I pannelli Slicer possono essere configurati: selezionare il pannello, quindi vedere "Dimensioni e proprietà...", "Impostazioni sezione", "Assegna macro" nel menu contestuale del tasto destro del mouse o nella voce "Opzioni" del menu principale. È quindi possibile impostare il numero di colonne per gli elementi (pulsanti) dell'affettatrice, le dimensioni dei pulsanti dell'affettatrice e del pannello, definire la combinazione di colori e lo stile di progettazione dell'affettatrice dal set esistente (o crearne uno personalizzato style), definire il titolo del proprio pannello, assegnare una macro di programma attraverso la quale è possibile espandere le funzionalità del pannello.

Esecuzione di una query MDX da Excel

Prima di tutto, è necessario eseguire l'operazione DRILLTHROUGH su qualche indicatore, ad es. scendere ai dati di dettaglio (i dati di dettaglio vengono visualizzati su un foglio separato) e aprire l'elenco dei collegamenti;
Apri le proprietà della connessione, vai alla scheda "Definizione";
Seleziona il tipo di comando predefinito e nel campo di testo del comando inserisci un pre-preparato MDX inchiesta;
Alla pressione del pulsante, dopo aver verificato la corretta sintassi della richiesta e la disponibilità di opportuni diritti di accesso, la richiesta verrà eseguita sul server e il risultato verrà presentato nel foglio corrente sotto forma di una normale tavola piatta.
È possibile visualizzare il testo della query MDX generata da Excel installando un componente aggiuntivo gratuito che fornisce anche altre funzionalità aggiuntive.

Traduzione in altre lingue

Il cubo analitico supporta la localizzazione in russo e lingua inglese e (se necessario, è possibile la localizzazione in altre lingue). Le traduzioni si applicano ai nomi di dimensioni, gerarchie, attributi, cartelle, misure, nonché elementi di singole gerarchie se sono presenti traduzioni per loro sul lato dei sistemi contabili / archiviazione dati. Per cambiare la lingua, apri le proprietà di connessione e aggiungi la seguente opzione nella stringa di connessione:
Proprietà estese="Locale=1033"
dove 1033 - Localizzazione inglese
1049 - Localizzazione russa

Estensioni aggiuntive di Excel per Microsoft OLAP

La capacità di lavorare con i cubi Microsoft OLAP aumenterà se si utilizzano estensioni aggiuntive, ad esempio OLAP PivotTable Extensions, grazie alle quali è possibile utilizzare la ricerca rapida per dimensione:

sito web 2011-01-11 16:57:00Z Ultima modifica: 2017-10-15 16:33:59Z Pubblico di età: 14-70