Manually Implementing the TIME SERIES WIZARD in Oracle BI EE

Manually Implementing the TIME SERIES WIZARD in Oracle BI EE
Fix for AGO() &TODATE() issue till version OBI 10.1.3.3.2

BACKGROUND

When the new Time Series functions (in Oracle BI 10.1.3.3.2) called "Ago" or “To Date” are used, it does not seem to produce correct results. The results get grouped by additional columns which are normally selected as chronological key in Time dimension.

This is a reported bug (Bug # 6032545) on the oracle support site.

RESOLUTION

In Siebel Analytics these calculations were possible by executing the Time Series Wizard utility. However as Oracle BI version has discontinued this functionality one has to manually implement the steps to create the AGO and TO DATE measures.

STEPS TO MANUALLY IMPLEMENT TIME SERIES WIZARD IN ORACLE BI


PREREQUISITE

CALENDAR Dimension Table must have YAGO (Year Ago) column


PHYSICAL LAYER CHANGES

1. CREATE ALIAS (ONE MORE COPY) OF THE FACT TABLE AS FACT_NAME_YAGO

2. CREATE PHYSICAL FOREIGN KEY JOIN WITH ALL OF THE OTHER DIMENSIONS (EXCEPT CALENDAR).
NOTE: THESE SHOULD BE FOREIGN KEY JOINS.

3. CREATE PHYSICAL JOIN (NOT FOREIGN KEY JOIN) WITH CALENDAR. JOIN CONDITION SHOULD BE ALIAS FACT.TIME_ID = CALENDAR.YAGO_TIME_ID

LOGICAL LAYER CHANGES

1. DRAG ALIAS FACT TABLE AS ADDITIONAL SOURCE TO THE LOGICAL FACT TABLE.

2. DUPLICATE THE LOGICAL COLULMNS OF MEASURES TO BE USED FOR TIME SERIES ANALYSIS AND ARE BASED ON PHYSICAL TABLE MAPPING. MAP THEM TO THE YAGO PHYSICAL TABLE AND KEEP SAME FORMULA.

3. FOR COLUMNS BASED ON OTHER LOGICAL COLUMNS, NO NEED TO DUPLICATE OR DO ANY CHANGE.

4. IF YOU NEED VARIANCE AND % VARIANCE COLUMNS, CREATE THEM MANUALLY AND USE LOGICAL TABLE AS SOURCE AND PUT IN FORMULA AS -

CALCULATING VARIANCE

IFNULL("Client Analytics"."Key Measures"."Total Pre Tax Margin", 0) - IFNULL("Client Analytics"."Key Measures"."Total Pre Tax Margin YAGO", 0)

CALCULATING % VARIANCE

CASE WHEN ("RMS Line Bank"."Key Measures"."Prior Year Sales Calls" IS NULL OR "RMS Line Bank"."Key Measures"."Prior Year Sales Calls" = 0) AND NOT "RMS Line Bank"."Key Measures"."Sales Calls" IS NULL AND "RMS Line Bank"."Key Measures"."Sales Calls" <> 0 THEN 100.0
WHEN ("RMS Line Bank"."Key Measures"."Prior Year Sales Calls" IS NULL OR "RMS Line Bank"."Key Measures"."Prior Year Sales Calls" = 0) AND ("RMS Line Bank"."Key Measures"."Sales Calls" IS NULL OR "RMS Line Bank"."Key Measures"."Sales Calls" = 0) THEN 0.0
WHEN "RMS Line Bank"."Key Measures"."Sales Calls" IS NULL AND NOT "RMS Line Bank"."Key Measures"."Prior Year Sales Calls" IS NULL AND "RMS Line Bank"."Key Measures"."Prior Year Sales Calls" <> 0 THEN -(100.0)
ELSE 100.0 * ("RMS Line Bank"."Key Measures"."Sales Calls" - "RMS Line Bank"."Key Measures"."Prior Year Sales Calls") / "RMS Line Bank"."Key Measures"."Prior Year Sales Calls" END


PRESENTATION LAYER CHANGES

PULL THOSE NEWLY CREATED LOGICAL COLUMNS IN PRESENTATION LAYER.

Popular posts from this blog

Image
If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below. If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below. Open Dev_Machine.rpd Administrator Administrator SetProperty "Connecti...
Read more

How To Unlock Bootloaders on your Xperia Arc S

UPDATE : Before you proceed, please visit  this link  to check if your phone’s boot loader can be unlocked or not? NOTE:  If the light above the USB port on your phone doesn’t turn blue when you connect your phone using the USB cable to your PC while holding the “MENU” button (on Xperia Arc. For Xperia Play – “SEARCH” button), then it means that you cannot connect to fastboot, and hence you cannot unlock the boot loader. Here’s a Step by Step guide (w/ video) on how to unlock bootloaders on your Xperia Arc, Play, Neo & Pro. [info]Although this guide is made using Xperia arc, I can assure that it would work perfectly with Xperia Play, Neo & Pro too. NOTE : Xperia Play users should press the “ Search ” button instead of “ Menu ” button in Step 8th of this tutorial.[/info] [warning]The methods and procedures discussed here are considered risky and you should not attempt if you don’t know completely what it is. If any damage occurs to you or your device, w...
Read more

Inherited Colour Vision Deficiency

Image
Red/green colour blindness is a common hereditary condition which means it is usually passed down from your parents. Colour blindness is usually passed from mother to son on the 23rd chromosome, which is the sex chromosome. Chromosomes are structures which contain genes – these contain the instructions for the development of cells, tissues and organs. If you are colour blind it means the instructions for the development of your cone cells are wrong and the cone cells might be missing, or less sensitive to light or it may be that the pathway from your cone cells to your brain has not developed correctly. The X chromosome is the sex chromosome: males have an X chromosome and a Y chromosome and females have two X chromosomes. For a male to be colour blind the faulty colour blindness gene only has to appear on his X chromosome. For a female to be colour blind it must be present on both of her X chromosomes. This is why red/green colour blindness is far more common in men than women. Bl...
Read more