Background
Users are willing to wait a short amount of time for results to come back from reports, but their expectations are a lot higher when it comes to dashboards – these need to be almost simultaneous.
We had to create 4 dashboards, each with 7 pages and each page with 3 or 4 scorecards.
These scorecards have 4 KPIs and the user needs to be able to drilldown to 3 static levels of attributes.
Even though the data is sourced from Analysis Services, the response time was sluggish, causing complaints.
Warming the Analysis Services Cache.
One method that we tried was to capture the MDX statements using SQL Server Profiler and use SSIS to fire these against the cube each morning. ( Chris Webb has a good article here )
This made things a lot faster, but it still wasn’t optimal.
Warming the PPS and IIS Caches
When a user opens a dashboard there are a number of cache’s that are built , so that subsequent executions return results quickly. Here’s an article that nicely explains the different caches for PPS 2010
Capture POSTs
I used a free utility called Fiddler to capture the POSTs that were being sent to Sharepoint .
These I then saved to a text file.
Send POSTs via SSIS
Next, I used a Script Task in SSIS to send each POST to the server.
public
void Main()
{
//Table
of Dashboards that need to be warmed.
List<string>
dashboardPosts = new List<string>();
string
requestFullName =
Dts.Variables[“RequestFullFilename”].Value.ToString();
string
webRequestURL = Dts.Variables[“WebRequestURL”].Value.ToString();
dashboardPosts
= stripPostsFromFile(requestFullName);
string
postData;
byte[]
byteArray;
int
PostCount = 0;
int
TotalPosts = dashboardPosts.Count;
bool
eventFireAgain = true;
foreach
(string dPost in dashboardPosts)
{
PostCount++;
//
Create a request using a URL that can receive a post.
WebRequest
request = WebRequest.Create(webRequestURL);
//Use
current credentials for the request
request.UseDefaultCredentials
= true;
request.PreAuthenticate
= true;
request.Credentials
= CredentialCache.DefaultCredentials;
request.Timeout
= 400000;
//
Set the Method property of the request to POST.
request.Method
= “POST”;
//
Create POST data and convert it to a byte array.
postData
= dPost;
byteArray
= Encoding.UTF8.GetBytes(postData);
//
Set the ContentType property of the WebRequest.
//request.ContentType
= “application/x-www-form-urlencoded”; //PPS 2007
request.ContentType
= “application/json”;
//
Set the ContentLength property of the WebRequest.
request.ContentLength
= byteArray.Length;
//
Get the request stream.
Stream
dataStream = request.GetRequestStream();
//
Write the data to the request stream.
dataStream.Write(byteArray,
0, byteArray.Length);
//
Close the Stream object.
dataStream.Close();
Dts.Events.FireInformation(1,
“Dashboard
Cache Build”,
“Post
”
+
PostCount.ToString() + “/” + TotalPosts.ToString(),
“”,
0,
ref
eventFireAgain
);
try
{
//
Get the response.
WebResponse
response = request.GetResponse();
//
Get the stream containing content returned by the server.
dataStream
= response.GetResponseStream();
//
Open the stream using a StreamReader for easy access.
StreamReader
reader = new StreamReader(dataStream);
//
Read the content.
string
responseFromServer = reader.ReadToEnd();
//
Clean up
dataStream.Close();
reader.Close();
response.Close();
}
catch
(Exception ex)
{
Dts.Events.FireInformation(
1,
“Exception
: ” + ex.ToString() ,
“Post
“
+
PostCount.ToString() + “/” + TotalPosts.ToString(),
“”,
0,
ref
eventFireAgain
);
continue;
}
}
Dts.TaskResult
= (int)ScriptResults.Success;
}
static
List<string> stripPostsFromFile(string fullFilename)
{
List<string>
posts = new List<string>();
using
(StreamReader rd = new StreamReader(fullFilename))
{
string
line;
while((line
= rd.ReadLine()) != null)
{
//if
(line.StartsWith(“dashboardId=”)) //PPS 2007
if
(
line.StartsWith(@”{“”renderRequestRecord”””
)
)
{
posts.Add(line);
}
}
}
return
posts;
}
}
The only difference between PPS 2007 and PPS 2010 is the content type and how the body of the POST begins:
Content Type
2007 – “application/x-www-form-urlencoded”
2010 – “application/json”
POST start
2007 – ‘ dashboardId= ‘
2010 – ‘ {“renderRequestRecord” ‘
BI ChopSuey 