Master Data is often overlooked
Master Data Management (MDM) should be an integral part of every company’s Business Intelligence Roadmap, but it’s often overlooked. Data Platform, ETL, and BI Tools are extremely important when planning your BI Architecture, but for 95% of our BI clients, Master Data Management has also played a critical part in filling gaps that could otherwise interfere with fragile ETL processes or create labor-intensive maintenance for BI development teams.
Master Data Services (MDS) is an MDM tool available with Microsoft SQL Server that has been DesignMind’s de facto standard for MDM solutions since the release of SQL Server 2012. Now with SQL Server 2016, MDS boasts even better performance and modeling capabilities that are impossible to ignore.
Having helped so many clients get started with MDS, we want to share a few simple use cases that will help you realize whether or not you might have a perfect use case for MDS and didn’t even know it. The key is to identify these early so that you can take advantage of the MDM platform included with SQL Server and show users what they’ll be able to do on their own without help from IT.
Should you make or buy your Master Data solution?
Some organizations have needs that can’t be fully met using MDS out of the box, requiring significant customization and integration efforts. For those organizations, we help with the “make vs. buy” decision, comparing customization efforts vs. other MDM tools. One such tool is Maestro, from Profisee. Maestro runs on top of Master Data Services, and offers additional capabilities and integration options. You can learn more about Profisee Master Data Management here.
When Master Data comes in handy
1. Sourceless Data
Every company has that “spreadsheet” or Quickbase table or Microsoft Access database that contains data which no other source system has. I’m referring to things such as custom data groupings, exclusion lists, and marketing scenarios which are only used for reporting and analysis. While many see this as a problem with no easy solution other than importing volatile excel spreadsheets, we see great use cases for MDS. MDS Entities can be developed which tightly resemble these special lists and data groupings for data stewards to maintain using either a web browser or an Excel plug-in. They can also have lookup fields (i.e. domain-based attributes) removing the possibility of errors in free-text fields. Now with the business getting their UI and data entry needs out of the way, your BI architecture can leverage this MDS data source for downstream analytics in either your ODS, enterprise data warehouse, cubes, or data models.
2. KPIs, Targets, and Quotas
These comparative measures as Stephen Few calls them in the definition of bullet graphs are fundamental to businesses so they can track progress. Surprise for some, these make excellent use cases for MDS. Set up a Sales Quota model and you’ll find that you don’t just have traceability over who makes changes, rollback changes, but you can also have different versions of the model as well. There’s enough flexibility for your end users to dial in their forecasts/quotas on a monthly/quartlery/yearly basis. Combined with domain-based attributes (or lookup fields), your data stewards now have templates which guide data entry and help BI teams control the granularity of these measures to match reporting databases downstream. A huge benefit to your BI architecture.
If this data is highly sensitive and must be controlled, not to worry. MDS security can be set to control permissions on who can view/update/version not only the model, but individual quotas on rows or for different subject matters such as Finance, Manufacturing, and Operations.
3. Custom Rollups
Otherwise known as user-defined hierarchies, are another well-known use case for Master Data. Think about how your Sales Regions are defined. How Products are categorized. Have you seen how those groupings are done? Maybe they’re defined in your source system, but what if you need a new category? We’re willing to bet there’s a use case where custom groupings exist for certain Geographies or Departments in your company based on how people and processes work. In these situations, many of our clients don’t want to wait for IT to have to update tables, run ETL, do UAT, and a Production push just to move Office A from Region X to Region Y. IT or BI teams can probably maintain this for end users using other means, but 100% of our clients have confirmed on prior projects that they need to maintain this data themselves. When our clients need this capability quickly for reporting purposes, we know we have a Master Data entity on our hands.
Get A Master Data Model
Maintaining master data on an enterprise scale can be cumbersome. Multiple spreadsheets, multiple file locations, multiple data stewards, multiple versions, multiple managers; it’s easy to see how data integrity can become an issue. However, a solution might be found in MDS, but you have to know how to model entities, secure those entities, and the options available to validate and approve changes to your master data automatically when they happen.
With an MDS model, you can enable multiple data stewards, multiple versions, all while updating a centralized model that is auditable and flexible for the rest of your BI Architecture to take advantage of in downstream systems. Data stewards can maintain this master data either through the web or with Excel. Their choice.
Do you have questions on how to get started? Contact us in San Francisco or schedule some training time with us. We’ll help you get started with MDS today.
Angel Abundez is VP, Solutions Architecture. He specializes in Microsoft SQL Server BI tools, Power BI, SharePoint, and ASP.NET.