creating fiscal year in a time dimension in frameowork manager

[nfldbunker]

hi

just wiondering if anyone can let me know how do i create a fiscal year in a time dimension in frameowkr manager.

ANy help would be greatly appreciated.

bunker

[MFGF]

Hi,

Can you be a bit more specific about what you are trying to achieve?  When you say Fiscal Year, what are the specific start/end dates for each year?

One approach to creating a Time Dimension is to use calculations based on a populated date item to extract year, quarter, month etc, but I'd need to know specifics to suggest what syntax will be best.

Regards,

MF.

[nfldbunker]

hi

in our time  dimension i want a field called fiscal year which  be from april 1st /year to march 31/year. when the time diemsion for fiscal year is pul;led into a report it will run from the time period suggested

thanks

bunker

[bdbits]

We have a table for a conformed time dimension. We have a stored proc to do all the calculations, so you can create this conformed table wherever you need it. Then you just drop in a standard dimension with appropriate hierarchies.

There is a single row for each day in the range we have decided we need. I think we went from 1990 to 2020 but it is arbitrary and additional rows can be added whenever you like/need. We then have a whole bunch of calculated attributes, like which governmental fiscal year, month name, quarter, etc. At the 'bottom' is a surrogate key to link the date to other tables.

In our case, we generally use alternate drill paths for each of the fiscal years, so the user can drill from All Time->Biennium->State Fiscal Year->Month->Day, or All Time->Fed Fiscal Year->Month->Day. Works well for us.

[TimNelson24]

What I would recommend is: 

Create your DDL(table) in your database with with field called DATE as the Primary Key and manually insert your companies Fiscal Calendar or create script to automate.  Then architect this table in as you would any other dimension.

CREATE TABLE FISCAL_CALENDAR
(
DATE 
WEEK
FISCAL_PERIOD
FISCAL_MONTH
FISCAL_YEAR
) UNIQUE PRIMARY INDEX (DATE)

For every unique Date you have the corresponding fiscal information. By materializing this information, you're going to allow the Database to take advantage of the statistics in this table to generate an optimal explain plan. I work for a retail company with Terabytes of Transactions.  Creating Calculations in FW for fields that are commonly filtered on tends to lead to poor performance

Tim Nelson
Lead BI Architect
Office Depot