Entendiendo Tablas, Relaciones y Modelado de Datos en Power Pivot

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:

  1. Tipos de tablas y sus atributos
  2. Llaves Primarias y Foráneas
  3. Normalización vs Denormalización
  4. 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]
  • Facebook
  • Twitter
  • LinkedIn

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.

Más filas es siempre una mejor opción que más columnas

Más columnas es a menudo una mejor opción que más tablas si la cantidad de filas es significativamente pequeña

Posted in: