Denodo is a Data Virtualization environment where the entire process of ETL is reduced to a configurable set of layers containing various views. These views depend on table definitions which reside in source systems. The upper layers of views should represent the business needs and are in need of solid data modeling. These upper layers of views in Denodo are like an interface. The most common usage is in a top-down design where you first define the fields and later associate the implementation(data) of the interface.
Denodo supports scripting for which a CaseTalk plugin is available. Below you'll find the example model, the relational diagram and the Denodo Script as an example.
Generated Denodo VQL
ENTER SINGLE USER MODE;
# ########################################################################
# I18N MAPS
# ########################################################################
DROP MAP i18n IF EXISTS nl_euro_custom CASCADE;
CREATE MAP i18n nl_euro_custom (
'country' = 'NL'
'currency' = 'EURO'
'currencydecimalposition' = '2'
'currencydecimalseparator' = ','
'currencygroupseparator' = '.'
'currencysymbol' = '€'
'datepattern' = 'dd-MM-yyyy HH:mm:ss'
'doubledecimalposition' = '2'
'doubledecimalseparator' = ','
'doublegroupseparator' = ''
'language' = 'nl'
'moneypattern' = '###.###.###,##'
'timepattern' = 'DAY'
'timesubtypepattern' = 'HH:mm:ss'
'timezone' = 'PST'
);
# ########################################################################
# DATABASE
# ########################################################################
DROP DATABASE IF EXISTS casetalk_denodo;
CREATE DATABASE casetalk_denodo 'CaseTalk Denodo';
CONNECT DATABASE casetalk_denodo;
# ########################################################################
# FOLDERS
# ########################################################################
CREATE OR REPLACE FOLDER '/CaseTalk';
CREATE OR REPLACE FOLDER '/CaseTalk/Student-Project-El_GLR';
CREATE OR REPLACE FOLDER '/CaseTalk/Student-Project-El_GLR/Interface';
CREATE OR REPLACE FOLDER '/CaseTalk/Student-Project-El_GLR/Association';
CREATE OR REPLACE FOLDER '/CaseTalk/Student-Project-El_GLR/Implementation';
# ########################################################################
# INTERFACE VIEWS
# ########################################################################
....
CREATE OR REPLACE INTERFACE VIEW v_Person (
first_name:text, # first name
surname:text, # surname
Gender:text, # Gender
Birthdate:date # Birthdate
)
FOLDER = '/CaseTalk/Student-Project-El_GLR/Interface'
DESCRIPTION = '"There is a student <first name> <surname>."\n"<first name> <surname> is <GenderType>."\n"<first name> <surname> is born on <Date>."\n'
SET IMPLEMENTATION c_Person;
CREATE OR REPLACE INTERFACE VIEW v_Preferences (
first_name:text, # Student/Person/first name
surname:text, # Student/Person/surname
Project:text, # Project
ordinal_number:text # ordinal number
)
FOLDER = '/CaseTalk/Student-Project-El_GLR/Interface'
DESCRIPTION = '"The <ordinal number> preference of student <first name> <surname> is project <project code>."'
SET IMPLEMENTATION c_Preferences;
....
# ########################################################################
# IMPLEMENTATION VIEWS
# ########################################################################
CREATE OR REPLACE VIEW c_Person (
FOLDER '/CaseTalk/Student-Project-El_GLR/Implementation'
DESCRIPTION = '"There is a student <first name> <surname>."\n"<first name> <surname> is <GenderType>."\n"<first name> <surname> is born on <Date>."\n'
PRIMARY KEY(first_name, surname)
AS
SELECT
'Peter' AS first_name,
'Johnson' AS surname,
'male' AS Gender,
'01-01-1960' AS Birthdate
FROM DUAL()
UNION
SELECT
'John' AS first_name,
'Hartman' AS surname,
NULL AS Gender,
NULL AS Birthdate
FROM DUAL();
....
# ########################################################################
# VIEW ASSOCIATIONS
# ########################################################################
CREATE OR REPLACE ASSOCIATION FK1_Student_Person REFERENTIAL CONSTRAINT
FOLDER '/CaseTalk/Student-Project-El_GLR/Association'
ENDPOINT Person_Student i_Person (1)
ENDPOINT Student_Person i_Student PRINCIPAL (1)
DESCRIPTION = ''
ADD MAPPING i_Student.first_name = i_Person.first_name
ADD MAPPING i_Student.surname = i_Person.surname;
For more technical documentation regarding the creation of virtual views visit: www.denodo.com
