I need to copy all data from one member to another member from the same dimension.
To be more specific, I have a scenario dimension with these members:
->Budget
->Forecast
And I want to start my Forecast with the Budget data from last year * 1.05. Because I will change the values from forecast I can't just put a formula (forecast = budget *1.05, if I do that the cube will always show the formula values and not some new values that I want to enter).
Is there a easy way to do that? Any tip will be great.
Thanks,
Handerson
You should be able to achieve what you are trying to do using SCOPE and FREEZE in the cube MDX script. There are actually some good examples in the Adventure Works cube script. Open the script and do a find on "quota". This will take you to a section in the script where there are several examples that are used to populate a measure called "Sales Amount Quota" in the Adventure Works cube.
HTH,
- Steve
|||Steve,
I have installed the Adventure Works cube but I can't find the scripts that you sad, where I can find then? I'm using SQL Server 2005.
I looked the Scope and Freeze command, you sugest that I do that:
Forecast01 = Budget * 1.05
Freeze(Forecast01)
After that the users can change de Budget values without change the Forecast, ok. But the users can change the Forecast value using write-back?
And every month I will generate a new Forecast, like this:
Forecast02 = Forecast01
Freeze(Forecast02)
Next month I will do:
Forecast03 = Forecast02
Freeze(Forecast03)
And so on... It'll be performatic? Because of that I was trying to find a way to copy the data instead of build a formule.
Thanks,
Handerson
|||Handerson,
Here is the script segment that I was referring to. With regard to your question, I thought that you were starting from a base period and then trying to leverage the data that was there into future periods. If you truly need to copy data then you would probably be better off doing this at the source. You can still leverage MDX to show future periods that do not have data using the Scope and Freeze functions. You can also check to see if there is an existing value before you compute a projected value.
/*--
| Sales Quota Allocation |
--*/
/*-- Allocate equally to quarters in H2 FY 2005 */
Scope
(
[Date].[Fiscal Year].&[2005],
[Date].[Fiscal].[Fiscal Quarter].Members,
[Measures].[Sales Amount Quota]
) ;
This = ParallelPeriod
(
[Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember
) * 1.35 ;
/*-- Allocate equally to months in FY 2002 --*/
Scope
(
[Date].[Fiscal Year].&[2002],
[Date].[Fiscal].[Month].Members
) ;
This = [Date].[Fiscal].CurrentMember.Parent / 3 ;
End Scope ;
/*-- Weighted allocation to remaining months */
// Pin quarterly values prior to assigning weights for months
// This is done in order to avoid overwriting the quarterly values
// once weights are entered for monthly values.
Freeze
(
[Date].[Fiscal].[Fiscal Quarter].Members,
[Measures].[Sales Amount Quota]
) ;
// Scope on month level in FY 2003 and onwards
Scope
(
[Date].[Fiscal Year].&[2003] : Null,
[Date].[Fiscal].[Month].Members
) ;
// Compute weights based on reseller sales ratio in previous year
This =
(
ParallelPeriod // Fetch reseller sales amount in previous year
(
[Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember
),
[Measures].[Reseller Sales Amount]
)
/
(
ParallelPeriod // Divide monthly value by quarterly value to obtain ratio
(
[Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember.Parent
),
[Measures].[Reseller Sales Amount]
) ;
// Allocate quarterly values to months according to weight
This = [Measures].CurrentMember * [Date].[Fiscal].Parent ;
End Scope ;
End Scope ;
sqlsql
mins on it and I