New to DW and question about fact table

View previous topic View next topic Go down

New to DW and question about fact table

Post  VikashKarra on Mon Aug 20, 2012 2:30 pm

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

VikashKarra

Posts : 2
Join date : 2012-08-20

View user profile

Back to top Go down

Re: New to DW and question about fact table

Post  BoxesAndLines on Tue Aug 21, 2012 8:53 am

Create another fact table at the order grain.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: New to DW and question about fact table

Post  sgudavalli on Wed Aug 22, 2012 7:29 am


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 : 33
Location : Pune, India

View user profile

Back to top Go down

Thanks

Post  VikashKarra on Wed Aug 22, 2012 9:43 am

Thanks guys for the response - appreciate it!

VikashKarra

Posts : 2
Join date : 2012-08-20

View user profile

Back to top Go down

Re: New to DW and question about fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum