February 18, 2015 at 5:55 am
Hi,
I have a fact table that holds sales values. This is linked to a product dimension. A product can appear in multiple seasons so I want to create a seasons dimensions and use a bridge table (I think) that links the product to the various seasons. I'm usure on how to join things in the DSV and how to define the dimension relationships. I need the user to be able to pick a season to show any sales for products that appeared in those seasons (without doubling up values). Here are some example tables and data which might make things clearer:
-- Create tables:
CREATE TABLE [dbo].[fact_sales](
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[DateKey] [int] ,
[product_id] smallint,
[qty] [int] ,
[net_value] [money] ,
[vat_value] [money] ,
[gross_value] [money] ,
CONSTRAINT [PK_fact_sales] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dim_products](
product_id smallint,
sku varchar(20),
sku_descr varchar(100),
CONSTRAINT [PK_dim_products] PRIMARY KEY CLUSTERED
(
product_id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dim_seasons](
[season_id] smallint NOT NULL,
[season_code] [varchar](20) NULL,
[season_descr] [varchar](100) NULL,
CONSTRAINT [PK_dim_seasons] PRIMARY KEY CLUSTERED
(
[season_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dim_product_seasons](
[product_id] smallint NOT NULL,
[season_id] smallint NOT NULL,
CONSTRAINT [PK_dim_product_seasons] PRIMARY KEY CLUSTERED
(
[product_id] ASC,
[season_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Add dummy records
INSERT dim_products
VALUES (1, 'PART1', 'Part 1'),
(2, 'PART2', 'Part 2'),
(3, 'PART2', 'Part 3')
INSERT dim_seasons
VALUES (1, 'SS12', 'Spring Summer 2012'),
(2, 'AW12', 'Autumn Winter 2012/13'),
(3, 'SS13', 'Spring Summer 2013'),
(4, 'AW13', 'Autumn Winter 2013/14'),
(5, 'SS14', 'Spring Summer 2014'),
(6, 'AW14', 'Autumn Winter 2014/15')
INSERT dim_product_seasons
VALUES (1,1),
(1,2),
(1,5),
(2,2),
(2,3),
(2,4),
(2,5),
(2,6)
INSERT fact_sales ([DateKey],
[product_id],
[qty],
[net_value],
[vat_value],
[gross_value])
VALUES (20150201, 1, 1, 20.00, 4.00, 24.00),
(20150201, 2, 4, 35.99, 7.20, 43.19),
(20150202, 2, 1, 20.00, 4.00, 24.00),
(20150203, 3, 1, 40.00, 8.00, 48.00),
(20150203, 1, 2, 50.00, 10.00, 60.00)
-- Return values for AW12
SELECT *
FROM fact_sales
WHERE product_id IN (SELECT dim_product_seasons.product_id
FROM dim_product_seasons
INNER JOIN dim_seasons ON dim_seasons.season_id = dim_product_seasons.season_id
WHERE dim_seasons.season_code = 'AW12')
Any ideas or good tutorials on this?
February 18, 2015 at 7:43 am
Rooster (2/18/2015)
Hi,I have a fact table that holds sales values. This is linked to a product dimension. A product can appear in multiple seasons so I want to create a seasons dimensions and use a bridge table (I think) that links the product to the various seasons. I'm usure on how to join things in the DSV and how to define the dimension relationships. I need the user to be able to pick a season to show any sales for products that appeared in those seasons (without doubling up values). Here are some example tables and data which might make things clearer:
-- Create tables:
CREATE TABLE [dbo].[fact_sales](
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[DateKey] [int] ,
[product_id] smallint,
[qty] [int] ,
[net_value] [money] ,
[vat_value] [money] ,
[gross_value] [money] ,
CONSTRAINT [PK_fact_sales] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dim_products](
product_id smallint,
sku varchar(20),
sku_descr varchar(100),
CONSTRAINT [PK_dim_products] PRIMARY KEY CLUSTERED
(
product_id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dim_seasons](
[season_id] smallint NOT NULL,
[season_code] [varchar](20) NULL,
[season_descr] [varchar](100) NULL,
CONSTRAINT [PK_dim_seasons] PRIMARY KEY CLUSTERED
(
[season_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dim_product_seasons](
[product_id] smallint NOT NULL,
[season_id] smallint NOT NULL,
CONSTRAINT [PK_dim_product_seasons] PRIMARY KEY CLUSTERED
(
[product_id] ASC,
[season_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Add dummy records
INSERT dim_products
VALUES (1, 'PART1', 'Part 1'),
(2, 'PART2', 'Part 2'),
(3, 'PART2', 'Part 3')
INSERT dim_seasons
VALUES (1, 'SS12', 'Spring Summer 2012'),
(2, 'AW12', 'Autumn Winter 2012/13'),
(3, 'SS13', 'Spring Summer 2013'),
(4, 'AW13', 'Autumn Winter 2013/14'),
(5, 'SS14', 'Spring Summer 2014'),
(6, 'AW14', 'Autumn Winter 2014/15')
INSERT dim_product_seasons
VALUES (1,1),
(1,2),
(1,5),
(2,2),
(2,3),
(2,4),
(2,5),
(2,6)
INSERT fact_sales ([DateKey],
[product_id],
[qty],
[net_value],
[vat_value],
[gross_value])
VALUES (20150201, 1, 1, 20.00, 4.00, 24.00),
(20150201, 2, 4, 35.99, 7.20, 43.19),
(20150202, 2, 1, 20.00, 4.00, 24.00),
(20150203, 3, 1, 40.00, 8.00, 48.00),
(20150203, 1, 2, 50.00, 10.00, 60.00)
-- Return values for AW12
SELECT *
FROM fact_sales
WHERE product_id IN (SELECT dim_product_seasons.product_id
FROM dim_product_seasons
INNER JOIN dim_seasons ON dim_seasons.season_id = dim_product_seasons.season_id
WHERE dim_seasons.season_code = 'AW12')
Any ideas or good tutorials on this?
My suggestion would be to build the Season attributes into your Product dimension. It'll change the business key of that dimension, but that's ok because the season is what defines the product as well.
February 18, 2015 at 8:33 am
IF you take Martin's approach, you'll be able to report on the individual 'season product' but also the product in total, regardless of the seasonal variant.
Steve.
February 18, 2015 at 8:36 am
I've managed to resolve this by creating a factless fact table and defining many to many dimension relationships. To add the season into the product dimension would mean I had multiple records for the same part which would work fine for the sales fact but not for another fact table - stock (which I didn't mention). Thanks for the responses.
February 18, 2015 at 8:40 am
stevefromOZ (2/18/2015)
IF you take Martin's approach, you'll be able to report on the individual 'season product' but also the product in total, regardless of the seasonal variant.
Yeah, I'm following this approach on a similar retail project right now...and it works well.
If you have separate dimensions, you'll end up combining them in the cube anyways...or associate the two dimensions through the facts, which may be dangerous because the relationship will then be dependent on the existence of fact records.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply