zTD_Month_Dim_Gen - DescriptionThis should be run in Perspectives. It uses a combination of Excel, VBA, and TI.
This generates a series of CSV files which can then be read by the provided
Turbo Integrator Processes to produce a month level time dimension.
The start and end calendar years can be selected.
It will always generate the dimension to include the given range of calendar years.
You can optionally select the generation of various financial years, eg the
financial year starting in month 4 and the financial year starting in month 7.
In this case it obviously cannot generate a full financial year for the first calendar
year, so you should expand the range of calendar years to one before and one
after the full set of financial years that you require.
The calendar months will be given aliases for the appropriate financial year, eg
for the calendar period E_CL_2018-M04 the financial year starting in month 4, will
have an F4 Alias of E_F4_2018/19-P01.
Named Hierarchies (only visible in PAW or PAX, not Perspectives) will be generated
for various consolidations on the months.
For consolidations such as YTD (Year to Date), different Named Hierarchies will be
generated for the calendar year, and also for each of the selected financial years.
For consolidations such as MAT (Moving Annual Total), consolidations will only
be generated for the calendar years, since the sum of 12 months up to and
including any calendar month will be no different for a financial period.
The range of time consolidation types generated includes:
Time consolidation typeYear, Half-Year, Quarter, Month
Year, Month
Year to Date
Rest of Year from Date
Year to Date Average
Rest of Year from Date Average
Run Rate YTD Ave * 12
Check for Date
Cumulative to Date incl Starting Bal
Opening to Date (CTD of month before)
Moving Annual Total
Moving Annual Average (MAT/12)
Difference to period before
As well as generating separate Named Hierarchies for each
combination of Calendar / Financial Year with each time consolidation type,
you can opt to also have these appear as alternate hierarchies in the
default hierarchy, also known as the classic or conventional TM1 dimension.
Relative PeriodsIt generates attributes giving relative periods:
Prev Per (Previous Period)
Prev Year (Same Period in Previous Year)
Next Per (Next Period)
Next Year (Same Period in the Next Year)
In addtition, each set of consolidations is generated in a logical order so that
you can use eg DIMIX -3 to go back 3 periods.
Calendar Month Name AliasThe CalMthName Alias can be used to show eg E_CL_2008-M01 as
E_CL_2008-Jan.
If the system is not being used by English speakers you can change the
month name abbreviations but it is recommended that you keep to 3 letters
so that the month name abbreviation is the same length as eg M01 or P01.
SubsetsIt also generates a number of appropriate subsets
Subsets starting with e_ use the CalMthName Alias
Subsets starting with eg F4_ use the financial period alias for the financial year
starting in the given month.
Subsets starting with z_ do not have an alias.
There are subsets for
base level elements
consolidated level elements
the full hierarchy
the default top level consolidation
Try itThe above attempts to describe what the generated dimension offers.
However, the best way to view it is probably to generate a trial dimension
on a non-production server at your site and to try it out.
It is not really possible in this documentation to show the numerous
sub-consolidations.
Download from GitHubDesign PhilosophyWe think it still holds true, as a general rule of thumb, that calculating something in rules is about 100 times slower than calculating it by consolidation.
We have therefore aimed to put many of the common time calculations into consolidations.
This dimension combines year and month into a single dimension, rather than having a separate year dimension and month dimension.
We have often worked in organisations that require more than one time dimension with different meanings, eg Effective Month, Posting Month, Inception Month,
Accident Month, Shipping Month, Booking Month, etc
With multiple time dimensions using a separate year dimension and month dimension for each would mean that most of the dimensions in the cube were solely related to handling time.
We have often worked in organisations where a merger has meant the need to handle parts of the organisation having different financial years or a change of financial year. This is much easier to handle in a combined year and month dimension.
This approach has been in use for many years. With the advent of Named Hierarchies, the generator was modified to make it handle Named Hierarchies.
With Named Hierarchies it is possible to use one Named Hierarchy on Rows and
another Named Hierarchy from the same dimension on Columns. You can therefore
achieve the Year and Month separation without the need to have physically
separate dimensions.
The reason behind the CalMthName Alias is that when dealing with financial years,
in our experience people often still find it easier to see 2021-Jul rather
than 2020/21-P04, particularly as not all users will be finance users.
EnhancementsIf you develop additional time consolidation types that are not included here,
we would ask that you contact us to make them available to others.
If you find an error, then please make us aware of this so that it can be
corrected and made available to others.
If you need additional time consolidation types to be developed but don't have
the experience to do this, then please contact us, as we may be able to help.
info@successcubed.co.ukExample of all Named Hierarchies combined as alternate hierarchies into a Classic Dimension Hierarchy
------------------------------
Paul Simon
Success Cubed Ltd
www.successcubed.co.uk------------------------------
#PlanningAnalyticswithWatson