Publicada noviembre 4, 2013 
Figura 1: Muestra una tabla de hechos (tabla de ord. de ventas) con 2 tablas de dimensiones para Clientes y Vendedores
Una de las preguntas más comunes que tiene cualquier persona que usa Power Pivot es… Como debo de Modelar mis Datos en Power Pivot? y no es física cuántica… es fácil de aprender y espero que pueda entender más acerca de ello después que termine de leer este post.
Si eres nuevo en Power Pivot y nuevo al modelado de bases de datos entonces el diagrama de arriba tiene poco o ningún sentido pero sólo piense en ellas como tablas de búsqueda que más tarde va a hacer algunos BUSCARV, COINCIDIR / INDEX y todas aquellas fórmulas que nosotros, como usuarios de Excel, conocemos.
Vamos a aprender más acerca de lo que debe tener en cuenta al construir su modelo de datos!
¿Por qué es esto importante?
Esta es probablemente una de las partes más importantes, si no la más importante. ¿Por qué? piense en ello … Si le sucede que tiene un mal modelo de datos y sus datos están por todas partes o no almacenados en el camino correcto, entonces va a ser mucho más complejo el desarrollar sus tablas dinámicas, fórmulas DAX y, al final, el espacio real de almacenamiento que las tablas están tomando de la memoria podría estar bajando el rendimiento al pasar a través de la cuadrícula de Power Pivot y hacer nuevas columnas calculadas.
Por encima de todo, lo que realmente queremos es mantener las cosas tan simples como sea posible y tan optimizado como les sea posible ser para la generación de informes.
Para hacer esto, necesitamos entender los siguientes conceptos:
-
Tipos de tablas y sus atributos
-
Llaves Primarias y Foráneas
-
Normalización vs Denormalización
-
Esquema Estrella vs Copo de Nieve
Tipos de Tablas y sus atributos
En primer lugar, tenemos que entender que Power Pivot solo maneja tablas … dentro de una tabla encontrará campo (columnas) y dentro de esos campos encontrarás filas. Un registro es la combinación de todos los campos, o es sólo una fila completa, así por ejemplo:
la fila en la que vemos el Vendedor.ID 4, el nombre del vendedor Miguel, atributo1 como valor y como atributo2 Valor es un registro. Más importante aún, este registro tiene un identificador que está definido en la columna Vendedor.ID.
Detrás de esto, tenemos que entender por qué algunas tablas se llaman tablas de dimensiones (DIM) y algunas se llaman tablas de hechos (FACT). Aquí está la respuesta
DIM = Dimensiones también conocido como básicamente una tabla de búsqueda donde tendrá grupos, subgrupos o incluso sólo categorías en para una mejor división y filtrado de la información que usted tiene. Una dimensión es una manera de ver las cosas … alguien podría estar buscando en ventas por año, pero otra persona le gustaría ver las ventas de los clientes y si hay un grupo de clientes que se podría querer verlo de esa manera también … estos son llamados dimensiones porque dan otra serie de ideas o puntos de vista para el usuario final.
FACT = como su nombre indica, es una tabla con todos los hechos que tiene en una operación o proceso. Por ejemplo, mi tabla de hechos tiene todas las ventas que he hecho. Otro tipo de tabla de hechos podría ser un sistema de venta de entradas, donde todas las entradas se almacenan y todos los estatus se están cambiando. Es la tabla principal y la base de cualquier esquema ya que desde aquí es que usted va a calcular sus medidas (en la mayoría de los casos).
Llaves Primarias y Foráneas
Al hablar sobre los tipos de tablas que vamos a encontrar durante nuestro viaje en Power Pivot, también tenemos que hablar sobre el tipo de campos o columnas que relacionan cada tabla.
| Tabla de Ordenes de Ventas | |||
| # de Orden (ID) | Vendedor | Cliente | Venta Total |
| 1 | 4 | 2 | 133 |
| 2 | 1 | 3 | 63 |
| 3 | 1 | 4 | 75 |
| 4 | 1 | 4 | 135 |
| 5 | 3 | 5 | 18 |
| 6 | 1 | 1 | 88 |
| 7 | 4 | 5 | 117 |
| 8 | 2 | 1 | 36 |
| 9 | 4 | 4 | 70 |
| 10 | 2 | 4 | 41 |
| 11 | 2 | 1 | 54 |
| 12 | 5 | 6 | 51 |
En la tabla anterior podemos agrupar los campos como
Llave Primaria: # de Orden
Llaves Foráneas: Vendedor, Cliente
Campos para Agregaciones: Venta Total
ok, genial.. estamos agrupando ahora, pero … ¿qué significan llaves primarias y foráneas realmente?
Llave Primaria = El identificador de columna de la tabla en donde nos encontramos. Se trata de un valor único que hace una distinción entre cada fila de la tabla especificada.
Llave Foránea = es una llave primaria que pertenece a otra tabla, pero que se encuentra en la tabla de arriba, porque la usamos para relacionar las tablas. Por lo tanto, estamos creando las relaciones entre los Vendedores y la tabla Vendedores utilizando sus claves principales como claves externas en nuestra tabla de hechos.
Como usuarios de Excel, usted ya estaba haciendo esto con INDEX / MATCH, SUMAPRODUCTO, BUSCARV, BUSCARH y otras funciones que fueron capaces de relacionar los campos entre 2 tablas. Lo que cambia ahora es que usted es capaz de hacerlo con sólo crear una relación en lugar de crear una fórmula específica para ello. (Tenga en cuenta que todavía tenemos la fórmula LOOKUPVALUE para funcionar como una función BUSCARV FALSO)
Tenga en cuenta que las relaciones sólo se hacen entre una tabla con valores únicos a una que tiene n cantidad de valores duplicados. (relación una-a-muchos)
Normalización vs Denormalización
En primer lugar, vamos a definir ambos conceptos:
Normalización = optimizar mediante la reducción de la redundancia de los datos entre las tablas
Denormalización = intentar optimizar las consultas y los procesos de lectura mediante la adición de los datos redundantes o por agrupación de datos
Ahora, echemos un vistazo a un ejemplo real:
| Tabla de Ordenes de Ventas | |||||
| # de Orden | Vendedor | Cliente | Venta Total | Atributo 1 | Atributo 2 |
| 1 | 4 | 2 | 133 | Valor | Valor |
| 2 | 1 | 3 | 63 | Valor | Valor |
| 3 | 1 | 4 | 75 | Valor | Valor |
| 4 | 1 | 4 | 135 | Valor | Valor |
| 5 | 3 | 5 | 18 | Valor | Valor |
| 6 | 1 | 1 | 88 | Valor | Valor |
| 7 | 4 | 5 | 117 | Valor | Valor |
| 8 | 2 | 1 | 36 | Valor | Valor |
| 9 | 4 | 4 | 70 | Valor | Valor |
| 10 | 2 | 4 | 41 | Valor | Valor |
| 11 | 2 | 1 | 54 | Valor | Valor |
| 12 | 5 | 6 | 51 | Valor | Valor |
echar un vistazo más de cerca el color de las columnas, te darás cuenta de que tenemos las columnas atributo1 y atributo2 de la tabla Clientes ahora como parte de la tabla de hechos… ¿puedes adivinar si esta tabla es normalizado o sin normalizar?
La respuesta es Denormalizada…¿por qué? porque ya tenemos una relación con la tabla Clientes DIM con el Cliente.ID por lo que estamos creando una redundancia en la tabla por tener estas 2 columnas que se traduce en un mayor consumo de memoria (que es malo).
Así que ¿por cuál debería apuntar?
Normalización, pero hasta cierto punto … vamos a hablar más sobre esto en el tipo de esquemas que normalmente tenemos.
Esquema estrella (star) vs copo de nieve (snowflake)
VS
![snowflakes_PNG7545[1]](https://thepoweruser.com/wp-content/uploads/2013/11/snowflakes_PNG75451.png)
La principal diferencia del esquema de estrella y el copo de nieve es que en el esquema de estrella se tienen menos tablas, pero dentro de esas tablas se tiene una forma más compacta de almacenar los datos. En otras palabras, si tenemos una tabla principal, como la tabla de los clientes, pero dentro de esa misma tabla tenemos una columna / campo con el nombre de Grupo de Clientes … este campo podría estar relacionado con otra tabla y, posteriormente, esa tabla relacionado podría tener otra tabla relacionada con más atributos para definirlo
Ahora bien, si tratamos de definir todos los campos en el nivel más granular en una tabla separada vas a encontrarte con un escenario en el que podría haber una gran cantidad de tablas en nuestro modelo de datos. Una de las sugerencias del esquema de estrella es que alguna información se puede guardar en una mesa compartida, que es algo así como la combinación de los últimos 2 tablas
La idea de tener menos tablas y unas cuantas columnas más a menudo es el mejor enfoque, pero depende del modelo de datos específico para ver si se ajusta a sus necesidades.
Hola Miguel Ángel,
Te explico mi caso porque las relaciones no me salen bien..
– Tengo 2 tablas idénticas (en cuento columnas/campos): una para presupuesto y otra para ejecución (gastos e ingresos). Quiero relacionar ambas para saber si tengo desviaciones en la ejecución.
– En ambas tablas tengo una columna llamada «partida» a través de la cual quiero relacionar ambas tablas. Por tanto, la idea sería la siguiente: tengo la partida 34 con un presupuesto de 30.000€ y quiero compararla con la partida de «ejecución». En la tabla de ejecución puedo tener diversas filas con la partida de 34 (diversos costes)
– He conseguido relacionar ambas creando una tercera tabla en la que pongo las partidas (la columna «partida» de ambas tablas la relaciono con la columna «partida» de la tabla que he creado) y en la tabla dinámica resultante me consigue dar lo que quiero: compara el total presupuestado con el ejecutado.
– El problema viene cuando estos totales los quiero desglosar según categoría. Es decir, cada partida tiene asociada una categoría. Cuanto la columna de «Categoría» la traslado a «filas» los importes de la tabla «Ejecución» se desglosan pero no los de «Presupuesto», se sigue manteniendo el «Total» presupuestado.
Por tanto, entiendo que de alguna forma me falta relacionar las categorías de «ejecución» con «presupuesto» pero no consigo la forma… Me podrías ayudar?
Por otro lado, agradecería un post para ver cómo agregar al modelo de datos una tabla de calendario. He seguido los pasos de tu libro pero no lo consigo. He visitado también el enlace que aparece en el libro pero tampoco lo consigo.
Gracias y enhorabuena por el blog.
Hola Sergio!
Gracias por leer mi blog y por leer el libro!
Creo que estas leyendo el capítulo «17- Múltiples Tablas de Datos». Te recomiendo la lectura de la sección «Pivote Integrado» que se encuentra en la página 167 del mismo capítulo donde explica el porque es necesario tener dicha columna de «Categoría» en la nueva tabla intermediaria que has creado. Si no se encuentra en dicha tabla, sino en la tabla de Presupuesto o en la de actuales entonces la tabla dinámica solamente va a funcionar como lo estas planteando (o como te esta funcionando).
Voy a trabajar en un post sobre la tabla calendario! pero, ¿Qué es exactamente lo que necesitas o deseas hacer? Cuáles son las dudas que tienes sobre la tabla calendario?
Hola Miguel,
Lo conseguí. Ahora tengo otro problema porque cuando intento hacer la resta de dos medidas si se cumple un valor estático en una de las columnas de la tabla consulta, me da un error. Espero resolverlo pronto.
Respecto al calendario me he creado uno propio siguiendo la estructura de los excels que acompañaba al libro.
Gracias..
Hola Sergio, creo que estoy intentando hacer algo parecido a lo que comentas, me podrías ayudar con lo que has elaborado?.
Gracias
Excelente, bien resumido y explicado en idioma cristiano
Hola,instale powerpivot ,pero solo me deja relacionar 2 tablas,en donde puedo conseguir la version de Powerpivot donde se pueden relacionar varias tablas y ver el menu» relationship detection2? gracias
Hola Adrian,
Puedes utilizar el siguiente enlace:
http://www.microsoft.com/en-us/download/details.aspx?id=29074
Saludos,
Hola,de hecho utilice esa liga para bajar powerpivot,pero te repito que solo puedo relacionar 2 tablas,no se si tenga que tener algun programa instalado de sql para tener la version mas actualizada de powerpivot,yo necestio que power pivot relacione muchas tablas,no solamente 2.
Espero respuesta.
Saludos.
Hola Adrian!
Power Pivot puede relacionar múltiples tablas pero solo existen relaciones de 1:1 y de 1:n (una a una y de una a muchas – en español eso quiere decir de una tabla de valores únicos a una tabla con duplicados o no duplicados).
Te paso mi correo para que podamos resolver el problema que tienes. Me parece que lo podemos solucionar juntos =)
miguel.escobar@poweredsolutionsonline.com
Saludos!
Hola, tengo una relación n:n que no me doy cuenta cómo solucionar. En la tabla 1 tengo como llave única ID_tabla1. En la Tabla 2 tengo una lista de transacciones con ID_tabla2 y fecha_tabla2 como llave única.
Tengo hecha la relación tabla1.ID_tabla2 = tabla2.ID_tabla2.
Mi pregunta es cómo utilizar RELATED en la tabla1, si necesito obtener de esta relación 1:n el registro con fecha más reciente.
Hola!
Tengo una duda sobre las relaciones,
por ejemplo, tengo una tabla de activos, que estan catalogados por fecha, esa tabla la relaciono con un catalogo de fechas, hasta ahi esta ok
pero cuando agrego medidas en la tabla de activos, la relacion que hice con la tabla de fechas no me funciona ahi, sabes si es posible que las relaciones tambien funcionen con las medidas? o solo funcionan con los campos de las tablas predeterminadas?
Muchas gracias por el apoyo!
Hola!
Una duda sobre la relación de Tablas, como solucionar la relación de datos duplicados en el modelos de datos??
Perdón por la consulta en un tema viejo, pero estoy buscando info al respecto y pocos pueden decirme algo serio y con argumento.
Tener varios campos NULL en la tabla no rompe con la normalización?
Además te cuento que en un trabajo para un cliente tengo que auditar cada registro con los campos:
created_by -> clave foránea al id de la tabla usuario
created_at -> fecha de creación
updated_by -> clave foránea al id de la tabla usuario
updated_at -> fecha de modificación
Es requerimiento del cliente, mi duda es si esto también al repetirse en cada tabla no rompe con la normalización, más allá de esto tengo casi todas las tablas normalizadas en la 3fn, pero esto creo que me rompe con eso. Pensé en hacer 1 sola tabla llamada auditoria, y agregar estos campos y sumarle nombre de table y id de registro para relacionarla y no repetir todos estos campos en cada tabla.
Podrás orientarme un poco al respecto?
Muchas gracias de antemano y saludos!
Muchas gracias de antemano y saludos!
las llaves de una tabla no deberían de tener valores (null) y siempre deberían de ser valores únicos. Si los otros campos de la tabla tienen valores (null) pues no habría problema realmente.
Por lo que me comentas, me imagino que estás tratando de hacer un proceso de limpieza o en palabras más técnicas de ETL. Si necesitas hacer algún tipo de validación de datos antes de cargarlo a Power Pivot y auditarlo, entonces te recomendaría utilizar Power Query y luego cargar la tabla validada a Power Pivot.
El tener campos como created by, created at, updated by, updated at no es problema. Pueden estar presentes en todas las tablas de un sistema OLTP, pero muchos sistemas también tienen una tabla de ‘Table Log’ donde cada registro de dicha tabla especifica algún cambio que se le ha hecho a alguna tabla dentro de la base de datos.
Espero que esto sea de tu ayuda.
Saludo,s
Miguel! Muchas gracias por tu respuesta! Entiendo lo que me dices. Con respecto a las claves foráneas no son null nunca, me refería a otros campos cualquiera, si no era contraproducente tener campos null.
Y lo otro es porque lo que he visto de normalización dice que campos repetidos en distintas tablas no lleva a que este en la 3 forma normal.
La tabla log también la tengo pero para otras cosas, los campos de auditoría son para saber quién creó y modificó y el momento para cada registro. En base a tu experiencia qué opinas?
Gracias!
puede ser que las columnas tengan los mismos nombres, pero según lo que me indicas cada una tiene un contexto diferente, pues están en diferentes tablas. Al final la pregunta que te debes hacer es si vas a necesitar esas columnas en tu modelo de datos o no. Si no van a tener ningún objetivo en tu modelo de datos, entonces mejor seria no importarlas, pero todo depende de como las vas a utilizar.
Hola!
Una duda sobre la relación de Tablas, como solucionar la relación de datos duplicados en el modelos de datos??
Perdón por la consulta en un tema viejo, pero estoy buscando info al respecto y pocos pueden decirme algo serio y con argumento.
Tener varios campos NULL en la tabla no rompe con la normalización?
Además te cuento que en un trabajo para un cliente tengo que auditar cada registro con los campos:
created_by -> clave foránea al id de la tabla usuario
created_at -> fecha de creación
updated_by -> clave foránea al id de la tabla usuario
updated_at -> fecha de modificación
Es requerimiento del cliente, mi duda es si esto también al repetirse en cada tabla no rompe con la normalización, más allá de esto tengo casi todas las tablas normalizadas en la 3fn, pero esto creo que me rompe con eso. Pensé en hacer 1 sola tabla llamada auditoria, y agregar estos campos y sumarle nombre de table y id de registro para relacionarla y no repetir todos estos campos en cada tabla.
Podrás orientarme un poco al respecto?
Muchas gracias de antemano y saludos!
Muchas gracias de antemano y saludos!
Hola!
Una duda sobre la relación de Tablas, como solucionar la relación de datos duplicados en el modelos de datos??
Hola!
Tengo una duda sobre las relaciones,
por ejemplo, tengo una tabla de activos, que estan catalogados por fecha, esa tabla la relaciono con un catalogo de fechas, hasta ahi esta ok
pero cuando agrego medidas en la tabla de activos, la relacion que hice con la tabla de fechas no me funciona ahi, sabes si es posible que las relaciones tambien funcionen con las medidas? o solo funcionan con los campos de las tablas predeterminadas?
Muchas gracias por el apoyo!
Hola!
Una duda sobre la relación de Tablas, como solucionar la relación de datos duplicados en el modelos de datos??
Hola.
Yo tengo dos tablas de hechos que tienen datos y operaciones que quiero comparar entre si, y a la vez con cálculos de variables procedentes de ambas tablas. La relación entre ellas es a través de una tabla de dimension, de la cual ambas tablas de hechos comparten a lo sumo dos campos. Cuando en la tabla coloco un campo de una tabla y otro campo de la otra tabla (que no son el campo que se ha relacionado), cuando agrego un slicer para segmentar los datos, este solo funciona con una de las variables y la otra queda sin efecto.
Podría darte mas informacion con un video para compartir, que te enviaría privado.
Gracias!
Hola Carlos!
Usualmente lo que comentas sucede porque el contexto de filtro no está «fluyendo» como lo esperas y podría ser necesario hacer cambios a tu modelo de datos o a la fórmula DAX que estás utilizando. Todo depende de cómo luce tu modelo de datos y cuál es el resultado que estás esperando.
Te invito a que nos contactes a través de nuestro formulario de contacto para obtener mayor información sobre nuestros servicios de consultoría remota:
https://www.thepoweruser.com/es/contacto/
Saludos!
Estoy realizando mi egreso de la Universidad y necesito consultar dudas.
Hola!
No estoy seguro si entiendo tu comentario, peero espero el artículo de arriba te haya ayudado con tus consultas.
Saludos!