Dynamic Time with Fiscal Calendar

You may want to build a Time Dimension that allows the End Users to view the transactions through time from a different perspective. Many companies work with Fiscal calendars where the start and end month of the fiscal year is not necessarily January and December, respectively.

There are many different Financial (Fiscal) years, but one version could July-June as the 12-month period that in a financial context is referred to as a Year.

Whenever you want to compare This Year to Last Year, or YTD to LYTD in the TARGIT client, it would be nice to have a Fiscal Time Dimension that “knows” about the way the company/organization wish to analyze data through time.

The Fiscal Period Table

Your Fiscal Period table may look something like this:

Notice that July is Fiscal month number 1, and June is Fiscal month number 12. In other words, your fiscal calendar has been offset by 6 months, starting in July every year.

Other things worth noticing from the table:

• For 6 months, the Fiscal year number is one year "behind" the actual date.
• Month names doesn't change. 'April' is still 'April', also in the Fiscal year. We will use the Fiscal month numbers when working with the data, while the month names are usually used when presenting data to the end-users.
• Fiscal day numbers and Fiscal dates seem more or less the same, but also here, we will be using day numbers when working with data, while the Fiscal dates are used for end-user presentation.

Creating the Fiscal Period Table

You may already have used one or more SQL scripts to create a Fiscal period table similar to the one presented above.

If you should need to create one from scratch, using SSIS, you can benefit from TARGIT's Period Table Task. This is part of the 'TARGIT Custom SSIS Tasks' package, which can be downloaded for free from here: https://portal.targit.com/Download-Support/Download-center/Other-Documents

Once installed, it can be added to your SSIS Workflow:

The date keys are based on one of your existing tables, usually a date field in your central fact table:

On the output tab you define as many columns as you need. All extra columns are derived from the 'TheDate' key column.

Notice how Fiscal years, Fiscal months and Fiscal year names are offset by 6 months in this example:

If necessary, read more about the Period Table task here:

https://community.targit.com/hc/en-us/sections/360005019617-Period-Table-Task

and here:

https://community.targit.com/hc/en-us/articles/360017188997-Working-with-Offset-in-Period-Table-Task

The Fiscal Period Dimension

Based on the Fiscal Period table, you can now create a Fiscal Period dimension.

In this example, I created two almost identical hierarchies with three levels: Year, Month and Days/Dates. In fact, it will work with just the YMDay (filter) hierarchy - the YMDay (display) hierarchy is included only to be able to present data in an alternative way on the day/date level.

Notice that I am using Year Number, Month Number, and Day Number for the three attributes that goes into the hierarchy.

The numbers are important for working correctly with the data - so numbers are my keys for all three attributes.

However, I would like to present things differently to my end-users. Hence, I open properties for each of my attributes and change their KeyColumns, NameColumn and OrderBy properties. In this example, I have chosen that my month number attribute should display month name instead, and yet to be ordered by the month numbers.

Similar property changes are made to the year number attributes as well:

• Key: FiscalYearNumber. Name: FiscalYearName

Setting up Dynamic Dates Definition with Decorations

With the TARGIT Management client, it is now time to go to the Decorations section to set up the Dynamic dates definition for the YMDay hierarchy.

In this example, the correct syntax would be:

&[<YYYY>].&[<M>].&[<D>]

On the offset tab, make sure everything is offset with the correct number of months according to your financial year:

And last, define the type of each level:

Working with Dynamic Fiscal Periods in the TARGIT client

In the TARGIT client, you can now use the new Fiscal Period dimension for dynamic filtering.

In this example, notice that I am using the YMDay (filter) hierarchy - the one with day numbers on the third level - for dynamic filtering from the Criteria bar. In the two crosstabs below you can see the the two alternative hierarchies in use side by side.