Humble Trader

Tuesday, January 31, 2006

PL/SQL Library

This post contains links to other posts that make up a library of PL/SQL Functions, Procedures and Packages:

posted by Cat Play Crew at 11:20 pm | 0 comments

Monday, January 30, 2006

PL/SQL Library Header

Pre-Requisites:

...

Code:

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

-- ------------------------------------------------------------------------- --
--                                                                           --
--    Title: p_script_lib.sql                                                --
--   Author: Steve Roach                                                     --
--                                                                           --
--  Description: A set of library functions and procedures to ...            --
--                                                                           --
--    Functions: function                                                    --
--                  ...                                                      --
--                                                                           --
--   Procedures: procedure                                                   --
--                  ...                                                      --
--                                                                           --
-- Installation: To install for production, run this script as-is. This will --
--               install the package in the target database.                 --
--               To install for debug, edit the 'ALTER SESSION' statement,   --
--               below, changing 'debug: FALSE' to 'debug: TRUE' before      --
--               running the script.                                         --
--                                                                           --
-- ------------------------------------------------------------------------- --

-- Set flags for running debug mode:
ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE';

-- Package definition.
CREATE OR REPLACE PACKAGE p_script_lib
AS
   FUNCTION function(
      ...
      ...                  )
   RETURN ...;

   FUNCTION procedure(
      ...
      ...                  );
END p_script_lib;
/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY p_script_lib
AS
   -- Description...
   FUNCTION function(
      ...
      ...                  )
   RETURN ...
   IS
      ...
   BEGIN
      $IF $$debug
      $THEN
         dbms_output.put_line('function START');
      $END

      NULL;
      ...

      $IF $$debug
      $THEN
         dbms_output.put_line('RETURN = ' || ...);
         dbms_output.put_line('function END');
      $END

      RETURN ...;
   END function;

   -- Description...
   PROCEDURE procedure(
      ...
      ...                  )
   IS
      ...
   BEGIN
      $IF $$debug
      $THEN
         dbms_output.put_line('procedure START');
      $END

      NULL;
      ...

      $IF $$debug
      $THEN
         dbms_output.put_line('procedure END');
      $END
   END procedure;
END p_script_lib;
/

SHOW ERRORS


Test Script

posted by Cat Play Crew at 11:01 pm | 0 comments

Sunday, January 29, 2006

Time Dimension - Configuration

Pre-Requisites:

Database Objects:


Script Sourced:
  • tob_d_time:
    • Type: Scalar Object Type
    • Deployed to: whs@whs
    • Support Scripts:
      • Linux script library/whs/cre_tob_d_time.sql
      • Linux script library/whs/dro_tob_d_time.sql
  • tta_d_time:
    • Type: Table Type
    • Deployed to: whs@whs
    • Support Scripts:
      • Linux script library/whs/cre_tta_d_time.sql
      • Linux script library/whs/dro_tta_d_time.sql
      • Linux script library/whs/rec_tta_d_time.sql
  • f_d_time:
    • Type: Database Stored Function
    • Deployed to: whs@whs
    • Support Scripts:
      • Linux script library/whs/f_d_time.sql
Designer Sourced:
  • s_d_time:
    • Type: Sequence
    • Deployed to: whs@whs
OWB Sourced:
  • Deployed to WHS_TGT
    • Mappings:
      • M_W2W_D_TIME
    • Dimensions:
      • D_TIME
    • Connectors:
      • WHS_CTL
  • Deployed to OWF:

posted by Cat Play Crew at 10:59 pm | 0 comments

General Purpose Script Header

This template is used as a header for general purpose scripts:

-- ------------------------------------------------------------------------- --
--                                                                           --
--   Title: title                                                            --
--  Author: Steve Roach                                                      --
--    Date: DD-MON-YYYY                                                      --
-- Version: 01.00                                                            --
--                                                                           --
-- Description: Description.                                                 --
--                                                                           --
-- History:                                                                  --
--                                                                           --
-- Date      Ver   Author Description                                        --
-- --------- ----- ------ -------------------------------------------------- --
-- DD-MON-YY 01.00 SRR    First release                                      --
--                                                                           --
-- ------------------------------------------------------------------------- --

posted by Cat Play Crew at 10:24 pm | 0 comments

PL/SQL Function Header

This template is used as a header for PL/SQL Functions:

-- ------------------------------------------------------------------------- --
--                                                                           --
--   Title: title                                                            --
--  Author: Steve Roach                                                      --
--    Date: DD-MON-YY                                                        --
-- Version: 01.00                                                            --
--                                                                           --
-- Description: Description.                                                 --
--                                                                           --
--  Parameters:     IN:                                                      --
--              IN/OUT:                                                      --
--                 OUT:                                                      --
--                                                                           --
--              Return:                                                      --
--                                                                           --
-- History:                                                                  --
--                                                                           --
-- Date      Ver   Author Description                                        --
-- --------- ----- ------ -------------------------------------------------- --
-- DD-MON-YY 01.00 SRR    First release                                      --
--                                                                           --
-- ------------------------------------------------------------------------- --

/
SHOW ERRORS

posted by Cat Play Crew at 8:40 pm | 0 comments

Control Repository - Functional Specification

This repository consists of a number of components. The schema itself is hand cut, maintained in scripts and deployed to the ctl database.

This repository contains the objects and scripts that record and monitor data movements between data repositories. As such, it contains references to the repositories themselves, all of the mappings and histories of mapping runs.

It also contains information about specific classes of mappings that are placed into generic groups (e.g. the generic HTML components), although the data processing code is placed in the relevant repository.

posted by Cat Play Crew at 4:00 pm | 0 comments

Saturday, January 28, 2006

Time Dimension - Process

Process - Initial and Subsequent Load:

Schedule:

  • Run once and then checked annually to see if it needs to be run again.
Pre-Processing:
  • None.
Staging:
  • None.
Validation:
  • None.
ODS:
  • None.
Warehouse:
  • Process name: M_W2W_D_TIME
  • Description: Pre-load the Time Dimension with natural and financial calendar data.
  • This is based on the Oracle supplied example (found in /u01/app/oracle/10.2.0.1/wb01/owb/misc/time on my server) and is implemented with the use of a Table Function. Table Functions are not directly developed in OWB (at least not is the current release), rather, they use objects independently loaded into the database. These objects are:
    • tob_d_time: An object type that describes the parameters to a function that generates the data.
    • tta_d_time: A table type based on the tob_d_time object type.
    • d_time_s: A sequence used to generate level keys for the Time Dimension.
    • f_d_time: A pipelined table function that generates the data.
  • tob_d_time: This object type consists of the following components:
Component
Datatype
DAY_IDNUMBER
FMN_IDNUMBER
FQT_IDNUMBER
FWK_IDNUMBER
FYR_IDNUMBER
NMN_IDNUMBER
NQT_IDNUMBER
NWK_IDNUMBER
NYR_IDNUMBER
DAY_END_DATE
DATE
DAY_JULIAN_DATE
NUMBER
DAY_LAST_DAY_IN_MONTHVARCHAR2(1)
DAY_LAST_DAY_IN_QUARTERVARCHAR2(1)
DAY_NAMEVARCHAR2(10)
DAY_SHORT_NAMEVARCHAR2(3)
DAY_START_DATEDATE
DAY_WEEKDAYVARCHAR2(1)
FMN_FULL_NAMEVARCHAR2(25)
FMN_MONTHNUMBER(2)
FMN_NAMEVARCHAR2(10)
FMN_ORDERINGNUMBER(6)
FMN_SHORT_NAME
VARCHAR2(9)
FQT_FULL_NAMEVARCHAR2(25)
FQT_NAMEVARCHAR2(3)
FQT_ORDERINGNUMBER(5)
FQT_QUARTERNUMBER(1)
FQT_SHORT_NAMEVARCHAR2(11)
FWK_FULL_NAMEVARCHAR2(25)
FWK_NAMEVARCHAR2(3)
FWK_ORDERINGNUMBER(6)
FWK_WEEKNUMBER(2)
FYR_DAYS_IN_YEARNUMBER(3)
FYR_FULL_NAMEVARCHAR2(25)
FYR_NAMEVARCHAR2(9)
FYR_SHORT_NAMEVARCHAR2(20)
FYR_YEARNUMBER(4)
NMN_DAYS_IN_MONTHNUMBER(2)
NMN_MONTHNUMBER(2)
NMN_NAMEVARCHAR2(10)
NMN_ORDERINGNUMBER(6)
NMN_SHORT_NAMEVARCHAR2(3)
NQT_DAYS_IN_QUARTERNUMBER(2)
NQT_NAMEVARCHAR2(11)
NQT_ORDERINGNUMBER(5)
NQT_QUARTERNUMBER(1)
NQT_SHORT_NAMEVARCHAR2(7)
NWK_NAMEVARCHAR2(3)
NWK_ORDERINGNUMBER(6)
NWK_WEEKNUMBER(2)
NYR_DAYS_IN_YEARNUMBER(3)
NYR_NAMEVARCHAR2(4)
NYR_YEARNUMBER(4)

  • tta_d_time: This table type is a table of tob_d_time.
  • d_time_s: A sequence created with all clauses allowed to default.
  • f_d_time: This function has the following specification:
    • Parameters:
      • p_start_date VARCHAR2
      • p_end_date VARCHAR2
    • Return tob_time_d PIPELINED
    • Declaration:
      • Declare a record with type tob_time_d and set all components to NULL.
      • Declare local variables as required; one for each tob_time_d component and any needed control variables.
      • Declare exceptions:
        • e_date_span: Raised if passed parameters are insane.
    • Processing:
      • Determine start date, end dates and number of days in range from parameters and check for sanity. Raise e_date_span if there are no days to process.
      • Calculate calendar week of the start date - 1 day using the Time / Date PL/SQL Library.
      • Calculate calendar quarter of the start date - 1 day using the Time / Date PL/SQL Library.
      • Calculate financial week of the start date - 1 day using the Time / Date PL/SQL Library.
      • Calculate financial month of the start date - 1 day using the Time / Date PL/SQL Library.
      • Calculate financial quarter of the start date - 1 day using the Time / Date PL/SQL Library.
      • Calculate financial year of the start date - 1 day using the Time / Date PL/SQL Library.
      • Loop For each date in range (current_date):
        • Calculate the DAY attributes:
          • ID: d_time_s.NEXTVAL.
          • START_DATE: midnight on the current date.
          • END_DATE: 23:59:59 on the current date.
          • JULIAN_DATE: use date/time formatting.
          • NAME: use date/time formatting.
          • SHORT_NAME: use date/time formatting.
          • WEEKDAY: 'Y' if Mon-Fri, otherwise 'N'.
          • LAST_DAY_IN_QUARTER: 'Y' if 31st Mar, 30th Jun, 30th Sep or 31st Dec, otherwise 'N'.
          • LAST_DAY_IN_MONTH: 'Y' if tomorrow is 1st, otherwise 'N'.
        • Calculate the NAT_YEA attributes:
          • ID: If 1st Jan or first time round loop then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • YEAR: use date/time formatting.
          • NAME: use date/time formatting.
          • DAYS_IN_YEAR: If 29th Feb YEAR exists = 366, otherwise - 365.
        • Calculate the NAT_QTR attributes:
          • ID: If 1st Jan, 1st Apr, 1st Jul, 1st Oct or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • QUARTER: If 1st Jan = 1, if 1st Apr = 2, 1st Jul = 3, if 1st Oct = 4.
          • NAME: Generate cardinal value using the Number PL/SQL Library || ' Quarter'.
          • SHORT_NAME: Generate cardinal value using the Number PL/SQL Library || ' Qtr'.
          • ORDERING: Concatenate NAT_YEA.YEAR to QUARTER.
          • DAYS_IN_QUARTER: Number of days between first and last date of quarter.
        • Calculate the NAT_MTH attributes:
          • ID: If 1st or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • MONTH: use date/time formatting.
          • NAME: use date/time formatting.
          • SHORT_NAME: use date/time formatting.
          • ORDERING: Concatenate NAT_YEA.YEAR to MONTH (left padded with zeros to two digits).
          • DAYS_IN_MONTH: use date/time formatting.
        • Calculate the NAT_WEK attributes:
          • ID: If Monday or first time round loop then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • WEEK:
            • If current date is 1st Jan then 1, otherwise;
            • If Monday then increment from last iteration, otherwise use last value.
          • NAME: Generate cardinal value (1st, 2nd, etc.) from WEEK using the Number PL/SQL Library.
          • ORDERING: Concatenate NAT_YEA.YEAR to WEEK (left padded with zeros to two digits).
        • Calculate the FIN_YEA attributes:
          • ID: If 1st Jul then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • YEAR: If 1st Jul then increment from last iteration, otherwise use the local value from last iteration.
          • NAME: Concatenate YEAR - 1 || '/' || YEAR
          • SHORT_NAME: Concatenate NAME || 'Fin yr'
          • FULL_NAME: Concatenate NAME || 'Financial Year'
          • DAYS_IN_YEAR: If 29th Feb || YEAR exists = 366, otherwise - 365.
        • Calculate the FIN_QTR attributes:
          • ID: If 1st Jul, 1st Oct, 1st Jan, 1st Apr or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • QUARTER: If 1st Jul = 1, if 1st Oct = 2, if 1st Jan = 3, if 1st Apr = 4.
          • NAME: Generate cardinal value (1st, 2nd, etc.) from QUARTER using the Number PL/SQL Library.
          • SHORT_NAME: NAME || ' Fin Qtr'
          • FULL_NAME: NAME || ' Financial Quarter'
          • ORDERING: Concatenate FIN_YEA.YEAR to QUARTER.
        • Calculate the FIN_MTH attributes:
          • ID: If 1st or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • MONTH: If 1st then then increment from last iteration. If MONTH = 13 then = 1.
          • NAME: Generate cardinal value(1st, 2nd, etc.) from MONTH using the Number PL/SQL Library.
          • SHORT_NAME: NAME || ' Fin Mth'
          • FULL_NAME: NAME || ' Financial Month'
          • ORDERING: Concatenate FIN_YEA.YEAR to MONTH (left padded with zeros to two digits).
        • Calculate the FIN_WEK attributes:
          • ID: If Monday or first time round loop then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
          • WEEK:
            • If current date is 1st Jul then 1, otherwise;
            • If Monday then increment from last iteration, otherwise use last value.
          • NAME: Generate cardinal value (1st, 2nd, etc.) from WEEK using the Number PL/SQL Library.
          • FULL_NAME: NAME || ' Financial Week'
          • ORDERING: Concatenate FIN_YEA.YEAR to WEEK (left padded with zeros to two digits).
        • Load the output record with the calculated values and pipe it out.

    • The M_W2W_D_TIME Mapping:
      • Name Structure:
        • M: Mapping
        • W2W: Internal to whs
        • D_TIME: Operates on the Time Dimension.
      • Description:
        • This mapping loads the Time Dimension (D_TIME).
      • Input Parameters:
        • Start Date: Date of earliest row that will be loaded into the Time Dimension.
        • Number of Years: Number of years worth of data, starting at the Start Date that will be loaded into the Time Dimension.
      • Pre-Mapping - CTL.PK_CTL_GEN.INITIALISE_MAPPING:
        • Inform Control Database of startup.
        • Get Run Number and Current Run Date.
      • Constants:
        • Mapping Name: M_W2W_D_TIME
      • Stages:
        • Table Function:
          • Mapped to the f_d_time function
          • Inputs:
            • Start Date
            • Number of Years
          • Properties:
            • In group:
              • Input Parameter Type: SCALAR
              • Start Date Attribute:
                • Position: 1
              • Number of Years:
                • Position: 2
            • Out group:
              • Return Table of Scalar: FALSE
              • Attributes:
                • Type Attribute Name: Name of TOB_D_TIME attribute.
        • Map the output of the f_d_time Table Function to d_time.
        • Connect Run No and Current Run Date to d_time.
      • Post-Mapping - CTL.PK_CTL_GEN.FINALISE_MAPPING:
        • Inform Control Database of completion.

  • The PF_D_TIME Process Flow:
    • Module: PM_AD_HOC
    • Package: AD_HOC
    • Name: PF_D_TIME
    • Description: Load the D_TIME Time Dimension.
    • Type: Wrapper
Post-processing:
  • None.

posted by Cat Play Crew at 3:19 pm | 0 comments

Dimension Documentation - Process

This section describes the process and mappings that populate the dimension. Potentially, data could come from different sources and at different times. In this case all processes and schedules should be described.

Process - Process A:

Schedule:

  • Once a day at approximately 1:00am / Ad-hoc, on demand / etc.
Pre-Processing:
  • Describes any processes that will be carried out before the mappings are started. Usually, this will consist of file handling rules and pre-requisite events.
Staging:
  • Describes and names mappings that will be deployed to Staging (sta). Usually, these will move data as-is into the sta tables and, for some feeds, deltas are generated.
Validation:
  • Describes and names mappings that will be deployed to Validation (val). Usually, these will be concerned with data validation.
ODS:
  • Describes and names mappings that will be deployed to the ODS (ods).
Warehouse:
  • Describes and names mappings that will be deployed to the Warehouse (whs). This is the final load into the data warehouse proper.
Post-processing:
  • This describes any processes that will be carried out after the load has completed. This includes notification and error reporting.
Process - Process B:

etc...

Next -

posted by Cat Play Crew at 3:06 pm | 0 comments

Time Dimension - Sources

None. This dimension is generated internally. A process is built that generates rows for this dimension for a period defined by the designer.

posted by Cat Play Crew at 3:01 pm | 0 comments

Dimension Documentation - Sources

This section is used to define that data sources for the dimension. The first part of this section is a source object definition (usually a table, view of file). This is followed by the transformation rules.

This post consists of a pick-list of parts. Rarely would a dimension require all of these.

Next - Process

posted by Cat Play Crew at 2:53 pm | 0 comments

Create a Dimension

Introduction:

This procedure describes how a dimension definition is entered into OWB. Before this can be entered, it must be designed. Use this template to create the design, here is an example.

Aim:

Create a Dimension definition in OWB.

Requirements:

Access to an OWB Target Module.

Procedure:

  • Log on to OWB.
  • Open a project.
  • Expand 'Databases'
  • Expand 'Oracle'
  • Expand the target module.
  • Right-click 'Dimensions'
  • -Create Dimension-
    • Welcome:
      • [Next]
    • Name:
      • Name: Paste this from the Definition section of the design.
      • Prefix: This is buggy - leave blank.
      • Description: Paste this from the Definition section of the design.
    • Levels:
      • For each Level described in the Definition section of the design:
        • Name: Paste this from the Definition section of the design.
        • Prefix: Paste this from the Definition section of the design.
        • Description: Paste this from the Definition section of the design.
        • [Add]
      • [Next]
    • Level Attributes:
      • By default, each Level has the attribute; ID. This cannot - and should not - be removed.
      • For each Level described in the Definition section of the design:
        • Level: Use drop-down to select the Level to work with.
        • For each Level Attribute described in the Definition section of the design:
          • Name: Paste this from the Definition section of the design.
          • Datatype: Enter the Datatype, Length, Precision & Scale as appropriate from the Definition.
          • Description: Paste this from the Definition section of the design.
          • [Add]
      • [Next]
    • Hierarchies:
      • For each Hierarchy described in the Definition section of the design:
        • Name: Paste this from the Definition section of the design.
        • Prefix: Leave blank. OWB does nothing with this.
        • Description: Paste this from the Definition section of the design.
        • [Add]
      • [Next]
    • Level Relationships:
      • For each Hierarchy described in the Definition section of the design:
        • Hierarchy: Use drop-down to select the Hierarchy to work with.
        • Select and transfer Levels from the left side to right side as per the Definition.
      • [OK]
Testing:
  • Right-click the newly created dimension.
  • -Validate...-
  • This should result in the message; 'Validation completed successfully.'

posted by Cat Play Crew at 8:12 am | 0 comments

Create an Oracle Module

Introduction:

An Oracle Module contains all the objects associated with a specific schema on a particular instance. The objects that are created here are deployed as tables, views, sequences, package procedures, etc.

Aim:

Create an Oracle Module and associate it with it's physical schema.

Requirements:

Access to an OWB Project.

Procedure:

  • Log in to OWB.
  • On the main screen, open a project: Expand Project Name.
  • -Databases-
  • -Oracle-
  • Right-click Oracle
  • -Create Oracle Module-
    • Welcome:
      • [Next]
    • Name: Usually the name of the schema.
      • Module status:
        • This COULD be used as developer information. However, it is no more than a metadata label and doesn't form part of any greater configuration management infrastructure. I usually leave this as-is regardless of its actual status.
      • Identify the module type:
        • The primary difference between 'Data Source' and 'Warehouse Target' is that you can deploy object to the latter but not the former. This sounds like a simple choice; if it is a read-only external system, use 'Data Source', right? Well not always. You may, for example, wish to deploy a view or procedure used in a trigger to an Oracle source - handy if the source can't readily identify change records - though you will need a VERY understanding source system DBA. It is probably best to define all Oracle modules as targets at this stage. After all, you don't HAVE to deploy to a target but you can give yourself the option of doing so one day if necessary.
      • Description:
        • This is used to let the users know where their data is coming from / going to and should be geared to that.
      • [Next]
    • Connection Information:
      • This screen describes where you get the descriptions of objects (tables, etc) that will be used in the data warehouse. If you use Designer, you can define a connection to its repository. If you are creating objects directly in a schema and want to pull their definitions into OBW from there, you can define a link to this schema. Regardless of whether I am using Designer or not, I prefer to always use the 'Oracle Data Dictionary' option. Anything that I develop in Designer is deployed to the schema and imported from there into OWB. That gives me the choice of not using Designer for some objects, if I want. Of course, if you are designing and delopying different objects in different ways, you need a very good Configuration Management infrastructure to handle it all. What do you mean - you don't have one of those!
      • Oracle Data Dictionary
      • Datadase Link: If you already have a link defined for this target, choose that, otherwise: -New DB Link-
        • New Database Link:
          • DB Link Name: I usually use something like; [schema name]_SRC.[SID] or [schema name]_TGT.[SID].
          • NOTE: If you don't append '.[SID]', or at least '.[something]', Oracle will append the Global Database Name to the DB Link an this component can be very long. This can cause problems when using debug. Why? Because the links are deployed surrounded by double-quotes - e.g. "link name" - and, if the length of this is greater than 30 characters, it throws and error. It's best to have short-name DB Links.
          • Host Name:
            • Host Name: lisa
            • Port Number: 1521
            • Oracle Service Name: The instance name
          • User Name: The schema name
          • Password: *********
          • [Create and Test]
            • Testing... successful
          • [OK]
      • [Next]
    • Location:
      • This screen describes the location where the objects will be deployed to. Often, this points to the same location as the Connection Information location, i.e. the target contains objects sourced from Designer, other places and this module, and object definitions can be passed freely between them.
      • Deployment location: If you want to use one that's already defined, pick this from the drop-down, otherwise: [New...]
        • New Location Dialog:
          • Name: I usually use [schema name]_TGT
          • Description: This is a developer's message.
          • Version: The database version.
          • [OK]
      • [Next]
    • [Finish]

posted by Cat Play Crew at 7:15 am | 0 comments

Friday, January 27, 2006

Create a Project

Introduction:

The OWB Project is the least granular OBW component and houses all of the object that make up a complete data warehouse.

Aim:

Create a project.

Requirements:

Access to OWB.

Procedure:

  • Log on to OWB.
  • -Project-
  • -Create Project-
  • Welcome:
    • [Next]
  • Name:
    • Type in a name...: Think hard about this. All your developers will be accessing it. Something geeky is good.
    • Type in an optional description: Users will be able to read this through a metadata browser so this should be meaningful to them.
    • [Next]
  • Version Properties:
    • Type in an optional version label: The Version Control facilities of OWB are not very good. You have to jump through hoops to maintain objects at different versions and have developers working at different stages of a project. I just leave this blank.
    • [Next]
  • Finish:
    • [Finish]
  • A new project appears on the project tree in OWB's main screen.

posted by Cat Play Crew at 8:51 pm | 0 comments

OWB How-To

This post links to other posts that are related to doing things in OWB:

posted by Cat Play Crew at 8:47 pm | 0 comments

Time Dimension - Definition

Table:

Table
Description
D_TIME
The Time Dimension (D_TIME) contains several navigable hierarchies relating to time - these represent different ways of viewing the calendar. The two major groups are Natural (i.e. the normal yearly calendar) and Financial (a calendar representing the Australian business year).

Levels:
Level
Prefix
Description
NAT_YEA
NYR
Natural Year. This describes the Natural Calendar Year.
NAT_QTR
NQT
Natural Quarter. This describes the Natural Calendar Quarter.
NAT_MTH
NMN
Natural Month. This describes the Natural Calendar Month.
NAT_WEK
NWK
Natural Week. This describes the Natural Calendar Week.
FIN_YEA
FYR
Financial Year. This describes the Financial Year.
FIN_QTR
FQT
Natural Quarter. This describes the Natural Calendar Quarter.
FIN_MTH
FMN
Financial Month. This describes the Financial Month.
FIN_WEK
FWK
Financial Week. This describes the Financial Week.
DAY
DAY
Day. This describes the Calendar Day. The Day description is common to both the Natural and Financial calendars.

Level Attributes:

NAT_YEA Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
YEAR
NUMBER(4)
The year as a number type. e.g. 2005.
NAME
VARCHAR2(4)
The year as a character type. e.g. '2005'.
DAYS_IN_YEAR
NUMBER(3)
The number of days in the year. e.g. 365, 366.

NAT_QTR Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
QUARTER
NUMBER(1)
The quarter as a number type. e.g. 1, 2, 3, 4.
NAME
VARCHAR2(11)
The full quarter name as a character type. e.g. '1st Quarter'.
SHORT_NAME
VARCHAR2(7)
The short quarter name as a character type. e.g. '1st Qtr'.
ORDERING
NUMBER(5)
A number that allows ordering to span years. e.g. 20054, 20061.
DAYS_IN_QUARTERNUMBER(2)The number of days in the quarter. e.g. 92.

NAT_MTH Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
MONTH
NUMBER(2)
The month as a number type. e.g. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12.
NAME
VARCHAR2(10)
The full month name as a character type. e.g. 'January', 'February', etc.
SHORT_NAME
VARCHAR2(3)
The short month name as a character type. e.g. 'Jan', 'Feb', etc.
ORDERING
NUMBER(6)
A number that allows ordering to span years. e.g. 200512, 200601.
DAYS_IN_MONTH
NUMBER(2)
The number of days in the month. e.g. 31.

NAT_WEK Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
WEEK
NUMBER(2)
The week as a number type. e.g. 1, 2, 3, etc. Dates are assigned to weeks as follows:

- 1st Jan is assigned to Week 1.
- Each Monday, the week is incremented.
NAME
VARCHAR2(3)
The week name as a character type. e.g. '1st', '2nd', etc.
ORDERINGNUMBER(6)
A number that allows ordering to span years. e.g. 200552, 200601.

FIN_YEA Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
YEAR
NUMBER(4)
The financial year as a number type. e.g. 2006. As the Financial Year spans 2 natural years, this is the number of the later year. e.g. for the financial year 2005/2006, this value is 2006.
NAME
VARCHAR2(9)
The financial year as a character type. e.g. '2005/2006'.
SHORT_NAME
VARCHAR2(20)
The financial year as a character type. e.g. '2005/2006 Fin Yr'.
FULL_NAME
VARCHAR2(25)
The financial year as a character type. e.g. '2005/2006 Financial Year'.
DAYS_IN_YEAR
NUMBER(3)
The number of days in the financial year. e.g. 365, 366.

FIN_QTR Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
QUARTER
NUMBER(1)
The financial quarter as a number type. e.g. 1, 2, 3, 4.
NAME
VARCHAR2(3)
The financial quarter name as a character type. e.g. '1st'.
SHORT_NAME
VARCHAR2(11)
The short financial quarter name as a character type. e.g. '1st Fin Qtr'.
FULL_NAMEVARCHAR2(25)
The financial quarter name as a character type. e.g. '1st Financial Quarter'.
ORDERING
NUMBER(5)
A number that allows ordering to span years. e.g. 20054, 20061.

FIN_MTH Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
MONTH
NUMBER(2)
The financial month as a number type. e.g. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12.
NAME
VARCHAR2(10)
The financial month name as a character type. e.g. 'January', 'February', etc.
SHORT_NAME
VARCHAR2(9)
The short financial month name as a character type. e.g. '1st Fin Mth', 2nd Fin Mth', etc.
FULL_NAME
VARCHAR2(25)
The long month name as a character type. e.g. '1st Financial Month', etc.
ORDERING
NUMBER(6)
A number that allows ordering to span years. e.g. 200512, 200601.

FIN_WEK Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
WEEK
NUMBER(2)
The week as a number type. e.g. 1, 2, 3, etc. Dates are assigned to weeks as follows:

- 1st Jul is assigned to Week 1.
- Each Monday, the week is incremented.
NAME
VARCHAR2(3)
The week name as a character type. e.g. '1st', '2nd', etc.
SHORT_NAME
VARCHAR2(10)
The week name as a character type. e.g. '1st Fin Wk', '2nd Fin Wk', etc.
FULL_NAME
VARCHAR2(25)
The week name as a character type. e.g. '1st Financial Week', etc.
ORDERING
NUMBER(6)
A number that allows ordering to span years. e.g. 200552, 200601.

DAY Level:

Attribute
Datatype
Description
ID
NUMBER
OWB assigned.
START_DATE
DATE
Timestamp of the start of the day. e.g. '01-JAN-2006 00:00:00'.
END_DATE
DATE
Timestamp of the end of the day. e.g. '01-JAN-2006 23:59:59'.
JULIAN_DATE
NUMBER(7)
Days since 1st January 4712 BC.
NAME
VARCHAR2(10)
The name of the day. e.g. 'Monday', 'Tuesday', etc.
SHORT_NAME
VARCHAR2(3)
The short name of the day. e.g. 'Mon', 'Tue', etc.
WEEKDAY
VARCHAR2(1)
Y/N Indicator.
LAST_DAY_IN_QUARTERVARCHAR2(1)
Y/N indicator.
LAST_DAY_IN_MONTHVARCHAR2(1)
Y/N indicator.

OTHER - These are audit and control attributes. While not strictly belonging to a Level, they are assigned to the most granular so that OWB can maintain them - in this case; DAY:

Attribute
Datatype
Description
RUN_NO
NUMBER
The Run Number that created this row.
INS_TSP
DATE
Insert timestamp.

Hierarchies:

Hierarchy
Description
NAT_CAL
A hierarchy that allows navigation from Natural Calendar Year to Day.
NAT_WEK
A hierarchy that allows navigation from Natural Calendar Week to Day.
FIN_CAL
A hierarchy that allows navigation from Financial Calendar Year to Day.
FIN_WEK
A hierarchy that allows navigation from Financial Calendar Week to Day.

Level Relationships:

Hierarchy
Level
NAT_CALNAT_YEA
NAT_QTR
NAT_MTH
DAY
NAT_WEKNAT_WEK
DAY
FIN_CALFIN_YEA
FIN_QTR
FIN_MTH
DAY
FIN_WEKFIN_WEK
DAY

posted by Cat Play Crew at 8:38 pm | 0 comments

Thursday, January 26, 2006

Time Dimension - Granularity

Level
Relative Granularity
Natural Year1
Natural Quarter4
Natural Month12
Natural Week52
Financial Year1
Financial Quarter4
Financial Month12
Financial Week52
Day365

posted by Cat Play Crew at 8:13 pm | 0 comments

Dimension Documentation - Granularity

This section outlines the total volume of rows for the dimension and the relative granularity of each level.

Volume:

A section that includes the assumptions that the volume calculation is based on, followed by the calculation itself.

Relative Granularity:

Level
Relative Granularity
Name of the level.
A number. The granularity relative to the lowest granularity in the dimension. For example; the lowest granularity in a time dimension is the Year. Granularities relative to this are; Quarter = 4, Month = 12, Week = 52, and Day = 356.


Next - Definition

posted by Cat Play Crew at 7:39 pm | 0 comments

Dimension Documentation - Definition

This section contains the dimension definition and should contain the following:

Table:

Table
Description
Name of the database table that implements the dimension.
A description of the dimension. This should be a plain-English description and, as it will end up in the user-accessible data dictionary, should be aimed at the user's understanding of what they are dealing with.

Levels:

Level
Prefix
Description
Name of the level.
Column prefix of the level. This is appended to the attribute name to create the database column name. A 3-character prefix is common.
A description of the level. This should be a plain-English description and, as it will end up in the user-accessible data dictionary, should be aimed at the user's understanding of what they are dealing with.

Level Attributes:

For each level:

Attribute
Datatype
Description
Name of the Attribute. This must be unique within the level.
Oracle Datatype. e.g. DATE, VARCHAR2(10), NUMBER(7,2), etc.
A description of the attribute. This should be a plain-English description and, as it will end up in the user-accessible data dictionary, should be aimed at the user's understanding of what they are dealing with.

Hierarchies:

Hierarchy
Description
Name of the hierarchy.
A description of the hierarchy. This should be a plain-English description and, as it will end up in the user-accessible data dictionary, should be aimed at the user's understanding of what they are dealing with.

Level Relationships:

Hierarchy
Level
Name of the hierarchy.
A list of levels that are assigned to the hierarchy. These are ordered from the least to most granular.

Next - Sources

posted by Cat Play Crew at 3:52 pm | 0 comments

Time Dimension - Roadmap



posted by Cat Play Crew at 3:20 pm | 0 comments

Dimension Documentation - Roadmap

This section should be a diagramatic representation of the dimension - one that user can understand and use. I find this to be one of the best tools I have when interacting with users - other than the lart, of course. Often, after a long and difficult requirements gathering process, when I finally get something useful out of the users, I will go back to them with this roadmap. One of the more common responses is; 'Why are you wasting my time telling me something I already know?'. Bingo!

As an example, here is a roadmap for a time dimension:



Next - Granularity

posted by Cat Play Crew at 10:53 am | 0 comments

Time Dimension - Functional Specification

This implementation of the Time Dimension is for use in the Australian business area. It contains four hierarchies:

  • Natural Calendar Full.
  • Natural Calendar Week.
  • Financial Calendar Full.
  • Financial Calendar Week.
The Australian financial year starts on 1st July and ends 30th June.

Natural Calendar Full Hierarchy:

This contains the following navigable levels:
  • Natural Year
  • Natural Quarter
  • Natural Month
  • Day
Natural Calendar Week Hierarchy:

This contains the following navigable levels:
  • Natural Week
  • Day
Financial Calendar Full Hierarchy:

This contains the following navigable levels:
  • Financial Year
  • Financial Quarter
  • Financial Month
  • Day
Financial Calendar Week Hierarchy:

This contains the following navigable levels:
  • Financial Week
  • Day
Sources:

As it is possible to predict the contents of this dimension far in advance, it is automatically generated and has no external sources.

posted by Cat Play Crew at 10:06 am | 0 comments

The Time Dimension

Links to posts for this sub-system:

posted by Cat Play Crew at 9:59 am | 0 comments