BI (Business Intelligence) models complement the operational business model in NexJ, providing capacity for optimizing data retrieval and reporting of data collected through the day-to-day transactions of NexJ Studio.

The operational databases used by NexJ CRM are typically optimized for transactional speed, and are often distributed across multiple networked systems. However, this configuration can often lead to slow performance when intense data retrieval is required, such as when doing reporting or gathering data for business analysis.

To address the need to have a highly searchable data store, NexJ Studio allows you to design and implement a BI model. This model contains its own set of classes, derived from classes in the business model. The BI classes and attributes can model data sets directly from the business model, or can be defined to store aggregate and calculated values based on those defined in the business model. In NexJ Studio, the BI model data is used to enable analysis functionality such as the Ad Hoc Reports engine.

Info

If there are no customizations made to the business model, BI model development is not required.

The general process for developing a BI model is:

Model your BI classes in a BI context file.
Create BI classes that contain the attributes that will appear in reports, including enumerations and localizations.
Create transformations between operational classes and BI classes.
Create ETL activities for each transformation.
Create sync classes for each BI class.
Update the LINKS_LIST attribute in the BI_1_BISyncTargetClassOut class.
Update the data source and Main.upgrade files.
Deploy the model.

After developing the BI model, a separate reporting environment must be created and BI synchronization must be enabled in the NexJ Admin Console to activate the BI model.

Developing the BI context

The BI context file is used to define how ad hoc reports appear and what fields can be queried. Within the BI context file are BI classes and attributes.

In the BI context, classes and attributes can mimic or derive from classes in the business model. One business model class can give rise to many BI classes, and likewise a single BI class may derive its attributes from multiple business classes.

If there are changes made to the BI model after synchronization is activated, the reporting database must be maintained to accommodate any new or updated tables.

BI context properties

The BI context defines BI classes, attributes, and relationships. These items are stored within a single BI context file.

Key properties in BI context

These are the key properties to set for a BI class in the BI context file, under the General subtab of the BI Classes tab.

Info

Navigate to the BI Models tab in the Business Model layer to create a BI context file.

You can also find online help in the bottom bar by selecting a field when editing the BI context.

Name

Name of the BI class.

Source Class

The name of the operational class that this BI class represents.

Include Derived Classes

Indicates whether this BI class also includes derived subclasses of the source class.

Where Clause

A clause to filter the source class for specific records.

Sync

Indicates whether the BI class will be sourced by a table in the BI model that is populated by synchronizing data from the source class.

Caption

Localized caption, used for display purposes in ad hoc reports.

Icon

Icon that represents the BI class. Set to match the icon in the source class.

Hidden

Indicates whether the class is visible within ad hoc reports. Classes without an understandable business concept to the end user should be hidden.

Type

Specifies the data structure of the a class. Currently, only METADATAMAP is supported.

bulkSyncOnLoad

Indicates whether the class is bulk synchronized when a database reseed is required. Ensures that changes to the class are applied to the BI database on server startup. Must be added as an attribute in the Source tab.

BI attributes

This is a list of key attributes to set for a BI class in the BI context file, under the BI Attributes subtab of the BI Classes tab. You can also find online help in the bottom bar by selecting a field when editing the BI context.

Name

Name of the BI attribute.

Value

The attribute name on the source class that this BI attribute maps to. Specify no value to represent a foreign key value that is stored on another table.

Type

Specifies the primitive type of the attribute, enumeration, or type localization. If the attribute is a foreign key to another BI class, do not put the BI class name here.

Key

Indicates whether the attribute is required in the BI class.

Sync

Indicates whether this attribute is sourced using synchronization of the source class attribute. If false, the attribute is a calculated attribute on the BI class to be evaluated by the scheme calculation in the Value field.

Navigable

Indicates whether the BI class is clickable on the ad hoc reports UI, jumping to the source instance in the Contacts tab. Only used for Entity classes.

Caption

Localized caption, used for display purposes in ad hoc reports.

Format

Used to format the data returned in ad hoc reports.

Hidden

Indicates whether the attribute is visible within ad hoc reports. An attribute such as a binary foreign key is an example of an attribute not to be displayed to end users.

Localized

Deprecated. Use the Type field to indicate if this attribute is a localized value.

BI class relationships

These are key settings to define relationships between BI classes. Configure these settings in the BI Class Relationships tab. You can also find online help in the bottom bar by selecting a field when editing the BI context.

Aggregation Computation Type

Setting to produce aggregations. Currently, only TABLEPERIODICALLYUPDATED is supported.

From

The first term of the BI class relationship.

To

The second term of the BI class relationship.

Relationship Type

The cardinality of the relationship between the classes in the From and To fields.

BI Attribute Mappings

Defines the attribute mappings in a class relationship. Set the From and To fields of an attribute mapping with the desired BI attributes.

BI Visible Classes

Defines the BI classes that this relationship is visible to. Can be used to resolve cyclical relationships in the model.

Creating BI contexts

BI context files define the BI classes and attributes that make up the BI model.

To create a BI context file:

In the Business Model layer, click the BI Models tab.
Right-click in the navigator and select New BI Context.
Specify a name for the BI Context and click OK.

The new BI context is created and available for editing.

Editing BI contexts

Edit a BI context to add new BI classes and attributes to the definition, or to add BI class indexes. In most cases, you will be customizing an existing BI context from your base model.

To edit a BI context file:

In the Business Model layer, click the BI Models tab.
In the navigator, double-click the BI context that you want to edit.
The BI context opens in an editor window.
In the BI Classes tab, select the BI class that you want to modify, or add a new one. By convention, all BI classes begin with the capital lettersBI.
- To add a new BI class, click the Add button.
- To edit an existing BI class, select the class in the BI Classes area.
After selecting a BI class to edit, use the subtabs to modify properties, create and update class attributes, and define indexes on the class. Details about each class property can be accessed in NexJ Studio by clicking on the property fields and reading the information bar at the bottom of the screen.
In the BI Class Relationships tab, specify the associative relationships between the BI classes, including the attributes that define the associations, and which classes each association is visible to. This information is used, for example, in the ad hoc report functionality of NexJ CRM to define which subreports are available within a given main report.
After completing your edits, save your changes.

Developing BI classes

A BI class is a counterpart of an operational class that is used in the BI model for ad hoc reports. The BI model is a flattened or denormalized collection of classes to allow faster access of data. Data is pulled into the class in which it will be commonly used. For example, a company name of a contact can be queried often when running an ad hoc report. For this purpose, it might be easier to create a companyName attribute in a BIContact class. When running a report, if the only information required about a contact is their company name, then this approach only requires querying a single table.

When designing a BI class, determine which attributes are important and will be commonly used in an ad hoc report. Attributes that are not queried within a report should be excluded from the BI class. In addition, multiple related attributes can be combined into a single attribute. For example, an Address class has a street, postal code, and province, but all the fields can be placed into a single string to avoid unnecessary synchronization of the Address class.

Ensure that any changes made to a BI class are reflected in the BI context file in order for ad hoc reports to use the data. By convention, BI classes are named <BIModel>_<Version#>_<BIClassName>, for example, BI_1_BIContact.

Adding enumerations and type localizations

The BI engine synchronizes enumerations and type localizations prior to use. This means that enumerations and type localizations have their own corresponding BI versions which should be used when defining these types of attributes in a BI class.

When defining an enumeration attribute for a class, ensure that these fields are configured:

In the Attributes tab, set the Type field to BI_1_BIEnumDisplayLocalized.
In the Value subtab, set the Where field to (= (@ typeCode) "X") where X is the type code value of the enumeration in use.

When defining a type localization, ensure that these fields are configured:

In the Attributes tab, set the Type field to BI_1_BITypeLocalizationLocalized.
In the Value subtab, set the Where field to(= (@ classCode) "X")where X is the class code value of the type localization in use.

Creating transformations for BI classes

Create a transformation to convert data from the operational class to the BI structure you created, using the operational class as the source and the BI class as the destination. For all BI classes, the object identifier (OID) from the operational class is mapped to the BI class. For information on creating a transformation, see Adding transformations.

Mapping enumerations and type localizations

When mapping enumerations and type locations, connect the root node of the source enumeration or type localization to the root enumeration/type localization node in the destination. In case of type localizations, the mapping should use the currentLocalization child node within the type node as the source attribute, not localizations (which is a collection).

Creating the ETL activity for the transformation

The ETL activity contains the logic for performing synchronization.

The ETL activity has two input parameters: where and isBulkSync. Place these arguments in the Args field within the Overview tab. A key order of blocks are used in most ETL activities for BI classes. This order can be customized to meet other functional requirements as necessary.

Script

Performs truncation of BI tables during BI bulk sync, speeding up the insertion of instances into the database. A sample script for truncating tables for a BIDocument class is:

(when isBulkSync
   (BIClass'truncateAll "BI_1_BIDocument")  
)

Read Cursor

Reads data from the operational class. In the Class field, enter the operational class that is used. In the Attributes field, enter the attributes to be read. In the Where Clause field, enter where and include any other conditions required.

Transform Cursor

Each record is transformed from the operational class to the BI class using the transformation created for the BI class. In the Transformation field, enter the created transformation. In the Attributes field, enter the attributes that are expected from the transformation. These attributes are similar to the destination attributes within the transformation.

Conditional Split Cursor

Depending on whether bulk or real-time synchronization occurs, data will be inserted or updated. In the Condition field, isBulkSync is used to determine which command to use. With bulk sync, the script from above has truncated the tables, allowing for simple insertions. Real-time sync, however, requires records to be upserted.

Create Command

With bulk sync, each record is created in the table. Enter the list of attributes to be persisted in the Attributes field. These attributes are similar to the destination attributes within the transformation.

Upsert Command

With real-time sync, records that are present are updated, and records that don't exist are created. Enter the list of attributes to be persisted in the Attributes field. These attributes are similar to the destination attributes within the transformation.

Creating sync classes

The sync class indicates to the framework that the BI model wants to receive updates. Create a sync class for each BI class defined in the BI context file. Add a prefix to the class to denote it is a sync class. By convention, BI sync classes are named <BIModel>_<Version#>_BISyncClass_<BIClassName>, for example, BI_1_BISyncClass_BIContact.

Attributes

These attributes must be set within a sync class. The sync class must also use SysSyncClass as its base class. For ease of use, copy an existing sync class and modify the attributes accordingly.

internalClass

Name of the operational class to track.

objectMessage

Message to be sent by framework. Should be set as BISyncOutMessage.

LINK_FILTER

Filter that is applied to instances of internalClass. Used to filter a subset of instances from synchronizing.

ETL_ACTIVITY

Name of the ETL activity to be used for synchronization.

DEPENDENCY_LIST

If the BI class is dependent on attributes from another class, the DEPENDENCY_LIST attribute is required. The value follows the format:

‘((OtherClass (<attr1> <attr2> [...])) (OtherClass2 (<attr1>)))

For example, if the Contact class has companyName synchronized with Company, then the value in the Contact sync class will be ‘((Company (contact))). If DEPENDENCY_LIST is present, two events are also required. For more information, see the following section about events.

name

Name of the sync class.

PRIORITY

Order in which synchronization is done for this link. A higher number means earlier synchronization. There is no hard restriction on priority, but classes should be set logically. Classes on which others depend should be processed before the dependent classes.

BI_CLASSES

List of BI classes that are being synchronized by this service. Used during bulk sync to truncate appropriate tables before loading the database with new instances.

ATTRIBUTES_TO_PRELOAD

Deprecated. Will be removed in the near future.

Events used with DEPENDENCY_LIST

If the sync class uses the DEPENDENCY_LIST attribute, these two events must be defined in the Events tab:

getDependencyFilter

Event that is invoked to determine if a change in a related class will change a BI class. For ease of use, copy an existing event. Here is a code sample from the UserPerson sync class:

(case className
   (("InternalUser")
      (for-each
         (lambda (tobj)
            (set! oidList
               (this 'getDependencyFilterHelper (tobj 'head) 'person oidList #f))
            (set! oidList
               (this 'getDependencyFilterHelper (tobj 'tail) 'person oidList #f))
         )
         objList
      )
      (if (null? oidList) #f `(in? (@) ,@ (remove-nulls oidList)))
   )
   (else #f)
)

Add more case statements based on the number of dependent classes. Using the previous example of companyName in the Contact class with DEPENDENCY_LIST set as ‘((Company (contact))), InternalUser would be swapped out with the class name of Contact and person would be swapped with contact.

getDependencyFilterHelper

Helper function that compiles a list of OIDs. The event should use this main script as-is:

(if
   isCollection
   (for-each
      (lambda (msgPartObj) (set! oidList (cons (msgPartObj ':oid) oidList)))
      (tobj attrName)
   )
   (set! oidList (cons ((tobj attrName) ':oid) oidList))
)
oidList

Configuring target classes

The target class, BI_1_BISyncTargetClassOut, is another class that communicates with the framework on dealing with synchronizations. Within the target class is a key attribute called LINKS_LIST that needs to be updated whenever new BI and sync classes are added. LINKS_LIST specifies which links framework will see and has the following format:

`(("linkName1" . ,BIClass1) ("linkName2" . ,BIClass2) [...])

When the target class is added in the Admin Console, links defined in LINKS_LIST will appear. If the target class was already added and LINKS_LIST is updated, then new links appear only after the database is reseeded.

Updating data source and upgrade your model

After you create the BI metadata, you must update the data source and upgrade your model.

Any BI classes that are added or altered should be reflected in the data source and Main.upgrade files. Customize the data source to add tables to support the persistence mappings of any BI classes, and add the appropriate upgrade steps in the Main.upgrade file. If the BI context file is modified, the database must also be reseeded with a Load step in order to reload the context information.

Maintaining the reporting database

Upgrade the reporting database with continued development of the BI model by using the tools in NexJ Studio.

To upgrade the reporting database, use the Data Load Tool with the recreate command on the BI data source to recreate the BI tables, and then send a snapshot to all links to repopulate the reporting database with the operational data. See the section Recreating databases for more information on recreating a database.

Another method to upgrade the database is to use the Database Schema Tool with the upgrade command on the BI data source. The Database Schema Tool will generate an upgrade script to run on the database that will create and alter any tables as necessary without having to repopulate the entire reporting database. Using a script may be preferable if you have a lot of data that would have to be rewritten with the previous method, requiring a lengthy amount of downtime.

Generating a domain from a BI model for Ad Hoc Reports

Generate the JasperReports Server domain and associated resources from the BI model using the JasperServer Domain Tool in NexJ Studio.
After you have developed either a BI star schema or snowflake schema in your BI model metadata, you can run the JasperServer Domain Tool to generate the equivalent domain metadata for use in Ad Hoc Reports for NexJ CRM.

For a description of the purpose of domains, see the TIBCO Jaspersoft JasperReports Server User Guide.

To generate a domain:

Open the JasperServer Domain Tool by launching NexJ Studio, selecting Run > JasperServer Tool > JasperServer Domain Tool.
The JasperServer Domain Tool dialog opens with the current model selected.
Ensure your model is selected. If not, browse to select it.
[Optional] You can select Test to select your test server and connections in the following steps.
In the Server drop-down menu, select the server for your NexJ CRM installation.
In the Connections drop-down menu, select your server connections.

Provide values for the following options:
BI Context
Select your BI context, which provides information on the relationships between metaclasses in your BI business model. The BI Context resource also specifies which attributes in a BI metaclass can be reported on and their captions.
Center Class
Select the BI metaclass, which serves as the center class to your star schema or snowflake schema. If multiple classes are selected, multiple domains are created.
Input Directory
[Optional] Browse and select the desired directory. If specified, the directory is searched for an existing domain with the same name as the one being generated. If found, customizations in the existing domain are copied into the newly generated domain.

Info

Customizations are resources with IDs that do not begin with the prefix that is reserved for use by the JasperServer Domain Tool. The reserved prefix used in the domain metadata is an underscore character ("_").

Output Directory
Browse and select the meta/jaspercatalogs/common-db/resources/organizations/organization_1/finance/BI subdirectory. The generated domains are saved in the output directory in their own individual folders. If a folder for the domain already exists, the domain schema, security file, and locale bundles are updated. If the folder does not already exist, the JasperServer Domain Tool creates the standardized folder hierarchy.
Datasource ID
Specify an alias name for the JasperReports Server data source used by the generated domain to query for BI data. For example, specify NexJ.
Database Schema [Optional]
Specify which database schema the tables belong to.
BI Folder Path
Specify the parent path within the JasperReports Server repository where the domain subfolder will be stored. The path is relative to the default organization. For example, specify finance/BI. Data Source Path Specify the relative path within the JasperServer repository to the JasperServer data source resource. For example, specify finance/data_sources/nexj.

[Optional] Click Advanced>> to expand the Advanced panel at the bottom of the tool popup, and enter your VM arguments:

Option	Description
`-Ddomain.maxDepth=<number>`	The maximum depth of class relationships to traverse. Defaults to `2`.
`-Ddomain.generateLongIds=<true>\|<false>`	True to generate long form IDs. Defaults to `false`. Info Long form IDs are more descriptive and easier to relate to BI metadata, however long IDs may exceed the maximum length for a resource ID within the JasperServer repository.

Click Run to run the tool.

Your JasperServer domain is generated in export format in your output directory. It is ready for packaging as a catalog.zip, which your system administrator with superuser privileges will import to the JasperServer repository.

Next, create a catalog import file to import your domain and all associated Ad Hoc Reports resources to a deployed JasperReports Server. For more information, see Importing the BI catalog import file.