Database Reference
In-Depth Information
3.5.2 Creating Custom/Filtered Sets
While functions on the surface are generally straightforward, when you start combining
functions with Boolean logic filtering, you can create powerful dynamic selection tools.
For instance, consider the following requirements for creating the product hierarchy:
• Product Sku (stock-keeping unit) grouped into product families (leveraging
the same concatenation and substring requirements from the previous example)
• Alternate hierarchies for all package types
• Alternate hierarchy for caffeinated products with sales greater than $800
The first two requirements are easily handled as standard hierarchies or subhierar-
chies. In fact, the second requirement could be easily handled as an attribute dimen-
sion. The third requirement, unfortunately, is a little more complex. you need to create
a hierarchy that changes dynamically based on both the attributes of a given product as
well as the numeric sales of that product. All three requirements can be handled with
the application of various CPL functions and the Studio filtering capabilities. to create
this specific hierarchy leveraging the Sample.Basic sample SQL source, you use multiple
tables (SuPPLIEr, SALESFACt, ProDuCt, ProDuCtDIm):
1. Create a dimension element using the following CPL code in the
Caption
Binding
area:
'substr'( connection : \'SampleSQL'::'TBC.dbo.
SUPPLIER'.'SUPPLIER_ALIAS', 0, 5 ) || "-" || connection :
\'SampleSQL'::'TBC.dbo.PRODUCT'.'SKU'
2. In the Filter area, type the following CPL code:
connection : \'SampleSQL'::'TBC.dbo.SALESFACT'.'SALES' >= 800
and connection : \'SampleSQL'::'TBC.dbo.PRODUCT'.'CAFFEINATED'
== "True"
In this case, notice that the AnD clause combines both the test for
CAFFEInAtED attribute as well as filtering the result set where SALES from
the SALESFACt table is greater than or equal to $800.
3. Create two additional dimension elements for the package types of can and
bottle using the same caption binding listed in step 1 and filtering for bottle and
can (use the filtering code in step 2 as an example). For example:
Caption Binding
'substr'( connection : \'SampleSQL'::'TBC.dbo.
SUPPLIER'.'SUPPLIER_ALIAS', 0, 5 ) || "-" || connection :
\'SampleSQL'::'TBC.dbo.PRODUCT'.'SKU'
Filter
connection : \'SampleSQL'::'TBC.dbo.PRODUCT'.'PKGTYPE' ==
"BOTTLE"
4. Create a new hierarchy object and leverage the dimension elements created in
the previous step (Figure 3.21).
The preview of this hierarchy can be seen in Figure 3.22.
Note:
This hierarchy contains a series of user-defined members (uDms) to separate the
hierarchies and act as totals (“All Products,” “Product by Package type,” etc.). Further,
when performing actions on existing dimension elements, it is recommended that you
make copies and perform all actions on the copies. This allows you to revert to the origi-
nal element to make additional custom nodes as required.
