2 Replies Latest reply on May 31, 2018 11:55 AM by Michael Hwa

    SSAS (Cube) data source sorting issue

    Justin Emerick



      I'm having an issue that might be a Tableau issue or it might be a SSAS issue.


      Here is the scenario.


      I'm connecting to a SSAS cube for my data source. I don't prefer Cubes, but it's all I got. The Date Dimension for the Cube has a Calendar Hierarchy (string values that drill from Year - quarter - month - day) and individual attributes for Year (2011, 2012, 2013, etc.), Month (Jan, Feb, March, etc.), and Day ("Jan 1, 2012," "Jan 2, 2012," etc.)


      While the calendar hierarchy provides the drill down functionality, you cannot use "Relative Date" filters with a Calendar Hierarchy (since they are physically  string values - you also can't modify in Tableau the "type" for Calendar Hierarchies to change from String to Date).

      A work around I discovered - since there is an attribute in my Cube Date Dimension "Day" that has values formatted like dates (i.e. "Jan 2, 2012"), I'm able to change that type from String to Date.


      Using "Day" (now formatted as a "date" type) allows me to do relative date filters on my data set (i.e. "Last 90 days").


      Here's the problem:

      I have a report that has a list of Products with a measure "Revenue."

      I create a bar chart that has a bar for the "Revenue" for each "Product."

      I drag into the filters the "Day" attribute and use a relative date filter to get "Last 90 days."


      The values are correct - each product has a revenue value based on the last 90 days.


      However, when I sort products by revenue (largest to smallest), the products are sorted by revenue based on the entire data set in my cube, not based on the revenue values in the last 90 days. So even though products are "sorted," it's not represented as the largest revenue to smallest revenue for the "last 90 days."


      I can't figure out why this is happening- is this an aspect of SSAS and I need to have my BI devs "fix" how the date attributes are associated to Facts or is it a bug in Tableau with how it's managing "sorting" with Cube data sources?