0

How does Master Data Services and Data Quality Services fit into the existing BI stack?

In SSIS there is a Fuzzy Lookup and Fuzzy Grouping component. Therefore I am tring to compare: SSIS v MDS/DQS.

From what I understand MDS/DQS is the preferred solution if you are processing records on a transaction by transaction basis e.g. end users updating individual records. SSIS is the preferred solution if you are processing records as a batch i.e. batch processing. Is that correct?

1 Answer 1

1

All three of these tools have very different functionality.

SSIS is an ETL (i.e. Extract, Transform, Load) tool. Yes, it's for batch processing of data, and also for parallel processing of multiple data flows. In the case of BI, its main job is population of various data warehouse tables. Those might be staging tables, or they might be the actual warehouse tables. It can also be used in plenty of non-BI situations where data needs to be transformed and moved. The Fuzzy Lookup and Fuzzy Grouping components pre-date MDS and DQS, and are fairly limited, but potentially still useful in some scenarios or if there's no intention to adopt DQS.

MDS is a data integration tool. The intention is to use it in situations where an organisation has multiple transactional databases with overlapping data, and there's a need to pull that data together and integrate it. That means setting up rules to determine how to match the data from the different systems. It can also be used to push the centralized data back out to transactional systems to resolve cases where data was different in different systems. It's an attempt to get "one version of the truth" in either a centralized place, or across all systems.

DQS is a data quality tool. It provides functionality to allow developers and data stewards to work together on setting up rules to sort out data quality issues. While these rules might help with data integration issues, DQS is not limited to cleaning those columns which are being used to match data sets. Rules can be set up to replace values in a certain column with another value, for instance - so perhaps there's a common data entry error that gets made in a system. When the issue is noticed, a rule can be set up to always replace that particular string with another string.


SSIS can be used with MDS and DQS, in cases where you need the functionality of more than one of them. Here's a Microsoft tutorial which goes through how they can be used alongside one another.

Of course, you might opt to implement master data functionality yourself - it's not a new concept, MDS is just Microsoft's semi-automated master data offering. For many organizations, the data warehouse (or at least one layer of it) is the store of master data. Likewise, DQS is just a data quality tool, but you might opt to set up your own data quality processes. If you're using the Microsoft stack, then the more you do yourself, the more you'll probably end up using SSIS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.