Database View

You implement the second type of date dimension role-playing by creating two database views, one for each of the two dates. You or your user can then use these views as dimension tables in the queries.

The script in Listing 13.6 can be used to create the views.

Listing 13.6: Creating date views

 /**********************************************************************/ /*                                                                    */ /* date_views.sql                                                     */ /*                                                                    */ /**********************************************************************/ USE dw; CREATE VIEW order_date_dim (   order_date_sk , order_date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date ) AS SELECT   date_sk , date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date FROM date_dim ; CREATE VIEW request_delivery_date_dim (   request_delivery_date_sk , request_delivery_date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date ) AS SELECT   date_sk , date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date FROM date_dim ; /* end of script                                                      */

Use this command to execute the script in Listing 13.6 to create the views.

 mysql> \. c:\mysql\scripts\date_views.sql

The query in Listing 13.7 uses the two date views to accomplish the same objective as the previous query that uses table alias.

Listing 13.7: Database view role playing

 /**********************************************************************/ /*                                                                    */ /* database_view.sql                                                  */ /*                                                                    */ /**********************************************************************/ SELECT   order_date , request_delivery_date , SUM (order_amount) , COUNT(*) FROM   sales_order_fact a , order_date_dim b , request_delivery_date_dim c WHERE     a.order_date_sk = b.order_date_sk AND a.request_delivery_date_sk = c.request_delivery_date_sk GROUP BY order_date, request_delivery_date ORDER BY order_date, request_delivery_date ; /* end of script                                                      */

Run the database_view.sql script using this command.

 mysql> \. c:\mysql\scripts\database_view.sql

The result should be the same as the result from the previous query.

 +-----------+-----------------------+--------------------+----------+ |order_date | request_delivery_date | SUM (order_amount) | COUNT(*) | +-----------+-----------------------+--------------------+----------+ |2007-03-04 | 2007-03-30            |            9500.00 |        3 | +-----------+-----------------------+--------------------+----------+ 1 row in set (0.00 sec)