Cuándo crear una tabla de búsqueda en Power Pivot

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: principiantes

Hoy explico cuándo es importante crear una tabla de búsqueda y cuándo está bien usar columnas nativas en una tabla de datos. He calificado este tema como un tema para principiantes, ya que es una habilidad fundamental que debe aprender en su viaje para convertirse en Power Pivot y Power BI Ninja. Sin embargo, hay mucho que aprender y solo puedes absorber una cantidad limitada de contenido antes de que tu cabeza comience a explotar. Debido a que hay mucho que aprender, es posible que se encuentre aprendiendo este tema por primera vez aunque sea un usuario intermedio. O puede volver a leer esto por segunda vez antes de que se asimile todo. Por lo tanto, este tema es bueno para todos, ya sea como una revisión, una confirmación de lo que sabe o como una nueva oportunidad de aprendizaje.

También vale la pena señalar que su tema no significa la diferencia entre “funciona” y “no funciona”. Puede tener éxito con Power Pivot y Power BI sin comprender bien este tema. Pero puede marcar una gran diferencia en la usabilidad de su modelo y, ciertamente, en el rendimiento (en algunos casos). Y no menos importante, si desea mejorar y mejorar sus habilidades, simplemente debe comprender bien estos principios.

Temas cubiertos en esta publicación

En esta publicación, cubro los siguientes escenarios para ayudar a explicar algunos de los matices y beneficios de usar tablas de búsqueda (o no):

  • Modelos de datos simples
  • Funciones de inteligencia de tiempo incorporadas
  • Para simplificar sus fórmulas DAX
  • Tablas de dimensiones sin sentido
  • Varias tablas de datos
  • Comunicación empresarial sencilla

Primero una definición de los 2 tipos de tablas

Antes de continuar, vale la pena replantear la definición de los 2 tipos diferentes de tablas para que quede claro en su mente.

Hay 2 tipos diferentes de tablas en un modelo de datos de Power Pivot. Hay tablas de dimensiones (las llamo tablas de búsqueda) y tablas de hechos (las llamo tablas de datos). Estos dos tipos de tablas son muy diferentes y comprender la diferencia es fundamental para comprender y usar Power Pivot y Power BI correctamente.

Tablas de búsqueda (tablas de dimensiones)

Las tablas de búsqueda contienen información normalmente sobre un concepto / objeto empresarial, por ejemplo, clientes, productos, tiempo, etc.

image_thumb-7146935

Las tablas de búsqueda siempre tienen las siguientes características:

  • Siempre hay una columna de datos que identifica de forma única cada fila de datos en la tabla.. En términos de base de datos, esta es la clave principal. En la tabla de clientes sería el número de cliente, en la tabla de productos es el código de producto, en la tabla de calendario es la fecha, y así sucesivamente para cualquier tabla de búsqueda.
  • No puede haber duplicados en esta columna de clave: cada fila debe ser única
  • Puede haber una o más columnas adicionales en cada tabla de búsqueda que brinden más información sobre esa columna de clave principal. Por ejemplo, en la tabla de búsqueda de calendario, la fecha es la clave principal. Otras columnas, como el nombre del día, el nombre del mes, el año, el número del día de la semana, etc., proporcionan detalles sobre la clave de fecha. A menudo me refiero a estas columnas adicionales como proporcionando metadatos sobre la clave principal.

Tablas de datos

Las tablas de datos contienen (normalmente) información transaccional. Pueden ser datos de ventas, datos de presupuestos, datos de GL de un sistema financiero, datos de llamadas del centro de llamadas o cualquier otro dato sobre actividades de algún tipo.

image_thumb-1-3328116

Las tablas de datos tienen las siguientes características:

  • No es necesario que una tabla de datos tenga una clave principal.
  • Debe haber una columna que se pueda conectar a las tablas de búsqueda relevantes (suponiendo que desee conectar estas tablas). En términos de base de datos, esta columna se denomina clave externa. Debería ser obvio que si desea unir su tabla de datos a su tabla de productos, debe tener una columna que defina el producto en su tabla de datos.
  • Se permiten duplicados en las columnas de la tabla de datos. por ejemplo, muchas ventas ocurren todos los días, muchas ventas ocurren para cada producto.

super-charge-power-bi-ad_1-1024x128-6595595

Uniendo las Mesas

No debe unir tablas de datos a otras tablas de datos. Las tablas de datos siempre deben unirse a las tablas de búsqueda utilizando la columna de clave principal de la tabla de búsqueda como columna de combinación.

Estoy usando la base de datos Adventure Works en la imagen a continuación. He colocado las tablas de búsqueda en mi modelo de datos en la parte superior de la ventana y la tabla de datos a continuación. Esta es la Metodología de Diseño de Collie; le da al usuario una pista visual de que las tablas que tiene que «buscar» para ver son las tablas de búsqueda, ¿lo entiende? No es obligatorio, solo es una forma fácil de recordar. Técnicamente, este diseño de datos se denomina esquema en estrella.

image_thumb-2-5764732

Cuándo usar tablas de búsqueda

Volviendo al punto de esta publicación. ¿Cuándo debería usar tablas de búsqueda y cuándo está bien usar simplemente la columna en su tabla de datos? Bueno, el hecho es que no «necesita» usar tablas de búsqueda para que su modelo de datos funcione; está perfectamente bien simplemente cargar una tabla de datos y comenzar desde allí. Habiendo dicho eso, sigue leyendo para descubrir los matices de algunos escenarios comunes.

Modelos de datos simples

Permítanme comenzar cubriendo cuando esté bien no use ninguna tabla de búsqueda. Si sus datos cumplen con el siguiente escenario, está bien ignorar las tablas de búsqueda:

  • Su conjunto de datos es pequeño. Lo que define a lo pequeño depende, por supuesto. Ciertamente, 10k – 250k filas de datos son pequeñas (en realidad, diminutas). Es menos importante almacenar sus datos de manera eficiente cuando su conjunto de datos es pequeño y, por lo tanto, una sola tabla de datos plana puede ser está bien.
  • Sus datos no contienen demasiadas columnas (este es un tema por derecho propio y fuera del alcance de esta publicación). Si tiene muchas columnas (por ejemplo,> 15), incluso 250.000 filas de datos pueden dejar de funcionar. Pero si tiene algunas columnas, quizás no necesite tablas de búsqueda.
  • Su tabla de datos ya contiene todas las columnas que necesita para su análisis.
  • No es necesario utilizar ningún cálculo de inteligencia de tiempo incorporado (más sobre eso a continuación).
  • Solo necesita escribir fórmulas DAX simples (como fórmulas SUM).

Si este es tu escenario, entonces no necesito tablas de búsqueda en absoluto. Sin embargo, si siempre opera en este escenario simple, no avanzará su conocimiento de DAX al siguiente nivel, así que téngalo en cuenta. Este escenario sin tablas de búsqueda es bastante raro y probablemente se limite a los usuarios de Excel que ya tienen sus datos en una sola tabla plana. También vale la pena señalar que una vez que llegue a escenarios más avanzados con fórmulas DAX, puede meterse en problemas serios a menos que cree un esquema en estrella. Este es un tema complejo y usted (y yo) no necesitamos entender las razones técnicas del por qué, solo necesitamos saber qué hacer para que funcione. Una vez que comience a escribir fórmulas más complejas usando CALCULATE (), le recomendamos que configure un esquema de inicio. Nunca escribiría una medida CALCULATE () sobre una mesa plana.

Funciones de inteligencia de tiempo incorporadas

Es obligatorio tener una tabla de búsqueda de calendario si desea utilizar las funciones de inteligencia de tiempo incorporadas de Power Pivot. Los ejemplos incluyen el cálculo de las ventas del año pasado, las ventas del año hasta la fecha, etc. Cubro las tablas de búsqueda de calendario en profundidad en este artículo aquí. Es posible hacer cálculos manuales de inteligencia de tiempo (no incorporados, sino manuales) usando una sola tabla de datos, pero no lo recomiendo. Si el tiempo es una parte importante de sus datos, le recomiendo encarecidamente que obtenga una tabla de búsqueda de calendario y continúe desde allí.

Para simplificar sus fórmulas DAX

Si bien es posible usar un modelo de datos simple con una sola tabla de datos y sin tablas de búsqueda (como se mencionó anteriormente), una vez que tenga necesidades de análisis más complejas (por ejemplo, además de usar SUM), deberá escribir algunas fórmulas DAX para crear los conocimientos empresariales. En mi experiencia, puede ser más fácil escribir fórmulas DAX cuando tiene tablas de búsqueda, especialmente cuando necesita cambiar el contexto del filtro en su modelo de datos. «Cómo» hacer esto también está fuera del alcance de este tema, pero tenga en cuenta que esta es otra razón para pasar de una sola tabla de datos.

Tablas de dimensiones sin sentido

No tiene sentido crear una tabla de búsqueda / dimensión sin una razón y ciertamente no si solo hay 2 columnas en la tabla de búsqueda. Esto tendrá más sentido con un ejemplo.

Tome el modelo de datos de Adventure Works como se muestra a continuación. Contiene una tabla de búsqueda (Productos) con 2 columnas, una para la clave del producto y otra para el nombre del producto. La tabla de búsqueda se une a la tabla de datos utilizando la clave principal (por supuesto).

image_thumb-3-4070096

Los datos de esta tabla de búsqueda se verían como los que se muestran a continuación: solo 2 columnas (pero muchas más filas de datos que se muestra en esta imagen, por supuesto).

image_thumb-4-3113739

Las personas que tienen algún conocimiento de las bases de datos relacionales tradicionales sabrán que las bases de datos relacionales almacenarán los datos en la primera columna (ProductKey) de manera mucho más eficiente que los datos en la segunda columna (Nombre del producto). Por lo tanto, es común pensar que es más eficiente almacenar la clave del producto en la tabla de datos y poner el nombre del producto solo en la tabla de búsqueda. Pero este no es el caso en Power Pivot y Power BI. Power Pivot utiliza el motor de almacenamiento xVelocity (almacén de columnas) para comprimir los datos. No hay (o poco) beneficio de ahorro de espacio al almacenar el código del producto en la tabla de datos en lugar de almacenar el nombre del producto. Además, cada relación en su modelo de datos tiene un costo / sobrecarga. Es por eso que esta tabla de búsqueda se llama dimensión basura. Si el único motivo por el que tiene esta tabla de búsqueda es para unir la columna única «Nombre del producto» a la tabla de datos a través de la clave del producto, entonces sería igual de bueno cargar el nombre del producto en la tabla de datos y soltar la clave del producto. juntos.

Si sus datos ya tienen el nombre del producto (y no la clave del producto) en la tabla de datos, y está pensando en crear esta tabla de búsqueda, entonces puede pensar de nuevo. Dicho esto, hay otras razones por las que es posible que desee mantener la clave principal y la tabla de búsqueda, incluidas

  • si tiene más de 2 columnas en su tabla de búsqueda.
  • si los nombres de sus productos no son únicos (a menudo, la clave del producto se administra de manera más rigurosa que el nombre)
  • si su tabla de datos ya tiene la clave de producto en la tabla y es más fácil hacerlo de esta manera.

para nombrar unos pocos.

Si su tabla de datos contiene una columna que no está relacionada con ninguna otra columna como un objeto lógico y se pregunta si debería crear una tabla de búsqueda para ella, la respuesta es no, no lo haga. Simplemente use la columna en su tabla de datos.

Varias tablas de datos

Como probablemente ya sepa, es posible tener varias tablas de datos en su modelo de datos. De hecho, esta es una de las muchas cosas buenas de Power Pivot.

image_thumb-5-1558335

Sin embargo, como mencioné al comienzo de esta publicación, no es posible unir una tabla de datos a otra tabla de datos en Power Pivot. Si desea utilizar varias tablas de datos en su modelo de datos, debe unirlas a través de tablas de búsqueda comunes. Por lo tanto, se deduce que si tiene varias tablas de datos en su tabla de datos, debe cargar tablas de búsqueda incluso si son dimensiones basura.

Comunicación empresarial simple

Existe un beneficio claro para los usuarios comerciales si agrupa lógicamente sus columnas de datos en tablas de búsqueda que tengan sentido comercial. Puede ser difícil para los usuarios si necesitan buscar información sobre un cliente o un producto en la tabla de datos, etc. La experiencia del usuario final es mucho mejor si hay agrupaciones lógicas de columnas de datos relevantes en una tabla llamada Cliente, Producto, etc. Dado que los usuarios finales pueden estar escribiendo sus propios informes utilizando la lista de campos para encontrar los datos relevantes, agrupar los datos en tablas de búsqueda puede mejorar realmente la experiencia del usuario final.

Aplanar sus tablas de búsqueda

Además del punto anterior, también debería considerar aplanar sus datos en una sola tabla de búsqueda si puede. El siguiente modelo de datos es factible y funcionará, pero difícilmente es fácil de usar. En la imagen de abajo hay una tabla de búsqueda de productos, y luego una tabla de búsqueda de subcategorías de la tabla de productos y una tabla de búsqueda de categorías de productos de la tabla de subcategorías.

image_thumb-6-2821136

Pero si implementa este modelo, es probable que resulte confuso para el usuario final. Recuerde que crearán informes utilizando la lista de campos y deberán buscar en diferentes tablas para encontrar las columnas de datos que utilizan. Es mucho mejor acoplar los datos de las tablas de búsqueda adicionales en una sola tabla de búsqueda de productos si puede. Además, aplanar estas tablas eliminará las relaciones adicionales y esto hará que el modelo sea más eficiente (cada relación tiene un costo). Y finalmente su DAX será más fácil de leer y escribir.

Envolver

Con suerte, este artículo ha ayudado a aclarar cuándo y dónde usar las tablas de búsqueda. Déjame saber tus pensamientos y / o cualquier pregunta que quede sin resolver en los comentarios a continuación.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.