Copying data from one year to another year

Unknown

Hi Community,

 

I have a requirement in which I have to give the flexibility to the user to select any year (reference) and move the data to another year (target). 

Any suggestion !!!!

Björn Reuber

Hi,

 

there are different approaches to get this.

For example you can create an entity to select the certain numbers of years to shift (containing 1 year, 2 year,..). On this entity you define a cube containing 1 for 1 year, 2 for 2 years, etc. [yes in this scenario you doesnt select the source year, you only select from how long ago the data should be shifted)

 

In your procedure the user only has to decide if he wants to shift the data for 1 year, 2 year,.. and according to the choice a certain df is executed (for this you only need different groups and if then else steps on the new cube).

 

I hope my explanation was understandable, if not, I can try to create a small sample.

 

regards

Björn

Unknown

Hi Bjorn,

Thanks for the quick reply, but my requirement say that I need to move/copy the data of one yea to another year.

One of my cube have data for 10 years(2007 to 2017) and my time range is set to 2005 to 2025. Suppose I m planning for 2022(Planning Year) and I find that my 2010(Reference year) data is suitable for my planning. So what I want is, to copy the data of 2010 to 2022 and play around to create my plan.

   I thought to use previous year function, but that will be a rigid solution not a dynamic solution. The user can select any year as his reference year. 

 

KR

Sanjay

Björn Reuber

Hi,

 

is your data on daily, monthly or yearly base? Did you tried to use the "Dynamic Offset" Function:

The DataFlow process 

 

regards

Björn

Daniele Di Lorenzo

Hello

 

use 2 flag cubes by month, one to set the starting year (A)  and 1 with the destination. (B)

create a timeless cube (with out time entity), called T

you have to move data from cube 1 to cube 2

 

set a procedure like this

 

clear cube T

select year on cube A

data flow 1 >> T

select year on cube B

data flow T >> 2

 

best regards

Björn Reuber

Hi Daniele,

 

if you create a "timeless" cube your solution won't work if source and target is stuctured not on Year base, but on Day/Month (remember the dataflow rules: The DataFlow process ).

Assuming you have month based cubes for Source and Target.

In your first Dataflow the monthly values will be cumulated to a yearly Value.

Rule:

When a factor has one dimension that the target cube doesn’t have, it will be aggregated. For example: The factor cube is structured by Customer/Product/Month and the target cube by Product/Month. Before the calculation is run, the factor is aggregated for all selected Customers.

Than in 2nd DF the Yearly value is writen (12 times) to the month based target (cause board is repeating the value)

Rule:

When the target has a dimension that a factor doesn’t have, the factor value is repeated for each member of the exceeding dimension of the target. For example, if one of the factors is Commission Percentage, structured by Salesperson/Month, and the target is Commission Percentage, structured by Salesperson/Product/Month, the same Commission Percentage value will be repeated for each Salesperson/Month for all Products.

 

 

Regards

Björn

Daniele Di Lorenzo

yes, you're right, the original request is not so detailed and I jsut share an approach I use in some applications.

 

but yes, the flag cubes are with the same time dimension, and I use extract-realod options for the 1st step.

 

I guess the advanced offset based on a cube managed by the user is the best way 

Unknown

Hi,

Can you make a simple sample...!!

 

KR

Sanjay

Björn Reuber

Hi

 

I can try, but did you tried my first suggestion using the dynamic offset?

 

regards

Björn

Miguel Angel Sisternas Seaton

I find the easiest way is to copy to an intermediate cube and from there to the final one:

 

Interactive selection. (A)

Copy from normal cube (with interactive selection A) to dummy cube.

Dummy now has A

New Interactive Selection (B)

Copy from dummy cube (A) to normal cube (with new interactive selection .

 

In the example below i copy the projects to activities mapping from one project to another. From my normal cube, to a C cube (short for copy cube) and then back to my normal cube.

 

Unknown

Hi

 

I have tried you suggestion. but its no working for me. I create a blank cube with n of years to copy and make that cube data entry enable with check box, then in the data flow i used that cube for dynamic offset of the target cube.

Björn Reuber

Hi,

 

the offset is based on the source cube,

for example I have a shift per 2 in 2013

so all Values from 2013 are shifted by 2 month to target cube:

so if you want to move Values from 2013 to 2023 you need to shift it with 10 (Years) * 12 (cause my cubes are based by month. So you need to type in 120 to the year 2013

 

Hope this short description is fine for you

 

regards

Björn

Previous Member

Good morning, if you still need help I can explain my solution (this solution offers many choices to users).

The user can choose a budget month and feed it with a month of his choice.

In the attached image (do not consider the second column) the selector uses a cube defined by "Month", "Anno mese origine"

You need to multiply the cube with the source data for this cube (a=source cube; b=selector cube; c= target cube: c=a*b)

The source cube must be defined by "Anno mese origine" and the other necessary dimensions (no Month). The target cube must be defined by "Month","Anno mese origine" and the other necessary dimensions.

At the end of the procedure your budget will be on the "Month" dimension.

 

For your purposes you have to structure the cubes by year instead of month ("Year", Anno origine").

Since a dataflow is used, it is necessary to optimize the dabase and limit in any way the number of dimensions involved.

 

Otherwise there is a less elegant solution where data extraction is necessary.

 

I generally prefer to do this with data flows.

Paolo Messa.

Unknown

Hi Björn Reuber,

I am getting this error.

Björn Reuber

Hi Sanjay Bindhani

 

sorry, but without knowing your application or what you have done it's impossible to help you.

So please include at least the information of cube structure (including dense/sparse) and detailed Layout configuration

 

regards

Björn

Unknown

Hi Björn Reuber,

 

Both my target as well as source cube have same structure with day(time entity), article(s), and store(s).

 

KR

Sanjay

Björn Reuber

Hi,

 

do you get the error message on the DataFlow action or on DataEntry? Cause if its on Dataentry (as the screenshot says) the settings of your layout are necessary to know

 

regards

Björn

Gerrit Kohrs

Hi all,

 

interesting solutions so far. I don't have the time to try it right now, but I just had another thought:

 

• Create custom time entity that contains only the number of the month (aggregation of "Month" without the year)
• Add relation to the regular "Month" entity
        - for example: 201809 [Month] -- 09 [NumberOf Month]
• Build a TMP-cube that contains all entities of your source cube and substitute the Month entity with NumberOfMonth
• Create procedure with
  1. Interactive selection for the source year
  2. DF from source cube to TMP-cube
  3. Interactive selection for the target year
  4. DF from TMP-cube to target cube

 

Any thoughts on this? Could this work?

 

Kind regards,

Gerrit