Error to create Logical Level Based Hierarchy

ManishPandey

Hi All,

i am creating logical level based hierarchy using Time Dimension

Time Dimension comes from following query

select to_char(bilmon,'RRRR') Year,

to_char(bilmon,'Month') Month,

to_char(bilmon,'MM') Month_in_num,

decode(to_char(bilmon,'MM'),'01','Q4','02','Q4','03','Q4','04','Q1','05','Q1','06','Q1','07','Q2','08','Q2','09','Q2','10','Q3','11','Q3','12','Q3') Qtr,

bilmon from

(

select trunc(bildt,'MM') bilmon from ad_bills

group by trunc(bildt,'MM')

)

now when i create drill down using time dimension error is coming.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 3113, message: ORA-03113: end-of-file on communication channel Process ID: 0 Session ID: 425 Serial number: 13254 at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

SQL Issued: SELECT s_0, s_1, s_2, s_3, s_4, s_5 FROM ( SELECT 0 s_0, CAST(NULL AS VARCHAR(1)) s_1, 'TimeDim Total' s_2, IDOF("AdvtBills"."TimeDim"."TimeDimDim"."TimeDim Total") s_3, CAST(NULL AS VARCHAR(1)) s_4, "AdvtBills"."Bills"."BILLAMT" s_5 FROM "AdvtBills" UNION ALL SELECT 1 s_0, "AdvtBills"."TimeDim"."YEAR" s_1, 'TimeDim Total' s_2, IDOF("AdvtBills"."TimeDim"."TimeDimDim"."TimeDim Total") s_3, IDOF("AdvtBills"."TimeDim"."TimeDimDim"."year") s_4, "AdvtBills"."Bills"."BILLAMT" s_5 FROM "AdvtBills" WHERE IDOF("AdvtBills"."TimeDim"."TimeDimDim"."TimeDim Total") IN (1) ) djm FETCH FIRST 65001 ROWS ONLY

Thomas Dodds

Best to have your time dimension structured physically and loaded in ETL ... doing calculations to get the levels will prohibit you from using the time hierarchy as a true time hierarchy with chronological keys and thus limits you from using time series functions (AGO, TODATE, PERIODROLLING).

Use of information drives your physical data model design.