Creating A Financial Year Dimension Using SSAS

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

  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
    wizard.
  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
    Next.

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

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

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

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




Array

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 |