Data Warehouse Schemas

Lots of jobs require some data warehouse experience. So it is time to study up on data warehouse schemas. Here are the common ones:

• Star
• Snowflake
• Fact Constellation
• Galaxy

The star schema is the simplest. It is also the most common. It is supported by business intelligence tools. OLAP systems build cubes with them. It represents multidimensional data. In the center of the schema is a fact table, usually in the third normal form. The points of the stars are denormalized dimension tables.  It is good for simple and fast queries.

The snowflake schema is like a star schema. However the dimensional tables are normalized. The fact constellation schema has multiple fact tables. It has shared dimension tables which are large. It is more complicated. The galaxy schema has many fact tables. They have common dimensions. It is the combination of many data marts.

APEX

I had briefly played with Oracle Application Express (APEX) before. But I am now coming back and familiarizing myself with the big picture of the tool. It allows you to build application using a browser based user interface. The IDE has a code editor, supports drag and drop, does not require recompilations, and allows you to use jQuery Mobile. The IDE also supports languages/standards like SQL, PL/SQL, JavaScript, REST, and SOAP.

You don't have to manually write your CSS and JavaScript. APEX produces a responsive user interface. You can deploy your APEX apps to the Oracle Cloud. APEX runs with different versions of the Oracle database from the free XE to the Enterprise Edition.

APEX comes with a number of sample applications such as bug tracking, survey builder, and a customer tracker. APEX incurs no additional cost. It runs wherever the Oracle database can run. It was first released in 2004. The latest version is 5.0.1 released on July 15, 2015. The product has gone through a number of names:

1. Flows
2. Oracle Platform
3. Project Marvel
4. HTML DB
5. Application Express (APEX)

APEX is used to build web apps. It was created by Mike Hichwa. He is the same guy who developed Web DB. It is built on top of mod_plsql. The tool is used by Oracle itself in a number of sites such as AskTom Knowledgebase and the Oracle online store.

APEX is easy to deploy. You can host your demo apps on Oracle's host for free. It supports themes. The install is heavy coming in at 147M for the English only version. There are some limitations on the tool. Primary keys can contain at most two fields. There is a max of 200 items per page. Forms can handle a max of 200 database items.

APEX is not supported by many webhosts. There is no built in version control. The base server side language is PL/SQL. In summary, it is a rapid web application development tool. It requires at least Oracle 9iR2. Since Oracle 11g, it has been included with the database install.

Oracle XML DB

I know XML. But I don't know XDB, which is Oracle's XML DB. It is actually a set of technologies paired with an Oracle database. The two main pieces are (1) XMLType tables/views, and (2) the XML DB Repository. The repository holds resources such as folders and files.

XML DB is a native XML storage technology. The tech supports XML standards such as XML schemas. You register the schemas, and it creates some tables to hold the info. You can access the XML data through HTTP, SQL, JDBC, WebDAV, or FTP.

Messaging is done with the Oracle Streams AQ. XMLType can be used as the payload type. You can queue up XMLType messages. There are a number of APIs to use XML DB. These include XMLType (surprise), DBMS_XDB, and a number of other PL/SQL packages that start with DBMS_XDB*.

There are a bunch of new views to check on XML DB info. Here are the DBA views:

• DBA_XML_SCHEMAS
• DBA_XML_TABLES
• DBA_XML_TAB_COLS
• DBA_XML_VIEWS
• DBA_XML_VIEW_COLS

You get the USER_* and ALL_* corresponding views as well. XML text is stored in CLOBs. There are two ways to handle storage: (1) unstructured storage, and (2) structured storage. Unstructured is fast. Structured uses up less space but increases overhead during processing.

XML DB supports the SQL/XML standard. It requires at least Oracle 9R2. Altova, the people who make XMLSpy, have an editor for XML DB. There is a lot of info out there on how to install XML DB. If you are upgrading an existing DB, you should install XML DB manually. Otherwise you can use the Database Configuration Assistant (DBCA).

XML DB can be managed with Oracle Enterprise Manager. The internal configuration is done via the "xdbconfig.xml" file.

Data Pump

I might need to take the contents of my development database with me. How should I do that? Glad you asked. Since Oracle 10g, the answer is most likely Data Pump. This features consists of three parts:

1. The clients expdp and impdp
2. The Data Pump API - DBMS_DATAPUMP
3. The MetaData API - DBMS_METADATA

The clients are similar to the original exp and imp utilities. They are not compatible with them. They just supersede them. The clients themselves use the Data Pump API internally. They programs are located in directory $ORACLE_HOME/bin. They can take a parameter file. You don't need to use these clients. You can use the API directly via the PL/SQL package.

DBA must create the directory objects used by Data Pump. You will need the following database privileges to take full advantage of the utility:

• EXP_FULL_DATABASE
• IMP_FULL_DATABASE  

Data Pump will use either direct path or external tables to handle the data. It decides which on what data you are trying to move. Sounds a bit like  sqlldr to me. These utilities do not support XML schemas. The export and import occur on the server, unlike the predecessors exp and imp.

One master process is created per job. That process controls the worker processes. There is also a master table which tracks progress. It can be used to restart a job that did not complete. This table gets dropped after the run is successful. You can check on the progress with the V$SESSION_LONGOPS view.

Here are the types of files managed by Data Pump:

• Dump files - the data
• Log files - messages
• SQL files - DDL to reconstruct

impdb is the Data Pump Inport. expdp is the Data Pump Export. They both use a dump file set, which are binary files containing the data. Do not run these as SYSDBA. Here are the modes in which these programs can operate:

• Full
• Schema
• Table
• Tablespace
• Transportable tablespace

Advanced Queuing (AQ)

Do you know what? I have never used Oracle's Advanced Queuing (AQ). That's no excuse for not knowing what it is. I did a little research this week. AQ is Oracle's version of message oriented middleware. Yeah. I know. Sounds like a good buzzword. In English, this is an asynchronous publish/subscribe model. One use is for web apps to communicate.

AQ was first released in Oracle 8. It became free of charge in Oracle 9.2. Later, it was folded into Oracle Streams in Oracle 10.1. The functionality is now known as Oracle Streams AC. That is because it is built on top of Oracle Streams. Actually, I think the low level block it is built on are Oracle database tables.

Here is the order of doing things in AQ. You create a type. Then you create a queue table. Next you create the actual queue. Once all that is done, you can enqueue and dequeue all you want. You should be aware of one piece of terminology. The messages are called the payload.

You can asynchronously queue and dequeue. Or you could use notifications to automatically dequeue. To do that you need to create a callback procedure, add a subscriber, and register the subscriber. The roles specific to AQ are AQ_ADMINISTRATOR_ROLE and AD_USER_ROLE.

DBMS_SCHEDULER

I am old fashioned. I use UNIX cron to kick off jobs. They might sqlplus into the database to run some SQL scripts. However Oracle has ways within the database to run scheduled jobs. I am just not that familiar with them. I think the old way was using DBMS_JOBS. Now we have a replacement (or at least an extension) in DBMS_SCHEDULER.

The package gives you procedures such to create a job, run a job, drop a job, enable/disable jobs, or create a schedule. You can provide a job to these procs. Or you can use a list of jobs. Be warned that if you chain jobs, any error halts the whole chain.

One way to do scheduling is to specify a frequency like DAILY or WEEKLY. You don't need to commit changes to have the jobs actually start. So they operate kinds of like DDL in that respect. Some good views of interest to see job info are DBA_SCHEDULER_JOBS and DBA_SCHEDULER_JOB_LOG.

The main privilege you need to run a job is CREATE_JOB. The super priv is SCHEDULER_ADMIN. But you should use that one sparingly. The package was first released in Oracle 10g. Other notable enhancements were the ability to run jobs on external servers in 11gR1. And we got email notifications in 11gR2.

I still am a newbie in the scheduling of jobs within the database. But I am starting to learn the landscape.

SQL*Loader Refresher

I have started searching for an Oracle development job. Optimally it will be one where I can do a lot of PL/SQL coding. Some jobs out there require experience with SQL*Loader. I have used the tool in the past. But not much. Time to refresh my memory.

SQL*Loader will take a data file as input, and put the data in your database tables. The main driver of reading in that file is the control file. The control file can specify a fixed datafile format. That has the best performance. Or it can specify a variable length. The length of each line can vary, with the size being set in the first couple character of the line.

You run SQL*Loader with the sqlldr command. You pass it a username and password. Plus you give it the name of the control file. It will generate a log file with the same name as the control file, but with a log extension, by default.

SQL*Loader can also generate a bad file. This will contain records rejected by SQL*Loader. It can also generate a discard file. That file has input records from the datafile that were not selected by the constraints specified in the control file. These are optional files to be generated.

There are a few methods that SQL*Loader can use to load data. The first is conventional path loading, where data is copied to a bind array, then inserted into the database table. The second is direct path loading, where blocks are built and written directly to the database. It is fast. The third is external table loading.

If you want to load an Excel file, which I sometimes do, you need to export it to CSV format first. There is a way to put the actual data being loaded right in the control file. This eliminates the need for a separate datafile. You just give them command "INFILE *" in the control file. Then you prepend your data section with the keyword BEGINDATA.

Selective loading of records can be accomplished with the WHEN clause. Just note that you can only use AND in the parts of that clause. OR is not allowed. You can specify the batch size using the ROWS parameter. You tell what table the data is going into using the INTO TABLE clause. That is followed by fields. and optionally their types.

Although I have never done it before, SQL*Loader can read data into collections such as nested arrays or VARRAYs.