Copy one dimension data to another identical dimension in the same model

[raj_vangala]

Hi

I have a planning model with two identical dimensions in it , Account (Account Type) and GL Account (Generic).

Initially we used GL account dimension to mitigate the single hierarchy issue that we could create on top of Account Type dimension, it was useful for other reasons as well - so far so good.

Now while budget data inputted through input schedules only Account dimension has used so with that GL Account assigned with "#".Budget data stored in the model something like below :

How can I copy Account column data to GL Account Column so that the reports that were created on top of GL Account dimension can work.

Thanks for your help.

Kind Regards

Raj

[N1kh1l]

raj_vangala

Considering the below from your question

Account - Account Type Dimension

GL Account - Generic Account Dimension

You want to assign Gl Account with Account Master data in your Budget Dataset.

My original data is on Actual and I am doing the assignment in Budget to clearly show before and after. You can use RESULTLOOKUP() if you are running the data action on Budget itself.

MEMBERSET [d/Measures] = "AMOUNT"
MEMBERSET [d/NA_ACCOUNT]="#"  // Generic Account Dimension

DATA([d/NA_ACCOUNT]=[d/NA_VDT_ACCOUNT]) = RESULTLOOKUP([d/Version]="public.Actual")

Output: original State

After the Data Action run:

Hope this helps !!

Nikhil

[raj_vangala]

Yes, that is exactly what I wanted but strangely it is not reading any records to process with the statement that you've mentioned and as well as William.

But when i tried restricting with all the dimensions in the MEMBERSET area, it is creating a new record rather than updating the existing record. see below:

What could be reason?

What is your model type and what are the settings?

It is the new model (model type) not classic.

It has following dimensions Version, Account (Account Type), Date (Month Granularity), Cost Centre,Profit Centre, Currency, Audit Trail, GLAccount , companycode (Organization) and 3 measures Amount_gc (Grp Currency),Amount_cc (Comp Currency), and Amount_tc (Tran Currency).

Thanks for your help.

Raj

[N1kh1l]

raj_vangala

My Model was new model with both account and measures. Adding more memberset to scope is ok and creation of new records is expected behaviour. The reason its creating new records is because you are changing the master data using an existing record. You have to explicitly write a DELETE() at the end of code block to clear the old records. The only time it affects the existing records without creating the new one is when you manipulate just the transaction value (arithmetic calc) without any change in master data of any dimension.

In this case its behaving on expected lines. I think it will work even without MEMEBRSET on Costcenter and CompanyCode.

Hope this helps !!

Nikhil

[raj_vangala]

Thanks Nikhil.

Creating a new record is fine, I can use it a create a new data set with new version but it is only creating rows for 1 account, though the data set has 14 accounts - it is just creating the first account in the dataset.

Data set:

It is creating new rows just for 90393 for all 12 months

Logic:

As you can see I've tried various options in the memberset area on account dimension but nothing worked.

what could be the reason?

Many thanks

Raj

[N1kh1l]

raj_vangala

It should have worked for all Accounts as per the scope defined. Please check if you have any data access defined at account level or validation rules. If not Try manually entering values for the accounts in a different version to see if you face any error in manual planning for these accounts. Not sure about your SAC version but with latest QRC release ability to set a trace on Data Action has been enabled. Trace will really help to know what accounts are getting scoped during the code run. As a last step you can raise a ticket to SAP.

Nikhil