Power BI Direct Query permite a los usuarios crear una conexión directa con la base de datos y trabajar sin importar los datos a Power BI. Hay muchas preguntas sobre lo que funciona y lo que no. En el segundo artículo de la serie, Decodificación de consulta directa, exploraremos cómo determinar la diferencia de fechas entre dos tablas. La consulta directa no admite la nueva columna, por lo tanto, tomar la diferencia entre las fechas de la misma tabla también requería un enfoque similar.
Configuración:
La última vez usamos SQL Server Express Edition, pero esta vez decidimos experimentar con Oracle. La edición Oracle 18c Express está disponible en Oracle. Esta edición también es compatible con Direct Query. Y hemos instalado lo mismo. También se requiere la instalación del cliente de Oracle para acceder a los datos en Power BI. Si bien la instalación es fácil, si olvida proporcionar la ruta del controlador, en la variable de ruta del entorno de Windows, después de la instalación, no funcionará.
Las preguntas que vamos a abordar:
- ¿Hay alguna diferencia entre SQL Server y Oracle para la función de inteligencia de tiempo utilizada en el último artículo?
- Cómo obtener la diferencia de fechas entre dos días en dos tablas diferentes.
- ¿Las fórmulas de diferencia de fecha son similares en SQL Server y Oracle?
Paso 1:
Conecte Oracle y, mientras se conecta, elija la opción de consulta directa.
Paso 2:
Arrastre todas las tablas requeridas a Power BI y cree el diagrama de relaciones como se indica a continuación. Hemos tomado dos hechos y puedes ver que ambos están conectados a las dimensiones comunes. La diferencia con la última vez es que tenemos una dimensión Orden común adicional. Para mantener el experimento igual, hemos agregado la misma dimensión a nuestro servidor SQL pbix. Esto significa que tanto las bases de datos como pbix son iguales ahora.
Hay algunas diferencias.
- Oracle no nos permitió nombres como Fecha y Orden como nombres de tabla y columna, por lo que cambiamos el nombre de algunas columnas. Hay formas en que podemos conservar esos nombres. Pero prefiero cambiar esos.
- Todos los nombres de las tablas están en mayúsculas en el caso de Oracle cuando los trasladamos a Power BI.
servidor SQL
Paso 3: marcar el horario
Lo mismo que hicimos la última vez. Simplemente haga clic con el botón derecho en la tabla en la pestaña Visualización.
Paso 4: cambiar el tipo de datos
Haga clic en cualquier campo/columna de la tabla. Verá las herramientas de columna como una de las pestañas. Tiene la opción de cambiar el tipo de datos allí. Quería hacer el año como texto en la dimensión de la fecha.
Paso 5: Cambiar la columna Ordenar
Nuevamente, en Herramientas de columna, tiene la opción de Ordenar por columna. Usa eso. Hemos cambiado el tipo de Mes Año.
Paso 6: Crear fórmulas
Nuestros datos no tienen ventas brutas ni ventas netas como valores. Entonces, necesitamos medidas. Ahora, estos cálculos son necesarios a nivel de línea. Entonces, usamos la función sumx ya que no tenemos la opción de crear una columna.
Gross Sales = sumx('ORDERS','ORDERS'[Qty]*'ORDERS'[Price])
Net Sales = sumx('ORDERS',('ORDERS'[Qty]*'ORDERS'[Price])* (100-'ORDERS'[Discount_Percent])/100)
Discount Amount = sumx('ORDERS',('ORDERS'[Qty]*'ORDERS'[Price])*'ORDERS'[Discount_Percent]/100)Paso 7: Cree la fórmula de inteligencia de tiempo
Hemos creado fórmulas usando funciones de fechas como fechasytd, fechasqtd y fechasmtd.
No encontramos ninguna diferencia en los usos de las funciones de fechas*. Pero inicialmente, encontramos que Oracle es lento. Creamos índices en la publicación de la tabla Oracle de que esa velocidad era casi la misma. La parte impresionante fue el caché de Power BI.
Net Sales LMTD = CALCULATE([Net Sales],DATESMTD(DATEADD(DATEDIM[DDATE],-1,MONTH)) )
Net Sales LQTD = CALCULATE([Net Sales],DATESQTD(DATEADD(DATEDIM[DDATE],-1,QUARTER)))
Net Sales LYMTD = CALCULATE([Net Sales],DATESMTD(DATEADD(DATEDIM[DDATE],-1,YEAR)) )
Net Sales LYQTD = CALCULATE([Net Sales],DATESQTD(DATEADD(DATEDIM[DDATE],-1,YEAR)) )
Net Sales LYTD = CALCULATE([Net Sales],DATESYTD(DATEADD(DATEDIM[DDATE],-1,YEAR)) )
Net Sales MTD = CALCULATE([Net Sales],DATESMTD(DATEDIM[DDATE]))
Net Sales QTD = CALCULATE([Net Sales],DATESQTD(DATEDIM[DDATE]))
Net Sales YTD = CALCULATE([Net Sales],DATESYTD(DATEDIM[DDATE]))
Paso 8: Cree fórmulas para la diferencia de fechas.
La diferencia de tiempo que usa la medida requiere un contexto (agrupar por), por lo que hemos usado el número de orden aquí de la dimensión de orden que es una de las tablas comunes entre dos. Las funciones Valores y Resumir nos ayudaron a lograrlo.
Max Delivery Date = max(DELIVERY[DEILVERY_DATE])
//Oracle
Min Order Date = MIN(ORDERS[SALES_DATE])
Date Diff = AVERAGEX(VALUES(ORDERDIM[ORDER_NO]) ,datediff([Min Order Date],[Max Delivery Date],DAY))
Date Diff 2 = AVERAGEX(SUMMARIZE(ORDERDIM,ORDERDIM[ORDER_NO] ,"_min",[Min Order Date],"_max",[Max Delivery Date]),datediff([_min],[_max],DAY))
//SQL Server
Max Delivery Date = max(DELIVERY[DEILVERY_DATE])
Min Order Date = MIN('order'[SALES_DATE])
Date Diff = AVERAGEX(VALUES(ORDERDIM[ORDER_NO]) ,datediff([Min Order Date],[Max Delivery Date],DAY))
Date Diff 2 = AVERAGEX(SUMMARIZE(ORDERDIM,ORDERDIM[ORDER_NO] ,"_min",[Min Order Date],"_max",[Max Delivery Date]),datediff([_min],[_max],DAY))
No hay diferencia entre estas fórmulas entre Oracle, SQL Server y el modo de importación. En el modo de importación, pudimos crear una nueva columna en la tabla y pudimos obtener la diferencia entre las fechas dentro de la tabla.
En el caso de la misma diferencia de fecha de tabla, no necesita una dimensión común. Puede usar la clave principal de la tabla.
¿Quiere que cubramos algún otro tema sobre el modo de consulta directa? Comenta y cuéntanos.
Esta vez, no adjuntaré el pbix, ya que no tiene acceso a la base de datos y no funcionará.
Mis blogs anteriores – Decodificación de consulta directa: inteligencia de tiempo, coloración de ganadores en MAP, análisis de recursos humanos, Power BI trabajando con períodos de tiempo no estándar y comparación de datos entre intervalos de fechas
Conectar en LinkedIn
