Data Warehouse script

I figured out what the problem was from class. The elements in the fact table were not in the same granularity. Particularly. employees and Customers were a problem. We could incorporate the customers if we went a step up to the general service table, rather than service details. We could add another fact table to handle this, but since most of the calculable values are at the detail level, I focused the fact table on that.

Here is the ERD

Here is the script

use master
Go
/************************************************
* Check to see if the database exists.
* If it does drop it and then recreate it.
************************************************/
if exists
 (Select name from sys.Databases 
   where name='AutomartDataWarehouse')
begin
 Drop database AutomartDataWarehouse
end
go
Create database AutomartDataWareHouse
Go
Use AutomartDataWareHouse
/*******************************************
* Create the Dimension tables.
********************************************/
Go


Go
Create table DimVehicle
(
   DimVehicleKey int identity(1,1) primary Key,
   VehicleID int,
   personKey int,
   LicenseNumber nvarchar(10),
   VehicleMake nvarchar(255),
   VehicleYEar nchar(4)
)
Go
Create table DimLocation
(
  DimLocationKey int identity(1,1) primary key,
  LocationID int,
  locationName nvarchar(255),
  LocationAddress nvarchar(255),
  LocationCity nvarchar(255),
  LocationState nchar(2),
  LocationZip nchar(10),
  LocationPhone nchar(13)
)

Go
Create Table DimService
(
 DimServiceKey int identity(1,1) primary Key,
 serviceId int,
 ServiceName nvarchar(255)
)
go
Create table DimDate
(
  DimDateKey int identity(1,1) primary key,
  ServiceDate Date,
  ServiceYear int,
  ServiceMonth int
)

/***********************************************
* Create the fact tables. The failure of the previous
* fact table was that it was of two different
* granularities that couldn't be resolved. To solve this
* I Left out the customer and employee dims. A different
* fact table might include them.
*************************************************/
Create table FactService
(
   FactServiceKey int identity(1,1),
      DimVehicleKey int Foreign Key references DimVehicle(DimVehicleKey),
   DimLocationKey int Foreign Key references DimLocation(DimLocationKey),
   DimDateKey int Foreign Key references DimDate(DimDateKey),
   DimServiceKey int Foreign Key references DimService(DimServiceKey),
  
   Constraint PK_FactService primary key(FactServiceKey,DimVehicleKey, DimLocationKey, DimDateKey, dimServiceKey),
   ServicePrice money,
   DiscountPercent decimal(3,2),
   TaxPercent decimal(3,2)
)

Go


/*****************************************************
* Populate the dim tables
******************************************************/


Go
Insert into DimVehicle(VehicleID,
   LicenseNumber,
   VehicleMake,
   VehicleYEar,
   personKey)
   Select VehicleID,
   LicenseNumber,
   VehicleMake,
   VehicleYEar, personKey
   From Automart.Customer.Vehicle
 
 Go
 Insert into DimLocation (
  LocationID,
  locationName,
  LocationAddress,
  LocationCity,
  LocationState,
  LocationZip,
  LocationPhone
 )
 Select 
 LocationID,
  locationName,
  LocationAddress,
  LocationCity,
  LocationState,
  LocationZip,
  LocationPhone
  From Automart.Customer.Location

  Go
  Insert into DimService(
  serviceId,
   ServiceName 
  )
  Select AutoServiceID, ServiceName
  from Automart.customer.AutoService

Go

Insert into DimDate(
 ServiceDate,
  ServiceYear,
  ServiceMonth)
 Select 
 ServiceDate,
  Year(ServiceDate),
  Month(ServiceDate)
  From Automart.Employee.VehicleService
  Go

  /***************************************
  * Insert into the fact table
  ****************************************/

  Insert into FactService(DimLocationKey, DimDateKey, DimServiceKey, DimVehicleKey, ServicePrice, DiscountPercent, TaxPercent)
  Select DimLocationKey, DimDateKey, DimServiceKey, DimVehicleKey, ServicePrice, DiscountPercent, TaxPercent
  from DimLocation dl
  inner Join Automart.Customer.Location  loc
  on dl.LocationID=loc.LocationID
  inner join Automart.Employee.VehicleService vs
  on loc.LocationID=dl.LocationID
  inner Join DimVehicle dv
  on vs.VehicleID=dv.VehicleID
  inner Join DimDate dd
  on dd.ServiceDate=vs.ServiceDate
  inner join Automart.Employee.VehicleServiceDetail vsd
  on vs.VehicleServiceID=vsd.VehicleServiceID
  inner Join DimService ds
  on ds.serviceId=vsd.VehicleServiceID
  inner join Automart.Customer.AutoService a
  on vsd.AutoServiceID=a.AutoServiceID

We will follow by creating a SSIS script to import data on a regular basis and creating a cube