Cómo unir tablas de datos en OneStream con relaciones definidas

Descripción general para unir tablas de datos en OneStream

He configurado muchos dashboards que contienen vistas de cuadrícula pobladas con un Data Adapter. El Data Adapter se alimenta con datos de una tabla de datos relacional. Esta es una forma sencilla de exponer el contenido de los datos relacionales dentro de un dashboard. Se pueden aplicar filtros, lo que brinda a los usuarios la capacidad de filtrar los datos mostrados en la vista de cuadrícula. Normalmente, he hecho esto usando cuadros combinados u otro componente que ofrezca a los usuarios una lista de opciones, lo que luego cambia el valor de un parámetro. Ese parámetro se utiliza dentro de la sentencia SQL que impulsa el Data Adapter. Esto siempre ha sido un proceso sencillo.

Entre bastidores, el data adapter puede hacerlo de dos maneras, utilizando un Method Command Type o un SQL Command Type. El SQL Command Type ejecutará una consulta SQL directamente contra la fuente relacional, mientras que el Method Query llamará a una regla de negocio de conjunto de datos que normalmente consulta una fuente relacional y luego crea una tabla de datos VB.Net que se devuelve al data adapter.

Probablemente se esté diciendo a sí mismo: “hasta aquí todo bien, ¡lo tengo!”. Sin embargo, ¿qué sucede si desea combinar los resultados de 2 fuentes de datos relacionadas y devolverlos a través de una sola combinación de data adapter/vista de cuadrícula? Probablemente esté diciendo: “No hay problema, son 2 fuentes relacionales, ¡simplemente usaré un join en la consulta de datos!”. Bien, ahora ¿qué pasa si el caso de negocio cambia y las 2 fuentes relacionales están en entornos separados a los que no se puede acceder mediante un SQL Join? ¿Qué pasa si una es una tabla de base de datos de la aplicación y la otra está en una base de datos externa? En este caso no hay una forma sencilla de crear un SQL Join. O incluso, me ocurrió recientemente, una fuente de datos era una tabla relacional y la otra era una fuente de datos JSON devuelta por una llamada a una API web externa. ¿Cómo va a unirlas ahora?

Al rescate llegan los DataSets de VB.Net y las relaciones definidas. Un Data Set puede contener varias tablas y puede incluir relaciones definidas entre esas tablas. Con esto puede consultar el DataSet, basándose en la relación definida entre esas tablas, y devolver una sola tabla de datos que contenga esas tablas de datos combinadas o unidas, o solo algunos campos de datos de esas tablas.

Para este ejemplo, estoy utilizando 2 tablas de la base de datos de la aplicación, una de las cuales es una tabla personalizada. Sin embargo, podría consultar con la misma facilidad una tabla externa usando brapi.Database.CreateCustomExternalDbConnInfo.

La tabla 1 devuelve cuentas y sus descripciones desde la tabla interna de miembros 

SELECT MemberId, Name, Description, DimId FROM dbo.Member WHERE DimTypeId = '5' ORDER BY Name;
Tabla de origen de OneStream con cuentas y descripciones

La tabla 2 devuelve datos de una tabla personalizada

Segunda tabla de OneStream usada en la relación

Lo que quiero hacer es unir esas 2 tablas por el campo Name de la Tabla 1 y el campo Account de la Tabla 2, y devolver solo Name, Description y Users a una vista de cuadrícula a través de un Data Adapter.

Resultado esperado al unir tablas de datos

Resultado esperado al unir tablas de datos en OneStream

Cómo unir tablas de datos en OneStream

  1. Configure un Data Adapter de tipo Method que llame a una regla de negocio Dashboard Data Set.
Configuración del Data Adapter de método en OneStream
  1. Cree la regla de negocio Dashboard Data Set y, dentro de esa regla de negocio, cree sus 2 tablas de datos de origen.
'First Data Table
Dim dtMembers As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT MemberId, Name, Description, DimId FROM dbo.Member WHERE DimTypeId = '5' ORDER BY Name;")
    dtMembers = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using

'Second Tata Table
Dim dtMemberNames As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT Account, Users FROM dbo.XFC_Accounts_Users;")
    dtMemberNames = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using
  1. Cree la tabla de datos que contendrá los resultados unidos y que será el valor de retorno.
Dim dtResult As New DataTable
dtResult.Columns.Add("Name")
dtResult.Columns.Add("Description")
dtResult.Columns.Add(""Users)
  1. Cree el Data Set y agregue a él las tablas de datos de origen.
'Data Set that holds the data tables
Dim dataSetMerged As New DataSet
dataSetMerged.Tables.Add(dtMembers)
dataSetMerged.Tables.Add(dtMemberNames)
  1. Ahora defina la relación entre las 2 tablas. Estos son los campos de cada tabla de datos que utilizará para unir los datos, piense en ello como si fuera un SQL Join.
Dim relation As New DataRelation("AccountNameRelation"),dataSetMerged.Tables(0).Columns("Name"),dataSetMerged.Tables(1).Columns("Account"),False)
dataSetMerged.Relations.Add(relation)
  1. En este punto ya tiene un Data Set que contiene 2 Data Tables y una relación definida. El siguiente paso es recorrer las filas de una tabla de datos, obtener las filas relacionadas de la segunda tabla de datos según la relación definida y agregar los campos deseados como una fila en su tabla de datos de retorno.
' Loop through the rows of the on data table and get related "parent" rows based on the relationship
For Each childRow As DataRow In dtMemberNames.Rows
    Dim filterValue As String = childRow("Users").ToString()
    Dim parentRows As DataRow() = childRow.GetParentRows(relation)
    
    "Get the fields from each table that you want and add them to the return data table
    For Each parentRow As DataRow In parentRows
        dtResults.Rows.Add(parentRow("Name"),parentRow("Description"), childRow("Users"))
    Next
Next

Return dtResult

Código completo para unir tablas de datos 

Public Function MergeDataTables(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As DataTable

Try

'First Data Table
Dim dtMembers As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT MemberId, Name, Description, DimId FROM dbo.Member WHERE DimTypeId = '5' ORDER BY Name;")
    dtMembers = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using

'Second Tata Table
Dim dtMemberNames As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT Account, Users FROM dbo.XFC_Accounts_Users;")
    dtMemberNames = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using

Dim dtResult As New DataTable
dtResult.Columns.Add("Name")
dtResult.Columns.Add("Description")
dtResult.Columns.Add(""Users)

'Data Set that holds the data tables
Dim dataSetMerged As New DataSet
dataSetMerged.Tables.Add(dtMembers)
dataSetMerged.Tables.Add(dtMemberNames)

Dim relation As New DataRelation("AccountNameRelation"),dataSetMerged.Tables(0).Columns("Name"),dataSetMerged.Tables(1).Columns("Account"),False)
dataSetMerged.Relations.Add(relation)

' Loop through the rows of the on data table and get related "parent" rows based on the relationship
For Each childRow As DataRow In dtMemberNames.Rows
    Dim filterValue As String = childRow("Users").ToString()
    Dim parentRows As DataRow() = childRow.GetParentRows(relation)
    
    "Get the fields from each table that you want and add them to the return data table
    For Each parentRow As DataRow In parentRows
        dtResults.Rows.Add(parentRow("Name"),parentRow("Description"), childRow("Users"))
    Next
Next

Return dtResult

Catch ex As dtResult
    Throw ErrorHandler.LogWrite(si, New XFException(si,ex))
End Try
End Function

Validar el resultado de la tabla combinada

Cuando se ejecuta a través de un data adapter, muestra 2 campos de la Tabla 1 y 1 campo de la Tabla 2.

Resultado de tablas combinadas de OneStream en una Grid View

Esta es una funcionalidad poderosa que puede utilizarse cuando necesita unir fuentes de datos relacionadas y no puede usar un SQL Join.

Buenas prácticas para unir tablas de datos

Al unir tablas de datos, confirme que las columnas relacionadas usan tipos compatibles y contienen valores únicos cuando la relación lo requiere. Antes de unir tablas de datos, pruebe un conjunto pequeño para detectar claves nulas, duplicados y filas sin correspondencia.

Documente la dirección de la relación y valide el resultado del Data Adapter antes de publicarlo. Unir tablas de datos mediante una regla Dashboard DataSet mantiene la lógica reutilizable, mientras que unir tablas de datos con nombres claros facilita el soporte. Después de unir tablas de datos, compare los totales de origen y destino para confirmar que la relación devuelve las filas esperadas.

Recursos relacionados para unir tablas de datos

Para ampliar esta guía sobre unir tablas de datos, consulte exportar datos relacionales de OneStream a CSV, mostrar tablas XF en paneles de OneStream, evitar inyección SQL en reglas de negocio, mejorar el rendimiento de reglas de negocio, servicios de paneles de OneStream. Revise también la documentación oficial de OneStream sobre reglas Dashboard DataSet.

Contacto

MindStream Analytics

Para obtener más información sobre OneStream y cómo MindStream Analytics puede ayudarle a mejorar su planificación, informes y analítica, complete el siguiente formulario.