Reporting Requirements for TeamConnect Business Intelligence (TCBI 6.3.6)
Note: This document is intended for internal use only.
TeamConnect Business Intelligence is an integration that provides colorful visual reports embedded within TeamConnect. This document provides technical details for TeamConnect Business Intelligence and is intended to provide general guidelines for hardware and software requirements for the integration. Mitratech can provide more detailed recommendations based on your company’s specific needs upon request.
Note that for hosted clients, Mitratech provides the server requirements in your hosted implementation.
For more information on installing and configuring the integration, refer TeamConnect Business Intelligence 6.3 Integration Guide.
Components
TeamConnect Business Intelligence uses Sisense as the reporting tool that provides Designers with a report creation environment and easy-to-use interface and gives View-only users the ability to view and drill down into data. The integration relies on the creation of an ElastiCube as the analytics database.
Sisense Server
The Sisense server houses the following components:
- Sisense Server Console - used for starting and stopping the ElastiCubes
- Sisense Web Application - used for administrative tasks such as sharing an ElastiCube with the admin account, configuring single sign-on (SSO), etc.
- ElastiCube Manager - used for tasks such as modifying the data source for ElastiCubes or importing ElastiCubes
- ElastiCubes - the analytics database that retrieves its data from the TeamConnect database
ElastiCubes and Builds
TeamConnect Business Intelligence uses Sisense as the Business Intelligence tool. Sisense has an internal online analytical processing (OLAP) database to store data that is populated periodically from the online transactional processing (OLTP) database (TeamConnect’s database). The internal database is called an ElasticCube. The ElasticCube contains the object model and the data.
ElasticCubes are created, populated and published through a build process that serves as the Sisense extract, transform, load (ETL) process. If the cube already exist, schema sync will synchronized the schema in the cube with TeamConnect design. This build process involves three types of syncs:
- Schema syncs are builds that create a cube, populate it with the object model, and then reload a fresh set of data from the OLTP. The schema sync requires the longest time to build and will consume the most system resources.
- Data reloads, like Schema Synchs, reload the data, but they use an existing cube and repopulate the data from scratch. Data reload syncs are faster than schema syncs but are slower than data syncs.
- Data syncs load incremental changes into the ElasticCube. They are the fastest of the builds.
For more information about the Sisense ETL and OLAP technology, please refer to the Sisense documentation found here: https://documentation.sisense.com/7-....htm#gsc.tab=0
Cube Sizes
Hardware requirements are heavily influenced by cube size. The size of the ElastiCube is impacted by the following:
- Number of reporting users
- Number of reportable objects
- Number of reportable fields
- Size of tables
- Security model
Increasing the number of reportable objects also increases the number of tables included in the ElastiCube and therefore increases the build time. Sisense pulls data from the OLTP in blocks of a hundred thousand rows at a time. However, if a custom table is created in the cube or if the table is large enough, the table is pulled completely into memory in order to process deleted elements.
The security model also impacts ElasticCube size. The TeamConnect security model is calculated based on the user access to records in TeamConnect. The resulting security model is approximately the cross product between the number of projects and the number of reporting users that have access to those projects. This security model is calculated on the fly during the ElastiCube build and the result is loaded into the ElastiCube, increasing the size of the ElastiCube itself.
The size of the ElastiCube should be less than 25 GB to allow for faster building time and less memory usage during the building and querying time. The AutoCube Estimator tool can help predict cube size.
AutoCube Estimator Tool
The AutoCube Estimator tool runs against the TeamConnect database, reads the TeamConnect design, analyzes the data, and estimates the ElastiCube size. Contact Support or Professional Services to use this tool to estimate the ElastiCube size and sync times.
The tool consists two files:
- ClientDataDetailAnalysis.sql
- TCBI schemaSync and dataSync Estimator.xlsx
The SQL script needs to be run against the TeamConnect OLTP as the schema owner or using connect through access. It reads the TeamConnect design, tables size, and data types, and then produce a tab delimited set of text. Copy and paste the result of the SQL script into the Excel file. Using pre-defined calculations and formulas, the spreadsheet calculates the results from the SQL script and estimates the time and size of building the ElasticCube.
If the estimated build time and size of the ElasticCube are not within the desired range, you can use the spreadsheet to make an educated guess to remove some users from reporting access, some of the unneeded custom fields, or even custom objects from reporting. The spreadsheet automatically re-adjusts the estimated build time and ElastiCube size.
Before adjusting or removing any field or object from the spreadsheet for re-estimation, understand the following:
- The data type of the fields that you are trying to remove
- The relation between those fields with the categories/objects
- The relation between the objects and their children/embedded objects
For example, if you are trying to reduce the size of Matter’s custom fields table, analyze the custom fields belong to that category and the data types. If the fields that can be removed are 1 date field and 1 text field, you can reduce the number of the date field in the spreadsheet by 1, reduce the number of the text field by 1, and reduce the size of the text field by 2000.
If you are trying to remove an object from the spreadsheet, make sure to remove all tables that belong to the same object and note if the object is a parent object. If the object is a parent object, all its children and embedded objects need to be removed as well.
After the spreadsheet has been adjusted and has achieved the targeted build time and ElastiCube size, make the real adjustment in the TeamConnect and run the schema sync.
Reports, Queries, and Concurrent Users
The TeamConnect Business Intelligence has a number of elements as part of the integration interface:
- Reports, or widgets, are the foundational visualization objects used in dashboards. They are used to represent ElastiCube data as visualizations like bar charts, line graphs, pie charts and tables.
- Dashboards provide the canvas to organize reports into a related set of visualizations. A dashboard can contain one or many reports and can be shared with others for viewing or editing. Best practice recommends creating dashboards with six or fewer reports.
- Queries are the fundamental method by which reports retrieve their data from the ElastiCube. When a report is rendered, it sends a query request for data. A report usually issues a single query to retrieve the data it needs to render. Because a dashboard can contain many reports, each report in the dashboard will execute a query to retrieve the data necessary to render.
- Custom Blocks are a feature of TeamConnect where dashboards and or reports can be embedded in a page in TeamConnect. Use of this feature requires knowledge of creating custom block elements.
- Homepage Portlets are a feature in TeamConnect that allow homepage managers the ability to embed specific functionality as windows on the homepage. Only specific reports, not dashboards, can be embedded using Homepage Portlets.
Together, these elements impact requirements for hardware.
A dashboard is comprised of one or many individual reports. When a report is rendered, a query request is sent to the query server for the data necessary to render that report. For example, a single dashboard containing five reports will submit five total requests to the query server. As reports are loaded and queries executed, the ElastiCube data is cached on the query server to improve performance the next time that data is needed for a query request. Therefore, when sizing the Sisense machine for memory, make sure that all data be able to fit completely in memory. If the system does not have enough memory to fit all data, then data will be ejected from the cache. If reports continuously eject data to make room for new reports, cache thrashing will occur which could negatively impact the performance and throughput of reports.
Queries have the potential to impact both the servers CPU and memory resources. A complex query with many joins or a Cartesian product can consume all the available memory on the server. Take caution when creating joins between objects in reports to avoid performance issues. When calculating the total number of queries you expect to support, note that embedded reports in TeamConnect custom blocks and homepage portlets also issue queries and must be considered in your sizing. The homepage portlet is only capable of embedding a single report, whereas a report embedded in a custom block can embed a complete dashboard or a single report.
Architectural Diagram
Software Requirements
| Software | Version | Comments |
|---|---|---|
| TeamConnect | TeamConnect Enterprise 6.3.3 for TeamConnect Business Intelligence 6.3.6 | |
| TeamConnect Database | Oracle 19c ; SQL Server 2019 | |
| Sisense | Sisense.8.2.1.10110, provided by Mitratech | Mitratech only supports the version of Sisense and Sisense plug-ins included with the installation media. Any other version of Sisense or any other plug-ins are not supported. Mitratech is not responsible for unsupported configurations. |
| Java | 1.8 | 8u261 is the last build before the new paid licensing structure was put into place by Oracle. |
| TLS Protocol | 1.2 | Transport Layer Security protocol that products data as it's been communicated across the internet. |
Server Requirements
Supported Platforms
The following prerequisites and supported platforms are required for working with Sisense.
|
ElastiCube Server and ElastiCube Manager Operating Systems |
Product | Version |
|---|---|---|
| Microsoft Windows Server (64-bit) |
Note: If you are installing Sisense in Windows Server 2019, disable Windows Defender Real-Time Protection. |
|
|
Client Browsers |
Product | Version |
| Microsoft | Microsoft Edge Chromium | |
| Google Chrome | Current | |
| Firefox | Current |
Note: Ensure that you install TeamConnect and TCBI on different servers.
Capacity and Hardware Requirements
Actual capacity requirements are provided after consultation with Mitratech. The requirements of your configuration may vary depending on the number of concurrent users, builds running in parallel, ElastiCubes hosted on a server, and additional factors specific to your server, for example, non-Sisense applications running on the same server.
TeamConnect Business Intelligence deals with large amounts of data. Both the ETL (build of an ElasticCube) and the queries for the cube must process this data that is proportional to the original TeamConnect database (OLTP). The hardware recommendations in this document are based on a set of use cases and sizes from an average TeamConnect database, but TeamConnect can be highly customized and these customizations impact the hardware required to run TeamConnect Business Intelligence.
Factors that can affect the hardware requirements include:
- Numbers of reportable objects
- Number of categories per object
- Number of rows per table and number of columns (and their associated data type)
- The security model, which is a cross product of the users and the number of projects to which they have access
- Number of concurrent users
- What data is included in the cube
- TeamConnect customizations
Important: Read and understand the information in the following sections before estimating your hardware requirements:
The listed hardware specifications are based on the following:
- A typical TeamConnect deployment
- An Elasticube that is less than 25 GB (see Cube Size and AutoCube Estimator Tool for estimating the size of your ElastiCube)
- Fewer than 50 concurrent reporting users
- A typical number of concurrent queries (see Reports and Queries)
- Recommended use cases and best practices
- A single-node, on-premise configuration
| Processor | 16 Cores 2.5 GHz Supports Advanced Vector Extensions (AVX) |
|---|---|
| RAM | 96 GB |
| Disk Space | 256 GB SSD |
Contact Mitratech if any of the following are required:
- More users
- A larger cube
- A different set of behaviors
- A different data profile
- More than one cube is being build or queried
- Sisense is being used for more than TeamConnect Business Intelligence
Integration Requirements
- Each TeamConnect instance should have two Sisense administrative accounts - one for web UI login and one for TeamConnect integration.
- For on-premise installations, TeamConnect only supports one ElastiCube per Sisense server machine. Each TeamConnect instance can only be connected to one ElastiCube.
- Each TeamConnect user with an automatically created Sisense account must have a unique email address per Sisense server. This means that an on-premise client with a single Sisense server cannot have a TEST ElastiCube and a PROD ElastiCube with users with the same email. The second time an email account is used, the user will not be created automatically on the Sisense server..
- Using TeamConnect Business Intelligence solution outside of the intended purpose or using unsupported versions and plug-ins may impact your Upgrades Included Program eligibility. Mitratech is not be responsible for unsupported configurations.
Security
Project-centric Cascading Security
TeamConnect Business Intelligence offers project-centric cascading security at the object and record level, but not field/category level. This security is applied regardless if cascading security is enabled or disabled in TeamConnect.
An object inherits security from its parent matter. For example, Dispute security overrides all associated objects' security. System objects do not have security applied. (System Objects are objects that cannot be modified by end users and are identified by a monitor icon in the Setup Tool. For example, Document, Task, Invoice, Account, etc. In contrast, Custom objects can be modified by end users and can be created automatically in the system when new modules are installed. They are identified by a gear icon in the Setup Tool. For example, Dispute.) For example:
- If a user has access to a specific dispute, the user can report on anything associated to that specific dispute, even child items to which the user has not explicitly been granted access.
- If a user does not have access to a specific dispute, the user cannot report on anything associated to that specific dispute, even if explicitly granted access to one of the items.
SSL
If using SSL is configured for either TeamConnect or Sisense, both products must be configured for SSL. Otherwise, using mixed ssl and non-ssl products may trigger mixed-mode security errors and prevent you from viewing the Reports tab in your browser.
Recommended Use Cases and Best Practices
This section describes the use cases and best practices used relative to the hardware recommendations. Sisense is a generalized business intelligence tool, however TeamConnect uses it in a specific manner. The following guidance documents the usage of Sisense.
Building Sisense ElasticCubes
- The best practice when building the TeamConnect Business Intelligence ElasticCube is to build the cube at night after resetting IIS and the ElasticCube Management Service. It is recommended that you perform either a Data Reload or Data Synch during the week and a schema synch during the weekend. If you want to clean sensitive data that has been removed from the OLTP, use a Data Reload, otherwise a data synch is recommended.
- The hardware recommendations in this document assume that hardware resources used during the ElasticCube build will be re-used during the day to support the query load. If it is desired to refresh the ElasticCube during the day, it is recommended you contact Mitratech for guidance.
- The recommended maximum size of the resultant ElasticCube should be less than 25 GB to allow the ElasticCube to be built easily during the night. The hardware recommendations in this document are based on a 25 GB ElasticCube. If the resultant cube is less than 25 GB in size, allowances should be made for growth of the ElasticCube over time.
- Depending on whether you are using Data Synchs or Data Reloads, the time to build will vary. Data Synchs take less time as TeamConnect Business Intelligence reloads only the changed values. Data Reloads replace the complete data set. Schema synchs take the most time, as it prepares the ElasticCube by populating it with the schema and then doing a full Data Reload. After a Schema Synch is performed, then a second Data Synch should be immediately performed as it will help query times by pre-building the indices in the ElasticCube.
- The number of CPUs do not impact build performance; however, the speed of CPU helps in processing the data, but the main factors in build performance will be disk I/O and the OLTP latency. It is recommended to use SSD drives for the Sisense servers.
Queries
- The hardware recommendations in this document should be able to handle 50 concurrent users and their associated queries. Assuming the recommended maximum of 8 widgets per Dashboard and 50 concurrent users, the below hardware specification should be able to handle bursts of 100 queries per minute under load.
- Note: When a cube is rebuilt, the first set of queries need to repopulate the Sisense cache, resulting in slower initial queries that are not an artifact of inappropriately sized hardware.
- Query performance is tied directly to CPUs. Increasing the number of CPUs impacts the performance of the Sisense server for queries. Have enough memory to hold the complete data set for commonly run queries or Sisense will perform poorly as it will need to continuously reload the data sets. The type of the query also has a large impact. Understanding of the TeamConnect Business Intelligence object model so that you can avoid large, cross product queries that require large amounts of temporary space in memory. Memory should be large enough to handle the largest set of unique, concurrent cross product queries and all the cube.
Server Restart and Maintenance
- It is recommended to restart IIS and the Sisense ElasticCube Management Service every night. To restart IIS, use iireset in a command command prompt instead of PowerShell.
- The RabbitMQ logs will need to be cleared if they reach over 500,000 queued messages as the data cache can fill the drive. If these logs are not cleared, then both memory and diskspace will be affected causing Sisense to fail to build the cube and queries may fail or return errors. To clear the RabbitMQ queue:
- Open a command prompt as an administrator.
- Navigate to the folder C:\Program Files\Sisense\Infra\Rabbitmq\sbin.
- Type the following command: rabbitmq-plugins enable rabbitmq_management
- If you receive an error, restart the pulse/broker service and proceed to the next step.
- Navigate to "http://localhost:15672" (on the server) and log in with the Username: guest and Password: guest.
- Open the Queues tab and if you see 0 messages, the issue is not related.
- If you see many hanging massages, clear the massages by clicking Queue sisense.ecs.build.logs.persist.
- Click Purge and repeat until there are no more in the queue.
Disclaimer
Using TeamConnect Business Intelligence solution outside of the intended purpose or using unsupported versions and plug-ins may impact your Upgrades Included Program eligibility. Mitratech is not be responsible for unsupported configurations, including but not limited to:
- Adding or deleting ElastiCubes
- Using more than one ElastiCube per TeamConnect instance
- Deleting schemas
- Using versions of Sisense or plug-ins other than the specified version provided to you by Mitratech
- Using Sisense outside of the TeamConnect Business Intelligence integration
Mitratech reserves the right to not support non-standard or non-default functionality and extended functionality available in third-party software, unless specifically documented as supported or certified in the Mitratech product documentation.
For further information regarding third-party non-standard or non-default functionality, please contact Mitratech Support. This document, along with the software that it describes, is furnished under license and may be used or copied only in accordance with the terms of such license. The content of this document is furnished for informational use only, is subject to change without notice, and should not be construed as commitment by Mitratech. Though every effort was made to ensure that the information in this document is correct and reliable, Mitratech does not assume any liability for any errors encountered in this document. If you need support, please contact the Mitratech support team by sending an email to: support@mitratech.com.