OBIEE 11G: Fragmentation Content for String

Ray Mark

Hi,

I am looking for suggestions on how to use the fragmentation content based on this scenario: Partition was based on the last 2 digits of the column Ticket. Ticket is in string format

Partition 1: 00 - 09

Partition 2: 10 - 19

Partition 3: 20 - 29

.

.

Partition 10: 90 - 99

Examples:

01493 - Partition 10

04218 - Partition 2

12360 - Partition 7

12450 - Partition 6

03945 - Partition 5

I tried using string functions like cast and substring but it does not work. It seems that Fragmentation Content does not accept this. Simple <,>,= is working however we cannot get the last two digits of the string to be able to get the correct LTS. What happened is, the first LTS was being used all the time when using these string functions.

Thanks!

[Deleted User]

Fragmentation where you need to include convoluted logic rather than a simple comparison are always an issue.

"04" as a string and then I assume you must compared with said substring of the ticket?

What's your current, exact, fragmentation content formula?

Ray Mark

I tried these for Partition 1:

Cast(Substring("Ticket"."Ticket"."Ticket Number",4,2) AS INT ) between 0 and 9

Cast(Substring("Ticket"."Ticket"."Ticket Number" FROM 4 FOR 2) AS INT ) between 0 and 9

Cast(Substring("Ticket"."Ticket"."Ticket Number",4,2) AS INT ) >= 0 and Cast(Substring("Ticket"."Ticket"."Ticket Number",4,2) AS INT ) <= 9

Cast(Substring("Ticket"."Ticket"."Ticket Number" FROM 4 FOR 2) AS INT ) >= 0 and Cast(Substring("Ticket"."Ticket"."Ticket Number" FROM 4 FOR 2) AS INT ) <= 9

Substring("Ticket"."Ticket"."Ticket Number",4,1) = '0'

Substring("Ticket"."Ticket"."Ticket Number" FROM 4 FOR 1) = '0'

[Deleted User]

As I said it's not a perfect setup to begin with but this will get you the first "digit" of the fragmentation for your ticket number - the decade so to speak.

LEFT(RIGHT("Ticket"."Ticket"."Ticket Number", 2), 1)

So you can fragment by LEFT(RIGHT("Ticket"."Ticket"."Ticket Number", 2), 1) = '0'  etc. It will still be pure string rubbish though

Ray Mark

Still, it does not work.

[Deleted User]

Ok what does that mean "does not work".

Ray Mark

It seems that Fragmentation cannot evaluate it.

[Deleted User]

Faked in in SampleApp and it works perfectly:

Child Nodes (RqJoinSpec): <<593234>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593235>> []  (  RqList <<593238>> [for database 3023:28:01 - Sample App Data (ORCL),78]  D2.c1 as c1 GB [for database 3023:28:01 - Sample App Data (ORCL),78],  D2.c2 as c2 GB [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593244>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593245>> []  ((  RqList <<593249>> [for database 3023:28:01 - Sample App Data (ORCL),78]  D1.c1 as c1 [for database 3023:28:01 - Sample App Data (ORCL),78],  D1.c2 as c2 [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593255>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593256>> []  (  RqList <<593259>> [for database 3023:28:01 - Sample App Data (ORCL),78]  left(right(cast(F10 Billed Rev.Order_Key as VARCHAR ( 40 )) , 2) , 1) as c1 [for database 3023:28:01 - Sample App Data (ORCL),78],  0 as c2 [for database 3023:28:01 - Sample App Data (ORCL),78],  1 as c3 [for database 3023:28:01 - Sample App Data (ORCL),78],  2 as c4 [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593274>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593275>> []  SAMP_REVENUE_F AS F10 Billed Rev  ) as D1 aka SACOMMON593060  RqUnion All <<593248>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqList <<593278>> [for database 3023:28:01 - Sample App Data (ORCL),78]  D1.c1 as c1 [for database 3023:28:01 - Sample App Data (ORCL),78],  D1.c3 as c2 [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593284>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593285>> []  (  RqList <<593288>> [for database 3023:28:01 - Sample App Data (ORCL),78]  left(right(cast(F10 Billed Rev.Order_Key as VARCHAR ( 40 )) , 2) , 1) as c1 [for database 3023:28:01 - Sample App Data (ORCL),78],  0 as c2 [for database 3023:28:01 - Sample App Data (ORCL),78],  1 as c3 [for database 3023:28:01 - Sample App Data (ORCL),78],  2 as c4 [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593303>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593304>> []  SAMP_REVENUE_F AS F10 Billed Rev  ) as D1 aka SACOMMON593060  RqUnion All <<593248>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqList <<593307>> [for database 3023:28:01 - Sample App Data (ORCL),78]  D1.c1 as c1 [for database 3023:28:01 - Sample App Data (ORCL),78],  D1.c4 as c2 [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593313>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593314>> []  (  RqList <<593317>> [for database 3023:28:01 - Sample App Data (ORCL),78]  left(right(cast(F10 Billed Rev.Order_Key as VARCHAR ( 40 )) , 2) , 1) as c1 [for database 3023:28:01 - Sample App Data (ORCL),78],  0 as c2 [for database 3023:28:01 - Sample App Data (ORCL),78],  1 as c3 [for database 3023:28:01 - Sample App Data (ORCL),78],  2 as c4 [for database 3023:28:01 - Sample App Data (ORCL),78]  Child Nodes (RqJoinSpec): <<593332>> [for database 3023:28:01 - Sample App Data (ORCL),78]  RqJoinNode <<593333>> []  SAMP_REVENUE_F AS F10 Billed Rev  ) as D1 aka SACOMMON593060)  ) as D2  ) as D1OrderBy: c2 asc NULLS LAST, c3 asc NULLS LAST [for database 3023:28:01 - Sample App Data (ORCL),78]

Ray Mark

Does this mean that the filter from the Web Analytics when creating a report is not the whole value?

[Deleted User]

As you can see I just created a logical column than does the logic.

No reason for going the inefficient route and do things X times when you can just do it once on the RPD.