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.
Global Reporting
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.
Handling Hierarchies in a Database
Hierarchies are very common in the real world.
The two most common examples are Employees Hierarchies and Organisational Hierarchies.
These are shown in this simple Data Model on the Database Answers Web Site.
Using ANSI-standard SQL it is relatively straightforward to handle Hierarchies by joining a table to itself and using the ‘parent’ concept.
Joe Celko is a very well-respected writer on SQL, and Joe has written a book devoted to the topic of Trees and Hierarchies in SQL
His book scores 3.5 Stars on Amazon and is definitely worth a look.
Microsoft’s SQL Server provides a feature called a Hierarchy ID Datatype, which is described in this MSDN Magazine Article and this Technet Library ArticleOracle provides CONNECT BY, which is very easy to use and very powerful.
Tutorials
This simple Tutorial which provides an insight contains the following steps :-
1) Identify the Data Sources, such as RSS Feeds or Google Maps
2) Obtain the APIs for each Source
3) Map the API data items to a common data structure.
4) Using the appropriate programming language such as Apex, code the User Interface (UI)
5) Finally, behind the UI, code the integration of the data.
Additional Tutorials will be derived from a review of Mashup Data Architectures.
Microsoft’s highly respected Scott Guthrie mentions a Mashup Tutorial in his Blog
Microsoft has an excellent page on Enterprise Mashups in their Architecture Journal, which says
“… all mashups are RESTful in nature (they conform to the Representational State Transfer principles)”
and contains this very helpful diagram of the Architecture of a typical Mashup :-
It's worth checking out the highly recommended Microsoft Architecture Center.
Question : How do I Demonstrate the potential value of Mashups ?
The best way to demonstrate the potential is to produce something quickly that meets a genuine business requirement.
The best way to identify the requirement is to engage with Stakeholders to determine what kind of data they need that they are
not currently getting, especially data that comes from more than one source.
Qualities for Success in creating Mashups
To be successful in creating Mashups it is necessary to possess a number of qualities :-
To have some level of experience as a developer
to like to work with user people
to enjoy seeing results produced quickly that meet a clearly defined User requirement.
to enjoy keeping up-to-date with innovations in fast-moving technology
to be able to recognize innovations that are important and have lasting power.
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.
Oracle EXTERNAL Tables
Oracle offers a neat facility to read CSV Files as External Tables.
One important point to keep in mind is that you must put the CSV Files on the Oracle Server.
In other words, where Oracle can read them.
Simply putting them on your PC (eg C Drive) won't do .
Here is a Useful Link on OraFAQs.
Reference Data
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.
SBVRThe Wikipedia entry says "SBVR stands for the Semantics of Business Vocabulary and Business Rules is an adopted standard of the Object Management Group (OMG)
intended to be the basis for formal and detailed natural language declarative description of a complex entity, such as a business".
SBVR is related to BPMN(qv) and both of these approaches have been addressed by a product called Collibra.
Semantic Web
We have a page devoted to the Semantic Web.
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.
You should also check out SOA Patterns, an interesting Book on SOA Patterns and the SOA Symposuium.
A particularly helpful Pattern is for User Interfaces (UI).
Finally, Gil Fink has posted a Blog suggesting a great idea to generate Templates for the Microsoft Entity Framework.
Sharepoint
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.
Single Version of the TruthWikipedia describes a Single Version of the Truth in the following terms :-
"... a technical concept describing the data warehousing ideal which stores all of an organisation's data in a consistent and non-redundant form."
IBM offers the WebSphere Commerce.
Customer Data Integration.
Microsoft has defined a Single View Platform for Government.
It describes it as "a collaboration and data visualization platform that provides government agencies with solutions for one comprehensive geospatial view of critical data.
Microsoft SVP provides a single, geographic view of complex information and data sets across multiple roles, locations and user interfaces, which can
vastly improve agency communication, collaboration and decision-making, and significantly increase the success of government missions."
SQL and the System Date
A frequent requirement is to pick up the current date in an SQL statement.
This is done by SELECT 'The date stored in the System'.
This is not covered by ANSI SQL, but each vendor has implemented their own variation of a common theme.
For IBM's DB2 use CURRENT DATE (no underscore) :-
SELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1
You can also use CURRENT TIME or CURRENT TIMESTAMP depending on your needs.
For MySQL, the syntax is :-
SELECT CURRENT_DATE
For Oracle, which provides a dummy table called Dual, and the Oracle Syntax for SYSDATE looks like this :-
SELECT TO_CHAR
(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
FROM DUAL;
NOW
-------------------
04-13-2001 09:45:51
For SQL Server, the equivalent of Oracle's SYSDATE is the GETDATE() date function, for example :-
SELECT GETDATE() AS [SYSDATE]
GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.
Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.
Trusted Information
Trusted Information is a neat phrase that is achieving some currency these days.
It refers to the need for information that appears in mission-critical Performance reports to be of good quality, correct, consistent and trustworthy.
It implies Data Governance, Data Quality and a published set of Business Rules for Data Clean-Up.
IBM has given exposure to Trusted Information as part of their Information Agenda Concept
The phrase ‘Trusted Information’ appears on page 9 of the document entitled “Accelerating an information agenda with IBM InfoSphere Foundation Tools”.
Microsoft has also given thought to a Trusted Information Class and a Trusted Domain Object Data Model
The slightly more focussed expression “Trusted Intelligence” appears on the Global Data Excellence Web Site
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.
