Letter to a Programmer (just a moment to forget our work)

I can still see his tired eyes, behind his lenses,thick two fingers.

You gave me the light, happy.

I've grown, statement after statement, with passion and pride.

Each "if" any "then" every "else", each "loop", studied for hours.

Not an ounce of fat.

I ran very fast. Without ever falling. Without a moment's pause.

I was your only one. You were the only one, my creator.

Now you're not there anymore.

I do not know where you are.

"Relocation", some call it. Others "Industry Standard"

I just know that one day, after startup, there was another person.

A stranger.

Without a soul.

He scrolled my body with boredom.

Then one day, suddenly.

Without trying to understand.

Without trying to see through me.

He mutilated me.

He started to tear pieces of my body. Necessary for You.

And I started to stumble, to slow down, to suffer.

Now around me, there are only anonymous, aseptic, redone, fat, cloned and arrogant programs.

Suitable (or deformed) programs, badly, for each situation.

They run slow, weighed down by pounds of unnecessary lines of code.

I know, my programmer, that I am still in your heart.

That you dream me.

That you have not forgotten me.

But so, without your love, without your father's eyes,

I cannot continue.

I do not want to continue.

It's time to die.

It's time to abort.

Data Warehouse: Naming convention techniques (part 2)

Introduction

In the part 1, the application of the Naming Convention techniques had as its privileged object the tables, certainly the basic entities of a information system. We have defined a name to these entities in their easier form (table), in their aggregate form (materialized view or summary table) in their logical form (view).
It was emphasized that these techniques can be applied to any logical/physical entity of a Data Warehouse. So, I wish to complete these thoughts having in mind three targets:
 

Completeness: The tables are basic, but alone, do not constitute a Data Warehouse. There must be access rights to view them, programs must exist to load them ,must exist indexes to speed their access, there must be constraints to ensure data integrity. Even programs, rights, indexes and constraints must be created by respecting the Naming Convention. The tables are made of attributes.Even the attributes have a name. We will speak.

Pragmatism: Only seeing apply the techniques described in a real case, we can recognize the utility, then
we will examine and we will give a name to all the other entities in the game, using the sample Data Warehouse.

Knowledge: Some of the entities that will be subject to naming are specific to Oracle and this is a good
opportunity  to give them a brief description. The choices made for the Naming Convention are only guidelines. They are not a dogma. The convention can be discussed and changed according to our needs and to our particular view of the system. The main objective was to put attention to the importance and usefulness of the Naming Convention. 

Another point I wish to emphasize is that the convention is "Database Administator oriented" and not "Business oriented." It means that the names chosen, for example, for the tables, will be physical ones, and the names will be those that only the DBA sees. The "rest of the world" should not see those names,but the "logical" names  that are  filtered by synonyms and/or views.

The users of the Naming Convention

Based on some useful questions received, I want to clarify this point. Take for example the EDW_COM_CDI_CUST_DIT entity. This entity represents the customers (CUST) of the dimension table (DIT) of the conformed dimensions section (CDI) of the common area (COM) for all entities, of our Data Warehouse (EDW). Using the Naming Convention, the content of this entity appears clear to any DBA, also to who, for example, inherits the management of a Data Warehouse that does not know. (try to think if the table had been named A01DWCST).

The EDW_COM_CDI_CUST_DIT entity is seen and handled only by the DBA. In my view, the only other users who can see the entity (and only what we need, that are, usually, facts and dimensions) are the business-area builders,  by means of an administration module that is part of the front-end tool (eg Oracle Business Intelligence).
These users do not need to see the EDW_COM_CDI_CUST_DIT physical name, but a view/synonym (logical name) as,for example, CUSTOMER_DIT. If we had the foresight to make unique the last two
components of the name, the rest of the world will see an entity name much shorter, simple and near to its business logic.

The Naming Convention of the table attributes

As we know from the theory of relational databases, the table attributes are a set of specific characteristics of the various entities that define the logical model. Since we have defined a Naming Convention for the entity, it is necessary define a Naming Convention for attributes.
The paradigm that underlies the Naming Convention of the table attributes can be summarized in the following formula:

       <attribute name> = <logical name>_<type code>

For them, the name is very simplified because their logical context is already structurally defined by the table to which they belong. Into the data dictionary tables of an RDBMS such as Oracle,you can locate all the attributes and their tables associated. So an effective Naming Convention will be very useful in the research of all the attributes with certain common characteristics. Here are some examples from personal experiences.
 

In a Data Warehouse for a bank, was born the need to change the size of the currency numbers fields from two to six decimal places. The need was clearly linked to rounding problems. Hundreds of tables with different columns had to be involved in the modification. Have adopted the Naming Convention to typify all the columns of currency amounts with <logica namel>_AMT was decisive. Has allowed us to generate  a script that, accessing to the data dictionary tables ,it made dynamically the change of structure of all and only the affected columns.
 

Some ETL and reporting tools allow us to identify automatically all the descriptive columns of alphanumeric codes that will be displayed in the output: the interface of the tool will then use a clause "like" to locate the fields. If you use the standard to name the description of all the code columns with  "*_DSC, this will allow you to take advantage of this feature of the tool, and it will not need to specify one by one all the fields. Now we see some examples of the <type code>

• COD - Code - Alphanumeric code: This is the classic code that is associated with a description and a domain. It may be a customer number, an order type, an account status, etc.. I suggest to deal all the numeric codes as alphanumeric codes.
• DSC - Description - the code description is always the description associated with the code that is used in the reporting tools and in front-end. It is a design choice understand if only a single description is sufficient or define a short description (SDS which stands for short description) and a long description (LDS which stands for long description). It often happens that the user requires the concatenation of the short description with the code (CSD which stands for code plus short description)
• AMT - Amount - Always indicates an amount.
• QTY - Quantity - Indicates a quantity in pieces, weight or in some other unit of measure.
• KEY - Always indicates an artificial key. A column with this type must exist in all dimension tables and in the corresponding columns of the fact tables.
• DTS - Date Stamp - Date: indicates a date in the format Oracle, that is inclusive of the time (hours, minutes, seconds)
• FLG - Flag - It is always a binary field, ie that it may be only 0 or 1.
• TXT - Text - Field of generic text.
. YMD - Day in the YYYYMMDD format

The other entities of a Data Warehouse

Identify all the main entities or structures of a Data Warehouse, is not an easy job without forgetting that in Oracle there are over 30 different types of structures.
Eeach RDBMS has its own requirements and peculiarities and would be long-winded and useless to try to give a Naming Convention at all. So we will focus on the main entities, almost always present, leaving to the reader the application of the learned techniques for the remaining ones. Here is the list of entities, subject of our next guidelines.

• Index
• Tablespace
• Datafile
• Integrity Constraint
• Role
• Package

The Naming Convention of the indexes

As the Naming Convention is linked to the type of index, I will give a brief overview of the most common types of indexes. They typically cover 90% of the need for a Data Warehouse.
As everyone knows, the indexes are data structures that are created on one or more columns in a table to optimize the performance of access to data; the goal of an index is therefore to provide an immediate physical access to the rows of the table that contains the values. In Oracle, but there are also in other RDBMS, the indexes most used are the classic B-tree indexes, the local or global bitmap indexes, and the function index. The paradigm that underlies the Naming Convention of the indexes can be summarized in the following formula:

<index name> = <project code>_<area code>_<section code>_<logical name>_<index type>
 

The Naming Convention of the indexes will then have the same syntax of the entities, but will only change the tipology. In practice its name is identical to that of the table on which it is created except for the suffix. What follows is a list of the indexes applicable to a sales fact table. X indicates a progressive number.

• EDW_DM0_SLS_LBx: Represents a local bitmap index.
• EDW_DM0_SLS_GBx: Represents a global bitmap index.
• EDW_DM0_SLS_NUx: Represents a generic btree index not unique
• EDW_DM0_SLS_UIx: Represents a generic index btree unique
  EDW_DM0_SLS_FUx: Represents a function index

 

The Naming Convention of integrity constraints

The Integrity constraints allow us to associate some rules to the Data Warehouse tables, to order to prevent the introduction of outliers or non-compliant values.
It is needless to emphasize the importance that these rules have in the design of the system. Dispelling immediately a myth that often we hear: constraints on tables encumbers the data manipulation operations. Nothing could be further from the truth. Let's see to make things clear.

1. Is obvious that the introduction of an integrity constraint slows down the processes of manipulation of the table, but its overhead is minimal and, as a percentage, its weight in the loading process will be negligible. I remember you, however, that the constraints can be turned off before the data loading and reactivated immediately after loading.
2. If implemented programmatically in your application, the constraints will never be so complete, secure and manageable as those defined automatically by the RDBMS.
3. Always enter the integrity constraints, even if the source systems are in turn RDBMS with the active constraints. Do not to trust is better: try to think of what it means to have discovered duplicate keys after loading a few months of data and be in production.
4. The integrity constraints are necessary to activate the query rewrite in Oracle, ie its internal functionality, which is able to rewrite a query based on the fact table, and redirecting it on a materialized view. Without the integrity constraints between the fact table and its dimension table this mechanism will never work.

The paradigm that is the basis of the Naming Convention of the integrity constraints can be summarized in the following formula:

<constr. name> = <project code>_<area code>_<section code>_<logical name>_< constr.  type>
 

The following is a list of integrity constraints applicable to a fact table of sales. X indicates a progressive number.

•  EDW_DM0_SLS_Nxx: To indicate the requirement to have always a non-null value for a field. XX is a sequential number for each column in the table that requires the constraint.

• EDW_DM0_SLS_PK1: To indicate the primary key.

• EDW_DM0_SLS_UKx: To specify a unique key.

• EDW_DM0_SLS_FKx: To specify the foreign key. If you think that the number of foreign key can be higher of 9, use the convention Fxx

• EDW_DM0_SLS_CKx: To indicate a more complex constraint based on some conditions. (for example a start date should always be prior of the end date)

 

The Naming Convention of the tablespaces

The tablespaces are logical drives that connect objects with common logical characteristics. Each table, materialized view or index always has a table space that contains, either expressed explicitly inside the script of creation, or implied, that is (the default), the tablespace of the user who created the object.
In turn each tablespace are associated with one or more datafiles. The paradigm that underlies the Naming Convention of the tablespace can be summarized in the following formula:

<tbs name> = <project code>_<area code>_<section code>_< tbs  type>

where the section code  and type code are optional; In fact, the technique to be applied in this case, is not unique, but depends on the size of the objects that constitute the tablespace. Referring to our example of the sales , we have the following:

• EDW_COM: Tablespace of common entities. In the area that we have defined COM, there are definitely tables and indexes of little size, compared to data from other areas, so will be sufficient the project code plus the area code.

• EDW_STA: Tablespace for temporary objects. Also in this case, the staging tables, which are only transient and of small dimensions, may stay into only one tablespace.

• EDW_DM0_SLS: Tablespace objects from the sales data mart. If the total space occupied by these objects is limited, this may be sufficient only one tablespace. (limited,for me, is under 8 Gb). If the volumes are higher, it can be used DFT, IFT, DMT and IMT, ie fact table, index fact table , materialized view and index materialized view.

In cases of VLDW (Very Large Data Warehouse) is conceivable a tablespace for indexes, and a tablespace for the data, of each table.

The Naming Convention of the datafiles

The next considerations are valid if you are not using the Automatic Storage Management feature of Oracle.
As stated in the previous paragraph, the tablespace is made up datafile. At the time of the creation of the tablespace, you must already know about, the total space occupied by the objects that will stay in the tablespace, because you will be asked to allocate physical space. 

Let's forget about "to drive" the location of the data files on some disks of the Database Server. Now the virtualization techniques of physical space allow us to see a single disk. My advice is to divide the space occupied by the objects of the tablespace in a number of different files, of size not too high, for their better management. The paradigm that underlies the Naming Convention of the datafile can be summarized in the following formula:

<datafile name> = <tablespace name>_XX.<file type>

In this case, XX is a progressive number, the type 01,02, .., while the file type is usually fixed to DBF (Data Base File). Of course, instead of DBF you can also associate other acronyms, it is important that all the datafiles follow the same logic.

The Naming Convention of the roles

In a Data Warehouse, tables and their structures, must be aggregated to be accessible to users for data selection. I spoke at the beginning of the users of the Data Warehouse. I am aware that often the reality is more complicated, and there will always be users who access or wish to access the data directly. For this reason I speak about roles.
Provide access, means giving the grant to the entities. Because users generally have access to one or more data marts, the best way to simplify the management of access rights is to group all accesses to the data mart using roles. (When I speak about Data Mart,that is logical, I intend the fact table and the related dimension tables).
So the grant does not associate a user with a structure, but a user with a role. Appears immediately clear that the Naming Convention of the roles is closely connected to the data marts, ie with the logical partitioning at the section level . The paradigm that underlies the Naming Convention of roles can be summarized in the following formula:

<role name> = <project code>_<area code>_<section code>_<type code>

The type code may be optional, as users of the Data Warehouse will access always with "SELECT" query (I hope !); this does not mean that we cannot use "_SEL" to indicate the role of read-only access, and with "_UPD" the role of insert, update and delete. The next figure shows a summary of the techniques applied so far.

The Naming Convention of the packages

Packages are libraries of PL/SQL code. In Oracle, PL/SQL (procedural language sql) is the internal database language ,although you can write programs in Java, C, or other programming languages, callable from  PL/SQL modules. 

These modules may be procedures or functions. In Oracle, to use the package is crucial: I highly recommend that all modules necessary for the loading process are contained into packages. The advantages of their use are numerous, and I will mention only two:

• Modularity: organize your programs in an orderly manner according to the context in which they operate is essential for anyone that work, or will work, on the project.

• Performance: when you call a module of a package for the first once, the entire package is loaded into memory. Subsequent calls to other modules of the package doesn't require disk access.

Returning to the Naming Convention, this means that the procedure which is used to load the fact table of the sales or the aggregate monthly one, must be contained in the package that has the same name (if possible) of the target table.
If this procedure uses functions or procedures of the generic Data Mart of the sales, such a procedure should be contained in the package that has the same name of the corresponding section. The logical process will continue until reaching the common procedures to the entire Data Warehouse (for example, a function that returns me the difference of two dates for calculate the delta). Next figure shows an example of such encapsulation. 

 The Naming Convention to be adopted for the package is very flexible and may be in its most extensive form:

<pkg name> = <project code>_<area code>_<section code>_<logical name>_<type code>
as well as in its simplest form:
<project code>

The presence of the logical name and of the type code can be usable in complex systems where the number of package tends to be very high.
Do not forget that the type code must give value added to the semantics of the name. Add as _PKG type code does not create added value, as this information is obtainable from the Oracle catalog with a simple select statement.
If you decide that all modules that recall Java procedures in a certain section are within a specific package, then "_PKG" and "_JPK" will definitely  effective choices. In the case where, as in Oracle, it is not possible to have the same name for a package and a table, the use of "_PKG" Will be mandatory.

Conclusions

We have really reached the end of this short journey within the Naming Convention techniques . What is mentioned, is not certainly exhaustive of the many possible  applications of these techniques. Each of us, on the basis of own experience, can partition and can codify according to their needs and according to your own intuition. 

Indeed it is not  important the choice by which  you partition or codify the system, but it is important follow a method of standardization, in the most rigorous
way. An effective Naming Convention certainly provides all the tools necessary to keep under control soon the system, in terms of knowledge, management and maintenance

(you can download this article from slideshare: http://www.slideshare.net/jackbim/recipes-8-the-naming-convention-part-2

Micro ETL Foundation - A messaging system for Oracle Data Warehouse

Introduction

A goal of what I have called the Micro ETL Foundation (MEF) is to provide some simple and immediate solutions to the need of a Data Warehouse.
What could be more simple (and necessary) of a log message? It may seem strange to devote an entire article to describe how to report the message "Hello world". A message, famous for all the programmers of the computer world. But we find that what appears simple is actually a bit more complicated.
If we take the first paragraph of any programming book, (eg. java) the solution is expressed with a single line:

         System.out.println (\ Hello Word! ");
 
If we use another programming language (eg. PL/SQL Oracle), the solution is again expressed in a single line:

         Dbms_output.put_line ('Hello World');
 
What I want to emphasize is the incompleteness of the received message; In fact, the complexity lies not in the message itself: at the end it is a simple function call.The complexity is the lack of context. Complexity is the metadata.
Suppose that at this time are running more loading processes. The fact of receiving a warning or alert message is not enough.
What I want to know is when the message was sent, which job was running, how much time has elapsed since the previous message, which was the current procedure at the time of the message.
We find out that answer to all these questions is no longer trivial. When we go out from theoretical examples printed on the books and enter into the reality of the daily work,everything becomes more complicated.
We will see the theory and practice, then the code, which will give us an answer to contextual needs described above.
And the solution will be (surprise!), again expressed in a single line. The programming language used is the Oracle PL/SQL (simply because it is widely used in the Data Warehouse world and then I turn to a wider audience), but the techniques exposed are easily playable on any other RDBMS.
I forgot: we also see how to ensure that the message is sent via e-mail.
This, however, is not just for programmers. It 'an article that shows all the hard work behind a simple log message. Just imagine the work that is behind the complete loading process of a big Data Warehouse.

Definitions

Naming Convention
I spent a lot of words on the importance and need to always use a naming convention for the Data Warehouse projects. I summarize the concept. 
In general, the naming convention is the method by which you choose to assign a name to the various entities of the system being designed. This method should produce a name that must be able to represent immediately the semantic nature of the entities that we will use. It does not matter if they belong to a Data Warehouse system or to an operational system.

ETL Process
The ETL process is the set of programs that load data from external systems into the Data Warehouse tables.
Since this set can be very complex, it is extremely important to have a messaging system that gives me the
most information possible about the process.

Job
A job is a logical unit that describe a very specific task, such as loading a dimension of analysis or of a fact table or both.
A set of job-related between them is in turn a job that is part of a schedule.
It is activated at a preset time. In turn, a job consists of simple and sequential processing component that we call units.

Unit
The unit is the elementary processing component (so it is code) which, in turn, can call other procedures and functions that can be defined in a generic way, modules.

Execution
A job usually runs at night, but it could also run several times a day.
Each job execution is a run that has to be identified with a sequential number, which can be defined as exec counter.
For the moment are sufficient these definitions very short. Later we will analyze in more detail.

Requirements

The requirements are very simple. To have a basic messaging system that signals everything that it is useful to
monitor the execution of the ETL process.
It is a task of the designer to decide the content of these messages, which may simply be informative, as the number of rows processed, or of particular attention, such as the identification of errors or anomalies. In these cases it should be possible to send the message via e-mail.
The system should not merely store all messages generated, but must also have the context information.
The context is described in the paragraph relative to the definition of the ETL process.
More context information we insert, and more we can control the system and we will be more efficient and faster to resolve the problems.
I suppose a minimum knowledge of Oracle and SQL and PL/SQL languages.

Design

The design of the messaging component of the Micro ETL Foundation (MEF), consists of 3 tables, 2 sequences and 1 package.
We can see them as the basic components. The minimum necessary for the other MEF components that we will use in the future. The naming convention used is a smaller version of the one to be applied to all objects of a Data Warehouse. [See http://www.slideshare.net/jackbim/recipes-6-of-data-warehouse-naming-convention-techniques].
But it would be unreasonable to apply to MEF the same logic that is used to organize the hundreds of tables typical of a large Data Warehouse. Also we can use MEF in any type of project. We will use this simplified structure:

<entity name>=<area code>_ <type name>_<logical code>

The basic package, being common to all, will be further simplified by eliminating the <logical name>.
The area code is "MEF".
The table MEF_CFT is the table of system configuration.
The table MEF_EMAIL_CFT is the specific configuration table for e-mail addresses, the table MEF_MSG_LOT is one that will keep the text messages.
The sequence MEF_MSG_SEQ gives a sequence number to each message.
The sequence MEF_RUN_SEQ is used to numerically identify each job execution.
The Oracle package MEF is a library of programs that manages those objects.
All scripts for creating objects, can be downloaded from slideshare that show in a pragmatic and fun way the system [http://www.slideshare.net/jackbim/recipe-7-of-data-warehouse-a-messaging-system-for-oracle-dwh-2].
The scripts are very minimal, with only the main structures, leaving the reader with the completion of all other
accessory structures such as indexes, constraints, etc.. 
The tables will be created in the default tablespace, but you should always create ad-hoc tablespace.

The MEF_CFT table

This table contains general information about the MEF and the Data Warehouse.Other columns will be added in the future (or on your choice)

prj_cod: Code of the Data Warehouse project

user_cod: name of the Oracle user for ETL.

email_srv_cod: E-mail Server. Every corporate has always a server for managing e-mail, indicate here that server.

mef_root_txt: Path of the folder of MEF scripts.

mef_dir: Oracle directory pointing to mef_root_txt

The MEF_MSG_LOT table

This table stores all log messages that are sent by loading process.

seq_num: Sequential number of the message. It is obtained from an Oracle sequence.

day_cod: Time stamp of the message insert in the format YYYYMMDD.

sched_cod: The identifier for the schedule to which the job belongs.

job_cod: job identifier. It is a logical entity in the sense that we think of it as the launch of a list of processing units.

unit_cod: Identifier of the processing unit within the job. We can think of it as a procedure or a function of the Oracle package.

module_cod: module Identifier. A unit, though complex, can in turn call the sub-routine or subfunctions, ie modules. In this case, it is interesting to know this detail.

rows_num: Number of rows processed. Typically, this field is not set, but if we want to report the number of rows, for example, inserted into a table, we also have this information.

line_txt: Message text

cline_txt: Message text in CLOB type.

ss_num: Number of seconds that have elapsed since the previous message. This information, together with the next two, provides a summable data. If we wanted to know how long it took all the statements of a certain kind, we would be able to calculate.

mi_num: Number of minutes that have elapsed since the previous message

hh_num: Number of hours that occurred since the previous message

elapsed_txt: Time elapsed since the previous message in the format HH24:MI:SS

stamp_dts: Time stamp insertion of the message.

exec_cnt: The identifier for the execution of the job. Every run of a job should be characterized by a number,
in turn, extracted from an Oracle sequence.

user_cod: Oracle user who posted the message. It is setted automatically by a session variable. It can be useful in cases where multiple users contribute to the loading process(not recommended)

The MEF_EMAIL_CFT table

This table configures the email addresses.

email_cod: Code to identify a group of recipients.

from_txt: Sender of the message. This name will appear as the sender of the e-mail message. Do not use special characters, nor the blank between words. Eg. not set "Administrator ETL" but "Amministratore_ETL"  otherwise you get a run-time error message like:

ORA-29279: SMTP permanent error: 501
5.5.4 Invalid arguments 

to_txt: Identifier of the message recipient
cc_txt: This e-mail address of the recipient in knowledge
subj_txt: Subject default message
status_cod: Status (1 = active, 0 = inactive) of the recipient
 

The MEF_MSG_SEQ sequence

The sequence is an Oracle object. In practice it is a universal counter that increments each time you request it.
Each message line must have its sequential number. It is more functional than time stamp to sort the table.
Because sometimes the messages are separated at a fraction of a second of each other, the time stamp might not be sufficiently discriminating.

The MEF_RUN_SEQ sequence

Sequence that indicates unambiguously, a run of a job.

The MEF package

This is the basic package. I suggest to develop all the code inside PL/SQL packages (they are basically libraries), which allow a better management and use of the code.
Now a short description of the units contained in the package.

f_str: Utility function to generate a string after replacing the input variables.

p_ins_msg_lot: This procedure perform the insertion of the message in the MEF_MSG_LOT table  receiving a variable of row type as input parameter. This procedure has the "pragma autonomous_transaction". It is very important and requires a thorough description.
It seems incredible, but if there were not, it would not be possible this messaging system. The pragma autonomous_transaction allows us to commit (i.e. to validate into the database) only and exclusively the DML statements of the unit which contains this compiler directive. This concept is crucial because it allows us to commit (so to insert data into the database) without affecting the logic of the loading process.
Let's clarify with an example.
Typically, before to loading the daily data into a table, you delete, first of all, the data of that day, and then load/reload the new data. (forget for a moment the partition manipulation)
You do the commit at the end, where the loading, (delete/insert), was successfully completed. If i ran a commit after the delete and the insert has a problem, I could have the loss of data of the day.
As the messaging needs to do a commit of the message in the table, the execution of the innocent message "I have done the delete", would validate even the delete itself. And this is a side effect not acceptable.
The autonomous transaction solve the problem: the PL/SQL Oracle engine, produce a "daughter" transaction
who live an autonomous life, which validates the data in the MEF_MSG_LOT table without affecting the logic of the parent transaction.

p_rae: The management of the exception is standardized in the following manner. When any Oracle error happen, in the "when others" instruction there is the call of the p_rae procedure that enriches the content of the error with other useful information,such as, for example, where the exception occurred. The output will always be the standard error pv_error. The "when pv_error" is used to make sure that you keep the original error. 

p_init: Other private procedure. It initializes the line_row variable to perform the insert in the table.

delta_time: Procedure that, based on the input parameters, such as the date of the last message and the current date , calculates all the delta-time information, how many seconds, minutes, hours have passed and a delta time in the 'HH24:MI:SS' format. The ways in which we can calculate the delta time are numerous: one used is just one of many.

f_get_seq_val: Function that extracts generalized, in a dynamic way, the next number of the Oracle sequence whose name is given in the parameter. 

f_get_exec_cnt: Function that extracts the execution number of the job. Before calling the generic function
f_get_seq_val it verify that has not already been set as a global variable: in this case, use the number of the current execution.

f_get_cft: Function that extracts the current configuration from the MEF_CFT table.

p_esend: A function that performs the sending of email via the UTL_MAIL package.

p_send: This is the procedure that sends the message.

p_mail: Procedure for sending the e-mail. Using the email code in inputit looks for all the recipients in the MEF_EMAIL_CFT table and calls the p_esend passing all required parameters.

System configuration

Before you can create all the structures described above, you must perform some environment check. First of all it is necessary to verify that the Oracle RDBMS is designed for sending emails.
So you have to make sure that the Oracle user that sends the messages has all grant necessary for its operations.
Let's see in detail.

SMTP check
We need to verify that the Oracle RDBMS is designed for sending emails. In fact the sending is activated by calling a procedure that is part of a package of the RDBMS. To verify this, connect to SQL * Plus with SYS user and check the package UTL_MAIL (from Oracle 11):

Sqlplus / as sysdba
SQL> descr utl_mail
ERROR:
ORA-04043: object utl_mail does not exist

If you get this error message, you need to install the package system UTL_MAIL and give the execute permission to the user.
Always as user SYS, run the script to install the package from the rdbms/admin folder of the Oracle home, and give the execution permissions to the ETL user.
So you have to check the smtp server in the Oracle initialization parameter file; in the following example it is not setted and I indicate you how to do it. Here is the sequence of instructions for making these checks (remember to replace the file path of the utlmail.sql file with the relative path to your Oracle installation):

D:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 25 15:35:49 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option

SQL> @...\RDBMS\ADMIN\utlmail.sql
SQL> @...\RDBMS\ADMIN\prvtmail.plb
Package created.
Synonym created.

SQL> grant execute on UTL_MAIL to <ETL user>;
Grant succeeded.
SQL> sho parameters smtp
NAME TYPE VALUE
------------------------------------ ----------- -------
smtp_out_server string

SQL> alter system set smtp_out_server = <email server> scope=both;
System altered.
 
Almost always, in the company there is a mail server, the value should be include the domain, for example. exch.dev.com. The option "scope = both" make the change effective immediately and permanently.
We must also create and configure an ACL (from Oracle11) using the system package dbms_network_acl_admin.
The ACL (Access Control List) is only one way to define external resources to the RDBMS (such as email servers) and allow access to users.
Now open slideshare [http://www.slideshare.net/jackbim/recipe-7-of-data-warehouse-a-messaging-system-for-oracle-dwh-2] where you will find all the instructions on how to download and run the installation script of this messaging system.
The script will execute all the necessary settings for you. All this will take no longer than 5 minutes of work.

Test

In addition to the tests showned in the slides of slideshare, we perform another test now, certainly more exhaustive,  which clearly shows the functionality simulating a piece of ETL load.
We start by creating a test table, initializing it from a system table. At this point we run an anonymous block of code (anonymous block is defined as the set of SQL statements included between a begin and an end) that will simulate a real load of a table with insert and delete of data. The sequence of steps is quite simple: you initialize global variables package to better identify the various steps in the messaging table.

create table SALES as select * from tabs;
begin
   mef.pv_sched_cod := 'Daily';
   mef.pv_job_cod := 'Staging tables';
   mef.pv_unit_cod := 'Load sales table';
   mef.pv_exec_cnt := 10;

   mef.p_send('proc_prova','Load of SALES table');
   mef.p_send('proc_prova','Deleting...');
   delete from sales;
   mef.p_send('proc_prova','Deleted');
   mef.p_send('proc_prova','Loading...');
   insert into sales select * from tabs;
   mef.p_send('proc_prova','Loaded');
   mef.p_mail('MEF','ETL_administrator','Sales table loaded');
   mef.p_send('proc_prova','Load ended');
end;
/

The final result obtained, which can be seen in the table MEF_MSG_LOT, it is very interesting, and gives you the wealth of contextual information that is talked about in the beginning.

Conclusion

We saw in detail the steps required to build a messaging system, simple, but very useful for all the people working in the Data Warehouse projects.
This implementation, which is the basis of my Micro-ETL-Foundation, obviously works on any Oracle-PL/SQL project, is non-invasive, and can be applied at any time inserting simple procedure calls in an existing ETL process.

How to send mail with attach using Oracle pl/sql

In a Data Warehouse is very important to use e-mail to report the results of the processing and any  errors.  Oracle provides a package UTL_MAIL (from 10g) for this task.
                              
This package, however, can not be used immediately, but must be verified a number of conditions.        
Because I love to "see" the solutions in a graphical format, you can download the solution from www.slideshare.net
 
Inside it you can see, in a single slide, the solution and all the "objects" involved.                  
After making the controls in the slide, you can try the solution immediately in this way.
               
1) Copy and paste the following script in a file, for example, OAiO1.sql    

spool OAiO1

grant connect,resource,dba to &&1 identified by &&2;

grant read,write on directory DATA_PUMP_DIR to &&1;

begin
   dbms_network_acl_admin.drop_acl(
   acl => '&&3');
   commit;
end;
/
begin   
   dbms_network_acl_admin.create_acl (
   acl         => '&&3',
   description => 'Allow mail to be send',
   principal   => '&&1',
   is_grant    => TRUE,
   privilege   => 'connect');
   commit;
end;
/
begin   
   dbms_network_acl_admin.assign_acl(
   acl  => '&&3',
   host => '&&4');
   commit;
end;
/
begin  
   dbms_network_acl_admin.add_privilege (
   acl       => '&&3',
   principal => '&&1',
   is_grant  => TRUE,
   privilege => 'resolve'
   );
   commit;
   end;
/
connect &&1/&&2

create or replace procedure p_email(
   p_sender varchar2
   ,p_recipients varchar2
   ,p_subject varchar2
   ,p_message varchar2
   ,p_dir varchar2 default null
   ,p_file varchar2 default null) is
   v_fh utl_file.file_type;
   v_rfile raw(32767);
   v_flen number;
   v_bsize number;
   v_ex boolean;
begin
   if (p_dir is null) then
      utl_mail.send(
      sender => p_sender
      ,recipients => p_recipients
      ,subject => p_subject
      ,message => p_message
      );
   else
      utl_file.fgetattr(p_dir, p_file, v_ex, v_flen, v_bsize);
      v_fh := utl_file.fopen(p_dir, p_file, 'r');
      utl_file.get_raw(v_fh,v_rfile, v_flen);
      utl_file.fclose(v_fh);
      utl_mail.send_attach_raw(
      sender => p_sender
      ,recipients => p_recipients
      ,subject => p_subject
      ,message => p_message
      ,attachment => v_rfile
      ,att_inline => FALSE
      ,att_filename => p_file
      );
   end if;
end;
/

exec p_email('&&7','&&6','OAiO1_Test','Hello world from user &&1', 'DATA_PUMP_DIR', 'dp.log');

spool off

2) The script could create a new test user. For this example we will use the "historical" user SCOTT.
3) Connect to SQL from command prompt with sqlplus / as sysdba
4) Run the command passing your personal setting:

@OAiO1 <&&1> <&&2> <&&3> <&&4> "<&&5>" "<&&6>" <&&7>

where:
&&1 = Oracle User that send the email (uppercase, e.g. SCOTT)
&&2 = Oracle User password (e.g. tiger)
&&3 = An ACL name of your choice (e.g. scott_mail.xml)
&&4 = Email Server of your Company (e.g.  Acme.ita.com)
&&5 = Oracle Home path (e.g. E:\o\s\product\11.2.0\dbhome_1)
&&6 = Email receiver (e.g. massimo_cenci@yahoo.it)
&&7 = User sender (e.g. DWH_dba)

It is all.

Data Warehouse: Naming convention techniques

The naming convention is a key component of any IT project. The purpose of this article is to suggest a standard for a practical and effective Data Warehouse design.

 

1 - Introduction

Usually, the naming convention is the method by which you decide to name of the various entities of the OLTP (Online Transaction Processing) or DSS (Decision Support System) system that you are designing.  In this article the term "entity" will be synonymous with "structure": a  generic term to identify to data structures typical of a relational Data Warehouse such as: tables, views, materialized views, synonyms, etc.
The application of a method should not be considered a separate issue, restricted to the particular entity that is being analyzed, separated from the global system architecture.  On the contrary, it shall produce a name that should be able to immediately recall all semantic entities involved, no matter if they belong to a Data Warehouse or to an operational system.
This is the keystone of the method, because to define a naming convention for an entity not simply give it a name but means defining an architectural context.  The names selected will be used in discussions, meetings, documents, deliverables, in relational schemas, and accompany our working life for several months. 
Very soon the relationships between various entities, and between entities and programs, will prevent any changes in the naming convention, and bad choices or no choice, will have unpleasant consequences for the economy of the entire project.
The examples shown in the article focus mainly on the data tables, because practically all the work that takes place within the data loading process, is aimed at filling these entities.
In these cases the effectiveness of the naming convention appears clear: try to think of the time needed to acquire knowledge about a system when all entities, perhaps hundreds, have not followed a standard, or followed only in part or in ambiguous way. The purpose of these techniques is to avoid these situations for us and for our partners. 

2 - Guidelines

Naming convention must be established early in the life-cycle of a project. You can not start the process of physical design and development phase without the naming convention of the entities that constitute our system.  No "create ..." will be possible without these decisions. 
The guidelines that are the basis of the techniques showned here are just three:

 1 – The logical partitioning
 2 – The coding rule
 3 – The classification

The Logical partitioning allows you to define a hierarchy among the entities involved, the classification allows you to associate a physical connotation to the entities, the coding rule standardizes the naming derived from partitioning and classification.  Since the best way to learn these concepts is to see them do in practice, the three guidelines are detailed in the next sections where we will see how you can apply to a real project.  Now focus our attention on the entities. 

3 – Naming convention for the entities

The paradigm that underlies the naming convention for the entities can be summarized in the following formula:

<entity name> = <project code>_<area code>_<section code>_<logical name>_<type code>

To understand the formula, use the first guideline, that the logical partitioning. Define it a guideline is certainly simplistic to say the least. It's actually a moment of reflection that can only be faced after realizing our global vision of the system. 
The term ‘logical partitioning’ means the decomposition of the system in its logical components so called areas and sections. It is an approach very similar to a drill-down: an overview towards a particular view.  It starts with the project, which is the root of our logic tree, it is divided into areas, each area is divided into the sections. The figure 1 show you immediately an example of logical partitioning.


Figure 1

Since we are talking about Data Warehouse, what are the areas and sections and the way you think the logical partitioning will be based on the Ralph Kimball philosophy, an approach that I use successfully from several years.  Let's look all the five individual components of the <entity name> . 

3.1 - The project code

The first decision to be addressed in a project is to assign it a code.  It may seem an obvious statement, but the identification of a project code is a precondition to lead the project in the right direction.  The choice of the project code has immediate and important consequences that impact at different levels.  Let's see.

Architectural level

The first level impacted is the logical / physical  architecture of the Data Warehouse.  After choosing the code project, each entity will be named with that project code followed by underscore "_".
 This means that each table, view, materialized view, synonym, program, or generally, any physical structure is created, it must start with <project code>.
Anywhere the physical structures are allocated, we have established a unique way to identify all the objects in our Data Warehouse.  (Obviously there must not be another project with the same code).  A selection from the database dictionary will allow us to identify exclusively the objects that concerns us.

File system level

The second level impacted is the physical file system.  All project directories, in which arrives the flat files coming from the source systems, should also start with ''<project code> _ ".  Another example might be the directories of the database files, of the creation script, etc.

Documentation level

The third level impacted concern documentation and deliverables.  Using the same technique for them, we will be sure to identify with our search always and only the documents associated with our project.

The rule of three

At this point we need a coding rule.  If, for example, we have defined our project as "Intelligent Enterprise Data Warehouse," it is unthinkable to use 38 characters as project code.  My experience about the design of several Data Warehouses, provided me a very clear indication.  It can be argued that the best rule to identify the project code is the "rule of three."
This rule suggests that the project code must be of three characters. In our example, a good acronym, and then the project code, will be "EDW”. If we are designing a Data Warehouse of Exploration and Production of an oil company, a good project code will be" EPD. "
As you know, three is the perfect number, and in my past, this perfection was clearly manifested in all its aspects. Two characters cause ambiguity and misunderstanding, four or plus characters are too much.  Since three is the perfect number, that rule will also apply to other encodings.  So, we start then with the project code “EDW”.

3.2 - The area code

The areas are a way to make a first logical partitioning of the objects of our Data Warehouse. Break all objects in areas of membership, allow us to associate them in a single scope, all  the items with similar logical/temporal affinity.
To understand the areas, we must express some principles of data warehousing, in particular, some aspects of the ETL process (Extract, Transform and Load).  These principles will be simplified as much as possible to ensure an understanding to all.  Obviously there is a very extensive literature on this subject which I suggest you for further investigations.

3.2.1 - The ETL process

The loading processes of the tables of a Data Warehouse, typically is a workflow like the following.
External systems produce source data files. Such systems may be of any type (OLTP, other Data Warehouse, Operational Data Store, etc.) and these files can be of any format (ASCII flat files of fixed length, with terminator, XML, etc.).  The data in these flat files are loaded into work tables, defined staging tables. These tables are almost always structured almost identical picture of the flat file. 
After the loading of the staging tables, the process continues with the loading of the others tables, which are called dimensional and basic fact tables; I like call them of “level 0”. This process is responsible to perform all the cleaning and checking work on the data of the staging tables, define the artificial keys for the fact tables, and then load the data according to the "dimensional model." With this term we mean that the data structure is composed with a fact table and dimensional tables that make up what is called the “star-schema”. (excuse me, Ralph, the simplification...)
After loading the fact tables of level 0, may be loaded aggregate tables (to improve the performance of reading data from the Business Intelligent tools) or higher-level fact tables (1.2, .. N). It depends on the complexity and needs of the Data Warehouse.
A typical example in the market of large retail may be the level 0 fact tables that stores the data of sales of the stores and the costs associated with the sold items.  From these fact tables, you can load a fact table to a level 1 where you can lose some dimensions of analysis and compare the sell and costs  to get the net or gross margin.
The whole process that loads the data arriving from the external systems up to the loading of all other structures is defined the ETL process.  There may be variations on this workflow, but it can be said that nearly all Data Warehouse projects are adapting to this process.

First step - the area partitioning

After this brief interlude, we return to the speech left open, that is the connection of the ETL process with our naming convention.  The logical lartitioning of the entities that we do in fact, is precisely focused on the process described above.  We can identify, for example, the following logical areas:

• Staging Area: In this area we have all the entities that receive the flat data files coming from external systems.

• Level 0 Data Mart Area: In this area there are all entities which contain the data cleaned and dimensionalized by the load process.

• Level 1 Data Mart Area (and subsequent levels): In this area there are higher level entities that are derived by the entities of level 0.

• Common Area: In this area there are all the dimension tables and all those entities that are common to the whole Data Warehouse, such as configuration tables, service tables, etc..

Of course you can also identify other areas.  For example we can identify an area called “Operational Data Store” where all the data structures are normalized and not structured according to the dimensional model, or we can identify an area of infrastructural information, linked to the logging of programs and schedules.  In short, the possibilities are different, and we will think carefully.

Second step – the coding rule

The next step then is to codify by the "rule of three" all areas that we identified.  Using acronyms very intuitive, like STA, DM0, DM1, COM obtain as indicated in Figure 1.
As you can see the effects of naming conventions are starting to take shape and already at this level, still high, you can easily identify all the entities belonging to a common logic.
If you want to know the names of all entities that collect data from external flat files, is enough to extract ,from the RDBMS data dictionary, all the object types that begin with “EDW_STA_”.  If you want to identify all the fact tables of level 1, search all object types that begin with “EDW_DM1_”
In reality, this initial partitioning process gives you a too high level.  In projects of some complexity, where the number of entities is high, should be further partitioned areas in the sections or sub-areas.  Each area will then be decomposed into smaller elements that deepen and enrich the knowledge of the Data Warehouse structures.

3.3 - The section code (sub-area)

The sections allow you to group logically all entities that belong to the different areas. Each area has specific characteristics, so that even the process of partitioning areas in the sections is specific to each area.
Obviously, every designer can define the sections as it seems appropriate, based on their experience and creativity.  Sections that now will be described, for the areas defined above, are those based on my personal experience and it is only  a suggestion.  Let's look in more detail.

3.3.1 - The sections of the Staging Area

Taking the example of the flat data files feeding the Data Warehouse, often producing subsytems can be different, each generating different data.  If, for example, we have three subsystem like sales, accounting and logistical movements, the approach can be as follows:
Codify with the rule of three the external subsystems, such as selling stores, with STO (Store), logistics movements with MOV (Movements) and accounts with PLS (Profit and Loss). Each entity linked to these subsystems will have these codes within its name.
If you want to know the names of all Staging Area tables loaded from logistics subsystem, simply extract from the database data dictionary all entities that begin with "EDW_STA_MOV_.
 .

3.3.2 - The sections of the level 0 Data Mart area

This is a key area because it found space all the basic fact tables, that are those with the most detailed granularity of the information.  The best way to partition the area into sections, is obviously to identify with the rule of three the different entities that comprise it.
For example, if a data mart contains all the sales data, a code should be SLS or SAL (short for sales), if it contains cost data, will be CST or COS (short for Costs).
Since the concept of data marts is essential in a Data Warehouse project, I prefer to open another parenthesis about principles of data warehousing and give you a more detailed definition for ease of understanding of the naming convention that uses it.

The Data Mart

The data mart is a "logical subset of the complete Data Warehouse" as shown by R. Kimball in his book [1].  Each data mart is usually organized around a single business processes,  and the union of all data marts is the Data Warehouse.
Then sections of the DM0, will be specific business process that you are deploying. Someone can think that the fact table with and data mart are conceptually the same.  The data mart is a logical concept, the fact table is only a part of the physical implementation of this logical concept.  In reality, the data mart consists of a fact table and all its aggregated tables (materialized views in Oracle) that are created to increase performance reporting associated with that business area.  Obviously, the aggregated structures are not mandatory, but a necessity for VLDW (Very Large Data Warehouse), where the amount of data processed may exceed terabytes.
A data mart that contains only the fact table, perhaps several gigabytes of data, is not a data mart: it is a problem.
Because in a Data Warehouse the number of aggregate tables tend to be high, the naming convention provides an help to immediately identify all data structures associated with a specific business process.

3.3.3 - The sections of the level 1 Data Mart area

In this area there are the data marts whose content is not immediately derivable from a data mart level 0, but consists of data taken by several lower-level data marts or obtained by processes that do not make a trivial aggregate calculation. We have two examples to clarify the concept.
As mentioned above, in the market associated with large retailers, it is typical to have a data mart of Profitability, which contains a single data structure sales information and a data Mart with various cost types that allow you to get the net or gross margin.
We can think Profitability as a data mart of level 1, which is loaded by the two data marts level 0 indicated above, ie EDW_DM0_SLS and EDW_DM0_CST.
You can then encode this data marts of profitability with  EDW_DM1_PRF or  EDW_DM1_PRO.
Another example is related to aggregate data that are not immediately achievable with simple sums or counts (because in that case I use the materialized views).  If a user requirement is to see the monthly data associates with the the same month of the last year, both current and progressive, a design choice may be to load, with the ETL process, a level1 data mart with these aggregate data already calculated, rather than entrust this task to the reporting tools.
You can encode this data mart as EDW_DM1_MPY (indicating an Monthly, with data P revious year and progressive, Year to date).

3.3.4 - The sections of the Common Area

In this area we associate all entities that are common to the entire project and not connected to one of the areas described above.  Typical examples of these sections are the following:

CDI section (Conformed Dimension).

The dimensions of analysis present in the Data Warehouse, are not specific to a Data Mart, but they are shared by many data marts. Just think of the time dimension, present in virtually all data mart of any level.  Are therefore common structures that can be grouped into one section.

CFA section (Conformed Fact).

Concept similar to the previous but applied to the fact tables. An example is the table of currency conversion, which is a typical fact table but comprehensive and usable by all the Data Warehouse.

LOG section (Logging). 

All structures associated with the log of the ETL process.

MTD section (metadata). 

All metadata structure in the Data Warehouse.

The Figure 2 show you an example of sections, and then complete our design with that just described.


Figure 2

At this point our partitioning process can be considered complete.  Additional sub-sections may give little added value to knowledge, in addition to further extend the final name of various entities.  Indeed, in very simple projects, may be enough the partitioning at the level area. Explore now the fourth components of the <entity name>, that is the logical name.

3.4 - The logical name

We have to identify a logical name, preferably short, after the codes just described.  Returning to the source data files that load a Staging Area table, if the subsystem Profit and Loss  provide us the customer informations, its logical name will be “CUST” and its complete name will be:

EDW_STA_PRL_CUST

You can see the completeness of information that comes from the name of this entity.  In a few characters we know that they are customer data, provided by Profit and Loss Subsystem, which are loaded on a Staging area that is part of the Intelligent Enterprise Data Warehouse.
It is not necessary submit the logical to the rule of three, and, in some cases, it may be omitted.  For example, in the data mart area, the prefix is already sufficiently explanatory, and the addition of a logical name will not give any added value to understanding the content of the entity.  Indeed, long before the start of deploy to production, the data marts of Profitability exemplified above, will be called “affectionately" PRO”, and all recognize the Profitability Data Mart naming only the three characters of the logical name.

3.5 - The type code

Now you might think of getting our initial goal.  The name obtained by following the guidelines of the naming convention is semantically significant, and I believe that if we simply called the entity CUSTOMER, we would have lost in knowledge and context.
Unfortunately it is not just so. The entity EDW_STA_PRL_CUST  still does not tell us everything  about its own nature.
If you carefully review the semantics that the naming convention has given to the word EDW_STA_PRL_CUST, we find that there are some cognitive deficiencies. 
It is quite clear that  customer data are provided by the Profit and Loss Subsystem, but does not show up anywhere that it is a table of data. The technique of classification can help us to fill these gaps.
Among the various entities which are present in the section of an area, there may be tables, but also view (views are the selection of certain rows and / or certain columns in a table), synonyms (ie tables aliases), sequences, etc.
This information is important because it avoids us to access to the database data dictionary.  This fact should not be underestimated because in many situations, this information, which seems highly associated with the "physical" side of the entity, also has a substantial logical content.
Consider, for example the case of the fact table and materialized views (the Oracle summary tables).  The fact tables are always present in a Data Warehouse projects.  The materialized views are aggregated tables that are typically generated from fact tables, but that aggregate the data according to certain dimensions of analysis.
It is important to distinguish immediately the two types of entities, because their content is different and also their feeding process can be different and performed at different times.
Inside the data mart level 0 area, there may be a section that identifies the sales data mart, but within this section may be dozens of entities, and how many and what are the "aggregated" ie, materialized views, is an important information for us and for the database administrator.
By classification, this information will be immediately apparent, not only for a specific section or area, but also across all areas.  If we decide to typify all the materialized views derived from fact tables with the code FMV  (Fact Materialized View), all entities whose name ends with FMV will surely "aggregated" tables.
What follows is a fairly exhaustive list of codes and descriptions that will certainly help us in the classification process. 

• DAT - Data Table. General data table typical of staging areas.  Are basically tables that contain metrics, rate, or other measures, with their natural production codes not transformed in artificial keys.

• DST - Descriptive Table.  Like the previous, it refers to tables typical of staging areas.  For example the personal data codes and their natural attributes of the customers, the product codes, etc. In a world, all staging tables that will turn into dimensional tables. 

• EDT - External Data Table.  Typical of the staging areas. The 'external table’ is a very useful feature of Oracle by which a flat file is seen in effect as a table. Then easily managed with the SQL language.

• EXT - Exception Table.  Table that contains all lines that have failed integrity checks in a table.

• FAT - Fact Table. Typically contains facts at maximum transactional detail, how the individual movements of current account or the individual lines of receipts issued by a supermarket checkout.

• FFT - Factless Fact Table.  Fact table without numeric information.  In them, the mere presence of the line certify the occurrence of an event, such as the presence of a student in a university course.

• PSF - Periodic Snapshot Fact table.  This type of fact table reassume many facts in one line summary of a situation at the end of a period of time.  A typical example is the bank account: it is possible to construct a monthly summaries supplied by the basic fact table where there are details of each movement performed on the bank account.

• ASF - Accumulating Snapshot Fact table.  This type of table describes the processes that have a beginning and an end not too time-deferred and when a dimension is repeated several times.  A typical example is the story of an order or an invoice, all summarized in one line.  Or a list of days (linked to the time dimension) representing all the state changes of an object or a process.

• FAV - Fact View - Partial view of data in one or more fact tables

• FMV - Fact materialized views - materialized view built on one or more fact tables.

• DIT - Dimensional table - Table for dimensional analysis

• DIV - Dimensional View - view associated with a dimensional table.

• DIM - Dimension - For Oracle the dimension is not the dimension table, but the logical structure that describes its content in terms of hierarchical levels and attributes associated with the various levels.

• TMT - Temporary table – They are tables that contains temporary data used in complex ETL processes.

• CFT - Configuration table – Generic table containing configuration data.

• DTE - Data Entry Table - Table that can be updated by the user with a graphical interface.

• AWS - Analytic workspace - Cube OLAP data structure that is typical of the multidimensional databases.

Conclusions

It's the end of this brief journey through the naming convention techniques . How exposed is certainly not exhaustive of the potential for application of such techniques, nor intends to be a dogma to be followed.  Each of us, based on its experience, can build own naming convention according to their needs and according to their intuition.  In fact it does not matter which choice you start to partition the system or how its components are classified, but it is important to apply a standardization method.  An effective naming convention certainly provides all the tools needed to control the Data Warehouse, in terms of knowledge, management and maintenance.



You can download this article from Slideshare.
 Bibliography
 [1] R. Kimball - The Data WarehouseLifecycle Toolkit ", Wiley, 1998