What is our primary use case?
We use it for analytical and operational enterprise reference data management including management of campaign, product and financial management hierarchies. We currently only use batch interfaces to feed or get data from it. We also use it to maintain source to enterprise data mappings that are fed to ETL via a generic RDM (Reference Data Management) design. We have a variety of different groups of data stewards who use the tool for data management and they get automatically notified when source systems feed new data that needs to have stewardship carried out.
How has it helped my organization?
It enables data stewards to directly interact with data on a web site or Excel without building a custom solution. This includes data stewards getting notification emails automatically when new data is loaded including a link to the specific data that needs to be updated. It also has a built-in single level of approval management that is used where versions of data need to be approved prior to publishing. The end result is more consistent data, more consistent data and fewer data silos.
Reference data management is integrated via using standard automated ETL processes extracting from the published database views. This eliminates the need for one-off, silo'd and uncontrolled data management.
What is most valuable?
- Ease of use
- Excel interface
- Soft delete support
- Sophistication of business rules
- Price (FREE for sql server enterprise license)
- Hierarchy management
- Security integration with AD
- Very fine grained security
- Easy to build (and extendable) business rules
- Automatic Notification
- Integrated boolean business rules
- Change detection
- Integrated workflow
- Staging support
- Publication support
- Ability to propagate from one repository to another
- Ability to share reference data across models
- Integrates as a web part
- Includes both batch staging process and Excel data loading
- Unused area of hierarchies to easily identify members not assigned
What needs improvement?
- Support future - it is unknown how Microsoft will support this tool long term as it now gets very few updates when new versions of SqlServer come out.
- It "can" have a bug where a model becomes corrupted. It still operates but will no longer hard delete (purge function stops working) and can no longer migrate the model to another repository as the create or clone functions stop working. It gives internal FK and duplicate row errors when attempting these functions.
- Functionality shortcoming: Business rules cannot be used to prevent bad data from getting entered. Rules can only apply to data already saved. And even then it requires the data steward to push a validate rules button. If they forget they won't notice (and notifications won't go out) regarding rule violations. In my 30+ years in IT I've never experienced a tool that only allows you to create reactive rules that identify bad data but never prevent it from getting saved. In a large enterprise I would rule this as a show stopper.
- Bottom line: Business rules need the ability to prevent data changes, not just validate saved changes
- Second largest shortcoming: Hierarchy integration views don't support custom attributes. This capability (which would be awesome if it worked right) is completely worthless without supporting custom attributes.
- Explorer GUI is clunky and awkward.
- Tool needs to get more frequent enhancements. For example, since 2016 there has been no enhancement except replacing Silverlight in the 2019 version.
- It needs the ability to publish only a single business rule. Publishing business rules requires publishing all rules and results in requirement for re-validation of all rules. Until that is done the validation status on all affected records is in an "awaiting validation" state which can adversely affect data usage and validity.
- Needs ability to remove menu options that may not be relevant to data stewards such as Entity Dependencies, Collections or Changesets.
- Needs ability to default user group's first screen. For example defaulting to a specific hierarchy display.
- Resolve a bug in the domain based validation business rule feature: the web interface will not allow correcting domain based lookups in the child properties (only works using hierarchy drag/drop or within Excel).
- Hierarchy names should allow exceeding 30 characters.
- Selecting child members within the navigator in a hierarchy results in the GUI just ignoring you... it should allow selecting children in the navigator or at least give an error message. (Note that 2012 version resulted in blank middle panel when this occurred rather than just ignoring you).
- Needs to allow renaming the code and name attributes within the publication views (only allows renaming in the UI).
- Derived hierarchy views should optionally include all attributes from all respective levels, not just the code and name attributes.
- Needs to allow setting the CODE attribute datatype as numeric rather than forced datatype of string (which turns into nvarchar(250) behind the scenes). This is a significant headache as simple sorting on the CODE attribute with numbers causes numbers without leading zeros to not sort correctly and it requires adding business rule to enforce numeric datatype.
- Needs to support setting view datatypes to varchar rather than forcing nvarchar so subsequent extracts do not have to do datatype conversion if not using unicode in your target database.
- Versioning lacks granularity as it only supports model-level versioning.
- The deployment migration GUI tool doesn't support data, only meta-data making the GUI export tool fairly useless. (Including data requires use of command line to support data)
- The deployment migration tools do not support business rules requiring manual maintenance when using multiple environments (e.g. dev/sit/prod).
- Excel plug-in needs to support hierarchies.
- Needs to support email notification to more than one email group/user.
- Staging process is non-intuitive and overly complicated.
- Repository should utilize a prefix or unique schema for each model so that entities and views do not have to be uniquely named across models.
- Needs ability to integrate seamlessly with data quality products. (DQS has some level of integration with MDS but the integration is overly complex and DQS's days are likely numbered).
- Need to be able to do nNotification without an AD user first accessing the model. Once you setup the notification for an AD user or group it should just work without every user logging on at least one time first.
For how long have I used the solution?
We have been using MDS in some capacity for 8 years going back to the 2008R2 version.
What do I think about the stability of the solution?
No issues encountered with stability in terms of up time but one of our models won't send notifications just to specific users which appears to be a bug in the interface between MDS and SqlServer SMTP as MDS logs the notification but SqlServer does not log the email send (success or failure).
There is a bug if you set the CODE or NAME attribute security to read-only for a group that the admin is in, then administration capability disappears for that admin account. This can be reset in the backend repository but is a significant bug. Supposedly it was fixed in CU1 but was not.
Some business rules when attempting to use them cause vague database error that was also supposed to be fixed in SP1 but was not.
Notification stopped working after a couple of years. MDS is sending notification but it no longer communicates with SqlServer smtp even though SqlServer SMTP can be successfully run from other tools such as SqlAgent. It appears to have stopped working after a SqlServer patch.
What do I think about the scalability of the solution?
Not yet however our data volumes are low - the largest entity is a few million rows.
How are customer service and technical support?
Microsoft was incredible during beta testing but customer services hasn't been needed since much earlier versions that had installation bugs. Documentation online is missing some critical installation nuances such as SqlServer security settings needed in order to deploy models.
There is a great amount of information available on the web for this product and its relatively simple so direct technical support has been rarely needed once we were off the beta version.
Which solution did I use previously and why did I switch?
We didn't have a previous solution. Any augmentation of source system data for reporting was done via spreadsheets.
I find this product far superior to a previously used product which was Hyperion MDM, now Oracle MDM. Although Oracle has enhanced their product in recent years since I last used it it was built upon the dying Hyperion database and had no governance built in and no web interface. And the Oracle tool is hundreds of thousands verses this tool which is free. Need I say more?
Note that competing products in the market place are very expensive and will include possibly more sophistication than you need. This tool does everything you will need for simple dimension or reference data management purposes.
How was the initial setup?
It is fairly straightforward as the Excel plug-in makes the initial data load very simple.
What about the implementation team?
We implemented it in-house.
What was our ROI?
There was no cost for the software as it comes with SQL Server BI, and Enterprise editions. The ROI is terrific and it means you can avoid building or purchasing a solution.
What's my experience with pricing, setup cost, and licensing?
None as our in-house staff did the setup. There is no day to day maintenance needed.
Which other solutions did I evaluate?
No as this was the only no-cost option with level of sophistication we needed.
What other advice do I have?
- It's sweet spot is reference data. Attempting to use it for large scale customer data or products may be problematic.
- The tool is integrated into Dynamics AX but there is not clear direction by Microsoft for improving and supporting the product as a stand alone long term. Use it with that knowledge and look for any indication from Microsoft that it will continue to support it or deprecate it.
- If starting out I would not recommend using any version prior to 2016.