When do you need a Lookup table?
Generally, you will need a Lookup table when you are working with calendars that are different from the Standard Calendar, and you want to use this non-standard calendar together with dynamic criteria.
Notice: For a simple fiscal calendar – where months have been shifted simply by a fixed number of months – the Lookup table is not necessary. E.g., a fiscal calendar starting April 1st (fiscal month 1) and ending March 31st (fiscal month 12), does not need a Lookup table. In this case, just use the more simple method explained here https://community.targit.com/hc/en-us/articles/360017289898-Dynamic-Time-with-Fiscal-Calendar, and here https://community.targit.com/hc/en-us/articles/360017188997-Working-with-Offset-in-Period-Table-Task.
Examples of non-standard calendars that require a Lookup table to work correctly with dynamic criteria:
- 4-4-5 calendars. The 4-4-5 year is made up from 12 months in four quarters. Each quarter contains two months with 4 full weeks (28 days) and one month with 5 full weeks (35 days).
- “Season” calendars. A season calendar may be relevant for companies working with “Spring collections”, “Easter sale”, “Summer collections” etc. The collection periods may be different from one year to another.
- Non-standard weeks. Maybe your are working in a company where it makes more sense to have weeks starting on Thursdays rather than on Sundays or Mondays.
Requirements - Auxiliary database
Before you start working with a lookup table, you must make sure that you have a TARGIT Auxiliary database running. The TARGIT Auxiliary database is often used for logging purposes but may also be used for Lookup tables.
The Auxiliary database is made as a simple relational database, e.g. on a SQL Server. In this article, my Auxiliary database is called TargitAUXdb, but it could be any name.
Once the database is created, you should go to the TARGIT Management client to set up a connection to it.
The Lookup table
The purpose of the Lookup table is to look up alternative values to a date's sub-elements (e.g. Year, Month, Day).
- The date “2021-05-27” would normally be associated with a month key value of '5', but in your lookup calendar this value should be replaced with '4'.
- Likewise, in a standard calendar, the date “2021-05-27” is part of week #22, but in your customized calendar it should look up week #17.
Required fields:
- The Lookup table must contain a field with dates to look up. The type must be DateTime and the name must be THEDATE.
Optional fields. At least one must be present.
- YEAR
- HALFYEAR
- QUARTER
- MONTH
- WEEK
- DAY
These fields will hold the values that should replace the sub-element values from THEDATE.
Example: A 4-4-5 Lookup table
A custom 4-4-5 calendar is something that will require a Lookup table. The 4-4-5 year is made up from four quarters with 3 months each. Each month is either 4 weeks (28 days) long or 5 weeks (35 days long).
An example on a 2021 4-4-5 calendar, all weeks starting on Sundays:
From the table, notice things like these:
- February is month #1 … January (next year) is month #12
- Week #1 is the first week (Sunday to Saturday) of February
- Each month is exactly 4 or 5 weeks long
- Dates at the beginning and at the end of a month may belong to a different “standard” month - e.g., 31st January 2021 is part of February in this 445 calendar
- Quarter 1 is February, March and April, Quarter 2 is May, June and July etc.
When translated into a SQL Server based Lookup table, it may look like this:
In this example, the columns Year, Quarter, Month and Week are the actual lookup columns.
E.g., when looking up TheDate = 2021-01-27, the sub-elements will be replaced like this:
- Year = 2020
- Quarter = 4
- Month = 12
- Week = 52
In this example, the other columns – MonthName, DayString, DayName – are not used for the actual lookup functionality but are included here for convenience, as they will be used for building a 445 Calendar dimension.
The 445 Calendar dimension
In this example (from a SSAS project), based upon the Lookup table, I have created a 445 Calendar dimension with two hierarchies:
- YQMDate – e.g., 2020 4 12 2021-01-27
- YQMDay – e.g., 2020 4 12 27
Notice: The Month attribute is typically set up with KeyColumn = MonthNumbers and NameColumn = MonthNames.
Dynamic syntax, Decorations
In the Management client, in Decorations, we can add Dynamic date definition to the 445 Calendar hierarchies.
For the YQMDay hierarchy, Dynamic dates definition:
Offset and table lookup:
Level type overrides:
Dynamic Syntax, Description field (SSAS)
If you are not using Decorations for the Dynamic dates definition, but rather the Hierarchy's Description property in the SSAS project, you should instead use this syntax:
- #|TimeDef:&[<>].&[<>].&[<>] .&[<DD>],TableLookup:445Lookup|#
Dynamic dates from Lookup in the Client
Below, I am using my 445 YQMDay Hierarchy (with dynamic options) in the criteria bar. The left most crosstab is using the YQMDate Hierarchy (to show dates on lowest level). The right most crosstab may not be necessary, but it is using a Standard YQMD Hierarchy and I included it to show the difference from the 445 calendar.
Here we are looking at a "This month" dynamic criteria:
Notice: Using “Month to date” or “Year to date” directly from the 445 Calendar YQMDay Hierarchy will in many cases cause problems due to the nature of the 445 Calendar.
To the resolve this issue, you can bring the Standard Calendar Hierarchy from the criteria bar into play as well.
Below, I have used the Standard Calendar Hierarchy to produce a “<= Today” dynamic criteria. Together with the “This month” criteria from the 445 dimension, it will actually produce a “Month to date” criteria.
Comments
Thank you very much for this article!
Peronally, I prefer the 4-5-4 concept as it is smoother for the 53 Week year correction which happens every five to six years.
As well, I prefer to name the "months" of the 4-5-4 calendar using different names to show clearly, it's not the regular calendar month.
I assume this will work as well using the lookup concept (except for the 53-week-year which cannot be compared)?
Hi Jimmy,
These 4-4-5 or 4-5-4 periods are quite a challenge. In fact, just working with standard weeks dynamically can be frustrating, especially around week53/week1.
Yet, this article should be able to bring you successfully through a 4-5-4 setup as well. And yes, you will need a Lookup table.
BR / Ole
Hi Ole,
I've set identical lookup tables up for a couple of clients with tabular "cubes":
TheDate - datetime
Year - varchar(7)
Quarter - varchar(2)
Month - varchar(3)
Day - date
The date/period tables are identical and the decorations are identical:
[<>].[<>].[<>].[<>]
Table: DateLookup
Levels: Year, Quarters, Months, Days
At one of the client sites, dynamic time only works down to the month level and not at the date level ... at the other sites, everything works nicely.
If I change the lowest level from date (dd/mm/yyyy) to day of month (d) at the "problem" client site, it works nicely ... I just prefer the full date at the lowest level rather than day of month.
What could cause the lowest level not to work when it's a date or datetime datatype?
(I've tried both and none works at the "problem" client site)
Thanks,
Søren
Hi Søren,
I am sorry, but I don't know why it is working at some sites and not at others.
If you haven't done it already, I think this is a case for TARGIT Support: https://www.targit.com/support
BR / Ole
Please sign in to leave a comment.