locked
Selectively mark Excel columns in MDS as Read-Only RRS feed

  • Question

  • Is there an internally supported feature in MDS 2012 Excel add-in wherein we can set some columns (e.g Code column) as Read-Only? The use case we're trying to evaluate is to not allow users to make changes to the Business Key column of an entity (populated from an actual DW dimension) and instead accept changes to only a few attributes. We'd want to display the read-only attributes for informational purposes to the user.
    Monday, April 15, 2013 7:23 PM

Answers

  • I don't know if this helps but we had a case when and MDS entity needs to have one of its columns imported from an outside table. The requirement was that the users can not change the values of that column, also they are not allowed to add new members or delete existing members of the entity.

    We solve this by giving readonly permission on the entity and the update permission on all the columns of the entity except the one that should be readonly.

    Eg:

         TestEntity

                Code

                Name

                Col - should be readonly

    Applied permissions:

         TestEntity        --readonly

                Code        --update

                Name       --update

    Because Readonly is applied at the entity level(TestEntity) new members can't be added, existing members can't be deleted, and because Update is applied on the attribute level(Code and Name) their values can be updated. As nothing is applied on Col it inherits the permissions from TestEntity and remains readonly.

    The test should be applied with a different user that the admin of the MDS.

    Tuesday, April 29, 2014 11:46 AM
  • Hi Muqadder,

    In the Master Data Services Add-in for Excel, the following input statuses are possible:

    Status

    Description

    Error

    One or more values in the row don’t meet system requirements like length or data type. The value is not updated in the MDS repository.

    New Row

    The values in the row have not yet been published to the MDS repository.

    Read Only

    The logged in user has Read-only permissions to one or more values in the row and the value(s) cannot be updated.

    Unchanged

    No values in the row have been changed in the worksheet. This does not mean the values in the repository have not changed; to get the latest data in the sheet, in the Connect and Load group, click Load or Refresh.

    This is the default setting for each row.

    For more information, please see:
    Validating Data (MDS Add-in for Excel): http://technet.microsoft.com/en-us/library/hh479625.aspx

    Master Data Services Add-in based on the user permission to determine the data is read-only or not. It is not possible to mark some column as Read-only.

    Thanks,


    Elvis Long
    TechNet Community Support

    Friday, April 19, 2013 3:18 AM
  • Hello,

    The answer Elvis provided you states that it is not possible to do it in the Excel Add-in, but if I read the use case you describe all you want is to put one column as read only for a set of users, and that is something you actually can do in the admin section.

    All you have to do is :

      • go the security section of MDS administration (this icon : )
      • select the user (or user group) and click on the edit button.
      • Navigate to the Model Tab
      • Click on the Edit  button again 
      • Select the Attribute in the tree view and select read-only
    • Save

    Now for the user this attribute will be displayed in gray in his Excel and he won't be able to publish the change he makes on it to MDS.

    If you do the modification directly on the user to test it, just check that the group he belongs to don't have the write permission on the attribute or the behavior will be a bit strange as the column will be displayed on gray in excel but he still will be able to modify it, I guess this is a bug...

    Regards,


    Friday, April 19, 2013 9:21 AM
  • There may be a bug in Office 2013 or something here.  I tried this config (works perfectly from the Web UI I might add) and then ran an Excel test.  The column is definitely dark grey which is a nice visual que.  However, my test account could still enter a deliberate (poorly chosen) value and successfully publish with no errors or validation warnings.  This is likely to be an issue for my current MDS engagement as my customer wants their users to create new members but they don't want users deciding what the code values should be as they worry about standards and consistency in the code generation. 

    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Tuesday, April 30, 2013 6:09 PM

All replies

  • Hi Muqadder,

    In the Master Data Services Add-in for Excel, the following input statuses are possible:

    Status

    Description

    Error

    One or more values in the row don’t meet system requirements like length or data type. The value is not updated in the MDS repository.

    New Row

    The values in the row have not yet been published to the MDS repository.

    Read Only

    The logged in user has Read-only permissions to one or more values in the row and the value(s) cannot be updated.

    Unchanged

    No values in the row have been changed in the worksheet. This does not mean the values in the repository have not changed; to get the latest data in the sheet, in the Connect and Load group, click Load or Refresh.

    This is the default setting for each row.

    For more information, please see:
    Validating Data (MDS Add-in for Excel): http://technet.microsoft.com/en-us/library/hh479625.aspx

    Master Data Services Add-in based on the user permission to determine the data is read-only or not. It is not possible to mark some column as Read-only.

    Thanks,


    Elvis Long
    TechNet Community Support

    Friday, April 19, 2013 3:18 AM
  • Hello,

    The answer Elvis provided you states that it is not possible to do it in the Excel Add-in, but if I read the use case you describe all you want is to put one column as read only for a set of users, and that is something you actually can do in the admin section.

    All you have to do is :

      • go the security section of MDS administration (this icon : )
      • select the user (or user group) and click on the edit button.
      • Navigate to the Model Tab
      • Click on the Edit  button again 
      • Select the Attribute in the tree view and select read-only
    • Save

    Now for the user this attribute will be displayed in gray in his Excel and he won't be able to publish the change he makes on it to MDS.

    If you do the modification directly on the user to test it, just check that the group he belongs to don't have the write permission on the attribute or the behavior will be a bit strange as the column will be displayed on gray in excel but he still will be able to modify it, I guess this is a bug...

    Regards,


    Friday, April 19, 2013 9:21 AM
  • There may be a bug in Office 2013 or something here.  I tried this config (works perfectly from the Web UI I might add) and then ran an Excel test.  The column is definitely dark grey which is a nice visual que.  However, my test account could still enter a deliberate (poorly chosen) value and successfully publish with no errors or validation warnings.  This is likely to be an issue for my current MDS engagement as my customer wants their users to create new members but they don't want users deciding what the code values should be as they worry about standards and consistency in the code generation. 

    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Tuesday, April 30, 2013 6:09 PM
  • Ran a second test using Excel 2010 (32 bit) and got a completely different experience from what I saw with Excel 2013 64bit.  This time, I get a popup error if I try to edit the code on existing records.  However, from the older version of Excel I am not able to add new members (records).  When I tab to the next row, nothing happens and it doesn't go into edit mode for a new member.  Also, I am able to delete a member which seems really  bizarre that I can delete members from Excel but not add them.  This has got to be a known issue.  I gotta believe that others are clamoring for this to work right from Excel. 

    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Wednesday, May 1, 2013 3:08 AM
  • I'm back to this post after a long time but I feel more comfortable seeing that someone else was able to reproduce the same issue I originally reported (I am using Excel 2013 like Chad). I haven't quite tested the behavior with other versions of excel yet but considering that Office 2013 is going to be the defacto office version in my company soon, there is little to no merit for me to work with other versions.

    If someone from Microsoft MDS support team could share some thoughts around this issue, that'd be helpful.

    -Muqadder.

    Thursday, June 20, 2013 8:48 PM
  • I don't know if this helps but we had a case when and MDS entity needs to have one of its columns imported from an outside table. The requirement was that the users can not change the values of that column, also they are not allowed to add new members or delete existing members of the entity.

    We solve this by giving readonly permission on the entity and the update permission on all the columns of the entity except the one that should be readonly.

    Eg:

         TestEntity

                Code

                Name

                Col - should be readonly

    Applied permissions:

         TestEntity        --readonly

                Code        --update

                Name       --update

    Because Readonly is applied at the entity level(TestEntity) new members can't be added, existing members can't be deleted, and because Update is applied on the attribute level(Code and Name) their values can be updated. As nothing is applied on Col it inherits the permissions from TestEntity and remains readonly.

    The test should be applied with a different user that the admin of the MDS.

    Tuesday, April 29, 2014 11:46 AM