Periodically caching the results of an Xacute query

[Former Member]

I have a report that can be initiated by users when they select a 'product family' and 'time period' (currentmonth, currentyear etc..) as input parameters. [note: timeperiod is passed as a string parameter, not mapped in xacute query, I compute SD and ED within the transaction so that caching will work].

The transaction then calls an R/3 RFC, and does quite a bit of data manipulation with that result before returning it to the user.

The R/3 side transaction is a bit of a dog, and can take as long as 15minutes to return. [Actually better than the week it used to take an intern to assemble this report ]

Fortunately the data doesn't change much on a daily basis, so I have setup query caching within the xacute query for 7 days. The caching works great, however I would rather not have several users each week draw the unlucky card and end up waiting a significant amount of time.

What I am hoping to do is create a scheduled job that runs nightly to refresh any queries that have gone out of date. What would be the most graceful method to accomplish this? My first idea was to create a BLS transaction to iterate through the list of parameter combinations passed in via the scheduler and call the report, however it doesn't appear that you can call an Xacute (where the caching is handled) from within BLS.

Any help would be appreciated.

Regards,

Rod Hoffman

[agentry_src · Accepted solution]

Rod,

Try this.

Create the Xacute Query.

Create another transaction.

Add a Transaction Property named XacuteQueryName (string datatype). Put the name in there with the full project path (you can use a regular query in configure object to see what the path should look like.

Add another Transaction Property named server (string datatype).

Put "XacuteConnector" in server.

Add an SQL_Query action block to this transaction.

Do not configure it at all.

Go into the link editor and map Transaction.server to the server property of the SQL_Query action block. Map XacuteQueryName to Template. You will have to put the path from the project down in the property name.

Put in your tracer and test it.

It should work fine. I tested it in 12.0.4 and have used it previously in 12.0.2. I will let you test for the caching. Since you have 7 days retention in mind, you may want to test over a longer time period. But if it doesn't work, you will probably know it pretty quickly.

Good luck,

Mike

Edited by: Michael Appleby on Jul 11, 2008 9:26 PM

[Former Member · Accepted solution]

Thanks Mike, that worked beautifully. I added your suggestion to my existing transaction that steps through the families and periods, then linked those parameters within the sqlQuery.

With a clear cache, I executed the transaction (passing in two families and two periods). It took about 4 minutes. Then I executed a second time, and it returned almost immediately. I can now call this from the job scheduler passing in my CSV list of parameters.

Thanks a bunch,

Rod Hoffman

[Former Member · Accepted solution]

Woops, I might have fired my 'point gun' a bit pre-maturely.

Although through testing, it is definitely executing the proper xacute with the proper parameters and caching, as evident by the reduced execution time on the second pass. When I then execute the report from the UI, using the same family and time period passed in the scheduled transaction, I must wait the full time. So there must be something that causes xMII to not use the cached data, some subtle difference.

Is there a difference when using the xacuteconnector that causes this behavior? I may be able to brew up a way so that my UI utilizes the same transaction through another xacute.

Mike, your solution was still worth the points, I just may need to do some more tweaking.

Rod

[agentry_src · Accepted solution]

Rod,

I am glad you like the solution and I do hope you find out what the cause of the problem is when running it from the UI. Please let us know what you find out.

Thanks,

Mike

[agentry_src · Accepted solution]

I was also thinking of another way to solve your problem and if you can't get this one to work, I may have a couple more possibilities.

Mike

[Former Member · Accepted solution]

Perhaps it may be the query type - using a SQL query instead of an Xacute query will pass a different set of parameter name/value pairs, which is what the MII caching engine uses in its comparisons.

Alternatively, you could invoke the Xacute query via a URL request (using an XML loader) instead of hacking it using an SQL Query. This would use the "Illuminator" servlet, not the "Runner" servlet (you want to run the Xacute Query, rather than directly invoking the contained BLS transaction).

Rick

[Former Member · Accepted solution]

OK Rick. I would like to give this a try. Have I missed something in this URL for my XML loader?

http://server:port/XMII/Illuminator?Server=XacuteConnector&Mode=Query&Transaction=<Transaction>&Param.1=1&Param.3=CurrentMonth

It doesn't allow me to select an Xacute, only a BLS. Referencing the Xacute returns 'Transaction not found'.

Rod

[Former Member · Accepted solution]

Use the name of the Xacute query template you created via:

http......&QueryTemplate=YourFolder/YourTemplate

Rick

[agentry_src · Accepted solution]

Hi Rod,

I like Rick's idea, but if you run into difficulty making it work, you can also store the results in the NetWeaver database by saving the data in a file on the Web side of the xMII.

Run your update periodically (daily?) and simply leave the file there for use by your application.

Use an XML Saver action block and define the path in terms of db://<projectname>/WEB/<folder>/<filename.xml>.

It is not going to be quite as fast as caching, but it will work and addresses the performance issue you are fighting.

Mike

[Former Member · Accepted solution]

Thanks Rick and Mike,

Rick, the URL is fine now, however I'm still not getting the desired caching results.

As a test I ran the xacute from the URL without passing any of the 3 parameters, then ran the xacute directly from workbench. They were cached separately even though they were both using the same default parameters as defined in the mapped params of the xacute.

In the Netweaver DB 'XMII_TempFiles' table I can see that there is a subtle difference in the header of the two cache entries that are created. But I can only see the 'hex'.

Rod

[agentry_src · Accepted solution]

Rick,

Just curious, but why would you use an xml loader instead of an xml query. I have not done much with either action block so I am looking to get educated.

Thanks,

Mike

[Former Member · Accepted solution]

I have learned a lot in this thread. I did end up implementing your second suggestion Mike, saving files to db://. Basically I have a transaction that is called by the Job that is run weekly, which purges old files and creates new ones for each parameter combination. Then I added some logic to the existing transaction so that it first looks for a file, and if none is available executes the full transaction.

I would still be curious to know what the difference is between the query run directly from the xacute in workbench, and the query executed via xml loader.

Rod

[agentry_src · Accepted solution]

I suspect that some of the more technical folks could answer that right off the top of their heads. I use the tool and have some good experience to pass on. But my background is manufacturing, not computer science. So hopefully Rick can answer it or someone else who knows the tool on the inside can answer it for you.

Mike