Power BI Days Mallorca – Septiembre 2023
Siempre me encuentro indecisa sobre si participar o no en los eventos relacionados con Power BI, ya que aún estoy empezando y me queda mucho por aprender. Pero desde el inicio, mi querida compañera de charla Ana Mª Bisbé, me insistió en que mi experiencia en el mundo SQL Server podría ayudar mucho y a mucha gente, asi que me lancé a la piscina.
El tema escogido fue «Conecta con Power Query a SQL de forma eficiente» y hacerla junto con Ana Mª fue un acierto ya que ella es toda una experta en PowerQuery y yo podía aportar mucho en la parte relativa al SQL.
Empezamos la charla dando un repaso al Power Query y al plegado de consultas. El concepto de Plegado de consultas o Query Folding, para mi era aún algo abstracto hasta que de la mano de Ana Mª, preparamos esta charla. Un pequeño resumen:
- ¿Cuándo hay que tenerlo en cuenta? Cuando el origen es una base de datos relacional y en los casos de: Listas de SharePoint, Fuente OData, Active Directory o Microsoft Exchange
- ¿Qué es? Es cuando el origen se encarga de evaluar y procesar la consulta
- ¿Por qué es importante? Porque es más optimo.
Ana se encargó de explicar toda la parte del Power Query y poner ejemplos donde se rompía el plegado de consultas y yo me encargué de explicar las diferentes formas de optimizar y esquivar esa rotura del plegado del consultas. Algunas de las cosas que pudimos ver:
- Hay que evitar cogerse «todo» , solo lo que se necesite: para ello vimos algunos ejemplos de cómo afectaría en el motor hacer un SELECT * en vez de coger solo las columnas que necesitas, o poner un WHERE con un filtro concreto.
Algo similar nos encontramos si usamos la cláusula WHERE para filtrar y quedarnos solo con los registros que cumplen determinada condición. En el caso de la tabla pequeña, pasamos de 244 ms a 85 ms. Si hablamos de la tabla grande, entonces pasamos de 285.978 ms a 24.530 ms.
- Si hay algún paso que provoca la rotura del plegado de consultas, lo mejor es retrasarlo al máximo: es decir, hacer todo lo que puedas antes, ya que todo eso lo procesará el origen y desde el paso que rompe el plegado, será ya el Power Bi el encargado de procesarlo.
- La query que se construye automáticamente (izquierda) es más «compleja» que la que haría un desarrollador (derecha). Esto ayuda a quienes no saben SQL. Los que saben SQL, tendrán ventaja.
USE AdventureWorksDW2022
GO
SET STATISTICS TIME, IO ON
select [_].[ProductKey] as [IdProducto],
[_].[EnglishProductName] as [EnglishProductName],
[_].[t0_0] as [StandardCost],
[_].[ProductLine] as [ProductLine],
[_].[t1_0] as [DealerPrice],
[_].[Class] as [Class],
[_].[ModelName] as [ModelName],
[_].[EnglishDescription] as [EnglishDescription],
[_].[SpanishProductSubcategoryName] as [DimProductSubcategory.SpanishProductSubcategoryName],
left([_].[t0_02], 3) as [DimProductSubcategory.DimProductCategory.SpanishProductCategoryName]
from
(
select [_].[ProductKey] as [ProductKey],
[_].[EnglishProductName] as [EnglishProductName],
[_].[StandardCost] as [StandardCost],
[_].[ProductLine] as [ProductLine],
[_].[DealerPrice] as [DealerPrice],
[_].[Class] as [Class],
[_].[ModelName] as [ModelName],
[_].[EnglishDescription] as [EnglishDescription],
[_].[SpanishProductSubcategoryName] as [SpanishProductSubcategoryName],
[_].[SpanishProductCategoryName] as [SpanishProductCategoryName],
ceiling([_].[StandardCost]) as [t0_0],
ceiling([_].[DealerPrice]) as [t1_0],
upper([_].[SpanishProductCategoryName]) as [t0_02]
from
(
select [$Outer].[ProductKey],
[$Outer].[EnglishProductName],
[$Outer].[StandardCost],
[$Outer].[ProductLine],
[$Outer].[DealerPrice],
[$Outer].[Class],
[$Outer].[ModelName],
[$Outer].[EnglishDescription],
[$Outer].[SpanishProductSubcategoryName],
[$Inner].[SpanishProductCategoryName]
from
(
select [$Outer].[ProductKey] as [ProductKey],
[$Outer].[ProductSubcategoryKey2] as [ProductSubcategoryKey2],
[$Outer].[EnglishProductName] as [EnglishProductName],
[$Outer].[StandardCost] as [StandardCost],
[$Outer].[ProductLine] as [ProductLine],
[$Outer].[DealerPrice] as [DealerPrice],
[$Outer].[Class] as [Class],
[$Outer].[ModelName] as [ModelName],
[$Outer].[EnglishDescription] as [EnglishDescription],
[$Inner].[SpanishProductSubcategoryName] as [SpanishProductSubcategoryName],
[$Inner].[ProductCategoryKey] as [ProductCategoryKey2]
from
(
select [_].[ProductKey] as [ProductKey],
[_].[ProductSubcategoryKey] as [ProductSubcategoryKey2],
[_].[EnglishProductName] as [EnglishProductName],
[_].[StandardCost] as [StandardCost],
[_].[ProductLine] as [ProductLine],
[_].[DealerPrice] as [DealerPrice],
[_].[Class] as [Class],
[_].[ModelName] as [ModelName],
[_].[EnglishDescription] as [EnglishDescription]
from
(
select [ProductKey],
[ProductSubcategoryKey],
[EnglishProductName],
[StandardCost],
[ProductLine],
[DealerPrice],
[Class],
[ModelName],
[EnglishDescription]
from [dbo].[DimProduct] as [$Table]
) as [_]
where ([_].[Class] <> 'L' or [_].[Class] is null) or [_].[Class] is null
) as [$Outer]
left outer join [dbo].[DimProductSubcategory] as [$Inner] on ([$Outer].[ProductSubcategoryKey2] = [$Inner].[ProductSubcategoryKey])
) as [$Outer]
left outer join [dbo].[DimProductCategory] as [$Inner] on ([$Outer].[ProductCategoryKey2] = [$Inner].[ProductCategoryKey])
) as [_]
) as [_]
USE AdventureWorksDW2022
GO
SET STATISTICS TIME, IO ON
SELECT
p.[ProductKey] as [IdProducto],
p.[EnglishProductName],
ceiling(p.[StandardCost]) as [StandardCost],
p.[ProductLine],
ceiling(p.[DealerPrice]) as [DealerPrice],
p.[Class],
p.[ModelName],
p.[EnglishDescription],
s.[SpanishProductSubcategoryName],
left(upper(c.[SpanishProductCategoryName]),3) as [SpanishProductCategoryName]
FROM [dbo].[DimProduct] p
left outer join [dbo].[DimProductSubcategory] s on (p.[ProductSubcategoryKey] = s.[ProductSubcategoryKey])
left outer join [dbo].[DimProductCategory] as c on (s.[ProductCategoryKey] = c.[ProductCategoryKey])
where (p.[Class] <> 'L' or p.[Class] is null) - Lleva la transformación que rompe el plegado al origen:
- Con una Vista, que luego incluyes como origen del mismo modo que si fuera una tabla
- Usa NativeQuery, incluyendo el flag EnableFolding = True. En este caso se puede poner directamente la SELECT de SQL o incluso una ejecución de un Procedimiento Almacenado, pero si optas por esta opción, recuerda que sólo se tratará el primer result set que devuelva.
- Cualquier transformación se puede hacer en SQL: vimos algunos ejemplos, incluidos el pivotado de consultas con nombre de columnas dinámico.
Os dejo aquí el enlace a mi GitHub donde podréis encontrar:
- La presentación de la charla
- El archivo PBIX con los ejemplos
- Queries SQL de Demo con varios ejemplos
También os quiero dejar varios enlaces importantes:
- Documentación de Microsoft relacionada: https://learn.microsoft.com/es-es/power-bi/guidance/power-query-folding
- Web de mi compañera de charla Ana Mª Bisbé: https://amby.net
- Link a los libros de mi compañera Ana Mª, que explican todo esto y más, por si queréis comprarlos: https://www.amazon.es/Libros-Ana-María-Bisbé-York/s?rh=n%3A599364031%2Cp_27%3AAna+María+Bisbé+York
Excelente post y sesión en el #PowerBIDaysMallorca.
Tuve una gran compañera… ¡A ver qué otras sorpresas preparamos en un futuro!