rmoff

October 1, 2009

Performance testing OBIEE using HP Performance Center (a.k.a. LoadRunner)

Filed under: loadrunner, obiee, performance — rmoff @ 10:26

My two earlier posts (here and here) detail the difficulties I had with LoadRunner (now called HP Performance Center). After a bit of a break along with encouragement from knowing that it must be possible because it’s how Oracle generates their OBIEE benchmarks I’ve now got something working. I also got a useful doc from Oracle support which outlines pretty much what I’ve done here too.

In essence what you do – and what the Metalink document 496417.1 states – is you use the Web (HTTP/HTML) protocol with URL-mode.

Here’s details about how I created a VUser script for testing OBIEE.

How-To

Recording Overview

Here’s how you’d record a simple login – run dashboard – logout scenario:

  • Create a new VUser of Protocol type Web (HTTP/HTML) [File -> New… -> New Single Protocol Script -> Web (HTTP/HTML)]
  • Create a new VUser of Protocol type Web (HTTP/HTML) [File -> New... -> New Single Protocol Script -> Web (HTTP/HTML)]

  • Set Options as detailed in the “Recording options” section below
  • LR02

  • Set action to vuser_init
  • LR05

  • Record login
  • LR06

  • Set action to Action
  • LR07

  • Record click to dashboard
  • LR08

  • Set action to vuser_end
  • LR09

  • Record logout
  • LR10

  • Save!

Press F5 to test the replay. A browser window should pop up showing the resulting pages, if it doesn’t go to Tools -> General Options -> Display -> Show browser during replay.
Instead of F5 to play the whole scenario you can step through using F10.

Recording options

Most of these are set from Tools -> Recording Options, or from the Options button on the “Start Recording” window.

  • Protocol: Web (HTTP/HTML) [this is defined when you create your VUser script]
  • Recording HTTP / HTML Level: URL-based scriptLR49
  • HTTP Properties -> Advanced -> Recording schemes -> Content Types, set to “Exclude content types in list” and set the list to:
    • text/css
    • image/gif
    • image/png
    • image/jpeg
    • application/x-javascript
    • application/x-shockwave-flash

    LR50LR51

  • Copy this to a file called obiee.cor:
    <?xml version="1.0"?>
    <CorrelationSettings><Group Name="OBIEE" Enable="1" Icon="logo_bi.bmp"><Rule Name="scid" LeftBoundText="_scid=" LeftBoundType="1" LeftBoundInstance="0" RightBoundText="&quot;" RightBoundType="1" AltRightBoundText="" AltRightBoundType="1" Flags="0" ParamPrefix="" Type="8" SaveOffset="0" SaveLen="-1" CallbackName="" CallbackDLLName="" FormField="" ReplaceLB="" ReplaceRB=""/></Group></CorrelationSettings>
    

    Import this correlation file (see notes here for information about correlation) by going to Tools -> Recording Options -> Correlation -> Import. Optionally you can include an icon by downloading this image logo_bi and converting it to bmp and saving it to C:\Program Files\HP\Virtual User Generator\dat\webrulesdefaultsettings\icons
    LR52
    After importing the correlation file, untick all other applications in the correlation list except for OBIEE

Parameterisation

Using parameterisation we can change the action that was recorded navigating to a specific dashboard to instead navigate to any dashboard we want. Herein lies the power of the tool because complex and realistic loadtests can be created from a few carefully crafted building blocks.

Rename “Action” to “Navigate_to_dashboard” (Just change the name in the code, or right-click on the Action in the left-hand view and click Rename Action).

To parameterise, select the whole string (in this case “/shared/Sample Sales/_portal/02 History & Benching”), right-click and select “Replace with a parameter”.
LR17
Select a File parameter, and click Properties.
LR18
Click “Create Table”, and the value that you’d selected to “Replace with a parameter” should be added as the first entry.
Populate the table with Add Row, or Edit with Notepad, to add the remaining Dashboard names.
LR19
Set “Select next row” to Random, and then click on “Simulate Parameter” to see an example of the parameter value that will be picked on each iteration.
LR20

Your code should now look something like this:


Navigate_to_dashboard()
{

	web_submit_data("saw.dll_2",
		"Action=http://10.3.105.181:7777/analytics/saw.dll?Dashboard",
		"Method=POST",
		[...]
		ITEMDATA,
		[...]
		"Name=PortalPath", "Value={Dashboard}", ENDITEM,
		LAST);
	return 0;
}

On the “return 0;” line press F9 to insert a breakpoint, and then press F4 to bring up the runtime settings. Set number of iterations to 5 (or enough to cycle through the dashboards), and then click OK.
LR21
Press F5 to run and make sure on each breakpoint the dashboard has loaded (if the browser doesn’t load up then go to Tools -> General Options -> Display -> Show browser during replay). Press F5 to continue past the breakpoint.

Record new action – Navigate to report

We can now add a new function, navigate to a report within a dashboard. To do this click the Start Record button, untick “Record the application startup” and create a new Action by clicking New…
LR22
Recording will start paused (because we’ve already got a login and dashboard navigate script, we don’t need to record another).
LR23
Login and navigate to a dashboard, and then click “Record”.
Click on a dashboard report tab, and ensure that the recording windows shows and increased number of events captured.
Once the report’s loaded, click the Stop button on the record toolbar.
If you’ve not set exclude content types in recording options then you may need to strip out the static content and web_url calls to saw.dll?DocPart&_scid=faN65Op1PFg&StateID=18814381 which are the hardcoded unique chart IDs and no use to us.

You should have a single web_submit_data call, with ITEMDATA for PortalPath and Page representing the Dashboard and Report respectively.
The Dashboard string should be replaced with the existing Dashboard parameter (select the whole string after Value= and right click, this time select “Use Existing Parameter”)
LR24
We now need to expand the parameter Dashboard to include the Reports within each dashboard (Dashboard:Report being a 1:* relationship). Press Ctrl-L to bring up the Parameter List and select the Dashboard parameter. Add a Column called Report. LR25
Click Edit in Notepad (or load it into your favourite text editor).
For each dashboard repeat the line and add the report names next to it, thus:
LR26
Save the file and check that it is loaded correctly into the Parameter window
LR27
Click New on the left of the Parameter Window and give the parameter a name of Report. Click File path and set it to Dashboard, and set “Select column” “By number” to 2. Set Select next row to “Same line as Dashboard”:LR28

Now highlight the report string in the script after Value= and chose “Replace with an existing parameter” and select Report.
Set “return 0;” as a breakpoint line, and in Run-time settings (F4) increase the number of iterations to a handful.
Press F5 to run the VUser and check in the browser window that the reports screens are loaded correctly.

Record new action – Drilldown

Let’s now record the action required for drilling on a report. Following the same steps as when we added the report navigation Action;
Click the Start Record button, untick “Record the application startup” and create a new Action by clicking New…LR29
Login and navigate to a dashboard report and then click “Record”.
In this example I was on “01 Ranks & Toppers” dashboard with the default report “11 Multi Dim TopNs”.
I clicked on the value of Product 04 / Market 5 (657,882) and then “History Filtered”.
Once the new report’s loaded, click the stop button on the recording toolbar.
Remove the think time and web_url statements from the generated script if necessary.
Examine the web_submit_data statement data, after the ITEMDATA line:

  • PortalPath and Page is what we’d expect – the Dashboard and the Report within it.
  • P1 and Action have values of “dashboard” and “Navigate” respectively, so kind of obvious but it would be interesting to know the other permutations
  • P19 is either a unique ID or encoded value.
  • P0 is interesting, and discussed next.

P0 is XML with backslash-escaped quotation marks (i.e. search and replace \" for "), and LoadRunner script breaks it over several lines. For each line break remove the double quotation marks either side, eg this:

[...]   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">"D4 "
	"Product"."P01  Product"</sawx:   [...]

becomes this:

[...]   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">"D4 Product"."P01  Product"</sawx:   [...]

The resulting XML after formatting looks like this, and is fairly self-explanatory. The data is the predicate for the report – an AND statement, specifying the values of “D4 Product”.”P01 Product” and “D2 Market”.”M01 Market”.

<sawx:expr xsi:type="sawx:logical" op="and">
<sawx:expr xsi:type="sawx:comparison" op="equal">
 <sawx:expr xsi:type="sawx:sqlExpression">"D4 Product"."P01 Product"</sawx:expr>
 <sawx:expr xsi:type="sawx:untypedLiteral">Product 04</sawx:expr>
 </sawx:expr>
<sawx:expr xsi:type="sawx:comparison" op="equal">
 <sawx:expr xsi:type="sawx:sqlExpression">"D2 Market"."M01 Market"</sawx:expr>
 <sawx:expr xsi:type="sawx:untypedLiteral">Market 5</sawx:expr>
 </sawx:expr>
 </sawx:expr>

XML Notepad is useful for this kind of work. You can paste the single-line XML statement and on the XSL Output tab see the reformatted version.

Since the values passed to the report are exposed in this manner, we can … guess what …. parametrise them!
In the script remove the line breaks and extraneous double quotation marks as describe above so that P0 is all on one line. Then replace “Product 04” and “Market 5” with new File-based parameters (see above for details) for Product and Market respectively

Original:

“Name=P0”, “Value=<sawx:expr v1 “>expr “>expr “>xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xmlns:XMLSchema-instance “>xsi=\”http://www.w3.org/2001/XMLSchema-instance\” xsi:type=\”sawx:logical\” op=\”and\”><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:comparison\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\” op=\”equal\”><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:sqlExpression\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>\”D4 Product\”.\”P01 Product\”</sawx:expr><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:untypedLiteral\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>Product 04</sawx:expr></sawx:expr><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:comparison\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\” op=\”equal\”><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:sqlExpression\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>\”D2 Market\”.\”M01 Market\”</sawx:expr><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:untypedLiteral\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>Market 5</sawx:expr></sawx:expr></sawx:expr>”, ENDITEM,

Parameterised:

“Name=P0”, “Value=<sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xmlns:v1 “>xsi=\”http://www.w3.org/2001/XMLSchema-instance\” xsi:type=\”sawx:logical\” op=\”and\”><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:comparison\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\” op=\”equal\”><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:sqlExpression\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>\”D4 Product\”.\”P01 Product\”</sawx:expr><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:untypedLiteral\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>{Data_Product}</sawx:expr></sawx:expr><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” v1 “>xsi:type=\”sawx:comparison\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\” op=\”equal\”><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:sqlExpression\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>\”D2 Market\”.\”M01 Market\”</sawx:expr><sawx:expr xmlns:sawx=\”com.siebel.analytics.web/expression/v1\” xsi:type=\”sawx:untypedLiteral\” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\”>{Data_Market}</sawx:expr></sawx:expr></sawx:expr>”, ENDITEM,

At this stage we can do something funky with the Parameters, discussed in the next section.

Parameters sourced from data

Up until now we’ve been dealing with dashboard and report names which are generally going to be fairly static and easy to derive. What about actual data though? We don’t want to have a load test based on one set of drill parameters, because what kind of test would that be. Nor do we want to have to sit and type out hundreds of permutations of data. Instead, let’s populate our parameter with real data.

This next bit assumes that you’ve got an ODBC system DSN set up to your BI Server that you’re load testing. Go and do that now if you haven’t.

In the Parameter List window (Ctrl-L) select the parameter, in this case Data_Market, and then click Data Wizard.
LR30
On the Database Query Wizard if you’re hard-core then you can click on “Specify SQL statement manually” but mere-mortals should leave the default “Create query using Microsoft Query”.
LR31
Chose your Data Source from the list – remember this is your BI Server ODBC connection, it is not your database. As a side-note, you could query the database directly, but you’d then have to work out which database table and column corresponded to the column in the Presentation Layer that you’re putting the predicate on. So you may as well just use the BI Server.
LR32
Click OK and then specify your login credentials for the RPD.
LR33
Check the Database corresponds to the Subject Area (you might get an error that it could not be accessed – I’ve ignored it so far without problem). Now pick the “Table” that your parameter is from, in this case Market is under “Other Dimensions”. Click Add and then Close.
LR34
You should now have a Microsoft Query window with a table showing. Find the item in the table that corresponds to the parameter you’re populating, and double click it. It’ll be added to the lower pane and all its values shown.
LR36
You can now click the exit icon (fourth from the left, a door with an arrow pointing left) LR37 or toolbar menu “File” -> “Exit and return to HP Virtual User Generator”. The data should be listed in the Parameter window list (and in the corresponding .dat file).
LR38

If you have data that is related then you could build a query here to populate a single .dat parameter file. In this example I’ve create a simple cartesian product just to demonstrate the concept, but if you had specific relationships such as Customers to Orders then this would be very relevant.
LR39
The multiple columns of data returned now populate a single .dat file, which you should name appropriately. If you’ve put Product as a separate parameter already then delete it, create it again and set it as a parameter type File with a File Path of the same .dat file as Market. Set the column number correctly so that it picks the data up. Note that BI Server’s Presentation Layer returns variable names with double quotes which disturbs the CSV format of the parameter .dat file and confuses LoadRunner’s dialog:
LR40
After populating the file click “Edit with Notepad” and fix the column headings by changing this:
LR41 to this: LR42.
LoadRunner doesn’t seem to like double quotations at all, so don’t use them.
Close the Parameter List window and re-open it to get it to pick the file change up properly:
LR43
So now the variables are set up thus:

Data_Market
Type: file
File: data.dat
Select column, by name: Market
File format: Comma
First data line: 2
Select next row: Random
Update value on: Each occurance

Data_Product
Type: file
File: data.dat
Select column, by name: Product
File format: Comma
First data line: 2
Select next row: Same line as Data_Market

NB If you’ve recreated any of your parameters by deleting and recreating them make sure the Parameter is still in your script, as LoadRunner will replace it with the original value when you delete the parameter.

Set “return 0;” as a breakpoint and update the run-time settings to run the new Drill action multiple time. Under run-time settings -> Log set Extended Log and tick Parameter Substitution so you can check the parms are working. Execute the VUser and check that you’re getting correctly parametrised drills.
LR44
Make sure that as well as seeing the parameters working you are actually getting the correct drill, by looking at the Run-Time Viewer (Tools -> General Options -> Display -> Show browser during replay). Compare the screen with that of when you manually navigate to the drill you’ve performed. The charts won’t display but assuming there’s some text content to the report it should show up correctly.

view from the Run-Time viewer

view from the Run-Time viewer

view from running the report manually

view from running the report manually

Be aware that you may get this message showing on your dashboard:
LR45
This is to do with session IDs and correlation, of which see below.

Think time

Once your basic script is ready you should add in some think times. Think time is a simulation of the user thinking (or staring gormlessly at the girl from HR šŸ˜‰ ), and is important to a realistic performance test. In performance testing OBIEE we’re not trying to see how many MIPS we can thrash out of it, we’re trying to judge how the system would perform with certain volumes of user activity.

The syntax is :
lr_think_time(30);
where 30 in time in seconds. Don’t forget the trailing semi-colon.

I added a think time of five seconds prior to a report or drill click, and 30 seconds afterwards.

In the run-time settings think time can be altered or ignored. I’ve set it to use a random amount between 10% and 150% of that defined.
LR53

Parametrising OBIEE usernames

It makes sense to parametrise logins so that you simulate many users (rather than many instances of one user).
This will be in the vuser_init step. Change:

[...]
		ITEMDATA,
		"Name=NQUser", "Value=Administrator", ENDITEM,
		"Name=NQPassword", "Value=Administrator", ENDITEM,
[...]

to

[...]
		ITEMDATA,
		"Name=NQUser", "Value={Username}", ENDITEM,
		"Name=NQPassword", "Value={Password}", ENDITEM,
[...]

To add a set of many users to your repository you can use UDML. For more info on repository manipulation see here and here.

USING UDML IS NOT SUPPORTED BY ORACLE. USE AT YOUR OWN PERIL!!

  1. Add a single test user to your repository, with a known password.
  2. Use NQUdmlGen.exe to generate UDML of the repository
    c:\OracleBI\server\Bin\nQUDMLGen.exe  -U Administrator -P Administrator -R c:\OracleBI\server\Repository\samplesales.rpd -O c:\scratch\samplesales.udml.txt
  3. Search the resulting UDML file for your new user, should look something like this:
    DECLARE USER "PerfTestUser_01" AS "PerfTestUser_01" UPGRADE ID 2150312724 FULL NAME {Performance Testing user} PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5FF150B512EE8ED94985E8734986D5553C8F3BEE6EAF9FC34' NEVER EXPIRES
    	HAS ROLES (
    		  "Administrators" )
    	NO STATISTICS
    	DESCRIPTION {Pwd is y0rkshire}
    	PRIVILEGES ( READ);
    
  4. Strip all the line breaks so that it’s on a single line, and replace tabs with spaces
  5. Put it in an Excel file so that the username’s repeated and the rest of the text static
    NB. If you replace “<username>” with \t”<username>”\t (where \t is tab character) then when you paste it into Excel it’ll sort the columning out automatically.LR54
    Copy the resulting UDML to a new file, eg. newusers.udml.txt
  6. Use nQUDMLExec.exe to merge in the new users. Make sure you work on a backup copy of the repository. Whilst you can specify to overwrite the existing RPD, it is prudent to write to a new one and then rename it once you’ve verified it’s all ok.
    c:\OracleBI\server\Bin\nQUDMLExec.exe -u Administrator -P Administrator -I c:\scratch\newusers.udml.txt -B c:\OracleBI\server\Repository\samplesales.rpd -O c:\OracleBI\server\Repository\samplesales.new.rpd
    

    You should get the nicely optimistic “Complete success!!!” message šŸ™‚

  7. Open your new repository (in this example samplesales.new.rpd) in the Administration Tool and admire your shiny new users: LR55
  8. Using the same Excel sheet, create a CSV file for use as a Parameter data file:LR57
  9. In Load Runner, define two new Parameters with type File and using the csv file you’ve just created as the source:
    LR58

And finally …

You should now have the basics of a valid VUser script to run through the load generator and get some numbers.
Bear in mind this article is aimed at getting OBIEE and LoadRunner working together. It does not touch on other crucial aspects of load testing such as:

  • Designing test scenarios
  • Designing and validating repeatable performance tests
  • Monitoring, capturing and analysing the database during the test
  • Monitoring, capturing and analysing the application host server’s vital stats (CPU, disk, memory, etc) during the test

All of these are topics in their own right, and to get any value out of performance testing need to be researched and done properly. Otherwise you end up with geeee ain’t this cool, I can run a thousand users at once! oh, what now …. which is not very scientific and not much use to anyone.

See this supplemental blog post for various notes that I made during this but which aren’t directly part of the step-by-step tutorial.

Advertisements

8 Comments

  1. I was wondering if this approach handles multiple graphs/charts per dashboard. I would like to wrap each graph/chart in the dashboard with a LR transaction. Is this possible. I saw in your doc that I should remove any reference to “hardcoded unique chart ids”. Thanks for any help you can provide

    Comment by rob stephens — November 17, 2009 @ 18:25

    • Hi Rob,
      I’m not sure. You’d have to do some tracing work yourself on this to see how OBIEE handles charts. I instructed to remove the chart IDs because they’re unique each time and so confuse things, but I’d imagine you could get around it with some correlation.
      If I get chance I’ll do some investigation here.

      Comment by rnm1978 — December 3, 2009 @ 14:26

  2. Hi..

    Nice work….

    How do I get the correlation file…i did try copying the code and saving it as obiee.cor ..But when I imported nothing showed up on the list..

    Appreciate your help….

    Comment by kalyan — November 17, 2009 @ 23:49

    • Not sure what the problem would be. I’ve just tested copying the two lines from this article and pasting them into a .cor file using Notepad and it imported into VUGen with no problem.
      Do you get an error? What version of VUGen are you using?

      Comment by rnm1978 — December 3, 2009 @ 14:25

  3. Can I get a sample of the excel file for creating multiple new users

    Comment by DD1855 — July 21, 2010 @ 15:56

    • There isn’t one to download, sorry. You can create it easily following the steps in this post though.

      Comment by rnm1978 — July 23, 2010 @ 15:29

  4. very nice overview

    Comment by prashant — July 22, 2010 @ 12:34

  5. Hi Robin
    Great articles around OBIEE. I am not a OBIEE developer but I try to understand all the low level details before I aim performance testing on any project. Many of my questions to my developers were answered in your articles. I have done a PoC on load tools for BI and found that LoadRunner(http/HTML) best suitable protocol. The current article you have explained everything except the major bit of handling Searching…” in scripts.
    When anyone record the script(apply correlation and param) and replay it using LoadRunner, lodrunner completes the transaction as soon as the bi server sends http response 200 even though searching page is displayed rather than the actual report. Normally browser sends reload target to the bi server every second(not sure about how frequently . Question to you) till the actual report loaded on browser.
    In my script I was searching for “Searching…” as soon as I submit the request and resubmit the same request till the Searching disappears. In this way ami hammering the server with the same request? How BI server responds to my second web submit request with the same data? Does BI server sends multiple requests to my database server due to my approach? Could you please advise me on this article or bvpd@yahoo.com.

    Comment by Loadtester007 — May 21, 2011 @ 12:16


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.

%d bloggers like this: