Discrepancy between tbl_1_4_EN and tblAttribute

Question

Hi,

Assuming that the following SQL statement is correct (please tell me if I got it wrong):

SELECT   at.DisplayName

,at.MUID ,md.[Name]

,md.[MUID]

,md.[ObjectId]

,md.[Description]

FROM [FOMMDR].[mdm].[tbl_1_4_EN] md

            INNER JOIN [FOMMDR].[mdm].[tblAttribute] at

                                    ON md.ObjectId = at.MUID WHERE at.DisplayName <> md.[Name]

 

I have found a worrying discrepancy in the data model we have developed and are testing: in our model, there are 57 occurrences in which the attribute Display Name or the attribute Name in tblAttribute is different from the name in the 'model' table tbl_1_4_EN.

Upon examination all occurrences are instances in which the attribute was renamed due to various reasons, such as misspellings or finding a more appropriate name, often shorter, for the attribute. In all cases, tblAttribute  is correct and tbl_1_4_EN is wrong. There is an easy work around, if you know you have the problem, but this should have happened in the first place.

This causes a problem when comparing by name the results of queries against a model and the metadata model - i.e., joining the column names of the query against the model to the attributes names in the query against the metadata model. Effectively, through the web service there does not seem to a way to perform the join mentioned above.

Have I missed something? Is this and issue?

Thanks for your help

Answer 1

Hi,

Nobody has replied to this old post which is slightly worrying. There are the steps to reproduce:

1. Create a model with at least 1 random entity, call it AnEntity, and a couple of randomly named attributes (or more if you are over zealous), e.g. AnAttribute1 and AnAttribute2
2. Run the query above, it should return an empty set
3. Now rename one of the attributes -e g. rename AnAttribute1 to AnAttribute0 and run the query again, it would return as many rows as attributes you have renamed - 1 if you follow this steps literally.

This query indicates that tblAttribute holds the correct attribute name as modified but tbl_1_4_EN holds the old attribute name, the name of the attribute before it was renamed.

Why is this a problem, if you use the EntityMembersGet method to return data from the Metadata model for the entity mentioned above - i.e., AnEntity, it will return 2 attributes, one of which is incorrect. This means that I cannot fully rely on  EntityMembersGet on the metadata model to return correct information for an extended metadata model -  i.e., a metadata model with additional attributes to control the behavior of a bespoke solution.

Any comments are more than welcome.

Answer 2

Hi M Vega

Don't you have other way that using tbl_1_4_EN ?

I confirm that "old" name is remaining in this table but not in tblAttribute.

I've created a test model and an "Identifier" attribute that i've renamed to "IdentifierRenamed"

But I tried to use entitymembersget on metadata model and i just retrieve only the old attribute name, not the new one

but you mention that when you use entitymembersget you retrieve both attributes : the one with the old name and the one with the new name

did i miss something ?

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 3

Hi Xavier,

Thanks for the reply. I did not word the last paragraph unambiguously. We do get the old name for the renamed attribute, as you have reproduced; this is the incorrect attribute name. The correct attribute name is the 2nd attribute, the one that was not renamed - in your case isDup (but this is irrelevant for the discussion).

The relevant part is that EntityMembersGet returns the wrong, out of date attribute name against the metadata model.

This is troubling for a number of reasons:

1) The MDS Administration website always shows the correct attribute name because it is not making a call to EntityMembersGet on the Metadata model, it does not need to.

2) The key message is that we found this issue/feature extending the Metadata model. Could I ask you to try to visualize/reproduce the following scenario?

i) Add a custom attribute to the metadata model, isVisible (free form/numeric), this attribute is intended to control whether an attribute should be displayed or not by a bespoke solution

ii) Set the value for isVisible for the isDupe attribute in your TestEntity to 0 - assuming this means false

iii) Make a call to EntityMembersGet on your TestModel to get all members for TestEntity

iv) Make a call to EntityMembersGetcall for each attribute in TestEntity on the Metadata model to get the value of isVisible attribute.

v) What happens when you try to get the isVisible value for the renamed attribute? Can you do it? Does it throw an exception?

In a nutshell, you cannot extend the Metadata model as I would expect and I understand you can without sooner or later hitting a very annoying glitch (for which there is a workaround).

I cannot make my mind on whether this is a feature aimed at making the design more flexible for future enhancements - e.g., multi-language support for attribute names. 

Hope this makes sense.

Kind regards

 

Answer 4

Hi M Vega

I don't what the issue is for me, but I cannot even get my custom metadata attributes displayed in my own models/entities, when I create them in Metadata model

maybe it is due to this known issue (about regionalization [my MDS website is in French langage] ) of MDS 2012 and a workaround I found that may be not fully functionnal:

https://connect.microsoft.com/SQLServer/feedback/details/778032/sql-server-2012-mds-cannot-update-attribute-metadata#details

As soon as I could override this blocking issue for testing your example,  I will try to test further

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 5

Hi Xavier,

I think that Microsoft is going to use this feature to support multiple locales for attribute names. It will be very easy to exploit this feature by adding a locale column to the [tbl_1_4_EN] table, although the naming convetion seems to imply that there should be a [tbl_1_4_FR] and [tbl_1_4_FR] table,  and have multiple rows per attribute with attribute names in different languages -  e.g., First Name (EN), Prénom (FR), Nombre (ES) - all referring back to the same attribute name.

It will be great if Microsoft could confirm if this has been left open for additional functionality or it is a known issue that will be corrected in future service packs.

Kind regards,

M

Answer 6

Hi M

Thank you for your answer.

I think that MDS should support multiples locales but the bug I've found is a known issue as my bug request (https://connect.microsoft.com/SQLServer/feedback/details/778032/sql-server-2012-mds-cannot-update-attribute-metadata#details) has been validated by MS team (I hope it will be fixed quickly in a future CU)

this blocking issue is on web UI and prevent user to save/update metadata model changes

I've found a workaround by commenting the regexvalidator inside [MDSWEBSITE]/Admin/Domain.aspx but I think that it's not the only problem to solve to be able to save/update metadata changes

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 7

Hi Xavier,

Hopefully MS will fix the locale issue soon.

For your workaround, have you tried removing the following from the RegEx, ^~`. These are the only French and Spanish (~) specific characters I can find in the RegEx. Interestingly, the circumflex accent, ^, is twice in the RegEx, see ^*.

Just let me know if you can replicate this issue. As I said I would like to know from MS if this is a known issue or has been left open to improve functionality.

Kind regards,

M

Answer 8

Hi M Vega

thanks for your tips

I had maybe a cache issue

it's working now with ValidationExpression="^([\w\d]+[ é' ]?)+$":

 <asp:RegularExpressionValidator ID="regName" CssClass="ErrorMessage" runat="server"
                                                ControlToValidate="txtName" ErrorMessage="Name may not contain special characters."
												ValidationExpression="^([\w\d]+[ é' ]?)+$" Display="Dynamic"
                                                meta:resourcekey="SpecialCharacters"></asp:RegularExpressionValidator>

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".