Im new and need help merging data from two dimensions to one

[olapc]

Hello and thanks for helping me in advance!

I have worked before with Planning and now started learning TM1 which is whole different world.
I'm having probably a simple problem which I haven't yet worked out myself even though tried many times and wasted hours.

PROBLEM is that I have two cubes,
1. first cube got time dimensions separated as Months (Jan, Feb, ...) & Years (2008, 2009, ... 2020), and
2. second cube is formed with only one time dimension Years/Months (2008-01, 2008-02, ... 2016-12).
They both share the same item from which I'm bringing the data.

I have to bring calculated data back from cube one to cube two in this way (Jan, 2008) goes to (2008-01)
and so forth whole package all the way to the end of year 2016 with lots of data. Of course I could write single
code for every cell but I was hoping there was a more decent way to handle this.

Problem sorted here in matrix,
(1,1) (2,1) (3,1)
(1,2) (2,2) (3,2) CONVERSION ==> (1,1) (2,1) (3,1) (4,1) (5,1) (6,1) ... (9,1)
(1,3) (2,3) (3,3)

In my situation theres 156 cells of data.

Any suggestions gratefully received
J

[qml]

How are you planning to "bring" these data to your target cube? Do you want to physically copy the data using a TI process or do you want to use rules to pull data on demand in real time, physically keeping them in your source cube only?

[olapc]

Thank you for replying.

This data is to be updated in real time and physically it can remain in the source cube but its also used in calculations in this cube. Am I making any sense?

[qml]

Its sounds like a fairly straightforward task.

Firstly, I would create an alias for the month dimension (let's call it "Numeric_Month") that would store "01" for Jan, "02" for Feb etc.

Then, your rule (in the target cube) should look something like this:

Code: Select all

[<range the rule should work for>] = N: DB ('source_cube', <some dimensions>, SUBST(!period,1,4), SUBST(!period,6,2), <some more dimensions> );

I'm assuming your single time dimension is called "period" in this case and am using the SUBST function to extract the year part and the (numeric) month part. Since numeric month is now an alias, TM1 is able to identify the right elements.

Provided your design is reasonable and you are using SKIPCHECK, you will also need to create a feeder statement in the SOURCE cube's rule file:

Code: Select all

[<feeding range>] => DB ('target_cube', <some dimensions>, !year | '-' | ATTRS ('month',!month,'Numeric_Month'), <some more dimensions>);

[olapc]

I tried different variations based on your suggestion but wasn't able to retrieve any values. I created alias for Months dimension like you told. In your versions !period is same as !year_months (elements: 2008-01, 2008-02..).

['Item1''] = N: DB('Production Plan', 'item1', !organization, SUBST(!year_months, 1, 4), SUBST(!year_months, 6,2));

I also flipped the SUBST commands other way around with the same end result. How does TM1 recognize to use SUBST commands as one dimension? If my year_months dimension got 2008-01 should this command return 2008 01 as value and spread the dimension with data for every cell it recognizes?

[olapc]

I made a test cube and got it to work.

Thank you for the advices qml. I'll sort the rest out for myself.