Addidici Usage Tracking Project – Part 01 – Oracle Analytics by Adrian Ward

Today is day one of a new project to keep me busy, The OBIEE Usage Tracking Project.

The aim of the project is to build a system to analyse usage data. This will include a database build, ODI code, RPD and Catalog metadata.

This will all be free to download.

You can join in the project if you like. drop me an email and we can discuss how to collaborate.

The first object I will build is a date table. Below is the raw code to build the table and populate it with date records.


	— — Created by Adrian Ward, Addidici – http://blog.obiee.info This is the raw, no finese, non packaged version.

	— Drop if already exists
	DROP TABLE G_DATE;

	— Create a table called G_DATE. you call the table whatever you like!
	— Note the use of Id columns. These are dates but in the number form using a mask of YYYYMMDD

	CREATE TABLE G_DATE
	(
	DATE_WID NUMBER(8) NOT NULL,
	CAL_DATE DATE NOT NULL,
	DAY_NAME CHAR(10),
	MONTH_NAME CHAR(10),
	MONTH_NUMBER NUMBER(2),
	YEAR_NUMBER NUMBER(4),
	DAY_OF_WEEK NUMBER(1),
	DAY_OF_MONTH NUMBER(2),
	DAY_OF_YEAR NUMBER(3),
	WEEK_OF_YEAR NUMBER(2),
	QUARTER_OF_YEAR NUMBER(1),
	YEAR_AGO_DATE DATE,
	MONTH_AGO_DATE DATE,
	WEEK_AGO_DATE DATE,
	DAY_AGO_DATE DATE,
	NEXT_DAY_DATE DATE,
	NEXT_WEEK_DATE DATE,
	NEXT_MONTH_DATE DATE,
	NEXT_YEAR_DATE DATE,
	CUR_MONTH_FIRST_DATE DATE,
	CUR_MONTH_LAST_DATE DATE,
	CUR_YEAR_FIRST_DATE DATE,
	YEAR_MONTH NUMBER(6),
	WEEK_WID NUMBER(6)
	);

Now we insert a row for a zero record:


	INSERT INTO G_DATE (DATE_WID, CAL_DATE, DAY_NAME, MONTH_NAME, MONTH_NUMBER, YEAR_NUMBER, DAY_OF_WEEK, DAY_OF_MONTH, DAY_OF_YEAR, WEEK_OF_YEAR, QUARTER_OF_YEAR, YEAR_MONTH, WEEK_WID ) VALUES ( 0, ’01-JAN-1900′, ‘Unknown’,‘Unknown’,0,0,0,0,0,0,0,0,0);
	commit;

Now the fun bit.. This is pretty clever SQL that will insert records:

— Fill G_DATE with calculated data
	INSERT INTO G_DATE
	SELECT
	TO_NUMBER(TO_CHAR(CURRDATE, ‘YYYYMMDD’)) AS DATE_WID,
	CURRDATE AS CAL_DATE,
	TO_CHAR(CURRDATE,‘DAY’) AS DAY_NAME,
	TO_CHAR(CURRDATE,‘MONTH’) AS MONTH_NAME,
	TO_NUMBER(TO_CHAR(CURRDATE,‘MM’)) AS MONTH_NUMBER,
	TO_NUMBER(TO_CHAR(CURRDATE,‘YYYY’)) AS YEAR_NUMBER,
	TO_NUMBER(TO_CHAR(CURRDATE,‘D’)) AS DAY_OF_WEEK,
	TO_NUMBER(TO_CHAR(CURRDATE,‘DD’)) AS DAY_OF_MONTH,
	TO_NUMBER(TO_CHAR(CURRDATE,‘DDD’)) AS DAY_OF_YEAR,
	TO_NUMBER(TO_CHAR(CURRDATE+1,‘IW’)) AS WEEK_OF_YEAR,
	TO_NUMBER((TO_CHAR(CURRDATE,‘Q’))) AS QUARTER_OF_YEAR,
	ADD_MONTHS(CURRDATE, –12) AS YEAR_AGO_DATE,
	ADD_MONTHS(CURRDATE, –1) AS MONTH_AGO_DATE,
	CURRDATE – 7 AS WEEK_AGO_DATE,
	CURRDATE – 1 AS DAY_AGO_DATE,
	CURRDATE + 1 AS NEXT_DAY_DATE,
	CURRDATE + 7 AS NEXT_WEEK_DATE,
	ADD_MONTHS(CURRDATE, 1) AS NEXT_MONTH_DATE,
	ADD_MONTHS(CURRDATE, 12) AS NEXT_YEAR_DATE,
	TRUNC (CURRDATE, ‘MM’) AS CUR_MONTH_FIRST_DATE,
	LAST_DAY(CURRDATE) AS CUR_MONTH_LAST_DATE,
	TRUNC(CURRDATE, ‘Y’) AS CUR_YEAR_FIRST_DATE,
	TO_NUMBER(TO_CHAR(CURRDATE, ‘YYYYMM’)) AS YEAR_MONTH,
	TO_NUMBER(TO_CHAR(CURRDATE, ‘YYYYWW’)) AS WEEK_WID
	FROM (
	SELECT LEVEL N, TO_DATE(’31/12/1990′,‘DD/MM/YYYY’) + NUMTODSINTERVAL(LEVEL,‘DAY’) CURRDATE
	FROM DUAL
	CONNECT BY LEVEL <= 40000)
	ORDER BY 1;
	commit;

The code is available in github:

til next time..