I have used another dbo.worldwideimporters to insert data into a new table.
even though commands was succesfull only the columns shows. I dont see the data(rows)
here is the full script.
use master
go
Create Database WWI_DW
use WWI_DW
go
Create Schema Star
go
Create table DimDate
(
[DateID] Bigint primary key,
[Date] date,
[WeekDayName] varchar(20),
[MonthName] varchar(20),
[Year] smallint,
[Month] tinyint,
[WeekDay] tinyint,
[Week] tinyint,
[Day] tinyint,
[Quarter] tinyint,
[QuarterName] char(2),
)
go
Create proc usp_FilldimDate (@startDate datetime2, @NOYears int)
As
Begin
set language swedish
declare @datetoadd datetime2
declare @EndDate datetime2
declare @cnt int --kontroll om variabel finns
--värde till variblerna
set @EndDate = Dateadd(year, @NOYears, @startDate)
set @datetoadd = @startDate
--loop
While @datetoadd < @EndDate
begin
-- kontrollera om datum finns, 1 = datum finns, 0 = datum finns ej
select @cnt = count(*) from DimDate
where [DateID] = cast(convert(char(10), @datetoadd, 112) as int)
if @cnt = 0 --datum finns ej, lägg upp en
begin
insert into DimDate
select
cast(convert(char (10), @datetoadd, 120)as bigint) as [DateID] --id
, cast(convert(char(10), @datetoadd, 120) as Date) as [Date] --YY-MM-DD
, Datename(weekday, @datetoadd) as [WeekDayName] --Sve Tisdag
, Datename(MM, @datetoadd) as [MonthName] --Sve Mars
, datepart(year, @DateToAdd) as [Year]
, datepart(MONTH, @DateToAdd) as [Month] --Month Number
, datepart(weekday, @datetoadd) as [Weekday] --Weekday Number
, datepart(week, @datetoadd) as [Week] --Week Number
, datepart(day, @datetoadd) as [Day] -- Day Number
, datepart(quarter, @datetoadd) as [Quarter] -- Quarter Number
,'Q' + cast(datename(quarter, @DateToAdd) as char(2)) as [QuarterName] -- Eng Q
end
set language us_english
end
set @DateToAdd = dateadd(dd, 1, @DateToAdd)
end
exec usp_FilldimDate '2013-01-01', 1 --select min(orderdate) from WideWorldImporters.Sales.Orders
go
Create Table DimCustomer --skapar tabell
(
CustomerID int primary key,
CustomerName varchar (255),
CustomerCategoryName varchar (255),
)
go
Create proc usp_FillDimCustomer ---lägger in data
as begin
Insert into DimCustomer (CustomerID, CustomerName, CustomerCategoryName)
select
c.CustomerID as [CustomerID],
left(c.CustomerName,100) as [CustomerName],
left(u.CustomerCategoryName,50) as [CustomerCategoryName]
from WideWorldImporters.Sales.Customers as c,
WideWorldImporters.Sales.CustomerCategories as u
end
exec usp_FillDimCustomer
go
Create Table DimSalesPerson
(
SalesPersonID int primary key,
Lastname varchar (50),
FullName varchar (100),
)
go
create proc usp_FillDimSalesPerson
as begin
insert into DimSalesPerson
select
s.SalespersonPersonID as [SalespersonPersonID],
left(p.Fullname, 100) as [FullName],
SUBSTRING(p.FullName, CHARINDEX(' ', p.FullName) + 1, LEN(p.FullName)) AS [LastName]
from WideWorldImporters.Sales.Orders as s,
WideWorldImporters.Application.People as p
end
exec usp_FillDimSalesPerson
go
Create table DimProduct
(
StockItemID int primary key,
SupplierName varchar (100),
ProductName varchar (100)
)
go
Create proc usp_FillDimProduct
as begin
insert into DimProduct
Select
b.StockItemID as [StockItemID],
b.StockItemName as [ProductName],
d.SupplierName as [SupplierName]
from WideWorldImporters.Warehouse.StockItems as b,
WideWorldImporters.Purchasing.Suppliers as d
end
exec usp_FillDimProduct
go
Create Table SalesFact
(
ID int identity(1,1) primary key,
CustomerID int not null references DimCustomer(CustomerID),
SalesPersonID int not null references DimSalesPerson(SalesPersonID),
StockItemID int not null references DimProduct(StockItemID),
OrderDateID bigint not null references DimDate(DateID),
Quantity int not null,
UnitPrice decimal(10,2) not null,
Sales decimal(10,2) not null
)
go
create proc usp_FillSalesFact
as begin
insert into SalesFact
select
cast(replace(convert(varchar(10), o.OrderDate, 120), '-', ' ')as bigint) as OrderDateID,
o.CustomerID as [CustomerID],
o.SalespersonPersonID as [SalesPersonID],
t.StockItemID as [StockItemID],
q.Quantity as [Quantity],
q.UnitPrice as [UnitPrice],
q.UnitPrice * q.Quantity as [Sales]
from WideWorldImporters.Sales.Orders as o,
WideWorldImporters.Warehouse.StockItems as t,
WideWorldImporters.Sales.OrderLines as q
end
exec usp_FillSalesFact
go