Categories
OBIEE 12c - Range-based LTS fragmentation not working for me
Working with OBIEE 12c.
I am attempting to implement range-based partitioning of my fact sources, but not having much luck.
I can easily implement value-based partitioning, and it works just fine. For instance, my two fact sources have these fragmentation formulas:
(1) "Mavu_Biz"."Times"."Term" in ('FL-06','SP-07','SM-07','FL-07','SP-08','SM-08','FL-08','SP-09','SM-09','FL-09')
(2) "Mavu_Biz"."Times"."Term" in ('SP-10','SM-10','FL-10','SP-11','SM-11','FL-11')
Looking at the query log, I see that the analysis hits only the appropriate table depending on the analysis filter, so the fragmentation is working for value-based conditions. So far so good.
Next, I'll try changing the fragmentation formulas to this (YYYY_MM is an integer column):
(1) "Mavu_Biz"."Times"."YYYY_MM" in (2007,2008,2009)
(2) "Mavu_Biz"."Times"."YYYY_MM" in (2010,2011)
This also works just fine. Queries for Terms in 2008 only hit the first source table, queries for Terms in 2010 hit the second source table, queries for both years hit both tables. Still all good.
Now I want to try range-based formatting:
(1) "Mavu_Biz"."Times"."YYYY_MM" > 2000 and "Mavu_Biz"."Times"."YYYY_MM" <= 2009
(2) "Mavu_Biz"."Times"."YYYY_MM" >= 2010 and "Mavu_Biz"."Times"."YYYY_MM" <= 2999
I made that change in the repository and saved. Reloaded Server Metadata in Answers. Ran a query for SP-10. The query was a UNION - the Oracle SQL hit both fact tables. Ran a query for SM-08. Again it ran a union query against both tables.
I have tried all of the following combinations of fragmentation formulas. All insist on running a union for queries that only require one of the fragments.
(1) "Mavu_Biz"."Times"."YYYY_MM" > '2000' and "Mavu_Biz"."Times"."YYYY_MM" <= '2009'
(2) "Mavu_Biz"."Times"."YYYY_MM" >= '2010' and "Mavu_Biz"."Times"."YYYY_MM" <= '2999'
(1) "Mavu_Biz"."Times"."YYYY_MM" > 2000 and "Mavu_Biz"."Times"."YYYY_MM" < 2010
(2) "Mavu_Biz"."Times"."YYYY_MM" >= 2010 and "Mavu_Biz"."Times"."YYYY_MM" < 2999
(1) "Mavu_Biz"."Times"."YYYY_MM" < 2010
(2) "Mavu_Biz"."Times"."YYYY_MM" >= 2010
(1) "Mavu_Biz"."Times"."YYYY_MM" >= 2000 AND "Mavu_Biz"."Times"."YYYY_MM" < 2010
(2) "Mavu_Biz"."Times"."YYYY_MM" < 2000 OR "Mavu_Biz"."Times"."YYYY_MM" >= 2010
(1) "Mavu_Biz"."Times"."YYYY_MM" >= 2000 AND "Mavu_Biz"."Times"."YYYY_MM" < 2010
(2) "Mavu_Biz"."Times"."YYYY_MM" >= 2010 OR "Mavu_Biz"."Times"."YYYY_MM" < 2000
What can I be missing?
Answers
-
Problem solved. I finally understand what one of the critical pieces of documentation means. I wasn't understanding the meaning behind a "parallel content predicate with the use of the OR operator". Now I get it.
It simply means this: If the fragment description in the logical table source refers only to "Times"."YYYY_MM", and I create an analysis that is filtered on "Times"."Year", the fragmentation doesn't occur. The analysis filter must refer to a component of the fragmentation formula. If my fragmentation equation says something like "Times"."YYYY_MM" < 2010 OR "Times"."Month End Date" < "01-JAN-2010", and if I then create an analysis that is filtered by either YYYY_MM or Month End Date, the fragmentation works. It's not enough to create the analysis filter on a related column. It has to be filtered on one of the columns explicitly mentioned in the fragmentation formula.
Unfortunately, I would like to filter on a character field (academic terms are character strings like FL-09, SP-10, SM-10), so those can't be range based. Although my immediate problem isn't fully solved, at least I know why it wasn't working last night, and that it does work when configured properly.
(For those who are wondering why we don't just partition the tables in the database: This is for an OBIEE training class. Since our data sources might not always be relational databases with cool partitioning abilities, we will teach the partitioning concept as it exists in OBIEE, with the recommendation to partition in the database if possible.)
0