Time Dimension 2 PARENTH2 and PRIOR,NEXT

[former_member622327]

Hi all,

We have standart TIME hierarchy from AppShell (MONTH, QUARTER, YEAR). But in some cases users want insert data in detalization by week. We decide add additional ID's with detalizations to weeks and add they in second hierarchy. Everything is ok in system.

But than we try write scrip logic with PRIOR or NEXT which use data from previous year it doesn't work. But than we use PRIOR or NEXT with current year months everything ok.

Example of logic.

*XDIM_MEMBERSET TIME = PRIOR, %TIME_SET%, %YEAR%.DEC

*CALC_EACH_PERIOD

*WHEN P_ACCT
*IS A01
*REC(EXPRESSION=%VALUE% - GET(P_ACCT="A02",TIME=PRIOR), P_ACCT = A03) 
*ENDWHEN
*ENDWHEN

Then we save data on January it doesn't work. BUt on December prev year A02 and January A01 we have data.

Maybe you have some advises according second TIME dimension hierarchy. I think problem on it.

Best regards,

iaroslav

[JohnL]

Hi Iaroslav

Look into the DebugLogic.log file to see what TIMEID's are getting scoped into your logic.

You can post the log here as well and maybe we can have a look.

Thanks,

John

[former_member622327]

Hi,

Here is fragment of debuglogic.

****************************************************************************************************
Start time --->7:29:17 PM  -  Date:9/28/2011  (build code:7.5.107)
****************************************************************************************************

User:T2RU\bpc.sysadmin
Appset:DEVTELE2
App:BUDGETS
Logic mode:0
Logic by:
Scope by:CATEGORY,INPUTCURRENCY,P_BF,P_BUDDIM,P_BUDDIM2,P_BUSTYPE,P_CC,P_CITY,P_DATASRC,P_PROJECT,TIME
Data File:
Debug File:D:\PC_MS\DATA\WebFolders\DEVTELE2\BUDGETS\PrivatePublications\bpc.sysadmin\TempFiles\DebugLogic.log
Logic File:
Selection:DIMENSION:CATEGORY|BU|DIMENSION:INPUTCURRENCY|RUR|DIMENSION:P_BF|IBF19|DIMENSION:P_BUDDIM|NO_BUDDIM|DIMENSION:P_BUDDIM2|NO_BUDDIM2|DIMENSION:P_BUSTYPE|NO_PRODUCT|DIMENSION:P_CC|3_KEM_FF_FA|DIMENSION:P_CITY|KEM|DIMENSION:P_DATASRC|CCDATA|DIMENSION:P_PROJECT|PR_PL|DIMENSION:TIME|2012.JAN|
Run mode:1
Query size:0
Delim:,
Query type:0
Simulation:0
Calc diff.:1
Formula script:



Max Members:
Test mode:0
Is Modelling:1 

Query Type:0
Max members:
Region:
DIMENSION:CATEGORY
BU
DIMENSION:INPUTCURRENCY
RUR
DIMENSION:P_ACCT 
----- There a lot of accounts <All> accounts and I dont show it all
DIMENSION:P_BF
IBF19
DIMENSION:P_BUDDIM
NO_BUDDIM
DIMENSION:P_BUDDIM2
NO_BUDDIM2
DIMENSION:P_BUSTYPE
NO_PRODUCT
DIMENSION:P_CC
3_KEM_FF_FA
DIMENSION:P_CITY
KEM
DIMENSION:P_DATASRC
CCDATA
DIMENSION:P_PROJECT
PR_PL
DIMENSION:TIME
2012.JAN


DIMENSION:P_CC
3_KIR_FF_FA
DIMENSION:CATEGORY
BU
DIMENSION:P_DATASRC
CCDATA
DIMENSION:INPUTCURRENCY
RUR
DIMENSION:P_BF
IBF19, IBF15
DIMENSION:P_BUDDIM
NO_BUDDIM
DIMENSION:P_BUSTYPE
NO_PRODUCT
DIMENSION:P_BUDDIM2
NO_BUDDIM2
DIMENSION:TIME
PRIOR, %YEAR%.JAN, %YEAR%.FEB, %YEAR%.MAR, %YEAR%.APR, %YEAR%.MAY, %YEAR%.JUN, %YEAR%.JUL, %YEAR%.AUG, %YEAR%.SEP, %YEAR%.OCT, %YEAR%.NOV, %YEAR%.DEC
DIMENSION:P_ACCT
<ALL>
Loading TIME.TIMEID
Time to load properties:0.0 sec.
----------------------------------------------------------------------------------------------------
select P_ACCT,P_BF,TIMEID,SIGNEDDATA
into #tblTempLogic_266166
from tblFactBUDGETS
WHERE  CATEGORY=N'BU' AND  INPUTCURRENCY=N'RUR' AND  P_BF in (N'IBF19',N'IBF15') AND  P_BUDDIM=N'NO_BUDDIM' AND  P_BUDDIM2=N'NO_BUDDIM2' AND  P_BUSTYPE=N'NO_PRODUCT' AND  P_CC=N'3_KIR_FF_FA' AND  P_CITY=N'KIR' AND  P_DATASRC=N'CCDATA' AND  P_PROJECT=N'PR_PL' AND  TIMEID in (N'20120400',N'20120800',N'20121200',N'10000425',N'20120200',N'20120100',N'20120700',N'20120600',N'20120300',N'20120500',N'20121100',N'20121000',N'20120900')
insert into #tblTempLogic_266166 (P_ACCT,P_BF,TIMEID,SIGNEDDATA)
select P_ACCT,P_BF,TIMEID,SIGNEDDATA
from tblFactWBBUDGETS
WHERE  CATEGORY=N'BU' AND  INPUTCURRENCY=N'RUR' AND  P_BF in (N'IBF19',N'IBF15') AND  P_BUDDIM=N'NO_BUDDIM' AND  P_BUDDIM2=N'NO_BUDDIM2' AND  P_BUSTYPE=N'NO_PRODUCT' AND  P_CC=N'3_KIR_FF_FA' AND  P_CITY=N'KIR' AND  P_DATASRC=N'CCDATA' AND  P_PROJECT=N'PR_PL' AND  TIMEID in (N'20120400',N'20120800',N'20121200',N'10000425',N'20120200',N'20120100',N'20120700',N'20120600',N'20120300',N'20120500',N'20121100',N'20121000',N'20120900')
and SOURCE = 0
insert into #tblTempLogic_266166 (P_ACCT,P_BF,TIMEID,SIGNEDDATA)
select P_ACCT,P_BF,TIMEID,SIGNEDDATA
from tblFAC2BUDGETS
WHERE  CATEGORY=N'BU' AND  INPUTCURRENCY=N'RUR' AND  P_BF in (N'IBF19',N'IBF15') AND  P_BUDDIM=N'NO_BUDDIM' AND  P_BUDDIM2=N'NO_BUDDIM2' AND  P_BUSTYPE=N'NO_PRODUCT' AND  P_CC=N'3_KIR_FF_FA' AND  P_CITY=N'KIR' AND  P_DATASRC=N'CCDATA' AND  P_PROJECT=N'PR_PL' AND  TIMEID in (N'20120400',N'20120800',N'20121200',N'10000425',N'20120200',N'20120100',N'20120700',N'20120600',N'20120300',N'20120500',N'20121100',N'20121000',N'20120900')
select tmpTable.P_ACCT,tmpTable.P_BF,tmpTable.TIMEID,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_266166 as tmpTable
group by tmpTable.P_ACCT,tmpTable.P_BF,tmpTable.TIMEID
drop table #tblTempLogic_266166

I saw that TIMEID 10000425 is incorrect -its 2012.DEC.WEEK3 from second hierarchy

Thanks,

Bets regards

Iaroslav

[JohnL]

Hi,

What do you have for the PRIOR property of the 2012.JAN time dimension member?

Thanks,

John

[former_member622327]

Hi,

We haven't PRIOR property in TIME dimension.

Best regards

Iaroslav

[JohnL]

Hi Iaroslav,

You need to create/update/maintain the following properties in your Time dimension:

PRIOR,CURRENT,NEXT

i.e. for 2012.JAN the values would be:

2011.DEC,2012.JAN,2012.FEB

Don't forget to update the dimension properties with these properties as well.

After you do that re-run your test and review your logic log files to see if it is working.

Thanks,

John