Topics of Interest
Vision of the Future
What,Why,How & When
This purpose of this page is to provide definitions of some important Topics of current Interest, such as Master Data Management.
Agile Database Techniques
Agile Development is an approach that emphases close collaboration with End-Users, and
incremental delivery on short time-scale, typically about six weeks.
The recommended reference here is a book entitled “Agile Database Techniques” by Scott Ambler,
a highly-respected writer in the general field of Agile Development.
A Database Administrator or ‘DBA’ is responsible for planning, installing and maintaining Databases.
Being a DBA, particularly for production Databases, can be a very challenging and rewarding role,
but it is not for the faint-hearted.
The current trend is towards ‘Point and Click’.
This means that many functions that used to require a DBA to learn complex command-line
instructions have now been deskilled.
This trend has been accelerated by Microsoft with SQL Server where they have exploited
their expertise in Windows Interfaces.
Therefore, a Microsoft SQL Server DBA can look after more Databases than an Oracle DBA.
This means that it is important for a DBA to acquire skills in SQL Server’s T-SQL and Oracle’s PL/SQL.
The move in the marketplace is away from Oracle to SQL Server.
This is especially encouraged by the Global credit crunch, where Oracle might lose out to SQL Server
on grounds of cost
Being able to produce Reports from Databases is a facility that justifies the existence of many Database.
Users frequently have flexible Report Requirements which change from time to time.
A very powerful technique to meet these Requirements is the approach of building Data Marts.
These can be informally described as ‘throwing all the data into a huge basket and providing flexible
Data Marts usually have a clearly defined scope and a clearly defined User community.
Data Warehouses, in contrast, are intended to be ‘a solution to everybody’s Report Requirements’.
In other words, Data Marts have a smaller scope and are easier to cost-justify.
Some examples are shown on the Database Answers Web Site.
Data Modelling is a core discipline that provides a solid foundation for Data Analysis and
comprehensive Data Management.
The market leader in Data Modelling Tools is ERWin from Computer Associates.
My favourite is Dezign from Datanamic.
Both of these can generate SQL Scripts to create Tables for a wide variety of Databases,
including Oracle and SQL Server, IBM’s DB2, mySQL and so on.
Data Modelling therefore offers an excellent path away from vendor-specific thinking to a
higher level of abstraction.
A Tutorial on Data Modelling is available on the Database Answers Web Site.
It is also available in Portuguese and Spanish
During the Data Analysis Stage, Data Modelling can be very effective as a vehicle for establishing
communication with a range of interested parties, including End-Users, Managers, Developers and so on.
During Data Migration, Data Modelling is essential because it provides the details necessary to define the
field-level Mapping from Sources to Targets.
For Data Architecture, Modelling can be used to create simple Architecture diagrams, with easy expansion to
the underling Entities.
From time to time, efforts have been made to extend the scope of Modelling techniques to include more of the
System Development Lifecycle.
These have met with mixed success. Therefore, although Data Modelling remains a powerful and widely-used technique,
Systems Modelling is not common practice except in the area of Object-Orientated Design, where the Rational Unified Process (RUP) is common practice.
I think of Data Services as Web Services which are specifically designed to move data around a Service-Oriented Architecture (SOA).
There is a Wikipedia definition which is full of intriguing hints,
and one of the links which is worth following up is to an interesting page from Laura Gibbons, who is a Senior Business Intelligence
Solutions Architect, and self-titled ‘Sassy Data Chic’.
Data Warehouses, in contrast to Data Marts, are intended to be ‘a solution to everybody’s Report Requirements’.
They require a corporatew commitment and budget and may not have any committed Users until late in the development process.
Data Marts (q.v.) have a smaller scope and are easier to cost-justify.
Design Patterns are generic solutions to a particular group of problems.
In the case of Data Models, we can easily identify that a Design Pattern would be a generic Data Model for Master / Detail situations.
For example, Customers and Service Requests or Voters and Elections.
On the Database Answers Web Site, there are several Design Patterns, which are referred to as Generic Models.
An excellent one to start with is this one which provides a Generic Foundation for a family of related Data Models.
Global Reporting presents a range of demands for Data Integration and Performance Reporting.
The Databases must provide multiple languages in a range of aspects, such as product descriptions, messages for Users in the User Interfaces.
In order to avoid major redesign o the databases, these need to be built-in from the start, rather than bolted on later.
Industry-Standard Data Models
Industry-Standard Data Models are extremely useful because they facilitate Shared Services and development by Vendors and data exchange between
interested parties, such as Industry Associations.
Wikipedia contains some useful background entries for
• A Generic Justice XML Data Model “for the exchange of information within the justice and public safety communities”.
• A Standard Data Model
• A Standard User Model
At Database Answers, we have defined some generic Data Models for specific Industries or ‘Vertical Applications’.
These are documented in this Section in the form of Entity-Relationship Diagrams (ERDs) and also Data Marts, which are particularly useful for
exchanging data at summary level.
These Data Models cover the following areas :-
1) Customer Services and Local Government
3) Law Enforcement
5) Web Site Analytics
The ERDs are included in this Section, and the Data Marts are in Section A.6.
There is a small number of standard Data Models that have been proposed by other organizations.
Two of these are IBM and Oracle.
1) IBM offers a Banking Data Warehouse and an n Insurance Application Architecture (IAA).
2) Oracle offers a Retail Data Model.
Master Data Management
Master Data Management, or MDM, can be defined as 'A Single View of the important Things of Interest' in an organisation.
These will typically include Customers, Products and Suppliers.
The MDM Approach is commonly used when data from a number of different sources is being integrated.
When there are many different definitions of a Customer, it is necessary to consolidate them and to match the same Customer across different Sources.
Microsoft considers this are so important that they acquired a company called Stratature, and integrated the Stratature product into their own MDM offering
Here’s an excellent article by Roger Wolter and Kirk Haseldon of Microsoft discussing the “What, Why and How” of Master Data Management.
Microsoft Unified Dimension Model
With the Unified Dimension Model, Microsoft offers a Generic Data Mart to provide a common front-end for Business Users and other
interested parties to share a common perspective on data for Business Intelligence (BI) and Performance reports.
The Microsoft TechNet Library has a useful Weblink.
Mobile Data Platforms and Applications
Mobile applications are increasing being used for serious commercial and business uses, such as data entry of observations in the environment.
The implications are replication and synchronization of remote Databases running on Cellphones and Mobile phones, typically running Windows Mobile with SQL Server CE.
Business Intelligence is also being adapted for mobile working.
There are three broad categories :-
* Mobile Alerts – eg Inventory is too low
* Mobile Reports – eg Total Employee Headcount today
* Mobile Dashboards – eg provide broad summary of performance for senior management
All of these categories benefit from the specification of a Mobile Data Platform which provides a consistent view of data that is being delivered by mobile applications.
Commercial products in this area include mobiScaler from Vaultus, (who work with Salesforce.com) using the Vaultus Enterprise AppGenerator.
Multiple Languages in a Database
When Users need to logon to a system, the Database will store a preferred language field for every User.
UNICODE is used to correctly store different characters sets, such as Japanese, French or Hungarian
Multiple languages are commonly used for Multi-national Corporations, such as Microsoft, where they need to store the description of the same Product in many different languages.
In this case there would be a language field in every table that stores text that must be displayed in the language suitable for the country (or the preferred language for a User).
There may not be any text of this nature that must be displayed in different languages.
There may be messages that the application will display to the User.
In other words, they exist in the interface between the application and the User.
For example, the Japanese characters for "Welcome" would be displayed as ????.
Open Source Databases
The Open Source approach offers free software with support available at a charge.
The most widely-used Open Source Database is mySQL, and here’s their Web Site http://dev.mysql.com/
A while ago, MySQL was taken over by Sun, which was recently taken over by Oracle, (in place of IBM).
It will be interesting to see how the future of MySQL evolves.
Products and Prices
The design of a database will always reflect the business requirement.
In the specific case of the price of a product, this can frequently be flexible and change to reflect supply and demand.
For example, you can negotiate the price of a hotel room or a plane flight.
Therefore the design of the database must accommodate the requirement to store a standard price and then the actual discounted price.
This is usually stored with the Order Item details.
I recommend this basic Customers and Products Data Model as a good starting-point.
Then add a field called actual_price_paid to the Order_Items table.
This Hotel Researvations Data Model shows a good example variable pricing in the Daily_Room_Rates table.
Reference Data is an important part of any Database.
Its importance frequently goes unrecognized.
The specification and sourcing of Reference Data should be one of the early tasks in creating an operational Database.
Where possible, it should conform to National or International Standards.
For example, Currency Codes and Currency Codes.
Reference Data is an important part of Master Data Management, which requires a single, consistent definition of ‘Things of Interest’ in a Database.
An exxcellent reference is our sister Master-as-a-Service Web Site.
Service-Oriented Architecture (SOA)
The Service-Oriented Architecture ‘SOA’ approach is important and will become increasingly important in the future.
It is based on providing data processing functions as a series of Services, which can be Web Services.
It can include an Enterprise Service Bus.
It is an essential design approach for Cloud Computing because it makes it possible to think about Data Services as independent functions that can be
executed as required in an environment that is loosely coupled around an SOA and an Enterprise Service Bus.
Microsoft’s position on SOA is worth reading, and they also have a Self-Assessment Tool to help organizations identify where they are in SOA and
Business Process capabilities.
Sharepoint is a very powerful and very popular Microsoft toolset for creating Intranets.
It is particularly appropriate for global organisations who want to share documents with access control for different groups of people within the organization.
It can also be used for creating and publishing Internet Web Sites.
Sharepoint includes a Business Data Catalog which can be used to integrate information from a variety of Databases, including Oracle.
It also provides Business Intelligence in the form of Key Performance Indicators,(‘KPIs’).
It can therefore be used to implement the Database Answers Road Map for Enterprise Data Management.
A good starting-point is Here’s a link to Microsoft’s home page for Sharepoint ,and Wikipedia has a useful entry.
XML Database Schema
The ability to export Data Schema or MetaData in industry-standard XML is a powerful facility that should be included in the criteria you
follow when choosing the right Data Modelling Tool to meet your requirements.
This would provide a facility to export Database Schemas into a Data Dictionary or Information Catalogue.
In other words, this very useful facility would be one that exported records of tables and fields from a Table into a CSV file which could then be
imported into a Data Dictionary Table.
Of course, even better would be an ODBC or Web Service interface to the System Catalogues in the Data Modelling Tool.
My favourite Data Modelling Tool is Dezign from Datanamic because it produces diagrams that I like to look at, it has excellent
features and it is very affordable.
Dezign stores Data Models in a .dez file, which is itself is an xml file.
This could probably be transformed into a csv file with XSL transformations.
Here is an article contributed by Robert Allen of SlaphappyGeeks.com, which provides an excellent introduction to the subject.
And this one shows how it can be used in practice.