Load data to the cube, with a business rule, without transformation and validation

[Sergey]

Dear Community,

For an allocation purpose, I would like to load data currently stored in a data table, into a cube. But to speed up the process, I would like to ignore Validation and Transformation steps. Is there a way to do so ?

Regards,

[ChristianW · Accepted solution]

You can launch a custom business rule to load from a datatable or a csv file using a calculation.

• Query the table or load a file (you will find samples in the snippets)
• Loop the rows/lines
• Identify the pov
• Use a calculation API.data.calculate($“{pov} = {amount}“,…,True)

The true is for the stickiness of the calculation and is required to let the data survive the next calc or consolidation.

[Sergey]

This is very efficient and works like magic !

Thanks Christian !

EDIT : @ChristianW Do you know if V#QTD can be used for this function ?

[chris_rothermel]

Another approach is to use MemberScriptAndValue.  See Cameron and Celvin's book "OneStream Planning - The Why How and When" p 71.

MemberScriptAndValue Calculations Outside the Data Unit. However, the need to write outside a Data Unit exists. This is accomplished through the MemberScriptAndValue object, the List(Of MemberScriptAndValue) collection, and the BRApi.Finance.Data.SetDataCellsUsingMemberScript Method.

Using a Member Script string, the MemberScriptAndValue is assigned a tuple and data value, the MemberScriptAndValue is added to the List(Of MemberScriptAndValue) list collection (collections can have many Key Value Pairs or just one), and the BRApi.Finance.Data.SetDataCellsUsingMemberScript Method writes the list collection to the database.

This technique ignores Data Unit boundaries; values can be written to any Data Unit in the Cube or even outside the Cube. The MemberScriptAndValue approach cannot write to O#Import; it is O#Forms only. As discussed in the Level 2 Data Unit section, this is a net positive as O#Import data can be overwritten given the right data circumstances.

Note: Although the primary use case for this technique is for writing outside the Data Unit, it can be used within as well.

Dim objMemberScriptValue As New MemberScriptAndValue
Dim objMemberScriptValues As New List(Of MemberScriptAndValue)
Dim strMemberScript As String

Dim objMemberScriptValue As New MemberScriptAndValue
Dim objMemberScriptValues As New List(Of MemberScriptAndValue)
Dim strMemberScript As String