AGGREGATE TABLES
1. What is granularity?
Granularity is nothing but a least level of information we can get from a table.
For example let us consider time dimension.
Time Dimension Table: Year, Quarter, Month, Day
From this time dimension the least level of information what we can get is based on Day.
Fact Table
In fact table the lowest levels of all the dimensions tables together is called as granularity.
2. What is content logical level ?
To drive SQL Query correctly by using source in BMM layer we need to set the content logical level. To use a source correctly, the Oracle BI Server has to know what each source contains in terms of the business model. Therefore, you need to define aggregation content for each logical table source of a fact table. The aggregation content rule defines at what level of granularity the data is stored in this fact table. For each dimension that relates to this fact logical table, define the level of granularity, making sure that every related dimension is defined.
3. How OBI server give the priority between the aggregate tables and detail tables?
At query time, the Oracle BI Server first determines which sources have enough detail to answer the query. Out of these sources, the Oracle BI Server chooses the most aggregated source to answer the query, because it is assumed to be the fastest.
The most aggregated source is the one with the lowest multiplied number of elements(rows and columns).
4. What is the priority group ?
Priority Group is useful to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.Priority ‘0’ is the highest-priority so on upto we can set ‘100’.
5. If Two fact tables, due to some reason has same granularity then how to decide the priority?
Using priority group we can decide i.e., which one fact table we need to execute then set priority group to that fact table LTS.
6. What is aggregate table? What is the use aggregate table?
Pre calculated tables known as aggregate tables. which are useful to improve the performance of queries.
7. How u can justify if I use aggregate tables, performance is going to improve?
Aggregate tables store pre calculated measures that have been aggregate over a set of dimensional attributes. This is very useful technique for speeding up query response time in decision support systems. This eliminates the need of run time calculations and delivers faster results to users. The calculations are done ahead of time and the results are stored in the tables.
The key point is that the aggregate table should have fewer rows than the non aggregate table and therefore processing should be quicker.
8. As we have many performance using techniques like aggregate tables, partitions, hints etc. How u will decide aggregate tables give more performance?
The key point is that the aggregate table should have fewer rows and therefore processing should be quicker.
9. If there is no hierarchy can we configure aggregate tables, if yes how?
Process of Aggregate Tables:
Step1: Importing tables
1. Right click on supplier cp Connection pool → Click on import metadata
2. Click on next → Expand supplier2 → select d1_order_agg1, d1_salesrep,
months → Click on import (>) → Click on finish.
Step2: providing physical joins
1. Select D1_ORDER_AGG1, MONTHS, D1_SALESREPS and
D1_PRODUCT_TYPE tables→ Click on physical diagram → Click on new join
→ drag and drop from D1_ORDER_AGG1 to D1_PRODUCT_TYPE → select
type code & type key → click on ok.
Similarly provide below joins.
2. "ORCL".""."SUPPLIER2"."D1_SALESREPS"."SALESREP" = "ORCL".""."SUPPLIER2"."D1_ORDER_AGG1"."REPKEY"
3. "ORCL".""."SUPPLIER2"."MONTHS"."MONTHCODE" =
"ORCL".""."SUPPLIER2"."D1_ORDER_AGG1"."PERKEY"
4. Close physical diagram.
Step3: Map Physical Columns to Logical Columns
Drag and drop physical columns to logical column.
D1_SALESREPS.DISTRICT →CUSTOMERS.DISTRICT
D1_SALESREPS.REGION →CUSTOMERS.REGION
D1_SALESREPS.SALESREP →CUSTOMERS.SALESREP
MONTH.MONTHINYEAR →PERIODS.MONTHINYEAR
MONTH.MONTHCODE →PERIODS.MONTHCODE
MONTH.MONTHNAME →PERIODS.NAME
MONTH.QUARTER →PERIODS.QUARTER
MONTH.YEAR →PERIODS.YEAR
D1_ORDER_AGG1.ACTLEXTND →SALESFACTS.DOLLARS
D1_ORDER_AGG1.UNITORD →SALESFACTS.UNITORD
D1_ORDER_AGG1.UNITSHPD →SALESFACTS.UNITSHPD
Step 4: Setup Priority Group in Logical Table Source
Double click on D1_ORDERS2 LTS in SalesFacts → change Priority Group is 1 → Click on ok
Step5: deleting multiple keys of different levels
1. Double click on customer → select sales rep key & delete.
2. Double click on periods → delete month code key → ok.
Step6: Testing
1. Check in into oracle BI server → set weblogic user log level as 2
2. Develop a report with year and dollars → observe output → observe the log
physical query → The physical query developed using aggregate tables.
3. Similarly develop another report year, quarter and dollars → Observe Physical Query
4. year, quarter, month, dollars → Observe Physical query
5. year, quarter, month, day, dollars → Observe Physical query
Method 2:
In this method we are only modify the step 4 in the above process.
1. Defining customers logical table → double click on D1_customer2 LTS → content tab → Aggregation Content Group By Select Column → Select table customers → Select column customer → Click on ok.
2. Defining customers logical table → double click on D1_SALESREPS LTS → content tab → Aggregation Content Group By Select Column → Select table customers → Select column Sales Rep → Click on ok.
Similarly set below tables and cloumns
3. D1_CALENDAR2 LTS table as Periods and column as Day.
4. D1_CALENDAR2 LTS table as Periods and column as Month.
5. D1_PRODUCTS LTS table as Products and column as Specific.
6. Type LTS table as Products and column as Specific.
7. D1_ORDERS2 LTS table as customers,Periods,Products and column as customer,Day,Specific.
8. D1_ORDER_AGG1 LTS table as customers,Periods,Products and column as Sales Rep,Month,Specific.
9. Create your aggregate table at the level of product subtype, month level, district level, and try to configure in the rpd of practitioner level output ?
Process:
Step 1: Log into database with supplier2 username and password → type
CREATE TABLE D1_ORDER_AGG_TABLE AS SELECT * FROM D1_ORDER_AGG2
SELECT * FROM D1_ORDER_AGG_TABLE
STEP 2: IMPORT METADATA
1) Open online RPD → Right click on connection pool → next
2) Expand supplier2 schema → select D1_ORDER_AGG_TABLE,D1_DISTRICT,MONTHS→ click on Import(>) → click on finish.
STEP 3: Creating Physical Joins
1) select D1_ORDER_AGG_TABLE,D1_DISTRICT,MONTHS,D1_PRODUCTS k on physical diagram → click on new join → drag & drop D1_ORDER_AGG_TABLE to D1_DISTRICT → select DISTRICT and DISTKEY → click on ok.
2) Similarly Create another join between D1_ORDER_AGG_TABLE to MONTHS,D1_PRODUCTS.
3) Close the physical diagram.
Keys :-
1) "ORCL".""."SUPPLIER2"."D1_DISTRICT"."DISTRICT" =
"ORCL".""."SUPPLIER2"."D1_ORDER_AGG_TABLE"."DISTKEY"
2) "ORCL".""."SUPPLIER2"."D1_PRODUCTS"."PRODUCTKEY" =
"ORCL".""."SUPPLIER2"."RAM_ORDER_AGG"."PRODKEY"
3) "ORCL".""."SUPPLIER2"."MONTHS"."MONTHCODE" =
"ORCL".""."SUPPLIER2"."RAM_ORDER_AGG"."PERKEY"
STEP 4: map physical columns to logical columns
Drag and drop physical columns to logical column.
D1_DISTRICT.DISTRICT→ Customers.District
D1_DISTRICT.REGION → Customers.Region
MONTH.MONTHINYEAR →PERIODS.MONTHINYEAR
MONTH.MONTHCODE →PERIODS.MONTHCODE
MONTH.MONTHNAME →PERIODS.NAME
MONTH.QUARTER →PERIODS.QUARTER
MONTH.YEAR →PERIODS.YEAR
D1_ORDER_AGG_TABLE.ACTLEXTND →SALESFACTS.DOLLARS
D1_ORDER_AGG_TABLE.UNITORD →SALESFACTS.UNITORD
D1_ORDER_AGG_TABLE.UNITSHPD →SALESFACTS.UNITSHPD
D1_ORDER_AGG_TABLE.NETWGHTSHPD → SALESFACTS.Net Weight Shipped
Step 5: defining content Logical level
1. Defining customers logical table → double click on D1_customer2 LTS → content tab
→ logical level: customer detail → ok.
2. Double click on D1_DISTRICT LTS → Click on content tab → Select logical level
District → ok.
Similarly set below logical levels
3. Double click on D1_CALENDAR2 LTS → Click on content tab → Select logical level
Day → ok.
4. Double click on MONTHS LTS → Click on content tab → Select logical level
Month → ok.
5. Double click on D1_PRODUCTS LTS → Click on content tab → Select logical level
specific → ok.
6. Double click on TYPE LTS → Click on content tab → Select logical level
Subtype → ok.
7. Double click on D1_ORDERS2 LTS → Click on content tab → Select logical level as
customer detail, day, specific → ok.
8. Double click on D1_ORDER_AGG_TABLE LTS → Click on content tab → Select logical level as
District,Month,Subtype → ok.
Step6: deleting multiple keys of different levels
1. Double click on customer → select District key & delete.
Step7: Testing
1. Check in into oracle BI server → set weblogic user log level as 2
2. Develop a report with year, and dollars → observe output → observe the log
physical query → The physical query developed using aggregate tables.
3. Similarly develop another report year, quarter and dollars →Observe Physical
query
4. year, quarter, month, dollars → Observe Physical query
5. year, quarter, month, day, dollars → Observe Physical query
10. Aggregation based on dimensions?
Based on dimensions is a option of the aggregation column. if your measure has different additivity for different dimensions (in other words, for semi-additive measures).
PROCESS:
STEP 1:
Open online RPD → In BMM layer Select SalesFacts.Dollars column → Right click and select Duplicate → Double click Duplicate column and genral tab rename the AvgDollars → Select Aggregation tab select Based on Dimensions → Select PeriodsDim → Click on ok → Select Edit Formula → Develop Avg("SupplierSales"."SalesFacts"."AvgDollars" ) & ok → Click ‘+’ → Select other & ok → Select Edit Formula → Develop SUM("SupplierSales"."SalesFacts"."AvgDollars") → Click on ok → Click on ok → Click on Check In changes → Save the RPD
STEP 2:
Open Analitics Page → Develop a report MonthCode,Dollors,AvgDollors → Click Result.
No comments:
Post a Comment