Posted in PerformancePoint

Building multiple caches for PerformancePoint

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” ‘

Advertisements

Author:

I'm a Business Intelligence Developer working for a financial services company. My focus is on the Microsoft suite of BI tools.

One thought on “Building multiple caches for PerformancePoint

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s