SQL with Onestream

Hi basszo50,

In order to create custom tables in OS I would probably suggest you use the Table Data Manager (TDM) solution available on Solution Exchange.

You can then build a dashboard with a SQL editor component that points to that table. That will give users access to view and update data.

If you want to be loading data from an excel or delimited file, the following code snippets might help you getting started (ensure you follow the file setup as per design and ref guide - link ):

Delimited file:

'Select Load options
Dim loadMethod As String = "Replace"                   '<-- Select file load method (Replace, Merge)
Dim dbLocation As String = "App"                       '<-- Specify db type
Dim tableName As String = "XFT_Employee"               '<-- Specify table name to load
Dim blnSkipHeader As Boolean = True                    '<-- Specify if the first row should be skipped (True/False)
      
'Note: file path must be accessible from all application servers
Dim fileName As String = "DelimitedFileName.csv"       '<-- Enter name of delimited file
Dim filePath As String = "\\FileServerName\FilePath\"  '<-- Enter path to file

'Define columns that are in the table (this must match the fields in the deminted file)
 'Update the fields with the column names/types to match the table being loaded
Dim fieldTokens As New List(Of String)
fieldTokens.Add("xfGuid#:[Field1]::NewGuid")
fieldTokens.Add("xfText#:[Field2]")
fieldTokens.Add("xfInt#:[Field3]")
fieldTokens.Add("xfBit#:[Field4]")
fieldTokens.Add("xfDec#:[Field5]")
fieldTokens.Add("xfDbl#:[Field6]")
fieldTokens.Add("xfDateTime#:[Field7]")

'Execute file load
BRApi.Utilities.LoadCustomTableUsingDelimitedFile(si, SourceDataOriginTypes.FromFileShare, filePath & fileName, Nothing, ",", dbLocation, tableName, loadMethod, fieldTokens, blnSkipHeader)

Excel file:

'Load Excel File
'Note: file path must be accessible from all application servers
Dim fileName As String = "ExcelFileName.xlsx"          '<-- Enter name of Excel file
Dim filePath As String = "\\FileServerName\FilePath\"  '<-- Enter path to Excel file
   
'Note: dbLocation, TableName, LoadMethod & Field names defined in header of each excel "xft" range.
BRApi.Utilities.LoadCustomTableUsingExcel(si, SourceDataOriginTypes.FromFileShare, filePath & fileName, Nothing)

 

You'll probably also find this Tech Talk useful:

Tech Talks After Hours - Mining for Gold (Code) - SQL Parameterization