The difference between CURRENT_DATE and SYSDATE is:
CURRENT_DATE is the client’s time zone
SYSDATE is server’s time zone
i.e. if you are in London and your Oracle database server is in New York:
CURRENT_DATE: 02/01/2013 10:26:25
SYSDATE: 02/01/2013 05:26:25
Some things on Oracle proved to be very useful for me. I’ll share them below because I’m sure they are useful for some of you too.
1. Searching for a column name
select * from all_tab_columns where table_name like ‘C%’ and column_name like ‘%ST%’
2. Add/delete a column
alter table account add account_number varchar2(30)
alter table account add (account_number varchar2(30), balance decimal(20,4))
alter table account drop column account_number
3. Change column name / data type
alter table account rename column account_no to account_number
alter table account modify account varchar2(35)
4. Change table name
alter table account rename to customer_account
5. Update a column based on another table
update account set account_number =
( select account_number
from account_mapping
where account_mapping.customer_id = account.customer_id
) where account_number is null
6. Create a table from another table
(this is very useful for backing up before updating)
create table account_bak as
select * from account where 1=2
7. Escaping & (ampersand)
update table1 set column1 = ‘M’ || chr(38) || ‘S’
In the last few months I used both SQL Server and Oracle every day. This is because the source system is in SQL Server and the data warehouse is in Oracle. I begin to miss little things on SQL Server that I don’t get in Oracle, and vice versa. I thought I’d write this experience down as it could be useful for others.
In this article, when I say Oracle I mean using Toad, whereas when I say SQL Server I mean using SSMS. You can use Toad with SQL Server, and you can use other UI for Oracle, but that’s for another article. Version? SQL Server: 2008, Oracle: 10g R2 (well doesn’t really matter 10 or 11 as I use Toad as UI).
Oracle: Control Enter
First is the Control Enter to run SQL at the cursor. In SQL Server you need to block the code you want to run before pressing F5. In Oracle if you press Control Enter, you don’t need to block anything. Very useful I use this every day.
Oracle: No need for “top 1000”
You don’ t need to type “top 1000” when selecting large table. It will only bring back 500 rows. Automatically. That can be useful and can be annoying. Strangely, I found it very useful.
Oracle: Column Selection
It is called Intellisense in SSMS 2005/8 but Toad one is better: it enables you to select the column you like. When you type table alias followed by dot, it shows the column names. But you have to alias the table first, i.e. “select from fact_interest_rate_swap S” then you go back, put your cursor between “select” and “from” and type “S.” (s dot) and it will list all columns of fact_interest_rate_swap table.
Oracle: Count(*) Works
In the last 6 months or so, I work in a project where we have a column called countrysomething and every time I type count(*) it shows this country column instead. It is so irritating that I have to carefully press escape after typing count but before typing “(“.
SQL Server: Copy including Header
This SQL Server feature in SSMS is very useful to put data into Excel. In Oracle/Toad we need to Export to clipboard get the header.
SQL Server: Object Explorer
It’s true the saying: you don’t know how valuable it is until you don’t have it. In Toad we have Object Pallette, but it’s not as good as SSMS’ object explorer where you can browse the columns, stored proc, etc. In Toad Object Pallette you can only browse the tables, not the columns.
SQL Server: Top N
We won’t know how useful this top N is until we don’t have it. In Oracle, a SQL guy will go wondering why “select top 100 * from table1” doesn’t work. It’s “select * from table1 where rownum <= 10”.
In Informix/Ingress it’s FIRST: select first 100 * from table1. And it’s very handy in Informix to have SKIP clause: select skip 10 first 100 * from table1.
In DB2 we have FIRST too: select * from table1 fetch first 100 rows only
Ending
Well it’s not 2 things. It’s more like 11 things I know, but who’s counting anyway? 🙂
For years the ETL for Oracle Data Warehousing has been OWB (Oracle Warehouse Builder). But in 2007 Oracle introduced another ETL tool called ODI, Oracle Data Integrator (not to confused with BODI, Business Object Data Integrator). ODI was originally from Sunopsis. Sunopsis had an ETL product called Data Conductor, famous for its ELT approach, i.e. load into the target server first, then use the power of the DB engine to transform and upsert into the target tables. This approach suited Oracle so Oracle bought Sunopsis in 2006 and branded Data Conductor as ODI.
It was (and still is) Oracle’s intention to combine OWB and ODI into 1 product. In January 2009 they bundled ODI and OWB into 1 license and called it ODIEE. Still two separate software, but 1 licence. Some of ODI functionalities were added into OWB and it was called OWBEE. Formal statement here. Formal OWB blog here. ODI product page here. Data Integration blog here. OWB product page here.
So today Oracle has 4 ETL products:
In the future they will be merged into 1 software: ODI. For now (2011), companies which are new to Oracle ETL should use ODI, because that is the future. ODI is the strategic product. Companies which already use OWB for a long time should use OWBEE. In the end, the old OWB will be gone, replaced by ODI. But they can’t do that now, because there are many companies using OWB at the moment. There must be a smooth way of migrating them to ODI, and there must be support for OWB for many more years. But there’s no escape that they must migrate all to ODI, just like DTS to SSIS.It will probably take 5 years for the world to change. For companies using OWB, this means a big project, and significant costs.
But there’s no “middle way”. Oracle as a company can’t maintain 2 ETL tools, because of the cost. They must have only 1 tool. ODI is the strategic product. In 5 years time this is ETL product that will survive. This is the product that will be enhanced and developed further. ODI is Oracle’s strategic ETL tool, as per the formal statement here.
The alternative is to move all ODI core features to OWB, which in my opinion can’t be done. There is a fundamental difference in principle of how they work. If OWB is like a house, we can’t just add the core ODI features on top that house. But we have to demolish that house, build a new foundation (the ODI way) then rebuild the house.
In addition to OWB & ODI, Oracle also has GoldenGate, a famous CDC (change data capture) tool that Oracle acquired in July 2009. GoldenGate capture the changes in the source systems by reading the transaction log file and apply the data changes to the target system such as DW or ODS using a native DB interface or ODBC. The component that applies the data to target is called Replicat. GoldenGate 11g Admin Guide is here, architecture diagram is on page 12. The real beauty of it is that GoldenGate can operate between various RDBMS, i.e. DB2, MySQL, Oracle, SQL Server, Sybase and Teradata, integrating data between them in real time (synchronous) or in batch using CDC. Using GoldenGate is not easy, there is a lot of command line interface. Not as easy as (and not as modern in UI) for example, DataMirror, DBMoto and Attunity. But functionality wise, GoldenGate is probably the richest.
One word about the term “Data Integration”. In 2006 all ETL companies seem to be in agreement that the word “ETL” had a bad perception. The word “ETL” only covered a small part of the scope. They all rebranded their product as “Data Integrator”. a) they could add/integrate data quality and data profiling into the product, b) they could get into MDM market with their tool, c) they could get into data integration projects (not only data warehousing projects), d) they can get into operational systems such as ERP, business systems, and ODS projects. I totally agreed. It should be called Integration, not ETL. Look at Business Objects. They called it BODI, Business Objects Data Integrator. SSIS: Integration Services. In 2007 in Boston I listened to Michael Gonzales’ ETL session at the TDWI conference. He said that “ETL is dead, it is now called Data Integration”. I totally agreed. But, in 2010, because everybody in the market (and the press) still call it ETL, the word ETL seems revived again. Hence the title of this post 🙂
If you find something incorrect above, I’d be grateful if you could let me know, either via comments or vrainardi@gmail.com, I will correct them.
If you are a MS BI developer or Architect (SS, IS, RS, AS) you need to look at Oracle BI. a) so that we know what’s out there, and b) we increase our understanding of our own area by studying other areas. When I ask people which BI stack did you implement? These days the answer is almost always MS BI. There other BI suites: SAS, SAP (BW & BO), IBM (Cognos + InfoSphere) and Oracle. Out of these, OBI is not the most popular (BO or Cognos probably is), but OBI is the “latest trend”, i.e. with the arrival of OBIEE 11g, in the 2nd half of 2010 people start considering OBI as an alternative to MSBI.
The documentation for OBIEE in general is here. Release 11g is here. Release 10g is here.
History
The OBIEE 11g originated from Siebel Analytics (latest version was 7.8, Jan 2006). Answers, Interactive Dashboards, and Delivers were from Siebel Analytics (read here). Discoverer was not from Siebel Analytics, it was from Fusion Middleware (see this book page 31), along with Forms, Reports and Portal. At that time, OBI was part of “Oracle Fusion Middleware 11g Release 1”, see here. Apart from OBI, OFM 11g R1 consists of Communication Services, Identity Management, SOA Suite, WebCenter, WebLogic, HTTP Server, and Application Development Framework.
So, in Jan 2006, OBI is located in 2 places: a) Siebel Analytics contains Answers, Delivers and Intelligent Dashboards, b) OFM 11g R1 contains Discoverers, Forms, Reports and Portal.
Then OBI 10g was released in June 2006 (10.1.3.2). It consisted of Answers, Delivers, Interactive Dashboards, BI Publisher, Disconnected Analytics. Discoverer 10g was a separate product, released in Dec 2004 (10.1.2, aka Drake), see this book page 4. Discoverer was a very mature product, has been in the market for a long time, first released April 1997. Other Oracle BI tools: Beans, Reports, Data Miner, OWB, Spreadsheet Add-In were all separate tools, not in the “OBI 10g” suite.
OBIEE 11g was released in July 2010. 10g’s Answers is renamed as Analysis. 10g’s Interactive Dashboards is renamed as Dashboards. 10g’s Delivers is renamed as Agents. New features added: Scorecards, KPI, Actions, Conditions, Filters, Prompts and OBI Add-in for MS Office. BI Publisher (Report Designer) and Real-Time Decision are also part of OBIEE 11g.
Components
I’m going to use 11g terms here, instead of 10g.
An Analysis is a DW/BI SQL query presented in the form of tables, pivot tables and charts. These tables and charts can then be included in a Dashboard. We can create a prompt in an Analysis, to allow the users to select a value to filter the result of the Analysis. We can integrate an Analysis with an Excel Internet Query (IQY) to enable us to run the Analysis from Excel. We can create an Agent from an Analysis to schedule the Analysis to run at certain time. We can use variables practically anywhere within an Analysis (title, column, formula, formatting conditions, SQL, header, etc).
A Dashboard is a collection of analysis. A Dashboard consists of one page or several pages (shown as “tabs” on the top of the Dashboard). Apart from displaying Analysis, a Dashboard page can also display images, text, alerts, action links, views, reports, URL links and embedded objects. There is a toolbar on the top right of the Dashboard containing buttons such as: Edit, Print, etc. We can create a Skin to change the appearance of a Dashboard, e.g. background colour, logo and style sheets. A Skin can be assigned to users so each user can have a different look. A Style control how a Dashboard are formatted, e.g. the colour, font & size of the text, table border, graph attributes. Styles are organised into folders that contains Cascading Style Sheets, images and graph templates. We can create a Dashboard Template, which acts as a starting point for users to build their own Dashboard pages.
A Briefing Book is a collection of Dashboards. A Briefing Book can also contain an individual Analysis and a BI Publisher Report. The Dashboards, Analyses and Reports in a Briefing Book can be static (can’t be updated) or updatable (the content is refreshed everytime we open it). We can export a Briefing Book to PDF. We can deliver a Briefing Book to users using an Agent.
BI Publisher is a tool to create and publish reports. We can read data from databases (using SQL), spreadsheets, Analysis and OLAP DB (using MDX). We can also read from LDAP, Web Services, HTTP feeds, and XML files. If the data source is unrelated (such as individual files), we can create relationships between data sources. Using Publisher we can create layouts for our reports and publish the reports to various output format, including: HTML, PDF, Excel, PowerPoint, XML, CSV, and Rich Text. The output can be sent to: printer, email, file, fax, WebDAV or FTP. We can split a single report into multiple sections, each section is sent to different recipients on multiple destinations, using different formats. This is called Bursting. We can create a Template that contains style information that can be applied to RTF layouts. For PDF output, we can split the PDF into several smaller files (called PDFZ). We can schedule a report to produce multiple outputs, each with different layout, and each is sent to different destinations. There is an interactive viewer, where users can sort or filter a table, scroll the table, get a pop-up graph, and do “propagated filtering” across different area of the report.
Real Time Decision (RTD) is a tool to create/model business processes, gather data/statistics, and make recommendations (all that is called an Inline Service). An Inline Service consists of several elements including: Performance Goals, Choices, Rules, Statistics Collector and Models. A Performance Goal is KPIs used for setting criteria for the scoring of choices. A Rule is used to target a segment of population. A Rule can also be used to decide whether a choice is eligible. A Model is self-learning and predictive, used for optimising decisions and providing real-time analysis. RTD’ is fully integrated with Eclipse development environment. We can deploy an Inline Service, run it to process requests, monitor its performance, gather and view the statistics, and fine-tune/optimise it.
How these components fits together is shown in OBIEE system architecture diagram here.
For my next DWBI project, I’m thinking about implementing Oracle 11g OLAP option instead of SSAS, so I’m comparing them.
Oracle OLAP blog is here. The Wiki is here. 11g OLAP user guide is here. OLAP Option documentation is here.
If you find something incorrect above, I’d be grateful if you could let me know, either via comments or vrainardi@gmail.com. I will correct them. Thanks again for Bud Eggress for your corrections.
Data Warehousing and Data Science 