SSAS Cache Warming Using SSIS

by Allan Mitchell 31 Oct 2008 08:22

Having your users walk into a cold cache on a morning is something that will cause them to have slower running queries initially.  Cache warming takes care of a lot of that by executing typical queries against the newly processed cube and thereby building the cache ready for your users when they get in.  There are a number of ways to warm the cache available.

Chris Webb has already blogged about how he has done it and we thought we would show you a variation on that theme.

UPDATE: The Trace File Source Adapteris now available.

The package is relatively simple.

 

Cache Warm Control Flow

 

Let’s take a look inside the Data Flow task to start with.

 

Cache Warm Data Flow

 

The component TFS_MDX (Trace File Source) reads a Trace file saved from Profiler and puts the data into the pipeline.  The CSPLIT_MDX(Conditional Split) separates out the MDX queries by looking for an EventSubclass value of 0.

 

Cache Warm CSPLIT

 

After we have got our MDX queries separated then we perform a conversion to DT_WSTR() on the TextData column in order to be able to Sort on it and remove duplicates (You cannot sort on TextData as it is a DT_NTEXT datatype).

 

Cache Warm Convert Textdata

 

After we have sorted our data we then terminate our pipeline in a Script Component acting as a destination.  On the property pages of the component we make a reference to our OLEDB connection manager that points to our SSAS server because we are going to use its ConnectionString property inside the component. In the script Component we add a reference to the Microsoft.AnalysisServices.AdomdClient library.  Our script then looks like this

 

Cache Warm Script destination

 

We use ADOMD.NET to execute our MDX queries against our SSAS server.

 

This method should prove useful in preventing users having a cold cache and experiencing slow running queries of a morning.

Comments (16) -

11/8/2008 9:06:01 AM #

rajib

Hi,
I m trying to implement the logic.
but in script task I'm not getting the assembly  ADOMD.NET for Ananlysis Services.
I tried to add through add references but the assembly is not present in the available list.
Can you guide me how to add assembly reference in SSIS Script task.

Regards
Rajib

rajib

11/10/2008 11:48:09 AM #

Allan Mitchell

Hi Rajib

In 2005 adding an external assembly is clucky (it is implemented properly in 2K8).  here you go

The easiest way is to drop the dll into the following folder

c:\Windows\Microsoft.Net\Framework\v2.0.50727

Allan Mitchell

11/18/2008 2:39:09 PM #

David Cleaves

Hi Allan,

As it is a standard procedure in SSAS to log into OlapQueryLog table for tuning purposes. Can we derive from the Dataset field the necessary input for warm caching?

Regards

David

David Cleaves United Kingdom

11/18/2008 9:11:11 PM #

Allan Mitchell

Hi David

Technically yes you can derive the statement from the results of this column.  it is not though for the faint hearted and needs work to make it happen.  Our good friend Chris tells us about it here:

chriswebb.findtechblogs.com/default.asp

Allan Mitchell

2/11/2009 10:42:27 PM #

cosmin

Allan,
the Trace File Source Component... where is that available from (SQL2008)?  I've looked in the Toolbox .NetFramework Components and the only component there.... "Trace" (for AS 10) ... when added... did not bring up anything).

Is this a custom component that you guys wrote?
thx

cosmin

2/12/2009 6:28:47 AM #

Allan Mitchell

Yes the Trace File source component is a component we have written.  It is something we are going to get out to the community ASAP though once we get a bit of time.

Allan Mitchell Germany

4/30/2009 6:46:16 PM #

faith

Save the trace file as table in the database and use this table.
If you don't want to use script task use Execute SQL task along with in a  foreach container where the MDX query is passed from another Execute SQL task.

faith United States

4/30/2009 10:54:16 PM #

Allan Mitchell

The TFS is a custom component written by us but will be available very very soon (watch the site).  So the issue with saving to a table in the DB is that the size of the column may not be able to hold the right length of data coming from the trace.  During testing we found that ObjectPath for instance was being created as 176 but the values held in the column when read through the API were far INXS of that.  The data was silently truncated.

Allan Mitchell United Kingdom

5/7/2009 2:40:39 PM #

Darren Green

See the update note, the Trace File Source is now available - www.sqlis.com/post/Trace-File-Source-Adapter.aspx

Darren Green United Kingdom

8/1/2009 10:40:05 PM #

Peter

Great solution.
Works well with non-parameterized queries. What's a good way to deal with MDX queries generated by SSRS(lots of parameters)?

Peter

7/1/2010 2:42:22 PM #

Dwain

Hi Alan

Is this only for 2008 as the install files go to the 100/DTS folder

downloading www.sqlis.com/post/Trace-File-Source-Adapter.aspx mentions that its for 2008

is there a 2005 install floating around somewhere ???

Dwain United Kingdom

7/6/2010 12:30:56 PM #

Darren Green

Dwain, I'm afraid the Trace File Source is for SQL Server 2008 only.

Darren Green United Kingdom

8/18/2010 7:55:33 PM #

Stephen

WHere can i download the package you built? This is perfect for what i'm looking for but didnt want to built it if you already have it available for download.

Stephen United States

12/14/2010 4:48:27 AM #

Anil Maharjan

hello,
I also try to implement as similar to this blog but couldnot get the solution excatly some error occurrs.
So,i am also wondering where we can download the package you built,can't we use the package that you already built.Hope you will help us out..!!!
Also i am new to SSIS so could some one suggest me some blog\links from where we can learn SSIS basic details.
hope some one help me out... :)

Thanks
Anil Maharjan

Anil Maharjan Nepal

2/23/2011 4:32:59 PM #

Sam Kane

Here are other articles on Cache Warming: http://ssas-wiki.com/w/Articles#Cache_Warming

Sam Kane United States

8/9/2011 10:21:08 AM #

sankar

Hello,
I am also try to implement as similar to this blog  but I couldn't get the solution exactly. Few error may occurs.

sankar India

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS