Data Modeler

Oracle has a free Data Modeler tool. There is a stand alone version of this tool. Another version is bundled with SQL Developer v3 and beyond. Both versions have the same functionality. The stand alone version gives you more screen real estate and easier access to the menus. The tool itself was developed in Java. As such, it requires the JDK.

Data Modeler lets you create, browse, and edit models. Here are some model types it supports:

• Logical
• Relational
• Physcial
• Multi-dimensional
• Data Type

You can do forward and reverse engineering with the tool. The targets it supports are Oracle, SQL Server, and DB2. Although I would think you would want to have the tool produce DDL, it cause also spit out other formats like CSV. You can perform collaborative development with this tool.

Why go through the trouble of using a Data Modeler? Some say it is much easier than maintaining scripts manually. It can serve as a documentation tool. Or it can just help you get a handle on an existing database system.

Entity Relationship Diagram

I drew my first Entity Relationship Diagram (ERD) over 20 years ago. Used some drawing package to make the pictures. Not a whole lot has changed since then. The ERD was invented by Peter Chen back in 1976. It looks a lot like a flowchart. The ERD repsents an information system. It shows the logical structure of a database.

The ERD deals with entities and relationships. It does not show a specific entity. Instead it deals with entity sets. Drawing up an ERD is part of a data modeling technique. In the end, it is used to create a relational database. An ERD is not well suited for unstructured data.

There are different style to draw the ERD:

• Information engineering
• Peter Chen
• Charles Bachman
• James Martin

Of course there are tons of tools that can help you draw an ERD. Here are some commercial ones I have heard about:

• ERwin
• MagicDraw
• Oracle Designer
• Rational Rose
• System Architect
• TOAD Data Modeler

 I mentioned before that ERDs have entities and their cardinality. Entities are the objects that have data. Cardinality is the relationship between these objects in terms of numbers. The main types of such relationships are:

1. One-to-one
2. One-to-many
3. Many-to-many

CA ERwin

I have been seeing some jobs that want you to have experience with ERwin. I don't think I have used that tool before. Heck. I first started drawing ER diagrams with a generic graphics program like Visio. The last time I used any formal programs for design, it must have been Rational Rose.

CA ERwin is a data modeler. The name stands for "Entity Relationship" and Microsoft "Windows". The tool was created by Logic Works. It allows you to also do requirements analysis. Of course it lets you do database design. ERwin will let you create a custom information system, a transactional database system, or a datamart. There are four main editions of the software:

1. Community - free, subset of features, max 25 objects
2. Navigator - read only
3. Standard - an enterprise edition
4. Workgroups - collaborative

 ERwin lets you do both logical and physical modeling. Once you have the physical model, ERwin can automatically generate the DDL to create your schema. The physical model supports the following targets:

• DB2
• Informix
• MySQL
• Oracle
• SAS
• Azure
• SQL Server
• Sybase

The one targeting Microsoft Azure is its own edition. I tried downloading the community edition. Be warned. The thing takes a long time to install. It first wants to install the Microsoft dot net framework. Then it uses an Installshield wizard to guide you through the options. The default options require 814M of disk space.

Once installed, I found the IDE to be very intuitive. It felt like using Microsoft's Visual Studio, where I feel at home. I started a logical modeling project. Generated a bunch of entities chock full of attributes. Save my file off with an "*.etwin" extension. Later I plan to create a physical model and deploy it to an Oracle database. Oh my.

Oracle Warehouse Builder

I remember my first encounter with Oracle Warehouse Builder (OWB). We were moving to a new database design. We were also moving to a web based system from a client/server one. The scope was huge. The timeline was short. This is the story of my life.

But back to OWB. It is a data warehouse integration solution. The goal is to migrate data from legacy systems and put it into a data warehouse. Sounds like it was just what we needed. OWB has some secondary goal: modeling, profiling, cleansing, and auditing of data.

It was first released in January 2000. OWB 11gR2 is the last released. It will be patched to work with Oracle 12c. But it scheduled to be integrated in Oracle Data Integrator (ODI).

OWB is a repository driven tool. That is, the metadata that describes the ETL is put into a schema. You develop mappings and design process flows with it. It deploys into PL/SQL packages. It runs under Control Center and the OWB runtime. Oracle has to be the target.

The system we were replacing using OWB and a web version of our application went live briefly. It had a bunch of problems. It was slow. It did not implement all of the requirements of the old system. I can't blame these problems on OWB per se. Others have tried to rewrite the huge legacy system without success, and they did not use OWB in their solutions.

E-Business Suite R12

I am looking for my next gig. A human resources person in my company asked what I did for a living. I said I was an Oracle developer. She told me they had some hard to fill positions for me. Unfortunately they required EBS experience. Turns out I have none.

EBS stands for E-Business Suite. It is also know as e-BS, EB-Suite, and Applications/Apps. They are a bunch of Oracle business applications. They are each licensed separately. The latest release is Release 12, also known as R12. EBS is huge. It is second only to SAP in sales.

Here are the main groups of apps in EBS:

1. Enterprise Resource Planning (ERP)
2. Customer Relationship Management (CRM)
3. Supply Chain Management (SCM)

 The EBS is built on top of the following stack of technologies:

• Oracle Forms Server
• Oracle Reports Server
• Apache Web Server
• Oracle Discoverer
• JInitiator
• Java

OLAP

Heard the term OLAP a lot? It stands for online analytical processing. You would use it to analyze multidimensional data. OLAP is part of business intelligence. For example, it can be used for business reporting of sales. It can also be used for data mining. The data is organized in cubes, not tables. This data is queried and becomes parts of reports. There are not as many transactions involved. The database does not have to be huge like a data warehouse.

There are three main operations in OLAP:

1. Consolidation
2. Drill down
3. Slicing and dicing

Consolidation, also know as roll up, is aggregation of data. Drill down is the opposite. It is going into the details. Slicing and dicing let's you view data from different viewpoints. Those viewpoints are called dimensions.

There is a certain term in OLAP called the OLAP Cube. It is also known as a multidimensional cube or hypercube. It contains numeric facts (called measures) categorized by dimensions. This cube is created from data warehouse schemas. The speed in accessing the cube is from the aggregation of data. For some complex queries, accessing data can be 1000 times faster than querying a normal database.

There are different types of popular OLAP systems:

• MOLAP
• ROLAP
• HOLAP

The MOLAP is the multidimensional one. This is the classic type of OLAP. Data is in special array storage hardware. It is fast on queries, and requires less disk space. Loading data into it can be time consuming though. ROLAP is a relational database OLAP. Slicing and dicing translate into WHERE clauses of SQL statements in ROLAP. You can ask this system any question since it has all the non-aggregated data. HOLAP is a hybrid between MOLAP and HOLAP.

More Data Warehouse Schemas

Previously I had written about the different types of schemas for data warehousing. Now I want to write about some more details. The schemas are made up of fact tables and dimension tables. Fact tables contain a bunch of numeric values called measures. They have foreign keys into the dimension tables. In fact, their primary keys are usually a composite of all the foreign keys.

The dimension table categorizes data. It has less rows than a fact table. But it will probably have a lot of attributes. These attributes are descriptive text values that help answer business questions. They can contain data collected at the lowest level. The data can be aggregated into hierarchies. These tables might take along time to load.

Here are some misc terms. A level is a position within a dimension table hierarchy. A stars query is a join between a fact table and a dimension table. A centipede schema is a star schema with a lot of dimensions.

An entity is a chunk of information. That are things of importance. Entities usually map to a database table. Attributes are components of an entity. They define the unique of the entity. Logical design is the process of identifying entities and attributes. You could use a tool such as Oracle Warehouse Builder or Oracle Designer to conduct logical design.