Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Caching subset data for performance best practices
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Caching subset data for performance best practices
 Login/Join
 
Member
posted
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
 
Posts: 5 | Registered: November 30, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1206 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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, IBM DB2, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Hadoop.
 
Posts: 317 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: November 30, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1206 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: November 30, 2018Reply With QuoteReport This Post
Guru
posted Hide Post
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, IBM DB2, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Hadoop.
 
Posts: 317 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Caching subset data for performance best practices

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.