How to Split a MultiPolygon Column Into the Corresponding Multiple Polygons in SQL Server

If you're looking for a way to decompose SQL Server geometry (or geography) columns containing multiple polygons (MULTIPOLYGON) into their corresponding polygons, then the following method will come for sure in handy.

First of all, we create a custom dataset (a table variable) containing our sample multipolygons:

declare
    @tv_MultiPolygonsAndPolygons
table
(
    MultiPolygon_ID int identity
    , MultiPolygon_Shape geometry
)
;

insert into
    @tv_MultiPolygonsAndPolygons (MultiPolygon_Shape)
values
(
    geometry::STPolyFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0))
    , (geometry::STMPolyFromText('MULTIPOLYGON(((6 20, 8 20, 8 22, 6 22, 6 20)),((3 6, 6 7, 5 9, 3 6)))', 0))
    , (geometry::STPolyFromText('POLYGON((10 0, 12 0, 12 2, 10 2, 10 0))', 0))
    , (geometry::STMPolyFromText('MULTIPOLYGON(((0 10, 2 10, 2 12, 0 12, 0 10)),((15 10, 25 12, 23 20, 15 20, 15 10)))', 0)
);

By querying this table variable:

select
    g.*
from
    @tv_MultiPolygonsAndPolygons g
;

we get the following 4 multipolygons, as expected, each of one with its own distinctive color:

The easiest and cleaniest way to split our multipolygons into their separate polygons is by creating a table-valued function, and then cross-apply (or outer-apply) our table variable containing the multipolygons to it. In order to do so, we make use of a recursive CTE:

create function [dbo].[ft_Geographie_SplitMultiPolygon](@pr_MultiPolygon geometry)
returns table
as
return
(
    with
        NS(Num) as
    (
        select
            Num = 1
   
        union all
   
        select
            Num = Num + 1
        from
            NS
        where
            Num < @pr_MultiPolygon.STNumGeometries()
    )
    select
        MultiPolygon_ID     =  NS.Num
        , Polygon           =  @pr_MultiPolygon.STGeometryN(NS.Num)
    from
        NS option (MaxRecursion 1000)
)

The CTE contains the STNumGeometries()function, a function returning the number of geometries that comprise a geometry instance, in our case the individual polygons.

We're now ready to test our brand new table-value splitting function:

select
    g.MultiPolygon_ID
    , Polygon_ID        =    sp.ID
    , Polygon_Shape        =    sp.Polygon
from
    @tv_MultiPolygonsAndPolygons g
    cross apply usw.ft_Geographie_SplitMultiPolygon(g.MultiPolygon_Shape) sp
order by
    MultiPolygon_ID
    , Polygon_ID

And, as expected, we're now getting a different row for each polygon:

Dynamic Labelling of Dimension Attributes

In Dimensional Modelling, dimensions are usually implemented as single, fully denormalized tables. This morphology translates into a simplified and more readable structure as well as into a performance gain when processing user queries, since less joins between tables are involved.

Dimensions essentially consist of attributes. Each dimension attribute should always be uniquely identified by a code and might show one or more descriptive fields:

The "Car" Dimension and its attributes.

In this example, each car to be sold is associated with a model, a manufacturer, a building year, a price, and a color. "building year" and "price" are numeric attributes and don't need therefore any further additional descriptive information - in some cases we might even copy the price field back to the fact tables, in order to allow user analysis over it. But what about "model", "manufacturer" and "color"?

Those attributes are uniquely identified by a code. However, during the processing of the dimensional table, we need a way to dynamically associate these codes with their corresponding text labels. Here a simple, ANSI-standard SQL-based solution:



; with
    iPvt
as
(
    select
        ID_iPvt                         =    ID // Dimension ID
        , Color_Descr                   =    max([color])
        , Manufacturer_Descr            =    max([manufacturer])
        , Model_Descr                   =    max([model])
    from   
        (
            select
                k.ID
                , m.Code
                , m.Description
            from
                tb_Dim_Car k
                inner join tb_Car_Attribute_Map m on
                    (m.Code = k.Color_Code and m.Type = 'Color')
                    or (m.Code = k.Manufacturer_Code and m.Type = 'Manufacturer')    
                    or (m.Code = k.Model_Code and m.Type = 'Model')
        ) Src
        pivot
        (
            max([Description])
            for [Attribute]  in
            (
                [color]
                , [manufacturer]
                , [model]
            )       
        ) Pvt   
    group by   
        ID
)
select
    ID                              =    k.ID   
    , Color_Code                    =    k.Color_Code
    , Color_Descr                   =    coalesce(p.Color_Descr, 'n/a')
    , Manufacturer_Code             =    k.Manufacturer_Code
    , Manufacturer_Descr            =    coalesce(p.Manufacturer_Descr, 'n/a')
    , Model_Code                    =    k.Model_Code
    , Model_Descr                   =    coalesce(p.Model_Descr, 'n/a')
    , Building_Year                 =    k.Building_Year
    , Price                         =    k.Price
from
    tb_Dim_Car k
    left outer join iPvt p on
        p.ID_iPvt = k.ID
;


This simple query joins every attribute code/key of our "tb_Dim_Car" dimension with the "tb_Car_Attribute_Map", a 2NF table containing all metadata we need in order to allow a dynamic mapping across all attributes:

The tb_Car_Attribute_Map mapping table.

The result of this join will be then pivoted back and outer joined with the original dimensional table. In order to allow this, however, we need a unique "ID" for each dimension row - in case we lack it, a ranking windowing functions comes of course at handy - if supported by the database engine.

This pattern can be easly extended in case of multilngual description attributes by simply adding a "Language Code" column in the mapping table.

First Steps to Market Basket Analysis with R

In this article I'd like to provide a walkthrough guide on how to perform a simple market basket analysis using R. First of all, I suggest a brief reading of the "Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management" book written by Gordon S. Linoff and Michael J. A. Berry, and more specifically its chapter 15.


The Market Basket Analysis is a marketing application of the data mining technique known as "affinity analysis". It is often used in the retail industry as instrument to infer the customer purchase behavior, in order to improve the sales process by means of activities such as loyalty programs, discounts, and cross-selling.

A well known application of market basket analysis: the Amazon "customers who bought this item also bought" cross-selling functionality.

We suppose to have access to the historical user search data of a car retail portal. In order to perform a Market Basket Analysis, data must to be first fully pivoted - i.e. every user search must correspond to a row, and every search criteria must be corresponding to a column.

Fully pivoted user search data. Source: e "Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management" by Gordon S. Linoff and Michael J. A. Berry, p. 295.

We now need our pivoted data to be imported in our R environment. Through ODBC, by querying a table or executing a stored procedure, we can direct import them - thus generate a R data frame object:

require(RODBC)
channel <- odbcConnect("dwh", uid = "<your_username>", pwd = "<your_password>")
db <- sqlQuery(channel, "select * from <your_table>")
close(channel

df <- db[,c('column1', 'column2', 'column3', 'column4')]

Our data are now in the "df" data frame object.

In order to perform a Market Basket Analysis, we need to load the "arules" and the "arulesViz" R packages:

library(arules)
library(arulesViz)

Are all our data column of "factor" type? This is required by the arules package.

sapply(df, class)

If not, we can "correct" the "wrong" column by using a cast operation:

class(df$ProductX)
df$ProductX <- as.factor(df$ProductX)
class(df$ProductX)

We might not be want to manually cast every single column of our dataframe. If this is the case, we can use the colwise function of the plyr package:

require(plyr)
tofactor <- function(x) { x <- as.factor(x) }
df <- colwise(tofactor)(df)

The Market Basket Analysis algorithm ignores every NA value (the R equivalent of the database NULL). In case our DWH dataset is still not qualitative good enough, we might want to to manually clean it:

df$WithDiscount <- replace(df$WithDiscount, df$WithDiscount==0, NA)

Our data are now ready to generate useful information. We begin with a standard support of 0.1% and a confidence of 80%:

rules <- apriori(df, parameter = list(supp = 0.001, conf = 0.8, maxlen=5))

Let's give a look at the top 7 rules, ordered by lift:

options(digits=2)

rules <- sort(rules, by="lift", decreasing=TRUE)

    inspect(rules[1:7])

We obtain something like this:

   lhs   rhs      support     confidence     lift

1  {WithDiscount=1} => {Model=Golf}  0.0070  0.92   1.3

2  {WithDiscount=1} => {EngineType=Diesel} 0.0064  0.85  1.1

3  {Model=Golf} => {Color=Green}   0.0300      0.97 1.4

4  {Model=Golf} => {Color=Red}   0.4138      0.90   1.3

5  {Color=Red} => {EngineType=Gasoline}  0.4138   0.90   1.2

6  {Model=Golf} => {EngineType=Diesel}  0.6343  0.89  1.2

7  {EngineType=Electric} => {Color=White}  0.6343      0.

For better reading we can export our rules to a csv file:

write(rules, file="rules.csv", quote=TRUE, sep=";")

As usual, most of the Data Mining results appears to be trivial - i.e. they are already known by anyone familiar with the business.

In this example, it may appear non trivial and therefore worth a further analysis the rule 7 (a customer choosing an electric vehicle is more likely to purchase it of white color) and the rule 5 (future owners of red painted cars tend to prefer traditional gasoline engines over diesel or electric ones).

Finding out a sound and useful interpretation of these derived rules is, of course, part of the analysis itself.

Distinct Count of Values in R

issue of duplicate rows

Multidimensional datasets often shows the issue of rows containing duplicate values. In SQL we can easly handle this problem thanks to the COUNT DISTINCT aggregate function. But what about R?

According to a couple of websites and blogs I've quickly checked, the fastest and most efficient way to get a distinct count of values in R seems to be by making use of the R unique function:

unique(dataset$column)

where "column" is the column name of the "dataset" dataset, whose values we'd like to distinct count.
The function is gonna return us a vector containing the unique list of values of the specified column - i.e. a vector without duplicate elements.

Thus what we need now is a simple count of this vector:

nrow(newdataset)

Wrapping in one, single scalar-returning statement:

nrow(unique(dataset$column))

If we wanna apply the same logic to the whole dataset rather than a single column, we can use the sapply() lamba-function:

sapply(dataset, function(x), length(unique(x)))

How to Determine the Bounding Box of a Spatial Index in SQL Server

In SQL Server you can associate an object with a region by invoking the STIntersects() function against a geometry or geography column, as it would be a traditional join:

DECLARE @g geometry = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0)
DECLARE @h geometry = geometry::STGeomFromText('POINT(1 1)', 0)
SELECT @g.STIntersects(@h)

This method allows the spatial location of objects using a standard SQL query: we're basically asking SQL Server which polygons our objects/points fall in.

Tracking a large population of objects and especially if they're moving, however, shows a really poor query performance. Is there any method to increase this geometrical logic performance?

According to the Microsoft online documentation, we can make use of a spatial index - a standard B-tree structure, which decomposes the 2-dimensional spatial data into a linear, hierarchial grid. By means of a spatial index, SQL Server can then internally perform a spatial calculation using simple and fast integer arithmetic.

A spatial index decomposes the 2-dimensional spatial data into a linear, hierarchial grid.

The syntax is pretty much trivial:

create spatial index
    six_tb_Dim_Geographie_Polygon
on
    dbo.tb_Dim_Geographie(Polygon)
using GEOMETRY_GRID
with
(
    BOUNDING_BOX =(xmin=9.53074889950784, ymin=46.3723050741545,xmax=17.1607732090805, ymax=49.0205207370626)

    GRIDS = (LOW, LOW, MEDIUM, HIGH),
    CELLS_PER_OBJECT = 64,
    PAD_INDEX  = ON
);

Since geometry data can -teoretically- occupy an infinte plane, the spatial index requires a rectangular bounding box, i.e. the coordinates of the x/y coordinates of the lower-left/upper-right corners of the entire geometrical structure. How to calculate them? Here is a simple query:

; with
x
as
(
   select
     geom = geometry::EnvelopeAggregate(g.Polygon)
   from
     dbo.tb_Dim_Geography g
)
   select
   xMin = x.geom.STPointN(1).STX
   , yMin = x.geom.STPointN(1).STY
   , xMax = x.geom.STPointN(3).STX
   , yMax = x.geom.STPointN(3).STY
  from
   x
;

Whereby the '1' point is the lower-left corner and '3' is the upper-right one; 'dbo.tb_Dim_Geography' could be the geographical dimension of your data warehouse, or any table containing the geographical structure in a normalised environment.

How to convert a Shapefile from UTM (WGS84) Coordinates into GPS Latitude/Longitude in R

According to Gartner, more than 80% of all information is supposed to have spatial reference.
The business value of any kind of data with spatial reference can be dramatically leveraged by means of integration and visualization with geographical, demographic and geopolitical data.

Developers and IT professionals often choose the way of creating their own geographical master data instead of relying on traditional GIS applications, which are often too highly specialized in order to be integrate into the ongoing information systems.

For this purpose, many open data sources and technologies can be used; the most common data format is the ERSI shapefile. To import a shapefile from the filesystem to one database many different tools can be used; in SQL Server environments I suggest the free and fast Shape2SQL Freeware tool.

Important: during the upload. Shape2SQL uses an unique transaction. If for any reason the creation of the SQL spatial index fails, the entire transaction will be rollbacked and and you won't see any newly created table in your target database - I suggest to disable this automatic and buggy index creation feature. Do not also forget to set the SRID as 4236.

You might think that once you your "shape" table has been created, your pairs of latitude and longitude coordinates are ready to uniquely identify every surface/polygon as well as every point on the earth's surface. Unfortunately, it's not quite that simple.

If your goal is to integrate and visualize data on standard platforms as Google Maps, OpenStreeMap or Bing Maps, you need in fact GPS latitude/longitude coordinates in WGS 1984 format - otherwise known as EPSG 4326. Most of the open data sources, however, publish shapefile data in UTM format, an old format that differs from the latitude/longitude system in several respects.

How to convert shapefile from UTM to latitude/longitude GPS formats? Here is fast and no-cost solution, using the popular data manipulation and data analysis opensource framework "R", togheter with the gdal library.

First of all, we install gdal and switch to our working directory (i.e., the directory in which we copied the original UTM shapefile):

install.packages("rgdal")
setwd("[yourworkingdirectory]")
getwd()

We then import the shapefile by creating a dataset in our workspace:

shape <- readOGR("directory", layer="filename_without_extension")

A dataset called "shape" of type SpatialPolygonsDataFrame has now been created. We are curious to see what exactly we just imported, and how does it looks like:

dimensions(shape)
summary(shape)
plot(shape)

You should see something like this:

We are now ready for the UTM to GPS Lat/Long conversion:

shape_gps = spTransform(shape, CRS("+proj=longlat +ellps=GRS80"))

Eventually, we commit the result back to the filesystem:

writeOGR(shape_gps, ".", "shape_gps", driver="ESRI Shapefile")

A file called "shape_gps.shp" will now contain your gps coordinate data.

In case SQL Server complains about the validity of your shape data, make use of the MakeValid() SQL (CLR) function.

Talend Open Studio Cookbook by Rick Barton

 

Packt Publishing hat recently published a new Book: the "Talend Open Studio Cookbook" by Rick Barton.

As Data Warehouse developer and engineer, I've been extensively used Talend for many years. Apart from the basic tutorials provided by Talend, however, my only source for learning has always been the proactive Talend online community.

This book doesn't digress too much in theory and provides a full, comprehensive view on many every-day, concrete situations and their corresponding solving patterns. The learning-by-doing "recipe" approach followed by the book makes it easier to read and understand it. The book also provides a good foundation of XML principles; it lacks, however, a chapter illustrating the development of custom components, a scenario that is more likely to occur than expected.

Prerequisites for the book are a basic knowledge of Java or any c-like object-oriented programming language, as well as a rudimentary understanding of relational concepts.

I highly recommend this book for both IT experts and novices with a focus on system and data integration.

How to Determine if Two Date Ranges Overlap in SQL

By the many possible configurations two time intervals can have with respect to each other, the most common is when two or more date ranges overlap. How to express it in relational terms?

Suppose we have a reference time range X. X will be in SQL represented by a pair of date columns or variables (how else?): @pr_DateFrom and @pr_DateTo.

We are now interested in determining whether the date ranges contained in one table, tb_DateRanges, are overlapping with our reference date range. tb_DateRanges has two column, DateFrom and DateTo. Moreover, if only one of those ranges are overlapping, our query should return a positive result. In SQL, the solution to this problem can be easly determined by an aggregating query.

The reference date range (black), the overlapping date ranges (green) and the non-overlapping date ranges (red).

We begin by expressing the opposite condition, i.e. if date range A does NOT overlap with reference range X. This happens when:
   

tb_DateRanges.DateTo <= @pr_DateFrom

OR

tb_DateRanges.DateFrom >= @pr_DateTo

Now, according to deMorgan's law:
   

not(A OR B)

is equivalent to  

not(A) AND not(B)

which means:

NOT(tb_DateRanges.DateTo <= @pr_DateFrom)

AND

NOT(tb_DateRanges.DateFrom >= @pr_DateTo)

   

By cross-applying this condition for every row of tb_DateRanges, we obtain a sequence of "true" (1) and "false" (0) values. By aggregating this raw result with the MAX() SQL function, we simulate an "OR" condition within the values.
   
This solution admits configurations where the edges overlap exactly. If you wish to exclude that, you may change the non-equijoins operators to ">= "to ">", and "<=" to "<", and viceversa.

A similar pattern can be used for spatial problems. For istance, for three-dimensional objects the same logic can be applied by listing the relation for each coordinate separately.

How to Split a Comma Separated Values Column into Multiple Rows

In many cases a SQL Developer may face a situation in which different values are "wrapped" into the same column. Even though this violates the First Normal Form, it is not uncommon.

Depending on your design considerations, you may want to "unwrap" the column, generating a new table with multiple rows with the same ID -each for every corresponding "splitted" value-, or simply use an additional reference table. The first solution fits well with the fact table of a dimensional data warehouse, while the second is more suitable for a normalized environment, such as a traditional information system.

Here is my definitely not elegant but working solution for a DWH scenario. First of all, you need a splitting table-valued function:

create function [dbo].[ft_Split]
(
    @pr_RowData nvarchar(2000),
    @pr_SplitOn nvarchar(5)
) 
returns @vr_Rtn table
(
    Id int identity(1,1),
    Data nvarchar(max)
)
as
begin
    Declare @vr_Cnt int
    Set @vr_Cnt = 1

    while (Charindex(@pr_SplitOn, @pr_RowData); 0)
    begin
        Insert Into @vr_Rtn (data)
        Select
            Data = ltrim(rtrim(substring(@pr_RowData, 1, charindex(@pr_SplitOn, @pr_RowData)-1)))

        Set @pr_RowData = substring(@pr_RowData, charindex(@pr_SplitOn, @pr_RowData)+1, len(@pr_RowData))
        Set @vr_Cnt = @vr_Cnt + 1
    end
   
    insert Into @vr_Rtn (data)
    Select Data = nullif(ltrim(rtrim(@pr_RowData)), N'')

    Return
end

Now you can write a query with the CROSS APPLY clause, which works very much like a traditional join:

select
    s.ID
    , virtualSplit.data  as SplittedValue

    ...
from
    dbo.tb_CrazyWrappedTable as s
    cross apply dbo.ft_Split(s.CSVWrappedColumn, N',') as virtualSplit

The CROSS APPLY operator was introduced in SQL Server 2005 and in our scenario returns the original table (CrazyWrappedTable) rows matching with the virtual table (virtualSplit) generated by ft_Split.

As you might have guessed, the original table expression is processed first; the right table expression is then evaluated against each row of the left table expression. The final result-set contains all the selected columns from the left table expression combined with all the corresponding columns of the right table expression - and this means the original source table ID will be duplicated! Queries against your resulting fact table should therefore use the COUNT DISTINCT aggregate function - slow and disgraceful, but logically consistent.

To reduce performance issues or simply boost your read-only queries, you should build an index-view upon the original table, based on the two previous code fragments.

MSSQL List of Table's Referencing Objects

Similarly as we did in our Oracle past post, we now propose a simple but useful MSSQL query, that let us to know which objects (for example, stored procedures) reference a specified table (or any other object):


SELECT DISTINCT
   referenced_schema = d.referenced_schema_name
   , referenced_object_name = d.referenced_entity_name
   , referenced_object_type = o1.type_desc
   , referring_object_schema = s.name
   , referring_object_name = o.name
   , referring_object_type = o.type_desc
FROM
   sys.sql_expression_dependencies d
   INNER JOIN sys.objects o ON d.referencing_id=o.object_id
   INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
   INNER JOIN sys.objects o1 ON d.referenced_id=o1.object_id
WHERE
   --d.referenced_entity_name = 'table_name'
   d.referenced_entity_name like '%object_name%'
ORDER BY
       referenced_schema_name
;



But how can MSSQL obtain such a list of referenced table objects, contained inside the code of a stored procedure? Well, everytime you CREATE or ALTER a stored procedure, the compiler dynamically creates a list of syntatic objects, internally implemented as hash table; this list contains also all the code's referenced table, and it´s therefore used to keep the system tables and metadata updated.

LIKE is not always enough

Let´s imagine a situation in which we have, as input, a column s.namelist from a table s containing a comma-separated list of values:

Fabio,Andrea,John,Sara,Sarah...

This could be, for example, a dump from a CSV file or an Oracle External Table, inside a database staging area.

Now suppose we have to perform a filtering operation basing on the corresponding value of another column, s.name; in particular, we would like to identify -and insulate- all the names from our comma-list, whose name is the same in our corresponding s.name column. So, if we have a row like:

  ... | Smith | ... | Sara, Karl, John, Smith, Bill, Smith, Hoppen,... | ....

we wanna obtain:

 | Smith | Smith, Smith |

or just the entire name list.

(this doesn´t seem having much sense, but a similar situation could easly occour in many DWH or data integration scenarios).

Starting with a simple query statement (in this case T-SQL under MSSQL) like this:

select

       s.name

       , a.namelist  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

Doesn´t work correctly. What happen if we have two names like "Sarah" and "Sara", while filtering for "Sara"? With the LIKE operator, we would catch also "Sarah".

The solution is to clearly break and delimiatet the comma list names before performing any additional filtering/denormalization operation, through the use of the REPLACE operator

select

       s.name

       , '|' + REPLACE(UPPER(LTRIM(RTRIM(a.namelist))),' ', '|') + '|'  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

We will then obtain a clear, delimited list of names as |Sara|Sarah|Pippo|... in which we can easly apply the LIKE operator with the name explicitely delimited by the "|" as input

The RTRIM/LTRIM, and the UPPER operators will help us on avoiiding the classical data quality problems involving fields coming from source systems as names, addresses, etc...

Oracle APEX Login Troubles

Oracle Application Express is a web-based, scalable development framework based on the Oracle Database, available from the Oracle 9.2. Oracle APEX follows a thin-client logic, demanding most of the processing and validation operations to the Oracle DBMS itself.

Oracle APEX is also available in the free XE version of the Oracle Database.
   
While providing an Oracle APEX solution to a small set of users, it's of course a good idea to deploy it in the "Embedded PL/SQL Gateway" way, simply following step by step the guidlines as specified in the Oracle official documentation.

In this way however, after a successful installation and while logging into your new APEX system, you could face the presence of the a dialog box asking a password for the "XDB" user:

The server xxx at XDB requires a username and password.

   
followed by the same request, but for the "APEX" user:

The server xxxxx at APEX requires username and password

This is why the Oracle XDB HTTP Server is by default configured to use the same TCP port of the APEX Listener, the 8080. If you do not want to use a different port or disable the XDB HTTP Server completely, here is a simple workaround.

First of all, we unlock the XDB user:

ALTER USER xdb ACCOUNT UNLOCK;

...and we set a new password:

ALTER USER xdb IDENTIFIED BY xdb_new_pwd;

We make the same for the ANONYMOUS user (as described by the Oracle documentation) and for the APEX_PUBLIC_USER.

Oracle Automatic Startup at Boot Time - Linux Script

If you are frequently working with the Oracle technology, in most of the cases your Oracle system will run over a Linux machine, for example based on a Red Hat compatible distribution (Red Hat Enterprise, Fedora, or CentOS).

In fact, if your purpose is to test, develop or evaluate a solution based on the Oracle stack, you can freely rely on a virtual machine based on the free Oracle Virtualbox (in case, do NOT forget to install the VirtualBox Guest Additions!), the CentOS distribution, and the Oracle Database Enterprise Edition (or Standard, or even XE - it depending on your purposes).

Unfortunately, by default both Oracle Enterprise and Standard Edition don' t set any startup script in the /etc/init.d/ directory at installation time. No panic: we can create a custom one and manually deploy it.


First of all, we check our /etc/oratab file, whose row follows the following syntax:

istance_sid:oracle_home:[Y|N]

The "oratab" file is automatically created by the Oracle installer, and it's used by the "dbstart" and "dbshut" scripts to figure out which database istances have to be start up or shut down. In particular:

• istance_sid: System ID (SID) of the desired oracle instance;
• oracle_home: ORACLE_HOME directory associated to the specified istance;
• [Y|N] simply indicates if the istance should automatically start at boot time (Y="yes", N="no").

Obvioulsy, we set as "Y" every database instance we wanna automatically to be started at boot time.

For example, a production /etc/oratab file could look like something like this:

orcl:/opt/oracle/product/11.2.0/dbhome_1:N
dev:/opt/oracle/product/11.2.0/dbhome_1:Y
test:/opt/oracle/product/11.2.0/dbhome_1:Y
prod:/opt/oracle/product/11.2.0/dbhome_1:Y

Now we can create our new init script. We create a new, empty file called "oradb" and we add the following lines:

#!/bin/sh
# chkconfig: 345 20 80
# description: counter daemon
# processname: counter
# /etc/rc.d/init.d/oracle
# Description: Automatically starts and stops the Oracle database and the listeners.

Note: the line "# chkconfig: 345 20 80" is absolutely necessary to make the script compatible with the Red Hat service management subsystem and should not be skipped!

The following code implements the "body" of our init script:

case "$1" in
  start)
        echo -n "Starting Oracle Databases: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbstart $ORACLE_HOME >> /var/log/oracle
        echo "...done."
        echo -n "Starting Oracle Listeners: "
        su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        touch /var/lock/subsys/oracle
        ;;
  stop)
        echo -n "Shutting Down Oracle Listeners: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c "lsnrctl stop" >> /var/log/oracle
        echo "...done."
        rm -f /var/lock/subsys/oracle
        echo -n "Shutting Down Oracle Databases: "
        su - oracle -c dbshut $ORACLE_HOME >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  restart)
        echo -n "Restarting Oracle Databases: "
        echo "---------i-------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbshut $ORACLE_HOME >> /var/log/oracle
        su - oracle -c dbstart $ORACLE_HOME >> /var/log/oracle
        echo "...done."
echo -n "Restarting Oracle Listeners: "
        su - oracle -c "lsnrctl stop" >> /var/log/oracle
        su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        touch /var/lock/subsys/oracle
        ;;
  *)
        echo "Usage: oracle {start|stop|restart}"
        exit 1
esac

As you may notice, we make use of the Oracle standard "dbstart" ("dbshut") and "lsnrctl" utilities.

Note: in order to make this script correctly work, the "dbstart", "dbshut" and "lsnrctl" utilites must be present in the PATH environmental variable! You can check it by typing the command:

# env | grep PATH

If the PATH environmental variable is not correctly set up, you can manually do it using:

# PATH:$PATH:oracle_utils_path; export PATH

You can locate the utilities using the command:

# updatebd; locate dbstart

or

# find / -name dbstart

Adding this line to the /etc/profile file will make the correct PATH variable also available at boot time.




Now you must deploy the script in the /etc/init.d/ directory. The script should be own by the "root" user:

# chown root:root /etc/init.d/oradb

and should be granted with the "775" permission:

# chmod 775 /etc/init.d/oradb

Eventually, to make Oracle automatically start at boot time type:

# chkconfig --add oradb

Restart your system, set the ORACLE_SID to your database SID name, and test the script it via SQLPLUS, TOAD, or SQL Developer. You can (as root) also manually call the script:

• # /etc/init.d/oradb start: to make the database istances start;
• # /etc/init.d/oradb stop: to make them stop;
• # /etc/init.d/oradb restart: to make them restart.

Precision of a Timestamp in Oracle

Sometimes in Oracle you could need to explicitly specify the precision of a TIMESTAMP field. In case, you should use the "DUAL" pattern.

Let' s give an example:

SELECT to_char(CURRENT_TIMESTAMP, 'YYYYMMDD HH24MISS.FFN') FROM DUAL;

where "N" refers to the desired precision (number of digits after the comma). We use an explicit cast using the "TO_CHAR" function, since we wanna see the real output - and not the one setted in the IDE environment (TOAD, SQLPLUS, or SQL Developer).

So, for istance:

SELECT to_chat(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

will result in something like:

20120326 114347.215

and

SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF9') FROM DUAL;

in:

20120326 114551.183876000

Oracle Stored Procedures with Custom Java Code

Togheter with the Oracle´s official Structured Query Language PL/SQL, and the open-source statistical analysis language bundled in the Oracle Data Mining package, R, we also have the possibilty to use and integrate another great technology in our database: Java, the world's number one programming language in enterprise environments.

The usage of Java inside stored procedures in pretty much simple: you just need to create a standard stored procedure "linking" to your custom java code. Here is a short example:

CREATE OR REPLACE FUNCTION    your_function_name( p_arg1 IN VARCHAR2) RETURN NUMBERAS   LANGUAGE JAVA   NAME 'your_java_function_name(java.lang.String...) return integer';

public static int your_function_name(String separator, String args) {     ...your java code...} 

Getting the List of Referring Tables to a Table

If you wanna know, in Oracle, all referring tables to a certain table here is a simple query:

SELECT  bs.OWNER , bs.TABLE_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf WHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='YOUR_TABLE_NAME' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME;

If, in addition, you wanna also know the referring column names:

SELECT  bs.OWNER , bs.TABLE_NAME , cl.COLUMN_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf  , USER_CONS_COLUMNS clWHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='T_CL_KUNDE_D' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME;

Using the same script of the previous post, we can implement a fast script for disabling all the foreign key contraints referring to the desired table:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN
  FOR i IN
(
SELECT
bs.CONSTRAINT_NAME "constraint_name"
, bs.TABLE_NAME "table_name"
FROM
USER_CONSTRAINTS bs
, USER_CONSTRAINTS rf
, USER_CONS_COLUMNS cl
WHERE
bs.CONSTRAINT_TYPE='R'
AND rf.TABLE_NAME='your_table_name'
AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME
AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME
)
  LOOP
   v_stmt := 'ALTER TABLE your_table_name DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
   EXECUTE IMMEDIATE v_stmt;

   dbms_output.put_line('disabled: ' || i.constraint_name || ' from table: ' ||  i.table_name);
  END LOOP;
END;
/

Temporary Disabling the Foreign Keys in Oracle

Foreign Keys are the most widely used constraint types in OLTP systems, since they ensures that a value of the referencing column cannot contain a value that does not exist in the corresponding column of the referenced table. Foreign Keys do not guarantee absolute data quality: rather, they guarantee... referential integrity, which is a good starting point for obtaining a good level of data quality and reliability.

OLTP systems are always accessed in a "transactional" way by multiple users: each data entry is inserted row by row, and should therefore be controlled  one by one, togheter with the help of the other database constraints (or the application level filters, if the system is ill-designed).

In DWH environments, however, things are different. First, the data integrity is - or should be- guaranteed by the automatic ETL process; exceptions, error or any data inconsistency has to be automatically managed - and reported - direct at etl level. Sometimes one of the goal of the ETL process is also to find data anomalies and problems: this can be part of the DWH structure itself, for example with the use of an Audit Dimension or Fact.

Second, the presence of explicit foreign keys (logically we ALWAYS have referencial integrity between facts and dimensions) load performance is brutally degraded: a cross-table check is triggered for every insert/update/delete statement, resulting into an unacceptable overhead.

Third, during the developement of a DWH System operations like table truncating and changing are frequent, thus causing headaches when making DML or DDL changes.

If you are not the Project Manager, and you cannot decide to drop -once for all- the explicit and annoying foreign keys from the database, you can temporary disable them by using a simple procedure similar to the one contained in the following anonymous block:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('disabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

Similarly, to re-enable the foreign keys:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' ENABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('enabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

As always, do not forget the backslash "/" at the end of the anonymous block.

Date Comparison in Oracle

If you wanna compare (equal) two dates in Oracle, you can' t simply use the "=" operator in the query clause. Instead, you can perform a simple string comparison through the TO_CHAR function:

SELECT   dt1   , dt2FROM   src_tableWHERE TO_CHAR(dt1, 'YYYY-MM-DD')=TO_CHAR(dt2, 'YYYY-MM-DD');

Working with Oracle Sequence Objects in Talend Open Studio

In case you need a column that contains unique, sequentially generated numbers, you can realize autonumber fields (also known as "auto-increment" fields) in RDMBS engines like Oracle and PostgreSQL by using sequence objects. This is often the case of the primary surrogate keys in DWH environments.

In Oracle, a sequence object is a separate structure specifically created to generate sequential values; it can be efficiently and concurrently accessed by more than one process at time, and the same sequence can be used for one or more tables.

If you are using an ELT tool like Oracle Warehouse Builder, the access and increment of the sequence object into a mapping is pretty much easy: we simply drag and drop the sequence object itself, and connect the "NEXTVAL" field to the desired column of the target table.

Mapping of a sequence object in Oracle Warehouse Builder.

With our favourite Data Integration tool, however, things are a bit more complicated. Suppose we don' t wanna delegate the "autoincrement" logic to the ETL process, and that we simply wanna insert new rows into one table, letting the RDBMS manage the sequential logic by itself. In this case, we would obviously make use of a sequence object:

DROP  SEQUENCE seq_name;

CREATE  SEQUENCE seq_nameSTART WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;

The problem is that, with Talend Open Studio, we don't have a way to directly access the sequence object inside the ETL flow - and we can't therefore implement any incremental logic at ETL level; neverthless, the sequence object doesn't simply increment by itself - you have to access it in some way, at application level.

We can' t directly access an Oracle sequence object in Talend Open Studio.

The solution is to implement a trigger object: each row in the target table will trigger an increment of the sequence object, whose value will be stored in the "autoincrement" column itself: 

CREATE OR REPLACE TRIGGER  trg_nameBEFORE INSERT ON  table_name FOR EACH ROW WHEN     (new.autoincr_col IS NULL)  BEGIN    SELECT       seq_name.NEXTVAL    INTO         :new.autoincr_col    FROM        DUAL;  END;/

The trigger object makes slower the loading process - as a matter of fact each insert row triggers a different operation. However, it perfectly works in case of small amount of insert data.

Do not forget to add the backslash "/" at the end of the trigger creation script.