SQL Server Techniques for Creating a Web Reporting Page — Part 1

In this article, I’ll demonstrate how to give web users the ability to search and group data via web reporting pages –- using good old web scripting and SQL Server. No ETL/OLAP tool needed. Before we begin, I need to explain data warehousing. In general, as raw transaction level data accumulates, we summarize the data into a different table. We create an “on-line analytical processing cube”. Web users can then query against this cube, w/o interfering with your actual data feed. Also, the cube contains summarized data, speeding up queries.

For example, suppose your sales manager says, he wants to see Sales by Region, Date, and Employee ID. We would write a stored procedure that summarizes sales by region, date, & employee –- and store it into our cube. Typically, a cube does not have a primary key. But we do need to add a clustered index, so SQL Server’s index engine can work properly. SQL Server will automatically attempt to add a clustered index, if there are primary keys. If a table doesn’t have a clustered index, SQL will treat it as an unordered heap. That is, it MAY do table scans, even if there are indexes available, unless you give it an index hint.

In the following cube, N is the number of sales.

<Create the cube and populate it>

CREATE TABLE [dbo].[Cube] (

[Region] [varchar] (50) NULL ,

[Date] [datetime] NULL ,

[EmpID] [int] NULL ,

[N] [int] NULL

)



insert into cube values('North','2003/7/1',1,10)

insert into cube values('North','2003/7/2',1,8)

insert into cube values('North','2003/7/4',2,6)

insert into cube values('South','2003/7/1',12,5)

insert into cube values('South','2003/7/5',12,6)

insert into cube values('South','2003/7/7',12,33)

insert into cube values('South','2003/7/9',13,2)

insert into cube values('East','2003/7/1',21,1)

insert into cube values('East','2003/7/2',21,3)

insert into cube values('West','2003/7/10',31,22)

insert into cube values('West','2003/7/12',31,2)

insert into cube values('West','2003/7/11',32,13)

insert into cube values('West','2003/7/18',32,23)

insert into cube values('West','2003/7/19',33,4)

</Create the cube and populate it>

Search the Cube

Our sales manager’s first request is to “search” or filter the cube. For example, he wants to be able to select, the “West” region or select ‘All’ regions. Similarly he wants to select employee “21” or select “All” employees. Dates aren’t a big issue, as he only wants to be able to select a date range. In our web page, we provide him with input select boxes. Below is an example input select box for Region, which would be passed into the @Region parameter of our reporting stored procedure “CubeSP”.



<Our Region input select box >

<select name="Region">

<OPTION value="ALL">ALL</option>

<OPTION value="North">North</option>

<OPTION value="East">East</option>

<OPTION value="South">South</option>

<OPTION value="West">West</option>

</select>

</Our Region input select box >

<Our Reporting Stored Procedure>

CREATE PROCEDURE CubeSP

(

@method varchar(50)='sumSales' --:sumSales

,@region varchar(50)='all'

,@startdate datetime='1900/1/1'

,@enddate datetime='3000/1/1'

,@empid varchar(50)='all'

)

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select @region=case when @region='all' then '%' else @region end

select @empid=case when @empid='all' then '%' else @empid end



if @method='sumSales' begin

select sumSales=sum(N)

from cube

where [date] between @startdate and @enddate

and region like @region

and empid like @empid

return

end

</Our Reporting Stored Procedure>