Date Filtering in Excel from your Analysis Services Dimension

I recently had to set up another Date Dimension and went through the process of setting up all the properties in the date dimension so that when it is used in Excel, the end users would then be able to use the Date Filters built into Excel.

The Excel Date Filters are very handy when creating Excel documents. As an example if you create your Date Filter to be for the last month. Every time you open your Excel document it will automatically filter to the current month.

So below are the details on how to set this up on your Date Dimension. I have used the Adventure Works DW 2008R2 Database and Analysis Services Project.

1. Open up your Dim Date Dimension
2. Then you need to click on your Date Key and select Properties
3. You MUST now add the following to your Date Key Properties:
   1. Where it says Key Column make sure it has the default which should be:

i.      DimDate.DateKey(Integer)

1. Where it says Name Column click on the Ellipses button and select the following:

i.      DimDate.FullDateAlternateKey  (WChar)

ii.      NOTE: Make sure that it is set to WChar

1. Where it says Value Column click on the Ellipses button and select the following:

i.      DimDate.FullDateAlternateKey (Date)

ii.      NOTE: Make sure that it is set to Date

1. Then if you have a Hierarchy defined you need to create the attribute relationships in order for the Date to filter correctly in Excel.
2. With our example we had a hierarchy with the following:
   1. IT went Calendar Year – Calendar Quarter – English Month Name – Full Date Alternate Key
   2. So in the Attribute relationships screen we created the following relationships with the screenshot below:

Figure 2 – Attribute Relationships Example

1. NOTE: When creating the Attribute Relationships it always goes from Right to left, starting with the Year and ending with the Date.
2. The final step is for each of the attributes used in your Hierarchy you must ensure that they ALL have a Name Column Value defined.
   1. As in our example for Date it had TD_Date.Date (WChar) in the Name column, as well as all the others for English Month Name, Calendar Quarter and Calendar Year.
   2. Next you need to set the Type of your Dimension Attribute to match what your date function is
      1. EG:

i.      If it is Calendar Quarter then you need to set the Type for Quarter to Quarters

1. You do this by doing the following for each of your Dimension attributes in your Hierarchy above

i.      So it would be for Full Date Alternate Key, English Month Name, Calendar Quarter, Calendar Year

ii.      So we are going to start with Full Date Alternate Key as our Example. But you would need to do this for all of the above in bold.

iii.      Click on Full Date Alternate Key in the Attributes pane.

1. Then either right click and select Properties or click on Properties window
2. Under Basic look for Type
3. Click on the Drop Down Next to type
   1. Then click on Date, then the plus Sign next to Date and scroll down until you get to Days

1. Then click on Days.
2. Once done it will then look like the following:

1. You will then need the Type for each of the following below:
   1. English Month Name:
   2. Type:

i.      Months

1. Calendar Quarter
2. Type:

i.      Quarters

1. Calendar Year
2. Type:

i.      Years

1. You then need to put in the following so that you don’t get duplicates when trying to process the dateDimension
   1. NOTE: You do not have to do this for all the levels only specific ones below

i.      English Month Name

1. Go into the Properties for English Month Name and under Key Columns put in the following, with the same order as shown below:

i.      Calendar Quarter

1. Go into the Properties for Calendar Quarter and under Key Columns put in the following, with the same order as shown below:

1. The final step is to set your Dimension to Time
   1. You do this by clicking on Dim Date at the top of your Attributes and right clicking andselecting Properties

i.      Where it says Type change this to Time

1. Then finally do a Full Process on the Date DIM.
2. Once that is done it will invalidate all the cubes associated with the date DIM, so you will also have to do a Full Process on the cubes for the new Date DIM to pull through.
3. Finally go through to your Excel Spreadsheet, possibly refresh the data if it already had data.
   1. Then put in the Date Hierarchy in your Row Labels and put something in your values, you should then see your Date Filters as shown below