en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Jeremy Kimmons  
#1 Posted : Friday, June 7, 2013 11:37:41 AM(UTC)
Jeremy Kimmons
Rank: Advanced Member

Groups: extranet\Forum
Joined: 2/28/2013(UTC)
Posts: 10

Hi all - I am attempting to configure and implement dynamic time functions in Targit 2k11 build 5418.

Here's where I have got to - I have created in my cube a dimension of type Time, and within that a Fiscal Hierarchy.
This has 5 levels:

Fiscal Year (set as type FiscalYears)
Fiscal Year and Quarter (set as type FiscalQuarterOfYear)
Fiscal Year and Month (set as type FiscalMonthOfYear)
Fiscal Year and Week (set as type FiscalWeekOfYear)
Full Date (set as type Date)

Here's an example of the structure of each level:

2013
Qtr 1 2013
P01 - APR 2013
Week 1 2013
2013-03-31 00:00:00.000


So, my primary question is - what should the syntax of the TimeDef be?

I've got as far as <YYYY>.<"Qtr "Q YYYY> for the first two elements (although confirmation that those are correct would be handy), but after that i'm stumped!

Note that these fiscal elements are being fed from a database table with the correct fiscal values in it, so I think i'm right in saying that there doesn't need to be any offsetting done, as there is in the example given for a fiscal calendar.


Many thanks in advance for any help, this is driving me nuts!

Cheers

Jeremy
Peter  
#2 Posted : Friday, June 7, 2013 1:53:27 PM(UTC)
Peter
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/1/2011(UTC)
Posts: 43

Hi Robert

Take a look in the "Installation and configuration guide" there are some good examples there (You can find it in the download center on the TARGIT portal as a part of the download for your version of TARGIT).

Right off the bat I can tell you that you have to change the types, even though they're "fiscal" to you the only types supported by TARGIT is:

Years
Quarters
Months
Weeks
Days

Expecially note that it's days and NOT date ;-)

I would recommend you using keys instead of addressing the "Name" column of the attributes.

An example of keys I would see relevant below:
2013 : 2013
20131 : Qtr 1 2013
201301 : P01 - APR 2013, 201302 : P02 - MAY 2013
201301 : Week 1 2013, 201352 : Week 52 2013
20130331 : 2013-03-31 00:00:00.000

You can use the SQL datepart or similar functions to extract information for each of the levels.
One of my classical is the period (yearmm) that I lots of times create like this: year(datecolumn)*100+periodcolumn 2013*100 = 201300 + 12 (Period 12) = 201312.

An example for a timedef for a hierarchy with Year, Quarter, Month, Day could then be: #|TimeDef:&[YYYY].&[YYYYQ].&[YYYYMM].&[YYYYMMDD]#|

To sum it up; try reading the installation guide, follow my tips and if you don't get it up and running then don't get frustrated. Contact support and set up a session with a consultant. It's usually very well worth it and ensures you get a great start on building your own cube.

Best Regards,

TARGIT

Peter Orum
Product Manager
Jeremy Kimmons  
#3 Posted : Friday, June 7, 2013 5:49:13 PM(UTC)
Jeremy Kimmons
Rank: Advanced Member

Groups: extranet\Forum
Joined: 2/28/2013(UTC)
Posts: 10

Hi Peter - thanks for the info, the guide re: not using Fiscal types is certainly something I wouldn't have realised otherwise.

I've added key fields as per your suggestions to the underlying database that Time is built from, and added those to the attributes, and changed the attribute types. I've rebuilt my Year/Quarter/Month/Week/Day hierarchy with them, and set the TimeDef on the hierarchy as:

#|TimeDef:&[YYYY].&[YYYYQ].&[YYYYMM].&[YYYYWW].&[YYYYMMDD]|#

However, i'm still not getting dynamic functionality once this has built.

A couple of other quick questions:

Which attribute should be the key attribute for the dimension?
My attribute relationships go date->year/week->year/month->year/quarter->year - is that correct?
Is there fundamentally an option somewhere in the targit server that needs to be set to enabled dynamics?


Many thanks for the help.


Edited by user Friday, June 7, 2013 5:58:14 PM(UTC)  | Reason: Not specified

Jeremy Kimmons  
#4 Posted : Friday, June 7, 2013 6:25:55 PM(UTC)
Jeremy Kimmons
Rank: Advanced Member

Groups: extranet\Forum
Joined: 2/28/2013(UTC)
Posts: 10

Peter - ignore that previous post, i'd done something dumb - it's now working!


Many thanks for the help!

Peter  
#5 Posted : Friday, June 7, 2013 6:37:02 PM(UTC)
Peter
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/1/2011(UTC)
Posts: 43

Glad that I could help you out ;-)

Have a great weekend.

Peter
Peter  
#6 Posted : Sunday, June 9, 2013 4:08:17 PM(UTC)
Peter
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/1/2011(UTC)
Posts: 43

I suddenly noticed that there are things missing from our post, the greater than and less than sign before the level-details. This is how it should be.

#|TimeDef:&[<YYYY>].&[<YYYYQ>].&[<YYYYMM>].&[<YYYYWW>].&[<YYYYMMDD>]|#

However you will get yourself in trouble with having a hierarchy like

Year
Quarter
Month
Week
Day

Since months and weeks don't go together by default; a week can span several months, e.g. Week 1 can e.g. be Dec, 31st, Jan 1st, ..., Jan 6th.

You can resolve this if you use a lookup table (see the documentation ;-))

My recommendation for your scenario is to create two hierarchies: Year, Quarter, Month, Day and another with Year, Week, Day.

Peter
Mike Baker  
#7 Posted : Monday, December 5, 2016 5:11:15 PM(UTC)
Mike Baker
Rank: Newbie

Groups: extranet\Forum
Joined: 8/4/2015(UTC)
Posts: 1

Originally Posted by: Peter Go to Quoted Post
I suddenly noticed that there are things missing from our post, the greater than and less than sign before the level-details. This is how it should be.

#|TimeDef:&[<YYYY>].&[<YYYYQ>].&[<YYYYMM>].&[<YYYYWW>].&[<YYYYMMDD>]|#...



Peter,

I hope all is well. I believe you gave me my TCP training a few years back.

However I am trying to get my dynamic time functionality rebuilt.

I am using a Year-Month_Day hierarchy and the backend development is complete.

I am trying to set the decorations/definitions and can get year to work but Month and Day say "Not Found".

Our Values echo... Year=FY 2016, Month=January 2016, Day=January 1, 2016.

Year is setup as [FY <YYYY>] and works. Everything from there does not.

I have tried .[<M YYYY>], [<MYYYY>], [<MM YYYY>], .&[<M>], [<>], .&[<>].... etc

I haven't worried about the day because figured needed month to work.

If you get this please let me know if you have any ideas as to what I should try.

Thanks,

Mike


Ole  
#8 Posted : Friday, December 9, 2016 2:42:29 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

Hi Mike,

It may be necessary to know exactly how your month level is defined on the Data Warehouse - not just how it is displayed.

However, now I am simply guessing that your month level contains a combined key with Month number and Year number. If that is the case, then you should try something like this for the month level dynamic definition: &[<M]&[YYYY>]

Best regards,
Ole
Ole Dyring
JAIME DE CARLOS  
#9 Posted : Thursday, May 9, 2019 8:27:00 AM(UTC)
JAIME DE CARLOS
Rank: Member

Groups: extranet\Forum
Joined: 3/29/2011(UTC)
Posts: 5


Hi Ole,

I am using a Year-Month hierarchy and the backend development is complete.

I am trying to set the decorations/definitions and can get year to work but Month says "Not Found".

Our Values echo... Year=Calendar 2016, Month=January 2016.

Year is setup as [<YYYY>] and works but everything from there does not.

I have tried:

  • [<YYYY>].[<M YYYY>]

    [<YYYY>].[<MMYYYY>]

    [<YYYY>].[<MM YYYY>]

    [<YYYY>].<January|February|March|April|May|June|July|August|September|October|November|December>

    [<YYYY>].[<January YYYY|February YYYY|March YYYY|April YYYY|May YYYY|June YYYY|July YYYY|August YYYY|September YYYY|October YYYY|November YYYY|December YYYY>]

    [<YYYY>].&[<January]&[ YYYY]&[|February]&[ YYYY]&[|March]&[ YYYY]&[|April]&[ YYYY]&[|May]&[ YYYY]&[|June]&[ YYYY]&[|July]&[ YYYY]&[|August]&[ YYYY]&[|September]&[ YYYY]&[|October]&[ YYYY]&[|November]&[ YYYY]&[|December]&[ YYYY>]

    etc


In a similar post like mine (posted by Mike Baker two years ago) you asked him for month level definition. Attach images you can help:

[img=https://1drv.ms/u/s!AjajulosIFeo7iT9WTbqXAGfsVRM]DateTable[/img]

[img=https://1drv.ms/u/s!AjajulosIFeo7iXDQRD54iyaNLIS]06A-DateMonth Hierarchy[/img]

[img=https://1drv.ms/u/s!AjajulosIFeo7iMsCIH9nkA_wk7f]06A1-Year Attribute[/img]

[img=https://1drv.ms/u/s!AjajulosIFeo7iIWHsmDQED7JrVc]06A2-Month Attribute[/img]

[img=https://1drv.ms/u/s!AjajulosIFeo7iXDQRD54iyaNLIS]06A-DateMonth Hierarchy in Cube[/img]

[img=https://1drv.ms/u/s!AjajulosIFeo7ilCcXZRUayp8dtP]Decorations Dynamic Dates Definition[/img]

[img=https://1drv.ms/u/s!AjajulosIFeo7ihmZpLMmQd3wbTQ]Decorations Level Type Overrides Tab[/img]


Could you please indicate me how to set up the definition in Dynamic Dates Definition tab in Decorations?

Any further information you need, please let me know.

Many thanks in advanced


Ole  
#10 Posted : Thursday, May 9, 2019 8:59:45 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

Hi Jaime,

I think you will need to look into how you set up your Month attribute in the backend. Usually it will be set up with differing KeyColumn and NameColumn properties.

From what you write, I think you may have a combined key (YearMonth or MonthYear or even a full date) for your Month attribute KeyColumn property. You will need to check what you have.

E.g., assuming it is a YearMonth combined key, your syntax might then be [<YYYY>].&[<YYYYMM>].

BR / Ole
Ole Dyring
Ole  
#11 Posted : Thursday, May 9, 2019 9:23:08 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

Fyi - I cannot see any of your attached images...
Ole Dyring
JAIME DE CARLOS  
#12 Posted : Thursday, May 9, 2019 10:26:11 AM(UTC)
JAIME DE CARLOS
Rank: Member

Groups: extranet\Forum
Joined: 3/29/2011(UTC)
Posts: 5


Hi Ole,

I am using a Year-Month hierarchy and the backend development is complete.

I am trying to set the decorations/definitions and can get year to work but Month says "Not Found".

Our Values echo... Year=Calendar 2016, Month=January 2016.

Year is setup as [<YYYY>] and works but everything from there does not.

I have tried:

[<Calendar YYYY>].[<M YYYY>]

[<Calendar YYYY>].[<MMYYYY>]

[<Calendar YYYY>].[<MM YYYY>]

[<Calendar YYYY>].<January|February|March|April|May|June|July|August|September|October|November|December>

[<Calendar YYYY>].[<January YYYY|February YYYY|March YYYY|April YYYY|May YYYY|June YYYY|July YYYY|August YYYY|September YYYY|October YYYY|November YYYY|December YYYY>]

[<Calendar YYYY>].&[<January]&[ YYYY]&[|February]&[ YYYY]&[|March]&[ YYYY]&[|April]&[ YYYY]&[|May]&[ YYYY]&[|June]&[ YYYY]&[|July]&[ YYYY]&[|August]&[ YYYY]&[|September]&[ YYYY]&[|October]&[ YYYY]&[|November]&[ YYYY]&[|December]&[ YYYY>]

etc



In a similar post like mine (posted by Mike Baker two years ago) you asked him for month level definition more information about hierarchy and attributes set up. For that, I attach next images:

UserPostedImage

UserPostedImage

UserPostedImage

UserPostedImage

UserPostedImage

UserPostedImage

UserPostedImage


Could you please let me know how to set up the definition in Decorations Dynamic dates definition tab?

Any further information you need, let me know.

Please, remove unuseful posts I have sent.

Many thanks for your help.

Edited by user Thursday, May 9, 2019 10:32:43 AM(UTC)  | Reason: delete url posting web site

Ole  
#13 Posted : Thursday, May 9, 2019 10:45:03 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

Ok. Got images now. Much better. :-)

From what I see, I think your Decorations syntax should be: &[<YYYY>].&[<YYYYMM>]

Also, please remove the syntax you added to your hierarchy Description property. Not sure, but it may potentially cause confusion to have the syntax in both places. Initially, at least, I would like to see it work with the Decorations syntax alone.

BR / Ole
Ole Dyring
JAIME DE CARLOS  
#14 Posted : Thursday, May 9, 2019 11:16:01 AM(UTC)
JAIME DE CARLOS
Rank: Member

Groups: extranet\Forum
Joined: 3/29/2011(UTC)
Posts: 5


Ole,

I have tried your recomendations:

1. I have removed the syntax I had added to my hierarchy Description property

UserPostedImage

2. I have typed in my Decorations syntax: &[<YYYY>].&[<YYYYMM>]

After that, I have processed 06A dimension and cube in full mode. Then, I have opened cube throught the Targit web site and obtained the mistake "Month Not Found":

UserPostedImage


May be, it could help how hierarchy 06A-DateMonth appears in cube (Year=Calendar 2019, Month=January 2019...December 2019):

UserPostedImage



Many thanks

Ole  
#15 Posted : Thursday, May 9, 2019 11:27:00 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

The "Month not fond" issue can be caused by not having any time dimension members for the current month: May 2019.

Try shifting the date with the "Dynamic date origin / Datos originales dinámicos" control in the dialog. Shift it to e.g. 09/02/2019. From your screenshots above, this date should exist in your data set.
Ole Dyring
JAIME DE CARLOS  
#16 Posted : Thursday, May 9, 2019 1:41:10 PM(UTC)
JAIME DE CARLOS
Rank: Member

Groups: extranet\Forum
Joined: 3/29/2011(UTC)
Posts: 5


Ole,

there are time dimension members from 2002 to 2025...

UserPostedImage


I have tried to shift the date with the "Dynamic date origin / Datos originales dinámicos" control in the dialog:

UserPostedImage


Then, I have obtained "Month Not Found" in cube:

UserPostedImage


Many thanks

Ole  
#17 Posted : Wednesday, May 15, 2019 7:29:11 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

I am sorry Jaime. I am running out of ideas.
You may want to contact the official TARGIT support: https://community.targit.com/en/support
Refer to this forum post, if you do so.

BR / Ole
Ole Dyring
JAIME DE CARLOS  
#18 Posted : Thursday, May 16, 2019 10:56:47 AM(UTC)
JAIME DE CARLOS
Rank: Member

Groups: extranet\Forum
Joined: 3/29/2011(UTC)
Posts: 5

Originally Posted by: Ole Go to Quoted Post
I am sorry Jaime. I am running out of ideas.
You may want to contact the official TARGIT support: https://community.targit.com/en/support
Refer to this forum post, if you do so.

BR / Ole


Ole,

many many thanks for your support. I am going to contact to official Targit support and , if they find the solution, I will post here the answer.

Best regards

Ole  
#19 Posted : Thursday, May 16, 2019 11:57:05 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 496

Jaime,

One more thing you might try: Go to your TARGIT Management client, Setup, and temporarily enable all Server Trace options.

Do a new login to your TARGIT client and try to apply a simple (non-dynamic) criteria, e.g. 2018 May, from your YearMonth dimension.

In Windows' Event Viewer (Windows Log / Application) you should now be able to see exactly how months are requested on your data warehouse. E.g. something like this:

<member>
<key>[Period].[YMD].[Year].&amp;[2018].&amp;[201805]</key>
<value>'2018','May'</value>
</member>

This might be helpful in understanding how to set up dynamic dates correctly.

Remember to disable Server Trace afterwards.

BR / Ole
Ole Dyring
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error

We use cookies to improve your site experience, but they also provide us with information on your use of our website.
To find out more about the cookies we use and how to delete them, see our Privacy Policy. By continuing to browse the site, you are consenting to our use of cookies.

ok