Body

Introduction to background searches and search functions

Before FileNet 5.2.1 there wasn’t an easy way to get insight about the system usage, If you wanted to build a report showing the advantages of your Content Manager you needed to understood how the FileNet database model worked or try to get the data from the class model using the API to fill a spreadsheet or another database.

 

Starting from FileNet 5.2.1 we have the Reporting Enabled Add-On that comes with the Background Search feature, which as its name states, are searches than run in the background with the advantage that we can run time-consuming queries without compromising the health of the system.

 

I encourage you to watch the following video in order to know how to enable the new Add-On and use a sample background search.

 

So, if you going are going to work with Cognos is important foryou to understand how this works at database level. Basically, every time you create a background search template a subclass of Abstract Search Result is created. This new class contains all the objects returned by the background search and stores all of them on its own table.

Figure 1. Background Search Results Classes

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Let’s take a look at the table definition of a background search.

Figure 2. Background Search Results Table Columns

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you have experience with FileNet database model you will notice that this table is similar to docversion table, it contains all the common properties: object_id, security_id, epoch_id, home_id, creator and so on, and a custom column per property selected on the query. The difference is that it has the background_search_id column which store the id of the background search sweep that inserted the data on the table, this is because when you run the same background search more than 1 time, the old data is not replaced so the only way to separate new and the old data is by the id of the search sweep.

 

The background search queries also support the use of functions, in FileNet these are called Search Functions, you can build your own based on Java or Javascript if you need to get a custom output or do some processing. We’ll see this topic in detail later in this article.

Design the Model

 

Let’s start by asking some questions that we would like to answer with our model.

 

What is the quantity of documents created per class?

What is the quantity of documents stored per folder?

What is the content size growth rate over the years?

What is the content size per class per month over the last 2 years?

What is the quantity of documents created per user?

 

Then, we will have the following model.

 

Figure 3. Data Model

 

 

 

 

 

 

 

 

 

As you can see in figure 3 we have a fact table and four dimensions.

The fact table will be represented by the table created for the background search results. In order to build our dimensions, we will use Abstract Persistable class which creates a table for every child class and we will fill it using a custom search function.

 

Now let’s put some attributes to the model:

 

We will use a hierarchy for the class dimension and the folder dimension to will be able to drill down in the report, so in order to build that we will need two columns per level. This means that if we have two levels we will need four columns, if four then eight and so on. This article considers 6 levels even though in the object store we only have 2 levels.

 

Figure 3. Class Dimension and Folder Dimension

 

 

 

 

 

 

 

 

 

 

 

 

 

You can build the date dimension based on any other model you have, we will use the following representation for aggregation purposes.

Figure 4. Date Dimension

 

 

 

 

 

 

 

 

 

 

For the user dimension we will include only the name, id and email, you can include the group or even retrieve information from the LDAP used by FileNet.

Figure 5. User Dimension

 

 

 

 

Creating our first dimension

In this topic we will create our first search function, background search template and test both of them. To create a search function, you need a class that implements the interface SearchFunctionHandler , the interface has four functions.

 

The first is the evaluate function which is called for each result row to evaluate the custom function for that result row.

 

Listing 1. Inside ClassPopulationHandler Part 1

 

         public Object evaluate(CmSearchFunctionDefinition sfd, Object[] param)

           {

             ObjectStore os = sfd.getObjectStore();

             ClassDescription cd = (ClassDescription)param[0];

             cd.refresh();

             CmAbstractPersistable cap = findRecord(os, cd.get_Id());

             if (cap == null)

             {

            

             cap = Factory.CmAbstractPersistable.createInstance(os, "DIMClass");

            

            

             cap.getProperties().putValue("ClassName", cd.get_SymbolicName());

             cap.getProperties().putValue("IdClass", cd.get_Id());

 

 

The first step is to obtain the object store from the CmSearchFunctionDefinition in order to search for an existing entry using our findRecord function. Since the entries on our dimension tables are not deleted we need to check if the object exists in order to avoid duplicated data.

 

Listing 2. Inside ClassPopulationHandler Part 2

 

  public CmAbstractPersistable findRecord(ObjectStore objectStore, Id child)

             {

               CmAbstractPersistable userObj = null;

               String useQuery = "SELECT Id FROM DIMClass WHERE IdClass = '" + child + "'";

               SearchSQL sql = new SearchSQL(useQuery);

               SearchScope ss = new SearchScope(objectStore);

               IndependentObjectSet ios = ss.fetchObjects(sql, null, null ,Boolean.valueOf(false));    

               IndependentObject io = null;

               Iterator iter = ios.iterator();

               if (iter.hasNext())

               {

                 io = (IndependentObject)iter.next();

                 Id id = io.getProperties().getIdValue("Id");

                 logger.info("Id: " + id.toString());

                 userObj = Factory.CmAbstractPersistable.getInstance(objectStore, "DIMClass", id);

               }

               return userObj;

             }

 

findRecord is implemented for validation purposes, a validation of the record existence will be needed in order to avoid duplicated data. We query the dimension using SELECT Id FROM DIMClass Where IdClass = ‘” + child + “‘“ using the Id child as a filter because it is unique. Finally, what was found is returned to keep working with the evaluation function.

 

If something is found, we will return it as a property for the background. In case nothing is returned we will create a new instance of the DIMClass and put some logic to fill the properties.

 

 

Since the dimension will be build based on a hierarchy all the levels will need to be filled, even if you don’t have enough depth in all of the branches. If you populate a branch that is deep enough you just to populate from the bottom to the top, if its not deep enough you will to know who deep it is first in order to repeat the last level multiple times until we reach the deepest level.

 

Because we don’t know how deep our class is located, we will iterate from the bottom to the top checking each time if the parent class is document to know if we reach the top of the hierarchy. We also need to know how many levels we iterated before reaching the document class that’s the reason for the depth counter.

 

Listing 3. Depth Level

 

  int depth = 1;             

               ClassDescription classdepth = cd;

    while(!classdepth.get_SymbolicName().equals("Document"))

               {

                    depth++; 

                    classdepth = classdepth.get_SuperclassDescription();

               }

 

 

With the counter ready, the first thing we do is add a property of lower class and then iterate from the lower ancestor, whose number will be given by the variable i, towards the upper ancestor.  If the number of the ancestor is more than or equal to the depth level you keep adding the same class, if the number is less you add the parent.

 

Listing 4. From the bottom to the top

 

               for(int i=5;i>0;i--) 

               {

                    if(i >= depth) 

                    {

                             cap.getProperties().putValue("Level" + String.valueOf(i) + "AncestorID", cd.get_Id());

                             cap.getProperties().putValue("Level" + String.valueOf(i) + "AncestorName", cd.get_SymbolicName());

                    }

                    else

                    {

                            cd = cd.get_SuperclassDescription();

                             cap.getProperties().putValue("Level" + String.valueOf(i) + "AncestorID", cd.get_Id());

                             cap.getProperties().putValue("Level" + String.valueOf(i) + "AncestorName", cd.get_SymbolicName());

                    }        

               }

When all the iterations are done you can save the created object and return it to the background search.

 

Listing 5. Saving the object

 

      cap.save(RefreshMode.REFRESH);

              }

               return cap.get_Id();

             }   

now you can export your code to a jar and upload it to FileNet as a code module.

Listing 6 Inside ClassPopulationHandler Part 3

 

           public String getFunctionName()

           {

             return "JK::ClassPopulation";

           }

 

           public boolean requiresTransaction()

           {

             return false;

           }

 

           public Class validate(CmSearchFunctionDefinition definition, Class[] parameterTypes)

           {

             if ((parameterTypes == null) || (parameterTypes.length != 1))

             {

               throw new RuntimeException("ParameterTypes must consist of one Entry");

             }

 

             if (parameterTypes[0] != IndependentObject.class)

             {

               throw new RuntimeException("ParameterTypes must consist of one Object. Class is:" + parameterTypes[0].toString());

             }

 

             return IndependentObject.class;

           }

 

Finally, the getFunctionName function must return the name of the function as it would be entered on the SQL query. The requiresTransaction return true or false based on the need of a distributed transaction.  The last function can be confusing at first look, the first condition validates the quantity of input parameters for the function and the second validates that the type of parameter used is the one we want to work with.

Search Function configuration

 

Now that we have our code, we will need to configure it, go to ACCE > Object Store > Events, Actions, Processes > Search Function Definition and select new search function definition.

 

Figure 6. Creating new search function

 

 

 

 

 

 

 

 

 

 

 

 

 

 

put a name to your search function, I recommend you to use the same name you used in your code so you can identify it easily later.

Figure 7. New Search Function Part 1

 

 

 

 

 

 

 

 

 

 

Our example is coded in Java, select the Java class option and put the class handler.

Figure 8. New Search Function Part 2

 

 

 

 

 

 

 

Load the code module your uploaded before.

Figure 9. New Search Function Part 3

 

 

 

 

 

 

 

 

 

 

Click finish and move on to the dimension class creation.

Figure 10. New Search Function Part 4

 

 

 

 

 

 

 

 

 

 

 

 

 

Dimension class configuration

To configure the class, we’ll use as dimension, you need to create a subclass of Abstract Persistable. Go to ACCE > Object Store > Data Design > Classes > Other Classes > Abstract Persistable.

Figure 11.  Creating new abstract persistable class part 1

 

 

 

 

 

 

 

 

 

 

 

 

 

Put the name of your dimension class, it should be the same we are querying on the findRecord function of the ClassPopulationHandler.

Figure 12. Creating new abstract persistable class part 2

 

 

 

 

 

 

 

 

 

 

 

 

Once the creation is complete, you will need to add the following properties to the class, create your own property templates based on figure 13.

Figure 13. Dimension class properties

 

 

 

 

 

 

 

Background Search configuration

The last step to test is to create the background search template, we will use a simple query to obtain de document count, document size and get the reference to the document class information on the dimension class.

 

Go to ACCE > Object Store > Data Design > Background Search Class Template and create a new search class template.

Figure 14. Creating new background search part 1

 

 

 

 

 

 

 

 

Figure 15. Creating new background search part 2

 

 

 

 

 

 

 

 

 

 

 

 

Put a relevant name to your template and move to next step.

In this new text area, we write the SQL query that will be run by the background search. Look at Figure 16 and notice that every attribute has an alias which will be the name of the table column, also is here when we use our search function.

 

If you remember the code, the function will take the class description as input and then verify the existence on the dimension class, if doesn’t exists it will create a new instance and it will return its id. So, every time the function is executed you are also verifying and populating the dimension table.

Figure 16. Creating new background search part 3

 

 

 

 

 

 

You can uncheck the Generate a CSV file option, this time because we are going to use Cognos.

Figure 17. Creating new background search part 4

 

 

 

 

 

 

 

 

Select new class choice and move to the next step.

Figure 18. Creating new background search part 5

 

 

 

 

 

 

 

You will be prompted to create a new class for the background search results, this is where our fact table is created.

Figure 19. Creating new background search part 6

 

 

 

 

 

 

 

 

 

 

 

Now, select ID for our reference property CmRptClass and finish the process.

Figure 20. Creating new background search part 7

 

 

 

 

 

 

 

 

Figure 21. Creating new background search part 8

 

 

 

 

 

 

 

 

 

 

Background Search execution

Finally, only the execution is missing, everything we have been doing so far have been for this moment. Go back to the search template you created, click the actions button and select Start. Put a name for the search sweep, since we want the sweep run indefinitely and start now, its not necessary to fill the start date and end date fields.

 

When the sweep has finished go to the object store database and look for the created tables to verify if it worked, you can see part of the data on the figures 22 and 23.

Figure 22. Dimension Table sample data

 

 

Figure 23. Fact Table sample data