Análisis de datos e inteligencia de negocio (Ad y In) con Excel y Power BI. Data Analysis and Business Intelligence (DA and BI)

Casos Propuestos (Mis Casos)

1 Casos ETL y Modelado de Datos

2 Casos TD y DAX Básico

3 Casos Power BI y DAX Básico

4 Casos propuestos para su desarrollo

1 Casos ETL y Modelado de Datos

Los conceptos a aplicar a los casos propuestos son los siguientes:

Estudio del caso

Diseño del esquema de modelo de datos

Conocer el objetivo y reflexionar sobre las preguntas clave a las que debe dar respuesta los informes a diseñar, es decir que debe contener el informe a diseñar, indicadores KPis, etc

Este análisis previo determina los datos que necesitamos y confirmar de que información disponemos para incorporar al modelo.

Identificación de fuentes necesarias y/o disponibles para incorporar al modelo de datos e identificar tabla de hechos y tabla/s de dimensión.

Modelo Estrella.

Relaciones y tipos

Es altamente recomendable comenzar con una hoja en blanco e intentar pensar en cómo será el modelo de datos según el proceso de negocio que se quiere analizar e identificar todos los filtros o dimensiones asociados.
 
   
Caso Conceptos y técnicas requeridas Ficheros relacionados
101 Empleados

Creacion de una consulta, conexión a un fichero fuente no normalizado.
Uso de las opciones “Quitar Filas Superiores“ y “Encabezados Promovidos”  opcion Usar la Primera fila como encabezado
Aplicamos filtros para no disponer de determinadas filas, por ejemplo que contiene null en una determinada columna. 
Uso de la opcion “Rellenar Abajo” que accedemos usando el botón derecho sobre la columna deseada y nos da acceso entre otras opciones a esa.
Limpieza. Eliminar los registros que tienen espacios al comienzo y o al final, usando la opcion Transformar-Recortar.
Separar nombre y apellido, con la opcion “Dividir Columnas por delimitador
Uso de “Remplazar Valores”, por ejemplo, cambiar null por 0
Cambiar tipo de datos de las columnas a traves del icono izquierdo

Original
Propuesta

 

102 Ventas 7009 dos tablas Creacion de una consulta, conexión a un fichero fuente no normalizado. Relacionar dos consultas. Combinar-Combinar Consultas Uso de las opciones “Quitar Filas Superiores “y “Encabezados Promovidos” opcion Usar la Primera fila como encabezado. Quitar filas en blanco Quitar columnas. Cambiar tipo. Dividimos columnas por número de caracteres y creación de una columna personalizada por ejemplo: [Compañía Cliente.1]&" "&[Compañía Cliente.2] Limpieza. Eliminar los registros que tienen espacios al comienzo y o al final, usando la opcion Transformar-Recortar. Uso de “Remplazar Valores”, por ejemplo, cambiar null por 0. Quitar espacios en blanco y caracteres no deseados como guiones, puntos, etc. Transformación sobre campo fecha, en año, trimestre, semanas, etc. Original
Propuesta
103 Quantity Muy sencillo. Creacion de una consulta, conexión a un fichero fuente no normalizado tipo CSV.
Uso de las opciones “Encabezados Promovidos” opcion Usar la Primera fila como encabezado
Aplicamos filtros para no disponer de determinadas filas, por ejemplo, que contiene NA en una determinada columna. Quitar vacíos Cambiar tipo de datos de las columnas a traves del icono izquierdo, pasamos de texto a numero entero
Original
Propuesta
104 SABI - Revisado Fundamental. Encabezados promovidos, quitar columnas, columna de anulación de dinamización, dividir columnas por transición de caracteres o separando por últimos caracteres, columnas por nombre cambiado, tipo cambiado de columnas (texto por numero), quitar errores, etc. Original
Propuesta
105 Diario A3 En este caso vamos a estudiar como importar un diario contable del programa A3 en formato no estructurado y convertirlo en un fichero normalizado con el editor Power Query.
Realizar una consulta con Power Query a un origen de datos Excel con información a varias pestañas.
Filas filtradas, filas superiores quitadas
Columnas quitadas, columnas reordenadas, columna condicional agregada, tipo cambiado, encabezados promovidos, personalizada agregadas
Otras como se expandió DATA, índice agregado, rellenar hacia abajo, primeros caracteres insertados, últimos caracteres insertados.
Original
Propuesta
106 Diario y Plan txt

Realizar una consulta dos orígenes de datos de texto plano. Cambiar manualmente el nombre a las columnas. Agregar Columna – Columna Condicional agregando condiciones y fórmulas. Combinar dos Consultas. Creacion de una consulta referenciada. Otras acciones como Transformar-Agrupar por.

Original
Propuesta
107 EBI Diario PQ y PPivot Power query preparación de un diario de contabilidad con Power Query y Power Pivot. Mi primer modelo de datos y tabla dinámica. Realizar una consulta dos orígenes de datos fichero Excel.
Depuración en columnas. Cambiar manualmente el nombre a columnas. “Agregar Columna – Extraer – Primeros Caracteres”. “Cambiamos el tipo”. Eliminamos aquellas columnas vacías. Remplazamos null por 0 con Inicio-Remplazar Valores,
Limpiamos filas. “Quitar Filas – Quitar Filas Superiores”, Transformar – Usar la primera fila como encabezado
Combinar dos Consultas. Creacion de una consulta referenciada
Otras acciones como: Combinación de consulta. con la opcion “Inicio - Combinar Consultas”. Creacion de nuestro primer modelo de datos con Power Pivot. Creación de nuestra primera tabla dinámica basada en modelo de datos.
Original
Propuesta
108 Ventas por empleados Años Anexar varias consultas en una sola. Combinar consulta con relación múltiple, varios campos. Conectar Carpetas
Realizar una consulta dos orígenes de datos fichero Excel. Anexar varias consultas en una nueva. Combinar dos Consultas con relación múltiple. Conectar carpetas
Original
Propuesta
     

2 Casos TD y DAX Básico

Los conceptos a aplicar a los casos propuestos son los siguientes

Conceptos básicos

Aplicando Formato a las TD y GD

Ordenar, filtrar y agrupar TD y GD

Enriquecer datos con campos y elementos calculados en TD y GD

Creación de TD basada en un rango, tabla y modelo de datos.
Explorar las pestañas de "Análisis" y "Diseño"
Actualizar tablas dinámicas

Cambiar el formato de números
Formato de celdas en blanco
Aplicar formatos condicionales

Opciones de ordenar
Filtros de etiqueta y selecciones manuales. Filtros de etiqueta con comodines
Filtros de valor
Agrupar datos. Agrupar fechas automáticamente
Usar segmentación de datos y escalas de tiempo

Modos de resumir valores
Cálculos de "Mostrar valores como".: % del total de columnas/filas,  % del total principal, Diferencia de…
Definir campos calculados

 

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
201 Línea Blanca Marrón y PAE Contamos con los registros de ventas del ultimo periodo de Línea Blanca (neveras, lavadoras, secadoras, lavavajillas, etyc..) Línea Marrón (Aparatos electrónicos de consumo TV, HIFI, Sonido…) y PAE (pequeños electrodomésticos, planchas, Freidoras, Aspiradoras, cafeteras, exprimidores y hervidores, etc..) en un fichero Excel en bruto sin formato. En base a la información disponible, las preguntas basicas a responder están relacionadas con:
Datos de ventas por región, producto y vendedor. Filtro y selección por estos parámetros.
Informe de venta con gráficos relacionados.

Desarrollar informes similares a los propuestos en las siguientes ilustraciones aplicando los conceptos y técnicas estudiadas.

Original
Propuesta
202 Finanzas de SKU-DON

Contamos con un informe en bruto de los registros de nuestra aplicación CRM sobre la facturación a lo largo del último periodo, total de registros 7.080. En la otra pestaña contamos con las bases de clientes (93) y productos (344). En base a la información disponible, las preguntas basicas a responder están relacionadas con:

  • Análisis de los Ingresos, filtrando por país, por producto, el top 5 de clientes, ingresos por periodo, por producto y el top 5 de productos.
  • Resultados y márgenes por región y clientes. Análisis del beneficio y márgenes por región y tipo de clientes.
  • Resultados y márgenes por tipo de prodcuto. Análisis del beneficio y márgenes por tipo de producto.
Desarrollar informes similares a los propuestos en las siguientes ilustraciones aplicando los conceptos y técnicas estudiadas.
Original
Propuesta
203 Llamadas Call Center Contamos con los registros de las llamadas recibidas de atención al cliente de nuestra empresa y nuestro aplicativo de control y registro presenta un fichero en csv. En base a la información disponible, las preguntas basicas a responder están relacionadas con:
  • Análisis de las llamadas en el periodo
  • Análisis de las llamadas por intervalo horario
  • Análisis de las llamadas por día de la semana
  • Y algunas otras cuestiones que puedan resultar de su interes

Desarrollar informes similares a los propuestos en las siguientes ilustraciones aplicando los conceptos y técnicas estudiadas.

Original
Propuesta
204 Estudio de Mercado de VideoJuegos Queremos llevar a cabo un análisis del mercado de videojuegos a nivel mundial y para ello nos han facilitado la siguiente información normalizada en un fichero csv. Debemos realizar entre otras operaciones las siguientes: campo aplicar “Anular dinamización de columnas”. Quitar la palabra ventas y con la opcion extraer texto despues del delimitador espacio Original
Propuesta
     
     

3 Casos Power BI y DAX Básico

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
300 Power BI Básicos I

Relación de casos básicos de Power BI de introducción al análisis y potencial de esta herramienta. Documentos correspondientes al curso de Ana María Bisbé York - Power BI esencial (Enlace)

Fuente
301 Liena Blanca, Marron y PAE Caso básico de Power BI basado en el 201 Línea Blanca Marrón y PAE Fuente
Solución
301b Vendedores y Presupuestos v1 Caso básico de Power BI primeras visualizaciones Fuente
Solución
302 Mercado del Video Juego Caso básico de Power BI basado en el 204 Estudio de Mercado de VideoJuegos Fuente
Solución
310 Recursos Humanos

Trabajando con campos fechas creacion de columna fecha de referencia, Edad de Referencia y Edad Actual. Uso de la opcion Columna Condicional procedemos a crear cuatro grupos de edad. Modelo de datos y relaciones simples. Tablas y graficos dinámicos basicos basados en modelo de datos.
Trabajando con DAX, creacion de columnas y medidas básicas: Uso de COUNT, AVERAGE, IF

Original
Propuesta
311 Deuda de Clientes Creación de columnas y medidas básicas: Uso de SUM, IF, Year y separando columnas Original
Propuesta
312 Análisis de ventas por países Medidas: SUM y SUMX
Visualizaciones: Gráfico De Anillos - Gráfico De Cascada- Gráfico De Cintas- Gráfico De Columna Agrupada - Gráfico De Columna Apilada- Gráfico De Líneas – Mapa - Matriz Con Jerarquía – Segmentadores – Tabla - Tarjeta
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Original
Propuesta
313 Aplicando otras medidas 1 Medidas: SUMX, COUNTROWS, DISTINCTCOUNT SUMX con RELATED, DIVIDE
Visualizaciones: Tabla - Matriz
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Propuesta
314 Aplicando otras medidas 2 CALCULATE Medidas: CALCULATE
Visualizaciones: Matriz, Tarjeta y Segmentadores
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Propuesta
315 Comprar datos con SAMEPERIODLASTYEAR Medidas: SAMEPERIODLASTYEAR
Visualizaciones: Matriz y Segmentadores
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Original
Propuesta
316 Ventas Canarias SUMX con RELATED

Query: Copiar consulta, separar columnas, quitar columnas, eliminar filas
Medidas: SUM, SUMX con RELATE, DIVIDE
Visualizaciones: Tarjeta, Mapa, Matriz y Segmentadores

Original
Propuesta
317 Ventas Canarias v.2 CALCULATE v otros Medidas: COUNTROWS, AVERAGE, DIVIDE y CALCULATE
Visualizaciones: Tarjeta y Segmentadores.
Original
Propuesta
318 H&8 Distribuciones Medidas: SUM y DIVIDE
Visualizaciones: Tarjeta, segmentadores, cuadro de texto, gráfico de barras apilada, matriz y gráfico de columnas apilada
Datos
Propuesta
Visualizacion
     
320 Control de Calidad y KPI TD y DAX. Introducción a las medidas y a los KPI. Conceptos teóricos relacionados con los KPI Original
Propuesta
321 Control de Calidad y KPI TD y DAX. Introducción a las medidas y a los KPI. Conceptos teóricos relacionados con los KPI Original
Propuesta
     
     
     
     

 

4 Casos propuestos para su desarrollo

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
900 TPV I

Información facilitada del registro de TPV correspondiente a una cafetería. Entre los aspectos técnicos a considerar se requiere aplicar los siguientes:

  • Crear rangos de edad de los camareros, para la fecha máxima de los datos de ticket.
  • Crear y trabajar con tabla calendario para dias de la semana, mes, etc..
  • Contemplar franjas horarias para el análisis (mañana y tarde o mañana, mediodía y tarde, etc.)
  • Crear KPI

Fuente

901 Atencion Cliente suministro eléctrico Contamos con el registro diario de la actividad relacionado con la comercialización y gestión de un servicio de atención al público perteneciente a una empresa colaboradora de una compañía nacional del sector eléctrico de nuestro país.. Crear y trabajar con tabla calendario. Geolocalización de datos Fuente
902 APP TAXI

Contamos con el registro diario de la actividad relacionado con la APP TAXI que ofrece los servicios de contratación de Taxi en distintos municipios de nuestra isla. Entre los aspectos técnicos a considerar se requiere aplicar los siguientes:

  • Crear y trabajar con tabla calendario para dias de la semana, mes, etc
  • Aplicación de KPI
Fuente