Tuesday, January 20, 2009

Fragmentation in OBIEE

We use fragmentation when we have fact or dimensional data in one or more different tables or data is splitted in different data sources. Then each logical table source represents one data segment.

For example, clients, in the first table are clients from A to M, in the second from M to Z.

Fragmentation of dimension

If our dimensional data is located on two or more physical tables in database (tables) or it is on separeted data sources then we can handle this by using UNION ALL views in physical layer.

However for testing this option we'll simulate this by using CHANNELS table. So we splitt it to a table CHANNELS_OTHER and CHANNELS. Fragmentation attrubur is CHANNEL_ID.



In our SALES fact table that we used here we use CHANNEL_ID from both tables CHANNELS and CHANNELS_OTHER.

Physical model, join:



SALES.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID

BMM:



Settings:





Test in Answers:



Result:



NQQuery.log:



We see that UNION ALL is generated.

If we choose:



NQQuery.log:



We see that the SQL is using only the second fragmented logical table source and the condition is applied only for that logical table source.

If we choose:



NQQuery.log:



If we now choose any other attribut (CHANNEL_CLASS) that is not CHANELL_ID which we used as a fragmentation key:



Then this condition is applied on both logical table sources CHANNEL and CHANNEL_OTHER:



Fragmentation of fact table

Let's split data for SALES 1998 in separate table SALES_HIST. Data from 1998 we leave in SALES.



Physical model, join:



SALES.TIME_ID >- TIMES.TIME_ID
SALES_HIST.TIME_ID >- TIMES.TIME_ID

BMM:



Settings (for a fragmentation key we choose CALENDAR_YEAR):





Test in Answers:



Result:



NQQuery.log:



If we choose:



NQQuerylog:



If we choose:



NQQuery.log:



If we now choose any other attribut (CALENDAR_MONTH_DESC) that is not CALENDAR_YEAR which we used as a fragmentation key:



Then this condition is applied on both logical table sources SALES and SALES_HIST:



In this post we showed how to combine different sources of information using fragmentation option. We see that at any moment we can see which source OBIEE takes while generating code.

6 comments:

NAG said...

I Really appriciate this blog .very valuable and once through this document almost has practical experience..

Thanks
Nag

Will said...

can we use fragmentation when we're using essbase as a data source?

Regards,
will

Joch said...

Hi, your post is really helpful. I'm trying to create a fragmentation between 2 identical cubes. It suppose be done with partitioning in the cube, but the result is very slow. So thinking maybe fragmentation could help. but still stuck with the result. It always display the 2nd cube i added. do you have any blog about this ?

Unknown said...

This blog is very useful to me.


Thanks
Bhuvana

Unknown said...

Hey

This one real cool!!
One suggestion, the ideal implementation instead of hard coding the 'Calender_Year' ,we can use the 'Repository Variables' while specifying the Fragmentation Content.We should be using the 'Global variables' (Repository variables)at the filter criteria of the report, or can drive it from the 'Prompt' selection.

Thanks


Thanks
Hari Babu Tellabati

Anonymous said...

Thanks for the Blog this is exaclty what I am looking for. However, my results are always complete union or only return results from one table. If I have the Fragmentation key as the DAY_SID it always returns a complet union but when I change it to the DAY_DT which is what the reports are prompted off it only returns results from the current table and not the history table. Do you have any suggestions?