SQLTeam.com | Weblogs | Forums

Designing a Fact Table for SSAS Multidimensional Cube


Hi. I am a starter in the dimensional warehouse world and i have a question on the design and loading of a Fact table.

Example: I have an OLTP DB with an orders table as following

And i have customer and products tables. Now my task is to create and populate dimensional DW with following measures and dimensions

Facts: TotalOrders,TotalProducts,TotalCustomers,TotalSales
Dimensions : Product and Customer

Dimensions are OK but my confusion is all around Fact Table.

  1. Should i have one Fact table or multiple Fact tables for the above example?
  2. If it is one fact table then how do i load the fact tables with data at multiple angularities?

following is a SQL query to crate my fact table

CREATE TABLE [dbo].[Fact_Orders] (
[FactKey] [int] IDENTITY(1,1) NOT NULL,
[Key_OrderID] [int] NOT NULL,
[Key_CustomerID] [int] NOT NULL,
[Key_ProductID] [int] NOT NULL,
[TotalsOrders] [int] NOT NULL,
[TotalCustomers] [int] NOT NULL,
[TotalProducts] [int] NOT NULL,
[TotalSales] [int] NOT NULL )

Now, what should my SQL query look like to load data into the fact table? assuming 2 dimension tables are already loaded and granularity of the Fact table is orders.

Ultimately, once the dimensional warehouse is built we are going to build an SSAS cube on top of it. so if users connect to our cube using excel and drag and drop measures the results for example orders table should be as following

TotalCustomers= 3 (that is the distinct number of customers)
TotalOrders = 5
TotalProducts = 3
TotalProducts by Customer = 100 - 2 (1,2,1 so distinct products ordered is 2), 200 -1, 300 -1

Any help is appreciated. Thanks in advance.