GO New Orleans Saints!

I hope they win the super bowl and knock off those dumb Colts. They beat my Bears in 2006-2007, so GO SAINTS! Who DAT?!

Date Dimensions anybody?

Here is some sample code for creating a date dimension, It's been out there before, My colleague Dave posted this on his blog at one point. I'd like to take a moment and maybe look at how Oracle implements their date dim with the OWB date dimension wizard....hmmmm!

SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,
      dt date_value,
      TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,
      TO_CHAR (dt, 'Mon DD, YYYY') date_name,
      TO_CHAR (dt, 'YYYY') year_num,
      TRUNC (dt, 'YYYY') year_start_date,
      ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,
      TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,
      'Q' || TO_CHAR (dt, 'Q') quarter_name,
      TRUNC (dt, 'Q') quarter_start_date,
      ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,
      TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,
      TO_CHAR (dt, 'MM') month_num,
      TO_CHAR (dt, 'Mon') month_short_name,
      TO_CHAR (dt, 'Month') month_long_name,
      TRUNC (dt, 'MM') month_start_date,
      LAST_DAY (dt) month_end_date,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,
      TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,
      TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,
      TO_CHAR (dt, 'WW') week_of_year_num,
      TO_CHAR (dt, 'W') week_of_month_num,
      TRUNC (dt, 'W') week_start_date,
      TRUNC (dt, 'W') + 6 week_end_date,
      TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,
      TO_CHAR (dt, 'D') weekday_num,
      TO_CHAR (dt, 'Day') weekday_name
 FROM (SELECT     TO_DATE (:start_date) + ROWNUM dt
             FROM DUAL
       CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));

Metadata reporting on the OWB 11gR1 repository- options?

There are quite a few clients, coworkers and managers I've met with that feel the documentation capabilities of OWB are, shall we say....lacking a bit. Sure, there is the OWB online repository browser which is quite handy and gives you a lot of information. There is the metadata lineage and impact analysis diagramming which again is quite handy. There are various tricks one can do with OMB*PLUS or experts to whip up some simple documentation. Of course when you want to get specific, then it becomes a challenge.

Say you want a report, with minimal work, that is in a CSV or excel spreadsheet showing all sorts of values that you figure you can grab from the repository somehow. Again, you can accomplish some great things with OMB and experts, and I have done this, but many times it is too time intensive and overkill. Sometimes you might consider UDOs (User Defined Objects) which can be created with OMB scripting, which give you additional fields and properties to use on your objects. This can make things easy when you are in the beginning of a project and you can tackle documenting things as you go along.

What if you're 50% or even 90% done with your project, and someone demands something - fast. I say, look at the repository quickly, write some SQL, and get it out there fast.

Well in OWB 11gR1, the repository is broken down into things called workspaces. These workspaces have separate owners, and they have users themselves. So if you want to access the OWBSYS schema's many views that hold important metadata, you'll need to set some context for the user accessing the data- namely the various workspace owners. Below are a few steps that have been published in forums and the owb blog as well that outline what you should do if you cannot see any data in OWBSYS's views.....

First read this.

Then....


THEN, here are 2 sample scripts that are in the oracle forums as well. These should get you started, and get your mind thinking i terms of what other views you'd like to look at, and what other columns are you interested in...as well as the various ways to join the views!


--This script focuses more on mapping contents...
select
distinct 'TARGET',
comp.map_name,
comp.data_entity_name,
comp.operator_type
from
all_iv_xform_map_components comp,
all_iv_xform_map_parameters param
where
lower(operator_type)
in ('table', 'view', 'dimension', 'cube')
and param.map_component_id = comp.map_component_id
and param.source_parameter_id is not null
UNION
select
distinct 'SOURCE',
t1.c1,
t1.c2,
t1.c3
from
(select
comp.map_name c1,
comp.data_entity_name c2,
comp.operator_type c3,
max(param.source_parameter_id) c4
from
all_iv_xform_map_components comp,
all_iv_xform_map_parameters param
where
lower(operator_type) in
('table', 'view', 'dimension', 'cube')
and param.map_component_id = comp.map_component_id
group by
comp.map_name, comp.data_entity_name, comp.operator_type) t1
where t1.c4 is null
order by 2,1

--This script focuses more on source to target tables and column mappings

SELECT
S_TBL_LOC ,
S_TBL_name,
S_FLD_name,
T_TBL_LOC ,
T_TBL_name,
T_FLD_name
FROM
(
SELECT
parms.map_component_id S_COMP_id ,
parms.parameter_id S_PARM_id ,
colms.entity_name S_TBL_name ,
colms.column_name S_FLD_name ,
tbls.schema_name S_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
,
(
SELECT
parms.map_component_id T_COMP_id ,
parms.parameter_id T_PARM_id ,
parms.source_parameter_id T_Sid ,
colms.entity_name T_TBL_name ,
colms.column_name T_FLD_name ,
tbls.schema_name T_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NOT NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
WHERE
S_PARM_id=T_Sid

Gartner's take on oracle- quite positive.

see article here

Data Warehousing Training

I just got done custom creating an advanced data warehousing class, along with updating our beginner course to utilize Oracle 11g technologies. Whew, talk about a lot of work and only a short time to get it done! To summarize, the course that I teach is a full 5 day class on the following topics:

-Beginner Course Topics-
Beginning Data Warehousing
Dimensional Modeling
Oracle 11g Database options for Data Warehousing
Oracle Warehouse Builder OWB 11gR1 introduction
OWB11g Architecture and Installation
OWB11g and OWB 10gR2 differences, similarities
Exploring OWB11g Interface
Common setup of typical scenarios with remote machines and schemas
Building ETL Mappings from source to staging to target
Deep-Dive into OWB Mapping best practices and tips with the operators
Understanding Data Quality
Data Profiling and Data Rules Option within OWB11g
Advanced operators- Match & Merge, pre-post mapping operators, table functions, etc.
Understanding Workflow installation and Process Flow Basics

-Advanced Course Topics-
Oracle OLAP
Understanding Oracle's Position on OLAP & Hyperio Essbase
How to use OWB to construct full MOLAP cubes and how it differs from ROLAP
Extensive ETL Lab (2-3 hour lab encompassing many advanced techniques)
Pluggable Mappings
The Metadata Manager (Impact, Dependency, and Propagation)
Lifecycle Management (MDLs, Snapshots, Signatures, importation, exportation, comparison of
snapshots, etc.)
Security in OWB
Extra detail on Process Flows and Scheduling
OMB*PLUS scripting
OWB Experts
Advanced Tips and Tricks for project management, and high Performance


...and that's all. I built out about 90% of this class and taught it recently to pretty good reviews... Let's hope there's more students in store as I prepare to build out a new offering to my students!

Cheers
-Greg