How CubeSP Works
First we have three session settings “SET NOCOUNT ON”, “SET ANSI_WARNINGS OFF”, & “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”. The first two settings are for aesthetics. “SET NOCOUNT ON” tells the stored procedure not to print how many rows were returned. SET ANSI_WARNINGS OFF tells the stored procedure not to print a warning that nulls were excluded from group by operations. “READ UNCOMMITTED” is important. It tells the stored procedure to perform dirty reads, preventing shared read locks. As we typically update the cube only once a day –- it doesn’t make sense to have shared locks. Shared locks would only make a web user wait, until the previous web user finishes performing his query.
Let’s go through the parameters:
@region. Possible values are: All, North, East, South, & West.
@empid. Possible values are: All, 1, 2, 12, 21… Note the @empid is varchar while the empid column is an INT.
@startdate, @enddate. Possible values are any datetime value.
@method is my attempt to add Object Oriented programming to Transact-SQL. For example, suppose we want not only to sum sales, but also to group sales. We can create another stored procedure. Or we can simply add another “method”. Notice each method code block has a “return” command. This tells the stored procedure to exit at that point.
LIKE Operator. The sales manager wants to be able to select “West” region or select ‘All’ regions. We can accomplish this by using dynamic web scripting, dynamic SQL, or by the LIKE operator. The LIKE operator is straightforward to use and is standard SQL. For example, we can code the WHERE statement as “where region like @region”. If @region=”west”, this is interpreted as “where region like ‘west’”. If @region=”%”, this is interpreted as “where region like ‘%’” which returns all rows. To use the LIKE operator, we need to reset @region to “%” when it is “ALL”. We do that by “select @region=case when @region=’all’ then ‘%’ else @region end”. Finally, note that the @empid variable is varchar, but the empid column is an integer. The statement “where empid like @empid” does an implicit conversion of empid into varchar then compares it to @empid. I have had cubes w/ over 10 million rows and rely on this implicit conversion w/ little adverse affect. If you’re worried about it, you can always convert all INT to varchar as you populate the cube.
Here are some results:
exec CubeSp @region =’west’ — returns 64
exec CubeSp @region =’all’ — returns 138
exec CubeSp @empid =’21’ — returns 4
exec CubeSp @empid =’all’ — returns 138
The sales manager can now search the cube by region and empid. Now he wants to group the results. That is he wants a breakdown by region and/or empid and filter the data at the same time. We alter our CubeSp and add a new method code block “groupSales”. In our web page, we implement the group functionality by input checkboxes.
<Our input checkboxes>
<input type=”Checkbox” value=”Region” name=”group”>Region
<input type=”Checkbox” value=”Empid” name=”group”>EmpID
</Our input checkboxes>
For example, he would check Region and EmpID, if he wants to group by both. Because the input checkboxes have the same name, there is only one “Group” variable and its value is “Region,EmpID”. If he only checked region, the value would be “Region”. Note that in html, if no box was checked, there would be no Group variable –- it is undefined. In that case, use web scripting to default the value to “All”. We then pass the value to the @Group parameter of our altered stored procedure.
<Our Reporting Stored Procedure>
ALTER PROCEDURE CubeSP
(
@method varchar(50)=’groupSales’ –:sumSales, groupSales
,@region varchar(50)=’all’
,@startdate datetime=’1900/1/1′
,@enddate datetime=’3000/1/1′
,@empid varchar(50)=’all’
,@group varchar(5000)=null
)
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
if @method=’groupSales’ begin
select region,empid,sumSales=sum(N)
from
(
select
region=case when patindex(‘%region%’,@group)>0 then region else ” end
,empid=case when patindex(‘%empid%’,@group)>0 then empid else 0 end
,n
from cube
where [date] between @startdate and @enddate
and region like @region
and empid like @empid
) as a
group by region,empid
return
end
</Our Reporting Stored Procedure>