Forum Discussion

BenvanBergen's avatar
BenvanBergen
Contributor
6 months ago

Create second line on a data import

Hello,

I have imported data from a csv file with unit price adjustments.

Data is transformed to use the correct UD2, UD5 with One to one transformation.

In some cases there should be a second data record created with a different UD2 and UD5, is it possible to do that with a complex expression, or should I make a calculation after import in business rule or with a member formula?

kind regards

Ben

Methodology
Planning
  • MarcusH's avatar
    MarcusH
    6 months ago

    I always use derivative transformation rules for creating additional data like that - it can be audited and is created based on rules that are easily managed. If you use a BR or member formula you lose the transparency.

  • MarcusH's avatar
    MarcusH
    6 months ago

    Derivative Rules are a separate type - they are listed at the end of the UD8 Dimension rules. You select the source for the extra records: 

    The you specify how the extra records will be created and what label the new member will have eg

    A#[COGS]=COGS_TEMP

    That will create a new record for every source record where the Account member is COGS. The extra records will have the member COGS_TEMP. The Design and Reference guide has a section called: BI Blend Derivative Rules. And you can also search OneCommunity for derivative to get a lot more help.

  • MarcusH's avatar
    MarcusH
    Contributor III
    6 months ago

    I always use derivative transformation rules for creating additional data like that - it can be audited and is created based on rules that are easily managed. If you use a BR or member formula you lose the transparency.

  • BenvanBergen's avatar
    BenvanBergen
    Contributor
    6 months ago

    Thanks Henning  and MarcusH, I had overlooked that and didn't find anything in the ABA training documentation. Should be able to make it work, I'll let you know.

  • BenvanBergen's avatar
    BenvanBergen
    Contributor
    6 months ago

    Thank you MarcusH, would that be used then in a BI Blend import? I could find some documentation on that, however in the regular transformation rules I didn't find anything on derivative rules.

    • MarcusH's avatar
      MarcusH
      Contributor III
      6 months ago

      Derivative Rules are a separate type - they are listed at the end of the UD8 Dimension rules. You select the source for the extra records: 

      The you specify how the extra records will be created and what label the new member will have eg

      A#[COGS]=COGS_TEMP

      That will create a new record for every source record where the Account member is COGS. The extra records will have the member COGS_TEMP. The Design and Reference guide has a section called: BI Blend Derivative Rules. And you can also search OneCommunity for derivative to get a lot more help.

      • BenvanBergen's avatar
        BenvanBergen
        Contributor
        6 months ago

        Thanks Marcus,

        You had given all that was needed, still it took me some time to figure it out.

        Below a screenshot of the settings that worked for me, creating a duplicate line with a different UD2:

        This changed line also needs a different UD5, by using below rule we could manage that, without

         

        further transformations.

         

        kind regards

        Ben

    • Henning's avatar
      Henning
      Valued Contributor II
      6 months ago

      Hi, there is some more information and examples in the PDF Design and Reference Guide (PDFs (onestream.com)) on pages 629 and following:

      (in the online documentation, I can also only find the BI Blend related documentation, but in the PDF, there is content that I was not able to locate otherwise)