(coffee break)
Spending lots of your time configuring RAC, freshing MVs, checking Grid Control charts..
So I go for a walk. Wander about, wonder...
Wanderlust
Oracle On-line Analytic Processing (OLAP) (I)
 |
 |
OLAP Cubes can play three (3) roles in an Oracle Installation:
(a) Full-featured, fully integrated multidimensional server
- Applications can query all data using a dimensional query language (Oracle OLAP API or MDX)
- OLAP engine runs within the kernel of Oracle Database.
- Dimensional objects (cubes, measures, dimensions, levels, hierarchies, attributes) are stored in Oracle Database in their native multidimensional format.
- Cubes and other dimensional objects are first class data objects represented in the data dictionary.
- Data security is administered in the standard way, by granting and revoking privileges to users and roles.
- Applications can query dimensional objects using SQL.
(b) A Summary management solution
- The Oracle cube is used to manage summary data and is accessed transparently by SQL-based BI applications as a Cube-organized Materialized View using Query rewrite feature.
- Cube-Organized MVs were introduced on Oracle 11g, and play the same role as table-based MVs
- Cube-organized MVs allow any application to transparently access summary data managed by the cube.
- Cube-organized MVs provide substantial query performance enhancement with much lower maintenance overhead than table-based MV alternatives
- With Cube-organized MVs, applications query the detail tables and the database automatically rewrites the query to access summary data in the materialized view.
To expose the cube as a Materialized View, you can use the Analytic Workspace Manager:
Both detail and summary data in the cube are exposed in the Materialized View.
When the detail tables are queried, the optimizer rewrite the query to use the cube-organized materialized view
SQL> select t.calendar_year_name, p.department_name, cu.region_name, sum(s.quantity) as quantity, sum(s.sales) as sales from times t, customers cu, products p, sales_fact s where cu.customer_key = s.customer and p.item_key = s.product and s.day_key = t.day_key group by t.calendar_year_name, p.department_name, cu.region_name; Explain plan: OPERATION ----------------------------------- SELECT STATEMENT HASH CUBE SCAN CB$SALES_CUBE
(c) A supplier of rich analytic content
- OLAP cubes are exposed as Cube views.
- Cube views include detail and aggregate data as individual columns (measures)
- Cube views can list complex analytic content, including hiearchical aggregations, statistical forecasts, additive and non-additive aggregations, calculated measures, etc
Cubes and Dimensions seen from a relational perspective
Since Oracle provides a complete SQL interface to the OLAP cube:
- Cubes and Dimensionas can be thought of as relational objects.
- Cubes as relational objects that offer improved performance and advanced analytic content, and
- Dimensions as relational objects that include columns with information useful to creating hiearchical queries.
- The cube is simply another data type in the database.
 |  |
Some benefits OLAP Cubes bring to Business Intelligence applications
(a) Improved query performance for Ad-hoc query patterns
- As query patterns become less predictable, creation and maintenance of materialized views for specific queries becomes impractical.
- Consider a data model with four dimensions (time, customer, product, channel), each with six levels of summarization (i.e in the time dimension: day, week, month, quarter, half year and year).
- In this case, there are (4**6 -1) = 4095 possible combinations representing sumary level data that users might query.
(b) Improved query performance for summary data
(c) Fast incremental update
(d)Rich analytic content
(e)Metadata that describes the logical business model and the relational representations of the cube
(Q) What two categories can OLAP Metadata be grouped into?
(a) Metadata about the Cube's structure, data, and how it is calculated
(b) Metadata about how the cube is represented for query using SQL cube views
- Description of the cube's structure include information on: dimensions, hierarchies, levels, attributes and measures.
- For measures, metadata is available describing how the cube is calculated and the calculation expression of a measure.
SQL> select table_name from dict where table_name like '%CUBE%'; TABLE_NAME ------------------------------ ... DBA_CUBES DBA_CUBE_ATTRIBUTES DBA_CUBE_ATTR_VISIBILITY DBA_CUBE_BUILD_PROCESSES DBA_CUBE_CALCULATED_MEMBERS DBA_CUBE_DIMENSIONALITY DBA_CUBE_DIMENSIONS DBA_CUBE_DIM_LEVELS DBA_CUBE_DIM_MODELS DBA_CUBE_DIM_VIEWS DBA_CUBE_DIM_VIEW_COLUMNS TABLE_NAME ------------------------------ DBA_CUBE_HIERARCHIES DBA_CUBE_HIER_LEVELS DBA_CUBE_HIER_VIEWS DBA_CUBE_HIER_VIEW_COLUMNS DBA_CUBE_MEASURES DBA_CUBE_VIEWS DBA_CUBE_VIEW_COLUMNS ... => Checking the existing dimensions in SALESTRACK analytic workspace SQL> select * from dba_cube_dimensions; OWNER DIMENSION_NA DIMENSION_TYPE AW_NAME DEFAULT_HIERARCHY_NAME DESCRIPTION ---------- ------------ ----------------- ------------ ------------------------------ -------------------- OLAPTRAIN PRODUCT2 STANDARD SALESTRACK STANDARD product2 OLAPTRAIN CHANNEL STANDARD SALESTRACK SALES_CHANNEL Channel OLAPTRAIN GEOGRAPHY STANDARD SALESTRACK REGIONAL Geography OLAPTRAIN PRODUCT STANDARD SALESTRACK STANDARD Product OLAPTRAIN TIME TIME SALESTRACK CALENDAR Time
You can use SQL Developer to:
- check existing hierachies, levels and order within hierarchies in the 'PRODUCT2' dimension.
- List existing cubes, measures within the cube, whether a measure is derived or stored and the equation that generates that measure:
Cube Views
- OLAP Cubes are exposed through views on a star schema.
- As the structure of dimensions and cubes are updated,views are automatically maintained
- If you add new measures, the cube view will be updated
- Views are always in sync with the underlying dimensions and cubes
- A single cube view exposes (as columns) all the aggregations and all the calculations for a cube
SQL> select * from dba_cube_views; OWNER CUBE_NAME VIEW_NAME ---------- --------------- ------------------- OLAPTRAIN FORECAST FORECAST_VIEW OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW OLAPTRAIN NEW_CUBE NEW_CUBE_VIEW SQL> select owner, cube_name, view_name, column_name from dba_cube_view_columns; OWNER CUBE_NAME VIEW_NAME COLUMN_NAME ---------- --------------- ------------------------------ ------------------------------ OLAPTRAIN FORECAST FORECAST_VIEW BEST_FIT OLAPTRAIN FORECAST FORECAST_VIEW LINEAR_REGRESSION OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW SALES OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW QUANTITY OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW SALES_YTD OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW SALES_YTD_PY ...
=> The Cube's SQL interface is also available in SQL Developer:
Using Oracle External Tables
- External tables do not reside in the database, and can be in any format for which an access driver is provided.
- You can select, join, or sort external table data.
- You can also create views and synonyms for external tables.
- You CANNOT execute DML operations (UPDATE, INSERT, or DELETE) , and CANNOT create indexes on external tables.
External tables and Data Warehouse
- Exporting data from a database:
- External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump.
- External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.
Creating External tables
CREATE TABLE...ORGANIZATION EXTERNALstatement.- This statement creates only metadata in the data dictionary.
- External tables can be thought of as views that allows running any SQL query against external data without requiring that the external data first be loaded into the database.
Accessing External Data
- An access driver is the actual mechanism used to read the external data in the table.
- When you use external tables to unload data, the metadata is automatically created based on the data types in the SELECT statement.
Oracle Database provides two access drivers for external tables:
- ORACLE_LOADER driver (default)
- allows the reading of data from external files using the Oracle loader technology.
- provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility.
- ORACLE_DATAPUMP driver
- lets you unload data: read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.
Scenario 1:
Unload data to flat files and create External Table.
ORACLE_LOADER driver
Step 1 - Export data from table sh.customers
/* Step 1 - Export data from table sh.customers
Procedure UNLOAD_DATA receives table_name and table_owner as parameter and
unloads the table data to a flat file */
set serveroutput on
DECLARE
procedure unload_data(
p_table_name in varchar2,
p_owner in varchar2)
IS
sqltext varchar2(1000);
v_textrecord varchar2(2000);
cv sys_refcursor;
fdest utl_file.file_type;
type FileAttrRec is Record (
vfilexists BOOLEAN,
vfilelength number,
vblocksize binary_integer);
vfilerec fileattrrec;
vfilename varchar2(30);
procedure get_sql (
p_sqltext in out varchar2,
p_table_name in varchar2,
p_owner in varchar2)
IS
cursor c is
select column_name
from dba_tab_columns
where table_name = p_table_name and owner = p_owner
order by column_id;
i integer;
sql_stmt varchar2(2000);
from_clause varchar2(100);
begin
i :=0;
sql_stmt := 'SELECT ';
from_clause := 'FROM ' || p_owner ||'.' || p_table_name;
for r in c loop
if i = 0 then
sql_stmt := sql_stmt || ' ' || r.column_name;
i := 1;
else
sql_stmt := sql_stmt || '|| ''|''|| ' || r.column_name;
end if;
end loop;
sql_stmt := sql_stmt || ' as qrystr ' || chr(10) || from_clause;
p_sqltext := sql_stmt;
-- dbms_output.put_line( sql_stmt);
end get_sql;
begin
-- 1. Get Query SQL
dbms_output.enable(20000);
sqltext := '';
get_sql(sqltext, p_table_name, p_owner);
--dbms_output.put_line('Select stmt:' || sqltext);
-- 2. Open cursor for query. Write each row in the OS file
vfilename := 'unload_' || p_owner ||'_'|| p_table_name || '.dat';
utl_file.fgetattr('DATA_DUMP_DIR', vfilename, vfilerec.vfilexists,
vfilerec.vfilelength, vfilerec.vblocksize);
if vfilerec.vfilexists then
fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'a', 2048);
dbms_output.put_line('Destination file exists. Appending..');
else
fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'w', 2048);
end if;
open cv for sqltext;
LOOP
fetch cv into v_textrecord;
EXIT WHEN cv%NOTFOUND;
utl_file.put_line(fdest, v_textrecord, true);
END LOOP;
utl_file.fclose(fdest);
close cv;
END unload_data;
BEGIN
unload_data('CUSTOMERS' , 'SH');
END;
Step 2 - Split unloaded file and create external table using the resulting files
$ ls -ltr
total 482928
...
-rw-r--r-- 1 oracle oinstall 10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-- spliting the file in two smaller files
$ split -30000 unload_SH_CUSTOMERS.dat unload_SH_CUSTOMERS.dat
$ ls -ltr
total 503760
...
-rw-r--r-- 1 oracle oinstall 10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-rw-r--r-- 1 oracle oinstall 5768182 2012-03-20 22:15 unload_SH_CUSTOMERS.dataa
-rw-r--r-- 1 oracle oinstall 4893721 2012-03-20 22:15 unload_SH_CUSTOMERS.datab
Step 2 - Create external table using unloaded file(s)
-- create the external table
SQL> Create table ext_customers1
(
"CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20 BYTE),
"CUST_LAST_NAME" VARCHAR2(40 BYTE),
"CUST_GENDER" CHAR(1 BYTE),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20 BYTE),
"CUST_STREET_ADDRESS" VARCHAR2(40 BYTE),
"CUST_POSTAL_CODE" VARCHAR2(10 BYTE),
"CUST_CITY" VARCHAR2(30 BYTE),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40 BYTE),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
"CUST_INCOME_LEVEL" VARCHAR2(30 BYTE),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30 BYTE),
"CUST_TOTAL" VARCHAR2(14 BYTE),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile data_pump_dir:'cust1ext%a_%p.bad'
logfile data_pump_dir:'cust1ext%a_%p.log'
fields terminated by '|'
missing field values are null
)
LOCATION ('unload_SH_CUSTOMERS.dataa', 'unload_SH_CUSTOMERS.datab')
)
PARALLEL
REJECT LIMIT UNLIMITED;
table EXT_CUSTOMERS1 created.
SQL> select table_name, type_name, default_directory_name, reject_limit, access_parameters, property
from dba_external_tables;
TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME REJECT_LIMIT ACCESS_PARAMETERS
--------------- --------------- ------------------------ --------------- ------------------------------------------
EXT_CUSTOMERS1 ORACLE_LOADER DATA_PUMP_DIR UNLIMITED records delimited by newline
badfile data_pump_dir:'cust1ext%a_%p.bad'
logfile data_pump_dir:'cust1ext%a_%p.log'
fields terminated by '|'
missing filed values are null
SQL> select count(*) from ext_customers1;
COUNT(*)
----------
55500
Scenario 2: Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver
Step 1
- (a) Use CREATE TABLE ... ORGANIZATION EXTERNAL ... AS SELECT ...
- This will create a file with the data resulting from the specified query.
- The file is created with a binary format that can only be read by the ORACLE_DATAPUMP access driver
- The example below creates an external table and populates the dump file for the external table with the data from table sh.customers.
connect sh/**;
SQL> create table customers1_xt
organization external
(
type oracle_datapump
default directory data_pump_dir
location ('customers1_xt.dmp')
)
as select * from sh.customers;
table created.
SQL>
(b) Check the OS files created:
$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall 123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
(c) Compare descriptions of customers and customers1_xt tables.
SQL> desc customers;
Name Null Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
SQL> desc customers1_xt;
Name Null Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
(d) Check data on customers1_xt and compare with original table
SQL> select count(*) from customers1_xt;
COUNT(*)
----------------------
55500
SQL> select * from customers minus select * from customers1_xt;
no rows selected
(e) You CANNOT perform DML on the external table:
SQL> delete from customers1_xt where cust_id = 100055;
delete from customers1_xt where cust_id = 100055
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
(f) You can also Create an external table with multiple dump files and with a degree of parallelism higher than one.
SQL> create table customers1_xtp3
organization external
(
type oracle_datapump
default directory data_pump_dir
location('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp' )
)
parallel 3
as select * from sh.customers;
$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall 123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2690.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2737.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2739.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2741.log
-rw-r----- 1 oracle oinstall 2990080 2012-03-21 10:13 customers1_xtp32.dmp
-rw-r----- 1 oracle oinstall 3792896 2012-03-21 10:13 customers1_xtp33.dmp
-rw-r----- 1 oracle oinstall 3592192 2012-03-21 10:13 customers1_xtp31.dmp
(g) You can transfer or copy the dump file(s) and use them for another external table either in the same or in a different database.
SQL> Create table customers2_xt
(
"CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20 BYTE),
"CUST_LAST_NAME" VARCHAR2(40 BYTE),
"CUST_GENDER" CHAR(1 BYTE),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20 BYTE),
"CUST_STREET_ADDRESS" VARCHAR2(40 BYTE),
"CUST_POSTAL_CODE" VARCHAR2(10 BYTE),
"CUST_CITY" VARCHAR2(30 BYTE),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40 BYTE),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
"CUST_INCOME_LEVEL" VARCHAR2(30 BYTE),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30 BYTE),
"CUST_TOTAL" VARCHAR2(14 BYTE),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY data_pump_dir
LOCATION ('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp')
);
SQL> select count(*) from customers2_xt;
COUNT(*)
----------------------
55500
SQL> Select * from sh.customers minus select * from customers2_xt;
no rows selected.
Subscribe to:
Posts (Atom)