/****** Object: Stored Procedure dbo.usp_ReportingLoads_DriverPROC Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ReportingLoads_DriverPROC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_ReportingLoads_DriverPROC] GO /****** Object: Stored Procedure dbo.uspStorageTop20 Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorageTop20]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorageTop20] GO /****** Object: Stored Procedure dbo.uspStorageVirtualServers Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorageVirtualServers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorageVirtualServers] GO /****** Object: Stored Procedure dbo.uspStorage_AreaGrowthTrend Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorage_AreaGrowthTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorage_AreaGrowthTrend] GO /****** Object: Stored Procedure dbo.uspStorage_ListGrowthTrend Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorage_ListGrowthTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorage_ListGrowthTrend] GO /****** Object: Stored Procedure dbo.uspStorage_SiteCollectionTrend Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorage_SiteCollectionTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorage_SiteCollectionTrend] GO /****** Object: Stored Procedure dbo.uspStorage_SizeTrend Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorage_SizeTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorage_SizeTrend] GO /****** Object: Stored Procedure dbo.uspStorage_Size_Usage_Charts Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspStorage_Size_Usage_Charts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspStorage_Size_Usage_Charts] GO /****** Object: Stored Procedure dbo.usp_Insert_FactFileStorage Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Insert_FactFileStorage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Insert_FactFileStorage] GO /****** Object: Stored Procedure dbo.usp_Insert_FactIIS Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Insert_FactIIS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Insert_FactIIS] GO /****** Object: Stored Procedure dbo.usp_Insert_FactSiteStorage Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Insert_FactSiteStorage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Insert_FactSiteStorage] GO /****** Object: Stored Procedure dbo.usp_Insert_FactWSS Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Insert_FactWSS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Insert_FactWSS] GO /****** Object: Stored Procedure dbo.usp_Select_Keywords Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_Keywords]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_Keywords] GO /****** Object: Stored Procedure dbo.usp_Select_PageAccess Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_PageAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_PageAccess] GO /****** Object: Stored Procedure dbo.usp_Select_PageReferrer Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_PageReferrer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_PageReferrer] GO /****** Object: Stored Procedure dbo.usp_Select_PageUsers Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_PageUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_PageUsers] GO /****** Object: Stored Procedure dbo.usp_Select_SearchTerms Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SearchTerms]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SearchTerms] GO /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionCount Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteCollectionCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteCollectionCount] GO /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionUsers Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteCollectionUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteCollectionUsers] GO /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionWebs Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteCollectionWebs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteCollectionWebs] GO /****** Object: Stored Procedure dbo.usp_Select_SitePageTrend Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SitePageTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SitePageTrend] GO /****** Object: Stored Procedure dbo.usp_Select_SiteTrend Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteTrend] GO /****** Object: Stored Procedure dbo.usp_Select_SiteTrendByVirtualServerName Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteTrendByVirtualServerName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteTrendByVirtualServerName] GO /****** Object: Stored Procedure dbo.usp_Select_SiteUsers Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteUsers] GO /****** Object: Stored Procedure dbo.usp_Select_SiteWebs Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_SiteWebs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_SiteWebs] GO /****** Object: Stored Procedure dbo.usp_Select_WSS Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Select_WSS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Select_WSS] GO /****** Object: Stored Procedure dbo.usp_StartDateEndDate_Retrieve Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_StartDateEndDate_Retrieve]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_StartDateEndDate_Retrieve] GO /****** Object: Stored Procedure dbo.uspGetArchSurKey Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspGetArchSurKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspGetArchSurKey] GO /****** Object: Stored Procedure dbo.usp_Insert_Dims Script Date: 6/13/2005 9:55:06 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Insert_Dims]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Insert_Dims] GO /****** Object: Stored Procedure dbo.uspGetArchSurKey Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspGetArchSurKey Script Date: 5/26/2005 10:31:17 AM ******/ /****** Object: Stored Procedure dbo.uspGetArchSurKey Script Date: 5/4/2005 5:39:25 PM ******/ CREATE PROCEDURE dbo.uspGetArchSurKey /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/25/2005 ** Description : This procedure will return Virtual Server Name from DimArch table ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ AS Select VirtualServerName From DimArch Group By VirtualServerName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Insert_Dims Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_Dims Script Date: 5/26/2005 10:31:17 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_Dims Script Date: 5/4/2005 5:39:25 PM ******/ CREATE PROCEDURE dbo.usp_Insert_Dims AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Insert_Dims Author: Dave Harper - Quilogy Purpose: Load All Dimension Tables in the Reporting Database Usage: ----------------------------------------------------------------------------------------------------------------------------- */ declare @errorctr tinyint declare @StartDate datetime, @EndDate datetime set @StartDate = getdate() set @errorctr = 0 Begin Transaction --Site Load (DimSite) insert into DimSite (SiteGUID, SiteURL, SiteOwner, Property1, Property2, Property3) select distinct STAGING.SiteGUID, STAGING.SiteURL, STAGING.SiteOwner, STAGING.Property1, STAGING.Property2, STAGING.Property3 from dbSPSReportingStaging.dbo.tblSites STAGING left join DimSite RPTTABLE on STAGING.SiteGUID = RPTTable.SiteGUID where RPTTable.SiteGUID is null if @@error <> 0 set @errorctr = @errorctr +1 --Update DateTimeLastAccessed (DimSite) update DimSite Set DateTimeLastAccessed = STAGING.SiteDateLastAccessed from ( select SiteGUID, max(WSSLogDate + convert(varchar,cast(wsslogtime as datetime),108)) SiteDateLastAccessed from dbSPSReportingStaging.dbo.tblWSSLogData group by SiteGUID) STAGING where DimSite.SiteGUID = STAGING.SiteGUID if @@error <> 0 set @errorctr = @errorctr +1 --Web Load (DimWeb) Insert into DimWeb(WebURL, WebName, WebGUID, WebType, WebExpirationDate) select distinct STAGING.WebURL, STAGING.WebName, STAGING.WebGUID, STAGING.WebType, STAGING.WebExpirationDate from dbSPSReportingStaging.dbo.tblWebs STAGING left join DimWeb WEBTABLE on STAGING.WebGUID = WEBTABLE.WebGUID where WEBTABLE.WebGUID is null if @@error <> 0 set @errorctr = @errorctr +1 --Search Terms (DimSearchTerm) insert into DimSearchTerm(SearchTermDesc, SearchTermSource) select distinct STAGING.Keyword, 'SPS' from dbSPSReportingStaging.dbo.tblSPSKeyWord STAGING left join DimSearchTerm KWTABLE on STAGING.Keyword = KWTAble.SearchTermDesc where KWTAble.SearchTermDesc is null if @@error <> 0 set @errorctr = @errorctr +1 --Insert Searches from IISLogData insert into DimSearchTerm(SearchTermDesc, SearchTermSource) select distinct replace(LogKeyWordSearched, '%20', ' '), 'IIS' from dbSPSReportingStaging.dbo.tblIISLogData tblIISLogData left join DimSearchTerm on tblIISLogData.LogKeyWordSearched = DimSearchTerm.SearchTermDesc where DimSearchTerm.SearchTermDesc is null if @@error <> 0 set @errorctr = @errorctr +1 --update SearchTermSource if record added to SPS or removed update DimSearchTerm set SearchTermSource = 'SPS' From dbSPSReportingStaging.dbo.tblSPSKeyWord KeyWord where DimSearchTerm.SearchTermDesc = KeyWord.KeyWord and DimSearchTerm.SearchTermSource = 'IIS' if @@error <> 0 set @errorctr = @errorctr +1 --User Table (DimUser) Insert Into DimUser(UserName) select distinct STAGING.WSSUser from dbSPSReportingStaging.dbo.tblWSSLogData STAGING left join DimUser USERTABLE on Staging.WSSUser = USERTABLE.UserName where USERTABLE.USERNAME is null if @@error <> 0 set @errorctr = @errorctr +1 --List Table (DimList) Insert Into DimList(ListTitle, ListType, ListGUID) select STAGING.ListTitle, STAGING.ListType, STAGING.ListGUID from dbSPSReportingStaging.dbo.tblLists STAGING left join DimList LISTTABLE on STAGING.ListGUID = LISTTABLE.ListGUID where ListTable.ListGUID is null if @@error <> 0 set @errorctr = @errorctr +1 --File Table (DimFile) Insert Into DimFile(FileType) select Distinct lower(STAGING.DocType) from dbSPSReportingStaging.dbo.tblDocs STAGING left outer join DimFile on Staging.DocType = DimFile.FileType where DimFile.FileType is null if @@error <> 0 set @errorctr = @errorctr +1 --Arch Table (DimArch) insert into DimArch(DatabaseName, VirtualServerName, IISServiceName, HasPortalSites) select distinct DB.DatabaseName, VS.VirtualServerName, VS.IISServiceName, coalesce(max(cast(IsPortal as tinyint)),0) HasPortal from dbSPSReportingStaging.dbo.tblDatabases DB JOIN dbSPSReportingStaging.dbo.tblVirtualServers VS ON DB.VirtualServerGUID = VS.VirtualServerGUID left join dbSPSReportingStaging.dbo.tblSites SITES on DB.DatabaseGUID = SITES.DatabaseGUID left join DimArch on VS.VirtualServerName = DimArch.VirtualServername and VS.IISServiceName = DimArch.IISServiceName and DB.DatabaseName = DimArch.DatabaseName where DimArch.VirtualServerName is null group by VS.VirtualServerName, VS.IISServiceName, DB.DatabaseName order by VS.iisservicename if @@error <> 0 set @errorctr = @errorctr +1 --FileName (DimFileName) Insert into DimFileName(RelativeURL) select distinct tblDocs.relativeURL from dbSPSReportingStaging.dbo.tblDocs tblDocs left join DimFileName on tblDocs.RelativeURL = DimFileName.RelativeURL where DimFileName.RelativeURL is null if @@error <> 0 set @errorctr = @errorctr +1 --Referrer (DimReferrer) Insert into DimReferrer(ReferrerURL) select distinct tblWSSLogData.ReferringURL from dbSPSReportingStaging.dbo.tblWSSLogData tblWSSLogData left join DimReferrer on tblWSSLogData.ReferringURL = DimReferrer.ReferrerURL where DimReferrer.ReferrerURL is null and tblWSSLogData.ReferringURL is not null if @@error <> 0 set @errorctr = @errorctr +1 set @EndDate = getdate() If @errorCtr >0 Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'Load Reporting Dims', @StartDate, @EndDate, 'Errors Occurred' RAISERROR ( 'Errors Were encountered During usp_Insert_Dims.', 16, 1 ) Return -1 end else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'Load Reporting Dims', @StartDate, @EndDate, 'Dims Loaded Successfull' End Return 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorageTop20 Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorageTop20 Script Date: 5/26/2005 10:31:17 AM ******/ /****** Object: Stored Procedure dbo.uspStorageTop20 Script Date: 5/4/2005 5:39:25 PM ******/ /****** Object: Stored Procedure dbo.uspStorageTop20 Script Date: 5/4/2005 5:39:25 PM ******/ CREATE PROCEDURE dbo.uspStorageTop20 ( @asofDateFull DateTime = NULL ) /************************************************************************************* ** Author : FirAS Al-Takrouri ** Date Written: 4/11/2005 ** Description : This procedure will return the site name, Virutal Server, ownder, size, files and quota. ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ AS DECLARE @asofDateSurKey int IF @asofDateFull IS NULL BEGIN -- If there is not "AS of date" pASsed in then determine it by the most recent date data wAS captured SELECT @asofDateSurKey = Max(DateSurKey) FROM FactSiteStorage SELECT @asofDateFull = DateFull FROM DimDate WHERE dimdate.datesurkey = @asofDateSurKey END ELSE BEGIN -- Otherwise, determine the "AS of DateSurKey" bASed on the date pASsed in SELECT @asofDateSurKey = DateSurKey FROM DimDate WHERE CONVERT( varchar(10), DateFull, 101 ) = CONVERT( varchar(10), @asofDateFull, 101 ) END SELECT TOP 20 @asofDateFull as DateFull, S.SiteURL AS Site, S.SiteSurKey, Arch.VirtualServerName AS VirtualServer, S.SiteOwner AS Owner, Count (FileS.FileSurKey) AS Files, FSS.DiskSpaceUsed/1024.0/1024.0 AS SizeMB, FSS.DiskSpaceQuota/1024.0/1024.0 AS Quota FROM DimSite S INNER JOIN FactSiteStorage FSS ON S.SiteSurKey = FSS.SiteSurKey INNER JOIN DimArch Arch ON Arch.ArchSurKey = FSS.ArchSurKey INNER JOIN FactFileStorage FileS ON FileS.SiteSurKey = S.SiteSurKey WHERE FSS.DateSurKey = @asofDateSurKey AND FileS.DateSurKey = @asofDateSurKey GROUP BY S.SiteSurKey,Arch.VirtualServerName, S.SiteURL, S.SiteOwner,FSS.DiskSpaceUsed,FSS.DiskSpaceQuota ORDER BY FSS.DiskSpaceUsed desc GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorageVirtualServers Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorageVirtualServers Script Date: 5/26/2005 10:31:18 AM ******/ CREATE PROCEDURE dbo.uspStorageVirtualServers ( @asofDateFull DateTime = NULL ) /************************************************************************************* ** Author : FirAS Al-Takrouri ** Date Written: 4/11/2005 ** Description : This procedure will return the site name, Virutal Server, ownder, size, files AND quota. ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ AS DECLARE @asofDateSurKey int IF @asofDateFull IS NULL BEGIN -- If there is not "as of date" passed in then determine it by the most recent date data was captured SELECT @asofDateSurKey = Max(DateSurKey) FROM FactSiteStorage SELECT @asofDateFull = DateFull FROM DimDate WHERE dimdate.datesurkey = @asofDateSurKey END ELSE BEGIN -- Otherwise, determine the "as of DateSurKey" based on the date passed in SELECT @asofDateSurKey = DateSurKey FROM DimDate WHERE CONVERT( varchar(10), DateFull, 101 ) = CONVERT( varchar(10), @asofDateFull, 101 ) END SELECT CONVERT( varchar(10), @asofDateFull , 1 ) AS AsOfDate, (SELECT COUNT(distinct dw.WebSurKey) FROM DimWeb dw INNER JOIN FactFileStorage ffs on ffs.WebSurKey = dw.WebSurKey INNER JOIN DimArch da on da.ArchSurKey = ffs.ArchSurKey WHERE dw.WebType = 'A' AND da.VirtualServerName = a.VirtualServerName) AS AreaCount, (SELECT COUNT(distinct dw.WebSurKey) FROM DimWeb dw INNER JOIN FactFileStorage ffs on ffs.WebSurKey = dw.WebSurKey INNER JOIN DimArch da on da.ArchSurKey = ffs.ArchSurKey WHERE dw.WebType = 'S' AND da.VirtualServerName = a.VirtualServerName) AS SiteCount, (SELECT ISNULL (CONVERT(DECIMAL(15,2), SUM(FSS.DiskSpaceQuota)/1024.0/1024.0),0) FROM FactSiteStorage FSS INNER JOIN DimArch da on FSS.ArchSurKey = da.ArchSurKey WHERE FSS.DateSurKey = @asofDateSurKey AND FSS.TimeSurKey = (SELECT MAX(FSS2.TimeSurKey) FROM FactSiteStorage FSS2 WHERE FSS2.SiteSurKey = FSS.SiteSurKey AND FSS2.DateSurKey = @asofDateSurKey GROUP BY FSS2.SiteSurKey) AND da.VirtualServerName = a.VirtualServerName) AS Quota, (SELECT Distinct ISNULL (CONVERT(DECIMAL(15,2), SUM (FSS.DiskSpaceUsed)/1024.0/1024.0),0) FROM FactSiteStorage FSS INNER JOIN DimArch da on FSS.ArchSurKey = da.ArchSurKey WHERE FSS.DateSurKey = @asofDateSurKey AND FSS.TimeSurKey = (SELECT MAX(FSS2.TimeSurKey) FROM FactSiteStorage FSS2 WHERE FSS2.SiteSurKey = FSS.SiteSurKey AND FSS2.DateSurKey = @asofDateSurKey GROUP BY FSS2.SiteSurKey) AND da.VirtualServerName = a.VirtualServerName) AS [Size], a.VirtualServerName, Count(Distinct s.SiteSurKey) AS SiteCollection, Count(Distinct FileS.ListSurKey) AS ListCount, Count(Distinct FileS.FileSurKey) AS FileCount FROM FactFileStorage FileS LEFT OUTER JOIN DimArch a on a.ArchSurKey = FileS.ArchSurKey LEFT OUTER JOIN DimSite s on FileS.SiteSurKey = s.SiteSurKey LEFT OUTER JOIN DimList l on FileS.ListSurKey = l.ListSurKey LEFT OUTER JOIN DimFile F on FileS.FileSurKey = F.FileSurKey GROUP BY a.VirtualServerName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorage_AreaGrowthTrend Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_AreaGrowthTrend Script Date: 5/26/2005 10:31:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_AreaGrowthTrend Script Date: 5/4/2005 5:39:26 PM ******/ /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/14/2005 ** Description : Usage: ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ CREATE PROCEDURE dbo.uspStorage_AreaGrowthTrend ( @DateRange varchar(5) = DEFAULT, @charVirtualServerName nvarchar(255) ) AS DECLARE @Range int DECLARE @Type char DECLARE @StartDate int DECLARE @EndDate int SET @StartDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10) , GetDate(), 1) WHERE dd.DateFull = GETDATE() Select dd.DateDay as 'Day', dd.DateSurKey as DateSurKey, dd.DateMonthName, Count (W.WebSurKey)as Areas, IsNull(Arch.HasPortalSites,0) as HasPortalSites, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @StartDate), 1) As DateStart, CONVERT (VARCHAR(10), dd.DateFull, 1) as DateFull, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @EndDate ), 1) As LastDate From DimDate dd LEFT OUTER JOIN FactFileStorage FFS ON dd.DateSurKey = FFS.DateSurKey LEFT OUTER JOIN DimArch Arch ON (Arch.ArchSurKey = FFS.ArchSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) LEFT OUTER JOIN DimWeb W ON (W.WebSurkey = FFS.WebSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) WHERE dd.DateSurKey >= @StartDate And dd.DateSurKey <= @EndDate AND (W.WebType = 'A' OR W.WebType is NULL) GROUP BY Arch.VirtualServerName,dd.DateFull, dd.DateDay,dd.DateSurKey,Arch.HasPortalSites,dd.DateMonthName Order By dd.DateSurKey asc /* TESTING ============ exec dbo.uspStorage_AreaGrowthTrend '30D' , 'Default Web Site' */ GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorage_ListGrowthTrend Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_ListGrowthTrend Script Date: 5/26/2005 10:31:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_ListGrowthTrend Script Date: 5/4/2005 5:39:26 PM ******/ /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/14/2005 ** Description : Usage: ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ CREATE PROCEDURE dbo.uspStorage_ListGrowthTrend ( @DateRange varchar(5) = DEFAULT, @charVirtualServerName nvarchar(255) ) AS DECLARE @Range int DECLARE @Type char DECLARE @StartDate int DECLARE @EndDate int SET @StartDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10) , GetDate(), 1) WHERE dd.DateFull = GETDATE() Select dd.DateDay as 'Day', dd.DateSurKey, dd.DateMonthName, Count (l.ListSurKey)as ListCount, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @StartDate), 1) As DateStart, CONVERT (VARCHAR(10), dd.DateFull, 1) as DateFull, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @EndDate), 1) As LastDate From DimDate dd LEFT OUTER JOIN FactFileStorage FFS ON dd.DateSurKey = FFS.DateSurKey LEFT OUTER JOIN DimArch Arch ON (Arch.ArchSurKey = FFS.ArchSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) LEFT OUTER JOIN DimList l on (FFS.ListSurKey = l.ListSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) WHERE dd.DateSurKey >= @StartDate And dd.DateSurKey <= @EndDate GROUP BY Arch.VirtualServerName,dd.DateFull, dd.DateDay,dd.DateSurKey,dd.DateMonthName ORDER BY dd.DateSurKey -- exec dbo.uspStorage_ListGrowthTrend '30D' , 'Default Web Site' GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorage_SiteCollectionTrend Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_SiteCollectionTrend Script Date: 5/26/2005 10:31:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_SiteCollectionTrend Script Date: 5/4/2005 5:39:26 PM ******/ /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/14/2005 ** Description : Usage: ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ CREATE PROCEDURE dbo.uspStorage_SiteCollectionTrend ( @DateRange varchar(5) = DEFAULT, @charVirtualServerName nvarchar(255) ) AS DECLARE @Range int DECLARE @Type char DECLARE @StartDate int DECLARE @EndDate int SET @StartDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10) , GetDate(), 1) WHERE dd.DateFull = GETDATE() Select dd.DateDay as 'Day', dd.DateSurKey as DateSurKey, dd.DateMonthName, Arch.VirtualServerName, Count(S.SiteSurKey) as CollectionCount, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @StartDate), 1) As DateStart, CONVERT (VARCHAR(10), dd.DateFull, 1) as DateFull, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @EndDate), 1) As LastDate From DimDate dd Left OUTER JOIN FactSiteStorage FSS ON dd.DateSurKey = FSS.DateSurKey Left OUTER JOIN DimArch Arch ON (Arch.ArchSurKey = FSS.ArchSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) Left OUTER JOIN DimSite S ON (S.SiteSurkey = FSS.SiteSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) WHERE dd.DateSurKey >= @StartDate And dd.DateSurKey <= @EndDate GROUP BY Arch.VirtualServerName,dd.DateFull, dd.DateDay, dd.DateSurKey,dd.DateMonthName ORDER BY dd.DateSurKey asc -- exec dbo.uspStorage_SiteCollectionTrend '60D' , 'Team Site Virtual Server' GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorage_SizeTrend Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_SizeTrend Script Date: 5/26/2005 10:31:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_SizeTrend Script Date: 5/4/2005 5:39:26 PM ******/ /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/14/2005 ** Description : Usage: ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ CREATE PROCEDURE dbo.uspStorage_SizeTrend ( @DateRange varchar(5) = DEFAULT, @charVirtualServerName nvarchar(255) ) AS DECLARE @Range int DECLARE @Type char DECLARE @StartDate int DECLARE @EndDate int SET @StartDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @StartDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10) , GetDate(), 1) WHERE dd.DateFull = GETDATE() Select convert(Decimal(15,2), SUM(FSS.DiskSpaceUsed)/1024.0/1024.0) as Storage, convert(Decimal(15,2), SUM (FSS.DiskSpaceQuota)/1024.0/1024.0) as Quota, dd.DateDay as 'Day', dd.DateMonthName, dd.DateSurKey as DateSurKey, Arch.VirtualServerName as 'Server_Name', CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @StartDate), 1) As DateStart, CONVERT(VARCHAR(10), dd.DateFull, 1) as DateFull, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @EndDate), 1) As LastDate From DimDate dd LEFT OUTER JOIN FactSiteStorage FSS ON dd.DateSurKey = FSS.DateSurKey LEFT OUTER JOIN DimArch Arch ON (Arch.ArchSurKey = FSS.ArchSurKey AND (Ltrim(Rtrim(Arch.VirtualServerName)) = @charVirtualServerName)) WHERE dd.DateSurKey >= @StartDate And dd.DateSurKey <= @EndDate GROUP BY Arch.VirtualServerName, dd.DateFull, dd.DateDay, dd.DateSurKey,dd.DateMonthName ORDER BY dd.DateSurKey -- exec dbo.uspStorage_SizeTrend '6M' , 'Default Web Site' GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.uspStorage_Size_Usage_Charts Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_Size_Usage_Charts Script Date: 5/26/2005 10:31:18 AM ******/ /****** Object: Stored Procedure dbo.uspStorage_Size_Usage_Charts Script Date: 5/4/2005 5:39:26 PM ******/ CREATE PROCEDURE dbo.uspStorage_Size_Usage_Charts ( @asofDateFull DateTime = NULL ) /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/26/2005 ** Description : This procedure will return the site name, Virutal Server, size, files and quota. ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ AS DECLARE @asofDateSurKey int IF @asofDateFull IS NULL BEGIN -- If there is not "as of date" passed in then determine it by the most recent date data was captured SELECT @asofDateSurKey = Max(DateSurKey) FROM FactSiteStorage SELECT @asofDateFull = DateFull FROM DimDate WHERE dimdate.datesurkey = @asofDateSurKey END ELSE BEGIN -- Otherwise, determine the "as of DateSurKey" based on the date passed in SELECT @asofDateSurKey = DateSurKey FROM DimDate WHERE CONVERT( varchar(10), DateFull, 101 ) = CONVERT( varchar(10), @asofDateFull, 101 ) END Select CASE WHEN Sum(FSS.DiskSpaceQuota) = 0 Or Sum(FSS.DiskSpaceQuota) is null THEN a.VirtualServerName + ' *' ELSE a.VirtualServerName END as VirtualServerName, (Select IsNull (convert(Decimal(15,2), Sum(FSS.DiskSpaceQuota)/1024.0/1024.0),0) From FactSiteStorage FSS inner join DimArch da on FSS.ArchSurKey = da.ArchSurKey Where FSS.DateSurKey = @asofDateSurKey and da.VirtualServerName = a.VirtualServerName) as Quota, (Select Distinct IsNull (convert(Decimal(15,2), Sum (FSS.DiskSpaceUsed)/1024.0/1024.0),0) From FactSiteStorage FSS inner join DimArch da on FSS.ArchSurKey = da.ArchSurKey Where FSS.DateSurKey = @asofDateSurKey and da.VirtualServerName = a.VirtualServerName) as [Size] From DimArch a Left Outer Join FactSiteStorage FSS on FSS.ArchSurKey = a.ArchSurKey group by a.VirtualServerName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Insert_FactFileStorage Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactFileStorage Script Date: 5/26/2005 10:31:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactFileStorage Script Date: 5/4/2005 5:39:26 PM ******/ CREATE PROCEDURE dbo.usp_Insert_FactFileStorage AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Insert_FactFileStorage Author: Dave Harper - Quilogy (4/27/05) Purpose: Load the FactFileStorage table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ declare @StartDate datetime, @ProcessDate datetime set @StartDate = getdate() set @ProcessDate = getdate() Begin Transaction if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempFileStorage_toFactStorage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblTempFileStorage_toFactStorage] if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Dropping Table tblTempFileStorage_toFactStorage', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors Were encountered dropping tblTempFileStorage_toFactStorage', 16, 1 ) Return -1 End else Commit Transaction Begin Transaction create table dbo.tblTempFileStorage_toFactStorage ( RowID int identity not null, DocGUID uniqueidentifier null, FileSize bigint not null, FileDateTime datetime not null, ListGUID uniqueIdentifier null, --FileType nvarchar(25) not null, --Fix for bug # 403546 FileType nvarchar(255), WebGUID uniqueIdentifier not null, SiteGUID uniqueIdentifier not null, RelativeUrl nvarchar(255) not null, FileID int null, DateID int null, TimeID int null, ListID int null, WebID int null, ArchID int null, SiteID int null, FileNameID bigint ) if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Failed to Create Table tblTempFileStorage_toFactStorage', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Failed to Create Table tblTempFileStorage_toFactStorage', 16, 1 ) Return -1 End else Commit Transaction --Create Indexes - outside of transaction Create Index idx_nc_TmpFileStorage_1 on dbo.tblTempFileStorage_toFactStorage(ListGUID) Create Index idx_nc_TmpFileStorage_2 on dbo.tblTempFileStorage_toFactStorage(DocGUID) Create Index idx_nc_TmpFileStorage_3 on dbo.tblTempFileStorage_toFactStorage(SiteGUID) Begin Transaction insert into tblTempFileStorage_toFactStorage (DocGUID, FileSize, FileDateTime, ListGUID, FileType, WebGUID, SiteGUID, RelativeUrl) select DocGUID, DocSize, @ProcessDate, ListGUID, DocType, tblDocs.WebGUID, SiteGUID, RelativeUrl from dbSPSReportingStaging.dbo.tblDocs tblDocs join dbSPSReportingStaging.dbo.tblWebs tblWebs on tblDocs.WebGUID = tblWebs.WebGUID update tblTempFileStorage_toFactStorage set FileID = DimFile.FileSurKey from DimFile, tblTempFileStorage_toFactStorage a where a.FileType = DimFile.FileType update tblTempFileStorage_toFactStorage set DateID = DimDate.DateSurKey from DimDate where convert(varchar,FileDateTime,101) = convert(varchar,DateFull,101) update tblTempFileStorage_toFactStorage set TimeID = DimTime.TimeSurKey from DimTime where convert(varchar,FileDateTime,108) = convert(varchar,TimeFull,108) update tblTempFileStorage_toFactStorage set ListID = DimList.ListSurKey from tblTempFileStorage_toFactStorage a, DimList where a.ListGUID = DimList.ListGUID update tblTempFileStorage_toFactStorage set ListID = DimList.ListSurKey from DimList where ListID is null and DimList.ListTitle = 'No Title' update tblTempFileStorage_toFactStorage set WebID = DimWeb.WebSurKey from tblTempFileStorage_toFactStorage a, DimWeb where a.WebGUID = DimWeb.WebGUID update tblTempFileStorage_toFactStorage set ArchID = DimArch.ArchSurKey from tblTempFileStorage_toFactStorage a, (select ArchSurKey, SiteGUID from DimArch, ( select distinct DatabaseName, VirtualServerName, IISServiceName, SiteGUID from dbSPSReportingStaging.dbo.tblDatabases DB join dbSPSReportingStaging.dbo.tblSites SITES on DB.DatabaseGUID = SITES.DatabaseGUID JOIN dbSPSReportingStaging.dbo.tblVirtualServers VS ON DB.VirtualServerGUID = VS.VirtualServerGUID ) tblArch where DimArch.DatabaseName = tblArch.DatabaseName and DimArch.IISServiceName = tblArch.IISServiceName and DimArch.VirtualServerName = tblArch.VirtualServerName) DimArch where a.SiteGUID = DimArch.SiteGUID update tblTempFileStorage_toFactStorage set ArchID = DimArch.ArchSurKey from DimArch where ArchID is null and DimArch.VirtualServerName = 'No VirtualServerName' update tblTempFileStorage_toFactStorage set SiteID = DimSite.SiteSurKey from tblTempFileStorage_toFactStorage a, DimSite where a.SiteGUID = DimSite.SiteGuid update tblTempFileStorage_toFactStorage set FileNameID = DimFileName.FileNameSurKey from DimFileName, tblTempFileStorage_toFactStorage where tblTempFileStorage_toFactStorage.RelativeURL = DimFileName.RelativeURL update tblTempFileStorage_toFactStorage set SiteID = DimSite.SiteSurKey from DimSite where SiteID is null and DimSite.SiteOwner = 'No Owner' if 1<= (select count(*) from tblTempFileStorage_toFactStorage where (DateID is null or SiteID is null or ListID is Null or FileID is null or WebID is null or ArchID is null or TimeID is null or FileNameID is null)) Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Update tblTempFileStorage_toFactStorage', @StartDate, getdate(), 'Null Values' RAISERROR ( 'Null Values Exist in tblTempFileStorage_toFactStorage. Data not loaded', 16, 1 ) Return -1 end else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Update tblTempFileStorage_toFactStorage', @StartDate, getdate(), 'Update Successful' End Begin Transaction insert into FactFileStorage(FileSurKey, DateSurKey, TimeSurKey, ListSurKey, WebSurKey, ArchSurKey, SiteSurKey, FileNameSurKey, DiskSpaceUsed) select FileId, DateID, TimeID, ListID, WebID, ArchID, SiteID, FileNameID, FileSize from tblTempFileStorage_toFactStorage if @@error <> 0 Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Insert Rows FactFileStorage', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors occurred loading FactFileStorage. Data not loaded', 16, 1 ) Return -1 End else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Insert Rows FactFileStorage', @StartDate, getdate(), 'FactFileStorage Loaded Successfully' End Return 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Insert_FactIIS Script Date: 6/13/2005 9:55:18 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactIIS Script Date: 5/26/2005 10:31:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactIIS Script Date: 5/4/2005 5:39:27 PM ******/ CREATE PROCEDURE dbo.usp_Insert_FactIIS AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Insert_FactIIS Author: Dave Harper - Quilogy (4/27/05) Purpose: Load the FactIIS table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ declare @StartDate datetime set @StartDate = getdate() Begin Transaction if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempIIS_toFactIIS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblTempIIS_toFactIIS] if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactIIS - Dropping Table tblTempIIS_toFactIIS', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors Were encountered dropping tblTempIIS_toFactIIS', 16, 1 ) Return -1 End else Commit Transaction Begin Transaction Create Table dbo.tblTempIIS_toFactIIS ( RowID int identity not null Primary Key, LogDate datetime not null, LogTime datetime not null, LogKeyword nvarchar(150) not null, IISServiceName nvarchar(255) not null, TimeID bigint null, DateID bigint null, ArchID bigint null, KeyWordID bigint null, BestBetFlag tinyint , KeywordFlag tinyint default(0) not null ) if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactIIS - Failed to Create Table tblTempIIS_toFactIIS', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Failed to Create Table tblTempIIS_toFactIIS', 16, 1 ) Return -1 End else Commit Transaction --Create Indexes outside of Transaction Create Index idx_nc_TmpIIS_1 on dbo.tblTempIIS_toFactIIS(LogKeyWord) Create Index idx_nc_TmpIIS_2 on dbo.tblTempIIS_toFactIIS(IISServiceName) Begin Transaction insert into tblTempIIS_toFactIIS(LogDate,LogTime, LogKeyword, IISServiceName) select logdate, logtime, replace(logKeyWordSearched, '%20', ' '), IISServiceName from dbSPSReportingStaging.dbo.tblIISLogData update tblTempIIS_toFactIIS set DateID = DimDate.DateSurKey from DimDate where LogDate = DimDate.DateFull update tblTempIIS_toFactIIS set TimeID = DimTime.TimeSurKey from DimTime where convert(varchar,LogTime,108) = convert(varchar,DimTime.TimeFull,108) update tblTempIIS_toFactIIS set ArchID = DimArch.ArchSurKey from DimArch, tblTempIIS_toFactIIS a where a.IISServiceName = DimArch.IISServiceName update tblTempIIS_toFactIIS set KeyWordID = DimSearchTerm.SearchTermSurKey from DimSearchTerm where LogKeyWord = DimSearchTerm.SearchTermDesc update tblTempIIS_toFactIIS set BestBetFlag = 1 from dbSPSReportingStaging.dbo.tblSPSKeyWord KeyWord where LogKeyWord = KeyWord.Keyword and HasBestBet = 1 update tblTempIIS_toFactIIS set BestBetFlag = 0 where BestBetFlag is null update tblTempIIS_toFactIIS set KeyWordID = DimSearchTerm.SearchTermSurKey from DimSearchTerm where DimSearchTerm.SearchTermDesc = 'No Value' and KeywordID is null update tblTempIIS_toFactIIS set ArchID = DimArch.ArchSurKey from DimArch, tblTempIIS_toFactIIS a where a.IISServiceName is null and DimArch.IISServiceName = 'No Service' update tblTempIIS_toFactIIS set KeywordFlag = 1 from DimSearchTerm where LogKeyWord = DimSearchTerm.SearchTermDesc and DimSearchTerm.SearchTermSource = 'SPS' if 1<= (select count(*) from tblTempIIS_toFactIIS where (DateID is null or ArchID is null or TimeID is null or KeyWordID is null or BestBetFlag is null or KeyWordFlag is null)) Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactIIS - Update tblTempIIS_toFactIIS', @StartDate, getdate(), 'Null Values' RAISERROR ( 'Null Values Exist in tblTempIIS_toFactIIS. Data not loaded', 16, 1 ) Return -1 end else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactIIS - Update tblTempIIS_toFactIIS', @StartDate, getdate(), 'Update Successful' End Begin Transaction insert into FactIIS(TimeSurKey, DateSurKey, ArchSurKey, SearchTermSurKey, KeyWordFlag, BestBetFlag) select TimeID, DateID, ArchID, KeywordID, BestBetFlag, KeyWordFlag from tblTempIIS_toFactIIS if @@error <> 0 Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactIIS - Insert Rows FactIIS', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors occurred loading FactIIS. Data not loaded', 16, 1 ) Return -1 End else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactIIS - Insert Rows FactIIS', @StartDate, getdate(), 'FactIIS Loaded Successfully' End Return 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Insert_FactSiteStorage Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactSiteStorage Script Date: 5/26/2005 10:31:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactSiteStorage Script Date: 5/4/2005 5:39:27 PM ******/ CREATE PROCEDURE dbo.usp_Insert_FactSiteStorage AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Insert_FactSiteStorage Author: Dave Harper - Quilogy (4/27/05) Purpose: Load the FactSiteStorage table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ declare @StartDate datetime set @StartDate = getdate() Begin Transaction if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempSiteDiskUsage_toFactSiteUsage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblTempSiteDiskUsage_toFactSiteUsage] if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactSiteStorage - Dropping Table tblTempSiteDiskUsage_toFactSiteUsage', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors Were encountered dropping tblTempSiteDiskUsage_toFactSiteUsage', 16, 1 ) Return -1 End else Commit Transaction Begin Transaction Create table dbo.tblTempSiteDiskUsage_toFactSiteUsage( RowID bigint identity(1,1) not null, ProcessDateTime datetime null, SiteGUID uniqueIdentifier null, ArchID int null, DateID int null, TimeID int null, SiteID int null, DiskSpaceUsed bigint null, DiskQuota bigint null ) if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactSiteStorage - Failed to Create Table tblTempSiteDiskUsage_toFactSiteUsage', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Failed to Create Table tblTempSiteDiskUsage_toFactSiteUsage', 16, 1 ) Return -1 End else Commit Transaction --Create Indexes Create Index idx_nc_TmpSiteStorage_1 on dbo.tblTempSiteDiskUsage_toFactSiteUsage(SiteGUID) declare @ProcessDateTime datetime set @processDateTime = getdate() Begin Transaction insert into tblTempSiteDiskUsage_toFactSiteUsage(ProcessDateTime, SiteGUID, DiskSpaceUsed, DiskQuota) select @processDateTime, SiteGUID, SiteDiskUsed, SiteDiskQuota from dbspsreportingstaging.dbo.tblSites update tblTempSiteDiskUsage_toFactSiteUsage set DateID = DimDate.DateSurKey from DimDate where convert(varchar, ProcessDateTime,102) = convert(varchar, DimDate.DateFull,102) update tblTempSiteDiskUsage_toFactSiteUsage set TimeID = DimTime.TimeSurKey from DimTime where convert(varchar, ProcessDateTime,108) = DimTime.TimeFull update tblTempSiteDiskUsage_toFactSiteUsage set SiteID = DimSite.SiteSurKey from tblTempSiteDiskUsage_toFactSiteUsage a, DimSite where a.SiteGUID = DimSite.SiteGUID update tblTempSiteDiskUsage_toFactSiteUsage set ArchID = DimArch.ArchSurKey from ( select archsurkey, siteguid from dimarch, ( select distinct DatabaseName, IISServiceName, VirtualServerName, SiteGUID from dbSPSReportingStaging.dbo.tblDatabases DB join dbSPSReportingStaging.dbo.tblSites SITES on DB.DatabaseGUID = SITES.DatabaseGUID JOIN dbSPSReportingStaging.dbo.tblVirtualServers VS ON DB.VirtualServerGUID = VS.VirtualServerGUID) a where DimArch.IISServiceName = a.IISServiceName and DimArch.DatabaseName = a.DatabaseName and DimArch.VirtualServerName = a.VirtualServerName ) DimArch where tblTempSiteDiskUsage_toFactSiteUsage.siteguid = DimArch.siteguid update tblTempSiteDiskUsage_toFactSiteUsage set ArchID = DimArch.ArchSurKey from DimArch where ArchID is null and DimArch.VirtualServerName = 'No VirtualServerName' update tblTempSiteDiskUsage_toFactSiteUsage set SiteID = DimSite.SiteSurKey from DimSite where SiteID is null and DimSite.SiteOwner = 'No Owner' if 1<= (select count(*) from tblTempSiteDiskUsage_toFactSiteUsage where (DateID is null or SiteID is null or ArchID is null or TimeID is null)) Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactSiteStorage - Update tblTempSiteDiskUsage_toFactSiteUsage', @StartDate, getdate(), 'Null Values' RAISERROR ( 'Null Values Exist in tblTempSiteDiskUsage_toFactSiteUsage. Data not loaded', 16, 1 ) Return -1 end else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactSiteStorage - Update tblTempSiteDiskUsage_toFactSiteUsage', @StartDate, getdate(), 'Update Successful' End Begin Transaction insert into FactSiteStorage(TimeSurKey, DateSurKey, ArchSurKey, SiteSurKey, DiskSpaceUsed, DiskSpaceQuota) select TimeID, DateID, ArchID, SiteID, DiskSpaceUsed, DiskQuota from tblTempSiteDiskUsage_toFactSiteUsage if @@error <> 0 Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactSiteStorage - Insert Rows FactSiteStorage', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors occurred loading FactSiteStorage. Data not loaded', 16, 1 ) Return -1 End else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactSiteStorage - Insert Rows FactSiteStorage', @StartDate, getdate(), 'FactSiteStorage Loaded Successfully' End Return 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Insert_FactWSS Script 2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactWSS Script Date: 5/26/2005 10:31:20 AM ******/ /****** Object: Stored Procedure dbo.usp_Insert_FactWSS Script Date: 5/4/2005 5:39:27 PM ******/ CREATE PROCEDURE dbo.usp_Insert_FactWSS AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Insert_FactWSS Author: Dave Harper - Quilogy (4/27/05) Purpose: Load All Dimension Tables in the Reporting Database Usage: ----------------------------------------------------------------------------------------------------------------------------- */ declare @StartDate datetime set @StartDate = getdate() Begin Transaction if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempWSS_ToFactLoad]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblTempWSS_toFactLoad] if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Dropping Table tblTempWSS_toFactLoad', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors Were encountered dropping tblTempWSS_toFactLoad', 16, 1 ) Return -1 End else Commit Transaction Begin Transaction Create table dbo.tblTempWSS_ToFactLoad ( WSSFactID bigint identity not null Primary Key, SiteGUID uniqueidentifier, WebGUID uniqueidentifier, -- WSSDate datetime, -- Fix for Bug #: 403546 WSSDate smalldatetime, WSSTime nvarchar(8), --WSSUser nvarchar(150), --Fix for Bug #: 403546 WSSUser nvarchar(255), --WSSDoc nvarchar(50), --Fix for Bug #: 403546 WSSDoc nvarchar(255), WSSList uniqueidentifier, WSSReferrer nvarchar(255), WSSRelativeURL nvarchar(255), SiteID bigint, WebID bigint, DateID bigint, TimeID bigint, UserID bigint, FileID smallint, ListID bigint, ReferrerID bigint, RelativeURLID bigint) if @@error <>0 Begin --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Failed to Create Table tblTempWSS_toFactLoad', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Failed to Create Table tblTempWSS_toFactLoad', 16, 1 ) Return -1 End else Commit Transaction --Create Indexes Create Index idx_nc_TmpWSS_1 on dbo.tblTempWSS_ToFactLoad(SiteGUID) Create Index idx_nc_TmpWSS_2 on dbo.tblTempWSS_ToFactLoad(WSSDoc) Create Index idx_nc_TmpWSS_3 on dbo.tblTempWSS_ToFactLoad(WSSList) Create Index idx_nc_TmpWSS_4 on dbo.tblTempWSS_ToFactLoad(WebGUID) Begin Transaction insert into tblTempWSS_ToFactLoad(SiteGUID, WebGUID, WSSDate, WSSTime, WSSUser, WSSDoc, WSSList, WSSReferrer, WSSRelativeURL) select SITEGUID, WebGUID, wsslogdate, convert(nvarchar,cast(wsslogtime as datetime),108), wssuser, right(WSSDoc, len(WSSDoc) - charindex('.', WSSDoc)), ListGuid, ReferringURL, RelativeURL from dbSPSReportingStaging.dbo.tblWSSLogData update tblTempWSS_ToFactLoad set SiteID = DimSite.SiteSurKey from DimSite where tblTempWSS_ToFactLoad.SiteGUID = DimSite.SiteGUID update tblTempWSS_ToFactLoad set DateID = DimDate.DateSurKey from DimDate where WSSDate = DimDate.DateFull update tblTempWSS_ToFactLoad set TimeID = DimTime.TimeSurKey from DimTime where WSSTime = DimTime.TimeFull update tblTempWSS_ToFactLoad set WebID = DimWeb.WebSurKey from DimWeb where tblTempWSS_ToFactLoad.WebGUID = DimWeb.WebGUID update tblTempWSS_ToFactLoad set UserID = DimUser.UserSurKey from DimUser where WSSUser = DimUser.UserName update tblTempWSS_ToFactLoad set FileID = DimFile.FileSurKey from DimFile where WSSDoc = DimFile.FileType update tblTempWSS_ToFactLoad set ListID = DimList.ListSurKey from DimList where WSSList = DimList.ListGUID update tblTempWSS_ToFactLoad set ReferrerID = DimReferrer.ReferrerSurKey from DimReferrer where WSSReferrer = DimReferrer.ReferrerURL update tblTempWSS_ToFactLoad set RelativeURLID = DimFileName.FileNameSurKey from DimFileName where WSSRelativeURL = DimFileName.RelativeURL --handle nulls update tblTempWSS_ToFactLoad set ListID = DimList.ListSurKey from DimList where DimList.ListTitle = 'No Title' and ListID is null update tblTempWSS_ToFactLoad set WebID = DimWeb.WebSurKey from DimWeb where DimWeb.WebName = 'No WebName' and WebID is null update tblTempWSS_ToFactLoad set FileID = DimFile.FileSurKey from DimFile where DimFile.FileType = 'No File' and FileID is null update tblTempWSS_ToFactLoad set ReferrerID = DimReferrer.ReferrerSurKey from DimReferrer where tblTempWSS_ToFactLoad.ReferrerID is null and DimReferrer.ReferrerURL = 'No Referrer' update tblTempWSS_ToFactLoad set RelativeURLID = DimFileName.FileNameSurKey from DimFileName where tblTempWSS_ToFactLoad.RelativeURLID is null and DimFileName.RelativeURL = 'No FileName' update tblTempWSS_ToFactLoad set SiteID = DimSite.SiteSurKey from DimSite where SiteID is null and DimSite.SiteOwner = 'No Owner' if 1<= (select count(*) from tblTempWSS_ToFactLoad where (DateID is null or UserID is null or SiteID is null or ListID is Null or FileID is null or WebID is null or TimeID is null or ReferrerID is null or RelativeURLID is null)) Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Update tblTempWSS_ToFactLoad', @StartDate, getdate(), 'Null Values' RAISERROR ( 'Null Values Exist in tblTempWSS_ToFactLoad. Data not loaded', 16, 1 ) Return -1 end else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Update tblTempWSS_ToFactLoad', @StartDate, getdate(), 'Update Successful' End Begin Transaction insert into FactWSS(DateSurKey, UserSurKey, SiteSurKey, ListSurKey, FileSurKey, WebSurKey, TimeSurKey, ReferrerSurKey, FileNameSurKey) select DateID, UserID, SiteID, ListID, FileID, WebID, TimeID, ReferrerID, RelativeURLID from tblTempWSS_ToFactLoad if @@error <> 0 Begin Rollback Transaction --Insert Logging Table Message Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Insert Rows FactWSS', @StartDate, getdate(), 'Errors Occurred' RAISERROR ( 'Errors occurred loading FactWSS. Data not loaded', 16, 1 ) Return -1 End else Begin Commit Transaction --insert into Logging Table Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Insert Rows FactWSS', @StartDate, getdate(), 'FactWSS Loaded Successfully' End Return 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_Keywords Script Date: 6/13/2005 9:55:19 AM ******/ CREATE PROCEDURE dbo.usp_Select_Keywords ( @VirtualServerName varchar(255), @DateRange varchar(5) = DEFAULT, @NumSelect int = 0 ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_Keywords Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) IF @NumSelect < 0 BEGIN SET @NumSelect = -@NumSelect SET RowCount @NumSelect SELECT case when iis.BestBetFlag = 1 then dst.SearchTermDesc + ' *' else dst.SearchTermDesc end as SearchTermDesc, sum(iis.HitCount) AS HitCount from FactIIS iis inner join DimArch da on iis.ArchSurKey = da.ArchSurKey inner join DimSearchTerm dst on dst.SearchTermSurKey = iis.SearchTermSurKey inner join DimDate dd on dd.DateSurKey = iis.DateSurKey where dd.DateSurKey >= @EndDate and iis.KeyWordFlag = 1 and da.VirtualServerName = @VirtualServerName group by dst.SearchTermDesc, iis.BestBetFlag order by HitCount ASC set RowCount 0 END ELSE BEGIN SET RowCount @NumSelect SELECT case when iis.BestBetFlag = 1 then dst.SearchTermDesc + ' *' else dst.SearchTermDesc end as SearchTermDesc, sum(iis.HitCount) AS HitCount from FactIIS iis inner join DimArch da on iis.ArchSurKey = da.ArchSurKey inner join DimSearchTerm dst on dst.SearchTermSurKey = iis.SearchTermSurKey inner join DimDate dd on dd.DateSurKey = iis.DateSurKey where dd.DateSurKey >= @EndDate and iis.KeyWordFlag = 1 and da.VirtualServerName = @VirtualServerName group by dst.SearchTermDesc, iis.BestBetFlag order by HitCount DESC set RowCount 0 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_PageAccess Script Date: 6/13/2005 9:55:19 AM ******/ CREATE PROCEDURE dbo.usp_Select_PageAccess ( @FileNameSurKey int, @SiteSurKey int, @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_PageAccess Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) declare @maxdate int select @maxdate = max(ffs.DateSurKey) from FactFileStorage ffs where ffs.FileSurKey = 2 declare @maxtime int select @maxtime = max(ffs.TimeSurKey) from FactFileStorage ffs where ffs.FileSurKey = 2 and ffs.DateSurKey = @maxdate SELECT case when dfn.RelativeURL = 'No Filename' then '(Direct Site Hits)' else dfn.RelativeURL end as RelativeURL, du.UserName, (select top 1 ffs.DiskSpaceUsed from FactFileStorage ffs where ffs.FileNameSurKey = @FileNameSurKey and ffs.DateSurKey = @maxdate and ffs.TimeSurKey = @maxtime and ffs.SiteSurKey = @SiteSurKey order by ffs.DiskSpaceUsed) as FileSize, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE MAX(fwss.DateSurKey) = DateSurKey), 1) As DateLastAccessed, dr.ReferrerURL, COUNT(*) AS Count FROM FactWSS fwss INNER JOIN DimFileName dfn ON fwss.FileNameSurKey = dfn.FileNameSurKey INNER JOIN DimUser du ON fwss.UserSurKey = du.UserSurKey INNER JOIN DimReferrer dr ON fwss.ReferrerSurKey = dr.ReferrerSurKey WHERE fwss.DateSurKey >= @EndDate AND fwss.FileNameSurKey = @FileNameSurKey GROUP BY dfn.RelativeURL, du.UserName, dr.ReferrerURL ORDER BY Count DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_PageReferrer Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_PageReferrer Script Date: 5/26/2005 10:31:20 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_PageReferrer Script Date: 5/4/2005 5:39:27 PM ******/ CREATE PROCEDURE dbo.usp_Select_PageReferrer ( @FileNameSurKey int, @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_PageReferrer Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT dr.ReferrerURL, COUNT(*) AS Count FROM FactWSS fwss INNER JOIN DimFileName dfn ON fwss.FileNameSurKey = dfn.FileNameSurKey INNER JOIN DimReferrer dr ON fwss.ReferrerSurKey = dr.ReferrerSurKey WHERE fwss.DateSurKey >= @EndDate AND fwss.FileNameSurKey = @FileNameSurKey GROUP BY dr.ReferrerURL ORDER BY Count DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_PageUsers Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_PageUsers Script Date: 5/26/2005 10:31:21 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_PageUsers Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_PageUsers ( @FileNameSurKey int, @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_PageUsers Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT du.UserName, COUNT(*) AS Count FROM FactWSS fwss INNER JOIN DimFileName dfn ON fwss.FileNameSurKey = dfn.FileNameSurKey INNER JOIN DimUser du ON fwss.UserSurKey = du.UserSurKey WHERE fwss.DateSurKey >= @EndDate AND fwss.FileNameSurKey = @FileNameSurKey GROUP BY du.UserName ORDER BY Count DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SearchTerms Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SearchTerms Script Date: 5/26/2005 10:31:21 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SearchTerms Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_SearchTerms ( @VirtualServerName varchar(255), @DateRange varchar(5) = DEFAULT, @NumSelect int = 0 ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SearchTerms Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) IF @NumSelect < 0 BEGIN SET @NumSelect = -@NumSelect SET RowCount @NumSelect SELECT case when iis.KeyWordFlag = 1 then dst.SearchTermDesc + ' *' else dst.SearchTermDesc end as SearchTermDesc, sum(iis.HitCount) AS HitCount from FactIIS iis inner join DimArch da on iis.ArchSurKey = da.ArchSurKey inner join DimSearchTerm dst on dst.SearchTermSurKey = iis.SearchTermSurKey inner join DimDate dd on dd.DateSurKey = iis.DateSurKey where dd.DateSurKey >= @EndDate and da.VirtualServerName = @VirtualServerName group by dst.SearchTermDesc, iis.KeyWordFlag order by HitCount ASC set RowCount 0 END ELSE BEGIN SET RowCount @NumSelect SELECT case when iis.KeyWordFlag = 1 then dst.SearchTermDesc + ' *' else dst.SearchTermDesc end as SearchTermDesc, sum(iis.HitCount) AS HitCount from FactIIS iis inner join DimArch da on iis.ArchSurKey = da.ArchSurKey inner join DimSearchTerm dst on dst.SearchTermSurKey = iis.SearchTermSurKey inner join DimDate dd on dd.DateSurKey = iis.DateSurKey where dd.DateSurKey >= @EndDate and da.VirtualServerName = @VirtualServerName group by dst.SearchTermDesc, iis.KeyWordFlag order by HitCount DESC set RowCount 0 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionCount Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionCount Script Date: 5/26/2005 10:31:21 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionCount Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_SiteCollectionCount ( @VirtualServerName varchar (255) ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteCollectionCount Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ select count(distinct fss.SiteSurKey) as Count from FactSiteStorage fss inner join DimArch da on da.ArchSurKey = fss.ArchSurKey where da.VirtualServerName = @VirtualServerName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionUsers Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionUsers Script Date: 5/26/2005 10:31:21 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionUsers Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_SiteCollectionUsers ( @VirtualServerName varchar (255) ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteCollectionUsers Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ SELECT du.UserName FROM FactWSS fwss INNER JOIN DimUser du ON fwss.UserSurKey = du.UserSurKey INNER JOIN FactSiteStorage fss ON fwss.SiteSurKey = fss.SiteSurKey INNER JOIN DimArch da ON da.ArchSurKey = fss.ArchSurKey WHERE da.VirtualServerName = @VirtualServerName GROUP BY fwss.UserSurKey, du.UserName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionWebs Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionWebs Script Date: 5/26/2005 10:31:21 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteCollectionWebs Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_SiteCollectionWebs ( @VirtualServerName varchar (255) ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteCollectionWebs Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ select ffs.SiteSurKey, ffs.WebSurKey from FactFileStorage ffs inner join DimArch da on da.ArchSurKey = ffs.ArchSurKey where da.VirtualServerName = @VirtualServerName group by ffs.SiteSurKey, ffs.WebSurKey GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SitePageTrend Script Date: 6/13/2005 9:55:19 AM ******/ CREATE PROCEDURE dbo.usp_Select_SitePageTrend ( @SiteSurKey int, @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SitePageTrend Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) declare @maxdate int select @maxdate = max(ffs.DateSurKey) from FactFileStorage ffs where ffs.FileSurKey = 2 declare @maxtime int select @maxtime = max(ffs.TimeSurKey) from FactFileStorage ffs where ffs.FileSurKey = 2 and ffs.DateSurKey = @maxdate SELECT TOP 20 fwss.FileNameSurKey, case when ds.SiteURL = '/' then '/ (Root)' else ds.SiteURL end as SiteURL, (select top 1 ffs.DiskSpaceUsed as DiskSpaceUsed from FactFileStorage ffs where ffs.FileNameSurKey = fwss.FileNameSurKey and ffs.DateSurKey = @maxdate and ffs.TimeSurKey = @maxtime and ffs.SiteSurKey = @SiteSurKey order by ffs.DiskSpaceUsed) as FileSize, case when dfn.RelativeURL = 'No Filename' then '(Site/Area Only Hits)' else dfn.RelativeURL end as RelativeURL, COUNT(*) AS Count, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE MAX(fwss.DateSurKey) = DateSurKey), 1) As DateLastAccessed FROM FactWSS fwss INNER JOIN DimFileName dfn ON dfn.FileNameSurKey = fwss.FileNameSurKey INNER JOIN DimSite ds ON ds.SiteSurKey = fwss.SiteSurKey WHERE fwss.DateSurKey >= @EndDate AND fwss.SiteSurKey = @SiteSurKey GROUP BY fwss.FileNameSurKey, ds.SiteURL, dfn.RelativeURL ORDER BY COUNT DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteTrend Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteTrend Script Date: 5/26/2005 10:31:22 AM ******/ CREATE PROCEDURE dbo.usp_Select_SiteTrend ( @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteTrend Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT TOP 20 SUBSTRING (da.VirtualServerName, 1, LEN (da.VirtualServerName) - 1) + ds.SiteURL as SiteURL, SUM(fwss.HitCount) AS COUNT FROM FactWSS fwss INNER JOIN FactSiteStorage fss ON fss.SiteSurKey = fwss.SiteSurKey INNER JOIN DimArch da ON da.ArchSurKey = fss.ArchSurKey INNER JOIN DimSite ds ON ds.SiteSurKey = fwss.SiteSurKey WHERE fwss.DateSurKey >= @EndDate GROUP BY da.VirtualServerName, ds.SiteURL ORDER BY COUNT DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteTrendByVirtualServerName Script Date: 6/13/2005 9:55:19 AM ******/ CREATE PROCEDURE dbo.usp_Select_SiteTrendByVirtualServerName ( @VirtualServerName varchar(255), @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteTrendByVirtualServerName Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT TOP 20 ds.SiteSurKey, ds.SiteOwner, case when ds.SiteURL = '/' then '/ (Root)' else ds.SiteURL end as SiteURL, ds.Property1, ds.Property2, ds.Property3, CONVERT(VARCHAR(10), ds.DateTimeLastAccessed, 1) AS DateTimeLastAccessed, COUNT(*) AS COUNT FROM FactWSS fwss INNER JOIN FactSiteStorage fss ON fss.SiteSurKey = fwss.SiteSurKey INNER JOIN DimArch da ON da.ArchSurKey = fss.ArchSurKey INNER JOIN DimSite ds ON ds.SiteSurKey = fwss.SiteSurKey WHERE fwss.DateSurKey >= @EndDate AND da.VirtualServerName = @VirtualServerName GROUP BY ds.SiteSurKey, ds.SiteOwner, ds.SiteURL, ds.Property1, ds.Property2, ds.Property3, ds.DateTimeLastAccessed ORDER BY COUNT DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteUsers Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteUsers Script Date: 5/26/2005 10:31:22 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteUsers Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_SiteUsers ( @SiteSurKey int ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteUsers Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ SELECT fwss.UserSurKey FROM FactWSS fwss WHERE fwss.SiteSurKey = @SiteSurKey GROUP BY fwss.UserSurKey GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_SiteWebs Script Date: 6/13/2005 9:55:19 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteWebs Script Date: 5/26/2005 10:31:22 AM ******/ /****** Object: Stored Procedure dbo.usp_Select_SiteWebs Script Date: 5/4/2005 5:39:28 PM ******/ CREATE PROCEDURE dbo.usp_Select_SiteWebs ( @SiteSurKey int ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_SiteWebs Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ SELECT ffs.WebSurKey, dm.WebName FROM FactFileStorage ffs INNER JOIN DimWeb dm on dm.WebSurKey = ffs.WebSurKey WHERE ffs.SiteSurKey = @SiteSurKey GROUP BY ffs.WebSurKey, dm.WebName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_Select_WSS Script Date: 6/13/2005 9:55:19 AM ******/ CREATE PROCEDURE dbo.usp_Select_WSS ( @DateRange varchar(5) = DEFAULT ) AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Select_WSS Author: Charles Wieland - QDC Purpose: This procedure inserts an entry into the tblDatabase table Usage: ----------------------------------------------------------------------------------------------------------------------------- */ DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) SELECT da.VirtualServerName, case when ds.SiteURL = '/' then '/ (Root)' else ds.SiteURL end as SiteURL, case when dw.WebName = 'No WebName' then '(Deleted Sites / Areas)' else dw.WebName end as WebName, case when dl.ListTitle = 'No Title' then '(Site/Area Only Hits)' else dl.ListTitle end as ListTitle, SUM(fwss.HitCount) AS COUNT FROM FactWSS fwss INNER JOIN FactSiteStorage fss ON fss.SiteSurKey = fwss.SiteSurKey INNER JOIN DimArch da ON da.ArchSurKey = fss.ArchSurKey INNER JOIN DimSite ds ON ds.SiteSurKey = fwss.SiteSurKey INNER JOIN DimWeb dw ON dw.WebSurKey = fwss.WebSurKey INNER JOIN DimList dl ON dl.ListSurKey = fwss.ListSurKey WHERE fwss.DateSurKey >= @EndDate GROUP BY da.VirtualServerName, da.DatabaseName, ds.SiteURL, dw.WebName, dl.ListTitle ORDER BY da.VirtualServerName ASC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.usp_StartDateEndDate_Retrieve Script Date: 6/13/2005 9:55:20 AM ******/ /****** Object: Stored Procedure dbo.usp_StartDateEndDate_Retrieve Script Date: 5/26/2005 10:31:23 AM ******/ /****** Object: Stored Procedure dbo.usp_StartDateEndDate_Retrieve Script Date: 5/4/2005 5:39:29 PM ******/ /************************************************************************************* ** Author : Firas Al-Takrouri ** Date Written: 4/14/2005 ** Description : Usage: ************************************************************************************** ** Modification Log ** Developer Date Description ************************************************************************************** ** *************************************************************************************/ CREATE PROCEDURE dbo.usp_StartDateEndDate_Retrieve ( @DateRange varchar(5) = DEFAULT, @charVirtualServerName nvarchar(255) ) AS DECLARE @Range int DECLARE @Type char DECLARE @EndDate int SET @EndDate = 1 SET @Range = CONVERT(INT, LEFT(@DateRange, Len(@DateRange) - 1)) SET @Type = RIGHT(@DateRange, 1) IF @Type = 'D' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(DAY, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(DAY, -@Range, GETDATE()) ELSE IF @Type = 'M' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(MONTH, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(MONTH, -@Range, GETDATE()) ELSE IF @Type = 'Y' SELECT @EndDate = dd.DateSurKey FROM DimDate dd -- WHERE dd.DateFull = CONVERT(VARCHAR(10), -- DATEADD(YEAR, -@Range, GETDATE()), 1) WHERE dd.DateFull = DATEADD(YEAR, -@Range, GETDATE()) Select dd.DateDay, dd.DateSurKey, dd.DateFull, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @EndDate), 1) As DateStart, CONVERT (VARCHAR(10), dd.DateFull, 1) as DateFull, CONVERT (VARCHAR(10), (SELECT DateFull FROM DimDate WHERE DateSurKey = @EndDate + @Range), 1) As LastDate From FactSiteStorage FSS INNER JOIN DimDate dd ON dd.DateSurKey = FSS.DateSurKey INNER JOIN DimArch Arch ON Arch.ArchSurKey = FSS.ArchSurKey WHERE dd.DateSurKey >= @EndDate And Arch.VirtualServerName = @charVirtualServerName GROUP BY Arch.VirtualServerName, Arch.ArchSurKey,dd.DateDay, dd.DateSurKey, dd.DateFull ORDER BY dd.DateDay /* exec dbo.usp_StartDateEndDate_Retrieve '3D' , 'Default Web Site' select * from DimArch select * from DimDate -- select * from FactSiteStorage Select * from FactFileStorage */ GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO /****** Object: Stored Procedure dbo.usp_ReportingLoads_DriverPROC Script Date: 6/13/2005 9:55:20 AM ******/ /****** Object: Stored Procedure dbo.usp_ReportingLoads_DriverPROC Script Date: 5/26/2005 10:31:20 AM ******/ /****** Object: Stored Procedure dbo.usp_ReportingLoads_DriverPROC Script Date: 5/4/2005 5:39:25 PM ******/ CREATE PROCEDURE [dbo].usp_ReportingLoads_DriverPROC AS /* ----------------------------------------------------------------------------------------------------------------------------- Name: usp_Insert_Dims Author: Dave Harper - Quilogy Purpose: Driver Procedure for all Loads in (Dims / Fact Tables) Usage: ----------------------------------------------------------------------------------------------------------------------------- */ declare @error tinyint set @error = 0 Begin Transaction exec @error = usp_Insert_Dims If @error <> 0 Begin Rollback Transaction RaisError('Errors occurred in usp_Insert_Dims',16,1) Return -1 end exec @error = usp_Insert_FactWSS If @error <> 0 Begin Rollback Transaction RaisError('Errors occurred in usp_Insert_FactWSS',16,1) Return -1 end exec @error = usp_Insert_FactIIS If @error <> 0 Begin Rollback Transaction RaisError('Errors occurred in usp_Insert_FactIIS',16,1) Return -1 end exec @error = usp_Insert_FactFileStorage If @error <> 0 Begin Rollback Transaction RaisError('Errors occurred in usp_Insert_FactFileStorage',16,1) Return -1 end exec @error = usp_Insert_FactSiteStorage If @error <> 0 Begin Rollback Transaction RaisError('Errors occurred in usp_Insert_FactSiteStorage',16,1) Return -1 end if @error = 0 Commit Transaction Return 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO