Extracción: querys en SQL

Curso de Data Warehousing y Modelado OLAP

Contenido del curso

No sé dónde empezar

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

26
De datos crudos a modelo dimensional listo
01:18 min

Tomar examen

Cursos Empresas Blog Live Conf Eventos Precios

0:0017:28

Extracción: querys en SQL

Curso de Data Warehousing y Modelado OLAP

Clase 18 de 26

Clase 18 / 26

Comentarios (4)4 comentarios

ANDRES ALFONSO MIRA MEJIA

Hace 4 años

Como buena práctica en el desarrollo de data warehouse no se recomienda tener registros NULL en las dimensiones y/o hechos. Se sugiere cambiarlos por registros dummy, por ejemplo, asignar palabras dicientes de cada campo como (sin_telefono, sin_ciudad, no_registra…) o asignar el valor en blanco (" ").

Nicolás Muriel

Hace 3 años

A todos los interesados en el arte de data warehousing, de dimensional modeling, les recomiendo dbt, es una herramienta que es el estandar en la industria.

Soy Analytics Engineer y es mi herramienta principal, fundamental en lo que llamamos el "Modern Data Stack".

Seria buenisimo un curso de dbt por parte de platzi, hoy en dia no conozco una startup que no utilice dbt, y aqui nunca lo he escuchado en ningun curso.

Daniel da Silva Jarque

Hace 3 años

¿La herramienta que comentas es de CDATA?. ¿Puedes dejar una URL, x fa?. Gracias por tu aporte.

EDUARDO FLORES

Hace 3 años

Una consulta: Si las tablas en el datawarehouse no tienen relaciones como en las bases de datos transaccionales como hago para presentar información en una herramienta de bi y qeu esa relacion se mantenga cuando realizo los filtros?

Octavio Hurtado

Hace 2 años

en lo personal todos los BI que me ha tocado utilizar, después de que cargas las tablas necesarias, tienen un apartado para seleccionar en ambas tablas bajo que columna o campo se van a relacionar.

Adrian Andres Landazuri Rivas

Hace 3 años

De acuerdo con lo visto aqui, quiere decir que hay clientes creados que no existen en la tabla personas?

No debe existir primero la persona y luego el cliente?, dado que no todas las personas creadas deben ser necesariamente clientes, pero si todos los clientes son necesariamente personas?

Entiendo que esto aquí no tiene relevancia porque es para temas netamente académicos, pero si nos encontramos con problemas como estos en la vida real, cual sería la metodología mas correcta a seguir?

Nicolás Muriel

Hace 3 años

Hola Adrian, lo mas adecuado en estos casos es preguntar, directamente al equipo de backend, de engineering, de data engineering o incluso a las personas de negocio.

En mi experiencia, esto es bastante comun, los datos nunca son perfectos, seguramente se rompieron algunos datos en una migracion, por ejemplo migraron de AWS a GCP, o por ejemplo cambiaron de Stripe a Shopify para procesar datos, o hubo algun bug y se registraron mal los clientes.

Recursos

¿Cómo crear soluciones de bases de datos efectivas?

En el ámbito del Business Intelligence, construir una arquitectura sólida de bases de datos es clave para el proceso de toma de decisiones. La creación de tablas bien estructuradas y llenas de datos precisos es esencial. Revisemos los pasos y mejores prácticas para lograrlo.

¿Qué importancia tienen las tablas de dimensiones y hechos?

Al estructurar un Data Warehouse, se utilizan tablas de dimensiones y de hechos para organizar la información:

Tabla de Dimensiones: Define categorías de análisis (por ejemplo, productos, territorios, vendedores, tiempo) e incorpora campos de auditoría como fecha de carga y actualización.
Tabla de Hechos (FAQ): Se centra en los eventos procesables, vinculando todas las dimensiones necesarias mediante sus IDs.

¿Cómo se integra una dimensión de tiempo útil?

La dimensión de tiempo es fundamental y debe estar ajustada de acuerdo a:

Atributos Clave: Fecha, día, mes, año, día de la semana, día del año.
Utilidad: Permitir análisis temporales como mensuales, anuales o diarios.

Algunas herramientas de visualización como Power BI pueden generar esta dimensión automáticamente, pero estructurarla manualmente en modelos permite flexibilidad en el contexto de diferentes herramientas.

¿Cómo ejecutar extracción y transformación de datos?

Uno de los componentes más emocionantes del flujo de trabajo en BI es la creación del código para extracción y transformación de datos. Este proceso puede simplificarse con herramientas como Pentaho. Veamos cómo hacerlo de manera eficiente usando un ejemplo práctico: la dimensión de clientes.

¿Cómo se construye un Query detallado?

Para extraer la dimensión de clientes desde AdventureWorks a nuestro Data Warehouse, se siguen estos pasos:

Construcción Inicial:
- Empezamos con el campo CustomerID desde la tabla Customer.
```
SELECT C.CustomerID
FROM sales.Customer AS C
```

Incorporación de Datos Personales:

Datos de nombres y apellidos se extraen de Person.Person.

LEFT JOIN person.Person AS P
ON C.PersonID = P.BusinessEntityID
SELECT 
  (P.FirstName + ' ' + P.LastName) as NombreCompleto

Extracción de Teléfonos:
- Se realiza un LEFT JOIN con la tabla PersonPhone para obtener distintos tipos de teléfono.
```
LEFT JOIN person.PersonPhone AS PH
ON P.BusinessEntityID = PH.BusinessEntityID
```
Relación de Dirección:
- Para obtener la ciudad, se realiza una secuencia de JOINs desde Person.Person a Address.
```
LEFT JOIN person.BusinessEntityAddress AS B2
ON P.BusinessEntityID = B2.BusinessEntityID
```
Uso de Condiciones Específicas:
- Inclusión de condiciones WHERE para filtrar los datos, como solo incluir números de teléfono tipo 1 (celular).

¿Cómo se implementan estas transformaciones en Pentaho?

Después de la creación exitosa del Query SQL:

Integración en Pentaho: Copiar y pegar el Query en Pentaho para ejecutar transformaciones de datos.
Resultado esperado: Cumplir con la estructura necesaria para la tabla dimensional de clientes.

Construir tablas eficaces y aplicar un flujo de extracción, transformación y carga (ETL) preciso asegura que el Data Warehouse funcione de manera óptima para apoyar el proceso de decisión. Este tipo de trabajo detallado y considerado es esencial para cualquier proyecto de inteligencia de negocios exitoso. ¡Sigue aprendiendo y aplicando estas técnicas para dominar el mundo del BI!

Resumen

¿Cómo crear soluciones de bases de datos efectivas?

¿Qué importancia tienen las tablas de dimensiones y hechos?

Al estructurar un Data Warehouse, se utilizan tablas de dimensiones y de hechos para organizar la información:

Tabla de Dimensiones: Define categorías de análisis (por ejemplo, productos, territorios, vendedores, tiempo) e incorpora campos de auditoría como fecha de carga y actualización.
Tabla de Hechos (FAQ): Se centra en los eventos procesables, vinculando todas las dimensiones necesarias mediante sus IDs.

¿Cómo se integra una dimensión de tiempo útil?

La dimensión de tiempo es fundamental y debe estar ajustada de acuerdo a:

Atributos Clave: Fecha, día, mes, año, día de la semana, día del año.
Utilidad: Permitir análisis temporales como mensuales, anuales o diarios.

¿Cómo ejecutar extracción y transformación de datos?

¿Cómo se construye un Query detallado?

Para extraer la dimensión de clientes desde AdventureWorks a nuestro Data Warehouse, se siguen estos pasos:

Construcción Inicial:
- Empezamos con el campo CustomerID desde la tabla Customer.
```
SELECT C.CustomerID
FROM sales.Customer AS C
```

Incorporación de Datos Personales:

Datos de nombres y apellidos se extraen de Person.Person.

LEFT JOIN person.Person AS P
ON C.PersonID = P.BusinessEntityID
SELECT 
  (P.FirstName + ' ' + P.LastName) as NombreCompleto

Extracción de Teléfonos:
- Se realiza un LEFT JOIN con la tabla PersonPhone para obtener distintos tipos de teléfono.
```
LEFT JOIN person.PersonPhone AS PH
ON P.BusinessEntityID = PH.BusinessEntityID
```
Relación de Dirección:
- Para obtener la ciudad, se realiza una secuencia de JOINs desde Person.Person a Address.
```
LEFT JOIN person.BusinessEntityAddress AS B2
ON P.BusinessEntityID = B2.BusinessEntityID
```
Uso de Condiciones Específicas:
- Inclusión de condiciones WHERE para filtrar los datos, como solo incluir números de teléfono tipo 1 (celular).

¿Cómo se implementan estas transformaciones en Pentaho?

Después de la creación exitosa del Query SQL:

Integración en Pentaho: Copiar y pegar el Query en Pentaho para ejecutar transformaciones de datos.
Resultado esperado: Cumplir con la estructura necesaria para la tabla dimensional de clientes.

Introducción a BI y Data Warehouse

Qué es Business Intelligence y Data Warehousing

Niveles de analítica y jerarquía de datos

Qué son data warehouse, data mart y dimensiones

OLTP vs OLAP: diferencias clave

Inmon, Kimball y Hefesto en BI

Modelos dimensionales

Data Warehouse, Data Lake y Lakehouse

Modelo estrella vs copo de nieve en datos

Tipos de dimensiones lentamente cambiantes

Dimensión tipo 1: sobrescribir sin guardar historia

Dimensión tipo 2

Dimensión tipo 3: historia en columnas

Tabla de hechos (fact)

Configuración de herramientas para Data Warehouse y ETL

Cómo extraer dimensiones de preguntas de negocio

Diseño de tablas en un modelo dimensional

ETL para inserción en Data Warehouse

Documento de mapeo en ETL para data warehouse

Creando tablas dimensionales en Redshift

Extracción: querys en SQL

Cruce de fuentes en Pentaho con Stream Lookup

Transformación ETL con Pentaho paso a paso

Carga de datos transformados a Redshift con Pentaho

Cómo cargar la tabla de hechos con Pentaho

Cómo calcular MaxID y MaxDate en Pentaho

Orquestar ETL en Pentaho: job

Solución ETL con dimensiones en paralelo en Redshift

Cierre

De datos crudos a modelo dimensional listo

Extracción: querys en SQL

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos