El segundo caso práctico se basaba en la información de morosidad de clientes y fue un caso bastante más complejo.
Actualmente existe un cuadro de mandos integrado en D3FO que muestra esa información, pero en nuestro caso necesitábamos añadir las dimensiones financieras para obtener deuda, no por cliente, si no por dimensión analítica.
Aquí teníamos dos opciones:
Modificar el PBI original para añadirle las dimensiones
Crear un PBI nuevo desde cero
Dado que el uso que se le iba a dar a dicho PBI no era necesariamente dentro de D3FO y que la forma de mostrarlo era distinta a la del original, optamos por crearlo de cero por lo que el desafío en este caso era el de entender la estructura de la información que contiene este PBI.
De entre todas las entidades existentes, las necesarias para obtener dicha información son:
CustCollectionsBIMeasurements
FinancialReportingTransactionData
Como vimos en el artículo anterior, estas entidades realmente están formadas por un número mayor de tablas y vistas:
Después de algo de investigación las tablas o vistas necesarias para obtener dicha información son las siguientes:
Una vez añadidas a nuestro informe de PBI se deben relacionar de la siguiente forma:
· GeneralJournalAccountEnt se relaciona con:
- DimensionCombination usando DIMENSIONID contra DIMENSIONCOMBINATIONRECID
- BICustTransVouchers usando SUBLEDGERVOUCHERDATAAREAID contra SUBLEDGERVOUCHER
- FactWithSubledger usando SUBLEDGERVOUCHERDATAAREAID, SUBLEDGERVOUCHER, FinancialReportingTransactionData_FinancialReportingFactWithSubledger_FK contra SUBLEDGERVOUCHERDATAAREAID, VOUCHER, ROW_UNIQUEKEY
· BICustTransDimensions se relaciona con:
- GeneralJournalAccountEnt usando DATAAREAID, VOUCHER, ACCOUNTNUM, ImporteAbsCust siendo este último campo una transformación de CUSTTRANSSYSTEMCURRENCYAMOUNT para convertirlo a valor absoluto. Dichos campos se relacionan con SUBLEDGERVOUCHERDATAAREAID, SUBLEDGERVOUCHER, ACCOUNTNUM, ImporteAbsLedger, siendo este ultimo valor otra transformación a valor absoluto del campo PRIMARYAMOUNT
· Transacciones se relaciona con:
- BICustTransDimensions usando DATAAREAID, CUSTTRANSOPENRECID contra DATAAREAID, CUSTTRANSOPENRECID
· Clientes se relaciona con:
- Transacciones usando un campo combinado de Cliente+Empresa
Una vez relacionadas toca transformar los datos para que sean entendibles, en nuestro caso, la tabla mas importante será la de transacciones, esta sería la transformación realizada:
let
Source = Sql.Database(«localhost», «axdw»),
dbo_CustCollectionsBIMeasurements_CustCollectionsBICustTrans_MATERIALIZED = Source{[Schema=»dbo»,Item=»CustCollectionsBIMeasurements_CustCollectionsBICustTrans_MATERIALIZED»]}[Data],
#»Filtered Rows» = Table.SelectRows(dbo_CustCollectionsBIMeasurements_CustCollectionsBICustTrans_MATERIALIZED, each ([OPENSYSTEMCURRENCYAMOUNT] <> null)),
#»Added Custom» = Table.AddColumn(#»Filtered Rows», «VoucherEmpresa», each [VOUCHER]&[DATAAREAID]),
#»Added Custom2″ = Table.AddColumn(#»Added Custom», «ClienteEmpresa», each [ACCOUNTNUM]&[DATAAREAID]),
#»Changed Type» = Table.TransformColumnTypes(#»Added Custom2″,{{«CUSTTRANSOPENRECID», type text}}),
#»Added Custom1″ = Table.AddColumn(#»Changed Type», «OpenRecIdEmpresa», each [CUSTTRANSOPENRECID]&[DATAAREAID]),
#»Renamed Columns» = Table.RenameColumns(#»Added Custom1″,{{«NAME», «Pagador»}, {«DATAAREAID», «Empresa»}}),
#»Uppercased Text» = Table.TransformColumns(#»Renamed Columns»,{{«Empresa», Text.Upper, type text}}),
#»Renamed Columns1″ = Table.RenameColumns(#»Uppercased Text»,{{«VOUCHER», «Asiento»}, {«ACCOUNTNUM», «Nº Pagador»}}),
#»Added Custom3″ = Table.AddColumn(#»Renamed Columns1″, «Hoy», each DateTime.LocalNow()),
#»Renamed Columns2″ = Table.RenameColumns(#»Added Custom3″,{{«DUEDATE», «F. Vencimiento»}, {«TRANSDATE», «F. Transacción»}}),
#»Inserted Year» = Table.AddColumn(#»Renamed Columns2″, «Year», each Date.Year([F. Vencimiento]), Int64.Type),
#»Renamed Columns3″ = Table.RenameColumns(#»Inserted Year»,{{«Year», «Año vencimiento»}}),
#»Changed Type1″ = Table.TransformColumnTypes(#»Renamed Columns3″,{{«Año vencimiento», type text}}),
#»Inserted Month» = Table.AddColumn(#»Changed Type1″, «Month», each Date.Month([F. Vencimiento])),
#»Renamed Columns4″ = Table.RenameColumns(#»Inserted Month»,{{«Month», «Mes vencimiento»}}),
#»Changed Type2″ = Table.TransformColumnTypes(#»Renamed Columns4″,{{«Mes vencimiento», type text}}),
#»Inserted Text Length» = Table.AddColumn(#»Changed Type2″, «Length», each Text.Length([Mes vencimiento]), Int64.Type),
#»Added Conditional Column» = Table.AddColumn(#»Inserted Text Length», «MesNumero», each if [Length] = 2 then [Mes vencimiento] else «0»&[Mes vencimiento]),
#»Added Custom5″ = Table.AddColumn(#»Added Conditional Column», «AñoMesKey», each [Año vencimiento]&[MesNumero]),
#»Changed Type5″ = Table.TransformColumnTypes(#»Added Custom5″,{{«AñoMesKey», type text}}),
#»Renamed Columns7″ = Table.RenameColumns(#»Changed Type5″,{{«Mes vencimiento», «Mesvencimiento»}}),
#»Changed Type3″ = Table.TransformColumnTypes(#»Renamed Columns7″,{{«F. Vencimiento», type date}, {«Hoy», type date}}),
#»Renamed Columns5″ = Table.RenameColumns(#»Changed Type3″,{{«OPENAMOUNTMST», «Pendiente»}}),
#»Removed Columns» = Table.RemoveColumns(#»Renamed Columns5″,{«CUSTTRANSSYSTEMCURRENCYAMOUNT», «PARTITION», «RECID», «COLLECTIONLETTER», «COLLECTIONLETTERCODE», «COMPANY», «CREDITMAX», «CREDITRATING», «CROSSRATE», «GREGORIANDATE», «PARTY», «REASONREFRECID», «SYSTEMCURRENCYCREDITMAX», «TRANSTYPE», «ISDSO30», «CustCollectionsBIMeasurements_CustTable_FK», «COLLECTIONLETTER_LABEL», «COLLECTIONLETTERCODE_LABEL», «TRANSTYPE_LABEL», «ISDSO30_LABEL», «CUSTTRANSRECID», «CUSTTRANSTABLEID», «SYSTEMCURRENCYCODE», «VoucherEmpresa»}),
#»Added Custom4″ = Table.AddColumn(#»Removed Columns», «Nº/Pagador», each [Nº Pagador]&»-«&[Pagador]),
#»Merged Queries» = Table.NestedJoin(#»Added Custom4″,{«Empresa», «CUSTTRANSOPENRECID»},BICustTransDimensions,{«DATAAREAID», «CUSTTRANSOPENRECID»},»BICustTransDimensions»,JoinKind.LeftOuter),
#»Expanded BICustTransDimensions» = Table.ExpandTableColumn(#»Merged Queries», «BICustTransDimensions», {«Cod Dim1», «Dimension 1», «Cod Dim2″, » Dimension 2″, «Cod. Departamento», «Departamento»}, {«Cod Dim1», «Dimension 1», «Cod Dim2″, » Dimension 2″, «Cod. Departamento», «Departamento»}),
#»Renamed Columns6″ = Table.RenameColumns(#»Expanded BICustTransDimensions»,{{«F. Vencimiento», «F. Venc.»}, {«Cod. Departamento», «Cod. Dep}),
#»Removed Columns1″ = Table.RemoveColumns(#»Renamed Columns6″,{«Length»})
in
#»Removed Columns1″
A partir de aquí podremos obtener la deuda y su vencimiento usando el campo “Pendiente” por las dimensiones financieras que queramos, independientemente del cliente.
Como veis este informe resultó ser bastante mas complejo por la labor de investigación de las relaciones entre las tablas y las distintas transformaciones que hubo que realizar.
About the Author: Hugo de Jesús
