Creating A Financial Year Dimension Using SSAS

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

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

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

  1. 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.

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

  1. 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:


  1. To
    add the financial year dimension to the cube we shall the use the add dimension
  2. Right
    click on the dimensions and click new dimension.

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

  1. 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.

  1. 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.

  1. 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.

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

  1. Click

  1. 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.

  1. Click
    next and then finish.

  1. After
    clicking the  schema is generated and the time table is populated

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

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

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


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

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)


Half Year








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.

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

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

around with the Financial Year dimension we just created.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |