It was fairly difficult to come up with a title for this article that sort of covers the content. To me it is quite clear what this is about – but how to convey that in a title? Let me explain: today in our project we discussed the implementation of a data service. The service operation under scrutiny takes a city as input and returns a list all open orders from customers located in that city. Nothing very special there. The interesting complication lies in the fact that the customers are part of a different domain than the orders. This means – under our architecture guidelines – that we cannot create a single SQL query that joins together the customers table with the orders table. A database link to join the tables across databases is out of the question and even if these tables currently reside in the same database – such a join is not allowed. Different data domains are treated as independent entities and no direct dependencies between the two should be created. Every design has pass the check ‘will it still work if one of the domains involved were to be relocated to the cloud or be replaced by a third party application’.
The architecture is service oriented. Every domain More >
To be useful, data held and used in information systems has to live up to a number of expectations. The data should be an accurate representation of its source. It should be reliable. The data should have internal consistency. The data should adhere to rules based on the logic of the real world. This accuracy, internal quality, and reliability of data is frequently referred as data integrity.
Safeguarding the integrity of data is a challenge, one that increases in complexity when multiple users access and manipulate the data simultaneously , obviously a common situation. And that challenge reaches new heights when the data is managed in multiple independent data stores rather than a single database.
Earlier this month, the Oracle Technology Network published an article that I recently wrote on this subject: http://www.oracle.com/technetwork/articles/soa/jellema-data-integrity-1932181.html. I was triggered into writing it by two recent experiences.
One was at a customer of mine where we are designing a service oriented architecture, based on a number of distinct and independent data domains. These domains are exposed through elementary (entity) services. A second tier of More >
Hierbij nog dank voor allen die aanwezig waren bij de weer gevulde, informatieve & gezellige avond tijdens “Hotsos Revisited 2013″.
Wij presentatoren hebben genoten van het ambiance. Hier ook nog voor degenen die graag het nog een keer willen nalezen het presentatie materiaal van Toon, Jacco, Gerwin, Frits en mij…
Presentatie materiaal in alfabetische volgorde:
- Marco Gralike – Creating Structure in Unstructured Data (pdf-3,6MB)
- Gerwin Hendriksen – “Method GAPP” Used to Mine OEM 12c Repository and AWR Data (pdf-7,6MB)
- Frits Hoogland – About Multiblock Reads (pdf-4,3MB)
- Toon Koppelaars – SQL Plan Management (pdf-3,1MB)
- Jacco Landlust – Lessons Learned while Pushing the Limits of SecureFiles (pdf-1,1MB)
Hopelijk zien we jullie misschien weer tijdens de Masterclass over Oracle storage “Understanding storage met Oracle ACE Director James Morle” en anders hopelijk tot een volgende keer bij AMIS in Nieuwegein.
Hereby, for those who want another look or for people to share, my presentation content “Creating Structure in Unstructured Data” given during the Hotsos 2013 Symposium on Monday morning.
Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR1
Using Oracle SQL to resolve meaningful and slightly less serious challenges is one of my favorite (semi-)professional pastimes. In the last two weeks, I have been presenting on various topics including Oracle SQL to audiences in six cities all across India as part of the OTN Yathra 2013. These presentations and the interaction with the attendees on the various capabilities of SQL have inspired me in several ways. One of the outcomes is this article – also inspired by the fairly long journey home and the many flights within India. In this article I will use several powerful options in Oracle SQL to resolve some simple to ask questions. The SQL functions I am using include:
- Insert with Multiple Subqueries
- Insert generating some random data
- LISTAGG for aggregating strings
- LEAD to produce the result for one row using information from the next
- PIVOT to present the data in a matrix format
The statements are straightforward (relatively), the data model is simple. You will like it.
Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring0
The challenge in this article is a simple one. I have a table with records (duh!) and each record describes the occurrence of a certain payload. In this example the payload will be a color. Every record also has a sequence number to indicate well, when it appeared relative to the other observations. A subset of the data would look like this:
The challenges I will discuss in this article are simple:
- which sequences of three color observations occur in the data set
- how often do these sequences of three occur (give me the top 3 sequences)
- given the colors that have been observed, which combinations of three can be created
- which possible combinations (or color sequences of three colors) have not been observed at all
Using Oracle SQL features such as Analytical Functions, Outer Join and Subquery Factoring – these questions become very easy to answer.