Focal Point
[CLOSED] Caching subset data for performance best practices

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5177059096

December 03, 2018, 10:15 AM
Sean Sweeney
[CLOSED] Caching subset data for performance best practices
Good Morning,

We have gone through a few training courses and are ready to begin creating reports for our organization. From what I have learned I have documented the following explaining the logic on how I think we should approach our deliverable. I would love to bounce this off anyone willing to read who could provide some input on best practices, (and to confirm I am understanding things properly), in trying to achieve what we want.

So, we want to create a report that presents a variety of metrics on what I will call events. The data presented for each of these metrics will be a count of the number of events that meet different criteria.

For example, the first metric will be a count of the number of events that have a parameter with a certain value. The next metric could be based on a different field / value and so on. I've created a crude diagram below that I hope explains our final deliverable and maybe illustrates some challenges we may face.

##################################
Metric Section A

|----------------------------------------------|
| Metric | Event Count | % of all Events |
|----------------------------------------------|
| Total | 56 | 100.0 |
| A | 3 | 5.4 |
| B | 0 | 0.0 |
| C | 40 | 71.4 |
|----------------------------------------------|
##################################

So we have upwards of 3 of these sections which group related metrics together. The top line represents the total number of episodes involved in that subset and every following row is a metric, containing an episode count and a % representing the relationship to the total in the subset.

The selection of the events that should be included in these calculations, a subset of all events ever recorded, is based on a few criteria but to give a couple to help paint the picture:
- the user requesting the report
- The user requesting the report is associated with the event through a set of tables, let's say a user can be engaged with a number of event managers who themselves are related to events.
- Therefore, we have a user that is involved in events by that user being associated with event managers who are associated with events.
- a date range in which the events "event execution date" must be contained

Since the end goal of this report is to have upwards of 20 metrics on one page, I feel the obvious bad implementation of this is to have each metric being a completely fresh query through the business logic. To explain, a metrics calculation could be broken into stages:

1. Parameters we receive are a user and a date range
2. Find the event managers the user is associated with from the user ID we received in 1
3. Find the events that are associated with the event managers we found in 2 and whose dates are contained in the date range we received in 1
4. Calculate our metric on these events
5. Repeat from 1 for all 20 metrics

The reasoning that this is a bad implementation is because steps 1 through 3 are the same for all 20 metrics so we can easily see a performance gain if we cache the data at the end of 3. If I'm not mistaken, the best way to approach this is to create a hold file in focacche as it will be unique to each browser session and will be a temporary place to store the events.

I think I will have some follow up questions, (having trouble creating the hold file at the beginning of my test metric procedure; wondering how I should go about relating procedures such that I can get the "% of all Events" for each metric etc.), but I think this is a good place to start - is a hold file in the foccache the best place to cache this? Or should I approach it a different route?

Any guidance is greatly appreciated.

Thanks!

This message has been edited. Last edited by: FP Mod Chuck,

Windows 8203 All output formats
December 03, 2018, 10:57 AM
FP Mod Chuck
Hi Sean

First of all Welcome to Focal Point. I think you will find it a useful place to get answers to your development questions.

I think you are right on in how to approach this using foccache. You can create a procedure (fex) that creates your hold file and use the universal profile to launch it. The universal profile can be configured using the client administration console under the application settings / client settings. This profile runs with the execution of the users first request after logging into WebFOCUS and by staging the hold file in foccache will allow that info to be reused during the entire user session.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
December 03, 2018, 11:43 AM
Frans
If it's one report, you don't need to use foccache. Just execute the business part in the beginning, HOLD AS ... and it will be in your temporary workspace.

Some things to keep an eye on with foccache, when you start a dashboard with let's say 5 panels all use foccache, initaially there will be 5 different foccache directories, only one survives. Your admin can force creation of foccache with login.

Another thing to keep in mind is that foccache can be manipulated by users.

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
December 04, 2018, 02:14 PM
Sean Sweeney
Thanks for the information Chuck!

Frans - Would this be private to the users session as well? Just to confirm I'm understanding the setup you are suggesting:

When setting up the output format, in the Output Destination modal I would set Destination to "Temporary File" but change the location from foccache to, say, my baseapp directory?

Thanks for the help!

Windows 8203 All output formats
December 04, 2018, 02:29 PM
FP Mod Chuck
Sean

Frans solution has all the code in one procedure so the hold file would be created and immediately re-used in the next section of code so no need to specify an application folder. I was thinking your scenario was more session oriented where you create the cached data up front and re-use it in other procedures. That is where foccache would be the correct place for the hold files.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
December 04, 2018, 02:46 PM
Sean Sweeney
Ah I see. Okay, I'll continue down the foccache route then for now as I think with what we are trying to achieve we need to use separate procedures. At the very least it's keeping each metric nice and cleanly separated out which I like.

Now, I'm actually drafting another Q right now and the conversation it raises may suggest we approach this original solution differently. We require the output of one of the procedures to be an input for the other procedures - we'll see what everyone thinks and maybe I'll have to come back to this Q.

Thanks!

Windows 8203 All output formats
December 06, 2018, 08:55 AM
Frans
quote:
Originally posted by Sean Sweeney:
Thanks for the information Chuck!

Frans - Would this be private to the users session as well? Just to confirm I'm understanding the setup you are suggesting:

When setting up the output format, in the Output Destination modal I would set Destination to "Temporary File" but change the location from foccache to, say, my baseapp directory?

Thanks for the help!

The foccache is a session cache. This directory exists when a user is signed in, and is deleted woth sign out. There is also an agent "cache" this directory exists during execution. You can execute one request (with multiple procedures via -INCLUDE) and everything in this request has access to this space ON TABLE HOLD AS TEMPFILE will put data in this space. Then there is a myhome space (depending on your license?), this is a user's private persistent space and then there are the applications like baseapp that can be used to share common things.

Baseapp is a application directory that is normally visible for everyone, I would not put data there unless there is a really good usecase.

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.