1 Reply Latest reply on Feb 11, 2015 12:11 PM by Mary Solbrig

    [Microsoft Analysis Services] Working around dates, filters and rankings

    Benoit Cocheteux

      Dear All,

       

      First, let me introduce myself as this is my first post on the Forum. I work in an organization that seeks to extract the most from its huge amount of data coming for different sources (we operate in the transportation industry). My job is to make this happen and that's why I'm here: I would like to share with you all my issues and gather enough knowledge to be able to later help others on similar topics.

       

      So, I'm working with Tableau 8.2 and I am connecting to the unique data source that I have access to: the Cubes. Basically, we have more that 500 dimensions available but we restrained them to a few that are useful at first.

      Here is a screenshot of what we have :

      Basic View.jpg

      The idea is know to be able to:

      _Compare current results against budget and against previous year.

      _Drill down from Group Level to Local office

      _Surf within our dimensions to identify discrepancies, best/worst performers and so on..

       

      As I prepared the workbook, I encountered several issues which are:

      _Delete a calculation column from a specific year

      Delete column.jpg

      _Show only relevant values when filtered by division for a specific dimension

      Here, when I filtered one division (Division 1 for instance) and that I selected to see our customers names, I had all the names of other divisions showing up although they had null values. I then filtered the GM to show non-null values but I'd like to avoid this filtering!

       

      _Work out the Top N Customers for a sub-category (Division and Area)

      When I drill-down and filter Top N Customers by GM, it calculates the top N Customers for all divisions and then only returns  customers of the filtered division that are within this Top N.

       

      _Work around the dates (very common issue with Microsoft Analysis Services).

      I did try to use a precedent post technique (using Tableau – Designing and working with cubes in Tableau | Inspari blog) but it didn't work well as my GM is blurred by overlapping text:

      Overlapping text.jpg

      I cannot use the DATEPARSE function and I think I'm getting pretty close to what I want here, so I'd like to know what you think about it. It seems that I have to set date attributes, but I know not where.

       

      _Create a filter list of filters

      Here, I would like to offer the option for any user to select on the dashboard a specific filtering set to drill down the data. For instance, I would like to: (1) filter by Area and Customers ; (2) filter by Area and Office ; (3) filter by Office and Customers and so on...

       

      _Tableau failures:

      I encountered several Tableau failures while setting up the date dimension as "date" and then use it as filter. Here is the query in attachments as well as the Error details. I don't know why this is showing up, it doesn't do this when using another Cube.

       

      All in all, I know that using Microsoft Analysis Services is not the best option, but it's the only one I have and I'd like to make it work.

       

      If you have any questions, feel free to ask!

       

      Thank you very much for your time!

        • 1. Re: [Microsoft Analysis Services] Working around dates, filters and rankings
          Mary Solbrig

          First, why the overlapping marks are occurring:

           

          Cubes try to do all of the aggregation in the cube itself, and they have all sorts of predefined aggregation definitions that Tableau does not have access to. For instance, if you are trying to find the sum of sales for the month of September, it may not be the same as adding the sales from each day of September together. It is possible that 15th of September is pro-rated at 50% in the cube for some reason, and Tableau would not know. What Tableau can do is ask the cube for results at the month level, see what the result is and return that, or ask for results at the day-level and return each day separately. It must remain agnostic about how to combine those days.

           

          When you add a Day of Date to the filters shelf, that forces the view to return values at the level of day. If you then use a filter to select the entire month of September, this means that the cube returns 30 values to Tableau, and it does not know how to combine these. Therefore it creates 30 overlapping marks. A better choice would be to take a Month of Date to the filters shelf, ensure that it is the Discrete type, and select just the desired month.

           

          Now, the possible work around: If you are really very convinced that simply summing the values for each day together would give you the correct result and there is no fancy aggregation definition in the Cube, you can force Tableau to do this using a WINDOW_SUM. Again, we cannot guarantee that this will give the correct result because we do not know if there are hidden aggregation definitions in the cube, so use at your own risk:

           

          For each field "foo" you would like to sum up:

          1. Select Analysis > Create Calculated field

          2. In Name enter SUM Foo and in Formula enter WINDOW_SUM([foo]) and click OK

          3. Select Analysis > Create Calculated field

          4. In Name enter First Filter and in Formula enter FIRST()=0

          5. Drag Sum Foo to the Measures shelf in place of Foo

          6. Right click on Sum Foo and select Compute Using > Day(Time)

          7. Drag First Filter to the Filters shelf

          8. Select True and select OK

          9. Right Click on First Filter and select Compute Using > Day(Time)

          10. Select True and select OK

          11. If Day is on the Columns shelf, move it to the Details shelf on the Marks card.