Import Data Extracted via data Management using Extensibility Business Rule

Wei
New Contributor III

Hello - we would like to move specific Forms and Adj data from one application to another - data are extracted via Data Export in DM, does anyone have an example of the Extensibility Rule (BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile) to load the extract data? Many thanks - Wei

1 ACCEPTED SOLUTION

NickKroppe
Contributor

Hi,

 

Below is an example snippet of using the SetDataCellsUsingCsvFile BRApi. As always, please test in a Development application first!

 

 

 

'get app config settings
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
'retrieve exported cube data file from a path on the current application's fileshare
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppName)
'define the full path including the file name
Dim filePath As String = folderPath & "\SomeFileNameFormsData.csv"
'next check if the file exists on the file share...
If File.Exists(filePath) Then
	'define the source origin members
	Dim sourceOrigins As New List(Of String)
	sourceOrigins.Add("Forms")
	'define target origin member
	Dim targetOrigin As String = "Forms"
	'process the file and load the data - do not load zeros
	BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile(si, filePath, ",", sourceOrigins, targetOrigin, False)
End If

 

 

 

Nick Kroppe

OneStream Software

View solution in original post

18 REPLIES 18

NickKroppe
Contributor

Hi,

 

Below is an example snippet of using the SetDataCellsUsingCsvFile BRApi. As always, please test in a Development application first!

 

 

 

'get app config settings
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
'retrieve exported cube data file from a path on the current application's fileshare
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppName)
'define the full path including the file name
Dim filePath As String = folderPath & "\SomeFileNameFormsData.csv"
'next check if the file exists on the file share...
If File.Exists(filePath) Then
	'define the source origin members
	Dim sourceOrigins As New List(Of String)
	sourceOrigins.Add("Forms")
	'define target origin member
	Dim targetOrigin As String = "Forms"
	'process the file and load the data - do not load zeros
	BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile(si, filePath, ",", sourceOrigins, targetOrigin, False)
End If

 

 

 

Nick Kroppe

OneStream Software

Wei
New Contributor III

Thanks Nick - all tested and works well!

Hello,

We tried this, the rule executes fine, but we don't see any data in the destination scenario. Will you please share sample CSV file? DO you have to add any named range or so in the CSV file?

 

Thanks,
Nidhi Mangtani

Wei
New Contributor III

Hi Bharti,

The data was exported via Data Management job, and no need to add any named range to it.  

Here is an example - hope that helps 

Thanks

Wei

Cube Entity Parent Cons Scenario Time View Account Flow Origin IC UD1 UD2 UD3 UD4 UD5 UD6 UD7 UD8 Amount HasData Annotation Assumptions AuditComment Footnote VarianceExplanation
XXXXXXXX USDPlan_Working2023M1YTDAxxxxxInputFormsNoneNoneNoneXXXBaseNoneNoneNoneNone2032000Yes      
                           

Wei
New Contributor III

Here is the screenshot of the example:

Wei_0-1656583628539.png

 

Hi Wei, 

Does this work for "Import" data, we tried this with  target origin "Import", but doesn't seem to be working ?

Thanks,
Nidhi Mangtani

Wei
New Contributor III

Hi Bharti,

I believe it works with "Import" data (based on the Reference Guide) - however I have not tested it before. 

Thanks

Wei

Kai
New Contributor III

Hi Bharti,

I am trying to use this function with target origin "Import" as well, but it doesn't work on my end either. It works perfectly when target origin is Forms. Did you get any alternative solution? Thank you

 

Kai

Hi Kai,

The only safe way would be to use the standard Import > Validate > Load process. 

The other alternate probably would be to load these "Import" cells to "Forms" and a dummy U8 member say U8#Flag. Once these get loaded, copy O#Forms:U8#Flag to O#Import:U8#Originalmember and clear off data from O#Forms:U8#Flag. We have not tried this, looks too complex. Will let you know if this works.

Thanks,
Nidhi Mangtani

Kai
New Contributor III

Thank you very much Bharti for your reply. I totally agree with you. I think the best practice is to keep "Import" cells just for data loaded from Import > Validate > Load, as this is "import" is designed for, but I will give copy method a try and see whether this will work. 

 

Kai

Hey Nick,

Not sure what the etiquette is in digging up old posts but I had another question in regards to this. Where should this data be landing once the SetDataCellsUsingUsingCsvFile function is run? Design & Ref says Stage. I couldn't see anything in stage or in the cube however. Tried getting some feedback from the returned XFResult to confirm its working but all I get is BoolValue = True.

 

A small paragraph in the Design & Ref guide is the only place in OneStream documentation I can find this mentioned although it mentions it as SetDataCellsUsingCsvFile rather than SetDataCellsUsingUsingCsvFile so it is hard to find. Am I missing anything?

 

SetDataCellsUsingUsingCsvFile.png

Hi Daniel,

I had tried setting the target origin member as "Import" and using the SetDataCellsUsingUsingCsvFile function, but it did not work.

Thanks,
Nidhi Mangtani

Wei
New Contributor III

Hi Daniel,

The function push the data into the cube.

Thanks

Thanks Wei. That is what I expected and helps narrow down my issue. Now it's just a matter of figuring out why that isn't happening. 

chris_rothermel
New Contributor III

@DanielWillis  I'm having the same issue.  I'm able to run the load but I don't see any data loaded to the cube using this method.  Did you figure out your issue and if so can you help me along?

 

Hey Chris. No, unfortunately I put it in the too hard basket as it was more of a side thing at the time and haven't gotten back to it. Its definitely a function I'm keen to understand though.

Hello,

I was able to load data using SetDataCellsUsingUsingCsvFile function. Followed the below mentioned steps:

1. Export source data using a DM step. This will generate a CSV file. The format of CSV file should not be changed for loading in destination application.

2. Segregate Forms and Import data manually or through a BR, this would create two CSV files say "SourceImport.csv" and "SourceForms.csv".

3. "SourceForms.csv" data can be loaded in destination scenario/application using the SetDataCellsUsingUsingCsvFile function.

4. "SourceImport.csv" would have to be loaded via the usual Import, Validate, Load step.

Hope this helps.

Thanks,
Nidhi Mangtani

Thanks Daniel and Nihi,

This step is the trouble:  3. "SourceForms.csv" data can be loaded in destination scenario/application using the SetDataCellsUsingUsingCsvFile function.

I've run the code that uses the SetDataCellsUsingUsingCsvFile (UsingUsing) and although the Extensibility Rule says it ran without error it shows no sign of working (no records were loaded). 

Meanwhile I'm able to write XFSetCell commands but that is quite cumbersome for large data sets.

Disappointed SetDataCellsUsingUsingCsvFile is not working for Daniel and I and disappointed there is scant documentation on this process.

-Chris