Saturday January 20th 2018

Categories

Insider

Archives

Process From OLTP datamodel to Olap DataModel

This present s an example showing the process to denormalization for dataware house or data mart
Star schema. We researched on this topic in various books and even Microsoft examples they only give sample, but we think it is more important to know how oltp data model is converted in Star schema or olap.
We used Microsoft’s Northwind data model in oltp datamodel, created a query to populate our fact table with measure data i.e unit price quantity and discount alongwith foriegn keys from the related dimensions.
Query code used in DTS with SQL Server 2000 to create new table in Northwind_fact table.
You will notice in fact table that there foreign keys for the dimension tables like Employee, product, region and suppliers. The above picture shows design of OLTP Vrs OLAP. We will eloberat more on this topic from our search and experiance but will appreciate to correct us or add more to make it easier to understand as now days in many job interviews question like data warehousing, datamodeling cocepts asked and so more we discuss the topic more we will be well equiped to tacle the questions as well help for newcommers.
——————————————————————————————————
SELECT dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount,
dbo.Orders.CustomerID, dbo.Orders.EmployeeID, dbo.Orders.OrderDate, dbo.Orders.Freight, dbo.EmployeeTerritories.TerritoryID,
dbo.Territories.RegionID, dbo.Shippers.ShipperID, dbo.Categories.CategoryID, dbo.CustomerCustomerDemo.CustomerTypeID,
dbo.Suppliers.SupplierID
FROM dbo.Employees FULL OUTER JOIN
dbo.EmployeeTerritories FULL OUTER JOIN
dbo.Region FULL OUTER JOIN
dbo.Territories ON dbo.Region.RegionID = dbo.Territories.RegionID ON dbo.EmployeeTerritories.TerritoryID = dbo.Territories.TerritoryID ON
dbo.Employees.EmployeeID = dbo.EmployeeTerritories.EmployeeID FULL OUTER JOIN
dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID FULL OUTER JOIN
dbo.CustomerCustomerDemo ON dbo.Customers.CustomerID = dbo.CustomerCustomerDemo.CustomerID FULL OUTER JOIN
dbo.CustomerDemographics ON dbo.CustomerCustomerDemo.CustomerTypeID = dbo.CustomerDemographics.CustomerTypeID FULL OUTER JOIN
dbo.Shippers ON dbo.Orders.ShipVia = dbo.Shippers.ShipperID ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID FULL OUTER JOIN
dbo.Categories FULL OUTER JOIN
dbo.[Order Details] FULL OUTER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID FULL OUTER JOIN
dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID ON dbo.Categories.CategoryID = dbo.Products.CategoryID ON
dbo.Orders.OrderID = dbo.[Order Details].OrderID