Bob Jankovsky - Data architecture -

DATA ARCHITECTURE

Class: Search: for: |< < >

Light-weight asynchronous adhoc tasks processor AHOY

by Ludek Bob Jankovsky, 18-Jun-2020 (HEAP WORKFLOW)

Soemtimes we need to run several one-time tasks on Oracle database, just aside the standard orchestration.
Following simple solution helps to avoid makeshifts to cope with such situations again and again.
The solution is created as simple as possible, 2 tables and one package. It requires DBMS_SCHEDULER granted.
The definition table AHOY_TASK acts as the log table too. There could be necessary informative column added in the sake of particular solutions.
The parameters table AHOY_PAR contains one basic parameter APAR_THREADS allowing to define number of parallel threads or to stop running completely by setup to O(zero).
The queue works in the FIFO mode, first defined task will be first processed. No other dependencies.
The solution allows to increase and decrease number of parallel threads during runtime what allows the "watch and regulate" approach.
This is an AD HOC solution so clean the AHOY_TASK again and again after completing, there is no aging implemented in the sake of simplicity. ...more

Ale or Lager, you have to reconcile your Real-time Operational Data Store anyway

by Ludek Bob Jankovsky, 11-Oct-2019 (ETL PATTERNS)

After few lagers I will return to common Real time Operational Data Store topic. You can brew Lager, you can brew Ale, you can brew Stout, but still you will have to taste if you have braved it well. You will have to consider possibility of consistency loss in your system and you have to be ready for that.
The first inconsistency is the initial one, the state of the system before initial load. Then there could be consistency breaches, down-times of particular services etc. Never get too pampered by good taste of the first batch, because the second one could turn sour. And better than end up with sour faces, lets be ready for that. Lets involve consistency tests, consistency repairs, hard repairs to our brewing process.
This article follows previous three in the series: ...more

Lager-wise Real-time Operational data store and Elasticity

by Ludek Bob Jankovsky, 30-Sep-2019 (ETL PATTERNS)

In last two articles about beer (well with the taste of some boring Data processing and Operational data stores) and especially the last one sliding on surface of the Lager brewing (Lager-wise Real-time ODS ETL process), we can go deeper in aspects of optimization and elasticity of the process.
At the very start I was getting familiar with the real-time processing I imagined I can solve only operational rows based changes and all these bulk operations such as EOD processing, EOM processing etc. would be solved by some other way. In the rush of real-time problems we just postponed that somewhere to the future. And in that "future" what is now past we learnt that there is no one and nothing what will solve that for us. There is no synchronization mechanism allowing to stop real-time processing, switch it to bulk one and then return to the real-time seamlessly. First there is the synchronization and process management problem, but more important is that not all bulk operations are well mapped and planned in the source system. We cannot say to users: "I am sorry, we do not have up to date data now, because the system XY does some bulk operation we cannot cope with real-time."
Our real-time solution should be ready even for bulks.
Just to be clear. ...more

Well filtered lager, Lager-wise Real time Operational data store

by Ludek Bob Jankovsky, 26-Sep-2019 (ETL PATTERNS)

After the last article about beer and operational data stores and the audacity it got (understand some people have read it and few people even liked it) I got encouraged to write a sequel.
Some readers were slightly disappointed why do I mess some boring Data issues into relatively cute article about beer, but most people appreciated it.
We will focus on Lager-wise Real-time Operational data store now. As I have written Lager-wise ODS are much more complicated and the latency is not as real-time as expected. Ale drinkers sometimes wonder what is so complicated with lager and why my glass of lager is still half full when their is still half empty. Despite Lager-wise Operational data stores has such disadvantages and it is in general not easy to brew good lager, I feel that type will soon prevail, especially in the Business intelligence world where we tend not to bother legacy and source layer application owners by extensive integration tasks and the agile approach allows to do that efficiently. I am partially guilty. The Metadata-driven approach and ETL patterns helps that - from the point of view of ale drinkers - silly - approach feasible and even efficient. Silly things happen sometimes. Some many times.

So it is about Lager now!
...more

About Real-time Operational Data Store and Beer brewing

by Ludek Bob Jankovsky, 14-Jul-2019 (ETL PATTERNS)

Speaking about ODS nowadays we usually consider the real-time or nearly real-time one. That is what makes it operational by the way. There were concepts using that term for exaggerated staging areas of Data warehouses used for early access to D+1 data, in the past times of Business intelligence patterns, but that was just misusing of the term for something else or reducing the meaning to an operational reporting resource. Later, following issues made these concepts wrong:

Using EDW ETL as a source or driving structure collided with the requirement of up-to date information.
Ad-hoc requirements for operational reporting messed with data quality and design quality requirements of flawless operational stores.
Data Lake appeared to be a better solution for operational reporting.
The requirement of high quality high available and real-time or nearly real-time data caused the new Operational data stores are slimmer but much better monitored and operated.

So, speaking about ODS we mean the real-time one,
we speak about beer, not about lemonade,
just to be clear.
...more

Intra-field deduplication of list by regular expression

by Ludek Bob Jankovsky, 23-May-2018 (ORACLE STUFFS)

Sometimes you need to deduplicate repeating patterns from a text string. Our case has been inspired by list of values based on LISTAGG operation on source with duplicities. Of course, the best practice is to deduplicate the source before the LISTAGG operation, but what if we have just the result with mentioned duplicities? ...more

Reading PIPELINED function conntaining DML in a query, ORA-14551 - small cheat

by Ludek Bob Jankovsky, 06-Apr-2018 (ORACLE STUFFS)

When a PIPELINED function contains DML operaions it is usually impossible flat select from it. It works well in INSERT-SELECT operation but hardly in a plain select. ...more

ORA-01489: result of string concatenation is too long

by Ludek Bob Jankovsky, 26-Feb-2018 (ORACLE STUFFS)

That happens when we want to aggregate/concatenate text fields and the summary length exceeds the 4000 limit.
There are various scenarios what do you want to achieve:

cut the result to 4000 characters

return as a CLOB

more complex tasks such as cut particular long parts of the text.

Following solution solves the first case, i.e. return first 4000 characters in the case of exceeding.

Original statement: ...more

When ORA-01408: such column list already indexed really messes up

by Ludek Bob Jankovsky, 18-Dec-2017 (ORACLE STUFFS)

Cause
You tried to create an index on a set of columns in a table, but you've already indexed this set of columns.
But
...more

Data dictionary based DDL skeletons

by Ludek Bob Jankovsky, 01-Aug-2017 (ORACLE STUFFS)

For building usable structure for copies, bulk hard operations, etc. I have collected few seletons to be enhanced to get useful code when needed.
These codes are neither complete nor universal, they just reflect what I needed at the point of time I wrote them. They can just be useful not to start from scratch. ...more

Useful REGEXP examples

by Ludek Bob Jankovsky, 28-Jun-2017 (ORACLE STUFFS)

While generating codes and working with text there could be necessary to shorten or wrap input text to achieve better readable form.
Following examples of regular expressions usage within SQL queries can be handy. ...more

SIMPLELOAD - Elementary 'last phase' solution for ETL

by Ludek Bob Jankovsky, 31-May-2017 (ETL PATTERNS)

Despite on poorest of the solution in comparison to true metadata driven ETL, I often meet transformations realized as views with almost the same format as the target table, followed by the pattern based implementation of the final stage - loading the transformed source data into target table, considering current data and maintaining history.
I have to admit the smart approach is proper for small and quick win solutions.
For easy implementation I scripted a skeleton of the last phase load element based on data dictionary information, supporting the most common patterns. ...more

METASWAMP - XML schema

by Ludek Bob Jankovsky, 01-Dec-2016 (ETL PATTERNS)

METASWAMP is a set of metadata driven pattern and repository requirements implemented primarily as an XML schema. It is intended to serve as an interface between ETL patterns and Repository storage(s). ...more

Quantum eyeballing method of evaluation - comming soon

by Ludek Bob Jankovsky, 03-Nov-2016 (NEWS)

...more

ETL and the ORA-01502: index *** or partition of such index is in unusable state error, ORA-14063

by Ludek Bob Jankovsky, 02-Nov-2016 (ORACLE STUFFS)

Especially in huge ETL processes we often increase efficiency of the process by setting some indexes on the target table UNUSABLE. That way we speed-up active operations such as inserts and merges into the table with unusable indexes, but the ORA-01502 error can back-stab us in some phases of the process.
We should be worried about reading operations what we can slow down (sometimes even freeze) by lack of the unusable index, but most of ETL operations are provided in an exclusive time period. By the other hand many concurrent ETL processes and even most overall analytical queries do not need indexes for its work.
However, time after time it happens, the error mentioned above appears in the process. ...more

Simpletask is under the UPL license now

by Ludek Bob Jankovsky, 29-Oct-2016 (HEAP WORKFLOW)

To make all the license stuff clearer in the case of the Simpletask solution, the UPL - Universal permissive license has been applied for all parts of the software and documentation published on this site under the Simpletask name.
Differently from plain Copyleft license it allows easier incorporation of the software and parts of the software into Larger works without obligation of using the same Copyleft license for these larger works. ...more

APEX - Downloading files

by Ludek Bob Jankovsky, 11-Jan-2016 (ORACLE STUFFS)

Using APplication EXpress as a simple GUI cover for Matadata based generators of XML and SQL files led me to task to download these files.
Browsing over various helps I decided to add another one to the rubbish.
There are two basic scenarios:
1. We want to download result of PL/SQL method (function) directly to client machine, or
2. We want to download generated codes from stage - some storage of files prepared for download. ...more

Lazy hint - go faster for bulk processing in Oracle

by Ludek Bob Jankovsky, 24-Aug-2015 (ORACLE STUFFS)

Following an example of politicians, we add a tax to index usage to stimulate hash processing using a hint:

/*+ OPT_PARAM('optimizer_index_cost_adj', 30000)*/

...more

Shake It!

by Ludek Bob Jankovsky, 17-Mar-2015 (ORACLE STUFFS)

Time after time we need to move tables in development databases to "heal" non-standard operation gaps and chains. Following script could serve as a template. ...more

Real-time ETL patterns and how to piss to pit

by Ludek Bob Jankovsky, 16-Mar-2014 (ETL PATTERNS)

Recently I published an article about real-time ETL. Designing it we hit on several challenges and various different approaches. To be certain, we speak about Real-time ETL realizing Data integration of sources, not a Service integration. We have to react on smallest changes in source data and propagate the changes to transformed target Data structure. Advantages and disadvantages I described in previous article. ...more

Edges of Real-time ETL

by Ludek Bob Jankovsky, 11-Jan-2014 (ETL PATTERNS)

Nowadays Real-time and message based ETL take more significant place in IT infrastructure. Differently from Messaging based operational data, in the Real-time approach target data are refreshed based on changes of source data in source systems. That uses means what was earlier often used for replication of data. Differently from replication, realtime ETL is hardly 1:1 but the transformation takes place in the process. Real-time ETL brings many limitation in comparison to standard batch ETL. ...more

XML Import and export utility for SIMPLETASK

by Ludek Bob Jankovsky, 25-Aug-2013 (HEAP WORKFLOW)

According to principle of maintaining Logical process separately from physical implementation in SIMPLETASK the Export / Import format is necessary.
Following Add-on package contains API for import XML format. The primary intention is to use the feature for importing metadata generated from Logical process model, but it also can be used to archive physical metadata or to transfer them between environments. ...more

SIMPLETASK API REFERENCE - Views

by Ludek Bob Jankovsky, 14-Aug-2013 (HEAP WORKFLOW)

Passive API of SIMPLETASK are solved as a set of views. Most of the views are related to particular tables enriched by useful columns. ...more

SIMPLETASK API REFERENCE - Package BWTA_OPER

by Ludek Bob Jankovsky, 11-Aug-2013 (HEAP WORKFLOW)

BWTA_OPER is an active API in the form of oracle PL/SQL package. It is designed as a basic layer of API allowing active manipulation with tasks in their runtime within the SIMPLETASK solution. ...more

SIMPLETASK API REFERENCE - Package BWTA_METADATA

by Ludek Bob Jankovsky, 11-Aug-2013 (HEAP WORKFLOW)

BWTA_METADATA is an active API in the form of oracle PL/SQL package. It is designed as a basic layer of API allowing active manipulation with metadata of the SIMPLETASK solution. ...more

Scripts for creating of a deployment script of database object through SQL scripts

by Ludek Bob Jankovsky, 11-Aug-2013 (ORACLE STUFFS)

Sometimes it happens you want to create package script and the tool you can use doesn't satisfy your needs. Sometimes it happens you need to embed the creation scripts into script independent on any such tools. For these case I stored simple script extraction scripts in the article. ...more

APEX GUI for SIMPLETASK heap workflow

by Ludek Bob Jankovsky, 10-Jul-2013 (HEAP WORKFLOW)

Simpletask Heap Workflow is a simple Oracle based solution for ETL process management based on Oracle DBMS_SCHEDULER and Heap Workflow principles. Now we present APEX GUI, allowing to manipulate metadata and operate ETL easier way than using the PL/SQL based API only. ...more

Heap Workflow

by Ludek Bob Jankovsky, 29-Jun-2013 (HEAP WORKFLOW)

Long ago when I started to design ETL systems I realized, that robust ETL process management is as important as any other pillar of ETL approach.
Most current workflow systems are focused to easy visualization in structured form.
Structured workflow systems are good for simple process management. I have never seen ETL system with just a little focus on robustness and performance. For that you need a fine grain heap based workflow system allowing to use operational research methods.

What's different?
When you put task into structured workflow, you wire it in certain place to be exacuted at. In heap workflow you put all task in one heap and define constraints of their executions.
The constraints are of following types:

Predecessor dependency (solves dependencies comming from referential integrity, data rediness etc.)
Resource consumption (solves system resource balancing, exclusivity of filling certain segments and finally using pending resource consumption it can solve temporarily disability of certain objects)
Condition function (solves additional timing requirement, branches, skips etc.)

...more

Use data type arithmetic to convert from 'Timestamp' world to 'Date' world and opposite way

by Ludek Bob Jankovsky, 09-Mar-2013 (ORACLE STUFFS)

There is not straight conversion between the TIMESTAMP and DATE datatypes. Easy way to 'misuse' the standard data type arithmetic to do particular conversions in our queries.
Following article will briefly explain the ways. ...more

Analytical functions and interpolation - samples

by Ludek Bob Jankovsky, 23-Feb-2013 (ORACLE STUFFS)

There are various situations you want to fill missing values based on last filled or neighbor filled values. Following simple example show the approach: ...more

Articles: 1 .. 30 first prev next