La Data Web

Ciertamente parece sencillo conectarse a un OneDrive pues todo debería ser nativo, tener un conector e incluso podemos hacerlo desde la Web. Ciertamente me ocurrió que quería más flexibilidad en el modo de conexión a drive para poder explotarlo lo mayor posible desde Power Bi Desktop y que se actualice sin necesidad de un gateway. Para ello voy a enseñarles como conectar a OneDrive for business y OneDrive Personal con el conector de Excel.

El procedimiento en Power Bi es idéntico para los dos. Lo que cambiará es el modo de conseguir el link de acceso.

OneDrive para la empresa o Sharepoint

Vamos a navegar por la web o local (en caso que lo tengamos sincronizados) hasta el archivo que deseemos conectar. El ejemplo estará dado con un excel. Una vez que estemos en el archivo vamos a abrirlo con excel local.

En el menu de Archivo seleccionamos el siguiente botón:

La ruta se verá algo así: "https://[Empresa]-my.sharepoint.com/personal/ibarrau_[Empresa]/Documents/Pruebas%20Propias/PaisPciaDeptoLatiLon.xlsx?web=1″

Antes de poder disponer del enlace, deberemos borrar el ?web=1 para que termine la dirección en xlsx.

OneDrive - Personal

Este caso es un poco más engorroso. Deberemos construir una URL propia a partir de valores que encontremos navegando en los archivos. La dirección a construir es algo así:

https://onedrive.live.com/download?resid=AAXXXX&authkey=AAXXXXXXX&em=x&app=Excel

Donde todo aquello que está en negrita vamos a reemplazarlo. La forma de obtener esos valores puede variar según el tipo de suscripción de onedrive personal que tengan. No funciona igual en todos los casos. Para conocer estas formas abramos nuestro onedrive personal y busquemos el archivo.

Para la primera alternativa daremos click derecho al archivo e insertar o embed.

Ésta opción generará un código html que nos ayudará a encontrar dichos valores:

<iframe src="https://onedrive.live.com/embed?cid=FC0C3DA0437DBE5A&resid=FC0C3DA0437DBE5A%217492&authkey=EOYoRrNlXisI6BY&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>

Para la segunda alternativa vamos a dar click en "Share" o "Compartir" el archivo de excel.

Esto genera un enlace que comienza con 1drv. Copiemos y peguemos ese enlace en un navegador web para obtener un enlace como el siguiente:

https://onedrive.live.com/edit?id=FC0C3DE0157DVE3A!77979&resid=FA0C3DF0332DBE5A!71979&ithint=file%2cxlsx&authkey=!BOaAGc3bhiBgaas&wao=2&cid=fc0c3de0437dbe5a

Como pueden apreciar ese enlace tiene los valores que buscamos pintados en negrita listos para reemplazar.

La elección de cual usar es suya. Si aplican un método y no encuentran los valores de resid y de authkey, entonces intenten el otro modo.

Power Bi Desktop

Lo primero que haremos será abrir un Power Bi y Obtener Datos con el conector de excel. Elijamos cualquier de nuestra computadora y daremos "Editar" para dirigirnos al editor de consulta. Desde allí chequeamos en el menú Vista la "Barra de Fórmulas" para modificar unas líneas. Si seguimos todo igual tendremos el siguiente código.

Para conectarnos al excel de la web, vamos a hacer unos cambios. Primero borraremos la palabra "File" para reemplazarla por "Web" y luego copiaremos la cadena antes descrita entre las comillas de la dirección del archivo.

De esta forma podremos conectar a cualquiera archivo de OneDrive o Sharepoint como si fuera un excel. Por usar Web.Content no necesitará gateway cuando lo publiquemos a service.

¿Ya probaste conectarte?

¿Alguna vez se preguntaron si podrían encontrar un solo lugar donde consultar todas las funciones de los lenguajes que contiene Power Bi?, pues yo si. Encontre bastantes sitios y google me ayudó con diversas funciones pero recientemente me puse a recorrer la galería de historias de la comunidad de Power Bi. Encontré estos excelentes informes de Power Bi para ayudarnos a recorrer las funciones que más nos cuentan o ni conocíamos para resolver un problema. Resalto aún más las de Power Query, dado que las relacionadas no estamos acostumbrados a ver las de Azure y otras librerías normalmente.

Cheat sheets:

- Power Query
- DAX

Ojala les sirva para tener presente y a mano las funciones.

Excelente llegada luego de 4 largos meses para Power Bi Report Server. El servidor de reportes on premise se pone al día con todas las increíbles features de Power Bi que se estuvieron lanzando en los últimos meses.

Los detalles sobre los cambios son los siguientes:

Durante el desarrollo de un modelo Bi es indispensable contar con una tabla fecha. Aun en las diferentes teorías de bi la importancia de una tabla que nos congregue día a día las filas con columnas de años, mes, semana, trimestre, etc. a disponibilidad de uso es crítico para un buen desarrollo de tableros o end user bi. Hoy en día, en microsoft particularmente, los modelos tabulares son un lugar indispensable para tener una tabla fecha. Los mismos traen gran capacidad de funciones de fecha para cálculos complejos en dax. Estos cálculos los denominan Time Intelligence. Para no desviarnos más con el tema vamos a ver como y donde puedo crear esta tabla.

SQL Server
Podemos generar un procedimiento almacenado que corra para reconstruirla durante las noches o en cada llamada de la actualización del modelo tabular. 

Tengamos en cuenta que el SELECT solo retorna una tabla de una columna con fechas desde hasta que hayamos seteado. Vamos a ver el panorama completo, luego cada quien separará en un procedimiento almacenado o lo usará de consulta directa.

CREATE TABLE dim_date
(
 [date]       DATE PRIMARY KEY,
 [year]       AS DATEPART(YEAR,     [date]),
 [month]      AS DATEPART(MONTH,    [date]),
 [MonthName]  AS DATENAME(MONTH,    [date]),
 [day]        AS DATEPART(DAY,      [date]),  
 FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),  
 FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
 [week]       AS DATEPART(WEEK,     [date]),
 [ISOweek]    AS DATEPART(ISO_WEEK, [date]),
 [DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
 [quarter]    AS DATEPART(QUARTER,  [date]),    
 [datekey]    AS CONVERT(CHAR(8),   [date], 112),
 [dmy]        AS CONVERT(CHAR(10),  [date], 101)
);

Procedimiento:

Power Query
No siempre tendremos la posibilidad de nutrirnos de un acceso de escritura a una base de datos para poder armar la tabla fecha de la primera forma mencionada, sino que a veces vamos a tener que terminar el data modeling incorporando la fecha en la etapa de capa semántica junto al modelo tabular. Éste método es el más usado en mi opinión si construimos Bi como servicio a entidades. Lo más atractivo es tener una tabla fecha resulta antes de cargar y aplicar el modelo tabular. 

Tengamos en cuenta que la fecha de inicio esta seteada a mano hasta fecha actual igual que en SQL.

= let
//Set start date
   StartDate = #date(2010, 1, 1),
   EndDate = DateTime.Date(DateTime.LocalNow()),
//Get the number of dates that will be required for the table
   GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
   GetDateList = List.Dates(StartDate, GetDateCount,
   #duration(1,0,0,0)),
//Convert the list into a table
   DateListToTable = Table.FromList(GetDateList,
   Splitter.SplitByNothing(), {"Fecha"}, null, ExtraValues.Error),
// Convert to Date
   ConvertToDate = Table.TransformColumns(DateListToTable,{{"Fecha", Date.From, type date}}),
// Date Key
   DateKey = Table.AddColumn(ConvertToDate, "IdFecha", each Number.FromText(Text.From(Date.Year([Fecha])) & Date.ToText([Fecha],"MM") & Date.ToText([Fecha],"dd") ) , Int32.Type ),
//Add Year Column
   YearNumber = Table.AddColumn(DateKey, "Año",
   each Date.Year([Fecha]), Int32.Type),
//Add Quarter format QX
   Quarter = Table.AddColumn(YearNumber, "Trimestre",
   each Text.Combine({"Q", Text.From(Date.QuarterOfYear([Fecha]))}), type text),
//Add Month Number Column
   MonthNumber = Table.AddColumn(Quarter , "IdMes",
   each Date.Month([Fecha]), Int32.Type),
//Add Month Name Column
   MonthName = Table.AddColumn(MonthNumber , "Mes",
   each Date.ToText([Fecha],"MMMM", "es-es"), type text),
//Add Period
   Periodo = Table.AddColumn(MonthName , "Periodo",
   each Text.From([Año]) & Text.PadStart(Text.From([IdMes]),2,"0"), type text),
//Add YearQ
   TrimestreAño = Table.AddColumn(Periodo, "TrimestreAño",
   each Text.Combine({Text.From([Año]), [Trimestre]}), type text ),
//Add Day Number Column
   DayNumber  = Table.AddColumn(TrimestreAño , "Dia",
   each Date.Day([Fecha]), Int32.Type),
//Add Day Name Column
   DayName  = Table.AddColumn(DayNumber , "Nombre Dia",
   each Date.ToText([Fecha], "dddd"), type text)
in
   DayName

DAX
Al igual que el paso anterior las opciones de modelado puede acotarse más según el desarrollo y los permisos que nos estén concediendo. Entonces no debemos dejar de lado la posibilidad de construir una tabla fecha una vez ya existente el modelo tabular. La principal ventaja de éste método es construir la tabla fecha a partir de fechas del mismo modelo creado y no necesariamente predefinidas de la forma (hoy y últimos N días/meses/años). De esta forma puedo crearla a partir de la mínima y máxima fecha de mi tabla de hecho. 

En caso de dax podemos nutrir la fecha a partir de nuestra tabla de hecho o seteada a mano con la función DATE() y actual con NOW()

Date =
VAR MinYear = YEAR ( MIN ( TablaHecho[Fecha] ) )
VAR MaxYear = YEAR ( MAX ( TablaHecho[Fecha] ) )
RETURN
ADDCOLUMNS (
   FILTER (
       CALENDARAUTO( );
       AND ( YEAR ( [Date] ) >= MinYear; YEAR ( [Date] ) <= MaxYear )
   );
   "Year Number"; YEAR ( [Date] );
   "Month Name"; FORMAT ( [Date]; "mmmm" );
   "Month Number"; MONTH ( [Date] );
   "Day"; Day ( [Date] )
)

Para este tipo de casos yo soy de la frase "Mientras más atrás se resuelva, mejor". Si tenemos la posibilidad de construirlo en el origen de datos porque existe un data warehouse o tiene la posibilidad mejor. Sino intentaría siempre llevarlo por Power Query y de última instancia en DAX. No deberíamos construir modelos, ni aunque sean pequeños Power Bi de pequeñas necesidades, sin la tabla fecha. Ustedes, ¿usan tabla fecha?

¿Como puedo conocer los detalles de mi modelo de Power Bi? ¿Cuantas métricas tiene? ¿Cuales son las formulas? ¿Que tan performante son mis consultas DAX? ¿Funcionan mis agregaciones?

Todas estas y más preguntas pueden ser respondidas gracias a la herramienta DAX Studio creada por desarrolladores de SQLBI. Esta maravilla, que por cierto es muy parecida a SQL Management Studio, nos permite muchas y más detalles sobre el modelo de datos.
Tras la conexión a un modelo tabular en analysis services o un power bi desktop abierto en el escritorio, podemos acceder a consultar el modelo mediante DAX.

¿De que sirve? Para permitirnos correr consultas contra el modelo como si fuera una base de datos, ya sea para conocer que todo esté en su lugar, responder una duda puntual de un cliente o jefe como así tambien para controlar o debuguear que nuestras medidas/columnas/tablas que vamos a construir estén devolviendo la información correcta. El poder de la herramienta llega hasta medición de performance de cada query particular al modelo e inclusive detección de agregaciones en Power Bi (Preview feature).
Para analizar el modelo, cuando se conecten, por ejemplo, a un pbix podrán apreciar un apartado (DMV) de tablas con información del modelo que se encuentra abajo a la izquierda. 

Dos de las más comunes que utilizo para documentar son para conocer código dax:
select * from $SYSTEM.TMSCHEMA_MEASURES
select * from $SYSTEM.TMSCHEMA_COLUMNS where [Type]  = 2

Los animo a probar esta maravilla de herramienta para las preguntas que propuse como también para muchas mas que pueden encontrarse ustedes. Éstas son nada más y menos que algunas que me ayudaron a resolver a mi.

¿Cuales les ayudaran a resolver a ustedes?

P/D Recientemente Chris Webb encontró la forma de traer los pasos realizados en el editor de consulta de un informe en Power Bi Service. Muy útil para ayudarnos a documentar.