-- GuilleSQL - Un portal sobre Microsoft SQL Server en Castellano
-- https://guillesql.es
--
CREATE TABLE [dbo].[STG_IISLog](
	[ServerName] [varchar](250) NULL,
	[date] [varchar](50) NULL,
	[time] [varchar](50) NULL,
	[SiteName] [varchar](50) NULL,
	[ServerIP] [varchar](50) NULL,
	[Method] [varchar](50) NULL,
	[URIstem] [varchar](1024) NULL,
	[URIquery] [varchar](2048) NULL,
	[Port] [varchar](50) NULL,
	[ClientUserName] [varchar](250) NULL,
	[ClientIP] [varchar](50) NULL,
	[ClientUserAgent] [varchar](1024) NULL,
	[Status] [varchar](50) NULL,
	[SubStatus] [varchar](50) NULL,
	[Win32Status] [varchar](1024) NULL
)
GOCREATE TABLE [dbo].[DW_DIM_URIstem](
	[URIstem_ID] [int] IDENTITY(1,1) NOT NULL,
	[URIstem] [varchar](1024) NOT NULL,
   CONSTRAINT [PK_DW_DIM_URIstem] PRIMARY KEY CLUSTERED 
   ([URIstem_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_URIquery](
	[URIquery_ID] [int] IDENTITY(1,1) NOT NULL,
	[URIquery] [varchar](2048) NOT NULL,
   CONSTRAINT [PK_DW_DIM_URIquery] PRIMARY KEY CLUSTERED 
   ([URIquery_ID] ASC)
) 
GO
CREATE TABLE [dbo].[DW_DIM_SiteName](
	[SiteName_ID] [tinyint] IDENTITY(1,1) NOT NULL,
	[SiteName] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_SiteName] PRIMARY KEY CLUSTERED 
   ([SiteName_ID] ASC)
) 
GO
CREATE TABLE [dbo].[DW_DIM_ServerName](
	[ServerName_ID] [tinyint] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](250) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ServerName] PRIMARY KEY CLUSTERED 
   ([ServerName_ID] ASC)
) 
GO
CREATE TABLE [dbo].[DW_DIM_ServerIP](
	[ServerIP_ID] [smallint] IDENTITY(1,1) NOT NULL,
	[ServerIP] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ServerIP] PRIMARY KEY CLUSTERED 
   ([ServerIP_ID] ASC)
) 
GO
CREATE TABLE [dbo].[DW_DIM_Method](
	[Method_ID] [tinyint] IDENTITY(1,1) NOT NULL,
	[Method] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_Method] PRIMARY KEY CLUSTERED 
  ([Method_ID] ASC)
) 
GO
CREATE TABLE [dbo].[DW_DIM_ClientUSerName](
	[ClientUserName_ID] [int] IDENTITY(1,1) NOT NULL,
	[ClientUserName] [varchar](250) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ClientUSerName] PRIMARY KEY CLUSTERED 
   ([ClientUserName_ID] ASC)
) 
GO
CREATE TABLE [dbo].[DW_DIM_ClientIP](
	[ClientIP_ID] [int] IDENTITY(1,1) NOT NULL,
	[ClientIP] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ClientIP] PRIMARY KEY CLUSTERED 
   ([ClientIP_ID] ASC)
) 
GOCREATE TABLE [dbo].[DW_TH_IISLog](
	[ServerName_ID] [tinyint] NOT NULL,
	[DateTimeUTC] [smalldatetime] NOT NULL,
	[SiteName_ID] [tinyint] NOT NULL,
	[ServerIP_ID] [smallint] NOT NULL,
	[Method_ID] [tinyint] NOT NULL,
	[URIstem_ID] [int] NOT NULL,
	[URIquery_ID] [int] NOT NULL,
	[Port] [smallint] NOT NULL,
	[ClientUserName_ID] [int] NOT NULL,
	[ClientIP_ID] [int] NOT NULL,
	[ClientUserAgent_ID] [int] NOT NULL,
	[Status] [smallint] NOT NULL,
	[SubStatus] [smallint] NOT NULL,
	[Win32Status] [bigint] NOT NULL
)
GO
CREATE CLUSTERED INDEX [IX_DW_TH_IISLog_DateTimeUTC] ON [dbo].[DW_TH_IISLog] 
([DateTimeUTC] ASC)
GO