Home » SQL & PL/SQL » SQL & PL/SQL » SQL query rewrite, dimensions/datawarehouse with business objects
icon5.gif  SQL query rewrite, dimensions/datawarehouse with business objects [message #186711] Wed, 09 August 2006 03:35 Go to next message
Messages: 2
Registered: August 2006
Location: uk
Junior Member

I have a problem with firstly my understanding of SQL query rewrite. I have a central fact table with all my transaction data and a time based dimension hung off it.

the time dimension table has a dimension and hierarchy associated with it day, week, month, year etc.

now i have a materialized view aggregated to month level of my main fact table. i understand that the query would rewrite should i use a week level dimension... HOWEVER

business objects writes the query

select week, sum(fact)
from fact_table, dimension
fact_table.calendar_number = dimension.calendar_number
group by fact_table.week

how does the optimizer know that i have asked for a week level which it could get from the week level m_view when the query where clause comes on lower level(although that lower level actually works out to be equivalent of a week)

Re: SQL query rewrite, dimensions/datawarehouse with business objects [message #186742 is a reply to message #186711] Wed, 09 August 2006 05:25 Go to previous message
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You might want to check out aggregate awareness in BO designer. You can have BO decide whether to use a summary table or event level table. Check out the documentation (You can get the PDF by going to help in Designer) or you could register at www.forumtopics.com/busobj

Previous Topic: Creating sequences for each table in schema???
Next Topic: Return structure from DLL to PL/SQL
Goto Forum:

Current Time: Tue Oct 25 20:01:18 CDT 2016

Total time taken to generate the page: 0.10838 seconds