New to DW and question about fact table
3 posters
Page 1 of 1
New to DW and question about fact table
I hope someone can assist in this, as I am totally new to DW and looking at building a small data mart for the retail company that I work for:
Fact_Sales](
[TimeKey] [int] NULL,
[StoreKey] [int] NULL,
[ProductKey] [int] NULL,
[CustomerKey] [int] NULL,
[Quantity_Sold] [int](100) NULL,
[Unit_Price] [money] NULL,
[Unit_Cost] [money] NULL,
[Revenue] [money] NULL,
[Gross_Profit] [money] NULL
[dbo].[Dim_Customer](
[CustomerKey] [int] NOT NULL,
[Customer_Number] [varchar](4) NOT NULL,
[Customer_Name] [varchar](4) NULL,
[Customer_Address] [varchar](200) NULL,
[Customer_City] [varchar](100) NULL,
[Customer_State] [varchar](20) NULL,
[Customer_Country] [varchar](10) NULL,
[Customer_SIC] [varchar](40) NULL,
[Customer_SIC_Description] [varchar](100) NULL,
[Customer_Tier] [int] NULL,
[Customer_Age] [int] NULL,
[Customer_Type] [varchar](50) NOT NULL
[dbo].[Dim_Product](
[PartKey] [int] NOT NULL,
[Part_Number] [varchar](4) NOT NULL,
[MCC_Part_Number] [varchar](4) NOT NULL,
[Part_Type] [varchar](200) NULL,
[Part_Type_Description] [varchar](100) NULL,
[Retail_Price] [money] NOT NULL
) ON [PRIMARY]
[dbo].[Dim_Store](
[StoreKey] [int] NOT NULL,
[Store_Number] [varchar](4) NOT NULL,
[Store_YCode] [varchar](4) NOT NULL,
[Store_Name] [varchar](60) NOT NULL,
[Store_Close_Date] [datetime] NOT NULL
) ON [PRIMARY]
We also have a TIME Dimension as well. As you can see, our dimension tables are (Time,Product, Store, and Customer).
We have 1 fact table that has (timekey,productkey,customerkey,and storekey as its PrimaryKey),
and quantity_sold,unit_price,unit_cost,revenue,and gross_profit as its additive facts. If we want to bring in OrderCount (given the fact that right now, our level of granularity is by day-store-customer-product), how would we do so? The only thing I can think of is by bringing in the actual Order # and thus, making that our lowest level of granularity, but by doing so it seems that we have an OLTP really. Like i said, this maybe a stupid ?, and I do appreciate any feedback for a newbie! Thanks
Fact_Sales](
[TimeKey] [int] NULL,
[StoreKey] [int] NULL,
[ProductKey] [int] NULL,
[CustomerKey] [int] NULL,
[Quantity_Sold] [int](100) NULL,
[Unit_Price] [money] NULL,
[Unit_Cost] [money] NULL,
[Revenue] [money] NULL,
[Gross_Profit] [money] NULL
[dbo].[Dim_Customer](
[CustomerKey] [int] NOT NULL,
[Customer_Number] [varchar](4) NOT NULL,
[Customer_Name] [varchar](4) NULL,
[Customer_Address] [varchar](200) NULL,
[Customer_City] [varchar](100) NULL,
[Customer_State] [varchar](20) NULL,
[Customer_Country] [varchar](10) NULL,
[Customer_SIC] [varchar](40) NULL,
[Customer_SIC_Description] [varchar](100) NULL,
[Customer_Tier] [int] NULL,
[Customer_Age] [int] NULL,
[Customer_Type] [varchar](50) NOT NULL
[dbo].[Dim_Product](
[PartKey] [int] NOT NULL,
[Part_Number] [varchar](4) NOT NULL,
[MCC_Part_Number] [varchar](4) NOT NULL,
[Part_Type] [varchar](200) NULL,
[Part_Type_Description] [varchar](100) NULL,
[Retail_Price] [money] NOT NULL
) ON [PRIMARY]
[dbo].[Dim_Store](
[StoreKey] [int] NOT NULL,
[Store_Number] [varchar](4) NOT NULL,
[Store_YCode] [varchar](4) NOT NULL,
[Store_Name] [varchar](60) NOT NULL,
[Store_Close_Date] [datetime] NOT NULL
) ON [PRIMARY]
We also have a TIME Dimension as well. As you can see, our dimension tables are (Time,Product, Store, and Customer).
We have 1 fact table that has (timekey,productkey,customerkey,and storekey as its PrimaryKey),
and quantity_sold,unit_price,unit_cost,revenue,and gross_profit as its additive facts. If we want to bring in OrderCount (given the fact that right now, our level of granularity is by day-store-customer-product), how would we do so? The only thing I can think of is by bringing in the actual Order # and thus, making that our lowest level of granularity, but by doing so it seems that we have an OLTP really. Like i said, this maybe a stupid ?, and I do appreciate any feedback for a newbie! Thanks
VikashKarra- Posts : 2
Join date : 2012-08-20
Re: New to DW and question about fact table
Create another fact table at the order grain.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: New to DW and question about fact table
i m with the below reply...
i dont think grain of Order is same as Sales.. create a seperate fact for the Orders
sgudavalli- Posts : 29
Join date : 2010-06-10
Age : 40
Location : Pune, India
Similar topics
» Question for count in fact table
» Newbie's question regarding FACT table
» Multiple measures in a fact table- modelling question
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Fact Table Foreign Key reference question
» Newbie's question regarding FACT table
» Multiple measures in a fact table- modelling question
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Fact Table Foreign Key reference question
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum