none
SSAS Tabular Model in Excel Pivot Table issue RRS feed

  • Question

  • When I query a SSAS Multidimensional cube using an Excel pivot table, the "Show Fields" dropdown box shows a list of the measure group tables available in the cube. When I select a particular measure group, the "Fields" section will only display the related dimension tables. This makes it very easy for users to only select the dimensions and attributes that they can actually use.

    However, when I query a SSAS Tabular database using an Excel pivot table, the "Show Fields" dropdown box shows a list of all of the tables in the tabular model including those with measures and those without.  This is not very useful when you have a large number of tables. The bigger problem however is when I select a table that has measures. When I do this, the "Fields" section will only show the table that I selected. It will not show the related tables like it does for SSAS multidimensional. Users do not have any way to determine which tables are related to the particular measures they have selected. 

    Do the SSAS tabular and Excel teams plan to address this problem? Are there any workarounds for this issue besides creating Perspectives?

    Thank you.

    David


    davidg12

    Tuesday, November 14, 2017 12:45 AM

All replies

  • The bigger problem however is when I select a table that has measures. When I do this, the "Fields" section will only show the table that I selected. It will not show the related tables like it does for SSAS multidimensional. Users do not have any way to determine which tables are related to the particular measures they have selected. 

    I'm not seeing this issue. When I connect to a tabular copy of Adventure Works I do see all the tables listed, but when I pick the "Reseller Sales" table I then only see all the tables related to that table. Note that it's not the presence of measures that drives this - its the relationships in the data model. It's possible in a tabular model to have measures sitting in an isolated table that has no relationship to any other table.

    I think creating perspectives is your best short term solution. I have not heard if Microsoft plans to address this. They would probably have to re-build the pivot table to use the tabular specific metadata instead of what they currently do which is to ask for the multi-dim version of the metadata. It may happen in future, but I have not heard of any announcements of any plans around this.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 14, 2017 2:21 AM
    Moderator
  • I am using SSAS 2016 Tabular and the latest version of Excel (Office 365).  When I select a particular table in the "Show Fields" list, it only displays the table I have selected in the "Fields" section. It does not show the related tables.

    I have created the required relationships between the fact tables (tables containing the measures) and the dimension tables in the tabular model. 

    I understand that this functionality worked with tabular in a prior release but broke in a subsequent release.

    The problem with perspectives is that I'm using a Kimball  star schema dimensional model with about 20 fact tables and 75 conformed dimensions. Users need to be able to select measures from multiple fact tables with related dimensions. Perspectives don't allow this flexibility because they would require users to use different connections and various measures would not be available for them to select.


    davidg12


    • Edited by davidg12 Tuesday, November 14, 2017 12:26 PM
    Tuesday, November 14, 2017 12:26 PM
  • Hi davidg12,

    Thanks for your question.

    I can not reproduce this issue as you said. For this issue, you may consider opening a support case . 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.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 16, 2017 5:39 AM
    Moderator
  • I have this same problem.  Did anyone fine the solution to it.  in SSAS Dimension when you choose a table in Show Fields, it shows measures in all related tables.  In SSAS Tabular, it only shows the measures and table that you selected.  This is a real nuisance as the tabular model grows.  The users find it very confusing to know what fields in what tables they can choose from.
    Friday, September 28, 2018 10:48 PM
  • Clayton,

    This problem is somewhat resolved in the SQL Server 2017 version of SSAS tabular. When you select a specific table in the Show Fields dropdown in Excel, it correctly shows the related tables in the list below. 

    I say "somewhat resolved" because I would expect the "Show fields" list to only show tables with measures in it like it does for SSAS multidimensional. However, it shows all tables -- both with measures and dimensions. Because of this issue, it is much harder to navigate the SSAS Tabular structure  than SSAS multidimensional. 

    By the way, the show fields function does not work properly when using the SQL Server 2016 version of SSAS tabular. Microsoft told me that they will not fix this issue in SQL Server 2016 and you should just upgrade to 2017.

    David


    davidg12

    Saturday, September 29, 2018 12:28 AM
  • Thank you David.  I'll try upgrading then!
    Wednesday, October 10, 2018 2:39 PM