Transcript Aggregate Tables - dbmanagement.info

Using Aggregates
Copyright © 2009, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Describe aggregate tables and their purpose in dimensional
modeling
• Model aggregate tables
• Use the Aggregate Persistence Wizard to create aggregates
11- 2
Copyright © 2009, Oracle. All rights reserved.
Business Challenge
• Data in fact and dimension sources is stored at the lowest
level of detail.
• Data often needs to be rolled up or summarized during
analysis.
• Based on the amount of data, performing calculations at the
time of the query can be resource intensive and can delay
results to the user.
11- 3
Copyright © 2009, Oracle. All rights reserved.
Business Solution: Aggregate Tables
Aggregate tables store precomputed measures that have been
aggregated over a set of dimensional attributes.
• Popular technique for speeding up response time
Aggregate fact table;
fewer rows;
quicker to read; precalculated
Detailed fact data table;
many rows;
longer time to read and calculate
Invoice
Customer
Key
Period
Key
Product
Key
Dollars
Region
Year
Total
Dollars
122222
1001
19980102
100
100
West
1998
100000
133333
1001
19990105
200
200
West
1999
200000
144444
1002
19980505
300
100
Central
1998
50000
155555
1002
19980601
400
20
166666
1005
19980101
600
20
11- 4
Summarized
Copyright © 2009, Oracle. All rights reserved.
Oracle BI Aggregate Navigation
Enables queries to use the information stored in aggregate
tables automatically
• Oracle BI Server decides which tables provide the fastest
answers.
• Metadata must be configured for aggregate navigation.
11- 5
Copyright © 2009, Oracle. All rights reserved.
Aggregated Facts
Dimension hierarchies
Aggregated sales fact table columns store precomputed results
at a given set of levels.
11- 6
Total
Total
Total
Type
Region
Year
Subtype
Product
hierarchy
District
Customer
hierarchy
Quarter
Generic
Sales Rep
Month
Specific
Customer
Day
Copyright © 2009, Oracle. All rights reserved.
Period
hierarchy
Modeling Aggregates
Model aggregate tables in the same way as other source data.
• Physical layer:
– Create the Open Database Connectivity (ODBC) data source
(if necessary).
– Import physical sources.
– Create physical joins.
• Business Model and Mapping layer:
– Add sources to logical tables.
– Specify aggregation content.
New step
• Presentation layer:
– No changes: Aggregate navigation is independent of the
Presentation layer objects.
11- 7
Copyright © 2009, Oracle. All rights reserved.
ABC Example
• Uses prebuilt aggregate tables to improve performance
• Must have matching levels of aggregation for fact and
dimensions
Sales (fact) aggregated to Sales Rep, Product Type, and Month levels
Customer (dimension)
aggregated to
Sales Rep level
11- 8
Product (dimension)
aggregated to
Type level
Copyright © 2009, Oracle. All rights reserved.
Period (dimension)
aggregated to
Month level
Steps to Implement Aggregate Navigation
1. Import tables.
2. Create joins.
3. Create fact logical table source and mappings.
4. Specify fact aggregation content.
5. Specify content for the fact detail source.
6. Create dimension logical table source and mappings.
7. Specify dimension aggregation content.
8. Specify content for the dimension detail source.
9. Test results for levels stored in aggregates.
10. Test results for data above or below levels.
11- 9
Copyright © 2009, Oracle. All rights reserved.
1. Import Tables
Import fact and dimension aggregates.
Aggregate
fact table
11- 10
Copyright © 2009, Oracle. All rights reserved.
Aggregate
dimension tables
2. Create Joins
Use the Physical Diagram to create joins between the
aggregate fact table and the aggregate dimension tables.
11- 11
Copyright © 2009, Oracle. All rights reserved.
3. Create Fact Logical Table
Source and Mappings
Create the new aggregate logical table source in the existing
logical fact table and map the columns.
11- 12
Copyright © 2009, Oracle. All rights reserved.
4. Specify Fact Aggregation Content
Specify the aggregation content of the new fact logical table
source so that Oracle BI Server knows what level of data is
stored in the aggregate tables.
11- 13
Copyright © 2009, Oracle. All rights reserved.
5. Specify Content for the Fact Detail Source
Set the levels of the fact detail source to the lowest in the
hierarchies.
11- 14
Copyright © 2009, Oracle. All rights reserved.
6. Create Dimension Logical Table
Source and Mappings
Create the new aggregate logical table source in the existing
logical dimension tables and map the columns.
11- 15
Copyright © 2009, Oracle. All rights reserved.
7. Specify Dimension Aggregation Content
Specify the aggregation content of the new dimension table
source so that Oracle BI Server knows what level of data is
stored in the aggregate tables.
11- 16
Copyright © 2009, Oracle. All rights reserved.
8. Specify Content for the Dimension
Detail Source
Set the levels of the dimension detail source to the lowest in the
hierarchies.
11- 17
Copyright © 2009, Oracle. All rights reserved.
9. Test Results for Levels Stored in Aggregates
Run queries and inspect the query log to ensure that the
aggregate tables are accessed as expected.
11- 18
Copyright © 2009, Oracle. All rights reserved.
10. Test Results for Data Above or Below Levels
Aggregate
tables used for
requests at or
above the level
11- 19
Detail tables used
for requests
below the level
Copyright © 2009, Oracle. All rights reserved.
Aggregate Persistence Wizard
Automates the creation of physical aggregate tables and their
corresponding objects in the repository
Fact
aggregate
Use wizard to
build script to
generate
aggregate tables.
Script generates
physical tables,
repository objects,
and mappings.
Dimension
aggregates
11- 20
Copyright © 2009, Oracle. All rights reserved.
Aggregate Persistence Wizard Steps
1. Open Aggregate Persistence Wizard.
2. Specify the file name and location.
3. Select business model and measures.
4. Select dimensions and levels
5. Select connection pool, container, and name.
6. Review aggregate definition.
7. View complete aggregate script.
8. Verify script is created.
9. Create and run a batch file.
10. Verify aggregates in the Physical layer.
11. Verify aggregates in the BMM layer.
12. Verify aggregates in the database.
13. Verify results in Answers.
11- 21
Copyright © 2009, Oracle. All rights reserved.
1. Open Aggregate Persistence Wizard
Select Tools > Utilities > Aggregate Persistence Wizard and
click the Execute button.
11- 22
Copyright © 2009, Oracle. All rights reserved.
2. Specify File Name and Location
Specify a file and location where the output script should be
saved.
11- 23
Copyright © 2009, Oracle. All rights reserved.
3. Select Business Model and Measures
Select business model.
Select fact table.
Select measures.
11- 24
Copyright © 2009, Oracle. All rights reserved.
4. Select Dimensions and Levels
Select corresponding aggregate dimensions and levels.
11- 25
Copyright © 2009, Oracle. All rights reserved.
5. Select Connection Pool, Container, and Name
Select the database object.
Select the schema.
Select the connection pool.
Name the aggregate table.
11- 26
Copyright © 2009, Oracle. All rights reserved.
6. Review Aggregate Definition
View the aggregate
definition.
11- 27
Copyright © 2009, Oracle. All rights reserved.
7. View Complete Aggregate Script
Confirmation of script
creation and location
Script
11- 28
Copyright © 2009, Oracle. All rights reserved.
8. Verify that the Script Is Created
Navigate to the directory where the file was saved and verify
that the script was created as expected.
11- 29
Copyright © 2009, Oracle. All rights reserved.
9. Create and Run a Batch File
Create and run a batch file with the following format to drive the
aggregate creation process:
nqcmd
-d
-u
-p
-s
11- 30
Oracle BI Server command utility
Oracle BI Server data source name
Repository username
Repository password
Path to the create aggregate SQL script
Copyright © 2009, Oracle. All rights reserved.
10. Verify Aggregates in the Physical Layer
Verify that the aggregates are created in the Physical layer of
the repository as expected.
Fact aggregate
Dimension aggregates
11- 31
Copyright © 2009, Oracle. All rights reserved.
11. Verify Aggregates in the BMM Layer
Verify that the aggregates are created in the Business Model
and Mapping layer of the repository as expected.
Dimension aggregate
Fact aggregate
11- 32
Copyright © 2009, Oracle. All rights reserved.
12. Verify Aggregates in the Database
Verify that the aggregates are created in the database.
11- 33
Copyright © 2009, Oracle. All rights reserved.
13. Verify Results in Answers
• Activate the aggregate tables:
• Run a query in Answers:
• Check the log and verify that the aggregate tables are
accessed as expected:
11- 34
Copyright © 2009, Oracle. All rights reserved.
Troubleshooting Aggregate Navigation
If aggregate navigation is not working, the cause might be one
of the following:
• Aggregation content is not specified correctly for one or
more sources.
• Aggregate dimension sources are not physically joined to
aggregate fact table sources at the same level.
• Dimensional source does not exist at the same level as a
fact table source.
• Aggregate dimension sources do not contain a column that
maps to the primary key of the dimension hierarchy level.
• The number of elements is not specified correctly for
dimension hierarchy levels.
11- 35
Copyright © 2009, Oracle. All rights reserved.
Considerations
Using aggregates comes with a price:
• Additional time is required to build and load these tables.
• Additional storage is necessary.
Build only the aggregates you need:
• Look at query patterns and build aggregates to speed up
common queries that require summarized results.
• Ensure that enough data is combined to offset the cost of
building aggregates.
• Monitor and adjust to account for changing query patterns.
11- 36
Copyright © 2009, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe aggregate tables and their purpose in dimensional
modeling
• Model aggregate tables
• Use the Aggregate Persistence Wizard to create aggregates
11- 37
Copyright © 2009, Oracle. All rights reserved.
Practice 11-1 Overview:
Using Aggregate Tables
This practice covers the following topics:
• Importing aggregate tables
• Creating keys and joins for aggregate tables
• Creating logical table sources for aggregate tables
• Specifying aggregate content
11- 38
Copyright © 2009, Oracle. All rights reserved.
Practice 11-2 Overview:
Using the Aggregate Persistence Wizard
This practice covers using the Aggregate Persistence Wizard to
automate the creation of aggregate tables and their
corresponding objects in the repository.
11- 39
Copyright © 2009, Oracle. All rights reserved.