Ad hoc Mini Mart

If you need to improve performance for complex reports, think about implementing a minimart on top of your datamart.

On of my customers was specifically interested in analysing linehauls with a certain origin and a certain destination.

I created a stored procedure that can be kicked off via a cognos report.

CREATE procedure [dbo].[pLoadVolumeAnalysisMiniMart]
@origin varchar(6)
, @destination varchar(6)
as
declare @loaddate datetime
set @loaddate = getdate()

truncate table VolumeAnalysis MiniMart

insert into VolumeAnalysisMiniMart
select
,Sector
,Sectortype
,Week
,Weight
,Pieces
,Sender
,Receiver
,Origin
,Destination
,Service
,Departuretime
,Arrivaltime
,@loaddate
,Runid
,Rundate
from VolumeAnalysisDataMart
where Origin = @origin
and Destination = @destination

select 'Mini Mart has been loaded' as Result

end

This procedure loads a table with a specific origin and a destination (in casu the number of records decreased from 21 million to several thousands). The dataload takes about 1-2 seconds.

In framework manager I created a star schema with the mini mart as a fact table and aliases of the (original) dimension tables.

I also added the stored procedure as query object:


call pLoadReportPromptSector;1(
#prompt('origin')#,
#prompt('destination')#
);


In Report Studio I created a query with data item 'Result', prompts 'Origin' and 'Destination'.
As soon as the parameters are filled out, the procedure will be kicked off and the data will be loaded.

I also created hyperlink buttons on the report that links the user to several reports which retrieve the data from the minimart.

If you do not have schema's for each user, you can create separate tables or add column 'user' with #account. You can pass this variable to the procedure, so every user has it's own set of data.

See also:

http://www.sqlserverclub.com/pdfs/minimarts-organizational-segmentation-in-datawarehousing.pdf :

Many decisions makers not only need to analyze data from within the business unit,
but also across business units or the many segments within a business unit. Think of
it as market segmentation – cubes for more a selective audience.
Your customers can be grouped by segments of business units or smaller functional
groups with unique needs for reporting. Most segments will contain unique, pertinent
subsets of data along with business unit-wide data for decision making. Therefore,
cubes can be made from subsets of cubes in what I would call “minimarts.” These
are datasets that can be made “precise” for more targeted queries thereby
increasing performance, especially across a network, intranet, extranet (or even the
general public internet).
The pros are pretty clear by targeting your audience. Performance will be greater
than querying vast cubes of data that can go unused for most members of your
customer base.

How to calculate running totals \ subtotals (like YTD)

If you need to calculate running totals \ subtotals (i.e. year-to-date figures) you can use
a lot of methods (like CTE's). In SQL Server 2008 you can use the GROUP BY ROLLUP function.

However, if you are on 2005 the most efficient way to do this:



is running a query like:

declare
@value float
, @prevorganisationalunitid int
, @prevperiodyear int

update FACT_MEASURE
set @value = valueYTD =
case
when
Year = @PrevPeriodYear
or OrganisationalUnitid = @prevorganisationalunitid
then @value+isnull( value ,0)
else value
end
, @PrevPeriodYear = Year
, @prevOrganisationalUnitid = OrganisationalUnitid


This script only works if the sorting is correct
(order by measure,organisationalunitid,periodid).
To ensure this,
create a primary key on periodid, organisationalunitid and measure.