How to bring the data?

[dharav]

Hello All

We have an Input cube XYZ as below:

Image.JPG (74.19 KiB) Viewed 3993 times

Now, at the end of every month, as a part of roll over process, we want to move data from actual (view 2) to forecast (view 1).

IF the location of data (counter number) is same in both cube than we can easily move the data as below

Image 2.JPG (118.73 KiB) Viewed 3993 times

Now if the location of data (counter number) is not same in both cube
than i couldn't able to bring data at correct place. I wrote following rule

['Forecast','Jan']=N:IF((DB('XYZ','Actual',!Counter,'Location')@=DB('XYZ','Forecast',!Counter,'Oracle Location'))
& (DB('XYZ','Actual',!Counter','Inventory')@=DB('XYZ','Forecast',!Counter,'Inventory'))
& (DB('XYZ','Actual',!Counter','ID')@=DB('XYZ','Forecast',!Counter,'ID'))
,DB('XYZ','Actual',!Counter','Jan'),STET);

I want to know what modification i can make to achieve the functionality. If i put the counter number where all above three condition met than it would work. Anythough on it.

I am giving a try through TI where I would take V1 (COUNTER VALUE) for actual version and loop it in to forecast version. If it satisfy the condition than it would paste actual data in to forecast at jan month and V1 counter.

Thank You so much in advance for your valuable time & input.

Thanks

Dharav Patel

[rmackenzie]

I want to know what modification i can make to achieve the functionality. If i put the counter number where all above three condition met than it would work. Anythough on it.

It looks like you've based your cube design on relational tables that you might find in SQL Server for example. You use the Counter dimension as the index and the Input dimension for the fields. If you want to build relational tables to store data in, you will be much better off using a relational tool! There are all sorts of trials and express versions out there to experiment with if you are not sure where to start. If you want/ need to use TM1 then you should:

* ditch the Counter dimension
* create separate dimensions for scenario (Forecast/ Actual), Locations, Inventory items, $ values (measure dimension) and those IDs
* understand the data is captured at an 'intersection' e.g. Jan, 2015, Actual, Location ABC, ID A2, Table, Revenue from Sales - the data stored at this point has a value of $300

Once you've done this the 'address' of equivalent data points in Forecast and Actual will differ only by the Scenario value and you no longer have to be concerned about indices. The combination of dimension elements are forming a composite primary key if you are familiar with this relational concept.

I reckon of all the people I met in the TM1 world, about 20-25% are actually relational thinkers pretending, or wanting, to be multidimensional thinkers. Of those 20-25%, probably half can cut it either way but some just can't escape the mental framework that they established for themselves from their RDBMS background. Probably these people are tool-learners and not concept-learners.

If you are struggling to operate without the Counter dimension then ask yourself - am I one of those people? You may enjoy working with a relational database more than you do with TM1; and this will increase your job satisfaction.

[dharav]

Hello rmackenzie

Thanks for your time and conceptual voice. Still,

We have to build the way it exist now in CognosEP. I have already suggested to business people about the OLAP design. They are very reluctant about their existing design.

I achieved the functionality in TM1. I created 5 processes to conduct my rollover process.

1) to bring the actual data in to forecast for actual month (to map data point from one version to another)
2) to bring data from actual version to forecast version (the actual data for which there was not any forecast)
3) to move data from forecast to forecast period.
4) Create a cell security rule which would apply "READ" Security to the actual months in forecast. This rule is based on attributes
5) Create a TI to update attributes on the basis of the look up cube.

It works as well as faster.

Thanks

Dharav

[BariAbdul]

Hi Dharav,Just curious ,Why would you need three process for first three in the order you mentioned,Couldn't it be achieved in one process? Thanks

[rmackenzie]

Thanks for your time and conceptual voice. Still,

We have to build the way it exist now in CognosEP. I have already suggested to business people about the OLAP design.

Oh that's a shame. Why not suggest an OLAP based POC alongside the EP rebuild? Do a parallel run?