Level vs. Rule Drilling

Document ID : KB000056011
Last Modified Date : 14/02/2018
Show Technical Document Details
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.


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 = Bionic Cleaner Co Manufacturer 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 SO
UTHERN 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 DIST
RICT REGION
11/4/97 09:58:38.14 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SO
UTHERN 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 DIST
RICT REGION
11/4/97 09:58:38.46 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SO
UTHERN 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 MK
TDESC in ('JACKSONVILLE DISTRICT','CHARLOTTE DISTRICT','MEMPHIS DISTRICT','ATLAN
TA 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 ('BIONIC CLEANER CO') 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 BIONIC CLEANER CO ATLANTA DISTRICT
93197.00
11/4/97 09:58:54.23 - US4 - 88122401 BIONIC CLEANER CO DALLAS DISTRICT 19
0106.00
11/4/97 09:58:54.24 - US4 - 88122401 BIONIC CLEANER CO JACKSONVILLE DISTRIC
T 154923.00
11/4/97 09:58:54.24 - US4 - 88122401 BIONIC CLEANER CO MEMPHIS DISTRICT
44419.00
11/4/97 09:58:54.24 - US4 - 88122401 BIONIC CLEANER CO 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,SIZE
X,MFR,FORM,LEVELX,DEAL,SCENT,SEQ from PRODUCT where LEVELX='MFR' and MFR in ('BIONIC CLEANER CO')
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 - BIONIC CLEANER CO BIONIC CLEANER CO BIONIC CLEA
NER CO NULL 0 0 BIONIC CLEANER 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 DIST
RICT REGION
11/4/97 10:16:54.01 - US4 - 4 ATLANTA 27 MARKET ATL ATLANTA DISTRICT SO
UTHERN 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 DIST
RICT 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 ('BIONIC CLEANER CO') 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 BIONIC CLEANER CO ATLANTA DISTRICT 93197.00
11/4/97 10:17:09.73 - US4 - 88122401 BIONIC CLEANER CO DALLAS DISTRICT 190106.00
11/4/97 10:17:09.74 - US4 - 88122401 BIONIC CLEANER CO JACKSONVILLE DISTRICT 154923.00
11/4/97 10:17:09.74 - US4 - 88122401 BIONIC CLEANER CO MEMPHIS DISTRICT
44419.00
11/4/97 10:17:09.74 - US4 - 88122401 BIONIC CLEANER CO 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,SIZE
X,MFR,FORM,LEVELX,DEAL,SCENT,SEQ from PRODUCT where LEVELX='MFR' and MFR in ('BIONIC CLEANER CO')
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 - BIONIC CLEANER CO BIONIC CLEANER CO BIONIC CLEA
NER CO NULL 0 0 BIONIC CLEANER CO 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,L
EVELX from MARKET where LEVELX='DISTRICT' and DISTRICT in ('ATLANTA DISTRICT','D
ALLAS 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 LEV
ELX
11/4/97 10:17:10.10 - US4 - JACKSONVILLE DISTRICT JACKSONVILLE DISTRICT JAC
KSONVILLE 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 DISTR
ICT SOUTHERN REGION MEMPHIS DISTRICT DISTRICT
11/4/97 10:17:10.20 - US4 - ATLANTA DISTRICT ATLANTA DISTRICT ATLANTA DISTR
ICT SOUTHERN REGION ATLANTA DISTRICT DISTRICT
11/4/97 10:17:10.20 - US4 - DALLAS DISTRICT DALLAS DISTRICT DALLAS DISTRICT
SOUTHERN REGION DALLAS DISTRICT DISTRICT