How to Warm AS Cache using SSIS Package

By following below mentioned steps we can warm AS Cache using SSIS Package by using Process Execute & Analysis Services Execute DDL Task

-You need to get ascmd for SQL 2008 Analysis Services (https://www.codeplex.com/MSFTASProdSamples) or if you want to warm cache for SQL 2005 then you need to get ascmd version for SQL 2005 (https://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2008%21Readme%20For%20Command-line%20Utility%20Sample&referringTitle=Home)

- Using ascmd command you need to create a Batch File
(d:\ascmd -S karan2k8-64\sql2005 -i d:\test.mdx),
(-S is meant for server name )
(-i for input file which in our case is mdx file where MDX statement is specified)
-Open SSIS Package
-Add Process Execute Task
-Go to the properties of Process Execute Task and provide the location of batch file as Executable
-Ensure that files are located in one directory and right paths had been specified else you will get the error message.

Note:

If you like to clear cache before running ascmd command in that case follow below mentioned steps:

-Add Analysis Services Execute DDL Task in Control Flow of Package & Double Click on it
-In DDL Page provide Server Connection
-In Source Direct type below mentioned xmla:
<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<!-- Replace Database ID and Cube Id-->
<ClearCache>
<Object>

      <DatabaseID>Adventure Works DW 2008</DatabaseID>

      <CubeID>Adventure Works</CubeID>

    </Object>
</ClearCache>
</Batch>

-Drag Connection from Analysis Services Execute DDL Task to Process Execute task which we had created earlier.

This will ensure that Cache will be Cleared before executing Process Execute Task

Verify Create Cache is Effective

The goal of using Create Cache is to isolate the storage engine and formula engine work and prevent the user query from executing subcube commands. The SE is multi threaded when the FE is single threaded. Therefore, one can just use Task Manager to verify if the SE is running.

During a good run, initially all the CPUs are at 100% since the SE is busy with the Create Cache command. Then, only one CPU reaches 100% because the FE is busy with the formulas and preparing the result set.

Task Manager is the general way to verify, but to be sure, create a trace and look for event Query Subcube, subevent 2 Non Cache. The cache subcube queries are fast and generally not an issue for AS. (In fact it shows how effective it is to create a cache first.)

 Remark:

The Create cache statement will pre-load data from the fact. If you want to pre-load any calculated members, please pre-load the base measures that are used by the calculated members instead. Otherwise, if you include calculated members, you will receive the following exception error:

create cache for [Adventure Works]

as

[Measures].[Internet Gross Profit]

Error:

Executing the query ...

Operation was aborted. The set specified for creating cache contains caculated member.