By: Koen Verbeeck | Comments | Related: > Master Data Services
Problem
We hosted a webcast with the subject What's New for Master Data Services 2016. There were a couple of questions for which we didn't have time left to answer, so this tip takes the opportunity to answer those questions. Below are the questions, along with their answers. If you still have questions left, you can submit them in the comments section. You can watch a recording of the webcast here.
Solution
Q: What is your new favorite feature?
As explained in the webcast at the end, I personally like the member revision history a lot. It allows you to have a fine grained view on all the changes that happened for the members of an entity. It can server as an auditing tool as well. Since you can build SCD Type 2 views on top of the data, you can also use it as a reliable source for a data warehouse. Just remember its possible transaction history gets cleaned out by the maintenance jobs, so you better check those settings.
Q: If you are new to MDS, how would you suggest getting started?
Personally I started with the book Master Data Services 2012 (2nd edition). You can find a review here. It’s not the best book ever written (especially the eBook has some layout issues), but it gives you a good foundation of how MDS works. Once you have a grasp of the basics, you can read the MDS articles here at MSSQLTips.com to fill in the gaps and learn more about the new features of MDS 2016. The MDS team also published two TechNet Virtual Labs to get you started with MDS 2016: Developing a SQL Server 2016 Master Data Services solution and Exploring What's New in SQL Server 2016 Master Data Services.
Q: When should you use the browser vs. Excel?
In my opinion, the browser works best for everything related to administrative tasks, such as creating entities, setting up models, defining security and so on. You can do some administration through the Excel add-in, but not all options and properties are present.
For data manipulation, the Excel add-in is far superior compared with the browser. The browser uses outdated Silverlight and you can only edit one singly row at a time. In Excel, you can easily copy paste hundreds or thousands of rows into an entity and publish it to the server with a single click.
Q: Can you rollback to a specific revision and exclude the intermediate data?
You can rollback to a specific revision. Just select it in the history view and choose “Revert Member”. However, this will create a new version of the member, with the same values as the member you just reverted. All the other history is kept. Let’s take a look behind the scenes to see what happens. I created a single member and updated it a few times. In each update, I changed the location of Customer TestA.
Let’s suppose I want to rollback the member TestA to state where the location was Brussels. When I do this, a new current row is created with the value Brussels.
As you can see in the SCD Type 2 view, all the other history rows are kept. The entity truly behaves as a slowly changing dimension of Type 2.
Q: Besides the purge functionality, have you run into any other bugs? How have you worked around them?
The Explorer in the browser can behave a little funky sometimes. Sometimes rows disappear when you hover over them with the mouse. The work around is using Excel.
I have also experienced issues with permissions not being set property after installing MDS. For example, when I created the website, it wouldn’t launch because the MDS Service Pool user wasn’t given permission on the web.config file. I had to manually set the permissions on that file. You can read more about it here. It’s possible these permission issues are solved in a SQL Server 2016 cumulative update.
I also experienced an issue with a business rule. I tried to apply it, but got a database error instead. The logs showed that the MDS user didn’t have the VIEW SERVER STATE permission assigned. This was needed to check if there were already stored procedures running during the validation process (some system views are used behind the scenes). You can find more info about this problem in the following blog post.
During the webcast, several errors started popping up when I tried to use change sets. After a while, the whole Excel add-in became unstable. I’m currently working with the MDS support team to find out what the issue is. Note that this is an exception. I have done the same demos dozens of times without a single issue.
One final warning: with SQL Server 2016, Windows Update is able to install security updates. If you do not configure Windows Update correctly on your machine, it's possible such an update is running in the background. If the version number of the database is incremented, you have to run the database upgrade wizard in the MDS Configuration Manager. If you don't, MDS stops working altogether. So keep an eye on those updates!
Next Steps
- If you watched the webcast or the recording and you still have questions, you can always ask them in the comments section below. If you asked a question during the webinar and it is not featured in this tip, it's possible the question wasn't clear to me. Please use the comment section to clarify.
- Take a look at the MSSQLTips.com website to see which webcasts are planned: Free SQL Server Webcasts, Videos and Webinars.
- For more SQL Server 2016, read these other SQL Server 2016 Tips.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
