locked
To leveage PowerPivot with MDS data, users need to be able to set Excel Add-in to show code instead of code {Name} for domain-based attributes RRS feed

  • Question

  • Our business users would like to use PowerPivot to analyze data from MDS (data extracted from excel MDS add-in) with other sources of data. However, the domained-based attributes returned from MDS excel-add-in all have Code {Name} instead of just the Code. The users cannot change this through settings like they could in the web UI.

    This causes an issue for business users because, for example, MDS maintains products with product type as one of its domain-based attributes. Other data sources contain financial data by product type. Since the data returned from MDS has code {name}, it no longer match the product type from the other data sources.

    We figured out workarounds but none as user friendly. The ability to have MDS Excel Add-in to display code, instead of code {name} through a setting would be a huge plus for our users to leverage PowerPivot.

    Thanks in advance for your consideration.

    Thursday, April 11, 2013 12:51 PM

Answers

  • Hello,

    Althought I don't know about PowerPivot integration, there is a way to display only the code. It may not be the cleanest but works and can be almost transparent for users :

    - Add a String Attribute named ProductCode to your Product entity

    - Create a business rules on Product Entity that states that if the ProductType Attribute is not empty then the ProductCode Attribute value is set to ProductType.Code value.

    - Display the ProductCode to your user in Excel Add-in which will only contains the code. The user would have to apply the business rules after publishing and then refresh their excel sheet to retrieve the correct code.

    I still agree on the fact that customizing the display of domain attribute should be available though.

    Regards,


    Tuesday, April 16, 2013 10:21 AM

All replies

  • Hello,

    Althought I don't know about PowerPivot integration, there is a way to display only the code. It may not be the cleanest but works and can be almost transparent for users :

    - Add a String Attribute named ProductCode to your Product entity

    - Create a business rules on Product Entity that states that if the ProductType Attribute is not empty then the ProductCode Attribute value is set to ProductType.Code value.

    - Display the ProductCode to your user in Excel Add-in which will only contains the code. The user would have to apply the business rules after publishing and then refresh their excel sheet to retrieve the correct code.

    I still agree on the fact that customizing the display of domain attribute should be available though.

    Regards,


    Tuesday, April 16, 2013 10:21 AM
  •      Thank you Olivier. This is a neat workaround for an entity with small number of attributes.

         When we have a large entity such as Product with many domain-based attributes, the added attributes cause performance issues with the excel add-in. I guess we do not win with workarounds every time.

       Here is our large entity issue:

    http://social.technet.microsoft.com/Forums/en-US/sqlmds/thread/51eab6e8-896a-4c4f-b457-5903d5521a37

    Thursday, April 18, 2013 1:18 AM