PowerPivot for Excel - Táboa de busca en Data Warehouse

Unha das cousas que eu mostro máis sobre PowerPivot para Excel é a capacidade de engadir táboas de busca aos teus conxuntos de datos. Na maioría das veces, os datos que está a traballar non teñen todos os campos que precisa para a súa análise. Por exemplo, pode ter un campo de data, pero necesitará agrupar os datos por trimestre. Pode escribir unha fórmula, pero é máis fácil crear unha táboa de busca sinxela dentro do entorno PowerPivot.

Tamén pode usar esta táboa de busca para outra agrupación, como o nome do mes e a primeira / segunda metade do ano. Nos termos do depósito de datos, en realidade está creando unha táboa de dimensión de data. Neste artigo, vou darlle un par de táboas de dimensión de exemplo para mellorar o seu proxecto PowerPivot para Excel.

Nova táboa de Dimensión de texto (Busca)

Consideremos unha táboa con datos de orde (os datos de Contoso de Microsoft inclúen un conxunto de datos semellante a este). Asume que a táboa ten campos para o cliente, a data da orde, o total da orde e o tipo de pedido. Centrarémonos no campo do tipo de pedido. Supoña que o campo do tipo de orde inclúe valores como:

• Netbooks
• Escritorios
• Monitores
• Projetores
• Impresoras
• Escáneres
• Cámaras dixitais
• Cámaras SLR dixitais
• Cámaras cinematográficas
• Camcorders
• Teléfonos de oficina
• Teléfonos intelixentes
• PDAs
• Accesorios para celulares

En realidade, tería os códigos para estes pero manter este exemplo sinxelo, supoña que estes son os valores reais na táboa de pedidos.

Usando PowerPivot para Excel, poderías agrupar facilmente os teus pedidos por tipo de pedido. E se quixeses unha agrupación diferente? Por exemplo, supoña que precisa dunha agrupación "categoría" como computadoras, cámaras e teléfonos. A táboa de pedidos non ten un campo "categoría", pero pode crealo facilmente como unha táboa de busca en PowerPivot para Excel.

A táboa de busca de mostra completa está a continuación na Táboa 1 . Aquí tes os pasos:

• Paso 1: precisa dunha lista distinta do campo de tipo para a súa táboa de busca. Este será o seu campo de busca. Desde o seu conxunto de datos, cree unha lista distinta de valores desde o campo tipo de orde. Introduza a listaxe de "tipos" en un libro de Excel. Etiqueta a columna Tipo.
• Paso 2: na columna á beira da columna de busca (Tipo), engade o novo campo ao que quere agrupar. No noso exemplo, engade unha columna cunha etiqueta chamada Categoría.
• Paso 3: Para cada valor na súa lista distinta de valores (tipos neste exemplo), engade os valores correspondentes "Categoría". No noso exemplo sinxelo, introduza Orders, cámaras ou teléfonos na columna da categoría.
• Paso 4: Copie a táboa de datos de tipo e categoría no portapapeis.
• 5. Abra o caderno de traballo de Excel cos datos de orde en PowerPivot para Excel. Iniciar a xanela PowerPivot. Fai clic en Pegar que mostrará a táboa de busca nova. Dálle un nome a unha táboa e asegúrate de comprobar "Usar a primeira fila como cabeceiras de columnas". Faga clic en Aceptar. Creaches unha táboa de busca en PowerPivot.
• Paso 6: Cree unha relación entre o campo Tipo na táboa de Ordes eo campo Categoría na táboa de busca. Fai clic na cinta de deseño e elixe Crear relación. Fai as seleccións no diálogo Crear relación e fai clic en Crear.

Cando cree unha Táboa dinámica en Excel en función dos datos de PowerPivot, poderá agrupar polo seu novo campo Categoría. Teña en conta que PowerPivot for Excel só soporta Inner Joins. Se tes un "tipo de pedido" que faltan na táboa de busca, perderanse todos os rexistros correspondentes dese tipo de táboa dinámica en función dos datos de PowerPivot. Necesitará comprobar isto de cando en vez.

Táboa de Dimensión de datos (Busca)

A táboa de busca de data probablemente será necesaria na maioría dos seus proxectos PowerPivot para Excel. A maioría dos conxuntos de datos teñen algún tipo de campo de data. Existen funcións para calcular o ano eo mes.

Non obstante, se precisa o mes real ou o trimestre, cómpre escribir unha fórmula complexa. É moito máis doado incluír unha táboa de Dimensión de data (lookup) e combina-lo co número do mes no seu conxunto de datos principal. Necesitará engadir unha columna á táboa de pedidos para representar o número do mes desde o campo da data da orde. A fórmula DAX para "mes" no noso exemplo é "= MES ([Data de orde]). Isto devolverá un número entre 1 e 12 para cada rexistro. A nosa táboa de dimensións fornecerá valores alternativos que ligan ao número de mes. proporcionaralle flexibilidade na súa análise. A táboa de dimensións de mostra completa está a continuación na Táboa 2 .

A táboa de busca ou dimensión de data incluirá 12 rexistros. A columna do mes terá os valores 1 a 12. Outras columnas incluirán o texto do mes abreviado, o texto do mes enteiro, o trimestre, etc. Aquí están os pasos:

• Paso 1: Copia a táboa da Táboa 2 a continuación e pégalla en PowerPivot. Podería crear esta táboa en Excel pero estou aforrando tempo. Debe poder pegar directamente desde os datos seleccionados a continuación se está a usar Internet Explorer. PowerPivot colle o formato de táboa nas miñas probas. Se está a usar outro navegador, é posible que teña que pegar primeiro en Excel e copialo de Excel para retomar o formato da táboa.
• Paso 2: Abra o caderno de Excel cos datos da orde en PowerPivot para Excel. Iniciar a xanela PowerPivot. Faga clic en Pegar que traerá a táboa de busca copiada da táboa seguinte ou a partir de Excel. Dálle un nome a unha táboa e asegúrate de comprobar "Usar a primeira fila como cabeceiras de columnas". Faga clic en Aceptar. Creou unha táboa de busca de datos en PowerPivot.

• Paso 3 : Cree unha relación entre o campo Mes na táboa Orde eo campo MonthNumber na táboa de busca. Fai clic na cinta de deseño e elixe Crear relación. Fai as seleccións no diálogo Crear relación e fai clic en Crear.

De novo, coa adición dunha dimensión de data, poderá agrupar os datos na súa Táboa dinámica usando calquera dos diferentes valores da táboa de busca de data. A agrupación por trimestre ou o nome do mes será un complemento.

Táboas de Dimensión de mostra (Busca)

Táboa 1

Táboa 2