Summarizing Data in History Tables

By moving data to history tables, you can relieve your transactional system of the hit taken when reporting queries are run against it. History tables can be optimized for queries and maintained separately from the rest of the system. There are a number of ways to create these tables and keep them updated. We will walk through a few examples here that utilize both T-SQL and SQL Server Agent to load the tables.

In the following steps, you will create a summary table from the AdventureWorks database that will track the sales by a salesperson for given products. The goal is to be able to quickly discover how many units of a given product the salesperson has sold by querying on his or her name.

Creating and Loading a History Table

1.	You will need to set up the table as shown below in your AdventureWorks database in order to use the examples verbatim. The code to create this table and its clustered index is as follows (and is included in the sample files as CreateSalesPerson ProductWeeklySummary.sql). Enter and execute this code in a new query window of SQL Server Management Studio. USE AdventureWorks; GO CREATE TABLE Sales.SalesPersonProductWeeklySummary (SalesPersonID INT ,SalesPersonFirstName NVARCHAR(50) ,SalesPersonLastName NVARCHAR(50) ,OrderWeekOfYear INT ,OrderYear INT ,ProductID INT ,ProductName NVARCHAR(50) ,WeeklyOrderQty INT ,WeeklyLineTotal MONEY ); GO CREATE CLUSTERED INDEX cidx_SalesPersonProductWeeklySummary ON Sales.SalesPersonProductWeeklySummary(OrderYear, OrderWeekOfYear, SalesPersonID); GO
2.	Next, you will need a stored procedure that can be used to select the data that will be loaded into the SalesPersonProductWeeklySummary table. The following code (included in the sample files as CreateUspGetWeeklySalesSummary.sql) creates the procedure. Enter and execute this code in a New Query window. USE AdventureWorks GO CREATE PROCEDURE Sales.uspGetSalesWeeklySummary (@StartOfWeek DATETIME ,@EndOfWeek DATETIME ) AS BEGIN SELECT hdr.SalesPersonID ,cntc.FirstName AS SalesPersonFirstName ,cntc.LastName AS SalesPersonLastName ,DATEPART(WEEK, hdr.OrderDate) AS OrderWeekOfYear ,DATEPART(YEAR, hdr.OrderDate) AS OrderYear ,prod.ProductID ,prod.Name AS ProductName ,SUM(dtl.OrderQty) as WeeklyOrderQty ,SUM(dtl.LineTotal) as WeeklyLineTotal FROM Sales.SalesOrderHeader hdr INNER JOIN Sales.SalesOrderDetail dtl ON hdr.SalesOrderID = dtl.SalesOrderID INNER JOIN HumanResources.Employee emp ON hdr.SalesPersonID = emp.EmployeeID INNER JOIN Person.Contact cntc ON emp.ContactID = cntc.ContactID INNER JOIN Production.Product prod ON dtl.ProductID = prod.ProductID WHERE hdr.OrderDate BETWEEN @StartOfWeek AND @EndOfWeek GROUP BY hdr.SalesPersonID ,cntc.FirstName ,cntc.LastName ,prod.ProductID ,prod.Name ,hdr.OrderDate END; GO Tip By using the Sales schema, you can make sure that the permissions for accessing the sales data apply to the new summary data objects as well.
3.	Next, you can load the table with the uspGetWeeklySalesSummary stored procedure using the following code (included in the sample files as LoadSalesPersonProduct WeeklySummary.sql). Enter and execute this code in a New Query window. INSERT INTO Sales.SalesPersonProductWeeklySummary (SalesPersonID ,SalesPersonFirstName ,SalesPersonLastName ,OrderWeekOfYear ,OrderYear ,ProductID ,ProductName ,WeeklyOrderQty ,WeeklyLineTotal ) EXEC Sales.uspGetSalesWeeklySummary @StartOfWeek = '1/1/2004 00:00:00', @EndOfWeek = '1/7/2004 11:59:59'; GO
4.	Finally, you can use a variation of the above code in SQL Server Agent to automate the weekly loading of the data. Tip You can also use SQL Server Integration Services to load the data. If you have more than one load operation, this might be a better option for you.
5.	In SQL Server Management Studio, open the New Job dialog box by expanding the SQL Server Agent node in Object Explorer and right-clicking the Jobs folder. Select New Job from the context menu.
6.	On the General page of the New Job dialog box, give the job a meaningful name. You can add a description here as well.
7.	On the Steps page of the New Job dialog box, click the New button to create a new job step. This will open the New Job Step dialog box, as shown here:
8.	After giving the step a meaningful name (here we use Load The Weekly Summary), you need to select Transact-SQL Script (T-SQL) from the Type dropdown menu and AdventureWorks from the Database dropdown menu. Tip You can choose a different account to run the job by setting the Run As option. By default the SQL Server Agent account will be used to execute the command.
9.	Next, set the command code. You will need to set the dates dynamically. Enter the following code to set the variables to run from Sunday to Saturday of the previous week and load the table. This step will assume that the job is scheduled to run on Tuesday. (This code is included in the sample files as ScheduledJobStep.sql.) DECLARE @StartOfWeek datetime ,@EndOfWeek datetime SET @StartOfWeek = CAST(ROUND(CAST(DATEADD(DAY, -2, GETDATE()) AS FLOAT),0,1) AS DATETIME) SET @EndOfWeek = DATEADD(DAY, 7, @StartOfWeek) INSERT INTO Sales.SalesPersonProductWeeklySummary (SalesPersonID ,SalesPersonFirstName ,SalesPersonLastName ,OrderWeekOfYear ,OrderYear ,ProductID ,ProductName ,WeeklyOrderQty ,WeeklyLineTotal ) EXEC Sales.uspGetSalesWeeklySummary @StartOfWeek, @EndOfWeek Tip Because SQL Server does not have a datatype that supports date only, it is necessary to drop the time from the DateTime in order to set the parameters correctly. You can do this as shown above or as follows: SET @StartOfWeek = CAST(CONVERT(VARCHAR, DATEADD(DAY, -2, GETDATE()), 101) AS DATETIME). You can try it either way. This method uses a string conversion, while the other code uses numeric conversion and may perform better.
10.	Click the OK button to finish creating the Load the Weekly Summary job step.
11.	On the Schedules page of the New Job dialog box, you can set up when the job is to execute. In this example, you will set the job up to run on a weekly recurring schedule on Tuesdays at 2:00 AM. Click the New button and enter the appropriate settings in the New Job Schedule dialog box. The screen below shows the New Job Schedule dialog box set up to run this schedule. Click the OK button to save the new schedule and apply it to your current job. Tip Many of the dialog boxes in SQL Server Management Studio contain a Script button, as shown below. Click this button to script the changes you just applied. Right before you save the job, you can use this option to generate a script for the job, job steps, and schedule.
12.	Click the OK button to exit the New Job dialog box and create the new job.