If you have ever wanted to report on the total of two or more dimension members you have probably done so using in SQL using SUM ... GROUP BY.
Let's look at an example using the OLAPTRAIN schema. Suppose you want the total of the states in the northeastern United States. In OLAPTRAIN, that would include Massachusetts, Rhode Island, New York and Pennsylvania..
If you did this with SQL, it might look something like this.
SELECT t.calendar_year_long_descr AS calendar_year,
g.country_long_description AS country,
p.all_products_long_descri AS all_products,
c.all_channels_long_descri AS all_channels,
SUM(f.sales)
FROM time_calendar_view t,
product_standard_view p,
geography_regional_view g,
channel_sales_channel_view c,
sales_cube_view f
WHERE t.dim_key = f.time
AND p.dim_key = f.product
AND g.dim_key = f.geography
AND c.dim_key = f.channel
AND t.level_name = 'CALENDAR_YEAR'
AND p.level_name = 'ALL_PRODUCTS'
AND c.level_name = 'ALL_CHANNELS'
AND g.level_name = 'STATE_PROVINCE'
AND g.state_province_long_desc IN ('Massachusetts','Rhode Island','New York', 'Pennsylvania')
AND t.calendar_year_long_descr = 'CY2009'
AND g.country_long_description = 'United States'
GROUP BY t.calendar_year_long_descr,
g.country_long_description,
p.all_products_long_descri,
c.all_channels_long_descri;
And the query would return as
CY2009 United States All Products All Channels 10828869.07
A different way to approach this is to create a custom member that is the aggregate of other members. In this case, the custom member is added to the dimension and can be used just like any other dimension member. The only real difference is that a custom member is not within a hierarchy and does not belong to a level. The advantages are that the custom member is available to all users (unless you control access, more on that later), they work with all of the cube's aggregation rules (e.g., first, last, hierarchical weighted average and so on), they work seamlessly with calculated measures and they are available in all tools (e.g., Excel PivotTables).
Custom aggregates are created using the dbms_cube.import program. Note that the dimension keys are numeric in OLAPTRAIN. (Sorry for posting this sample as an image ... blogger wasn't happy about displaying XML. To view the full example option the image in a new tab or window).
I can view custom members in the dimension or hierarchy view with a query such as this:
SELECT * FROM geography_view WHERE member_type = 'C';
I can query the cube view with a simplified query (note the lack of SUM ... GROUP BY).
SELECT t.long_description AS time,
g.long_description AS geog,
p.long_description AS product,
c.long_description AS channel,
f.sales
FROM time_calendar_view t,
product_standard_view p,
geography_regional_view g,
channel_sales_channel_view c,
sales_cube_view f
WHERE t.dim_key = f.time
AND p.dim_key = f.product
AND g.dim_key = f.geography
AND c.dim_key = f.channel
AND t.level_name = 'CALENDAR_YEAR'
AND p.level_name = 'ALL_PRODUCTS'
AND c.level_name = 'ALL_CHANNELS'
AND g.long_description = 'Northeast US'
AND t.calendar_year_long_descr = 'CY2009';
And the query returns Northeast US rather than a modified version of United States.
CY2009 Northeast US All Products All Channels 10828869.07
If it is appropriate for the custom members to be visable to all users, there's nothing else to do. If you want to control access to custom member, Virtual Private Database is probably a good choice.
If you would like a sample script that creates a few custom members and sets up a VPD policy to control access, send me an email at william.endress@oracle.com.
