The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

kmd's avatar
kmd
Contributor II
3 years ago
Solved

Question: Is it Possible to Extract Data with Time Periods in Columns?

I know we can use the Matrix setting in our Data Sources to import data where time periods are in the columns (i.e. M1, M2, M3, M4, M5 M6 etc.). Is anyone out there aware of a way to extract data ...
Data Integration
Data Management
  • ChristianW's avatar
    ChristianW
    3 years ago

    Here it is:

     

    Dim dtObj As DataTable = BRApi.Import.Data.FdxExecuteDataUnitTimePivot(si, "Equipment Division", "E#NAE.Base", "Local", ScenarioTypeId.Budget, "S#Forecast3p9", "T#2023.base", "Periodic", False, True, "", 4, False)
Dim csvText As New Text.StringBuilder()

For Each drObj As DataRow In dtObj.Rows
	
	For Each dcObj As DataColumn In dtObj.Columns
		csvText.Append(drObj(dcObj.ColumnName))
		csvtext.Append(",")
	Next	
	csvText.AppendLine
Next

Dim folderName As String = "Documents/Public/Csv"
Dim fileName As String = "CsvFile.csv"
Dim csvFileInfo As New XFFileInfo(FileSystemLocation.ApplicationDatabase, fileName, folderName)
Dim csvFileObj As New XFFile(csvFileInfo, String.Empty, Text.Encoding.UTF8.GetBytes(csvText.ToString))
csvFileObj.FileInfo.ContentFileExtension = "csv"
					
Dim folderObj As New XFFolder(FileSystemLocation.ApplicationDatabase, folderName)
BRApi.FileSystem.InsertOrUpdateFolder(si, folderObj)
BRApi.FileSystem.InsertOrUpdateFile(Si, csvFileObj)

     

    It will save the file in "Documents/Public/Csv/"

    And here the functions details:

    Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnitTimePivot(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, useGenericTimeColNames, filter, parallelQueryCount, logStatistics)
ChristianW's avatar
ChristianW
Icon for OneStream Employee rankOneStream Employee
3 years ago

Yes, with some of the FDX commands you can export data to a datatable with time in the columns:

BRApi.Import.Data.FdxExecuteDataUnitTimePivot

kmd's avatar
kmd
Contributor II
3 years ago

Oh thank you so much!!  Coding is not my area of expertise - I don't suppose you have an example?

  • ChristianW's avatar
    ChristianW
    Icon for OneStream Employee rankOneStream Employee
    3 years ago

    Here it is:

     

    Dim dtObj As DataTable = BRApi.Import.Data.FdxExecuteDataUnitTimePivot(si, "Equipment Division", "E#NAE.Base", "Local", ScenarioTypeId.Budget, "S#Forecast3p9", "T#2023.base", "Periodic", False, True, "", 4, False)
Dim csvText As New Text.StringBuilder()

For Each drObj As DataRow In dtObj.Rows
	
	For Each dcObj As DataColumn In dtObj.Columns
		csvText.Append(drObj(dcObj.ColumnName))
		csvtext.Append(",")
	Next	
	csvText.AppendLine
Next

Dim folderName As String = "Documents/Public/Csv"
Dim fileName As String = "CsvFile.csv"
Dim csvFileInfo As New XFFileInfo(FileSystemLocation.ApplicationDatabase, fileName, folderName)
Dim csvFileObj As New XFFile(csvFileInfo, String.Empty, Text.Encoding.UTF8.GetBytes(csvText.ToString))
csvFileObj.FileInfo.ContentFileExtension = "csv"
					
Dim folderObj As New XFFolder(FileSystemLocation.ApplicationDatabase, folderName)
BRApi.FileSystem.InsertOrUpdateFolder(si, folderObj)
BRApi.FileSystem.InsertOrUpdateFile(Si, csvFileObj)

     

    It will save the file in "Documents/Public/Csv/"

    And here the functions details:

    Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnitTimePivot(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, useGenericTimeColNames, filter, parallelQueryCount, logStatistics)
    • kmd's avatar
      kmd
      Contributor II
      3 years ago

      Thank you SO much for this!!!!