Got some spare time? :)

I guess you’re currently on a holiday and you’re still checking the web for anything to read or view? Well here’s an overview of the best videos related to Microsoft BI from Microsoft Ignite of last May:

The SQL Server Evolution

Data Science with Microsoft Azure Machine Learning, Microsoft SQL Server and R

Microsoft Azure SQL Data Warehouse Overview

Microsoft SQL Server BI Drill Down

Power BI Overview

What’s Next for Visualizations in Microsoft Office

Azure SQL Data Warehouse: Deep Dive

Power BI for Developers

Scalable BI and Advanced Modeling with Microsoft SQL Server Analysis Services and Power Pivot

The use and misuse of Master Data Services (MDS) in SQL Server

After watching the SQL Hangout (Hangout #27 Master Data Management?), something told me to also shed light on other ways to use and misuse MDS.

Let start with the definition of MDS (by Microsoft):

Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists…The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

So…master data….check! You may immediatly think about a single version of the truth regarding customers, products, etc.
But in practice I found out that thanks to its brilliant (spoiler: a bit slow in 2012, but should be fixed in 2016) Excel add-in, business users can easily upload and maintain fairly static datasets. The web interface is horrible, you really need to get used to the way it’s been arranged. I still have figured out how the versioning-system works btw, but just create a subscription view and then use it as a source for your DWH. Beats building SSIS packages for each and every Excel-sheet the business user wants to ‘upload’.

Business users of my current client are use MDS for:
Master Data (list of branches, clusters, geo-locations, etc.)
Hierarchies (GL account, e.g. Profit & Loss or Balance sheet)
Targets for SSRS reporting (e.g. min/max target values per branch for a certain KPI)
Figures which are uploaded by the business, like:
* Actuals (e.g. illness rates, number of FTE’s, etc.)
* Budgets (e.g. P&L, etc.)
* Forecasts (e.g. P&L, etc.)

MDS wasn’t build for this and it isn’t fast when you want to publish upload or retrieve 295.356 records (via the MDS Excel Add-In).

MDS - Many many records
(Yep I’m Dutch)

But whatever…if it works….it works 🙂

An example of our list of views (MDS subscription views):

Use and misuse of Master Data Services 2012 - MDS

Want to learn more about MDS? Follow Kenneth Nielsen‘s blog: