Solved
Extensibility Rules error
I have the existing BR rule to export the region hierarchy which is working for last one year without any issues and I am trying to reuse the BR rule for country hierarchy export purpose. I copied & ...
Forum Discussion
aformenti while execute the rule using the execute extender, I am getting followig error:
1) Unable to execute Business Rule 'Export_Country_Hierarchy'. 2) Invalid object name 'Country'.
I couldnot able to figure out whats happening really. I have rule using the sqlA1.append, etc?
HI vpidala,
It could be a lot of things. If you share the business rule you trying to run I could have a look.
Alternatively, if what you trying to do is export the hierarchy in a Parent/Child format in Excel (or CSV), I have recently posted the following post that could help you:
Imports System Imports System.Data Imports System.Data.Common Imports System.IO Imports System.Collections.Generic Imports System.Globalization Imports System.Linq Imports Microsoft.VisualBasic Imports System.Windows.Forms Imports OneStream.Shared.Common Imports OneStream.Shared.MTf Imports OneStream.Shared.Engine Imports OneStream.Shared.Database Imports OneStream.Stage.Engine Imports OneStream.Stage.Database Imports OneStream.Finance.Engine Imports OneStream.Finance.Database Imports WinScp Imports DidiSoft.Pgp Imports DidiSoft.Pgp.Exceptions 'Imports System.Text.RegularExpressions Namespace OneStream.BusinessRule.Extender.Export_Country_Hierarchy Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object Try Dim sFTPFolder As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_Application_sFTP_Folder" ) Dim sFTPHost As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Host" ) Dim sFTPPort As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Port" ) Dim sFTPUsr As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Usr" ) Dim sFTPPwd As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Pwd" ) Dim sFTPHostKey As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_HostKey" ) Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult() 'Get WFCluster Criteria Values to determine which Workflow, Scenario, and WFTime we are working in Dim profileName As String = BRApi.Workflow.Metadata.GetProfile(si, si.WorkfloMTlusterPk.ProfileKey).Name Dim scenarioName As String = ScenarioDimHelper.GetNameFromId(si, si.WorkfloMTlusterPk.ScenarioKey) Dim timeName As String = TimeDimHelper.GetNameFromId(si.WorkfloMTlusterPk.TimeKey) 'brapi.ErrorLog.LogMessage(si, "profileName = " & profileName & "") 'brapi.ErrorLog.LogMessage(si, "scenarioName = " & scenarioName & "") 'brapi.ErrorLog.LogMessage(si, "timeName = " & timeName & "") Dim sqlA1 As New Text.StringBuilder sqlA1.Append("Delete " & Chr(13) & "") sqlA1.Append("FROM " & Chr(13) & "") sqlA1.Append("[Country] " & Chr(13) & "") 'Process the query Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si) Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sqlA1.ToString, False) BRApi.Database.SaveCustomDataTable(si, "App", "Country", dt, False) End Using End Using Dim sqlA2 As New Text.StringBuilder sqlA2.Append("--Declare a table variable, PTWithActivity, to store the record set for Personnel Transfer Activity " & Chr(13) & "") sqlA2.Append("--The datatype defined is from the table which the field is queried from " & Chr(13) & "") sqlA2.Append("DECLARE @SQL01 VARCHAR(MAX);" & Chr(13) & "") sqlA2.Append("DECLARE @SQL02 VARCHAR(MAX);" & Chr(13) & "") sqlA2.Append("DECLARE @DimensionName01 AS VARCHAR(500);" & Chr(13) & "") sqlA2.Append("DECLARE @DimensionName02 AS VARCHAR(500);" & Chr(13) & "") sqlA2.Append("SET @DimensionName01 = 'Entities';" & Chr(13) & "") sqlA2.Append("SET @DimensionName02 = 'Entities'; " & Chr(13) & "") sqlA2.Append("DECLARE @HierarchyName01 AS VARCHAR(500); " & Chr(13) & "") sqlA2.Append("DECLARE @HierarchyName02 AS VARCHAR(500); " & Chr(13) & "") sqlA2.Append("SET @HierarchyName01 = 'CDW_ENTY'; " & Chr(13) & "") sqlA2.Append("SET @HierarchyName02 = 'Mgt_Hierarchy'; " & Chr(13) & "") sqlA2.Append("DECLARE @ListOfAccounts AS VARCHAR(MAX); " & Chr(13) & "") sqlA2.Append("DECLARE @ListOfEntities AS VARCHAR(MAX); " & Chr(13) & "") sqlA2.Append("WITH VAR " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("DimensionName01 " & Chr(13) & "") sqlA2.Append(",DimensionName02 " & Chr(13) & "") sqlA2.Append(",HierarchyName01 " & Chr(13) & "") sqlA2.Append(",HierarchyName02 " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") sqlA2.Append("AS " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("@DimensionName01 AS DimensionName01 " & Chr(13) & "") sqlA2.Append(",@DimensionName02 AS DimensionName02 " & Chr(13) & "") sqlA2.Append(",@HierarchyName01 AS HierarchyName01 " & Chr(13) & "") sqlA2.Append(",@HierarchyName02 AS HierarchyName02 " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") '--SELECT * FROM VAR sqlA2.Append(",VW_DIMPAR AS " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("D.Name DimensionName " & Chr(13) & "") sqlA2.Append(",R.DimTypeId " & Chr(13) & "") sqlA2.Append(",R.DimId " & Chr(13) & "") sqlA2.Append(",R.ParentId ParentID " & Chr(13) & "") sqlA2.Append(",P.Name ChildName " & Chr(13) & "") sqlA2.Append(",P.Description ChildDescription " & Chr(13) & "") sqlA2.Append(",P.[DisplayMemberGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",P.[ReadDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",P.[ReadDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append(",P.[ReadWriteDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",P.[ReadWriteDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append(",R.ChildId MemberID " & Chr(13) & "") sqlA2.Append(",R.SiblingSortOrder " & Chr(13) & "") sqlA2.Append(",D.InheritedDimId " & Chr(13) & "") sqlA2.Append(",D.DimMemberSourceTypeId " & Chr(13) & "") sqlA2.Append("FROM " & Chr(13) & "") sqlA2.Append("[Member] P " & Chr(13) & "") sqlA2.Append("INNER JOIN [Relationship] R " & Chr(13) & "") sqlA2.Append("ON P.DimTypeId = R.DimTypeId " & Chr(13) & "") sqlA2.Append("AND P.DimId = R.DimId " & Chr(13) & "") sqlA2.Append("AND P.MemberId = R.ChildId " & Chr(13) & "") sqlA2.Append("INNER JOIN [Dim] D " & Chr(13) & "") sqlA2.Append("ON P.DimTypeId = D.DimTypeId " & Chr(13) & "") '--AND P.DimId = D.DimId --Comment out for extensible dimensionality sqlA2.Append("WHERE " & Chr(13) & "") sqlA2.Append("D.Name = (SELECT DimensionName01 FROM VAR) " & Chr(13) & "") sqlA2.Append("OR D.Name IN (SELECT DimensionName02 FROM VAR) " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") '--SELECT * FROM VW_DIMPAR ORDER BY DimensionName, ParentId '--SELECT DimensionName, COUNT(*) RC FROM VW_DIMPAR GROUP BY DimensionName '--SELECT DimensionName, ChildName, COUNT(*) RC FROM VW_DIMPAR GROUP BY DimensionName, ChildName HAVING COUNT(*) >1 --Duplicates would be due to member being in more than one dimension sqlA2.Append(",VW_DIMPARCLD0 AS " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("P.DimensionName " & Chr(13) & "") sqlA2.Append(",P.DimTypeId " & Chr(13) & "") sqlA2.Append(",P.DimId " & Chr(13) & "") sqlA2.Append(",P.ParentId ParentId " & Chr(13) & "") sqlA2.Append(",CASE " & Chr(13) & "") sqlA2.Append("WHEN M.Name IS NULL THEN P.DimensionName " & Chr(13) & "") sqlA2.Append("ELSE M.Name " & Chr(13) & "") sqlA2.Append("END ParentName " & Chr(13) & "") sqlA2.Append(",CASE " & Chr(13) & "") sqlA2.Append("WHEN M.Description IS NULL THEN P.DimensionName " & Chr(13) & "") sqlA2.Append("ELSE M.Description " & Chr(13) & "") sqlA2.Append("END ParentDescription " & Chr(13) & "") sqlA2.Append(",P.ChildName " & Chr(13) & "") sqlA2.Append(",P.ChildDescription ChildDescription " & Chr(13) & "") sqlA2.Append(",P.MemberId ChildId " & Chr(13) & "") sqlA2.Append(",P.SiblingSortOrder ChildSortOrder " & Chr(13) & "") sqlA2.Append(",P.[DisplayMemberGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",P.[ReadDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",P.[ReadDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append(",P.[ReadWriteDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",P.[ReadWriteDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append("FROM " & Chr(13) & "") sqlA2.Append("VW_DIMPAR P " & Chr(13) & "") sqlA2.Append("LEFT OUTER JOIN [dbo].[Member] M " & Chr(13) & "") sqlA2.Append("ON M.DimTypeId = P.DimTypeId " & Chr(13) & "") sqlA2.Append("AND M.DimId = P.DimId " & Chr(13) & "") sqlA2.Append("AND M.MemberId = P.ParentId " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") '--SELECT * FROM VW_DIMPARCLD0 WHERE ChildName = 'BW195' '--SELECT DimensionName, COUNT(*) RC FROM VW_DIMPARCLD0 GROUP BY DimensionName sqlA2.Append(",VW_DIMPARCLD AS " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("DimensionName " & Chr(13) & "") sqlA2.Append(",DimTypeId " & Chr(13) & "") sqlA2.Append(",DimId " & Chr(13) & "") sqlA2.Append(",ParentId " & Chr(13) & "") sqlA2.Append(",ParentName " & Chr(13) & "") sqlA2.Append(",ChildName " & Chr(13) & "") sqlA2.Append(",ChildDescription " & Chr(13) & "") sqlA2.Append(",ChildID " & Chr(13) & "") sqlA2.Append(",ChildSortOrder " & Chr(13) & "") sqlA2.Append(",CAST(ParentName + ' -> ' + ChildName AS NVARCHAR(500)) AS Lineage " & Chr(13) & "") sqlA2.Append(",[DisplayMemberGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ReadDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ReadDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append(",[ReadWriteDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ReadWriteDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append("FROM " & Chr(13) & "") sqlA2.Append("VW_DIMPARCLD0 " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") '--SELECT * FROM VW_DIMPARCLD sqlA2.Append(",DimensionStructure AS " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("DimensionName " & Chr(13) & "") sqlA2.Append(",DimTypeId " & Chr(13) & "") sqlA2.Append(",DimId " & Chr(13) & "") sqlA2.Append(",ParentId " & Chr(13) & "") sqlA2.Append(",ParentName " & Chr(13) & "") sqlA2.Append(",ChildSortOrder ParentSortOrder " & Chr(13) & "") sqlA2.Append(",ChildId " & Chr(13) & "") sqlA2.Append(",ChildName " & Chr(13) & "") sqlA2.Append(",ChildDescription " & Chr(13) & "") sqlA2.Append(",ChildSortOrder " & Chr(13) & "") sqlA2.Append(",0 AS Generation " & Chr(13) & "") sqlA2.Append(",Lineage " & Chr(13) & "") sqlA2.Append(",[DisplayMemberGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ReadDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ReadDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append(",[ReadWriteDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ReadWriteDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append("FROM " & Chr(13) & "") sqlA2.Append("VW_DIMPARCLD " & Chr(13) & "") sqlA2.Append("WHERE " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("ParentName = (SELECT DimensionName01 FROM VAR) " & Chr(13) & "") sqlA2.Append("OR ParentName = (SELECT DimensionName02 FROM VAR) " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") sqlA2.Append("AND " & Chr(13) & "") sqlA2.Append("( " & Chr(13) & "") sqlA2.Append("ChildName = (SELECT HierarchyName01 FROM VAR) " & Chr(13) & "") sqlA2.Append("OR ChildName = (SELECT HierarchyName02 FROM VAR) " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") '--)SELECT * FROM DimensionStructure sqlA2.Append("UNION ALL " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("[ParCld].[DimensionName] " & Chr(13) & "") sqlA2.Append(",[ParCld].[DimTypeId] " & Chr(13) & "") sqlA2.Append(",[ParCld].[DimId] " & Chr(13) & "") sqlA2.Append(",[ParCld].ParentId " & Chr(13) & "") sqlA2.Append(",[ParCld].ParentName " & Chr(13) & "") sqlA2.Append(",[DS].ChildSortOrder ParentSortOrder " & Chr(13) & "") sqlA2.Append(",[ParCld].ChildId " & Chr(13) & "") sqlA2.Append(",[ParCld].ChildName " & Chr(13) & "") sqlA2.Append(",[ParCld].ChildDescription " & Chr(13) & "") sqlA2.Append(",[ParCld].ChildSortOrder " & Chr(13) & "") sqlA2.Append(",[DS].Generation + 1 AS Generation " & Chr(13) & "") sqlA2.Append(",CAST([DS].[Lineage] + ' -> ' + [ParCld].ChildName AS NVARCHAR(500)) " & Chr(13) & "") sqlA2.Append(",[ParCld].[DisplayMemberGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ParCld].[ReadDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ParCld].[ReadDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append(",[ParCld].[ReadWriteDataGroupUniqueID] " & Chr(13) & "") sqlA2.Append(",[ParCld].[ReadWriteDataGroupUniqueID2] " & Chr(13) & "") sqlA2.Append("FROM " & Chr(13) & "") sqlA2.Append("VW_DIMPARCLD [ParCld] " & Chr(13) & "") sqlA2.Append("INNER JOIN DimensionStructure AS [DS] " & Chr(13) & "") sqlA2.Append("ON [DS].[DimTypeId] = [ParCld].[DimTypeId] " & Chr(13) & "") '--AND [DS].[DimId] = [ParCld].[DimId] sqlA2.Append("AND [DS].[ChildId] = [ParCld].[ParentId] " & Chr(13) & "") sqlA2.Append(") " & Chr(13) & "") '--SELECT [DimensionName], COUNT(*) RC FROM DimensionStructure GROUP BY [DimensionName] 'sqlA2.Append("SELECT ParentName,ChildName,ChildDescription FROM DimensionStructure WHERE Lineage LIKE '%Mgt_Hierarchy%' ORDER BY DimensionName, generation " & Chr(13) & "") 'Add Column Names sqlA2.Append("--Insert the record set for Personnel Transfer Activity " & Chr(13) & "") sqlA2.Append("INSERT INTO Country " & Chr(13) & "") sqlA2.Append("SELECT " & Chr(13) & "") sqlA2.Append("[DimensionName] AS [HierarchyName] " & Chr(13) & "") sqlA2.Append(",[DimTypeId] AS [HierarchyTypeID] " & Chr(13) & "") sqlA2.Append(",[DimId] AS [HierarchyID] " & Chr(13) & "") sqlA2.Append(",[ParentID] " & Chr(13) & "") sqlA2.Append(",[ParentName] " & Chr(13) & "") sqlA2.Append(",[ParentSortOrder]" & Chr(13) & "") sqlA2.Append(",[ChildID] " & Chr(13) & "") sqlA2.Append(",[ChildName] " & Chr(13) & "") sqlA2.Append(",[ChildDescription] " & Chr(13) & "") sqlA2.Append(",[ChildSortOrder] " & Chr(13) & "") sqlA2.Append(",[Generation] " & Chr(13) & "") sqlA2.Append(",[Lineage] " & Chr(13) & "") sqlA2.Append(",' ' AS [Parent] " & Chr(13) & "") sqlA2.Append("FROM " & Chr(13) & "") sqlA2.Append("[DimensionStructure]" & Chr(13) & "") sqlA2.Append("WHERE " & Chr(13) & "") sqlA2.Append("Lineage Like '%CDW_ENTY -> Country_Hierarchy%'" & Chr(13) & "") 'sqlA2.Append("Lineage LIKE 'Country_Hierarchy'" & Chr(13) & "") 'brapi.ErrorLog.LogMessage(si, "sqlA2=" & sqlA2.tostring & "") ' 'Process the query ' Dim folderpath As String = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest,Nothing) Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si) Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sqlA2.ToString, False) BRApi.Database.SaveCustomDataTable(si, "App", "Country", dt, False) ' End Using End Using Dim folderpath As String = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest,Nothing) Dim dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si) Dim dtMGMT As DataTable = BRAPi.Database.ExecuteSqlUsingReader(dbConn, "SELECT ParentName As ParentName, ChildName As ChildName, ChildDescription As ChildDescription FROM Country ORDER BY Generation ASC", False) If Directory.Exists(folderpath) Then Using readFile As StreamWriter = File.CreateText(folderpath & "\Country_Hierarchy" & ".txt") Dim icolCount As Integer = dtMGMT.Columns.Count For i As Integer = 0 To icolCount - 1 If Not Convert.IsDBNull(i) Then readFile.Write(dtMGMT.Columns(i)) End If If i < icolCount -1 Then readFile.Write("|") End If Next readFile.Write(readFile.NewLine) For Each dr As DataRow In dtMGMT.Rows For i As Integer = 0 To icolCount - 1 If Not Convert.IsDBNull(dr(i)) Then readFile.Write(dr(i).ToString()) End If If i < icolCount -1 Then readFile.Write("|") End If Next readFile.Write("|") 'FAPP-2556 - added by hadi rahal 3/11/2022 readFile.Write(readFile.NewLine) Next readFile.Close() End Using End If Dim blnRemoveFiles As Boolean = False Dim strHarvestPath As String = BRApi.FileSystem.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest,Nothing) 'BRApi.ErrorLog.LogMessage(si, "Harvest Path: " + strHarvestPath, Nothing) Dim sessionOptions As New SessionOptions With sessionOptions .Protocol = Protocol.Sftp .HostName = sFTPHost .portNumber = sFTPPort .UserName = sFTPUsr .Password = sFTPPwd .SshHostKeyFingerprint = sFTPHostKey End With Using session As New Session session.Open(sessionOptions) Dim sourceFiles() As String = IO.Directory.GetFiles(strHarvestPath) Dim pgp As New PGPLib() Dim batchFolder As String = BRApi.FileSystem.GetFileShareFolder(si, FileShareFolderTypes.Batch,Nothing) Dim publicKeyLocation As String = batchFolder & "\0x2C4F9010-pub.asc" For Each file In sourceFiles 'If Path.GetExtension(file) = ".txt" Then If Path.GetFileName(file) = "Country_Hierarchy.txt" Then Dim strOutputFile As String = strHarvestPath & "\" & "Country_Hierarchy.txt" &".pgp" pgp.EncryptFile(file, publicKeyLocation,strOutputFile) 'BRAPI.ErrorLog.LogMessage(si, file) Dim transferOptions As New TransferOptions Dim transferResult As TransferOperationResult transferOptions.TransferMode = TransferMode.Automatic transferResult = session.PutFiles(strOutputFile, "/Output/" & sFTPFolder & "/Metadata/*", blnRemoveFiles, transferOptions) transferResult.Check() End If Next session.Close() End Using Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End NamespaceThis code is perfectly working for region export. so I am reusing it for Country export. error is:1) Unable to execute Business Rule 'Export_Country_Hierarchy'. 2) Invalid object name 'Country'.
Hi vpidala ,
Looks like this rule is using a Custom Table Table:
Do you have this table created within your environment? You can check here towards the end where all the Custom Tables are:
You will there likely find the Region Table from the other dimension.
Thank you aformenti . I checked and the table does not exist under the application db. I need to create a one, so I am trying to find out the database and schema details to request DB team create one. May I know how to find the database name & Schema that the existing tables are connected? also, Do I need to create a table only or structure of the table (column names as per the BR, etc)? any suggestions please?