0mkF52FX

1IF EXISTS (SELECT * FROM "dwbi_cars".INFORMATION_SCHEMA.TABLES
2    WHERE TABLE_SCHEMA = N'dbo'  AND TABLE_NAME = N'fact_table')
3BEGIN
4DROP TABLE [fact_table]
5END
6GO
7
8CREATE TABLE [dbo].[fact_table]
9
10( 
11 [CAR_ID] int NOT NULL
12,[VEHICLE_RECORD_ID] int NOT NULL
13,[USER_ID] int NOT NULL
14,[YEAR] int NOT NULL
15,[CAR_MAKE] [nvarchar](50) NOT NULL
16,[CAR_MODEL] [nvarchar](100) NOT NULL
17,[DRIVE_TYPE] [nvarchar](50) NOT NULL
18,[EUROPE_STANDARD] int NOT NULL
19,[ENGINE_CC] int NOT NULL
20,[GAS_TYPE] [nvarchar](50) NOT NULL
21,[NOISE_LEVEL] [float] NOT NULL
22,[CO2] int NOT NULL
23,[NOX] int NOT NULL
24,[FUEL_COST_6000_MILES] int NOT NULL
25)
26USE[dwbi_cars]
27
28GO
29
30TRUNCATE TABLE [dbo].[fact_table]
31
32 GO
33
34INSERT INTO [dbo].[fact_table]
35
36      (a.[CAR_ID]
37      ,[VEHICLE_RECORD_ID]
38      ,[USER_ID]
39      ,a.[YEAR]
40      ,a.[CAR_MAKE]
41      ,a.[CAR_MODEL]
42      ,[DRIVE_TYPE]
43      ,a.[EUROPE_STANDARD]
44      ,a.[ENGINE_CC]
45      ,a.[GAS_TYPE]
46      ,a.[NOISE_LEVEL]
47      ,a.[CO2]
48      ,a.[NOX]
49      ,a.[FUEL_COST_6000_MILES])
50
51select a.[CAR_ID]
52      ,[VEHICLE_RECORD_ID]
53      ,[USER_ID]
54      ,a.[YEAR]
55      ,a.[CAR_MAKE]
56      ,a.[CAR_MODEL]
57      ,[DRIVE_TYPE]
58      ,a.[EUROPE_STANDARD]
59      ,a.[ENGINE_CC]
60      ,a.[GAS_TYPE]
61      ,a.[NOISE_LEVEL]
62      ,a.[CO2]
63      ,a.[NOX]
64      ,a.[FUEL_COST_6000_MILES]
65
66from Raw_cardetails d
67INNER JOIN Dimcardetails a ON a.CAR_ID = d.CAR_ID
68inner join Dimfuel  f on f.VEHICLE_MODEL = a.CAR_MODEL
69INNER join Dimmileage m on m.AUTOMOBILE_MODEL = a.CAR_MODEL