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.
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:
Physical model, join:
SALES.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID
BMM:
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID
BMM:
Settings:
Result:
NQQuery.log:
We see that UNION
ALL is generated.
If we choose:
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.
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:
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.
http://108obiee.blogspot.com/2009/01/fragmentation-in-obiee.html
ReplyDeletereferred same