Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

New to DW and question about fact table

3 posters

Go down

New to DW and question about fact table Empty New to DW and question about fact table

Post  VikashKarra 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

Back to top Go down

New to DW and question about fact table Empty Re: New to DW and question about fact table

Post  BoxesAndLines Tue Aug 21, 2012 8:53 am

Create another fact table at the order grain.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

New to DW and question about fact table Empty Re: New to DW and question about fact table

Post  sgudavalli 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 : 38
Location : Pune, India

Back to top Go down

New to DW and question about fact table Empty Thanks

Post  VikashKarra Wed Aug 22, 2012 9:43 am

Thanks guys for the response - appreciate it!

VikashKarra

Posts : 2
Join date : 2012-08-20

Back to top Go down

New to DW and question about fact table Empty Re: New to DW and question about fact table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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