Level vs. Rule Drilling
Article ID: 56011
Updated On:
Products
Issue/Introduction
Executive Summary
Each dimension table has different traits. Therefore, they may need to be treated differently when drilling. There are 2 methods of drilling, L or dimension level drilling or R, Rule drilling. The following SQL statements may help identify the best solution for a specific dimensions drilling.
The following queries were run on an IBM ThinkPad demonstration server running AIX and Red Brick. It contained 3 aggregation tables and one base fact table with approximately 250,000 rows. Keep in mind that these performance numbers may vary significantly on different servers and database engines.
Environment
Component: STGY
Resolution
L vs. R Drilling Types
The following report was the sample used for the following information. Notice that the difference between the 2 drill types is the market and fact table query.
If the 'L' type of drilling is used on the destination hierarchy (level) the queries will be similar to the queries that are created with dynamic filters.
If the 'R' type of drilling is used on the destination hierarchy the queries will be similar to the queries that are created with level filters. That is, a physical join will occur with the dimension table and the fact table. This level information is referenced from the levelName field of the metadata Dim table. 'R' type drilling is not available for the period dimension.
Utilizing the following report:
Section
Period Dimension = Current Week
Product Dimension = "Example Company" database element.
Rows
Market Dimension = 3 Markets database elements: Atlanta, Birmingham, and Boston
Columns
Facts = Gross $ volumetric fact
Drill up on 'Atlanta'; it is at the Market level. So drilling up will take us to the destination level, District.
As a result, the hierarchy row within the metadata Drill table that affects the type of drilling; 'L' or 'R' is the District level. The actual SQL statements follow.
'L' logging from Market Level up to District Level
Begins the selection of the item drilled upon:
11/4/97 09:58:37.64 - - START SQL ******************************
11/4/97 09:58:37.64 - - select * from MARKET d where d.MKTDESC='ATLANTA'
11/4/97 09:58:37.64 - - END SQL ********************************
11/4/97 09:58:37.99 - US4 - MKTKEY MKTDESC SEQ LEVELX MKT_SHORTDESC DISTRICT REGION 11/4/97 09:58:37.99 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SOUTHERN REGION
11/4/97 09:58:38.06 - - START SQL ******************************
11/4/97 09:58:38.06 - - select * from MARKET d where d.MKTDESC='ATLANTA'
11/4/97 09:58:38.06 - - END SQL ********************************
11/4/97 09:58:38.13 - US4 - MKTKEY MKTDESC SEQ LEVELX MKT_SHORTDESC DISTRICT REGION
11/4/97 09:58:38.14 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SOUTHERN REGION
11/4/97 09:58:38.39 - - START SQL ******************************
11/4/97 09:58:38.39 - - select * from MARKET d where d.MKTDESC='ATLANTA'
11/4/97 09:58:38.39 - - END SQL ********************************
11/4/97 09:58:38.46 - US4 - MKTKEY MKTDESC SEQ LEVELX MKT_SHORTDESC DISTRICT REGION
11/4/97 09:58:38.46 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SOUTHERN REGION
11/4/97 09:58:38.66 - - START SQL ******************************
11/4/97 09:58:38.66 - - Select count(*) from MARKET Mkt where Mkt.LEVELX='DISTRICT' and Mkt.REGION='SOUTHERN REGION'
11/4/97 09:58:38.66 - - END SQL ********************************
11/4/97 09:58:38.72 - US4 - NULL
11/4/97 09:58:38.72 - US4 - 5
11/4/97 09:58:38.78 - - START SQL ******************************
11/4/97 09:58:38.78 - - select Mkt.MKTDESC from MARKET Mkt where Mkt.LEVELX= 'DISTRICT' and Mkt.REGION='SOUTHERN REGION'
11/4/97 09:58:38.78 - - END SQL ********************************
11/4/97 09:58:38.85 - US4 - MKTDESC
11/4/97 09:58:38.85 - US4 - JACKSONVILLE DISTRICT
11/4/97 09:58:38.85 - US4 - CHARLOTTE DISTRICT
11/4/97 09:58:38.85 - US4 - MEMPHIS DISTRICT
11/4/97 09:58:38.85 - US4 - ATLANTA DISTRICT
11/4/97 09:58:38.86 - US4 - DALLAS DISTRICT
Market Queries for report creation:
11/4/97 09:58:48.08 - - START SQL ******************************
11/4/97 09:58:48.08 - - select MKTDESC,LEVELX,MKTKEY from MARKET where MKTDESC in ('JACKSONVILLE DISTRICT','CHARLOTTE DISTRICT','MEMPHIS DISTRICT','ATLANTA DISTRICT','DALLAS DISTRICT')
11/4/97 09:58:48.08 - - END SQL ********************************
11/4/97 09:58:48.15 - US4 - MKTDESC LEVELX MKTKEY
11/4/97 09:58:48.15 - US4 - JACKSONVILLE DISTRICT DISTRICT 57
11/4/97 09:58:48.16 - US4 - CHARLOTTE DISTRICT DISTRICT 64
11/4/97 09:58:48.16 - US4 - MEMPHIS DISTRICT DISTRICT 68
11/4/97 09:58:48.16 - US4 - ATLANTA DISTRICT DISTRICT 69
11/4/97 09:58:48.16 - US4 - DALLAS DISTRICT DISTRICT 71
11/4/97 09:58:48.24 - - START SQL ******************************
11/4/97 09:58:48.24 - - select MKTKEY,DISTRICT from MARKET where MKTKEY in (57,64,68,69,71)
11/4/97 09:58:48.24 - - END SQL ********************************
11/4/97 09:58:48.58 - US4 - MKTKEY DISTRICT
11/4/97 09:58:48.58 - US4 - 57 JACKSONVILLE DISTRICT
11/4/97 09:58:48.58 - US4 - 64 CHARLOTTE DISTRICT
11/4/97 09:58:48.59 - US4 - 68 MEMPHIS DISTRICT
11/4/97 09:58:48.59 - US4 - 69 ATLANTA DISTRICT
11/4/97 09:58:48.59 - US4 - 71 DALLAS DISTRICT
Fact Table Query for report with the use of Agg Aware:
11/4/97 09:58:52.88 - - select f.PERKEY,Prod.MFR,Mkt.DISTRICT,Sum(f.DOL)
from FACT f,PRODUCT Prod,MARKET Mkt where f.PRODKEY=Prod.PRODKEY and Prod.LEVELX='UPC' and f.MKTKEY=Mkt.MKTKEY and Mkt.LEVELX='MARKET' and f.PERKEY in (88122401) and Prod.MFR in ('EXAMPLE COMPANY') and Mkt.DISTRICT in ('JACKSONVILLE DISTRICT', 'CHARLOTTE DISTRICT','MEMPHIS DISTRICT','ATLANTA DISTRICT','DALLAS DISTRICT') group by f.PERKEY,Prod.MFR,Mkt.DISTRICT
11/4/97 09:58:52.88 - - END SQL ********************************
11/4/97 09:58:54.18 - US4 - PERKEY MFR DISTRICT NULL
11/4/97 09:58:54.23 - US4 - 88122401 Example Company ATLANTA DISTRICT 93197.00
11/4/97 09:58:54.23 - US4 - 88122401 Example Company DALLAS DISTRICT 190106.00
11/4/97 09:58:54.24 - US4 - 88122401 Example Company JACKSONVILLE DISTRICT 154923.00
11/4/97 09:58:54.24 - US4 - 88122401 Example Company MEMPHIS DISTRICT 44419.00
11/4/97 09:58:54.24 - US4 - 88122401 Example Company CHARLOTTE DISTRICT 16937.00
11/4/97 09:58:54.82 - - START SQL ******************************
11/4/97 09:58:54.82 - - select MFR,PRODDESC,PRODDESC,TRADEMARK,CPACK,SIZEX,MFR,FORM,LEVELX,DEAL,SCENT,SEQ from PRODUCT where LEVELX='MFR' and MFR in ('Example Company')
11/4/97 09:58:54.83 - - END SQL ********************************
11/4/97 09:58:54.96 - US4 - MFR PRODDESC PRODDESC TRADEMARK CPACK SIZEX MFR FORM LEVELX DEAL SCENT SEQ
11/4/97 09:58:54.97 - US4 - Example Company Example Company Example Company NULL 0 0 Example Company CO NULL MFR NULL NULL 3002
'R' logging on Market Level to District Level
Begins the selection of the item drilled upon:
11/4/97 10:16:53.66 - - START SQL ******************************
11/4/97 10:16:53.66 - - select * from MARKET d where d.MKTDESC='ATLANTA'
11/4/97 10:16:53.66 - - END SQL ********************************
11/4/97 10:16:54.01 - US4 - MKTKEY MKTDESC SEQ LEVELX MKT_SHORTDESC DISTRICT REGION
11/4/97 10:16:54.01 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SOUTHERN REGION
11/4/97 10:16:54.10 - - START SQL ******************************
11/4/97 10:16:54.10 - - select * from MARKET d where d.MKTDESC='ATLANTA'
11/4/97 10:16:54.10 - - END SQL ********************************
11/4/97 10:16:54.17 - US4 - MKTKEY MKTDESC SEQ LEVELX MKT_SHORTDESC DISTRICT REGION
11/4/97 10:16:54.17 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SOUTHERN REGION
11/4/97 10:16:54.41 - - START SQL ******************************
11/4/97 10:16:54.42 - - select * from MARKET d where d.MKTDESC='ATLANTA'
11/4/97 10:16:54.42 - - END SQL ********************************
11/4/97 10:16:54.49 - US4 - MKTKEY MKTDESC SEQ LEVELX MKT_SHORTDESC DISTRICT REGION
11/4/97 10:16:54.49 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SOUTHERN REGION
There is no Market Query since $Rule (rule drilling) is being utilized.
Actual Fact Query for the report run while utilizing Agg Aware:
11/4/97 10:17:08.72 - - select f.PERKEY,Prod.MFR,Mkt.DISTRICT,Sum(f.DOL)
from FACT f,PRODUCT Prod,MARKET Mkt where f.PRODKEY=Prod.PRODKEY and Prod.LEVELX='UPC' and f.MKTKEY=Mkt.MKTKEY and Mkt.LEVELX='MARKET' and f.PERKEY in (88122401) and Prod.MFR in ('Example Company') and Mkt.REGION='SOUTHERN REGION' group by f.PERKEY,Prod.MFR,Mkt.DISTRICT
11/4/97 10:17:08.72 - - END SQL ********************************
11/4/97 10:17:09.73 - US4 - PERKEY MFR DISTRICT NULL
11/4/97 10:17:09.73 - US4 - 88122401 Example Company ATLANTA DISTRICT 93197.00 11/4/97 10:17:09.73 - US4 - 88122401 Example Company DALLAS DISTRICT 190106.00 11/4/97 10:17:09.74 - US4 - 88122401 Example Company JACKSONVILLE DISTRICT 154923.00 11/4/97 10:17:09.74 - US4 - 88122401 Example Company MEMPHIS DISTRICT 44419.00
11/4/97 10:17:09.74 - US4 - 88122401 Example Company CHARLOTTE DISTRICT 16937.00
11/4/97 10:17:09.79 - - START SQL ******************************
11/4/97 10:17:09.80 - - select MFR,PRODDESC,PRODDESC,TRADEMARK,CPACK,SIZEX,MFR,FORM,LEVELX,DEAL,SCENT,SEQ from PRODUCT where LEVELX='MFR' and MFR in ('Example Company')
11/4/97 10:17:09.80 - - END SQL ********************************
11/4/97 10:17:09.95 - US4 - MFR PRODDESC PRODDESC TRADEMARK CPACK SIZEX MFR FORM LEVELX DEAL SCENT SEQ
11/4/97 10:17:09.95 - US4 - Example Company Example Company Example Company NULL 0 0 Example Company NULL MFR NULL NULL 3002
11/4/97 10:17:09.99 - - START SQL ******************************
11/4/97 10:17:09.99 - - select DISTRICT,MKTDESC,MKTDESC,REGION,DISTRICT,LEVELX from MARKET where LEVELX='DISTRICT' and DISTRICT in ('ATLANTA DISTRICT','DALLAS DISTRICT','JACKSONVILLE DISTRICT','MEMPHIS DISTRICT','CHARLOTTE DISTRICT')
11/4/97 10:17:09.99 - - END SQL ********************************
11/4/97 10:17:10.09 - US4 - DISTRICT MKTDESC MKTDESC REGION DISTRICT LEVELX
11/4/97 10:17:10.10 - US4 - JACKSONVILLE DISTRICT JACKSONVILLE DISTRICT JACKSONVILLE DISTRICT SOUTHERN REGION JACKSONVILLE DISTRICT DISTRICT
11/4/97 10:17:10.10 - US4 - CHARLOTTE DISTRICT CHARLOTTE DISTRICT CHARLOTTE DISTRICT SOUTHERN REGION CHARLOTTE DISTRICT DISTRICT
11/4/97 10:17:10.20 - US4 - MEMPHIS DISTRICT MEMPHIS DISTRICT MEMPHIS DISTRICT SOUTHERN REGION MEMPHIS DISTRICT DISTRICT
11/4/97 10:17:10.20 - US4 - ATLANTA DISTRICT ATLANTA DISTRICT ATLANTA DISTRICT SOUTHERN REGION ATLANTA DISTRICT DISTRICT
11/4/97 10:17:10.20 - US4 - DALLAS DISTRICT DALLAS DISTRICT DALLAS DISTRICT SOUTHERN REGION DALLAS DISTRICT DISTRICT
Attachments
Feedback
