10br_usingpartitionsfragments.pdf

Copyright 2008, Oracle. All rights reserved.

Using Partitions and Fragments

Copyright 2008, Oracle. All rights reserved.10 - 2

Objectives

After completing this lesson, you should be able to:

Identify reasons for segmenting data

Describe techniques to model partitions

Implement a value-based partition

Implement a fact-based partition


Business Challenge

Data is often partitioned into multiple physical sources for a single logical table.

Organizations need to seamlessly and efficiently access and process data from multiple sources to satisfy user requests.

Business applications must know where to go for what type of data and under what conditions.


Business Solution: Oracle BI Server

Oracle BI repository can be configured so that Oracle BI Server handles the navigation to the appropriate source.

Oracle BI Server seamlessly and efficiently accesses and processes data from multiple sources to satisfy user

requests.


Partition

Is a database element that contains part of the data for a fact or a dimension

Combines with other data fragments as necessary

May be:

Fact-based

Value-based

Level-based

Complex


Partitioning by Fact

Data is partitioned by fact when different fact data is stored in different tables.

Example: Actual sales versus quota targets

1000

Product

Sale

1100

Sales

Rep

2000

Product

Quota

1100

Sales

Rep

Actual sales Quota targets


Partitioning by Value

Data is partitioned by value when the data is split into separate tables according to the values of the data.

Example: Invoice data is stored separately for each region.

1000

Dollars

Central1135293

RegionInvNbrInvoices for

Central

Region

Invoices for

West

Region 200

Dollars

West114444

RegionInvNbr


Partitioning by Level

Data is partitioned by level when the same facts are stored in separate tables at different levels of aggregation.

Example: Detailed sales data is summarized and stored by year and region.

19981010

19981001

19980105

Date

100001100

250001100

10000

Product

Sale

1100

Sales Rep

Sales detailed data Sales by year and region

Central3000001999

200000

Total

Dollars

Central1998

RegionYear


Complex Partitioning

Data is partitioned using more than one technique.

Example: Invoice sales data is partitioned by value and level.

Central25000199802

10000

Total

Dollars

Central199801

RegionMonth

Invoices by month for Central Invoices by year for Central

200000

Total

Dollars

Central1998

RegionYear

Invoices by month for West Invoices by year for West

West350000199802

300000

Total

Dollars

West199801

RegionMonth

3000000

Total

Dollars

West1998

RegionYear


ABC Example: Value-Based (Customer)

Replace the current, single source for customer data with two

value-based partitions.

Clifton Lunch

Name

1000

NewKeyCustomers

with names

starting with

letters AM

Customers

with names

starting with

letters NZ Tongs Wok

Name

1002

NewKey


ABC Example: Fact-Based (Quota)

Allow users to query for actual sales data and quota data in a

single query.

1000

Product

Sale

1100

Sales

Rep

2000

Product

Quota

1100

Sales

Rep

Actual sales Quota targets


ABC Example: Value-Based (Inventory)

Build a business model for inventory data that is fragmented

into multiple tables.

Eight quarters of

inventory data

stored in eight

separate tables


Implementation Steps

Import physical sources.

Create physical joins.

Add sources to the Business Model and Mapping layer.

Specify fragmentation content.

Test the results.

New step


Specify Fragmentation Content

Use the Expression Builder to define the type of content that the fragment contains.

Set the flag to specify whether to combine this fragment with other data.

Specifies that customer

data from AM is contained in this fragment

Specifies combining this

fragment with the other


Summary

In this lesson, you should have learned how to:

Identify reasons for segmenting data

Describe techniques to model partitions

Implement a value-based partition

Implement a fact-based partition


Practice 10-1 Overview:

Modeling a Value-Based Partition

This practice covers the following topics:

Creating a partition

Defining fragmentation content for a value-based partition



Modeling a Fact-Based Partition

This practice covers modeling a fact-based partition for quota

data.



Using the Calculation Wizard to

Create Derived Measures

This practice covers creating measures for the quota partition.



Modeling Fragmented Inventory Data

This practice covers defining fragmentation content for

inventory data.

10br_usingpartitionsfragments.pdf

Documents