MDM Solutions

Christopher Brooks

MDM Solutions

Hi,

I am working for a medium sized company that is interested in managing data effectively and building up MDM within the business. 

 

However, the company relies more than any I've ever seen on siloed Excel based data. I've tried to roll out Master Data Solutions as a way of enforcing some governance over the data but due to technical issues with Master Data Solutions and the license that we have/technical constraints meaning that we cannot patch these issues...this tool has not been successful.

 

What are your experiences of developing a technical solution- bringing together Excel based data in disparate and siloed environments- together enacting both Data Quality rules, Data Governance Standards and centralising data so that it can be analysed by e.g. a BI team?

 

The knowledge in this forum is exceptional so please share it if you can.

 

Thank you!

Merrill Albert

RE: MDM Solutions
(in response to Christopher Brooks)

Wow.  So you can't pull them away from their Excel spreadsheets?  Are the spreadsheets at least locked down so they can't change the data?  It's so easy to accidentally change data in Excel without even thinking.

I'm not familiar with the tool you mention, but some of us have done MDM without MDM tools.  The key thing is understanding the business rules (business, not technical).  If the rules aren't being used and implemented correctly, they'll get bad results.

I've worked with teams who were very attached to their Excel spreadsheets.  I spent time with them understanding what they did and it was really sad.  They didn't have all the data they needed and they wasted a lot of time mashing together Excel spreadsheets to create other Excel spreadsheets.  I don't know how they had any confidence in having the right data set to work from.  It was key to show them that we were responsible for getting that quality level for them and then they could just start using the right data.  They were skeptical at first, but came around.

Christopher Brooks

RE: MDM Solutions
(in response to Merrill Albert)

Hi,

Yeah its proving a challenge to get them off of Excel. Strangely enough it isnt the Analysts who are so opposed but rather the senior management who see potential loss of control through the opening up of siloed data (I've had someone tell me this is why they dont want to move away from Excel). 

 

I've embedded validation steps in several key Excel reports to prevent random data changes etc. But this doesnt provide the reassurance that is really required and in my view is a sticky plaster at best. 

 

I've been concerned about going too far down the path of fixing individual Excel Sheets also because I worry that to senior management they will see the sticky plaster and accept it as the long term solution. I find in Data Governance there is always a rush to sign off and move on from snr management- have you found the same? What did you do about it?

 

I completely agree about communicating with teams to get them off of Excel but I am struggling to identify an alternative. My budget is zero and due to a couple of hugely overdue IT projects coming in there simply isnt the technical support to enact a technical solution.

What do you think?

 

Thanks

Jeff Albro

RE: MDM Solutions
(in response to Christopher Brooks)

If you are in an environment where SharePoint is used and supported, I'd consider SharePoint lists.  There is full history, you can enforce a schema, and you can enforce uniqueness on an ID column.  Users can read the data into Excel using Power Query.

https://support.office.com/en-us/article/Connect-to-a-SharePoint-List-Power-Query-3226f248-f8b3-4777-82ce-b4ed04dedaaf

-Jeff

william burkett

RE: MDM Solutions
(in response to Christopher Brooks)

MS Excel and Access are extremely compatible so it might be possible to build an Access-based tool that can "connect" and reconcile the data across the spreadsheets.  This would save you from having to worry about proprietary tools, and ubiquity of these MS Office apps means that almost everyone has them.

Setting aside the tooling question, though, is the question of MDM processes: How are YOU going to do MDM given the restrictions of your environment?  Do you know what your MDM "configuration items" are?  (i.e., the conceptual linchpins around which MDM revolves.)

Anthony J Algmin

RE: MDM Solutions
(in response to william burkett)

I feel I must chime in here, as I often tell my classes "Microsoft Access is ALWAYS a mistake."

Please no Microsoft Access -- it will only lead to pain down the road. It's like putting a big engine in a car without upgrading the drivetrain or suspension: you'll never be able to control it.

Because Access databases are so easy to use (and duplicate), they will inevitably buckle trying to support anything that looks like MDM. You'd be much better off rolling your own PostgreSQL database, even with a rudimentary data model, to do basic reconciliation and MDM functions. Get master data out of spreadsheets. They are a dirty analysis tool--super-useful, but this use case is not suited to spreadsheets.

Instead, create and populate a database with the data you want to manage centrally, and then connect your spreadsheets to it to extract information for people's analysis. This need not be a huge, expensive effort. But please, don't pour gasoline on your fire with Microsoft Access.

 

Anthony J. Algmin
[login to unmask email]
Go Make an Impact!

Visit Algmin Data Leadership today!

william burkett

RE: MDM Solutions
(in response to Anthony J Algmin)

You're mileage and experience differs from mine, Anthony.  I've had a lot of success and found a lot of value in/from MS Access.  Admittedly, this experience is all on small-scale projects; Access isn't suited for heavy-weight database work.  But if spreadsheets are your starting point, how "heavy" can the work be?  

But tooling is beside the point, IMO: developing MDM practices based on the circumstances or your environment is the long pole in the tent.  Tools won't solve your MDM problems.

Anthony J Algmin

RE: MDM Solutions
(in response to william burkett)

William, I totally agree that the tools aren't the point. I'm a big believer in starting small and working with what you have already, so to that end we are on the same page.

Personally, I've had far too many experiences evolving smaller-scale solutions that have outgrown their effectiveness. That's the slippery-slope if the early solutions are not upgraded to the larger demands when that time comes.

MDM is one area that should have greater ambitions from the start, otherwise it probably isn't worth the effort at all. So after dealing with one-too-many short-term solutions that became long-term disasters, I can't advocate implementing the uncontrollable power of Access, even in the early days.

I can advocate a healthy debate on it, though! I'm sure your experiences are the exceptions to my rule.

Anthony J. Algmin
[login to unmask email]
Go Make an Impact!

Visit Algmin Data Leadership today!

William McKnight

RE: MDM Solutions
(in response to Anthony J Algmin)

Hello. It's very difficult to get Excel (or Access) to be used successfully in enterprise production environments, except for personal use. I agree with Anthony. I would reengineer into a true database. Your challenge in MDM is much broader than tools. It's people and direction. You probably need to work on data as a shared asset, and show how that approach makes for the most returns, versus the siloed approach they are doing now. You can do MDM without a tool but that's doing it the hard way. Maybe it's a matter of crawl, walk, run for you, but keep the tool option on the table even if you do add some master data value some other way. By the way, Profisee is a nice next step for those who have hit walls with MDS.