PowerPivot vir Excel - Soektafel in Data Warehouse

Een van die dinge wat ek die meeste van PowerPivot vir Excel noem, is die vermoë om opblaftabelle by jou datastelle te voeg. Meeste van die tyd, die data waarmee jy werk, het nie elke veld wat jy nodig het vir jou analise nie. Byvoorbeeld, jy het dalk 'n datumveld, maar moet jou data per kwartaal groepeer. U kan 'n formule skryf, maar dit is makliker om 'n eenvoudige soektogtafel in die PowerPivot-omgewing te skep.

U kan ook hierdie opzoektabel gebruik vir 'n ander groepering soos maand naam en eerste / tweede helfte van die jaar. In data warehousing terme, is jy eintlik 'n datum dimensie tafel. In hierdie artikel gaan ek jou 'n paar voorbeeld dimensietabelle gee om jou PowerPivot vir Excel-projek te verbeter.

Nuwe Teks Dimension (Opsoek) Tabel

Kom ons kyk na 'n tabel met bestellingsdata (die Contoso-data van Microsoft sluit 'n datastel-similie in). Gestel die tabel het velde vir kliënt, bestellingsdatum, bestelbedrag en bestellingsoort. Ons gaan fokus op die bestelling tipe veld. Gestel die veld bestel type bevat waardes soos:

• netbooks
• desktops
• monitors
• projektors
• drukkers
• skandeerders
• Digitale kameras
• Digitale SLR-kameras
• Filmkameras
• camcorders
• Kantoorfone
• Slim fone
• PDA
• Selfone Toebehore

In werklikheid sou jy kodes hiervoor hê, maar om hierdie voorbeeld eenvoudig te hou, neem aan dat dit die werklike waardes in die bestellingstabel is.

Deur PowerPivot vir Excel te gebruik, kan u u bestellings maklik volgens bestelling tipe groepeer. Wat as jy 'n ander groepering wou hê? Stel byvoorbeeld dat jy 'n "kategorie" groepering soos rekenaars, kameras en fone nodig het. Die bestellingstabel het nie 'n "kategorie" -veld nie, maar jy kan dit maklik as 'n opvattende tabel in PowerPivot vir Excel skep.

Die volledige steekproef-tabel is hieronder in Tabel 1 . Hier is die stappe:

• Stap 1: Jy benodig 'n duidelike lys van die tipe veld vir jou opblaftafel. Dit sal jou soekveld wees. Maak uit u datastel 'n duidelike lys van waardes uit die veld bestellingsoort. Gee die onderskeie lys van "tipes" in 'n Excel-werkboek. Merk die kolom Tipe.
• Stap 2: Voeg die nuwe veld by wat jy wil groepeer in die kolom langs jou soekkolom (Type). Voeg in ons voorbeeld 'n kolom by met 'n etiket genaamd Kategorie.
• Stap 3: Voeg die ooreenstemmende "Kategorie" waardes vir elke waarde in jou afsonderlike lys van waardes (tipes in hierdie voorbeeld) by. In ons eenvoudige voorbeeld, tik Rekenaars, kameras of telefone in die kolomkategorie.
• Stap 4: Kopieer die Tipe en Kategorie data tabel in jou knipbord.
• Stap 5: Maak die Excel-werkboek oop met die bestellingsdata in PowerPivot vir Excel. Begin die PowerPivot-venster. Kliek op Plakken wat jou nuwe soektabel sal insluit. Gee die tabel 'n naam en maak seker dat jy tjek "Gebruik eerste ry as kolomopskrifte." Klik op OK. U het 'n opsoek tabel in PowerPivot geskep.
• Stap 6: Skep 'n verhouding tussen die Tipe veld in die tabel Bestelling en die Kategorie-veld in die opsoektabel. Klik op die Ontwerp lint en kies Skep verhouding. Maak die keuses in die dialoog Skep verhoudings en klik op Skep.

As jy 'n draaipunt in Excel maak op grond van die PowerPivot-data, kan jy groepeer deur jou nuwe Kategorie-veld. Hou in gedagte dat PowerPivot vir Excel net Inner Joins ondersteun. As u 'n "bestelling tipe" ontbreek in u opsoektabel, sal alle ooreenstemmende rekords vir die tipe ontbreek van enige draaipunt gebaseer op die PowerPivot-data. U sal dit van tyd tot tyd moet nagaan.

Datum Dimension (Opzoek) Tabel

In die meeste van jou PowerPivot-projekte vir Excel-projekte sal die datum-opsoektabel heel waarskynlik nodig wees. Die meeste datastelle het 'n tipe datumveld (e). Daar is funksies om die jaar en maand te bereken.

As jy egter die werklike maand teks of kwartaal benodig, moet jy 'n komplekse formule skryf. Dit is baie makliker om 'n Date-dimensie (opsoektabel) in te sluit en dit ooreen te stem met die maandnommer in jou hoofdatastel. U moet 'n kolom by u bestellingstabel byvoeg om die maandnommer uit die veld van die bestellingsdatum te verteenwoordig. Die DAX-formule vir "maand" in ons voorbeeld is "= MAAND ([Besteldatum]). Dit sal 'n getal tussen 1 en 12 vir elke rekord teruggee. Ons dimensietabel bied alternatiewe waardes wat verband hou met die maandnommer. sal u buigsaamheid gee in u analise. Die volledige voorbeeld datum dimensietabel is onder in Tabel 2 .

Die datumdimensie of opsoektabel sal 12 rekords insluit. Die maandkolom sal die waardes 1 tot 12 hê. Ander kolomme sal verkorte maandteks, volledige maand teks, kwartier, ens. Bevat. Hier is die stappe:

• Stap 1: Kopieer die tabel uit Tabel 2 hieronder en plak dit in PowerPivot. U kan hierdie tabel in Excel skep, maar ek spaar u tyd. U moet direk vanaf die geselekteerde data kan plak as u Internet Explorer gebruik. PowerPivot haal die tabelformatering op in my toets. As jy 'n ander blaaier gebruik, moet jy eers in Excel plak en kopieer dit uit Excel om die tabelformatering op te tel.
• Stap 2: Maak die Excel-werkboek oop met die bestellingsdata in PowerPivot vir Excel. Begin die PowerPivot-venster. Kliek op Plakken wat jou soektabel in die onderstaande tabel of Excel sal kopieer. Gee die tabel 'n naam en maak seker dat jy tjek "Gebruik eerste ry as kolomopskrifte." Klik op OK. U het 'n datumopslagtafel in PowerPivot geskep.

• Stap 3 : Skep 'n verhouding tussen die Maand-veld in die Bestel-tabel en die Maand-nommer-veld in die opsoektabel. Klik op die Ontwerp lint en kies Skep verhouding. Maak die keuses in die dialoog Skep verhoudings en klik op Skep.

Weereens, met die byvoeging van 'n datum dimensie, kan u die data in u PivotTable groepeer volgens enige van die verskillende waardes vanaf die datumopslagtabel. Groepering per kwartaal of die naam van die maand sal 'n snap wees.

Voorbeeld Dimension (Opzoek) Tabelle

Tabel 1

Tabel 2