January 12, 2010 at 12:11 pm
so i have my fact table that for this example holds the affiliate foreign key and the affiliatCategoryId.
My dimention tables for this example are DimAffiliate, DimAffiliateCategory, And DimCategory.
I was reading about Dimentions and it says your db design should be a star approach instead of a more normalized "Snowflake" approach. The below talbes are designed in a 3rd nomal form. There is a many to one relationship between the categories and affiliates. An affiliate can have multiple categories. Should i keep it in 3rd normal form or denormalize it and flatten it out and why?
GO
/****** Object: Table [dbo].[DimAffiliate] Script Date: 01/12/2010 13:59:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimAffiliate](
[AffiliateKey] [int] NOT NULL,
[FullName] [nvarchar](250) NULL,
[State] [varchar](20) NULL,
[Zip] [varchar](20) NULL,
[Active] [tinyint] NULL,
[subids] [tinyint] NULL,
[cat1] [int] NULL,
[cat2] [int] NULL,
[cat3] [int] NULL,
[manager] [int] NULL,
CONSTRAINT [PK_Affiliates] PRIMARY KEY NONCLUSTERED
(
[AffiliateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FGDIM]
) ON [FGDIM]
GO
SET ANSI_PADDING OFF
GO
GO
/****** Object: Table [dbo].[DimAffiliateCategory] Script Date: 01/12/2010 13:58:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimAffiliateCategory](
[AffiliateCategoryKey] [int] IDENTITY(1,1) NOT NULL,
[AffiliateKey] [int] NOT NULL,
[CategoryKey] [int] NOT NULL,
CONSTRAINT [PK_DimAffiliateCategory] PRIMARY KEY CLUSTERED
(
[AffiliateCategoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FGDATA]
) ON [FGDATA]
GO
ALTER TABLE [dbo].[DimAffiliateCategory] WITH NOCHECK ADD CONSTRAINT [FK_DimAffiliateCategory_DimAffiliate] FOREIGN KEY([AffiliateKey])
REFERENCES [dbo].[DimAffiliate] ([AffiliateKey])
GO
ALTER TABLE [dbo].[DimAffiliateCategory] CHECK CONSTRAINT [FK_DimAffiliateCategory_DimAffiliate]
GO
ALTER TABLE [dbo].[DimAffiliateCategory] WITH NOCHECK ADD CONSTRAINT [FK_DimAffiliateCategory_DimCategory] FOREIGN KEY([CategoryKey])
REFERENCES [dbo].[DimCategory] ([CategoryKey])
GO
ALTER TABLE [dbo].[DimAffiliateCategory] CHECK CONSTRAINT [FK_DimAffiliateCategory_DimCategory]
GO
/****** Object: Table [dbo].[DimCategory] Script Date: 01/12/2010 13:59:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[DimCategory](
[CategoryKey] [int] NOT NULL,
[CategoryName] [varchar](50) NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FGDIM]
) ON [FGDIM]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[FactTransactions] Script Date: 01/12/2010 14:00:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactTransactions](
[AffiliateKey] [int] NOT NULL,
[Commision] [decimal](8, 3) NOT NULL,
[RefComm] [decimal](8, 3) NOT NULL,
[Cost] [money] NOT NULL,
[AffiliateCategoryKey] [int] NULL
) ON [FGDIM]
GO
ALTER TABLE [dbo].[FactTransactions] WITH NOCHECK ADD CONSTRAINT [FK_FactTransactions_DimAffiliate] FOREIGN KEY([AffiliateKey])
REFERENCES [dbo].[DimAffiliate] ([AffiliateKey])
GO
ALTER TABLE [dbo].[FactTransactions] CHECK CONSTRAINT [FK_FactTransactions_DimAffiliate]
GO
ALTER TABLE [dbo].[FactTransactions] WITH NOCHECK ADD CONSTRAINT [FK_FactTransactions_DimAffiliateCategory] FOREIGN KEY([AffiliateCategoryKey])
REFERENCES [dbo].[DimAffiliateCategory] ([AffiliateCategoryKey])
GO
ALTER TABLE [dbo].[FactTransactions] CHECK CONSTRAINT [FK_FactTransactions_DimAffiliateCategory]
GO
January 12, 2010 at 12:49 pm
by the way. The table sizes i'm dealing w in this instance are in the 10+ millions. My fact table at the moment has 15 million record and will grow substancially over time. Which is why i normalized the design hopeing to improve performance. Just not that familiar w/ molap so i dont know if i'm actually decreasing performance.
January 13, 2010 at 1:36 pm
disregard. there was an error in my design.
July 13, 2018 at 8:06 am
Can you share your solution?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply