locked
MDS Domain-Based Attributes in Excel RRS feed

  • Question

  • I have setup SQL Developer 2016 and Excel 2013 with all of their updates through today. Still having the following problem.

    Specifically, this is regarding the drop down list in the Excel sheet cell after I have setup the attribute to be domain-based. On my install, the drop down in Excel only shows “DomainEntity2345ljh234j5h2….” (DomainEntity followed by a random string which is different for different columns that have the domain-based attribute setup). It should show the list of names and codes that it should pull from the associated entity. I have setup a basic test set of entities with nothing too complicated.

    Multiple re-installs of the add in and re-creation of the domain-based attribute both starting from Excel and the web explorer but nothing. It's fine in the web explorer when I drill down to the member row and open the side box. Just need it to work in Excel otherwise the entire domain-based drop down concept won't serve its function.

    Thanks,

    Chris

    Wednesday, December 14, 2016 8:08 PM

Answers

All replies

  • Any suggestions for Chris?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, December 19, 2016 9:34 AM
  • Chris, have you made any progress with this issue?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, January 3, 2017 10:43 PM
  • No, Ed, but thanks for keeping an eye out for it. It's rather frustrating because anywhere I read or watch a video on this it just works - drop down in Excel and there are the list of things to select. I have tried a few things short of a complete reinstall, but they all end up the same. In Excel, any domain-based attribute drop down just shows one item as "DomainEntity" immediately followed by a string of letters and numbers. Each different column/attribute has a different string. I have seen one other person post the same exact thing in a different MS forum/FAQ board. Recent post, too. When I check the drop down over on the web interface entry screen, the drop downs work as they should - shorter than 50 items and they show up there, longer and they show up in a pop-up window. I really need it to work in Excel for the type of work I am planning. I'm thinking of testing a third party MDS tool to do data entry one by one, but would much rather stay in the MS ecosystem. What would be your advice on how to proceed? Thanks, Chris
    Tuesday, January 3, 2017 11:26 PM
  • Chris,

    It's even since MDS 2012

    The domain attribute in Excel shows the Name and Code in the combobox if the parent entity have ~ <= 15000...20000 records. Next shows Code only.


    from Moscow with money

    Tuesday, January 10, 2017 10:16 AM
  • It would be great if it actually worked that way. I'm not getting name or code but just one choice of "DomainEntitiy746fjfkgmg74616..." ("DomainEntity" followed by a random string"
    Tuesday, January 10, 2017 4:07 PM
  • Hi ALL,

    have you guys considered changing the display format as shown in the below screenshot ? that's the sample data which you get when you install the MDS packages and it has more than 7000+ records in it . I haven't seen this behavior before .

    Wednesday, January 11, 2017 2:36 AM
  • I have another customer reported to this issue as well , can Chris or some one help me with the model package to quickly reproduce this on my environment ? just include only parent and child entity with few rows and generate using the MODEL deploy tool and provide it to me , so that I can invest some time to look into it .

    note : it looks like a bug to me as I haven't seen this issue with the models I have or MDS provide by default .

    Thursday, January 12, 2017 3:00 AM
  • Any Update Chris or some one who faced this issue can talk to me ?

    thanks,

    santhosh

    Sunday, January 22, 2017 8:08 AM
  • I will have to figure out how to do the model deployment.
    Sunday, January 22, 2017 8:39 AM
  • We have the same issue now. I haven't used the excel addin since november. we are on SQL 2016 SP1 and Office pro 2016 64 Bit.  We haven't changed any of our MDS models. The only thing which has happended since november are the huge amount of .NET framework / Office /Excel security updates on our workstations in the last two month. Could it be some security update broke the excel addin ? We urgendly need a solution. Kind Regards

    Christoph

    Ethenea S.A.

    Monday, January 23, 2017 8:27 PM
  • Just found out when i right click a cell with a domain based attribute (showing this "DomainEntitiy746fjf..") and select the menu "dropdownlist selection" then a dropdownlist filled with the domain based values is shown. Maybe this helps finding the bug

    Christoph

    Monday, January 23, 2017 10:09 PM
  • ok, i have narrowed down the problem. It is the SQL2016 SP1 Excel Addin. i have just installed the older SQL2016 Version and it works fine again with domain based attribute dropdownlists

    Christoph

    Monday, January 23, 2017 10:47 PM
  • Replicated what Christoph said on my end - right clicking to pick from dropdown shows the list. Left clicking on the dropdown arrow still shows the problem.
    • Proposed as answer by Tomas Brander Tuesday, April 25, 2017 12:38 PM
    • Unproposed as answer by Tomas Brander Tuesday, April 25, 2017 12:38 PM
    Monday, January 23, 2017 10:49 PM
  • please correct me if I am wrong . so are you guys saying after installing SQL 2016 SP1 excel addin and when you have DBA On the active MDS sheet ( be it any model/entity) , then if you left click there is an issue and right click shows the correct values on the DBA column ?

    thanks,

    santhosh

    Wednesday, January 25, 2017 10:48 AM
  • yes, the SQL 2016 SP1 excel addin does not work. Uninstall and reinstall of the older version works with SQL Server 2016 SP1. I have tested this on  three workstations running windows 10 and Office 2016 with all updates and security patches.The Version i have installed is 13.0.2121.20 But i have a few other problems. Sometimes when i open and refresh a workbook with mds models i get a index out of range exception and the workbook is broken and not usable anymore.  Other times when i refresh a worksheet the columns are all messed up and the workbook is also broken. This is very annoying. 

    Christoph

    Wednesday, January 25, 2017 1:58 PM
  • Yes for me as well. The newest version works by right click only. I also did as Christoph and uninstalled the newest add in then installed the older version and the left clicking dropdown works as it should. Similar setup as Christoph as for SQL and Office.
    Wednesday, January 25, 2017 3:32 PM
  • Thanks for the confirmation guys , this is definitely an odd behavior of right click . can anyone try opening a connect bug so that you guys can track it on the progress . we will investigate this issue and I have noted it down .

    thanks,

    sunny

    Thursday, January 26, 2017 3:24 AM
  • thanks, i will open a bug report on connect.

    Christoph

    Thursday, January 26, 2017 12:44 PM
  • well, there is something serious broken in the addin. The older addin is not usable, too.  A DBA based  dropdown works but after publishing, saving the workbook, closing excel, reopening the workbook and  clicking refresh results in an index out of bounce exception..

    Christoph

    Thursday, January 26, 2017 4:34 PM
  • An update on this , identified the root cause and ready with fix , once it is published live I will give you the download link for this . please be aware of the release timelines , it might take some time .

    mean while you can use excel 2016 RTM Addin from this link , which works fine with DBA .

    https://www.microsoft.com/en-us/download/details.aspx?id=52677

    Thursday, March 9, 2017 3:38 AM
  • hi Christoph,

    can you please send an email to this dl ? sqlmdsfeedback@microsoft.com with detailed repro steps and sample DB/ model package for me to repro it . which will help me investigate further

    thanks,

    sunny

    Thursday, March 9, 2017 3:40 AM
  • I just installed MDS and Excel add-in for MDS at weekend. I have same issue.

    The below string appears in the dropdown .....

    So a major issue now for anyone (including me) on SQL 2016 SP1 excel addin :(

    DomainEntitya274d944a3f34f1dacfd794b8ac1731e

    Sunday, March 12, 2017 8:33 PM
  •        

    An update on this , identified the root cause and ready with fix , once it is published live I will give you the download link for this . please be aware of the release timelines , it might take some time .

    mean while you can use excel 2016 RTM Addin from this link , which works fine with DBA .

    https://www.microsoft.com/en-us/download/details.aspx?id=52677

    Monday, March 13, 2017 6:02 AM
  • Is this fixed in the newest cu2 for sp1 (13.0.4422.0)?

    Probably related to this issue. It looks like the problem also appears in the webinterface.

    First the Webinterface bug:

    When editing a record with some domain based attributes, the first record editing goes fine.

    When I want to edit another record the list only shows the value of the last edited record.

    The list icon will not change back to the pencil icon.

    When I reload the whole page, I can select the records again.

    See video: https://www.youtube.com/watch?v=EyqIRvRctvM

    In Excel, with the add-in, we have this strange thing on some lists, not all:

    See video: https://www.youtube.com/watch?v=rabW5qQpMTg

    Some lists show up as expected. Others show a strange GUID like string.

    Thank you. I've tried to mail sqlmdsfeedback@microsoft.com. But not working anymore.


    Expert on Microsoft Business Intelligence with Excel and SQL Server http://qvada.com

    Friday, March 31, 2017 12:13 PM
  • Both are different issue .

    regarding excel addin release , it will be available in May release , we already fixed it and verifying before releasing it to the download center .

    Coming to web application , yes Pencil icon is hidden after setting the filter , temporary workaround for now is to refresh the page . we have a defect and will fix it in later versions based on the priority .

    thanks for letting me know about the SQLMDSFEEDBACK email is not working , just observed it and I will check with team why this is not working now .

    thanks,

    Sunny

    Thursday, April 6, 2017 6:34 AM
  • Replicated what Christoph said on my end - right clicking to pick from dropdown shows the list. Left clicking on the dropdown arrow still shows the problem.

    I tried this and right clicking gets a dropdown list, but it is not a complete list for the domain attribute entity but a list of values from the excel column. Like a filter list.

    //Tomas

    Tuesday, April 25, 2017 12:48 PM
  • Hello,

    I just reported the same issue in a separate post.  Is this the same issue and is the workaround to install the RTM Excel Add-in instead of SP1? Also, can you please confirm that it will be fixed since I'm responsible for rolling out a solution that requires the MDS Excel Add-in?

    Here's the link to my related post:

    https://social.technet.microsoft.com/Forums/en-US/105f5922-f0ac-48d8-a9e4-8aa6500eae4e/master-data-services-addin-for-excel-2016-are-not-consistent-with-mds-explorer?forum=sqlmds

    Thank you,

    Ben


    Ben Lezin

    Thursday, May 11, 2017 6:37 PM
  • Hello,

    Microsoft has released yesterday the Cumulative Update 3 for SQL Server 2016 SP1 supposed to fix the problem of the random string in a domain-based attribute dropdown list.

    https://support.microsoft.com/en-us/help/4018954

    Unfortunately, after installing this fix, i still have the problem using MDS SQL Server 2016 SP1 Add-in for Excel. I can't use the RTM version of the Add-in Excel because it has another bug with the filter Domain based Attribute feature.

    Can you please confim the same behavior after installing this fix.

    Thank you for your help.

    Zine Eddine





    • Edited by testaeff Tuesday, May 16, 2017 10:32 AM
    Tuesday, May 16, 2017 10:31 AM
  • I have also installed Cumulative Update 3 for SQL Server 2016 SP1 and MDS SQL Server 2016 SP1 Add-in for Excel. The problem still exists :-( "9731830 4018954 FIX: Domain entity drop-down displays an incorrect value when you connect to MDS by using MDS Add-in for Excel in SQL Server 2016" did not fix it.
     Is there a new Excel Add-in matching the CU3 ? I wanted to try the Excel-Addin from the SQL Server 2016 SP1 Feature pack but the 64 Bit MasterDataServicesExcelAddin.msi is not 64bit it is the 32Bit Version!

    @Smartysanthosh: Do you need  a sample model ? I can mail a repro to sqlmdsfeedback@microsoft.com.

    Christoph
    Wednesday, May 17, 2017 12:48 PM
  • Hello,

    Thank you for confirming that the Cumulative Update 3 doesn't resolve the issue with the SP1 version of the MDS Excel Add-in.  Unfortunately, I've also encountered another issue - specific to the MDS Shortcut Query File - that may be resolved by updating to CU3 and/or Add-in SP1. However, I don't want to upgrade since it will break the domain attribute functionality.

    Does anyone know if there is a designated Microsoft moderator for these MDS Add-in issues? I haven't received any traction for the Shortcut Query File issue on a separate thread:

    https://social.technet.microsoft.com/Forums/sqlserver/en-US/b699a3fb-86dd-4d2d-af14-3afbe3dcff95/mds-shortcut-query-file-doesnt-work-if-the-excel-workbook-is-created-via-the-mds-portal?forum=sqlmds

    Thank you!


    Ben Lezin



    • Edited by blezin Wednesday, May 24, 2017 4:59 PM
    Wednesday, May 24, 2017 4:58 PM
  • i have invested some time getting the MDS Addin for SQL vNext CTP1.3(CTP1.4) to work. The addin installation fails due to wrongly signed  assemblies but i got it working. This was just a test und is not recommended. 

    The DBA attributes and cascaded filters work in this release.

    https://connect.microsoft.com/SQLServer/feedback/details/3134869

    please upvote this report to get a working version for SQL2016/SQLvNext

    Christoph

      
    Monday, May 29, 2017 5:46 AM
  • @msdn ethenea,

    we recently met with some build infra issues and this is due for a release , if you see the SP1 excel addin available it is still the old one and we haven't updated  the link yet . I am working to release this once our build infra outage is back . appreciate the patience and sorry for the inconvenience .

    @Ben Lezin , sorry I haven't checked your post , let me reply you there .

    @Christoph , in 2017 ( which is VNEXT ) we added cascaded filters so you will not face this DBA drop down issue anymore .

    Wednesday, June 14, 2017 5:38 AM
  • Thanks for taking a look :-)  i am also the Ethenea guy, i used the wrong account. I have tested the cascaded filters/DBA with vNext 1.3CTP and works great.

    In the web MDS Webfrontend i tried to set permissions on a hierarchy but the hierarchy does does not load. it loads forever. I have tried several different hierarchies but i can't set permissions.  

    Yesterday the Webfrontend and Webservices crashed while not being used. i had to restart the default web site. Where can i look (or turn on) some error logging?

    Wednesday, June 14, 2017 6:28 AM
  • bug report for "In the web MDS Webfrontend i tried to set permissions on a hierarchy but the hierarchy does does not load. it loads forever. I have tried several different hierarchies but i can't set permissions. " 

    https://connect.microsoft.com/SQLServer/feedback/details/3136009/mds-unable-to-grant-permissions-to-a-hierarchy

    Wednesday, June 14, 2017 8:34 AM
  • Hello Smartysanthosh,

    Could you update about the progress, the last version (13.0.4447.0) still not woriking.

    Thursday, September 7, 2017 3:02 PM
  • we are working to release a latest version , the version we recently released (13.0.4447.0) has some other fixes . apologies for inconvenience .

    could you please use CTP1.3 2017 addin , this has the fix already and its absolutely the same and you will also see the new DBA edit viewer we added for the mean time.

    Friday, September 8, 2017 2:03 AM
  • Hi Santhosh,

    The CTP1.3 2017 addin is not working.

    In some entites i have this error:

    The current cell column title was not found. If the column title was changed, close the sheet and try again.

    In other entites where it's seems working the new DBA edit looks very useful, but when the the default settings is not {code} name then it's not working...

    Is there any solution?

    Cheers,

    M



    • Edited by MMJ_MDS Monday, September 11, 2017 10:28 AM
    Monday, September 11, 2017 10:28 AM
  • Hi Santhosh,

    Do you have any news?

    Thanks,

    M

    Wednesday, October 4, 2017 6:15 PM
  • Hi,

    Still no answer and we have no solution. What do you need from us to solve it? It's not working for more than 1 year now... Increadible.

    Tuesday, December 5, 2017 7:47 AM
  • Remove display name of the Domain-based attribute (or make it exactly the same as the original name) and it will work... (:
    Thursday, April 16, 2020 3:45 PM