A common requirement of financial reports is to have a financial year dimension for analysing the fiscal year data. This is complicated by the fact that financial years differs from country to country.

Creating A Fiscal Year Using SSAS Dimension Wizard

The financial year can be implemented easily using the SSAS dimension wizard. Let us see step by step how to create the financial year:

1. Go to Start->All Programs->  Microsoft SQL Server-> SQL Server  Data Tools

2. Click new project, select business intelligence template and name the project as the Financial Year.

3. Add the Data Source to the project by right clicking on the data source and providing the server name and the initial catalog name. I have chosen the adventure works database as my data source.

4. Add following data tables in the data source view.

5. Add cube to the project by right clicking on the cube. The wizard will guide you to add the measure group table and the dimensions. The summary of the cube can be seen as follows:

 

6. To add the financial year dimension to the cube we shall the use the add dimension wizard.
7. Right click on the dimensions and click new dimension.

8. Click Next. Select the date template and select the Generate timetable in the data source option. Click Next.

9. Specify the from and to year for which you wish to generate the financial year.

Select the time periods which you wish to see in the dimension. You can always customize the financial year attributes later on the basis of the date attribute of the dimension.

10. Select the fiscal year check box and specify the start day and month of the fiscal year. As this article is for Indian fiscal year the starting daya and month of the fiscal year will be  April.

11. The wizard will now complete and click on the generate schema now check box. This will generate the physical table in the data source defined earlier.Click finish to generate the table.

12. Click on the use existing data source view and click next.

13. Click Next.

14. This is the customization dialog box wherein you may perform some customization as what should be the separator and prefix for primary and foreign keys so on and so forth.

15. Click next and then finish.

16. After clicking the  schema is generated and the time table is populated automatically.

17. Once the table is created in the data source we can see the following dimension being created in our solution.

18. Add the newly created dimension to our cube and connect it on the basis of the PK_Date.

19. Explore the data by clicking on the explore data on the Financial Year Table in the DSV

src=”http://c3154802.r2.cf0.rackcdn.com/article_images/fin_year_files/image018.jpg”>

20. We can see the data has been populated in the table for the respective attributes

If you like to customize the Year, Half Year, Quarter and Month to suit your requirement you may do so to look something like this:

Financial Year (FY)	Year	Half Year	Quarter	Month
2004-2005	2005	Semester1	Quarter1	April

To achieve the above output you can add the calculated columns on the Table in the DSV and hence remove the unnecessary columns in the table by keeping only the require columns.

Now let us deploy and process the FinancialYear cube and see the result our self in the excel.

Connect to the FinancialYear cube using the Data tab in the excel as shown below:

Play around with the Financial Year dimension we just created.

]]>