Forum Discussion

raoulheinen's avatar
raoulheinen
New Contributor II
2 years ago

Seeding Cubes with extended account and UD1 dimension

Hi community, 

For one of my clients I need to do the following:

In cube 1 data is entered on an extended account dimension and extended UD1 dimension. 

Account dimension

         RootAccountDimension

                DetailedAccounts (members :10100) linked to cube 2

                       GLAccounts (members : 100001, 100002, 100003) linked to cube 1

 

UD1 Dimensions

           RootUD1Dim

                   SummaryUD1 (members :None) linked to cube 2

                           DetailedUD1 (members :Prop1, Prop2, Prop3, Prop4) linked to cube 1

 

Account Example is 100001, 100002, 100003 roll up to 10010 which is therefor a parent in cube 1 and is a base account in cube 2. Same for UD1 which has details in cube 1 and no UD1 details in cube 2. Example for UD1 is details prop1, prop2, prop3 and only the None member in cube 2

The data from cube 1 (entered on account and UD1 details)  then needs to be seeded to cube 2 which has different account and UD1 details which are on a higher level. Amount for account 100001, 100002, 100003 needs to be seeded to 10100 and sum of UD1 details need to be seeded to UD1 None in cube2.

Below is the start of a business rule that i used but does not give the expected outcome

 

 

cube1   Cube2  
Account UD1Amount  AccountUD1Amount 
100001Prop1100 10100None900
 Prop2100    
 Prop3100    
100002Prop1100    
 Prop2100    
 Prop3100    
100003Prop1100    
 Prop2100    
 Prop3100    
  900    

 

The example is just a short version of the full dataset.

Hope somebody has come across this issue before and has a solution for this that we can use. 

 

 

 

 

Finance

1 Reply

Sort By
  • db_pdx's avatar
    db_pdx
    Valued Contributor
    2 years ago

    Hi raoulheinen:

    I think you're close but need a few changes.

    1) for your sourceBuffer you should include U1#Top in the member filter

    2) you set/commit the buffer with api.Data.SetDataBuffer as the last step

    3) you need some additional info in your destination info.  Specifically, where top level members are going to fixed, base level members.  In this case your U1#None

    All together it would look something like (please double check for typos/accuracy):

    Dim sourceBuffer as DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(Cb#cube1:S#ActualM:C#Local:O#Forms:U1#Top))")
    Dim convertedBuffer As DataBuffer = api.Data.ConvertDataBufferExtendedMembers("cube2", "ActualM", sourceBuffer) 'note: you might need form 2 which uses entity in the function
    Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("O#Forms:U1#None") 'note: confirm you want it still in O#Forms vs O#Import
    api.Data.SetDataBuffer(convertedBuffer, destInfo,,,,,,,,,,,,,True) 'note: storing as durable. Confirm this is your requirement. Also, you can do additional filtering here while setting as well.

    Let me know if that works for you.  Cheers, -db