Aprenda a unir tablas de datos mediante conjuntos de datos y relaciones definidas
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;
La tabla 2 devuelve datos de una tabla personalizada
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 deseado
Entonces, ¿cómo hago esto?
- Configure un Data Adapter de tipo Method que llame a una regla de negocio Dashboard Data Set.
- 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- 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)- 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)- 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)- 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 dtResultEl código en su totalidad
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 FunctionEl resultado
Cuando se ejecuta a través de un data adapter, muestra 2 campos de la Tabla 1 y 1 campo de la Tabla 2.
Esta es una funcionalidad poderosa que puede utilizarse cuando necesita unir fuentes de datos relacionadas y no puede usar un SQL Join.
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.