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

image from book
 /**********************************************************************/ /*                                                                    */ /* 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                                                      */
image from book

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

image from book
 /**********************************************************************/ /*                                                                    */ /* 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                                                      */
image from book

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)



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net