Query on annotation data copy

Hi All,

I am aware of various options available for copying scenario data in OneStream. These options (data management or custom calculate) usually copy only periodic data across scenarios. Any idea how annotation data can be copied from sceanario1 to scenario2 in OneStream?

Any leads would be appreciated.

Thanks

Bharti

Admin

PeterFu
3 years ago
Hi,

If you create a Finance Business Rule and under CustomCalculate you run this syntax, api.Data.SetDataAttachmentText(). That should copy your annotation, but you challenge will then be to make it more dynamic. Probably use sql to create a data table for data attachments.

Peter
SWilyums
2 months ago
Here is an example I used to copy VarianceExplantions from one scenario to another. I am only copying the text values not files. I also run it from a DM Step using Custom Calculate.
#Region "Seed Prior Fcst VarianceExplanations to current Fcst"
Else If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Seed_VarExp_to_Curr_LE") Then
'Query DataAttachment table for VarianceExplanation values for the sourceScenario
'Write those values to a datatable (in-memory)
'Loop through the rows in the datatable and use api.Data.SetDataAttachmentText to assign the values to the WFScenario
Dim sourceScenario As String = args.CustomCalculateArgs.NameValuePairs("sourceScenario")
Dim povEntity As String = api.Pov.Entity.Name
'AttachmentType = View|200 = Annotation|220 = Assumptions|221 = AuditComment|222 = Footnote|223 = VarianceExplanation
Dim sql As New Text.StringBuilder()
sql.AppendLine("SELECT *")
sql.AppendLine("FROM DataAttachment ")
sql.AppendLine("WITH (NOLOCK) ")
sql.AppendLine("WHERE Time = '"& povYear & "M12" & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Scenario = '" & sourceScenario & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Cube = 'XXXX' ")
sql.AppendLine("AND ")
sql.AppendLine("Entity = '" & povEntity & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Cons = 'USD' ")
sql.AppendLine("AND ")
sql.AppendLine("AttachmentType = '223' ") 'View Dimension

Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString,False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows
api.Data.SetDataAttachmentText("A#" & dr.Item("Account") & ":V#VarianceExplanation:F#EndBalLoad:O#BeforeAdj:I#None:U1#" & dr.Item("UD1") & ":U2#No_BU:U3#No_Loc:U4#None:U5#None:U6#None:U7#Plan:U8#None", dr.Item("Text"), True)
Next
End If
End Using
End Using
#End Region

TonyToniTone
Contributor II
3 years ago
The Annotations or Commentary is not located in any of the Data Record tables. The Annotation and Commentary are stored in a table called DataAttachment where each comment is stored with a unique ID. Since the commentary is not directly stored in the Data Record tables, the Data Management Copy step using V#Annotation will not work through Data Management.
Peter is on the right path using the api.Data.SetDataAttachmentText() function to "copy" the data. You will need a SQL Statement to select the source data from the DataAttachment table to store in a temp table to then "copy" to another scenario using the SetDataAttachmentText() function. The data will be stored in the same DataAttachment table. Also, as Peter mentioned, some process like a Custom Calculate will have parameters to pass into the Custom Calc BR to execute in order to define what Scenario to "copy" to. Example of SQL Statement:
'Get annotation data table sql statement
Dim sql As New Text.StringBuilder()
sql.AppendLine("SELECT * ")
sql.AppendLine("FROM DataAttachment ")
sql.AppendLine("WITH (NOLOCK) ")
sql.AppendLine("WHERE Time = '"& povTime & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Scenario = '" & sourceScenario & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Entity = '"& povEntity & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Cons = '"& destinationCurrency & "' ")
- SirOti
  New Contributor
  2 years ago
  Yes, this is definitely the way to go. By the way, have you successfully copied over data attachments that are files rather than texts? If yes, how did you approach it?
  - TonyToniTone
    Contributor II
    2 years ago
    No I have not tried to copy over the data attachment file. The Data Attachment table has a column called "FileName" and a column called "FileBytes". Whenever a data attachment is uploaded to a specific data cell or a data unit, each record from within the data attachment file is stored as a unique record and tagged with the File Name. That File Name will have a corresponding unique File Bytes ID. This File Bytes ID is shared with each data record from the data attachment file. There are 2 GetDataAttachments functions - 1 to get Data Attachment List by DataCellPk and 1 to get Data Attachment List by Member Script
    
    Member Script
    
    Dim objDataAttachmentList As DataAttachmentList = BRApi.Finance.Data.GetDataAttachments(si, memberScript, includeFileBytes, startRowIndex, pageSize)
    
    DataCellPk
    
    Dim objDataAttachmentList As DataAttachmentList = BRApi.Finance.Data.GetDataAttachments(si, dataCellPk, includeFileBytes, startRowIndex, pageSize)
    
    These functions should be able to get the Data Attachment files. However, there is not a SetDataAttachmentsFile function to update the Data Attachment table. I'm sure there is a more creative way to update the Data Attachment table without having a SetDataAttachmentsFile type function using a combination of SELECT, INSERTS, and UPDATES
Mustafa_A
Contributor II
3 years ago
Hey NidhiMangtani
Have you tried changing you view to "annotation" and change your source/target scenario as per your requirement.
- NidhiMangtani
  Contributor III
  3 years ago
  Yes we have tried this but it doesn't copy annotation data.
  - PeterFu
    Contributor II
    3 years ago
    Hi,
    
    If you create a Finance Business Rule and under CustomCalculate you run this syntax, api.Data.SetDataAttachmentText(). That should copy your annotation, but you challenge will then be to make it more dynamic. Probably use sql to create a data table for data attachments.
    
    Peter
SWilyums
Contributor
2 months ago
Here is an example I used to copy VarianceExplantions from one scenario to another. I am only copying the text values not files. I also run it from a DM Step using Custom Calculate.
#Region "Seed Prior Fcst VarianceExplanations to current Fcst"
Else If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Seed_VarExp_to_Curr_LE") Then
'Query DataAttachment table for VarianceExplanation values for the sourceScenario
'Write those values to a datatable (in-memory)
'Loop through the rows in the datatable and use api.Data.SetDataAttachmentText to assign the values to the WFScenario
Dim sourceScenario As String = args.CustomCalculateArgs.NameValuePairs("sourceScenario")
Dim povEntity As String = api.Pov.Entity.Name
'AttachmentType = View|200 = Annotation|220 = Assumptions|221 = AuditComment|222 = Footnote|223 = VarianceExplanation
Dim sql As New Text.StringBuilder()
sql.AppendLine("SELECT *")
sql.AppendLine("FROM DataAttachment ")
sql.AppendLine("WITH (NOLOCK) ")
sql.AppendLine("WHERE Time = '"& povYear & "M12" & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Scenario = '" & sourceScenario & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Cube = 'XXXX' ")
sql.AppendLine("AND ")
sql.AppendLine("Entity = '" & povEntity & "' ")
sql.AppendLine("AND ")
sql.AppendLine("Cons = 'USD' ")
sql.AppendLine("AND ")
sql.AppendLine("AttachmentType = '223' ") 'View Dimension

Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString,False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows
api.Data.SetDataAttachmentText("A#" & dr.Item("Account") & ":V#VarianceExplanation:F#EndBalLoad:O#BeforeAdj:I#None:U1#" & dr.Item("UD1") & ":U2#No_BU:U3#No_Loc:U4#None:U5#None:U6#None:U7#Plan:U8#None", dr.Item("Text"), True)
Next
End If
End Using
End Using
#End Region

Forum Discussion

Query on annotation data copy

Related Content

Export data using a Method Query

Securty Roles Query

SQL Query to External Table

Netsuite ODBC TB query TimesOut

Carriage Return in Annotation data on Cube View