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.
- Should i have one Fact table or multiple Fact tables for the above example?
- 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
etc..
Any help is appreciated. Thanks in advance.