Primary Use Case
Reference data management
Product hierarchy origination
Org hierarchy origination
- Ease of use
- Excel interface
- Price (free for sql server enterprise license)
- Hierarchy management features
- Security integration with AD
- Very fine grained security
- Easy to build (and extendable) business rules
- Automatic Notification
- 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
Improvements to My Organization
It enables data stewards to directly interact with data on web site or using Excel without building a custom reference data management solution. This includes data stewards getting notification emails automatically when new data is loaded that includes a link to the specific data.
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. Used for end-user controlled data augmentation, reference data management and mastering the organizational product hierarchy.
Room for Improvement
- * Note below reflects the current 2016 version. Previous versions had significant issues the majority of which were resolved with the current version.
- Explorer GUI is still clunky and primitive.
- Needs ability to remove menu options that may not be relevant to data stewards such as Entity Dependencies, Collections or Changesets.
- Need ability to default user groups first screen to specific area such as a hierarchy.
- Bug in the new domain based validation business rule capability where 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 cannot exceed 30 characters and if it does there is no error message - it just won't let you drag levels into the hierarchy.
- Selecting child members within the navigator in a hierarchy result 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).
- Data changes can be saved (depending on the change and where the change is made) without first applying business rules.
- 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. Also requires adding business rule to enforce 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 versioning and not entity and hierarchy 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 has significant scalability and functionality constraints as well as not getting upgraded in either 2014 or 2016 Sql Server releases.)
Use of Solution
Three to five years.
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.
Not yet however our data volumes are low - the largest entity is a few million rows.
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.
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.
It is fairly straightforward as the Excel plug-in makes the initial data load very simple.
We implemented it in-house.
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.
Pricing, Setup Cost and Licensing
None as our in-house staff did the setup. There is no day to day maintenance needed.
Other Solutions Considered
No as this was the only no-cost option with level of sophistication we needed.
- 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 and there is clear direction by Microsoft for improving and supporting the product long term.
- If starting out I would recommend starting with the 2016 version.
Disclosure: I am a real user, and this review is based on my own experience and opinions.