Storing Fiscal Year in a table

Hi,

We are modelling tax bills and other related data. A question has come up on the topic of how to store the Fiscal Year for the data.

One option is to store the SK from our date tables (the SK representing day 1 of the fiscal year), another is just to store the year (e.g. 2012).

The tables will be access via a BI tool (no expectation that an end user will create a query, so SQL complexity for the user is not a concern).

Any opinions/guidance on these options or other options that should be considered?

Thanks

What is the business rule to determine the fiscal period that something belongs? Is it billing date, due date, or some other date?

Either way, fiscal period should be an attribute of the date dimension and you use whichever date role in the fact data that complies with the rule. You usually do not have fiscal period as its own dimension.

The fiscal year starts on 7/1, so the SK would be 20120701 for all rows in FY 2012.

Thanks for the input.

Why wouldn't you have a field in the date dimension called Fiscal Year? For dates from 7/1/2012 to 6/30/2013, the Fiscal Year would be 2012. For dates from 7/1/2011 to 6/30/2011, the fiscal year would be 2011. You could also have Fiscal Year Quarters.