• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Create New Logical Table Sources & Set Aggregate Content

1 . In the Physical layer, expand D1 Time Quarter Grain.

2

2 . In the BMM layer, expand D1 Time.

2

D1 Time Quarter GrainD1 Time
CAL_HALFCal Half
CAL_QTRCal Qtr
CAL_YEARCal Year
DAYS_IN_QTRDays in Qtr
JULIAN_QTR_NUMJulian Qtr Num
PER_NAME_HALFPer Name Half
PER_NAME_QTRPer Name Qtr
PER_NAME_YEARPer Name Year

This action creates a new logical table source named D1 Time Quarter Grain for D1 Time.

2

4 . Rename the D1 Time Quarter Grain logical table source to LTS2 Time Quarter Grain.

2

5 . Double-click LTS2 Time Quarter Grain to open the Logical Table Source dialog box.
6 . On the Column Mapping tab make sure show mapped columns is selected and note the column mappings. The logical columns now map to columns in two physical tables: D1 Time and D1 Time Quarter Grain.

2

7 . Click OK to close the Logical Table Source dialog box.
8 . In the Physical layer expand F2 Revenue Aggregate.

2

9 . In the BMM layer expand F1 Revenue.

2

10 . Drag the following physical columns from F2 Revenue Aggregate to their corresponding logical columns in F1 Revenue: Note: Do not add them as new columns.

F2 Revenue AggregateF1 Revenue
UNITSUnits
REVENUERevenue

This action creates a new logical table source named F2 Revenue Aggregate for F1 Revenue.

2

11 . Rename the F2 Revenue Aggregate logical table source to LTS2 Revenue Aggregate.

These core tutorials will help you to learn the fundamentals of OBIEE 11g. 
For an in-depth understanding and practical experience,  explore OBIEE 11g Training.

2

12 . Double-click LTS2 Revenue Aggregate to open the Logical Table Source dialog box.
13 . On the Column Mappings tab make sure show mapped columns is selected and note the column mappings. The Revenue and Units logical columns now map to columns in two physical tables:
F1 Revenue and F2 Revenue Aggregate.

2

14 . Leave the Logical Table Source – LTS2 Revenue Aggregate dialog box open.

Set Aggregate Content

1 . Click the Content tab.
2 . Set the following logical levels for the logical dimensions:

Logical DimensionLogical Level
H1 TimeQuarter
H2 ProductProduct Total
H20 ProductProduct Total
H3 CustomerCustomer Total
H5 Sales RepTotal

2

Explanation: You are setting aggregation content for the fact table to the corresponding levels in the dimension hierarchies. In a subsequent step, you set similar levels for the aggregate logical table source for the Time dimension. Note that all levels are set to the total level except for the H1 Time logical dimension, which is set to Quarter. The result is, when a user queries against a particular level, Oracle BI Server will “know” to access the aggregate tables instead of the detail tables.
For example, if a user queries for total sales by product by quarter, the server will access the F2 Revenue Aggregate fact table and the corresponding aggregate dimension table, D1 Time Quarter Grain. If a user queries for a level lower than the level specified here, for example Month instead of Quarter, then the server will access the detail tables (F1 Revenue and D1 Time). If a user queries for higher level (year instead of quarter) the aggregate tables will be used, because whenever a query is run against a logical level or above, the aggregate tables are used.

3 . Click OK to close the Logical Table Source dialog box.
4 . Double-click the LTS2 Time Quarter Grain logical table source to open the Logical Table Source dialog box.
5 . On the Content tab, set the logical level to Quarter.

2

6 . Click OK to close the Logical Table Source dialog box.
7 . Save the repository and check global consistency. Fix any errors or warnings before proceeding.
8 . Close the repository. Leave the Administration Tool open. Note that you did not need to change the Presentation layer. You made changes in the business model that impact how queries are processed and which sources are accessed. However, the user interface remains the same, so there is no need to change the Presentation layer. Oracle BI Server will automatically use the appropriate sources based on the user query.

For indepth understanding of OBIEE click on

Summary
Review Date
Reviewed Item
Create New Logical Table Sources & Set Aggregate Content

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Create New Logical Table Sources & Set Aggregate Content"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.