New to DW and question about fact table

[VikashKarra]

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

[BoxesAndLines]

Create another fact table at the order grain.

[sgudavalli]

i m with the below reply...
i dont think grain of Order is same as Sales.. create a seperate fact for the Orders

[VikashKarra]

Thanks guys for the response - appreciate it!

[Sponsored content]