excelpivot-tablessasdata-warehouseanalysisservices

Problem with SSAS ParallelPeriod and Excel 2013 Timeline Filter

Currently i had a project using Microsoft SQL Server Analysis Service. I found a problem regarding filtering data with excel timeline.

Here is my date dimension screenshot:

<img src="https://i.sstatic.net/NUr2x.png"/><img src="https://i.sstatic.net/5OSgA.png" />

I had a cube with 2 measures, Sales Quantity (measures) and Sales Quantity Last Year (calculation). Here is MDX expression for Sales Quantity Last Year calculation:

( ParallelPeriod([Date].[YM].[Calendar Year],1,[Date].[YM].CurrentMember),[Measures].[Sales Quantity In 1000] )

After deploying the project to my local server, the data can be shown perfectly using excel 2013: Pic: Data in Excel without filter

The problem start when i want to filter the data using excel timeline. When i filter only '2016', my calculation measure is no longer working. You can see the data in 'Sales Quantity in 1000 LY' column is blank. It looks like that i cant see the data outside current filter (2016). Pic: Filtered using timeline filter

But when i use slicer, the data can be shown normally Pic:Filtered using Slicer

Did i make a mistake in building date dimension? Or i need to fix the MDX calculation query? Because when i test this case in Microsoft AdventureWorksDW2014 with the same date hierarchy and the same calculation, all is going well.

Solution

  • Your parallel period calculation looks correct assuming [Date].[YM] is your date hierarchy. I am guessing that your date dimension is off somehow.

    Make sure that:

    1. it has a hierarchy created, and the hierarchy is what you are referencing in the parallel period calculation. Here is an example, you could have more or less attributes in the hierarchy obviously.

    Hierarchy

    1. Your attribute relationships are defined correctly.

    Attribute relationships[1]

    1. Key columns on the attributes in the hierarchy are correct. In the example above, you would just make year the key for the year column, but then for quarter it would be a collection of the year and quarter column. For period, key columns would be year, quarter, period. For week, key columns would be year, quarter, period, week. Date would just use the date column since date is the key.

    4.Make sure that the date key attribute is using a date field for it's value column, as a time slicer needs this.

    date value

    1. define time intelligence on your date dimension. Right click on the date dimension on the solution explorer and choose add business intelligence, then on choose enhancement screen pick define dimension intelligence. Then set the attribute type for each dimension attribute. Here is how it would be for our example.

    Time Intelligence

    Hopefully one of these does it for you.