Hola a todos
He estado investigando una solución de conversión de fecha en Power BI Report tanto para almacenamiento en modo consulta directa como en importación.
Estoy integrando estos informes en mi aplicación web con Embed for Customer Solution.
mi método actual: [cálculo DAX en Measure].
1. fuente: SQL Server
2. en aplicaciones web gestionando 30 zonas horarias (siete abreviaturas) y también el usuario puede cambiar su zona horaria y en la pantalla de la aplicación el usuario puede ver la fecha y hora convertida a esa zona horaria. es un cliente de funcionalidades que se busca y estoy intentando llevar esa función también a nivel de informe.
3. Dicho esto, he creado la medida que calcula el rango de horario de verano y no horario de verano para cada año a partir de una columna de fecha y hora que se le ha pasado. junto con DST_FLAG como algunas regiones no siguen DST pero están bajo la misma abreviatura (ex: Pacífico/Honolulu, HST y América/Adak, HST/HDT)
4. Esta medida añadió visuales de la tabla junto con otra información que quiero mostrar al usuario.
La medida convierte la fecha y hora en zona horaria de usuario mientras pasaba la información de la zona usando una tabla desconectada 'Dim_User_TimeZone' en modo consulta directa con RLS (ex: [USER_ID]=USERNAME()) y el nombre del rol es 'USER
nota: si el usuario cambia la información del huso horario en la aplicación web, puedo obtener la información de zona más reciente como esta tabla en DQ.
5. Desde la aplicación en generateToken pasaré el ID de usuario conectado actual con el nombre de rol 'USER', así que esto pasará a la tabla de zonas horarias y recibiré la información de los husos horarios del usuario con que ya está conectado, como nombre de zona, abreviatura, dst_flag.
6. A continuación se muestra la medida que utilicé tanto para la consulta directa como para el informe de almacenamiento en modo importación.
CallDate =
VAR SelectedDate = MIN(Fact_Call_Track[CALL_DATE])
RETURN
IF(
ISBLANK(SelectedDate),
BLANK(),
VAR TimeZone = MIN(Dim_User_TimeZone[Abbreviation])
VAR DSTFlag = MIN(Dim_User_TimeZone[DST_FLAG])
// DST boundaries
VAR Year_ = YEAR(SelectedDate)
VAR MarchFirst = DATE(Year_, 3, 1)
VAR FirstSundayInMarch = MarchFirst + MOD(7 - WEEKDAY(MarchFirst, 2), 7)
VAR DSTStart = FirstSundayInMarch + 7 + TIME(2, 0, 0) //adding 7 To get the second sunday of March
VAR NovFirst = DATE(Year_, 11, 1)
VAR FirstSundayInNov = NovFirst + MOD(7 - WEEKDAY(NovFirst, 2), 7)
VAR DSTEnd = FirstSundayInNov + TIME(2, 0, 0)
VAR IsInDST = SelectedDate >= DSTStart && SelectedDate < DSTEnd
VAR TotalOffsetMinutes =
SWITCH(TimeZone,
"CST", -360,
"EST", -300,
"HST", -600,
"AKST", -540,
"PST", -480,
"MST", -420,
"IST", 330,
0
) +
IF(DSTFlag = 1 && IsInDST, 60, 0)
VAR AdjustedDate = SelectedDate + (TotalOffsetMinutes / 1440.0)
RETURN
AdjustedDate
)
Esquema de 'Dim_User_TimeZone' se ve a continuación:
USER_ID TIME_ZONE_NAME Abbreviation DST_FLAG
test_user_1014 Asia/Kolkata IST 0
test_user_2177 America/Adak HST 1
test_user_4459 America/Phoenix MST 0
test_user_547 Pacific/Honolulu HST 0
test_user_7135 America/Chicago CST 1
test_user_924489 America/Los_Angeles PST 1
El problema:
Como es una medida, no puedo usarla en el segmento y en los ejes X/Y del gráfico para la alanysis de tendencia y también para algunas fechas cruciales
Diferencia w.r.to zona horaria del usuario, como el cubo diurno, el cubo de horas para mostrar el recuento (por ejemplo: si quiero mostrar el recuento de tareas por horario, como 'Vencida', <12 horas, <24 horas >72 horas).
Segundo método [con consulta dinámica parametrizada]:
1. Creó la tabla TZ con la columna Nombredezona y TimeZoneId en Power Query usando la opción Introduzir datos.
esta tabla contiene tanto el nombre de la zona de la base de datos IANA como los nombres del registro de Windows.
2. creó el parámetro llamado 'Nombrezona' con el valor por defecto 'Asia/Kolkata'
3. Filtré la columna Nombrede de la Zona de la tabla TZ con ese parámetro.
4. Aplicaba los cambios y a nivel de modelo de datos había utilizado la opción Bind to Parameter para la columna Nombrezonazona, de la tabla TZ
5. nota: La tabla TZ es una tabla desconectada y tiene aproxx. Mapeado de registros 460 con IANA y registro de Windows
La tabla TZ se ve a continuación
ZoneName TimeZoneId
Etc/GMT+12 Dateline Standard Time
Etc/GMT+11 UTC-11
America/Adak Aleutian Standard Time
Pacific/Honolulu Hawaiian Standard Time
Pacific/Marquesas Marquesas Standard Time
America/Anchorage Alaskan Standard Time
America/Los_Angeles Pacific Standard Time
America/Boise Mountain Standard Time
6. En la carga útil de configuración de aplicaciones web he superado el filtro básico usando la API del cliente, ver más abajo:
let config = {
type: 'report',
tokenType: tokenType == '0' ? models.TokenType.Aad : models.TokenType.Embed,
accessToken: accessToken,
embedUrl: embedUrl,
id: embedReportId,
permissions: permissions,
settings: {
panes: {
filters: {
visible: true
},
pageNavigation: {
visible: true
}
},
bars: {
statusBar: {
visible: true
}
}
},
filters: [
{
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "TZ",
column: "ZoneName"
},
operator: "In",
values: ["America/Adak"]
}
]
};
7. Luego he creado la tabla directa para la que quiero convertir los campos de fecha-hora, con el formato de consulta que aparece abajo
Nombre de la mesa: "Converted_Datetime"
let
Source = Sql.Database(Server, Database, [Query="
SELECT
CTRK.CALLTRK_IDN
,CTRK.CALL_DT AT TIME ZONE 'UTC' AT TIME ZONE '"&List.First(Table.Column(TZ,"TimeZoneId"))&"' AS CALL_DATE
,CTRK.FOLLOW_UP_DT AT TIME ZONE 'UTC' At TIME ZONE '"&List.First(Table.Column(TZ,"TimeZoneId"))&"' AS NEXT_FOLLOWUP_DATE
FROM
CALLTRK CTRK WITH(NOLOCK)
WHERE CTRK.ENTITY_ACTIVE = 'Y'
",CreateNavigationProperties=true])
in
Source
8. A partir de esto, gestionaré la conversión a nivel SQL usandola función AT TIME ZONE, luego construiré una relación para mi tabla de hechos (tabla importada) con calltrk_idn columna de Converted_Datetime y los datos fluyen de Converted_Datetime a "Fact_Call_Track", y esto convertirá el informe en modo importado en modo mixto.
9. para informes con consulta directa puedo llamar directamente a zoneId desde TZ concatenando la expresión (AT TIME ZONE 'UTC' AT TIME ZONE '"&List.First(Table.Column(TZ,"TimeZoneId"))&"') en la consulta sql de la tabla correspondiente.
10. Ahora la fecha y hora se convierte a zona horaria del usuario a nivel de columna y ahora puedo usar en slicer, line, gráfico de barras para análisis de tendencias y también para la calaculación de diferencias de fecha y hora w.r.to zona horaria del usuario.
Puede parecer un poco complejo, pero he implementado este enfoque y lo he probado con éxito con usuarios concurrentes, así como con el mismo usuario cambiando entre diferentes zonas horarias. En todos los casos, la columna de fecha y hora se convierte correctamente a la zona horaria seleccionada por el usuario.
Nota: Uno de los requisitos clave es evitar la actualización bajo demanda del conjunto de datos cuando un usuario actualiza su zona horaria en la aplicación web en modo Informe de importación. Con el enfoque basado en medidas, la fecha y hora convertida se muestra en tiempo de renderizado visual (para imágenes de tabla), por lo que no se requiere actualización. También exploré un segundo enfoque usando un método alternativo para abordar este requisito.
Busco orientación o buenas prácticas para manejar este escenario de forma más limpia o recomendada.
