none
SSAS Tabular Model and browse with MS Excel 2013 RRS feed

  • Question

  • I have a tabular model, It has an attribute with datatype and data format as "whole number". When I browse the model from excel and add label filter, it does not work as expected.

    When I specify greater than 180 as label filter, It gives following output:

    My guess is that it is just filtering on first two digits "18" and not "180". When I copy these values in a excel sheet and create pivot table on top of it, hell it works fine.

    Is this a microsoft bug? have you encountered something like this or I am doing some thing wrong?

    I am using tabular model 2012 and excel 2013 or 2010.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Wednesday, September 24, 2014 9:38 PM

All replies

  • Hi Itz,

    SSAS (Multidimentional /Tabular) Model doesn't seem to relate data between tables if there are no measures defined.

    When you specify greater than 180 as label filter, that time your "Number of days sim.." dimension filter the data on default measure which you specifed at the time of cube design.

    Just check placing the mesure and then try to filter out the data. (Before that just change your pivot table disply properties checked as "Show Item with no data on rows")

    Hope this will help you.

    Thanks,

    Suhas


    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    My Blog
    Follow @SuhasKudekar

    Thursday, September 25, 2014 9:29 AM
  • Hi Suhas,

    When I select an attribute in the pivot table, i get all the rows for that attribute. I agree that it relates to default measure if we do not select any measure. However when the data is in the pivot table, all the properties of the pivot must be applicable to the data irrespective of the data source. So when I filter on "greater than 180", it should give me desired result. Agree?

    I tried your suggestions and still the label filter is not working.

    I believe it is a bug unless there is some setting I am not aware of.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, September 25, 2014 1:53 PM
  • Hi Itz,

    According to your description, the filter not works fine in excel for a SQL Server Analysis Services Tabular models, right?

    I have tested it on the local environment, we cannot reproduce this issue. As per my understanding, this issue is related to the settings of your tabular model. Does this issue can be reproduce in multiple servers in your environment? In that is case, from a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with Microsoft. Visit this link to see the various support options that are available to better meet your needs: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Besides, if you consider this issue is a bug, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback Microsoft will confirm if this issue is a bug or not. 

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Stefano__ Wednesday, November 21, 2018 9:07 PM
    Saturday, September 27, 2014 3:13 AM
    Moderator
  • Hi Charlie,

    I can reproduce this issue. I will tell you the steps:

    1. Create a global temporary/ physical table with increment numbers from 1 to 5000 as follows:

    ;WITH Numbers (Numbers) AS
    (
        SELECT 1
        UNION ALL
        SELECT Numbers+1 FROM Numbers WHERE Numbers < 5000
    )
    
    SELECT Numbers INTO ##T
    FROM Numbers 
    ORDER BY Numbers
    OPTION (MAXRECURSION 5000);

    2. Create tabular model and import this data into the model.

    3. From excel connect to this model and apply label filter let's say "greater than 180". This is what I see as an output when I click ok on the message box below:

    What I see here is the Label filter DO NOT work. It does not bring me numbers greater than 180. Can you please try at your end and let me know if you also see this as a bug?


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, September 29, 2014 1:38 PM
  • When MDX is returning row and column labels, they are interpreted as text. You'll note that dates are not treated as dates either.

    This is the expected behavior for any pivot table in Excel against any SSAS instance, Tabular or Multidimensional

    Even though this pivot table is against a Tabular model, Excel only speaks MDX.

    Monday, September 29, 2014 3:03 PM
  • Thanks Greg for the info!

    Converting everything to TEXT is incorrect, excel must use the source data type. When querying the OLAP source, then there is no use of having other integer filter options in the excel. It should then display only related text filters.

    The other thing is when we create pivot table on top of data from excel, the label filter works correctly.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, September 29, 2014 3:41 PM
  • Greater than and less than operators can have valid uses on text fields.

    A workaround is to create a measure that enforces the logic you need, rather than using a label filter. If you create a simple measure such as the following you can enforce integer comparisons for label filters:

    Measure:=
    CALCULATE(
        COUNTROWS( FactTable )
        , FILTER( DimTable
            , DimTable[DimColumn] > 180
        )
    )

    This will force the pivot table to only display rows where DimTable[DimColum] >180.

    If this needs to be dynamic, you can use a disconnected slicer. You can use a pivot table filter just as easily as a slicer here.

    As you have observed, the text conversion is an artifact of the connection to an SSAS instance. If you can implement your solution in a Power Pivot workbook (hosted on SharePoint perhaps), it may be your better option.

    Monday, September 29, 2014 3:55 PM
  • I have to implement this at server side and the filter should be dynamic.

    This issue is only with the attributes, however when we convert these attributes into measure it works fine. So what we came up with a solution is to convert required attributes into measures with something like below:

    1. Create DistinctCount measure on top of required attribute : DistinctCountofNumbers:= DISTINCTCOUNT([Numbers])

    2. Create another measure Number:= IF([DistinctCountofNumbers]=1,MAX([Numbers]),BLANK())

    Original attribute Numbers and DistinctCount measure are hidden. Measure Number is same as attribute Numbers, just the label filter in excel works fine now.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Proposed as answer by Suhas Kudekar Tuesday, September 30, 2014 8:45 AM
    Monday, September 29, 2014 5:57 PM
  • I have given this feedback to microsoft. If you think this should be corrected in the future version of the product, please VOTE below:

    https://connect.microsoft.com/SQLServer/feedbackdetail/view/984778#tabs


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, September 29, 2014 6:10 PM