Copy Data Between cubes; Failing due to consolidated members

[michaelc99]

Good Morning Everyone,

I have a manual Excel process that retrieves data from Local Cubes, then combines that data with manually-input values and pushes the updated values back into the Local cubes. So, in an attempt to semi-automate the process via TI processes, I built two cubes (let's call them CubeA and CubeAInput) where the only difference is the period dimension. In my setup, CubeA uses PERIOD MAIN and CubeAInput uses PERIOD MONTH MAIN.

Note: Under PERIOD MAIN, the element "JUN 23 YTD" is a leaf level whereas it is a consolidated member in PERIOD MONTH MAIN. Additionally, "JUN 23" is a consolidated member in PERIOD MAIN and a leaf level in PERIOD MONTH MAIN. The purposes of these dimensions, and their specific setup, goes back to a business decision made before I joined my company.

The error that I am facing is due to the consolidated v. leaf level issue in the period dimension. Have I reached a dead-end in my approach or am I missing something?

Thank you in advance,
Michael

[Steve Rowe]

In this situation you would add an attribute to use for the mapping.

sPeriodToWriteTo = Attrs ('PERIOD MAIN' , vPeriodInSource, 'Mapping Attribute to PERIOD MONTH MAIN');

CellPutN('CubeAInput' ,..... sPeriodToWriteTo.....);

If it is just that your source has YTDs at leaf and your destination movements then just set your export up to use the consolidated movements in the source view. Make sure you set the other dims to leaf and uncheck / turn off the suppress consolidations option in the view.

Hope this helps.

Cheers,

[michaelc99]

In this situation you would add an attribute to use for the mapping.

sPeriodToWriteTo = Attrs ('PERIOD MAIN' , vPeriodInSource, 'Mapping Attribute to PERIOD MONTH MAIN');

CellPutN('CubeAInput' ,..... sPeriodToWriteTo.....);

If it is just that your source has YTDs at leaf and your destination movements then just set your export up to use the consolidated movements in the source view. Make sure you set the other dims to leaf and uncheck / turn off the suppress consolidations option in the view.

Hope this helps.

Cheers,

Just changing the month from JUN 23 YTD to MAR 23 YTD to reduce typing. This is effectively what I have in both dimensions, so I will be going to from PERIOD MONTH MAIN to PERIOD MAIN:

periodmonthtm1forums.png (9.42 KiB) Viewed 702 times

So, updating your recommendation, then I would enter:

sPeriodToWriteTo = Attrs ('PERIOD MONTH MAIN' , vPeriodInSource, 'Mapping Attribute to PERIOD MAIN');

And, I would need to build a new attribute and store the entered value on MAR 23 YTD (consolidated member) in PERIOD MONTH MAIN to MAR 23 YTD. Let me test that on my data set and see if the issue is resolved.

Thank you,
Michael

[Steve Rowe]

I think this is the second use case that I outlined.

You need to set your export up to use the C level of Period Month Main (the YTDS) and in order to get them to appear in the query you will need to uncheck the Exclude Consolidations in View check box (or use the equivalent TI command).

The both the source and destination will contain the movement periods and no mapping of the data will be required.

If you continue along the first use case I outlined you will end up writing YTD values into movements.

[michaelc99]

Just closing the loop -

Thank you Steve for the above support. I was able to update my code and get the desired results to fire correctly.

Thank you again,
Michael