Database Reference
In-Depth Information
JOIN GENRE G ON(M.GENRE_ID = G.GENRE_ID)
JOIN CONTINENT CT ON(CT.CONTINENT_ID = S.CONTINENT_ID)
JOIN COUNTRY CY ON(CY.COUNTRY_ID = S.COUNTRY_ID));
So now there is a view on a part of the sales data with various other
attributes included. Typically, in a data warehouse, this view might be cre-
ated as a materialized view. Now we need to create a few goodies for the
expression filters to function. We have to create what is called an
attribute
set
, essentially an Oracle TYPE object (class or structure). The following
anonymous PL/SQL procedure (see Chapter 24) uses an Oracle-provided
package called DBMS_EXPFIL. An attribute set or type called HitCD is
created, and three attributes are added: Continent, Country, and Genre.
BEGIN
DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(ATTR_SET=>'HitCD');
DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(ATTR_SET=>'HitCD'
, ATTR_NAME=>'Continent', ATTR_TYPE=>'VARCHAR2(32)');
DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(ATTR_SET=>'HitCD'
, ATTR_NAME=>'Country', ATTR_TYPE=>'VARCHAR2(32)');
DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(ATTR_SET=>'HitCD'
, ATTR_NAME=>'Genre', ATTR_TYPE=>'VARCHAR2(32)');
END;
/
Now we create a table to contain the filters (WHERE clauses) and assign
the attribute set to that new table and filter column using another anony-
mous PL/SQL procedure.
CREATE TABLE
SALESANALYSIS
(Filter VARCHAR2(4000));
BEGIN
DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET(ATTR_SET=>'
HitCD
'
, EXPR_TAB=>'
SALESANALYSIS
', EXPR_COL=>'
FILTER
');
END;
/
Now we create the filter rows in the SALESANALYSIS table. The first
filter is highlighted.
