PowerPivot for Excel: taula de cerca en Data Warehouse

Una de les coses que mostro més sobre PowerPivot for Excel és la possibilitat d'afegir taules de cerca als vostres conjunts de dades. La majoria de les vegades, les dades amb què està treballant no tenen tots els camps que necessiteu per a la vostra anàlisi. Per exemple, és possible que tingueu un camp de data però heu d'agrupar les dades per trimestre. Podeu escriure una fórmula, però és més fàcil crear una taula de cerca senzilla dins de l'entorn PowerPivot.

També podeu utilitzar aquesta taula de cerca per a una altra agrupació, com ara el nom del mes i la primera / segona meitat de l'any. En termes d'emmagatzematge de dades, en realitat està creant una taula de dimensions de data. En aquest article, us donaré un parell de taules de dimensions d'exemple per millorar el projecte PowerPivot for Excel.

Taula de noves dimensions de text (cerca)

Considerem una taula amb dades de comandes (les dades de Contoso de Microsoft inclouen un conjunt de dades semblant a això). Assumeixi que la taula té camps per al client, la data de la comanda, el total de l'ordre i el tipus de comanda. Anem a centrar-nos en el camp del tipus d'ordre. Assumeixi que el camp del tipus de comanda inclou valors com:

• Netbooks
• Escriptoris
• Monitors
• Projectors
• Impressores
• Escàners
• Càmeres digitals
• Càmeres SLR digitals
• Càmeres de cinema
• Videocàmeres
• Telèfons d'oficina
• Telèfons intel·ligents
• PDAs
• Accessoris per a telèfons mòbils

En realitat, tindria codis per a aquests, però per mantenir aquest exemple senzill, suposa que aquests són els valors reals de la taula de comandes.

Mitjançant PowerPivot per a Excel, podreu agrupar les vostres ordres per tipus de comanda. Què passa si voleu una agrupació diferent? Per exemple, suposem que necessiteu una agrupació de "categories", com ara ordinadors, càmeres i telèfons. La taula d'ordres no té un camp "categoria", però pot crear-la fàcilment com una taula de cerca en PowerPivot for Excel.

La taula de cerca d'exemple completa es mostra a continuació a la Taula 1 . Aquests són els passos:

• Pas 1: necessiteu una llista diferent del camp de tipus de la vostra taula de cerca. Aquest serà el vostre camp de cerca. Des del conjunt de dades, creeu una llista de valors diferent del camp de tipus d'ordre. Introduïu la llista diferent de "tipus" en un llibre de treball Excel. Etiqueta la columna Tipus.
• Pas 2: a la columna al costat de la columna de cerca (Tipus), afegiu el camp nou al qual voleu agrupar. Al nostre exemple, afegiu una columna amb una etiqueta anomenada Categoria.
• Pas 3: Per cada valor de la vostra llista de valors diferents (tipus d'aquest exemple), afegiu els valors "Categoria" corresponents. En el nostre senzill exemple, introduïu Ordinadors, càmeres o telèfons a la columna de la categoria.
• Pas 4: Copieu la taula de dades de tipus i categoria al porta-retalls.
• Pas 5: Obriu el llibre d'Excel amb les dades de l'ordre en PowerPivot for Excel. Inicieu la finestra PowerPivot. Feu clic a Enganxa que introduirà la vostra nova taula de cerca. Doneu un nom a la taula i assegureu-vos de marcar "Usar la primera fila com a encapçalaments de columnes". Feu clic a Acceptar. Heu creat una taula de cerca a PowerPivot.
• Pas 6: Creeu una relació entre el camp Tipus a la taula Ordre i el camp Categoria a la taula de cerca. Feu clic a la cinta de disseny i seleccioneu Crea una relació. Feu les seleccions en el diàleg Crear relació i feu clic a Crea.

Quan creeu una taula dinàmica en Excel basant-se en les dades de PowerPivot, podreu agrupar el vostre camp de nova categoria. Tingueu en compte que PowerPivot for Excel només és compatible amb Inner Joins. Si teniu un "tipus de comanda" que falten a la vostra taula de cerca, tots els registres corresponents d'aquest tipus falten en qualsevol taula dinàmica basada en les dades de PowerPivot. Haureu de verificar això de tant en tant.

Taula Dimensió de dates (cerca)

És probable que la taula de cerca de dates sigui necessària en la majoria dels projectes PowerPivot per a Excel. La majoria de conjunts de dades tenen algun tipus de camp (s) de data. Hi ha funcions per calcular l'any i el mes.

Tanmateix, si necessiteu el text real del mes o el trimestre, heu d'escriure una fórmula complexa. És molt més fàcil incloure una taula de dimensió de la data (cerca) i comparar-la amb el número de mes al conjunt de dades principal. Haureu d'afegir una columna a la vostra taula de comanda per representar el número del mes des del camp de data de la comanda. La fórmula DAX per "mes" en el nostre exemple és "= MES ([Data de comanda]). Això retornarà un número entre 1 i 12 per a cada registre. La nostra taula de dimensions proporcionarà valors alternatius, que enllaçaran amb el número de mes. us proporcionarà flexibilitat en la vostra anàlisi. La taula completa de la mida de la data de la mostra es mostra a continuació a la taula 2 .

La dimensió de la data o la taula de cerca inclourà 12 registres. La columna del mes tindrà els valors del 1 al 12. 12. Altres columnes inclouran text del mes abreujat, text del mes complet, trimestre, etc. Aquests són els passos:

• Pas 1: Copieu la taula de la Taula 2 a sota i enganxeu-la a PowerPivot. Podeu crear aquesta taula a Excel, però us estalviareu temps. Haureu de poder enganxar directament des de les dades seleccionades a continuació si feu servir Internet Explorer. PowerPivot agafa el format de la taula en les meves proves. Si feu servir un altre navegador, primer heu d'enganxar a Excel i copieu-lo des d'Excel per seleccionar el format de la taula.
• Pas 2: Obriu el llibre d'Excel amb les dades de l'ordre en PowerPivot for Excel. Inicieu la finestra PowerPivot. Feu clic a Enganxa que us mostrarà la vostra taula de cerca copiada de la taula següent o des d'Excel. Doneu un nom a la taula i assegureu-vos de marcar "Usar la primera fila com a encapçalaments de columnes". Feu clic a Acceptar. Heu creat una taula de cerca de dates a PowerPivot.

• Pas 3 : Creeu una relació entre el camp Mes en la taula de comanda i el camp Numero de mes a la taula de cerca. Feu clic a la cinta de disseny i seleccioneu Crea una relació. Feu les seleccions en el diàleg Crear relació i feu clic a Crea.

De nou, amb l'addició d'una dimensió de data, podreu agrupar les dades a la vostra taula dinàmica utilitzant qualsevol dels valors diferents de la taula de cerca de dates. Agrupar per quart o el nom del mes serà un complement.

Taules de dimensió de mostra (cerca)

Taula 1

Taula 2