Förstå analys i realtid (OLAP). En introduktion till OLAP Grunderna att skriva en formel från grunden

Facebook

I en vanlig pivottabell lagras källdata på den lokala hårddisken. På så sätt kan du alltid hantera och omorganisera dem, även utan att ha tillgång till nätverket. Men detta gäller inte på något sätt för OLAP -pivottabeller. I OLAP -pivottabeller lagras cachen aldrig på den lokala hårddisken. Därför, omedelbart efter att du kopplat från det lokala nätverket, blir ditt pivottabell oanvändbart. Du kan inte flytta några fält i den.

Om du fortfarande behöver analysera OLAP -data efter att du kopplat bort från nätverket skapar du en offlinedatakub. En offlinedatakub är en separat fil som är en pivottabellcache och lagrar OLAP -data som visas efter att den har kopplats bort från det lokala nätverket. OLAP -data som kopieras till pivottabellen kan skrivas ut, på webbplatsen http://everest.ua beskrivs den i detalj.

För att skapa en datakub offline, skapa först en OLAP -pivottabell. Placera markören i pivottabellen och klicka på knappen OLAP -verktyg på fliken Verktygs sammanhang, som är en del av flikgruppen Pivottabellverktyg. Välj kommandot Offline OLAP (Figur 9.8).

Ris. 9.8. Skapa en offline datakub

Dialogrutan Offline OLAP Data Cube Settings visas. Klicka på knappen Skapa offline datafil. Du har startat guiden New Data Cube File. Klicka på knappen Nästa för att fortsätta proceduren.

Först måste du ange de dimensioner och nivåer som ska ingå i datakuben. I dialogrutan måste du välja data som ska importeras från OLAP -databasen. Tanken är att bara ange de dimensioner som kommer att behövas efter att datorn har kopplats bort från det lokala nätverket. Ju fler dimensioner du anger desto större blir offlinedatakuben.

Klicka på knappen Nästa för att gå vidare till nästa dialogruta i guiden. Det ger dig möjlighet att ange medlemmar eller dataobjekt som inte kommer att ingå i kuben. I synnerhet behöver du inte måttet Internetförsäljning-utökat belopp, så kryssrutan för det kommer att rensas i listan. En avmarkerad kryssruta indikerar att det angivna objektet inte kommer att importeras och tar extra utrymme på den lokala hårddisken.

I det sista steget anger du platsen och namnet på datakuben. I vårt fall kommer kubfilen att heta MyOfflineCube.cub och finns i Work -mappen.

Datakubfiler har tillägget .Valp

Efter en tid kommer Excel att spara offlinedatakuben i den angivna mappen. För att testa den, dubbelklicka på filen, som automatiskt genererar en Excel-arbetsbok som innehåller en pivottabell som är associerad med den valda datakuben. När du har skapat det kan du distribuera offlinedatakuben till alla intresserade användare som arbetar i offline -LAN -läge.

När du är ansluten till det lokala nätverket kan du öppna offlinedatakubfilen och uppdatera den, samt motsvarande datatabell. Huvudprincipen är att en fristående datakub endast används för att fungera när det lokala nätverket kopplas bort, men den måste uppdateras efter att anslutningen återställts. Att försöka uppdatera offlinedatakuben efter att kopplingen kopplats bort kommer att resultera i ett misslyckande.

Arbetar med offline kubfiler

En offline kubfil (. Cub) lagrar data i form av en OLAP (Online Analytical Processing) kub. Denna data kan representera en del av en OLAP -databas på en OLAP -server, eller den kan genereras oberoende av en OLAP -databas. Använd en offline kubfil för att fortsätta arbeta med pivottabell- och pivotdiagramrapporter när servern inte är tillgänglig eller när du är offline.

Säkerhetsanvisning: Var försiktig när du använder eller distribuerar en offline kubfil som innehåller konfidentiell eller personlig information. Det rekommenderas att du sparar data i en arbetsbok istället för en kubfil så att du kan styra åtkomsten till data med hjälp av Rights Management. Mer information finns i Information Rights Management in Office.

När du arbetar med en pivottabell eller pivotdiagramrapport baserad på OLAP -serverkälldata kan du använda offlinekubguiden för att kopiera källdata till en separat offlinekubfil på din dator. Dessa offline -filer kräver en OLAP -dataleverantör för att stödja den här funktionen, till exempel MSOLAP från Microsoft SQL Server Analysis Services installerat på datorn.

Notera: Skapandet och användningen av offlinekubfiler från Microsoft SQL Server Analysis Services styrs av villkoren och licensieringen av Microsoft SQL Server -installationen. Granska den relaterade SQL Server -versionens licensinformation.

Arbeta med den fristående kubguiden

För att skapa en offlinekubfil kan du välja en delmängd av data i en OLAP -databas med hjälp av offlinekubguiden och sedan spara den delmängden. Rapporten behöver inte inkludera alla fält som ingår i filen, inte heller behöver du välja något av dem och datafälten som finns tillgängliga i OLAP -databasen. För att hålla filen på ett minimum kan du bara inkludera de data som du vill ska visas i rapporten. Du kan utelämna alla dimensioner, och för de flesta typer av dimensioner kan du också utesluta information på lägre nivå och objekt på toppnivå som du inte vill visa. För alla objekt som du inkluderar sparas även de egenskapsfält som finns i databasen för dessa objekt i en offline -fil.

Tar data offline och återansluter den

För att göra detta måste du först skapa en pivottabell- eller pivotdiagramrapport baserat på servern databas och sedan skapa en offline kubfil från rapporten. Efter det kan du när som helst växla rapporten mellan serverdatabasen och offline -filen. Om du till exempel använder en bärbar dator för hem- och videoresor, anslut sedan datorn till nätverket igen.

Följande är de grundläggande stegen att ta för att arbeta offline med dina data och sedan återföra din data online.

Skapa eller öppna en pivottabell eller pivotdiagramrapport baserat på OLAP -data som du vill ha åtkomst till offline.

Skapa en offline kubfil på din dator. I kapitel Skapa en offline kubfil från en OLAP -serverdatabas(senare i denna artikel).

Koppla från nätverket och arbeta med den offline kubfilen.

Gå online och återanslut kubfilen offline. Kolla in avsnittet Återansluter kubfilen offline till OLAP -serverdatabasen(nedan i denna artikel).

Uppdaterar offlinekubfilen med nya data och återskapar offlinekubfilen. Kolla in avsnittet uppdatera och återskapa den offline kubfilen(senare i denna artikel).

BLOGG

Endast inlägg av hög kvalitet

Vad är Excel -pivottabeller och OLAP -kuber

Se videon för artikeln:

OLAPÄr engelska. online analytisk bearbetning, analytisk teknik för behandling av data i realtid. Enkelt uttryckt - en lagring med flerdimensionella data (Cube), ännu enklare - bara en databas från vilken du kan hämta data i Excel och analysera med hjälp av Excel -verktyget - Pivottabeller.

SvängbordÄr ett användargränssnitt för visning av flerdimensionella data. Med andra ord, en speciell typ av tabeller som kan användas för att göra nästan vilken rapport som helst.

För att göra det klart, låt oss jämföra "Normaltabell" med "Pivottabell"

Normalt bord:

Översiktstabell:

Huvudskillnaden SvängbordÄr närvaron av ett fönster " Pivottabellfältlista", Från vilket du kan välja de obligatoriska fälten och få valfri tabell automatiskt!

Hur man använder

Öppna Excel -filen som är ansluten till OLAP -kuben, till exempel "BIWEB":

Vad betyder det här och hur använder du det?

Dra de obligatoriska fälten för att till exempel få en tabell så här:

« Plustecken»Låt dig fördjupa dig i rapporten. I det här exemplet beskrivs "Varumärke" till "Förkortade namn" och "Kvartal" till "Månad", dvs. Så:

Analytiska funktioner i Excel (kubfunktioner)

Microsoft lägger hela tiden till nya funktioner i Excel när det gäller dataanalys och visualisering. Att arbeta med information i Excel kan representeras som relativt oberoende tre lager:

"Korrekt" organiserad källdata
matematik (logik) för databehandling
datapresentation

Ris. 1. Dataanalys i Excel: a) källdata, b) mäta i Power Pivot, c) instrumentpanel; för att förstora bilden, högerklicka på den och välj Öppna bilden i en ny flik

Ladda ner anteckning i Word- eller pdf -format, exempel i Excel -format

Kubfunktioner och pivottabeller

Pivottabeller är det enklaste och samtidigt mycket kraftfulla datapresentationsverktyget. De kan byggas på grundval av data som ingår: a) i ett Excel -kalkylblad, b) en OLAP -kub eller c) en Power Pivot -datamodell. I de två sista fallen, utöver pivottabellen, kan du använda analytiska funktioner (kubfunktioner) för att generera en rapport på ett Excel -ark. Pivottabeller är enklare. Kubernas funktioner är mer komplexa, men de ger mer flexibilitet, särskilt när det gäller utformning av rapporter, så de används ofta i instrumentpaneler.

Det som följer är kubformler och pivottabeller baserade på Power Pivot -modellen och, i några fall, på OLAP -kuber.

Ett enkelt sätt att få kubernas funktioner

När (om) du började lära dig VBA -kod, lärde du dig att det enklaste sättet att få koden är genom att spela in ett makro. Sedan kan du redigera koden, lägga till slingor, kontroller etc. På samma sätt är det enklaste sättet att få en uppsättning kubfunktioner att omvandla pivottabellen (fig. 2). Stå på valfri cell i pivottabellen, gå till fliken Analys, klicka på knappen Medel OLAP och tryck på Konvertera till formler.

Ris. 2. Konvertera ett pivottabell till en uppsättning kubfunktioner

Siffrorna bevaras, inte värden, utan formler som hämtar data från Power Pivot -datamodellen (figur 3). Du kan formatera den resulterande tabellen. Inklusive kan du radera och infoga rader och kolumner i tabellen. Skivan finns kvar, och den påverkar data i tabellen. När originaldata uppdateras uppdateras även siffrorna i tabellen.

Ris. 3. Tabell baserad på kubformler

CUBEVALUE () -funktion

Detta är kanske kubens huvudfunktion. Det motsvarar området Värdena svängbord. CUBEVALUE hämtar data från en Power Pivot -kub eller modell och visar den utanför pivottabellen. Detta innebär att du inte är begränsad till pivottabellens gränser och kan skapa rapporter med otaliga möjligheter.

Skriver en formel från grunden

Du behöver inte konvertera den färdiga pivottabellen. Du kan skriva vilken kubformel som helst från grunden. Till exempel anges följande formel i cell C10 (fig. 4):

Ris. 4. Funktionen CUBEVALUE () i cell C10 returnerar cykelförsäljning för alla år, precis som i pivottabellen

Ett litet trick. För att göra det lättare att läsa kubformlerna är det önskvärt att endast ett argument placeras på varje rad. Du kan krympa Excel -fönstret. För att göra detta, klicka på ikonen Minimera till fönstret finns i skärmens övre högra hörn. Och justera sedan fönstrets storlek horisontellt. Ett alternativ är att tvinga formeltexten till en ny rad. För att göra detta, placera markören i formelfältet på den plats där du vill överföra och tryck på Alt + Enter.

Ris. 5. Minimera fönstret

Funktionssyntax CUBEVALUE ()

Excel Hjälp är helt korrekt och helt värdelös för nybörjare:

CUBEVALUE (anslutning; [member_expression1]; [member_expression2]; ...)

Förbindelse- obligatoriskt argument; en textsträng som representerar namnet på anslutningen till kuben.

Medlemsuttryck- valfritt argument; en textsträng som representerar ett MDX -uttryck som returnerar ett objekt eller en tupel i en kub. Dessutom kan "member_expression" vara en uppsättning som definieras med CUBESET -funktionen. Använd member_expression som en bit för att definiera den del av kuben som du vill returnera ett aggregerat värde för. Om inget mått anges i member_expression används standardmåttet för den kuben.

Innan vi fortsätter med att förklara syntaxen för CUBEVALUE -funktionen, några ord om kuber, datamodeller och den kryptiska tupel.

Lite information om OLAP -kuber och Power Pivot -datamodeller

OLAP -datakuber ( O n l ine A nalytisk P rocessing - operativ dataanalys) utvecklades specifikt för analytisk bearbetning och snabb extrahering av data från dem. Föreställ dig ett tredimensionellt utrymme där tidsperioder, städer och varor ritas ut längs axlarna (figur 5a). I noderna i ett sådant koordinatnät finns värdena för olika mått: försäljningsvolym, vinst, kostnader, antal sålda enheter etc. Tänk dig nu att det finns tiotals, eller till och med hundratals dimensioner ... och där är också många åtgärder. Detta kommer att vara den flerdimensionella kuben OLAP. Att skapa, konfigurera och hålla OLAP -kuber uppdaterade är IT -proffs verksamhet.

Ris. 5a. OLAP 3D -kub

Excel -analysformler (kubformler) extraherar axnamnen (t.ex. Tid), namn på element på dessa axlar (augusti, september), mätvärden vid korsningen av koordinater. Det är denna struktur som gör att kubbaserade pivottabeller och kubformler kan vara så flexibla och anpassningsbara efter användarens behov. Excel-kalkylbladbaserade pivottabeller använder inte mått, så de är inte lika flexibla för dataanalysändamål.

Power Pivot är en relativt ny funktion i Microsoft. Det är en inbyggd Excel och lite oberoende miljö med ett välbekant gränssnitt. Power Pivot är mycket bättre än vanliga pivottabeller. Att utveckla kuber i Power Pivot är dock relativt enkelt, och viktigast av allt, det kräver inte en IT -proffs. Microsoft implementerar sin paroll: "Business Intelligence - till massorna!". Även om Power Pivot -modeller inte är 100% kuber, kan de också kallas kuber (se Mark Moores introduktionskurs. Power Pivot och den större upplagan av Rob Colley. DAX Formulas for Power Pivot för mer information).

Huvudkomponenterna i en kub är dimensioner, hierarkier, nivåer, medlemmar (eller medlemmar) och mått. Mätning - huvudkarakteristiken för de analyserade data. Till exempel produktkategori, tidsperiod, försäljningsgeografi. En dimension är något som vi kan placera på en av axlarna i svängbordet. Varje dimension, förutom unika värden, innehåller en medlem som sammanställer alla medlemmar i den dimensionen.

Mätningar ritas ut baserat på hierarkier... Till exempel kan en produktkategori delas upp i underkategorier, sedan i modeller och slutligen i produktnamn (figur 5b). Hierarkin låter dig skapa sammanfattningsdata och analysera den på olika nivåer av strukturen. I vårt exempel hierarkin Kategori innehåller 4 Nivå.

Elementen(enskilda medlemmar) finns på alla nivåer. Till exempel på kategorinivå finns det fyra element: Tillbehör, Cyklar, Kläder, Komponenter. Andra nivåer har sina egna element.

ÅtgärderÄr beräknade värden som försäljning. Kubiska mått lagras i sin egen dimension som kallas (se figur 9 nedan). Åtgärder har inga hierarkier. Varje mått beräknar och lagrar ett värde för alla dimensioner och alla medlemmar och skärs ut baserat på vilka måttelement vi placerar på axeln. De säger också vilka koordinater som kommer att ställas in, eller vilket filterkontext som kommer att ställas in. Till exempel i fig. 5а i varje liten kub beräknas samma mått - vinst. Och värdet som returneras av måttet beror på koordinaterna. Till höger i figur 5a visas att Vinst (i tre koordinater) i Moskva i oktober på äpplen = 63 000 r. Måttet kan tolkas som en av dimensionerna. Till exempel i fig. 5a istället för axeln Varor, platsaxel Åtgärder med element Försäljningsvolym, Vinst, Enheter sålda... Då kommer varje cell att ha något värde, till exempel Moskva, september, försäljningsvolym.

Tupel- flera element av olika dimensioner, som anger koordinater längs kubens axlar, där vi beräknar måttet. Till exempel i fig. 5a Tupel= Moskva, oktober, äpplen. Också en giltig tupel är Perm, äpplen. En annan är äpplen, augusti. Dimensioner som inte ingår i en tupel finns implicit i en tupel och representeras av en standardmedlem. Således definieras alltid en cell i ett flerdimensionellt utrymme av en komplett uppsättning koordinater, även om några av dem utelämnas i tupeln. Du kan inte inkludera två medlemmar av samma dimension i en tupel, syntaxen tillåter inte. Till exempel en ogiltig motorcade Moskva och Perm, äpplen. För att implementera en sådan MDX behöver du en uppsättning av två tupler: Moskva och äpplen + Perm och äpplen.

Uppsättning element- flera element av en dimension. Till exempel äpplen och päron. En uppsättning tupler- flera tupler, som var och en består av samma dimensioner i samma sekvens. Till exempel en uppsättning med två tupler: Moskva, äpplen och Perm, bananer.

Automatisk komplettering för att hjälpa

Låt oss gå tillbaka till syntaxen för CUBEVALUE -funktionen. Låt oss använda automatisk komplettering. Börja skriva formeln i cellen:

Excel föreslår alla tillgängliga anslutningar i Excel -arbetsboken:

Ris. 6. Anslutningen till Power Pivot -datamodellen kallas alltid ThisWorkbookDataModel

Ris. 7. Anslutningar till kuber

Låt oss fortsätta mata in formeln (i vårt fall, för datamodellen):

Autoslutförande föreslår alla tillgängliga tabeller och mått på datamodellen:

Ris. 8. Tillgängliga element på första nivån - tabellnamn och uppsättning mått (markerade)

Välj ikonen Åtgärder... Sätt en poäng:

CUBEVALUE ("ThisWorkbookDataModel"; ".

Automatisk komplettering föreslår alla tillgängliga åtgärder:

Ris. 9. Tillgängliga andra nivåposter i måttuppsättningen

Välj ett mått. Lägg till citattecken, stäng parentes, tryck på Enter.

CUBEVALUE ("ThisWorkbookDataModel"; ".")

Ris. 10. Formel CUBEVALUE i en Excel -cell

På samma sätt kan du lägga till ett tredje argument i din formel:

VBA i Excel Excel.PivotTable -objekt och Arbeta med pivottabeller och OLAP -kuber i Excel

10.8 Arbeta med pivottabeller (PivotTable -objekt)

Excel.PivotTable -objekt, som programmatiskt arbetar med pivottabeller och OLAP -kuber i Excel med hjälp av VBA, PivotCache -objekt, skapar en pivottabellayout

Under driften av de flesta företag samlas så kallade rådata om aktiviteter. Till exempel, för en handlare kan data om försäljning av varor ackumuleras - för varje köp separat, för mobilföretag - statistik över belastningen på basstationer etc. Mycket ofta behöver företagsledningen analytisk information som genereras på grundval av rå information - till exempel för att beräkna varje produkttyps bidrag till företagets inkomst eller kvaliteten på tjänster inom ett visst område station. Sådan information är mycket svår att extrahera från rå information: du måste utföra mycket komplexa SQL -frågor som tar lång tid och ofta stör aktuellt arbete. Därför konsolideras numera allt oftare rådata först i datalagret - Data Warehouse och sedan - i OLAP -kuber, vilket är mycket bekvämt för interaktiv analys. Det enklaste sättet att föreställa sig OLAP -kuber är flerdimensionella tabeller, där det kan finnas många dimensioner istället för de två vanliga dimensionerna (kolumner och rader, som i vanliga tabeller). Termen "cutaway" används vanligtvis för att beskriva dimensioner i en kub. Till exempel kan marknadsavdelningen behöva information om tid, regioner, produkttyper, försäljningskanaler etc. Med hjälp av kuber (i motsats till vanliga SQL -frågor) är det mycket enkelt att få svar på frågor som ”hur många varor av denna typ såldes under fjärde kvartalet förra året i nordvästra regionen genom regionala distributörer.

Naturligtvis kan sådana kuber inte skapas i vanliga databaser. OLAP -kuber kräver specialiserad programvara. SQL Server levereras med en OLAP -databas från Microsoft som heter Analysis Services. Det finns OLAP -lösningar från Oracle, IBM, Sybase, etc.

För att arbeta med sådana kuber är en speciell klient inbyggd i Excel. På ryska heter det Svängbord(på den grafiska skärmen är den tillgänglig via menyn Data -> Svängbord), och på engelska - Svängbord... Följaktligen kallas objektet som den här klienten representerar en pivottabell. Det bör noteras att han vet hur man arbetar inte bara med OLAP -kuber, utan också med vanliga data i Excel -tabeller eller databaser, men många möjligheter går förlorade.

Pivottabellen och pivottabellobjektet är Panorama Software -produkter som förvärvades av Microsoft och integrerades i Excel. Därför skiljer sig arbetet med PivotTable -objektet något från att arbeta med andra Excel -objekt. Att gissa vad som behöver göras är ofta svårt. Därför rekommenderas att aktivt använda makroinspelaren för att få tips. Samtidigt, när de arbetar med pivottabeller, måste användarna ofta utföra samma repetitiva operationer, så automatisering är nödvändig i många situationer.

Hur ser det ut att programmatiskt arbeta med ett svängbord?

Det första vi behöver göra är att skapa ett PivotCache -objekt som ska representera uppsättningen poster som hämtats från OLAP -källan. Mycket konventionellt kan detta PivotCache -objekt jämföras med en QueryTable. Endast ett PivotCache -objekt kan användas för varje pivottabell. PivotCache -objektet skapas med hjälp av Add () -metoden för PivotCaches -samlingen:

Dim PC1 som PivotCache

Ange PC1 = ActiveWorkbook.PivotCaches.Add (xlExternal)

PivotCaches är en standardsamling, och av de metoder som förtjänar detaljerad övervägande kan endast Add () -metoden nämnas i den. Denna metod tar två parametrar:

Källtyp- krävs, anger vilken typ av datakälla för pivottabellen. Du kan ange om du vill skapa en pivottabell baserat på ett intervall i Excel, data från en databas, i en extern datakälla, en annan pivottabell och så vidare. I praktiken är det vanligtvis meningsfullt att använda OLAP endast när det finns mycket data - därför behöver du en specialiserad extern lagring (till exempel Microsoft Analysis Services). I denna situation väljs xlExternal.
Källdata- obligatoriskt i alla fall, utom de när värdet på den första parametern är xlExternal. Strängt taget definierar den dataintervallet på grundval av vilket pivottabellen kommer att skapas. Tar vanligtvis ett Range -objekt.

Nästa uppgift är att konfigurera parametrarna för PivotCache -objektet. Som redan nämnts är detta objekt mycket likt QueryTable och har en mycket liknande uppsättning egenskaper och metoder. Några av de viktigaste egenskaperna och metoderna:

ADOC -anslutning- möjligheten att returnera ett ADO -anslutningsobjekt som automatiskt skapas för att ansluta till en extern datakälla. Används för ytterligare konfiguration av anslutningsegenskaper.
Förbindelse- fungerar på exakt samma sätt som egenskapen med samma namn på QueryTable -objektet. Den kan acceptera en anslutningssträng, ett färdigt Recordset-objekt, en textfil, en webbförfrågan. filen Microsoft Query. Oftast, när man arbetar med OLAP, skrivs en anslutningssträng direkt (eftersom det är meningslöst att ta emot ett Recordset -objekt, till exempel för att ändra data - OLAP -datakällor är nästan alltid skrivskyddade). Till exempel kan inställningen av denna egenskap för anslutning till en Foodmart -databas (Analysis Services -exempeldatabas) på en LONDON -server se ut så här:

PC1.Connection = "OLEDB; Provider = MSOLAP.2; Datakälla = LONDON1; Initial Catalog = FoodMart 2000"

egenskaper CommandType och CommandText beskriv på liknande sätt vilken typ av kommando som skickas till databasservern och texten i själva kommandot. Till exempel, för att komma åt försäljningskuben och få den helt i cacheminnet på klienten, kan du använda kod som

fast egendom LocalConnection låter dig ansluta till en lokal kub (* .cub -fil) skapad av Excel. Naturligtvis rekommenderas det inte att använda sådana filer för att arbeta med "produktions" datamängder - bara för att skapa layouter etc.
fast egendom MemoryUsed returnerar mängden RAM som används av PivotCache. Om en pivottabell baserad på denna PivotCache ännu inte har skapats eller öppnats, returnerar 0. Kan användas för kontroller om din applikation körs på svaga klienter.
fast egendom OLAP returnerar True om PivotCache är ansluten till en OLAP -server.
OptimizeCache- möjligheten att optimera cachestrukturen. Initial laddning av data kommer att ta längre tid, men då kan hastigheten öka. För OLE DB fungerar inte källor.

Resten av egenskaperna för PivotCache -objektet är desamma som för QueryTable -objektet och kommer därför inte att diskuteras här.

Huvudmetoden för PivotCache -objektet är metoden CreatePivotTable (). Med denna metod utförs nästa steg - skapandet av ett pivottabell (PivotTable -objekt). Denna metod tar fyra parametrar:

TableDestinationär den enda nödvändiga parametern. Tar in ett Range -objekt i det övre vänstra hörnet av vilket pivottabellen kommer att placeras.
Tabellnamn- pivottabellens namn. Om det inte anges genereras namnet på typen "Pivottabell1" automatiskt.
ReadData- om den är inställd på True kommer hela kubens innehåll automatiskt att placeras i cachen. Du måste vara mycket försiktig med denna parameter, eftersom dess felaktiga användning dramatiskt kan öka belastningen på klienten.
DefaultVersion- den här egenskapen är vanligtvis inte specificerad. Bestämmer versionen av pivottabellen som skapas. Den senaste versionen används som standard.

Att skapa en pivottabell i den första cellen i det första bladet i arbetsboken kan se ut så här:

PC1.CreatePivotTable Range ("A1")

Vi har skapat en pivottabell, men omedelbart efter skapandet är den tom. Det ger fyra områden där du kan placera fält från källan (på den grafiska skärmen kan allt detta konfigureras antingen med hjälp av fönstret Pivottabellfältlista- den öppnas automatiskt eller med knappen Layout på den sista skärmen i guiden Skapa pivottabell):

kolumnområde- den innehåller de dimensioner ("cut" där data kommer att analyseras), vars medlemmar är färre;
linjeområde- dessa dimensioner med fler medlemmar;
sidområdet- de mätningar som du bara behöver filtrera för (till exempel visa data endast för en viss region eller bara för ett visst år);
dataområde- Strängt taget den centrala delen av bordet. Dessa numeriska data (till exempel mängden försäljning) som vi analyserar.

Det är svårt att lita på att användaren ska placera objekt korrekt på alla fyra områden. Dessutom kan det ta lite tid. Därför är det ofta nödvändigt att programmatiskt ordna data i pivottabellen. Denna operation utförs med hjälp av CubeField -objektet. Huvudegenskapen för detta objekt är Orientering, det avgör var detta eller det där fältet ska placeras. Låt oss till exempel placera dimensionen Kunder i kolumnområdet:

PT1.CubeFields (""). Orientering = xlColumnField

Därefter - mätning av tid i strängarnas område:

PT1.CubeFields (""). Orientering = xlRowField

Mät sedan produkten i sidområdet:

PT1.CubeFields ("") .Orientation = xlPageField

Och slutligen måttet (numeriska data för analys) Enhetsförsäljning:

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

Problem med analys, OLAP, datalager är av allt större intresse bland ryska IT -specialister. Hittills, i vår datorpress och på Internet, har många bra, akademiskt material om detta ämne, inklusive inledande, publicerats. Vi uppmärksammar en artikel där vi medvetet försöker förklara OLAP "på fingrarna", med hjälp av ett specifikt exempel. Praktiken visar att en sådan förklaring är nödvändig för vissa IT -proffs och särskilt för slutanvändare.

Så, OLAP * 1 i den första approximationen, "på fingrarna", kan definieras som ett speciellt sätt att analysera data och få rapporter. Dess essens är att förse användaren med en flerdimensionell tabell som automatiskt sammanfattar data i olika avsnitt och låter dig interaktivt hantera beräkningar och rapportformuläret. Den här artikeln kommer att prata om tekniken och grundläggande verksamheten för OLAP med hjälp av exemplet på att analysera fakturor från en livsmedelsgrossist.

*1. OLAP - On -line analytisk bearbetning, dataanalys online.

Som ett verktyg kommer vi att överväga ett OLAP -system av den enklaste och billigaste klassen - OLAP -klient * 1. Som ett exempel valdes den enklaste produkten från antalet OLAP -klienter - "Kontur Standard" av Intersoft Lab. (För tydlighetens skull, senare i artikeln, kommer allmänt accepterade OLAP-termer att markeras med fet stil och åtföljas av deras engelskspråkiga motsvarigheter.)

*1. Mer information om klassificering av OLAP-system beskrivs i artikeln "OLAP Made in Russia" i PC Week / RE, №3 / 2001.

Så låt oss börja arbeta med systemet. Först måste du beskriva datakällan - sökvägen till tabellen och dess fält. Det är upp till användaren som känner till den fysiska implementeringen av databasen. För slutanvändare översätter det namnet på tabellen och dess fält till domäntermer. Bakom "datakällan" finns en lokal tabell, tabell eller SQL Server -vy eller lagrad procedur.

Mest troligt, i en viss databas, lagras fakturor inte i en, utan i flera tabeller. Dessutom kanske vissa av fälten eller posterna inte används för analys. Därför skapas ytterligare ett urval (resultatuppsättning eller fråga) där följande är konfigurerade: algoritmen för att sammanfoga tabeller med nyckelfält, filtreringsförhållanden och en uppsättning returnerade fält. Låt oss namnge vårt urval "Fakturor" och sätta alla fält i datakällan "Fakturor" i det. Således döljer IT -proffsen, som skapar det semantiska lagret, den fysiska implementeringen av databasen från slutanvändaren.

Därefter konfigureras OLAP -rapporten. Detta kan göras av en ämnesexpert. Först delas platta samplingsfält in i två grupper - fakta (fakta eller mått) och dimensioner (dimensioner). Fakta är siffror och dimensioner är "nedskärningar" som sammanfattar fakta. I vårt exempel kommer måtten att vara: "Region", "Stad", "Kund", "Produkt", "Datum", och det kommer att finnas ett faktum - "Belopp" -fältet på fakturan. Faktum är att en eller flera aggregeringsalgoritmer måste väljas. OLAP kan inte bara sammanfatta, utan också att utföra mer komplexa beräkningar, upp till statistisk analys. Om du väljer flera aggregeringsalgoritmer genereras virtuella, beräknade fakta. I exemplet väljs en aggregeringsalgoritm - "Summa".

En särskild egenskap hos OLAP -system är generering av dimensioner och data för äldre tidsperioder från datumet och automatisk beräkning av totalsummor för dessa perioder. Låt oss välja perioderna "År", "Kvartal" och "Månad", medan det inte kommer att finnas någon data för varje dag i rapporten, men de genererade dimensionerna "År", "Kvartal" och "Månad" visas. Låt oss kalla rapporten ”Försäljningsanalys” och spara den. Arbetet med att skapa gränssnittet för den analytiska applikationen är över.

Nu, genom att starta detta gränssnitt dagligen eller månadsvis, kommer användaren att se en tabell och en graf där fakturor sammanfattas av varor, kunder och perioder.

För att göra manipulationer med data intuitiva är verktygen för att hantera en dynamisk tabell elementen i själva tabellen - dess kolumner och rader. Användaren kan flytta dem, ta bort, filtrera och utföra andra OLAP -operationer. I det här fallet beräknar tabellen automatiskt nya delsummor och slutliga totalsummor.

Till exempel, genom att dra (operation "flytta") kolumnen "Produkt" till första plats, får vi en jämförelsesrapport - "Jämförelse av försäljning av produkter för året". För att samla in data under ett år drar du bara kolumnerna "Kvartal" och "Månad" högst upp i tabellen - "området med inaktiva dimensioner". Dimensionerna "Kvartal" och "Månad", som överförs till detta område, kommer att stängas (operation "stäng dimension"), det vill säga uteslutet från rapporten; fakta kommer att summeras för året. Trots att måtten är stängda kan de användas för att specificera specifika år, kvartal och månader för att filtrera data (operation "filter").

För större klarhet, låt oss ändra typen av graf som illustrerar OLAP -tabellen och dess plats på skärmen.

Genom att gräva i data ("drill down" -operation) kan vi få mer detaljerad information om försäljningen av produkten vi är intresserade av. Genom att klicka på "+" -tecknet mittemot "Kaffe" -produkten ser vi försäljningsvolymen i regioner. Efter att ha öppnat Uralregionen kommer vi att få försäljningsvolymerna i samband med städerna i Uralregionen, genom att fördjupa oss i uppgifterna om Jekaterinburg, kommer vi att kunna se uppgifterna om grossistköpare av denna stad.

Du kan också använda öppna mätningar för att ställa in filter. För att jämföra dynamiken i försäljningen av godis i Moskva och Jekaterinburg, låt oss sätta filter på måtten "Produkt" och "Stad".

Stäng onödiga mätningar och välj "Line" -grafttyp. På den resulterande grafen kan du spåra dynamiken i försäljningen, bedöma säsongsvariationer och förhållandet mellan fall och tillväxt i försäljning av varor i olika städer.

Således såg vi till att OLAP -tekniken gör att användaren kan släppa dussintals olika typer av rapporter från ett gränssnitt och manipulera en dynamisk OLAP -tabell med musen. Uppgiften för programmeraren som äger ett sådant verktyg är inte rutinkodning av rapporteringsformulär, utan konfigurationen av OLAP -klienten för databaser. Samtidigt är metoderna för hantering av rapporten intuitiva för slutanvändaren.

OLAP är verkligen en naturlig fortsättning och utveckling av idén med kalkylblad. I huvudsak är OLAP: s visuella gränssnitt också ett kalkylblad, men utrustat med en kraftfull beräkningsmotor och en speciell standard för att presentera och manipulera data. Vissa OLAP-klienter implementeras dessutom som tillägg till MS Excel. Därför behärskar en miljonstark tjänstemannearmé, som tryggt behärskar kalkylblad, mycket snabbt OLAP-verktyg. För dem är detta en "sammetrevolution" som ger nya möjligheter, men inte förknippat med behovet av att omskola sig.

Om läsaren, efter att ha läst denna artikel, inte tappat intresset för OLAP, kan han hänvisa till det material som nämns i början. Samlingar av sådant material läggs ut på ett antal webbplatser på Internet, inklusive Intersoft -labbet - www.iso.ru. Från det kan du också ladda ner en demoversion av Kontur Standard -systemet med exemplet som beskrivs i artikeln.

Klient -OLAP -verktyg är applikationer som beräknar och visar aggregerade data (summor, genomsnitt, max- eller minimivärden), medan själva aggregerade data cachas i adressutrymmet för OLAP -verktyget.

Om källdata finns i skrivbordet DBMS utförs beräkningen av aggregerad data av själva OLAP -verktyget. Om källan till den ursprungliga datan är en server -DBMS skickar många av OLAP -klientverktygen SQL -frågor som innehåller GROUP BY -satsen till servern, och som ett resultat får de de aggregerade data som beräknats på servern.

Som regel implementeras OLAP -funktionalitet i statistiska databehandlingsverktyg (bland produkterna i denna klass är StatSoft- och SPSS -produkter spridda på den ryska marknaden) och i vissa kalkylblad. I synnerhet har Microsoft Excel flerdimensionella analysverktyg. Med den här produkten kan du skapa och spara som en liten lokal flerdimensionell OLAP -kub och visa dess 2D- eller 3D -sektioner.

Tillägg till Microsoft Office Data Extraction and Processing är en uppsättning funktioner som ger tillgång till datauttag och bearbetningsfunktioner för Microsoft Office-applikationer och därigenom möjliggör prediktiv analys på en lokal dator. Med algoritmerna för dataextraktion och manipulation inbyggda i Microsoft SQL Server -plattformstjänster som finns tillgängliga inom Microsoft Office -applikationsmiljön kan affärsanvändare enkelt extrahera värdefull information från komplexa datamängder med bara några klick. Tillägg för extraktion och manipulation av kontorsdata gör det möjligt för slutanvändare att utföra analyser direkt i Microsoft Excel och Microsoft Visio.

Microsoft Office 2007 innehåller tre separata OLAP -komponenter:

Data Extraction Client for Excel låter dig skapa och hantera SSAS-baserade dataxtraktions- och manipulationsprojekt från Excel 2007;
Med Excel-kalkylarkanalysverktyg kan du använda SSAS inbyggda extraktions- och bearbetningsfunktioner för att analysera data som lagras i Excel-kalkylblad.
Datautvinning och manipulationsmallar för Visio låter dig visualisera beslutsträd, regressionsträd, klusterdiagram och beroendenätverk i Visio -diagram.

Tabell 1.1. Oracle OLAP och affärsanalysprodukter

Typ av medel	Produkt

Det första gränssnittet för pivottabeller, även kallade pivottrapporter, ingick i Excel 1993 (version av Excel 5.0). Trots många användbara funktioner används den knappt av de flesta Excel -användare. Även erfarna användare menar ofta med termen ”sammanfattande rapport” något byggt med komplexa formler. Låt oss försöka popularisera användningen av pivottabeller i ekonomernas dagliga arbete. Artikeln diskuterar de teoretiska grunderna för att skapa sammanfattande rapporter, ger praktiska rekommendationer för deras användning och ger också ett exempel på datatillgång baserat på flera tabeller.

Multivariata dataanalystermer

De flesta ekonomer har hört begreppen "flerdimensionell data", "virtuell kub", "OLAP -teknik" och så vidare. Men med ett detaljerat samtal brukar det visa sig att nästan alla inte riktigt vet vad det handlar om. Det vill säga människor betyder något komplext och vanligtvis inte relevant för deras dagliga aktiviteter. I själva verket är detta inte fallet.

Flerdimensionella data, mätningar

Det är säkert att säga att ekonomer nästan ständigt står inför flerdimensionella data, men försöker representera det i en fördefinierad form med hjälp av kalkylblad. Multidimensionalitet betyder här möjligheten att ange, visa eller analysera samma information med en förändring av utseende, användning av olika grupper och datasorter. Till exempel kan en försäljningsplan analyseras enligt följande kriterier:

typer eller grupper av varor;
varumärken eller produktkategorier;
perioder (månad, kvartal, år);
köpare eller grupper av köpare;
försäljningsregioner
etc.

Var och en av ovanstående kriterier när det gäller multivariat dataanalys kallas "dimension". Vi kan säga att en mätning kännetecknar information för en viss uppsättning värden. En speciell typ av mätning av flerdimensionell information är "data". I vårt exempel kan data för försäljningsplanen vara:

försäljningsvolym;
Säljande pris;
individuell rabatt
etc.

I teorin kan data också vara en standarddimension av flerdimensionell information (till exempel kan du gruppera data efter försäljningspris), men vanligtvis är data fortfarande en speciell typ av värde.

Således kan vi säga att i praktiskt arbete använder ekonomer två typer av information: flerdimensionell data ( faktiska och planerade nummer, med många egenskaper) och referensböcker (egenskaper eller mätningar av data).

OLAP

Förkortningen OLAP (online analytisk bearbetning) översätts bokstavligen till "analytisk bearbetning i realtid." Definitionen är inte särskilt specifik, nästan vilken rapport som helst av någon mjukvaruprodukt kan sammanfattas under den. I betydelsen OLAP betyder det en teknik för att arbeta med specialrapporter, inklusive programvara, för att erhålla och analysera flerdimensionella strukturerade data. En av de populära mjukvaruprodukter som implementerar OLAP -teknik är SQL Server Analysis Server. Vissa anser till och med av misstag att det är den enda representanten för mjukvaruimplementeringen av detta koncept.

Virtuell datakub

"Virtuell kub" (flerdimensionell kub, OLAP -kub) är en speciell term som myntas av vissa specialiserade mjukvaruleverantörer. OLAP -system förbereder och lagrar vanligtvis data i sina egna strukturer, och speciella analysgränssnitt (till exempel Excel -pivotrapporter) får tillgång till data från dessa virtuella kuber. Samtidigt är användningen av en sådan dedikerad lagring inte alls nödvändig för behandling av flerdimensionell information. I allmänhet, virtuell kub- detta är en uppsättning speciellt optimerade flerdimensionella data som används för att skapa sammanfattande rapporter. Det kan erhållas både genom specialiserade mjukvaruverktyg och genom enkel åtkomst till databastabeller eller någon annan källa, till exempel en Excel -tabell.

Svängbord

Ett pivottabell är ett användargränssnitt för visning av flerdimensionella data. Med detta gränssnitt kan du gruppera, sortera, filtrera och ändra platsen för data för att få olika analytiska prover. Rapporten uppdateras på ett enkelt sätt i användargränssnittet, data aggregeras automatiskt enligt de angivna reglerna och ingen ytterligare eller upprepad inmatning av information krävs. Gränssnittet Excel PivotTable är kanske den mest populära mjukvaruprodukten för att arbeta med flerdimensionella data. Den stöder både externa datakällor (OLAP -kuber och relationsdatabaser) och interna kalkylbladsomfång som datakälla. Från och med version 2000 (9.0) stöder Excel också en grafisk form för visning av flerdimensionella data - ett pivottabell (Pivot Chart).

Med Excel PivotTable -gränssnitt kan du placera måtten på flerdimensionella data i ett område i ett kalkylblad. För enkelhetens skull kan du tänka på ett pivottabell som en rapport som ligger ovanpå en rad celler (i själva verket finns det en viss bindning av cellformat till pivottabellens fält). En pivottabell i Excel har fyra områden för visning av information: filter, kolumner, rader och data. Datamåtten heter pivottabellfält... Dessa fält har sina egna egenskaper och visningsformat.

Återigen vill jag uppmärksamma er på att Excel -pivottabellen endast är avsedd för dataanalys utan möjlighet att redigera information. Närmare i betydelsen skulle den allestädes närvarande användningen av termen "Pivot Report" vara, och så kallades detta gränssnitt fram till 2000. Men av någon anledning, i efterföljande versioner, övergav utvecklarna det.

Redigera pivottabeller

Enligt definitionen innebär OLAP -tekniken i princip inte möjligheten att ändra källdata när man arbetar med rapporter. Ändå har det skapats en hel klass mjukvarusystem på marknaden som implementerar både analys och direkt redigering av data i flerdimensionella tabeller. I grund och botten är sådana system inriktade på att lösa budgetproblem.

Med hjälp av inbyggda Excel-automatiseringsverktyg kan du lösa många icke-standardiserade uppgifter. Ett exempel på implementering av redigering för Excel -pivottabeller baserat på kalkylbladdata finns på vår webbplats.

Förbereda flerdimensionella data

Låt oss gå till den praktiska användningen av pivottabeller. Låt oss försöka analysera försäljningsdata i olika riktningar. Fil pivottableexample.xls består av flera ark. Ark Exempel innehåller grundläggande information om försäljning under en viss period. För enkelhetens skull analyserar vi den enda numeriska indikatorn - försäljningsvolymen i kg. Det finns följande viktiga datadimensioner: produkt, kund och transportör (rederi). Dessutom finns det flera ytterligare dimensioner av data som är kännetecknande för en produkt: typ, märke, kategori, leverantör och kund: typ. Dessa data samlas på referensbladet. I praktiken kan det finnas många fler sådana mätningar.

Ark Exempel innehåller ett standarddataanalysverktyg - AutoFilter. När man tittar på exemplet med att fylla i tabellen är det uppenbart att uppgifterna om försäljning efter datum (de är ordnade i kolumner) lämpar sig för normal analys. Dessutom kan du med hjälp av en autofilter försöka sammanfatta data genom kombinationer av ett eller flera nyckelkriterier. Det finns absolut ingen information om märken, kategorier och typer. Det är inte möjligt att gruppera data med automatisk sammanfattning med en specifik nyckel (till exempel av kunder). Dessutom är datumuppsättningen fast, och det kommer inte att vara möjligt att se sammanfattningsinformationen för en viss period, till exempel 3 dagar, med automatiska medel.

I allmänhet är förekomsten av en fördefinierad datumplats i detta exempel tabellens största nackdel. Genom att ordna datumen i kolumner, fördefinierade vi typ tabellens dimension och berövar oss själva från möjligheten att använda analys med hjälp av pivottabeller.

Först måste du bli av med denna nackdel - dvs. ta bort den fördefinierade platsen för en av dimensionerna för originaldata. Exempel på ett giltigt tabellblad Försäljning.

Tabellen har formen av en informationsinmatningslogg. Här är datum en kamratdimension av data. Det bör också noteras att för efterföljande analys i pivottabeller är den relativa positionen för raderna relativt varandra (med andra ord sortering) helt likgiltig. Registreringar i relationsdatabaser har dessa egenskaper. Det är på analysen av stora volymer databaser som gränssnittet för pivottabeller främst är fokuserat. Därför måste du följa dessa regler när du arbetar med en datakälla i form av cellområden. Samtidigt förbjuder ingen användning av Excel -gränssnittsverktyg i arbetet - pivottabeller analyserar endast data, och formatering, filter, gruppering och sortering av källceller kan vara godtyckligt.

Från autofilter till sammanfattningsrapport

Teoretiskt sett är det redan möjligt att analysera data i försäljningsbladet i tre dimensioner: varor, kunder och transportörer. Det finns inga uppgifter om produkternas och köparens egenskaper på detta blad, vilket därför inte tillåter dem att visas i pivottabellen. I normalt pivottabellskapande läge för källdata tillåter inte Excel att data från flera tabeller länkas med specifika fält. Du kan kringgå denna begränsning programmatiskt - se ett exempeltillägg till denna artikel på vår webbplats. För att inte använda mjukvarumetoder för informationsbehandling (särskilt eftersom de inte är universella) bör ytterligare egenskaper läggas till direkt i loggningsformuläret - se SalesAnalysis -bladet.

Användningen av VLOOKUP -funktioner gör det enkelt att komplettera originaldata med saknade egenskaper. Med AutoFilter kan du nu analysera data i olika dimensioner. Men problemet med grupperingar är fortfarande olöst. Till exempel är det ganska problematiskt att spåra beloppet endast av märken för vissa datum. Om du begränsar dig till Excel -formler måste du bygga ytterligare val med SUMIF -funktionen.

Låt oss nu se vilka möjligheter pivottabellgränssnittet ger. På arket Kodanalys flera rapporter byggdes baserat på en rad celler med bladdata Försäljningsanalys.

Den första analystabellen är byggd via Excel 2007 -gränssnittet Ribbon \ Insert \ Pivot Table(i Excel 2000-2003 menyn Data \ Pivottabell).

Den andra och tredje tabellen skapades genom kopiering och efterföljande anpassning. Datakällan för alla tabeller är densamma. Du kan kontrollera detta genom att ändra originaldata, sedan måste du uppdatera data för sammanfattande rapporter.

Ur vår synvinkel är fördelarna med informationsklarhet uppenbara. Du kan byta filter, kolumner och rader och dölja specifika grupper av värden för alla dimensioner och tillämpa manuell dra och släpp och automatisk sortering.

Egenskaper och formatering

Förutom den direkta visningen av data finns det en stor uppsättning alternativ för att visa utseende på pivottabeller. Extra data kan döljas med hjälp av filter. För ett enda element eller fält är det lättare att använda snabbmenyalternativet Radera(i version 2000-2003 Dölj).

Det är också lämpligt att ställa in visningen av andra element i pivottabellen inte genom att formatera cellen, utan genom att ställa in fältet eller elementet i pivottabellen. För att göra detta, flytta muspekaren till önskat element, vänta på att en speciell markörform visas (i form av en pil) och välj sedan det valda elementet med ett enda klick. Efter val kan du ändra vyn genom menyfliksområdet, snabbmenyn eller ringa till standarddialogrutan för cellformat:

Dessutom introducerar Excel 2007 många fördefinierade PivotTable -visningsstilar:

Observera att kontrollfiltren och dragområdena är aktiva i diagrammet.

Tillgång till extern data

Som redan nämnts kan den kanske största effekten av att använda pivottabeller uppnås vid åtkomst till data från externa källor - OLAP -kuber och databasfrågor. Sådana källor lagrar vanligtvis stora mängder information och har också en fördefinierad relationsstruktur som gör det enkelt att definiera mått på flerdimensionella data (pivottabellfält).

Excel stöder många typer av externa datakällor:

Den största effekten av användningen av externa informationskällor kan uppnås genom att använda automatiseringsverktyg (VBA -program) både för att erhålla data och för deras förbehandling i pivottabeller.

Arbeta med en OLAP -kub i MS Excel
1. Få behörighet att komma åt SQL Server Analysis Services OLAP -kub (SSAS)
2. MS Excel 2016/2013/2010 måste installeras på din dator (MS Excel 2007 är också möjligt, men det är inte bekvämt att arbeta i det, och funktionen i MS Excel 2003 är ganska dålig)
3. Öppna MS Excel, starta guiden för att konfigurera anslutningen till analytisk tjänst:

3.1 Ange namn eller IP-adress för den aktuella OLAP-servern (ibland måste du ange det öppna portnumret, till exempel 192.25.25.102:80); domänautentisering används:

3.2 Välj en flerdimensionell databas och en analytisk kub (om du har åtkomsträttigheter till kuben):

3.3 Inställningarna för anslutning till analystjänsten sparas i en odc -fil på din dator:

3.4 Välj rapporttyp (pivottabell / graf) och ange platsen för dess placering:

Om en anslutning redan har skapats i Excel -arbetsboken kan den användas igen: huvudmenyn "Data" -> "Befintliga anslutningar" -> välj anslutningen i den här arbetsboken -> infoga pivottabellen i den angivna cellen.
4. När du har anslutit till kuben kan du starta interaktiv dataanalys:

Innan interaktiv dataanalys påbörjas är det nödvändigt att avgöra vilket av fälten som kommer att delta i bildandet av rader, kolumner och filter (sidor) i pivottabellen. I allmänhet är pivottabellen tredimensionell, och vi kan anta att den tredje dimensionen ligger vinkelrätt mot skärmen, och vi observerar sektioner parallella med skärmens plan och bestäms av vilken "sida" som väljs för visning. Filtrering kan göras genom att dra och släppa motsvarande måttattribut i rapportens filterområde. Filtrering begränsar kubutrymmet, vilket minskar belastningen på OLAP -servern, så det är att föredra att installera nödvändiga filter först... Därefter placerar du måttattributen i raden, kolumnen och mäter områden i dataområdet i pivottabellen.

Varje gång pivottabellen ändras skickas en MDX -sats automatiskt till OLAP -servern och returnerar data. Ju större och mer komplex mängden bearbetad data, de beräknade indikatorerna, desto längre är frågans exekveringstid. Du kan avbryta genomförandet av begäran genom att trycka på knappen Fly... De senast utförda operationerna kan ångras (Ctrl + Z) eller göra om (Ctrl + Y).

Vanligtvis, för de mest använda kombinationerna av dimensionsattribut, lagrar kuben förberäknad aggregerad data, så svarstiden för sådana frågor är flera sekunder. Det är dock omöjligt att beräkna alla möjliga kombinationer av aggregationer, eftersom detta kan ta mycket tid och lagringsutrymme. Att utföra massiva frågor om data på granularitetsnivå kan kräva betydande serverresurser, så deras körningstid kan vara lång. Efter att ha läst data från hårddiskar placerar servern den i RAM -cachen, vilket gör att efterföljande sådana begäranden kan köras direkt, eftersom data kommer att hämtas från cachen.

Om du tror att din begäran kommer att användas ofta och utförandetiden är otillfredsställande kan du kontakta den analytiska utvecklingsstödstjänsten för att optimera genomförandet av begäran.

Efter att ha placerat hierarkin i raden / kolumnområdet är det möjligt att dölja enskilda nivåer:

Viktiga attribut(mindre ofta - för attribut högre i hierarkin) kan dimensioner ha egenskaper - beskrivande egenskaper som kan visas både i verktygstips och i form av fält:

Om du behöver visa flera fältegenskaper samtidigt kan du använda motsvarande dialogruta:

Användardefinierade kit

Excel 2010 introducerar möjligheten att interaktivt skapa dina egna (användardefinierade) uppsättningar från dimensionsmedlemmar:

Till skillnad från uppsättningar som skapas och lagras centralt på kubsidan, lagras anpassade uppsättningar lokalt i Excel -arbetsboken och kan användas senare:

Avancerade användare kan skapa uppsättningar med MDX -konstruktioner:

Ställa in pivottabellegenskaper

Genom posten "Pivottabellparametrar ..." i snabbmenyn (högerklicka i pivottabellen) kan du anpassa pivottabellen, till exempel:
- fliken "Output", parameter "Classic layout of the pivot table" - pivottabellen blir interaktiv, du kan dra fälten (Drag & Drop);
- fliken "Utmatning", "Visa objekt utan data i rader" parameter - tomma rader visas i pivottabellen som inte innehåller några indikatorvärden för motsvarande måttobjekt;
- "Layout och Format" -fliken, "Behåll cellformatering vid uppdatering" -parameter - i pivottabellen kan du åsidosätta och bevara cellformatet vid uppdatering av data;

Skapa pivottabeller

För en befintlig OLAP -pivottabell kan du skapa ett pivottabell - cirkel, stapel, stapel, diagram, scatter och andra typer av diagram:

I detta fall synkroniseras pivottabellen med pivottabellen - när du ändrar sammansättningen av indikatorer, filter, dimensioner i pivottabellen uppdateras pivottabellen också.

Skapa instrumentpaneler

Välj den ursprungliga pivottabellen, kopiera den till Urklipp (Ctrl + C) och klistra in en kopia av den (Ctrl + V), där vi kommer att ändra indikatorernas sammansättning:

För att hantera flera pivottabeller samtidigt, infoga en bit (ny funktionalitet tillgänglig sedan MS Excel 2010). Låt oss ansluta vår Slicer till pivottabeller - högerklicka i skivan, välj "Pivottabellanslutningar ..." från snabbmenyn. Det bör noteras att det kan finnas flera vridpaneler som kan servera pivottabeller på olika ark samtidigt, vilket gör det möjligt att skapa samordnade instrumentpaneler.

Skivfönster kan anpassas: du måste välja panelen och sedan se alternativen "Storlek och egenskaper ...", "Skivinställningar", "Tilldela ett makro" i snabbmenyn, aktiverad med högerklick eller "Alternativ" i huvudmenyn. Så det är möjligt att ställa in antalet kolumner för elementens (knappar) i segmentet, storlekarna på segmentknapparna och panelen, för att definiera färgschemat och designstilen från den befintliga uppsättningen för segmentet (eller skapa din egen stil), för att definiera din egen paneltitel, för att tilldela ett programmakro genom vilket du kan utöka panelfunktionen.

Kör en MDX -fråga från Excel

Först och främst måste du utföra DRILLTHROUGH -operationen på någon tangent, d.v.s. gå ner till detaljerade data (detaljerade data visas på ett separat blad) och öppna listan över anslutningar;
Öppna anslutningsegenskaper, gå till fliken "Definition";
Välj standardkommandotyp och placera den förberedda i kommandotekstfältet. MDX förfrågan;
När du klickar på knappen efter att du har kontrollerat frågans syntax och har lämpliga åtkomsträttigheter, kommer frågan att köras på servern och resultatet presenteras i det aktuella bladet som ett vanligt platt bord.
Du kan se texten i MDX-frågan som genereras av Excel genom att installera ett gratis tillägg som också ger andra ytterligare funktioner.

Översättning till andra språk

Den analytiska kuben stöder lokalisering till ryska och engelska (om det behövs är lokalisering till andra språk möjlig). Översättningar gäller namnen på dimensioner, hierarkier, attribut, mappar, mått samt element i enskilda hierarkier om det finns översättningar för dem på sidan av redovisningssystem / datalagring. För att ändra språk måste du öppna anslutningsegenskaperna och lägga till följande alternativ i anslutningssträngen:
Utökade egenskaper = "Språk = 1033"
där 1033 är engelsk lokalisering
1049 - lokalisering till ryska

Ytterligare Excel -tillägg för Microsoft OLAP

Möjligheten att arbeta med Microsoft OLAP -kuber ökar om du använder ytterligare tillägg, till exempel OLAP PivotTable -tillägg, tack vare vilken du kan använda snabbsökning efter dimension:

webbplats 2011-01-11 16: 57: 00Z Senast ändrad: 2017-10-15 16: 33: 59Z Ålderspublik: 14-70