Monday, December 2, 2013

SQL for various solution to M-M relation


The below warehouse schema represents a M-M relationship.  REL, SKU , and COMPOUND tables, provide various solutions for a M-M relationship. We will look at the sql generated by usaing these three different approaches. 


LU_COLOR
COLOR_ID
COLOR
1
Red
2
Blue
3
Green
4
White
LU_PRODUCT
PRODUCT_ID
PRODUCT
1
Car
2
Bike
3
Fridge
REL_PRODUCT_COLOR
ID
COLOR_ID
PRODUCT_ID
1
1
1
2
1
2
3
2
2
4
2
3
5
3
1
6
3
3
7
4
1
8
4
3
SKU_PRODUCT_COLOR
SKU_ID
PRODUCT_ID
COLOR_ID
1
1
1
2
1
3
3
1
4
4
2
1
5
2
2
6
3
2
7
3
3
8
3
4
LU_PRODUCT_COMPOUND
ID
PRODUCT_ID
COLOR_ID
PRODUCT_DESC
1
1
1
CAR
2
1
3
CAR
3
1
4
CAR
4
2
1
BIKE
5
2
2
BIKE
6
3
2
FRIDGE
7
3
3
FRIDGE
8
3
4
FRIDGE
FACT_PRODUCT_SALES
ID
DATE_ID
PRODUCT_ID
COLOR_ID
SALES
SKU_ID
1
4/1/2013
1
1
100
1
2
4/1/2013
2
2
50
5
3
4/1/2013
3
3
50
7
4
4/1/2013
3
4
100
8
5
4/8/2013
1
1
100
1
6
4/8/2013
3
3
50
7
7
4/8/2013
1
4
20
3
8
4/8/2013
3
4
100
8




1.       Method 1 :- using relationship table REL_PRODUCT_COLOR
Without adding metric

(since there is no metric the sql gets the results from the relate table)
select    distinct a12.[PRODUCT_ID] AS PRODUCT_ID,
                a13.[PRODUCT] AS PRODUCT,
                a11.[COLOR_ID] AS COLOR_ID,
                a11.[COLOR] AS COLOR
from      [LU_COLOR]       a11,
                [REL_PRODUCT_COLOR]              a12,
                [LU_PRODUCT] a13
where   a11.[COLOR_ID] = a12.[COLOR_ID] and
                a12.[PRODUCT_ID] = a13.[PRODUCT_ID]

Adding a metric

(since a metric is added the sql will query the results directly from the fact table, and will not use the relate table because both color and product are available in the fact table)
select      a11.[PRODUCT_ID] AS PRODUCT_ID,
                max(a13.[PRODUCT]) AS PRODUCT,
                a11.[COLOR_ID] AS COLOR_ID,
                max(a12.[COLOR]) AS COLOR,
                sum(a11.[SALES]) AS WJXBFS1
from         [FACT_PRODUCT_SALES]             a11,
                [LU_COLOR]          a12,
                [LU_PRODUCT]     a13
where      a11.[COLOR_ID] = a12.[COLOR_ID] and
                a11.[PRODUCT_ID] = a13.[PRODUCT_ID]
group by  a11.[PRODUCT_ID],
                a11.[COLOR_ID]


2.       Method 2 :- Compound attribute. This is done by making the product a compound attribute with color. For this you need to edit the warehouse schema.

Without adding metric.
The results look like below. They are not grouped by product since each record is treated as unique record.  Notice in the SQL, color_id is called twice , once to retrieve the product and once to retrieve the color.

select    distinct a11.[PRODUCT_ID] AS PRODUCT_ID,
                a11.[COLOR_ID] AS COLOR_ID,
                a11.[PRODUCT_DESC] AS PRODUCT_DESC,
                a11.[COLOR_ID] AS COLOR_ID0,
                a12.[COLOR] AS COLOR
from      ([LU_PRODUCT_COMPOUND]  a11
                inner join             [LU_COLOR]       a12
                  on         (a11.[COLOR_ID] = a12.[COLOR_ID]))

With a metric in the report. Since product is a compound attribute, you cannot subtotal at product level. Also notice that the join on Product table happens using both product_id and color_id .


select    a11.[PRODUCT_ID] AS PRODUCT_ID,
                a11.[COLOR_ID] AS COLOR_ID,
                max(a12.[PRODUCT_DESC]) AS PRODUCT_DESC,
                a11.[COLOR_ID] AS COLOR_ID0,
                max(a13.[COLOR]) AS COLOR,
                sum(a11.[SALES]) AS WJXBFS1
from      (([FACT_PRODUCT_SALES]          a11
                inner join                [LU_PRODUCT_COMPOUND]    a12
                  on         (a11.[COLOR_ID] = a12.[COLOR_ID] and
                a11.[PRODUCT_ID] = a12.[PRODUCT_ID]))
                inner join             [LU_COLOR]       a13
                  on         (a11.[COLOR_ID] = a13.[COLOR_ID]))
group by              a11.[PRODUCT_ID],
                a11.[COLOR_ID],
                a11.[COLOR_ID]


3.       Method 3 :- using a common child attribute. 
Without adding metric. The sql will use the SKU table to retrieve the data
select    distinct a12.[PRODUCT_ID] AS PRODUCT_ID,
                a13.[PRODUCT] AS PRODUCT,
                a11.[COLOR_ID] AS COLOR_ID,
                a11.[COLOR] AS COLOR
from      [LU_COLOR]       a11,
                [SKU_PRODUCT_COLOR]             a12,
                [LU_PRODUCT] a13
where   a11.[COLOR_ID] = a12.[COLOR_ID] and
                a12.[PRODUCT_ID] = a13.[PRODUCT_ID]


With a metric . results are same as using the relate table, however sql cannot retrieve the item and color combination from the fact table, it uses the SKU table for this.


select    a12.[PRODUCT_ID] AS PRODUCT_ID,
                max(a14.[PRODUCT]) AS PRODUCT,
                a12.[COLOR_ID] AS COLOR_ID,
                max(a13.[COLOR]) AS COLOR,
                sum(a11.[SALES]) AS WJXBFS1
from      [FACT_PRODUCT_SALES]             a11,
                [SKU_PRODUCT_COLOR]             a12,
                [LU_COLOR]       a13,
                [LU_PRODUCT] a14
where   a11.[PRODUCT_ID] = a12.[PRODUCT_ID] and
                a11.[SKU_ID] = a12.[SKU_ID] and
                a12.[COLOR_ID] = a13.[COLOR_ID] and
                a11.[PRODUCT_ID] = a14.[PRODUCT_ID] and
                a12.[PRODUCT_ID] = a14.[PRODUCT_ID]
group by              a12.[PRODUCT_ID],
                a12.[COLOR_ID]