Ampliando el modelo de datos

En este capítulo, aprenderá a ampliar el modelo de datos creado en los capítulos anteriores. La extensión de un modelo de datos incluye:

  • Adición de tablas
  • Adición de columnas calculadas en una tabla existente
  • Creación de medidas en una tabla existente

De estos, la creación de las medidas es crucial, ya que implica proporcionar nuevos conocimientos de datos en el modelo de datos que permitirán a los que utilizan el modelo de datos evitar la repetición de trabajos y también ahorrar tiempo al analizar los datos y la toma de decisiones.

Como el análisis de pérdidas y ganancias implica trabajar con períodos de tiempo y utilizará funciones de inteligencia de tiempo de DAX, necesita una tabla de fechas en el modelo de datos.

Si no está familiarizado con las tablas de fechas, consulte el capítulo Comprender las tablas de fechas.

Puede ampliar el modelo de datos de la siguiente manera:

  • Para crear una relación entre la tabla de datos, es decir, la tabla de datos financieros y la tabla de fecha, debe crear una columna calculada Fecha en la tabla de datos financieros.

  • Para realizar diferentes tipos de cálculos, debe crear relaciones entre la tabla de datos - Datos financieros y las tablas de búsqueda - Cuentas y ubicación geográfica.

  • Debe crear varias medidas que le ayuden a realizar varios cálculos y realizar el análisis requerido.

Estos pasos constituyen esencialmente los pasos de modelado de datos para el análisis de pérdidas y ganancias utilizando el modelo de datos. Sin embargo, esta es la secuencia de pasos para cualquier tipo de análisis de datos que desee realizar con el modelo de datos de Power Pivot.

Además, aprenderá cómo crear las medidas y cómo usarlas en las Power PivotTables en los capítulos siguientes. Esto le dará una comprensión suficiente del modelado de datos con DAX y del análisis de datos con Power PivotTables.

Agregar una tabla de fechas al modelo de datos

Cree una tabla de fecha para los períodos de tiempo que abarcan los años fiscales de la siguiente manera:

  • Cree una tabla con una sola columna con encabezado: fecha y fechas contiguas desde el 1 de julio de 2011 hasta el 30 de junio de 2018 en una nueva hoja de cálculo de Excel.

  • Copie la tabla de Excel y péguela en la ventana de Power Pivot. Esto creará una nueva tabla en el modelo de datos de Power Pivot.

  • Nombra la tabla como Fecha.

  • Asegúrese de que la columna Fecha de la tabla Fecha sea del tipo de datos: Fecha (Fecha y hora).

A continuación, debe agregar las columnas calculadas: año fiscal, trimestre fiscal, mes fiscal y mes a la tabla de fecha de la siguiente manera:

Año fiscal

Supongamos que el cierre del ejercicio es de junio de 30 de ju . A continuación, un fiscal vanos años desde el 1 er julio al 30 º mes de junio. Por ejemplo, el período de julio 1 st , 2011 (7/1/2011) a junio 30 de XX de 2012 (6/30/2012) será el año fiscal 2012.

En la tabla Fecha, suponga que desea representar lo mismo que el año fiscal 2012.

  • Primero debe extraer la parte del año financiero de la Fecha y agregarla con el año fiscal.

    • Para las fechas comprendidas en los meses de julio de 2011 a diciembre de 2011, el ejercicio económico es 1 + 2011.

    • Para las fechas comprendidas en los meses de enero de 2012 a junio de 2012, el ejercicio económico es 0 + 2012.

    • Para generalizar, si el mes del fin del año financiero es FYE, haga lo siguiente:

      Integer Part of ((Month – 1)/FYE) + Year

    • A continuación, tome los 4 caracteres de la derecha para obtener el año financiero.

  • En DAX, puede representar lo mismo que:

    DERECHA (INT ((MES ('Fecha' [Fecha]) - 1) / 'Fecha' [FYE]) + AÑO ('Fecha' [Fecha]), 4)

  • Agregue la columna calculada Año fiscal en la tabla Fecha con la fórmula DAX -

    = "FY" & RIGHT (INT ((MES ('Fecha' [Fecha]) - 1) / 'Fecha' [FYE]) + AÑO ('Fecha' [Fecha]), 4)

Trimestre fiscal

Si FYE representa el mes de cierre del año financiero, el trimestre financiero se obtiene como

Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)

  • En DAX, puede representar lo mismo que:

    INT ((MOD (MES ('Fecha' [Fecha]) + 'Fecha' [FYE] -1,12) +3) / 3)

  • Agregue la columna calculada Trimestre fiscal en la tabla Fecha con la fórmula DAX -

    = 'Fecha' [Año fiscal] & "- Q" & FORMATO (INT ((MOD (MES ('Fecha' [Fecha]) + 'Fecha' [FYE] -1,12) + 3) / 3), "0" )

Mes fiscal

Si FYE representa el final del año financiero, el período del mes financiero se obtiene como

(Remainder of (Month+FYE-1)/12) + 1

  • En DAX, puede representar lo mismo que:

    MOD (MES ('Fecha' [Fecha]) + 'Fecha' [FYE] -1,12) +1

  • Agregue la columna calculada Mes fiscal en la tabla Fecha con la fórmula DAX -

    = 'Fecha' [Año fiscal] & "- P" & FORMAT (MOD (MES ([Fecha]) + [FYE] -1,12) +1, "00")

Mes

Finalmente, agregue la columna calculada Mes que representa el número de mes en un año financiero de la siguiente manera:

= FORMAT (MOD (MES ([Fecha]) + [FYE] -1,12) +1, "00") & "-" & FORMAT ([Fecha], "mmm")

La tabla de fechas resultante se parece a la siguiente captura de pantalla.

Marque la tabla - Fecha como Tabla de fecha con la columna - Fecha como la columna con valores únicos como se muestra en la siguiente captura de pantalla.

Agregar columnas calculadas

Para crear una relación entre la tabla Datos financieros y la tabla Fecha, necesita una columna de valores Fecha en la tabla Datos financieros.

  • Agregue una columna calculada Fecha en la tabla Datos financieros con la fórmula DAX -

    = DATEVALUE ('Datos financieros' [Mes fiscal])

Definición de relaciones entre tablas en el modelo de datos

Tiene las siguientes tablas en el modelo de datos:

  • Tabla de datos: datos financieros
  • Tablas de búsqueda - Locn de cuentas y geografía
  • Tabla de fechas - Fecha

Para definir relaciones entre las tablas en el modelo de datos, los siguientes son los pasos:

  • Vea las tablas en la Vista de diagrama de Power Pivot.

  • Cree las siguientes relaciones entre las tablas:

    • Relación entre la tabla Datos financieros y la tabla Cuentas con la columna Cuenta.

    • Relación entre la tabla Datos financieros y la tabla Ubicación geográfica con la columna Centro de beneficio.

    • Relación entre la tabla Datos financieros y la tabla Fecha con la columna Fecha.

Ocultar columnas de las herramientas cliente

Si hay columnas en una tabla de datos que no utilizará como campos en ninguna tabla dinámica, puede ocultarlas en el modelo de datos. Entonces, no estarán visibles en la lista Campos de tabla dinámica.

En la tabla de datos financieros, tiene 4 columnas: mes fiscal, fecha, cuenta y centro de ganancias que no usará como campos en ninguna tabla dinámica. Por lo tanto, puede ocultarlos para que no aparezcan en la lista Campos de tabla dinámica.

  • Seleccione las columnas: Mes fiscal, Fecha, Cuenta y Centro de beneficios en la tabla Datos financieros.

  • Haga clic con el botón derecho y seleccione Ocultar de las herramientas cliente en la lista desplegable.

Crear medidas en las tablas

Ya está listo para el modelado y análisis de datos con DAX utilizando el modelo de datos y Power PivotTables.

En los capítulos siguientes, aprenderá cómo crear medidas y cómo usarlas en Power PivotTables. Creará todas las medidas en la tabla de datos, es decir, la tabla de datos financieros.

Creará medidas usando fórmulas DAX en la tabla de datos - Datos financieros, que puede usar en cualquier cantidad de tablas dinámicas para el análisis de datos. Las medidas son esencialmente los metadatos. Crear medidas en la tabla de datos es parte del modelado de datos y resumirlas en Power PivotTables es parte del análisis de datos.