I work for an online media company which allows our customers to login to a website and check their numbers. For the last several years all their activity reports have been pointed at SQL Server aggregated tables. While this worked, it wasn’t the ideal solution. Reports often timed out and the database server would have a high load throughout the day, specifically early month while everyone was checking month end numbers.

 

Eventually my boss approached me about speeding up the reports and possibly pointing them at a cube. However, there could be no changes to the report files or how the data was being called (no front end changes). All the modifications had to be done in the stored procedure.

 

After initial testing, by pointing at cubes rather than aggregated tables, most of the reports would be sped up by a factor of 5-10x. A definite improvement and well worth the work involved.

 

Here are the steps I had to take in order for it to happen:

 

1. Read http://support.microsoft.com/kb/931192 and install the hotfix on your dev box or desktop. You don’t have to do this if you’ll be working directly on the server.

 

2. Follow the steps here: http://jesseorosz.spaces.live.com/blog/cns!E322FD91218E57CF!329.entry. Double hop authentication is funky with SSAS.

 

3. Create a linked server on the same server the stored procedure resides. Use the fully qualified domain name (not optional).

Example:

/****** Object:  LinkedServer [MYCUBESERVER_SalesCubeDatabase]    Script Date: 02/12/2008 13:17:57 ******/

EXEC master.dbo.sp_addlinkedserver @server = N’MYCUBESERVER_SalesCubeDatabase’, @srvproduct=N’cubes’, @provider=N’MSOLAP’, @datasrc=N’MYCUBESSERVER.IPADDY.COM’, @location=N’MYCUBESSERVER.IPADDY.COM’, @catalog=N’Sales Cube Database’

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MYCUBESERVER_SalesCubeDatabase’,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’collation compatible’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’data access’, @optvalue=N’true’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’dist’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’pub’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’rpc’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’rpc out’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’sub’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’connect timeout’, @optvalue=N’0′

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’collation name’, @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’lazy schema validation’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’query timeout’, @optvalue=N’0′

GO

EXEC master.dbo.sp_serveroption @server=N’MYCUBESERVER_SalesCubeDatabase’, @optname=N’use remote collation’, @optvalue=N’true’

 

 

4. If your database server is running SQL Server 2000 and you are pointing at a 2005 OLAP database (which was our case), you’ll need to install the 9.0 OLAP provider on that box.

 

5. Here’s a generic example of what the stored procedure looks like (removed error trapping, nocounts, ect)

 

CREATE  PROCEDURE [dbo].[MsrsWebsiteAdCampaignHitsPerDay]

  @CustomerID INT,

  @BeginDate DATETIME,

  @EndDate DATETIME

 

AS

 

–Generate the dynamic SQL

DECLARE @sqlcode VARCHAR(5000)

SELECT @sqlcode =

‘

SELECT

       "[Date].[Date].[Day].[MEMBER_CAPTION]" AS ActivityDate

       ,’ + (CAST(@CustomerID AS VARCHAR(10))) + ‘ AS CustomerID

       ,"[Sales].[Customer to AdCampaign].[AdCampaign].[MEMBER_CAPTION]" AS AdCampaign

       ,CAST(convert(float, "[Measures].[Hits]") AS int) AS Hits

       ,CAST(convert(float, "[Measures].[Cost]") AS money) AS Cost

 

FROM Openquery(MYCUBESERVER_SalesCubeDatabase,

”

       SELECT NON EMPTY { [Measures].[Hits], [Measures].[Costs] } ON COLUMNS ,

       NON EMPTY { { { DESCENDANTS( [Sales].[Customer to AdCampaign].[Customer].&[‘ + (CAST(@CustomerID AS VARCHAR(10))) + ‘], [Sales].[Customer to AdCampaign].[AdCampaign] ) }  * { [Date].[Date].[Day].[‘ + @BeginDate + ‘]:[Date].[Date].[Day].[‘ + @EndDate + ‘] } } } ON ROWS 

       FROM [Sales]

”)

‘ 

 

–Create the temp table to insert the data produced by the MDX

CREATE TABLE #AdCampaignHitsPerDay

(

       ActivityDate NTEXT,

       CustomerID INT,

       AdCampaign NTEXT,

       Hits BIGINT,

       Cost MONEY NULL

)

 

–Insert the data

INSERT #AdCampaignHitsPerDay  EXEC(@sqlcode)

 

–Select for the MSRS report

SELECT

       CAST(CAST(ActivityDate AS VARCHAR(100)) AS SMALLDATETIME) AS ActivityDate,

    CAST((CAST((MONTH((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100)) + ‘/’ + CAST((day((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100))) AS VARCHAR(61)) AS [Date],

       YEAR((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))) AS [YEAR],

       MONTH((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))) AS [MONTH],

       CAST(AdCampaign AS VARCHAR(50)) AS AdCampaign,

       SUM(Hits) AS Visitors,

       SUM(Cost) AS Cost

FROM

       #AdCampaignHitsPerDay  

WHERE

       Hits > 0     

GROUP BY

       CAST(AdCampaign AS VARCHAR(50)),

       YEAR((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))),

       MONTH((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))),

       (CAST(CAST(.ActivityDate AS VARCHAR(100)) AS SMALLDATETIME)),

       (CAST((CAST((MONTH((CAST((CAST(.ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100)) + ‘/’ + CAST((day((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100))) AS VARCHAR(61)))

ORDER BY

       AdCampaign, ActivityDate

ORDER BY

       AdCampaign, ActivityDate

 

 

DROP TABLE #AdCampaignHitsPerDay

 

exit_procedure:

 

 

6. That’s really about it. 90% of the time was spent just trying to get the double hop authentication working.