Skip Headers
Previous
Previous
 
Next
Next

Joining Cubes to Tables and Views

You can join cubes to other cubes and to relational objects such as:

Typically, you do not need a fully aggregated cube when joining it to a table or view, and a CUBE JOIN operation limits the number of fetched values to improve performance automatically. The cube must be on the right side of the equation. If the query does not support CUBE JOIN, then the more expensive HASH JOIN, MERGE JOIN, or NESTED LOOPS are commonly used.

You can use hints in the query to influence the use of CUBE JOIN:

See "Viewing Execution Plans" for more information about CUBE JOIN.

Example: Joining a Cube and a Table joins a table that contains French descriptions of the Customer dimension to a cube that supports only English. The query returns these results:

CUSTOMER                                        SALES
---------------------------------------- ------------
La Marine des USA Washington                   600.34
Monolith Motor Co. Chattanooga               17946.51
Piedmont, Inc. San Jose                      24874.41
Ministere du Commerce Int. Nagano            27595.97
Depart. des commun. - Stuttgart              30706.10
Min. Env. Brit. Londres                      38125.77
Departement de travail Nouvelle-Orleans      42507.50
Ministere des Finances Sorbonne              43607.58
Monolith Motor Co. Knoxville                 50874.53
Serv. des USA de recherche Wyo               54497.19
Depart. des commun. - Bonn                   58944.97
     .
     .
     .

Joining a Cube and a Table

SELECT cu.ship_to_dsc_french customer,
     f.sales sales
FROM time_calendar_view t,
     product_primary_view p,
     customer_dim cu,
     channel_primary_view ch,
     units_cube_view f
WHERE t.dim_key = 'CY2006'
    AND p.level_name = 'TOTAL'
    AND ch.level_name = 'TOTAL'
    AND t.dim_key = f.TIME
    AND p.dim_key = f.product
    AND cu.ship_to_id = f.customer
    AND ch.dim_key = f.channel
ORDER BY f.sales;