none
SSAS Tabular Model in Excel issues RRS feed

  • Question

  • Hi all,

    I have a few queries regarding using a SSAS Tabular model in an Excel pivot table. Now, I know these things are possible, but I don't know if limitations of Excel stop it from happening.

    - Reporting Properties - I know in Multidimensional you could set Reporting Properties so that when you hover over a dimension in a pivot table, you can see a tooltip with extra dimension data. When I right click a dimension in rows, I see 'Show Properties in Report', but it says 'No Properties Defined'. This is obvious because I don't know where I can define them in Tabular!

    - Column Descriptions - In the Tabular designer, I'm able to add Descriptions to column properties, and I'm expecting in a pivot table to be able to see this description when I hover over the field. This isn't happening. It does however, work in Power View!

    - Sorting fields in data source order - Is there any way to order the field list in a pivot table by data source order when connected to a Tabular model?

    - Managed Sets - Is there any way to create a Data set in the Tabular model instead of within Excel?

    Any help on these is much appreciated

    Wednesday, May 20, 2015 4:23 PM

Answers

  • Ah, you know I've never noticed that there. :)

    I just checked and this is also greyed out for Multi-Dim models. The SSAS server actually returns the metadata sorted alphabetically, it's not something Excel is doing, so the fields are already in "datasource order" and Excel can't request a different order.

    I'm pretty sure I've seen requests in the past (probably on http://connect.microsoft.com/sql) for Microsoft to add a property to allow developers to turn off the automatic alphabetical sorting and to use the order defined in the designer, but to the best of my knowledge there has not been any work done in this area.


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

    Thursday, May 21, 2015 9:09 PM
    Moderator

All replies

  • Anyone?
    Thursday, May 21, 2015 9:29 AM
  • - Reporting Properties - I know in Multidimensional you could set Reporting Properties so that when you hover over a dimension in a pivot table, you can see a tooltip with extra dimension data. When I right click a dimension in rows, I see 'Show Properties in Report', but it says 'No Properties Defined'. This is obvious because I don't know where I can define them in Tabular!

    There is no where to define these in Tabular. These properties depend on attribute relationships and this concept does not translate over to tabular models.


    - Column Descriptions - In the Tabular designer, I'm able to add Descriptions to column properties, and I'm expecting in a pivot table to be able to see this description when I hover over the field. This isn't happening. It does however, work in Power View!

    Unfortunately Excel does not ask for the description property when it sends the query for a pivot table, I'm not aware of any way to change this.


    - Sorting fields in data source order - Is there any way to order the field list in a pivot table by data source order when connected to a Tabular model?

    You can specify a "sort column" in the properties of a column within your model (so that you can sort month names into the correct date order rather than having them sort alphabetically)

    - Managed Sets - Is there any way to create a Data set in the Tabular model instead of within Excel?

    There is no such thing as sets in Tabular models (everything is tables, columns and rows), hence there is no way to create named sets.


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

    Thursday, May 21, 2015 10:18 AM
    Moderator
  • Thanks for that Darren,

    With regards to sorting the fields, I meant the order in which the fields appear in the Pivot Field list, not the data in the columns. There usually is an option to 'Sort in Data Source order' which sorts the fields in an order to which they are in the model itself, but this is grayed out

    • Edited by MurtzD Thursday, May 21, 2015 10:54 AM
    Thursday, May 21, 2015 10:53 AM
  • With regards to sorting the fields, I meant the order in which the fields appear in the Pivot Field list, not the data in the columns. There usually is an option to 'Sort in Data Source order' which sorts the fields in an order to which they are in the model itself, but this is grayed out

    Sorry, I'm not sure what you are talking about here. I'm using Excel 2013 connected to a SQL 2012 SP2 tabular instance and I can't see that option in the field list at all.

    There is an option once I've dragged a field onto the pivot table, but I see all 3 options - Data Source Order, A-Z and Z-A - none of these are greyed out.


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

    Thursday, May 21, 2015 12:59 PM
    Moderator
  • Apologies for the confusion! Below are some screenshots to show what I mean..

    This shows my Date Dimension. On a connection to a database, I'm able to sort the columns as the order they are in the database table..

    However, if you see the above screenshot, the 'Sort in Data Source Order' is greyed out :(

    Thursday, May 21, 2015 1:50 PM
  • Ah, you know I've never noticed that there. :)

    I just checked and this is also greyed out for Multi-Dim models. The SSAS server actually returns the metadata sorted alphabetically, it's not something Excel is doing, so the fields are already in "datasource order" and Excel can't request a different order.

    I'm pretty sure I've seen requests in the past (probably on http://connect.microsoft.com/sql) for Microsoft to add a property to allow developers to turn off the automatic alphabetical sorting and to use the order defined in the designer, but to the best of my knowledge there has not been any work done in this area.


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

    Thursday, May 21, 2015 9:09 PM
    Moderator