none
VersionMember_ID no longer in subscription views in SQL 2012 MDS

    Frage

  • WTF did Microsoft do with the subscription views in SQL 2012 MDS? What WAS called the [VersionMember_ID] in a subscription view is now simply [ID].

    Here's my issue; I have hundreds of subscription views that are consumed by other down-stream processes and they all rely on the [VersionMember_ID] field in the views. Now that the database has been upgraded to MDS 2012, those same subscription views expose a column named [ID]. This breaks all my down-stream processes.

    I consider this an EPIC FAILURE of the design team of MDS. They were kind enough to keep the original batch based staging tables for backward compatibility concerns, but to go an REMOVE a column from a subscription view is ABSOLUTELY UNEXCUSABLE.

    I mean, really, how hard would it be to expose the [ID] column TWICE in every subscription view, once as [ID] and once as [VersionMemver_ID]. If you then wanted to use the view in backward compatibility mode, you could use the [VersonMember_ID] column. If you are going strictly with MDS 2012, use the [ID] column.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Donnerstag, 4. April 2013 15:26

Antworten

  • Hi,

    http://connect.microsoft.com/SQLServer/feedback/details/726447/versionmember-id-no-longer-exists-in-subscription-views


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Freitag, 12. April 2013 01:28
  • Todd,

    We have tracked down the history of this issue.

    The original connect feedback is at SQL2012 RC0. Neither VersionMember_ID or ID was in the subscription view.

    https://connectadmin/Feedback/ConnectTab.aspx?FeedbackID=726447

    The internal bug id is 885844 VersionMember_ID no longer Exists in Subscription Views

    The fix is shipped in CU1

    https://support.microsoft.com/kb/2695802?wa=wsignin1.0

    Although, the KB itself is not linked in CU kb.

    But as you discovered, the fix is add ID and MUID into subscription view, as it is described in KB2695802 "More Info" section.

    At this moment, the quick fix maybe alter the [mdm].[udpCreateAttributeViews] manually and regenerate all the views

    1. Search for "T.ID AS ID,"
    2. Add a line below it "T.ID AS VersionMember_ID,"

    I admit the KB 2695802 is not proper fixed, but considering it was done 3 years ago, I am not sure it can meet the bar to fix it again. We will discuss internally and get back to you.
    If you can point me to any other connects, forum threads or customer feedbacks that facing the same issue, it would help for us to make the decision. 

    Dienstag, 20. Januar 2015 22:46
    Moderator
  • Vincent:

    Thank you for your answer.

    Of the two links you supplied, the first gives an error and the second is still "Waiting for support.microsoft..." Also down is the SQL Connect site (going on 12 hours plus now). Are they connected?

    The fact still remains that in the time frame immediately after the release of SQL 2012, this was a MAJOR issue for my company and our client. One of us ended up paying for the entire code review to switch VersionMemeber_ID to ID in a large Master Data Services implementation. This could have been avoided, in my opinion, if the product had been regression tested against existing implementation in a lab. Couple that with the fact that the KB was not included in the CU (did I read that right?)...

    Oh, and a fresh install of SQL 2014 does NOT include "VersionMember_ID" nor have I seen any mention of it being deprecated in TechNet forums, Books On Line, etc.

    I think we should close this thread as we're not getting anywhere but further down the rabbit hole.

    However, if any representative from Microsoft would like direct input on my opinions of SQL Connect, with cited examples, please fee free to contact me directly: todd chitt at gee mail dot com.


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Mittwoch, 21. Januar 2015 01:13

Alle Antworten

  • Hi Todd,

    Thank you for your question.  I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Elvis Long
    TechNet Community Support

    Donnerstag, 11. April 2013 03:17
    Moderator
  • Hi,

    It mentioned that the fix already in place for SQL 2012 CU1: http://support.microsoft.com/kb/2679368

    Please try it.


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Donnerstag, 11. April 2013 03:27
  • Thanks, Christian, for the feedback, but I clicked on the link. The article is rather long so I did a search for the phrase "VersionMember_ID" and found nothing. Searched for "Master" and found one note about Master Data Services not supported on a cluster.

    Can you supply the VSTS Bug Number or the specific KB article number?

    BTW, We are running SQL Server 2012 version 11.0.3000


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Donnerstag, 11. April 2013 12:14
  • Hi,

    http://connect.microsoft.com/SQLServer/feedback/details/726447/versionmember-id-no-longer-exists-in-subscription-views


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Freitag, 12. April 2013 01:28
  • OK, That link bring me to a CONNECT site that reports this issue. Of Microsoft's two post links regarding the matter, one doesn't work, and the other is the same as you supplied.

    This time I read through ALL the article titles and NONE of them talked about the VersionMember_ID issue. What's more, the sections at the bottom are broken out by SQL component (SSIS, SSRS,etc.) There is no seciton for MDS.

    How do i know Microsoft fixed this bug in the CU if it isn't listed? I hate having to 'just install the update" if it's not actually there!

    Again, I'm running Version 11.0.3000, and the CU lists Version 11.0.2316. Seems I already have a higher version than offered by the CU ?!?


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Freitag, 12. April 2013 02:08
  • Message to Ed Price, MSFT: I have "un-marked as answers" some of the replies in this discussion. Please make sure you know the subject matter before marking posts as answers.

    I regularly scan any and all replies to my inquiries and if they do in fact answer my issue, I mark them as such. Falsely marking posts as answers devalues all the other replies that are true answers.


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Montag, 19. Januar 2015 13:07
  • I agree that this is a bug. 

    But this might be a good time to introduce a layer of indirection between the MDS subscription views and your downstream systems.  There are lots of reasons this is handy.  For instance you can filter out invalid rows, suppress attributes that shouldn't be visible to downstream systems, handle name changes, etc.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Montag, 19. Januar 2015 17:04
    Moderator
  • AND, it's a bug that's NOT going to be fixed, seeing as 2008R2 is a thing of the past.

    A little attention to detail on the part of the product designers, and all they had to do was add a calculated column in the Subscription View called "VersionMember_ID" that returned the same data from the new [ID] column could have saved me (and I presume others) tons of code re-writes.

    Come on, Microsoft, I expect better from you!


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Montag, 19. Januar 2015 17:10
  • I agree that this is a bug. 

    But this might be a good time to introduce a layer of indirection between the MDS subscription views and your downstream systems.  There are lots of reasons this is handy.  For instance you can filter out invalid rows, suppress attributes that shouldn't be visible to downstream systems, handle name changes, etc.

    David


    David http://blogs.msdn.com/b/dbrowne/

    If it's not that Connect bug, did you submit another one?

    If the issue is a bug, then the answer is to submit a bug request, and it gets tracked over there.

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

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


    Dienstag, 20. Januar 2015 04:51
    Besitzer
  • Ed: Don't take this personally, but Connect is useless, IMHO. When this was first noticed almost two years ago, that was one of the first places I checked. And I did so by searching for "VersionMember_ID", "VersionMember", "Version Member" and just about any other combination I could think of and found nothing. So I submitted a BUG there. I later found a Connect item which reported the exact same thing. Why did the Search not find it? Next: I have submitted numerous bugs and suggestions on the Connect site. I have one that has been out there for five (5, cinco) years AND HAS YET TO BE COMMENTED ON BY ANYONE MICROSOFT TO ACKNOWLEDGE. Often times, I see my posts closed with the comment that it is a duplicate. OK, WHICH ONE is it a duplicate of? I'd like to know so I can track THAT one instead! Shall I go on? I see status's of "To be fixed in the next release" yet the bug was reported back in 2008 R2, and I'm running 2014 and it's still not fixed. How many more MAJOR releases do I need to wait? Often times I get no notification when my posts have been commented on, replied to, or have other activity.

    Right now the Connect site is down. The instructions say to "click on the Help link at the top of the page to report the issue..." But clicking on the Help link does nothing except bring me to another page that is experiencing errors.

    Compare that to the Excel Power BI Preview and Power BI Dashboard Designer teams. They have a feedback icon right on the web site and after submitting some, I have gotten responses via email WITHIN 24 hours from someone on the Product team thanking me for the suggestion. I submitted a bug to that product and have been trading emails with a Product Manager regarding its resolution.

    In my opinion, the SQL development team could take a lesson from the Power BI team regarding customer satisfaction. I have really lost faith with the Connect site. Sorry.

    Back to your original question: Yes, there are TWO bugs submitted for this issue. One by myself.


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.


    • Bearbeitet Todd C Dienstag, 20. Januar 2015 13:00
    Dienstag, 20. Januar 2015 12:55
  • Todd,

    We have tracked down the history of this issue.

    The original connect feedback is at SQL2012 RC0. Neither VersionMember_ID or ID was in the subscription view.

    https://connectadmin/Feedback/ConnectTab.aspx?FeedbackID=726447

    The internal bug id is 885844 VersionMember_ID no longer Exists in Subscription Views

    The fix is shipped in CU1

    https://support.microsoft.com/kb/2695802?wa=wsignin1.0

    Although, the KB itself is not linked in CU kb.

    But as you discovered, the fix is add ID and MUID into subscription view, as it is described in KB2695802 "More Info" section.

    At this moment, the quick fix maybe alter the [mdm].[udpCreateAttributeViews] manually and regenerate all the views

    1. Search for "T.ID AS ID,"
    2. Add a line below it "T.ID AS VersionMember_ID,"

    I admit the KB 2695802 is not proper fixed, but considering it was done 3 years ago, I am not sure it can meet the bar to fix it again. We will discuss internally and get back to you.
    If you can point me to any other connects, forum threads or customer feedbacks that facing the same issue, it would help for us to make the decision. 

    Dienstag, 20. Januar 2015 22:46
    Moderator
  • Vincent:

    Thank you for your answer.

    Of the two links you supplied, the first gives an error and the second is still "Waiting for support.microsoft..." Also down is the SQL Connect site (going on 12 hours plus now). Are they connected?

    The fact still remains that in the time frame immediately after the release of SQL 2012, this was a MAJOR issue for my company and our client. One of us ended up paying for the entire code review to switch VersionMemeber_ID to ID in a large Master Data Services implementation. This could have been avoided, in my opinion, if the product had been regression tested against existing implementation in a lab. Couple that with the fact that the KB was not included in the CU (did I read that right?)...

    Oh, and a fresh install of SQL 2014 does NOT include "VersionMember_ID" nor have I seen any mention of it being deprecated in TechNet forums, Books On Line, etc.

    I think we should close this thread as we're not getting anywhere but further down the rabbit hole.

    However, if any representative from Microsoft would like direct input on my opinions of SQL Connect, with cited examples, please fee free to contact me directly: todd chitt at gee mail dot com.


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Mittwoch, 21. Januar 2015 01:13
  • Vincent:

    Thank you for your answer.

    Of the two links you supplied, the first gives an error and the second is still "Waiting for support.microsoft..." Also down is the SQL Connect site (going on 12 hours plus now). Are they connected?

    The fact still remains that in the time frame immediately after the release of SQL 2012, this was a MAJOR issue for my company and our client. One of us ended up paying for the entire code review to switch VersionMemeber_ID to ID in a large Master Data Services implementation. This could have been avoided, in my opinion, if the product had been regression tested against existing implementation in a lab. Couple that with the fact that the KB was not included in the CU (did I read that right?)...

    Oh, and a fresh install of SQL 2014 does NOT include "VersionMember_ID" nor have I seen any mention of it being deprecated in TechNet forums, Books On Line, etc.

    I think we should close this thread as we're not getting anywhere but further down the rabbit hole.

    However, if any representative from Microsoft would like direct input on my opinions of SQL Connect, with cited examples, please fee free to contact me directly: todd chitt at gee mail dot com.


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.


    Great! This input has been given to the Microsoft teams! Thanks!

    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

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

    Montag, 26. Januar 2015 20:51
    Besitzer