Russell Ackoff and Systems Thinking

Good post with YouTube links to Russell Ackoff speaking.

Troux and Enterprise Information Management

Over the last 3 years I have been using the EA tool Troux .

My use of this tool has mainly been focused on ‘Business Architecture’ related activities such as creating a business capability model/project investment analysis and ‘Enterprise Information Management (EIM)’.

This is the first in a series of blogs that will focus on my experience in the latter – looking at areas including:

• Business Information Modelling

• Managing information assets

• Creation of an enterprise business glossary

• BI strategy and ‘road mapping’

• Data governance and stewardship

• And most importantly of all, ensuring EIM aligns with the strategies, goal and objectives of the enterprise.

I intend to show how Troux can assist in the areas above and provide practical ‘tips and tricks’ based on my experiences.

Business Intelligence, Big Data and Analytics

I’ve just attended a two day seminar – Business Intelligence, Big Data and Analytics given by Rick F. van der Lans.

He combines business awareness with a deep technical background – whilst presenting in a relaxed, approachable manner.

Perhaps confirmation bias on my part but his views on the need for business agility leading to the technical requirement for data visualisation really resonated with me.

If you get the chance I would definitely recommend attending any event where he is presenting or reading the articles on his website.

Database debunkings – up and running again

It’s great to see this up and running again after a number of years being ‘on hold’.

The purpose of Database Debunkings is to:

“To correct misconceptions about and educate on the practical implications of data fundamentals–concepts, principles and methods–that receive little, incorrect, or no coverage. And to make foundation knowledge accessible to the thinking database professional and user, regardless of the DBMS used”

Definitely worth a regular visit.

Defining the financial industry

As discussed in a previous post – What do you mean? – having a common set of key business terms and definitions is so beneficial to an enterprise and is at the core of good data management.

So it is good to see people in the financial industry discussing the importance of having a common language both within an individual organisation and across a sector. Andy Haldane – Bank of England’s executive director for financial stability – told the Securities Industry and Financial Market Association in New York yesterday that there is often no common way of calculating risks and liabilities. A few quotes from the article:

“the world’s banks should develop a common language, like the barcodes used in international trade, so that financial risks can be mapped and understood”

“Most financial firms have competing in-house languages, with information systems siloed by business line. Across firms, it is even less likely that information systems have a common mother tongue,”

“Without a common language, Haldane said, trying to map the complex networks between different financial firms and their clients and customers is a ‘high-dimension puzzle’, which hampered the clean-up after the collapse of Lehman Brothers.”

So not having a common set of definitions has hampered the clean-up and makes it more difficult to calculate risks/liabilities. Therefore, can it also be argued that not having this might be one of the root causes of the current financial crisis – or at least made it more difficult to identify/prevent?

One of the excuses often put forward for not having appropriate data management processes is centred around cost. But, following on from this speech, perhaps organisations need to start asking – “can we afford not to do this”.

What is a conceptual, logical and physical model?

David Hay has written an excellent short presentation that describes the differences between these and places them within the context of the Zachman Framework.

He makes a greate point – how can we expect others to think about language/definitions when those in the data management community often use these terms in such an inconsistent fashion.

The presentation can be seen in the following – Introduction to Data Modeling

David mentioned this presentation in the following – LinkedIn discussion

Open data model

The Open Data Model site was launched recently.

It’s aim is to:

“bring together data modelers and subject matter experts both from IT and business. The purpose is to reach consensus on the best way to represent any given set of facts, by ensuring those facts are truly understood.”

Effectively to build up a set of conceptual models for different domains which can then be used as starting points in modelling efforts.

(As far as I am aware) It has only been running for a few weeks and has already built up some really useful resources.

Definitely worth taking a look at.

Note you have to register to access most parts of the site.

Building the Agile Database – book review

How do you create enterprise quality databases, that can be built quickly, effectively maintained/changed over their often long lifetimes and therefore achieve real and sustainable business value – within the constraints of an agile project? A big ask!

In Building the Agile Database: How to Build a Successful Application Using Agile Without Sacrificing Data Management’ Larry Burns looks to answer this question – building on articles he has published in this area and which I have previously referenced – see Agile database development 101.

In many projects there is often a contention between developers (tend to short term, project focused) and data professionals (tend to be longer term, enterprise focused), and while there is an abundance of material on agile software development – there is little to none relating to data management. This book looks at the issue of how agile development and data management can coexist – ‘agile data management’. It offers lots of practical advice on many topics including:

• Considering how a project/application fits within the overall enterprise.
• Logical and physical modelling – taking a ‘just enough’ approach without losing quality.
• Database design and development in an ‘agile way’.
• Taking into account all stakeholders within the enterprise.

Key take aways

• When designing databases teams need to think about PRISM – performance, reusability, integrity, security and maintainability.
• Achieve an appropriate balance of getting functionality to users quickly and thinking about the long term best interests of the organisation ie do not add unsustainable enterprise debt.
• Automate database development – from ‘physicalising’ logical models, testing, to deployment.
• Think about the maintenance and quality of the database and the data within it. Not just for initial development but for the overall life span of the database. Data quality issues are often much harder and more costly to fix than software issues. A truly agile approach needs to address this point. (the author makes the suggestion that developers should have to maintain application for at least a year after deployment!)
• Decouple databases from their applications by using a ‘virtual data layer’(VDL) (I would use the term DB API)using technology such as views/sprocs. This makes refactoring/changes less costly and ultimately more agile for both individual projects and the enterprise as a whole.
• The use of a VDL allows application specific views of the data – whilst allowing base schema to be based on a fully normalised logical model’.

Some enhancements?
There are a couple of areas in which I would have liked to have seen more detail including:

• Modelling data at an enterprise level and how it relates to projects.
• Database unit testing and automating techniques

Many developers perceive data management practices to be an unnecessary bottleneck, are often not aware that a continuous integration approach can be taken when developing databases and how using a ‘VDL’ can help decouple applications from databases therefore allowing more effective refactoring/changes without the need for ‘scrap and rework’. Many data professionals perceive agile as meaning little to no analysis/design and not paying appropriate attention to the strategic needs of the enterprise.

Whilst the book does address these misconceptions, I think it would have been the ‘icing on the cake’ to have included a more detailed end to end case study (especially the areas outlined above) – including enterprise models, code, scripts and tests – to clearly show the advantages this approach brings and to show how ‘both camps’ can learn from each other.

Note this is not meant to be a criticism of the book. In its 270 odd pages the author covers a lot of ground and I realise that at some point a line has to be drawn on what is/is not included. This is a good book and I would recommend all involved in projects to read it.

But, I would have liked to have seen them covered in a more detail. Ideally with practical examples and ‘fully integrated’ with the other areas covered in the case study.

Final note
To sign off I will leave you with a quote from the book – perhaps the key point for ALL involved in projects to remember:

“Both application developers and data managers need to understand that it’s not their money that is being spent and that, personal interests aside, the important goal is to satisfy both the short-term and long-term needs of the business.”

Data modelling standards

How to Draw an Architectural Data Model in UML – was published recently by David Hay. It lists a set of guidelines to follow when using UML notation for data modelling.

For those using ER/Barker notation, he also published a set of guidelines a number of years ago – see Data Model Quality: Where Good Data Begins.

Both are useful resources and worth taking look at. Especially if you are thinking about creating modelling standards within your organisation.

Tracking SQL schema changes

In – Roll your own lightweight SQL server source control – Ryan McCauley has provided a SQL sproc that can be used for MS SQL databases to poll for schema changes – with any changes stored in a central table in master db.

An interesting approach and worth taking a look at. It contrasts to one that I have outlined previously to achieve a similar outcome (see Automatically tracking database schema changes which uses DDL event triggers and stores changes in a table – dbo.EventLog – in each individual database).

Advantages to his approach include:

• Simplicity. One sproc is installed in the master database and that’s it. It can then check all databases on the SQL server instance – useful in a ‘legacy’ situation especially if a large number of databases needs to be managed.
• All changes are in one table making it easier to query/report on.

Disadvantages include:

• It does not identify changes in all types of SQL objects. It uses sys.objects.object_definition to check for changes so objects such as SQL base tables are not included. Though it would be possible to extend the routine to include further checks on other system tables eg INFORMATION_SCHEMA.COLUMNS).
• It polls for changes. There is a chance that a change could occur between polling periods and therefore not be identified (the chance of this depends on the polling interval used and the rate of schema change on the SQL server).

With many teams building their own way for tracking schema changes, it does beg the question, why this functionality isn’t included as standard in MS SQL?