Dimensional query - Rule no. 3 ... Multi-stars query must have common dimensions

For example, the following is a multi-stars query on sales star and stock star. The two stars have date_dim and product_dim dimension tables in common. The top query joins the queries of the individual star on the common dimensions.

SELECT ...
FROM
(SELECT ...
FROM sales_fact sf, date_dim dd, store_dim sd, product_dim pd
WHERE
sf.dd_sk = dd.dd_sk
AND sf.sd_sk = sd.sd_sk
AND sf.pd_sk = pd.pd_sk
AND dd.month_name = "January"
AND dd.year = 2010
AND sd.store_name = "DownTown Branch"
AND pd.ISBN = 0975212826) sales
,

(SELECT ...
FROM inventory_fact if, date_dim dd, stock_location_dim sd, product_dim pd
WHERE
if.dd_sk = dd.dd_sk
AND if.sd_sk = sd.sd_sk
AND if.pd_sk = pd.pd_sk) stock

WHERE
sales.dd_sk = stock.dd_sk
AND sales.pd_sk = stock.pd_sk

Dimensional query - rule no. 2...Constraints on the dimension

For example,


SELECT ...
FROM sales_fact sf, date_dim dd, store_dim sd, product_dim pd
WHERE
sf.dd_sk = dd.dd_sk
AND sf.sd_sk = sd.sd_sk
AND sf.pd_sk = pd.pd_sk
AND dd.month_name = "January" AND dd.year = 2010
AND sd.store_name = "DownTown Branch"
AND pd.ISBN = 0975212826

Dimensional query - rule no.1...Join on the surrogate keys

For every star that you want to query, join the fact table to its dimensional tables. So, for example:

SELECT ...
FROM sales_fact sf, date_dim dd, store_dim sd, product_dim pd
WHERE
sf.dd_sk = dd.dd_sk
AND sf.sd_sk = sd.sd_sk
AND sf.pd_sk = pd.pd_sk

Dimension table

Dimension table contains attributes that define or constraint the facts in the fact table that relates to the dimension. In the case of sales amount fact for example, one of its dimension can be Product. The Product dimension can have columns such as Product Code, Product Name, and Brand.

Fact tables likely have time-related dimension, such as Date dimension...one distinct characteristic of data warehouse is keeping history of data!

Fact table

Fact table contains one or more fact column. Fact is what the business measures; for example, sales amount, sales quantity, duration of calls, number of calls (in telephone business), grade (school test result), rank (in sports for example)

In addition fact table has all the surrogate keys of its related dimensions.

Purpose of dimensional model

Dimensional model is particularly suitable for developing data warehouse, and data mart as well--smaller, often departmental, data warehouse.

Benefiting from this blog

To understand and get benefit from Dimensional Modeling Cookbook blogs you need to have working knowledge of relational database and SQL.