SQL Server Master Data Service is the answer for BI reference data

By Chris du Toit

SQL Server Master Data Services (MDS) has been bundled with SQL Server since the 2008R2 version as part of Enterprise Edition and Business Intelligence Edition. Its architecture is beautifully simple but the value it adds is tremendous – especially with reference data for BI systems.

Whenever BI systems require data that doesn’t exist in any source system, and can only be manually managed, there has always been one answer – Microsoft Excel.

Excel provides the platform to structure and gather manual data feeds. Typical feeds encountered are forecast/budget templates, KPI values/targets, mapping data, additional dimension attributes, hierarchy management, sales campaign details, end-dated items, data-driven ETL inputs, user access rights, etc.

Almost everything that doesn’t exist in any source system ends up in Excel. Other solutions I have seen address this problem include Access databases, SharePoint lists, Google Spreadsheets and bespoke applications.

When data needs to be loaded into the data warehouse most of the manual input solutions share the following issues:

1. Security management – Security generally boils down to giving people read/write access to folders on a shared drive. It becomes a little more complicated when requirements advance to row, field or entity level and it can become quite expensive if a bespoke application is used.
2. Bad data entry – This can be invalid data types or in some cases structural changes. Excel is especially vulnerable to this issue.
3. Locked files – If the ETL is architected to read the same Excel file which users can edit there is always the chance of users forgetting to close the file. This can cause the ETL to fail.
4. No multi-user support – When data is accessed directly by means of opening up a file it generally means only one user can do it a time.
5. Project lifecycle pain – Having to maintain development, test and production versions of manual data entry systems isn’t fun. Especially if the data is part of the deployment.
6. No rules validation – Validating that an entry satisfies business rules, doesn’t invalidate other data entries and retains referential integrity isn’t something that is always easy to do.
7. Difficult to change – This is especially true with bespoke applications which require a developer to support any changes.

It is possible to work around these issues with things like password protected input templates, loading from copied files, VBA for data validation, separated input templates and bespoke applications. But it really doesn’t make sense to do all that work when all these issues are already addressed with SQL Server Master Data Services. Plus, MDS lets end-users keep the familiar Excel interface to manage their own data.

1. Security management – MDS makes it possible to apply read/write permissions on the database (model), table (entity), row (member) or field (attribute) level – all of which can be managed through a web browser.
2. Bad data rejected – Invalid data type entries are automatically rejected in MDS and users aren’t allowed to alter column names or types.
3. Locked files – ETL can read data form MDS subscription views without being affected by locking.
4. Multi-user support – Multiple users can edit/view the same MDS data at any time.
5. Project lifecycle pain – MDS has its own deployment mechanism built-in.
6. Rules validation – MDS business rules can be as simple as supplying default values or “if this, then that” all the way up to advanced validation through T-SQL (only in SQL 2016). Rules validation can even notify users by email.
7. Making changes – Adding or making changes to existing structures is very easy and can be managed through a web browser or the Excel add-in.

Using SQL MDS to manage BI reference data is simply a no-brainer, especially if you are already licensed for SQL Server Enterprise or BI Edition. Even if you are running standard edition or a non-SQL Server RDBMS, the list price for SQL Server BI Edition is below US$10,000 which is considerably less than the time and money organisations spend trying to address the issues mentioned above.

If you are interested in deploying SQL MDS feel free to contact us.

Chris du Toit is a Lead Consultant at Stellar.