declare @dbName nvarchar(MAX);
set @dbName = 'DATAWAREHOUSE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database
execute ('CREATE DATABASE ' + @dbName);
execute ('use ' + @dbName + '; ' +
'create table dbo.IC_D_ROUTE (
ID varchar(255) not null,
CODE varchar(255),
EXTERNAL_ID varchar(255),
ROUTE_DATE date,
DOMAIN_CODE varchar(255),
LOGISTICS_STATUS varchar(255),
TRUCK_CODE varchar(255),
DRIVER_CODE varchar(255),
TRAILER_CODE varchar(255),
PROVIDER_CODE varchar(255),
INVOLVED_PROVIDER_CODE varchar(255),
VALORIZED_PROVIDER_CODE varchar(255),
primary key(ID)
);
create table dbo.IC_M_ROUTE (
ID_ROUTE varchar(255) not null,
TOTAL_DISTANCE_PREDICTED numeric(19,6),
TOTAL_DISTANCE numeric(19,6),
NOMINAL_DIFF_TOTAL_DISTANCE numeric(19,6),
RELATIVE_DIFF_TOTAL_DISTANCE numeric(19,6),
TOTAL_TIME_PREDICTED numeric(19,6),
TOTAL_TIME numeric(19,6),
NOMINAL_DIFF_TOTAL_TIME numeric(19,6),
RELATIVE_DIFF_TOTAL_TIME numeric(19,6),
UV_DELIVERED_PREDICTED numeric(19,6),
UV_DELIVERED numeric(19,6),
NOMINAL_DIFF_UV_DELIVERED numeric(19,6),
RELATIVE_DIFF_UV_DELIVERED numeric(19,6),
UP_DELIVERED_PREDICTED numeric(19,6),
UP_DELIVERED numeric(19,6),
NOMINAL_DIFF_UP_DELIVERED numeric(19,6),
RELATIVE_DIFF_UP_DELIVERED numeric(19,6),
UQ_DELIVERED_PREDICTED numeric(19,6),
UQ_DELIVERED numeric(19,6),
NOMINAL_DIFF_UQ_DELIVERED numeric(19,6),
RELATIVE_DIFF_UQ_DELIVERED numeric(19,6),
UM_DELIVERED_PREDICTED numeric(19,6),
UM_DELIVERED numeric(19,6),
NOMINAL_DIFF_UM_DELIVERED numeric(19,6),
RELATIVE_DIFF_UM_DELIVERED numeric(19,6),
UV_COLLECTED_PREDICTED numeric(19,6),
UV_COLLECTED numeric(19,6),
NOMINAL_DIFF_UV_COLLECTED numeric(19,6),
RELATIVE_DIFF_UV_COLLECTED numeric(19,6),
UP_COLLECTED_PREDICTED numeric(19,6),
UP_COLLECTED numeric(19,6),
NOMINAL_DIFF_UP_COLLECTED numeric(19,6),
RELATIVE_DIFF_UP_COLLECTED numeric(19,6),
UQ_COLLECTED_PREDICTED numeric(19,6),
UQ_COLLECTED numeric(19,6),
NOMINAL_DIFF_UQ_COLLECTED numeric(19,6),
RELATIVE_DIFF_UQ_COLLECTED numeric(19,6),
UM_COLLECTED_PREDICTED numeric(19,6),
UM_COLLECTED numeric(19,6),
NOMINAL_DIFF_UM_COLLECTED numeric(19,6),
RELATIVE_DIFF_UM_COLLECTED numeric(19,6),
START_DATE_PREDICTED datetime,
START_DATE datetime,
NOMINAL_DIFF_START_DATE int,
ENDEND_DATE_PREDICTED datetime,
END_DATE datetime,
NOMINAL_DIFF_END_DATE int,
FILLRATE_UV numeric(19,6),
FILLRATE_UV_PREDICTED numeric(19,6),
NOMINAL_DIFF_FILLRATE_UV numeric(19,6),
RELATIVE_DIFF_FILLRATE_UV numeric(19,6),
FILLRATE_UP numeric(19,6),
FILLRATE_UP_PREDICTED numeric(19,6),
NOMINAL_DIFF_FILLRATE_UP numeric(19,6),
RELATIVE_DIFF_FILLRATE_UP numeric(19,6),
#SITES_DELIVERED int,
#SITES_DELIVERED_PREDICTED int,
NOMINAL_DIFF_#SITES_DELIVERED int,
RELATIVE_DIFF_#SITES_DELIVERED numeric(19,6),
#SITES_COLLECTED int,
#SITES_COLLECTED_PREDICTED int,
NOMINAL_DIFF_#SITES_COLLECTED int,
RELATIVE_DIFF_#SITES_COLLECTED numeric(19,6),
#OPERATIONS_DELIVERED int,
#OPERATIONS_DELIVERED_PREDICTED int,
NOMINAL_DIFF_#OPERATIONS_DELIVERED int,
RELATIVE_DIFF_#OPERATIONS_DELIVERED numeric(19,6),
#OPERATIONS_COLLECTED int,
#OPERATIONS_COLLECTED_PREDICTED int,
NOMINAL_DIFF_#OPERATIONS_COLLECTED int,
RELATIVE_DIFF_#OPERATIONS_COLLECTED numeric(19,6),
TRANSPORTATION_COST_PREDICTED numeric(19,6),
TRANSPORTATION_COST numeric(19,6),
NOMINALNOMINAL_DIFF_TRANSPORTATION_COST numeric(19,6),
RELATIVE_DIFF_TRANSPORTATION_COST numeric(19,6),
RATIO_UVKM_PREDICTED numeric(19,6),
RATIO_UVKM numeric(19,6),
NOMINAL_DIFF_RATIO_UVKM numeric(19,6),
RELATIVE_DIFF_RATIO_UVKM numeric(19,6),
RATIO_UPKM_PREDICTED numeric(19,6),
RATIO_UPKM numeric(19,6),
NOMINAL_DIFF_RATIO_UPKM numeric(19,6),
RELATIVE_DIFF_RATIO_UPKM numeric(19,6),
RATIO_KMUV_PREDICTED numeric(19,6),
RATIO_KMUV numeric(19,6),
NOMINAL_DIFF_RATIO_KMUV numeric(19,6),
RELATIVE_DIFF_RATIO_KMUV numeric(19,6),
RATIO_KMUP_PREDICTED numeric(19,6),
RATIO_KMUP numeric(19,6),
NOMINAL_DIFF_RATIO_KMUP numeric(19,6),
RELATIVE_DIFF_RATIO_KMUP numeric(19,6),
RATIO_COSTUP numeric(19,6),
RATIO_COSTUP_PREDICTED numeric(19,6),
NOMINAL_DIFF_RATIO_COSTUP numeric(19,6),
RELATIVE_DIFF_RATIO_COSTUP numeric(19,6),
RATIO_COSTUV numeric(19,6),
RATIO_COSTUV_PREDICTED numeric(19,6),
NOMINAL_DIFF_RATIO_COSTUV numeric(19,6),
RELATIVE_DIFF_RATIO_COSTUV numeric(19,6),
RATIO_COSTKM numeric(19,6),
RATIO_COSTKM_PREDICTED numeric(19,6),
NOMINAL_DIFF_RATIO_COSTKM numeric(19,6),
RELATIVE_DIFF_RATIO_COSTKM numeric(19,6),
primary key(ID_ROUTE)
);
create table dbo.IC_D_SEQUENCE (
ID_ROUTE varchar(255) not null,
ACTIVITY_CODE varchar(255) not null,
SITE_CODE varchar(255) not null,
PASSAGE_INDEX int not null,
ROUTE_ORDER int,
STARTING_SITE_CODE varchar(255),
primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX)
);
create table dbo.IC_D_OPERATION (
ID varchar(255) not null,
DIVISION_INDEX int not null,
ID_ROUTE varchar(255),
CLIENT_SITE_CODE varchar(255),
DEPOSIT_SITE_CODE varchar(255),
PRODUCT_CODE varchar(255),
PACKAGING_CODE varchar(255),
CLIENT_SEQ_ACTIVITY_CODE varchar(255),
CLIENT_SEQ_SITE_CODE varchar(255),
CLIENT_SEQ_PASSAGE_INDEX int,
DEPOSIT_SEQ_ACTIVITY_CODE varchar(255),
DEPOSIT_SEQ_SITE_CODE varchar(255),
DEPOSIT_SEQ_PASSAGE_INDEX int,
primary key(ID, DIVISION_INDEX)
);
create table dbo.IC_M_SEQUENCE (
ID_ROUTE varchar(255) not null,
ACTIVITYACTIVITY_CODE varchar(255) not null,
SITE_CODE varchar(255) not null,
PASSAGE_INDEX int not null,
TOTAL_TIME_PREDICTED int,
TOTAL_TIME int,
NOMINAL_DIFF_TOTAL_TIME int,
RELATIVE_DIFF_TOTAL_TIME numeric(19,6),
UV_PREDICTED numeric(19,6),
UV numeric(19,6),
NOMINAL_DIFF_UV numeric(19,6),
RELATIVE_DIFF_UV numeric(19,6),
UP_PREDICTED numeric(19,6),
UP numeric(19,6),
NOMINAL_DIFF_UP numeric(19,6),
RELATIVE_DIFF_UP numeric(19,6),
UQ_PREDICTED numeric(19,6),
UQ numeric(19,6),
NOMINAL_DIFF_UQ numeric(19,6),
RELATIVE_DIFF_UQ numeric(19,6),
UM_PREDICTED numeric(19,6),
UM numeric(19,6),
NOMINAL_DIFF_UM numeric(19,6),
RELATIVE_DIFF_UM numeric(19,6),
START_DATE_PREDICTED datetime,
START_DATE datetime,
NOMINAL_DIFF_START_DATE int,
END_DATE_PREDICTED datetime,
END_DATE datetime,
NOMINAL_DIFF_END_DATE int,
primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX)
);
create table dbo.IC_M_OPERATION (
ID_OPERATION varchar(255) not null,
DIVISION_INDEX int not null,
UV_PREDICTED numeric(19,6),
UV_INITIAL numeric(19,6),
UV_REALISED numeric(19,6),
NOMINAL_DIFF_UV numeric(19,6),
RELATIVE_DIFF_UV numeric(19,6),
UP_PREDICTED numeric(19,6),
UP_INITIAL numeric(19,6),
UP_REALISED numeric(19,6),
NOMINAL_DIFF_UP numeric(19,6),
RELATIVE_DIFF_UP numeric(19,6),
UQ_PREDICTED numeric(19,6),
UQ_INITIAL numeric(19,6),
UQ_REALISED numeric(19,6),
NOMINAL_DIFF_UQ numeric(19,6),
RELATIVE_DIFF_UQ numeric(19,6),
UM_PREDICTED numeric(19,6),
UM_INITIAL numeric(19,6),
UM_REALISED numeric(19,6),
NOMINAL_DIFF_UM numeric(19,6),
RELATIVE_DIFF_UM numeric(19,6),
primary key(ID_OPERATION, DIVISION_INDEX)
);
create table dbo.IC_D_LOGISTIC_AREA (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
create table dbo.IC_D_SITE (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
create table dbo.IC_D_PRODUCT (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
create table dbo.IC_D_PROVIDER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
create table dbo.IC_D_TRUCK (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
create table dbo.IC_D_DRIVER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
create table dbo.IC_D_TRAILER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);
CREATE TABLE dbo.IC_D_ROUTE_INVOLVED_PROVIDERS (
ID_ROUTE VARCHAR(255) NOT NULL,
ID_PROVIDER VARCHAR(255) NOT NULL,
PRIMARY KEY(ID_ROUTE, ID_PROVIDER)
);
CREATE TABLE dbo.IC_D_ROUTE_VALORIZED_PROVIDERS (
ID_ROUTE VARCHAR(255) NOT NULL,
ID_PROVIDER VARCHAR(255) NOT NULL,
PRIMARY KEY(ID_ROUTE, ID_PROVIDER)
);
CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''',
DEFAULT_DATABASE=[' + @dbName + '],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
ALTER LOGIN [' + @dbName + '] ENABLE
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + ']
ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + ']
CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + '
ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember ''db_owner'', ''' + @dbName + '''
USE [master]
GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa
RECONFIGURE ;
USE [' + @dbName + ']
ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + ']
RECONFIGURE ;
');
|
