Introducción •

La inteligencia en los negocios (o empresarial) es el conjunto de herramientas que facilitan la recopilación de datos y su posterior conversión en información significativa (conocimiento) para la toma de decisiones más acertadas para la empresa.

•

En este tema estudiaremos estas herramientas utilizando Microsoft Excel 2013.

•

Las capacidades de inteligencia empresarial en Excel 2013 son (entre otras): – Introducir cómodamente datos usando el “relleno rápido”. – Utilizar funciones de inteligencia de tiempo con DAX. – Crear informes, cuadros de mandos o paneles, haciendo uso de “análisis rápidos” o cuadros de mando con indicadores clave de rendimiento (KPI) y jerarquías representados mediante Power View. -2-

Relleno rápido •

Reconoce un patrón en los datos que estamos introduciendo.

•

Suponga que dispone de una tabla con una lista donde cada celda contiene un dato compuesto. Un ejemplo muy común lo tenemos en los dos apellidos y el nombre. Vea a continuación tres ejemplos. En todos ellos se ha escrito en la celda de arriba de la columna y después de INTRO se ha hecho CTRL+E (que activa el relleno rápido):

•

-3-

(dirección de correo: nombre_primerApellido_segundoAp [email protected])

Relleno rápido

• •

•

(nombre) (primer apellido y segundo apellido) Donde puede observarse qué fácil es crear direcciones de correo, extraer el nombre o los apellidos del cada campo. Para casos difíciles (apellidos compuestos, hay que repetir el patrón en la siguiente celda. Por ejemplo Álvarez-Osorio) -4-

Análisis rápido •

Con versiones anteriores de Excel para disponer de un formato condicional (y ver, por ejemplo, los datos con semáforos), o crear un gráfico o una tabla dinámica debíamos seguir una serie de pasos navegando por los distintos botones de los grupos en las fichas.

•

Es decir, para analizar los datos teníamos que realizar ciertas manipulaciones sobre los mismos.

•

Estas operaciones se han agrupado en un solo botón: Análisis rápido que, además, nos sugiere en algunas casos tablas o gráficos dinámicos que nos facilitan mucho el trabajo.

•

Para realizar este tipo de análisis podemos partir de cualquier rango de datos (en forma de rango normal o tabla).

-5-

Análisis rápido •

Bastará con que seleccionemos con el ratón el rango y nos coloquemos encima del botón que aparecerá (de forma automática) en la esquina inferior derecha:

-6-

Análisis rápido •

Aquí podremos escoger entre varias posibilidades:

– Formato: barra de datos, escalas de valores, conjuntos de iconos, etc. – Gráficos: varios tipos distintos de gráficos de barras y Más gráficos (sugeridos) o Todos los gráficos. – Totales: sumatorio, promedio, recuento, etc. – Tablas: obteniendo una tabla (para el caso de un rango normal), diferentes tablas dinámicas (sugeridas). – Minigráficos: de línea, columna y de ganancia o pérdida.

-7-

Análisis rápido •

Como ejemplo obtenga rápidamente el número de horas empleadas en cada proyecto:

-8-

Lenguaje de análisis de expresiones DAX

Es un lenguaje muy potente incorporado a Power Pivot. Aunque puede parecer prescindible en algunos casos, se hace imprescindible ya que Power View sólo utiliza la información contenida en el modelo (columnas, columnas calculadas, campos calculados, KPI y jerarquías).

Fórmulas DAX •

Las fórmulas DAX (como en Excel) empiezan por un signo igual seguido de una expresión o nombre de función y los argumentos o valores obligatorios.

•

Las fórmulas de DAX son diferentes porque: – Pueden utilizar un valor relacionado (de otra tabla relacionada) para realizar cálculos que varíen según el contexto. – Hay funciones que devuelven una tabla como resultado. Estas funciones suelen usarse como entrada a otras funciones (ya que no podemos representar una tabla en una celda).

– Existen funciones de inteligencia de tiempo que permiten hacer cálculos con intervalos de fechas y comparar los resultados en períodos paralelos. •

Puede crear fórmulas en PowerPivot bien en columnas calculadas (ventana Administrar de PowerPivot) o en campos calculados.

-10-

Funciones de agregación DAX •

En Excel las funciones SUM o AVERAGE permiten calcular la suma o la media de los valores de una columna.

•

DAX incluye funciones de agregación que reciben como argumento una tabla y una expresión (con lo que son bastante más potentes que las de Excel).

•

Los nombres en DAX son iguales pero con una X al final, así SUMX calcula la suma de una expresión evaluada en una tabla:

•

Un ejemplo lo tenemos en la siguiente fórmula (de Contoso):

•

= SUMX (FactSales;FactSales[UnitPrice]*FactSales[SalesQuantity])

•

Que calcula la suma en la tabla FactSales del producto de UnitPrice y SalesQuantity, es decir, el total de todas las ventas. Con lo que nos evitamos una columna calculada, [UnitPrice] * [SalesQuantity], y luego hacer la suma con SUM.

-11-

Funciones DAX de inteligencia de tiempo •

Uno de los cálculos más comunes realizados en análisis de datos es comparar resultados utilizando períodos de tiempo diferentes.

•

Esto está relacionado con el tratamiento de información histórica.

•

Este tipo de herramienta es muy útil para la inteligencia en los negocios.

•

Para poder utilizar estas funciones habremos de crear una tabla de una columna en Excel en la que cada fila sea una fecha diferente donde hayamos realizado algún tipo de transacción.

•

Incorporaremos dicha tabla al modelo y la relacionaremos (uno a muchos) con todas las tablas donde el campo fecha intervenga.

•

Tenga en cuenta que todas las tablas de fechas que devuelven (o manejan) las funciones de inteligencia de tiempo, son un subconjunto de la tabla de fechas creada.

•

Las funciones de inteligencia de tiempo se utilizan en campos calculados. -12-

Funciones con inteligencia de tiempo en DAX •

En la base Contoso esta tabla existe y se llama DimDate:

•

En realidad nos bastaría con la primera columna (el resto son funciones aplicadas a las misma).

•

DimDate está relacionada con las tablas FactSales (para las fechas en que se hicieron las ventas) y FactInventory (para determinar el tiempo de las entradas al inventario).

-13-

Funciones con inteligencia de tiempo en DAX •

Todas las funciones devuelven una tabla de una columna que contiene todas las fechas devuelta por la función (puede ser una única fecha).

•

Algunos ejemplos son:

PREVIOUSDAY (Date_Column), que devuelve la fecha anterior a la indicada en el argumento. PREVIOUSMONTH (Date_Column), que devuelve la tabla de fechas del mes anterior a una fecha. PREVIOUSQUARTER (Date_Column), igual que la anterior pero devuelve el cuatrimestre anterior a una fecha. PREVIOUSYEAR (Date_Column ), que devolverá el año anterior a una fecha. También tendremos ejemplos para el día siguiente o el siguiente año, etc. -14-

Funciones con inteligencia de tiempo en DAX •

DATESMTD (Date_Column), devuelve el mes de la fecha.

•

DATESQTD (Date_Column), devuelve el cuatrimestre de la fecha.

•

DATESYTD (Date_Column), devuelve el año de la fecha.

•

Mención aparte merece la siguiente función:

•

DATEADD(Date_Column; Number_of_Intervals; Interval), devuelve una tabla en la que cada fecha está desplazada hacia adelante o hacia atrás de acuerdo con el número especificado de intervalos de fechas en el contexto actual dado por Interval: DATEADD(fechas[clave_fecha]; -1; year) supone retrasar en un año todas las fechas del modelo.

•

Un modelo de datos va a ser para nosotros un conjunto de tablas (generalmente) relacionadas. -15-

Funciones con inteligencia de tiempo en DAX •

DATESBETWEEN(;;), devuelve las fechas comprendidas entre dos fechas dadas.

•

DATESINPERIOD(;;;) , devuelve las fechas en un periodo determinado por una fecha inicial, un número de intervalos y un interval0: Así, DATESINPERIOD(replantes[fecha];DATE(1981,01,01);-21;day)), devuelve una tabla donde la fecha indicada se retrasa 21 días.

-16-

Funciones DAX avanzadas •

Una de las más relevantes es CALCULATE(Expresión, SetFilter1, SetFilter2,...). Se trata de evaluar una fórmula DAX aplicándole una serie de filtros que, a menudo, vendrán dados por funciones de inteligencia de tiempo.

•

Como primer ejemplo de utilización de funciones de inteligencia de tiempo veamos estos dos campos calculados (en una tabla dinámica donde en filas tenemos los países y en columnas los años):

•

[VentasDeAlmacén] calculado con la fórmula:

•

= CALCULATE(SUM(FactSales[SalesAmount]);DimChannel[ChannelName]="Store"), es decir, sumar el total de las ventas de productos almacenados.

•

[VentasDeAlmacénAñoPasado] calculado como:

•

= CALCULATE([VentasDeAlmacén]; DATEADD(DimDate[DateKey]; -1; YEAR)), el total de ventas pero en el año anterior al dado en DateKey.

-17-

Funciones avanzadas de DAX •

Esto se traduce en que podemos comparar dicho total de ventas de un año con el anterior:

-18-

Funciones avanzadas de DAX •

Lo cual es bastante significativo para conocer la evolución de nuestro negocio (de ventas).

•

Gráficamente quizás sea más claro el análisis (se han quitado los países EEUU y UK):

•

Lo que nos indica que en la mayoría de los países cayeron las ventas en 2008 exceptuando Canadá, China o Japón (entre otros). -19-

Funciones avanzadas de DAX • •

•

Otra medida (o campo calculado) interesante sería el crecimiento relativo de las ventas dado por la fórmula (llame al campo calculado [CrecimientoAño]) =IF(and([VentasDeAlmacén];[VentasDeAlmacénAñoPasado]);([VentasDeAlma cén] [VentasDeAlmacénAñoPasado])/[VentasDeAlmacénAñoPasado];BLANK()), para evitar la división por cero si no hay ventas el año anterior (también tiene que haber ventas el año actual) Si el resultado es negativo las ventas han descendido, si es positivo, han aumentado (si es prácticamente la misma cantidad el resultado es cero):

-20-

Expresiones DAX cortas

•

Las fórmulas que hemos utilizado para los campos calculados se pueden escribir de otra forma más corta. De esta manera si la expresión es muy larga reduciremos notablemente su extensión.

•

En efecto, una vez que tenemos el campo calculado [VentasDeAlmacén], podremos expresar [VentasDeAlmacénAñoPasado] como:

•

= [VentasDeAlmacén](DATEADD(DimDate[DateKey]; -1; YEAR))

•

Como si [VentasDeAlmacén] fuese una tabla (que de hecho lo es), y le damos en el argumento la fecha (que es en realidad una tabla de fechas).

-21-

Inclusión de múltiples periodos en una fórmula

•

Al igual que hemos hecho en la obtención del campo calculado [CrecimientoAño], la condicional IF nos puede ser muy útil para expresar mediante una única (y compleja) fórmula el resultado de un campo calculado incluyendo el periodo de que se trate:

•

([Sales] es lo mismo que [VentasDeAlmacén])

•

De esta forma, si incluimos en la tabla dinámica el país y el periodo (de DimPeriod), obtendremos en cada columna las ventas por país y periodo. -22-

Inclusión de múltiples periodos en una fórmula •

Del ejemplo de Contoso (hoja SalesByPeriod) quitamos el campo del filtro y añadimos una escala temporal con el año 2008:

-23-

Inclusión de múltiples periodos en una fórmula • •

Lo primero que hace la fórmula es comprobar si DimPeriod tiene al menos dos valores, ya que si sólo tienen uno devuelve las ventas. En otro caso, calcula para cada país: – La primera columna son las ventas en el periodo actual (en el ejemplo el año 2008). – La segunda considera las ventas pero del último año. – La tercera son las ventas en el mes actual. – La cuarta en el mes actual hace un año. – La quinta en el cuatrimestre actual hace un año. – La sexta las ventas del año pasado. – La séptima en el cuatrimestre actual. – La octava son las ventas en el año actual.

-24-

Inclusión de múltiples periodos en una fórmula •

Gráficamente:

total ventas en el periodo

Ventas por países en distintos periodos

$1.000.000.000,00

YTD QTD PriorYearYTD PriorYearQTD

$500.000.000,00 $China País

France Germany

PriorYearMTD MTD LastYear

Current

•

Donde sólo hemos tomado datos de tres países y elegido uno de los diseños rápidos sugeridos para barras 3D (está girado el eje X unos 50º)

•

Realmente, si queremos comparar los datos, deberemos utilizar paneles como los que ofrece Power View (que estudiaremos más adelante). -25-

Power View

Es el entorno gráfico para el modelo almacenado por Power Pivot y permite crear uno o más tableros (o cuadros de mando) para obtener una visión global del negocio

Power View •

Esta herramienta gráfica (que se encuentra en el grupo Informes de la• ficha Insertar), permite reunir tablas, rangos normales y diversos gráficos en una vista interactiva que dinamiza la visualización de los datos.

•

Para crear un informe Power View (que no tienen relación con los generados desde Power Pivot), lo primero es partir de un modelo de datos o múltiples tablas interrelacionadas.

•

Dicho modelo puede modificarse si se agrega o quitan tablas o, mediante Power Pivot, añadimos columnas calculadas, campos calculados, KPI o jerarquías. - 27 -

Si nos vamos a la vista diagrama del modelo de datos (Contoso) podremos visualizar estos elementos:

Añadiendo gráficos al panel •

Si miramos el modelo encontramos en FactSales campos calculados (no están en la tabla sino en el modelo asociados a FactSales):

•

Estos campos son interesantes a la hora de utilizarlos en los informes de Power View como KPI (Indicadores Clave de Rendimiento. Este concepto lo desarrollaremos más adelante). - 28 -

Mapas •

La siguiente imagen es una muestra de la potencia de Power View a la hora de visualizar los datos por países:

- 29 -

Mapas •

Para obtener este mapa ha bastado con que hagamos Insertar->Power View y arrastrar a la caja campos el país (RegionCountryName) y las ventas(Sales), obteniendo una tabla (dinámica) con el resumen de las ventas por países.

•

Después en la ficha Diseñar escogemos la visualización de Mapa y en la ficha Diseño fondo de mapa aéreo.

•

Además en Etiquetas hemos escogido Centro y en Título Ninguno.

•

Como mapa que es, se puede mover (arriba, abajo, etc.) y podremos ampliarlo o disminuirlo.

•

Para resolver el problema de ambigüedad entre regiones de distintos países (hay una ciudad Córdoba en Argentina y otra en España), bastará con crear un campo compuesto por la provincia (región) y el país: =[provincia]&", "&[país] y tomarlo como campo en lugar de la provincia.

- 30 -

Matrices o Tablas dinámicas •

En los informes de Power View, una tabla dinámica se corresponde a la visualización Matriz (dentro de las opciones de Tabla, en el grupo cambiar visualización de la ficha Diseñar); aunque las tablas dinámicas son más potentes (en la Matriz tiene que existir una relación entre las tablas implicadas necesariamente, en las tablas dinámicas se sugiere).

•

Algo importante es que los informes (tanto de tablas como de gráficos dinámicos) que llevemos a cabo desde Power Pivot no se van a poder incorporar directamente a los paneles de Power View (a menos que las tablas implicadas estén vinculadas).

•

Así, la tabla dinámica de ventas por países y periodos no puede representarse en el panel de Power View porque la tabla DimPeriod no tiene relación con el resto de tablas.

- 31 -

Matrices o Tablas dinámicas •

Sin embargo, las ventas por países y para cada año, sí puede representarse, ya que existe relación entre las tablas DinDate y FactSales:

- 32 -

Matrices o Tablas dinámicas •

A esta matriz le podremos incorporar el campo calculado [cremientoAño] :

- 33 -

Gráficos dinámicos •

También podremos visualizar los datos utilizando distintos tipos de gráficos (dispondremos de plantillas más simples que las que vimos para tablas dinámicas con Power Pivot).

•

Sin embargo, hay dos diferencias muy notables en los gráficos de Power View: – Podemos tener en el mismo panel (o cuadro de mando) un número indeterminado de elementos gráficos (incluyendo Matrices o Mapas). Filtrados según unas restricciones globales. – La existencia de mosaicos que facilitan mucho la visualización de los resultados (son como un filtro donde nos podemos mover fácilmente por los distintos valores del campo). Dichos mosaicos también se establecen con las Tablas y Matrices.

- 34 -

Mosaicos •

Si disponemos de una tabla, matriz o un gráfico (para los mapas no está activo), tendremos un botón en el grupo Mosaicos que nos permite incorporar a la caja de mosaicos un campo con el que poder visualizar cómodamente los datos según los valores del mismo.

•

En el ejemplo que estamos estudiando, un campo interesante es el país, aunque también podría ser perfectamente el tipo de periodo (en realidad cualquier campo fila o columna de una matriz valdría):

- 35 -

Mosaicos •

Ahora nos podemos mover fácilmente con el ratón por los distintos valores del campo del mosaico.

•

Si cambiamos la visualización (grupo Cambiar visualización) a un gráfico de barras, tendremos para el mosaico periodo de tiempo:

- 36 -

KPI •

Un KPI es un campo calculado que indica si se han alcanzado o no unos objetivos marcados para el buen funcionamiento del negocio.

•

Por ejemplo, el departamento de ventas de una organización podría usar un KPI para ver si se superan las ventas cada periodo (es decir, si el campo calculado [crecimientoAño] es positivo o no).

•

El departamento de contabilidad podría medir los gastos mensuales frente a los ingresos para evaluar los costos.

•

Y el departamento de recursos humanos podría medir la rotación trimestral de empleados. Cada uno de ellos es un ejemplo de KPI.

•

Los profesionales de una empresa suelen usar KPI agrupados en un cuadro de mandos empresarial para para identificar tendencias.

•

Haremos uso de KPI y Power View para obtener Cuadros de mando.

- 37 -

KPI •

Un KPI incluye:

– Valor base: son medidas (campos calculados). – Valor de destino: son medidas o valores absolutos.

– Un estado: semáforo que muestra si un valor base ha alcanzado o no un valor destino. •

Como ya se ha comentado [crecimientoAño] puede servirnos de valor base de un KPI cuyo valor destino podría ser (si no somos muy optimista) 0,5 (las ventas se han incrementado en la mitad del año pasado) y representamos por un semáforo en rojo para valores negativos, amarillo para valores entre 0 y 0,25 y verde en otro caso.

- 38 -

KPI •

Si creamos un nuevo KPI con Power Pivot->Nuevo KPI:

- 39 -

KPI •

Con el KPI recién creado podremos representar la tabla dinámica de crecimiento relativo de ventas por año y país, obteniendo una visión rápida de dónde han ido mejor las ventas:

•

De donde podemos concluir que los KPI son semáforos (indicadores) que nos informan si va bien o no el negocio. - 40 -