Creating an ElastiCube for the TeamConnect Database

Creation of the ElastiCube can take hours to create the schema and move the data. For example, creating an ElastiCube for a nearly empty database can take 2 hours, but a fully populated database could potentially take more than 20 hours.

Before you begin:

• Make sure the user has DBA access on the server machine from which they will be running syncs. To check if the user is already a DBA:
  •  type lusrmgr in the Microsoft Windows search field and open lusrmgr.
  • In the lusrmgr window, click Groups in the navigation pane and then ora_dba to see the list of oracle DBAs on the server.
  • If the user is not in the ora_dba group, click the Add button and add the user.
• Grant the database schema access to create a materialized view by logging into sqlplus as sysdba and typing the command:  
  GRANT CREATE MATERIALIZED VIEW TO <DBUsername>;
  
  where <DBUsername> is the database username for your database. For example:
  GRANT CREATE MATERIALIZED VIEW TO TCTESTCSM;

To create the Elasticube:

1. In the TeamConnect_BI directory on the Sisense server, open the autoCube.properties file with a text editor. Define the values for the following properties. Note that these values are case sensitive.
   • database.hostname – the fully qualified domain name of the server that hosts the database
   • database.portnumber – the database port number
   • database.dbname – the database sid or database service name
   • database.dbtype – the database type (for example, Oracle)
   • database.driverClassName – the database driver class name (for example: oracle.jdbc.driver.OracleDriver)
   • database.connectionString – the database connection string for Sisense. This property is only needed if you are using load balancing or clustering.
   • database.username – the database username
   • database.userpassword – the database encrypted password (To encrypt the password, use the same encrypt.jar that encrypts the password in the teamconnect.properties file.)
   • sisense.cubename  – the name of the ElastiCube, not including the .ecube extension
   • sisense.serverName – the fully qualified domain name of the server that hosts Sisense
   • sisense.buildRetryLimit – the allowed number of attempts to automatically install or sync the cube (due to an issue in Sisense, the first attempt often fails)
   • sisense.pluginLocation –  the location of the Sisense plugin folder, excluding the "metadata\translations" subfolder and in the path, use \\ in stead of \, for example, C:\\Program Files\\Sisense\\PrismWeb\\plugins. This parameter allows the autoCube to copy the .json file to the translations folder automatically. If the folder location is changed, this property needs to be updated. If this property is left blank, the autoCube will not copy the json file.
   • enable.fail.email.notification -  YES if you want email notifications for failed builds, NO if you do not
   • enable.success.email.notification - YES if you want email notification for successful builds,  NO if you do not
   • email.recipient - the email of the recipient; If you want to send notifications to multiple recipients, separate the values with a comma
   • email.sender - a valid email address for the sender
   • email.server - the IP address of the email server
2. Open a command prompt as an administrator.
3. Change the directory to the TeamConnect_BI folder that you extracted in step 2. This directory contains the TCBI_SchemaSync.bat file.
4. Execute TCBI_SchemaSync.bat. The ElastiCube is created, integrated into Sisense, and is now available for use.
5. After cube is created, open the log file in the /TeamConnect_BI/autoCube_logs/ directory and check for any error keywords such as error and fail.

Note for TeamConnect Business Intelligence 6.0: Since emails must be configured ON at the Sisense Server level for clients to receive scheduled reports, users will receive an account activation email when their accounts are automatically created in Sisense. Ignore this email and access the reporting features through TeamConnect. This does not apply to 6.1 or later.

Sharing the ElastiCube with the Admin Account

Before the TeamConnect admin configures the integration, the ElastiCube needs to be shared with the Sisense administrator account user manually. You can share ElastiCubes through the Admin page of the Sisense Web Application.

To share an ElastiCube:

1. In the Sisense Web Application, select Admin > Data Sources and then the menu button for the relevant ElastiCube.
2. Click Share.
3. Type and select the Sisense administrator account given to you by Mitratech. The information for this account is listed in the center of the window.
4. Click Save.

Note: The following items pertain to configuring a high-availability configuration only.

• For a high-availability environment, add the ElastiCube to an ElastiCube set, share both the individual ElastiCubes and the ElastiCube set with the appropriate Sisense administrator account, and then use the name of the ElastiCube set in the report integration.
• If an ElastiCube is not secured by sharing it with the Sisense administrator account, the ElastiCube will always appear in the selection list when the dataset for a report is selected.

Configuring Single Sign-On

In order to embed dashboards, Single sign-on (SSO) must be configured and working. Single sign-on (SSO) is configured on the Admin tab in Sisense.

1. Log into Sisense Web Application as an administrator.
2. Click the Admin tab.
3. In the navigation pane, click Single Sign-on.
4. Enable SSO using the Single Sign-on Configuration toggle.
5. Select the method.
6. In the Remote Login URL, type the URL TeamConnect instance with the suffix of webservice/sso, typically in the form:
   
   http://<ServeNamer>:<Port>/<Context>/webservice/sso 
   
   where

   <ServerName> is the name of the TeamConnect server
   <Port> is the number of the port used to connect to the server
   <Context> is the context path for TeamConnect

   For a multi-tenant environment you can set the remote login URL to any URL or page as this user should never actually be used.

7. Click Save.

Preparing the TeamConnect Instance

1. After TeamConnect and Data Warehouse (if applicable) are installed and running correctly, open the teamconnect.properties file in the TeamConnect .WAR file and add the location of the Sisense server to the sisense.server.location property, including the http prefix and the port number. Redeploy the .WAR file.
2. In TeamConnect, click the Documents tab and select Top Level from the left pane.
3. Click the System folder and then click the Report Integration folder.
4. Click New Folder and the name specifying the name of the integration you are adding. For the Sisense integration, the folder must be named Sisense and is case sensitive.
5. Drop the appropriate .class files and .properties files into this folder. For example, for a Sisense integration:
   • Sisense.properties
   • SisenseReportAdapter.class
   • SisenseReportPortlet.class
     

Assigning Reporting Privileges to Users in TeamConnect

A user must be assigned rights to the Report Integration tool in order to use reporting.  

To assign Report Integration rights:

1. In TeamConnect, click the Admin tab and select a user.
2. Click Tool Rights in the navigation pane and click the Edit button.
3. Select Report Integration.
   
4. Click Save and Close.

Designer and Viewer rights are assigned to users through Group rights.  

To assign Designer and Viewer rights to a group:

1. Search for a group and open it.
2. In left navigation pane, click System Rights.
3. From the View drop-down list, select Report.
4. Click the Edit button.
   • To assign a Viewer rights, select View Report records (Legacy and New).
   • To assign Designer rights, select Create/Edit/Delete Reports (New).
5. Click a save option.

Note: if you are updating the reporting rights of a new or existing user and you already have a TeamConnect Business Intelligence integration with Sisense configured, sync the users and groups with the integration. However, if you are in the process of configuring the integration, you do not need to sync at this point. 

Adding an Integration to TeamConnect

In TeamConnect, click Tools and select Report Integration.

1. In the Report Integrations table, click the Edit icon in the Actions column for the Sisense integration.
   
2. The values displayed on the page correspond to the values in the the Sisense.properties file. Type the password, SharedSecret, Sisense admin username, ClientID, Environment, ElastiCube name in the appropriate fields.
   
    
3. Note that you must re-type any secured fields such as Password or SharedSecret when you are updating an integration.
4. Click Add Report Integration.
   Note: Upon initial setup of the report integration, the Client ID and Environment are configured and a new Sisense group is created with all the users in the TeamConnect instance. This group (<Environment>_<ClientID>) allows ElastiCube access for TeamConnect users. This information is used by TeamConnect to create a unique identifier for users on the report server and must not be changed after the initial saving of the settings in the Report Integration Tool.
5. Click the Sync User and Groups button.

Synchronizing Data between TeamConnect and Sisense

During the initial configuration of the integration between TeamConnect and Sisense, you do not need to manually sync the data but you do need to schedule the task for automatic sync. Schedule schema syncs on a regular basis for maintenance of the system, even if no structural changes are made (changing/adding objects & fields), to ensure deletions and updates are handled properly.

A data sync loads database records and permissions that have been added, modified, or deleted since the last sync so that the most recent information is included in the reports.The data sync can take hours to complete, but reporting functionality is still available during the data sync.

Important: Changing a user's reporting rights and deleting users must be done in TeamConnect so that they are synced in Sisense correctly. Do not delete or modify rights from Sisense; changes made in Sisense will not be reflected in TeamConnect.

To schedule automatic sync at regular intervals:

1. Schedule the TCBI_DataSync.bat task using a scheduler such as Microsoft Windows Task Scheduler.
2. Because the data sync and the schema sync tasks cannot run at the same time, configure the scheduler to not run the task if the other job is running.

To display the most relevant and recent information or if the connection between the database and the ElastiCube has been lost, you can manually sync the data.

To manually sync the data for reporting:

1. Make sure a data or schema sync is not already running.
2. On the Sisense server, open a command prompt as an administrator.
3. Change the directory to the AutoCube folder that you extracted when creating the ElastiCube. This directory contains the TCBI_DataSync.bat file.
4. Execute TCBI_DataSync.bat. The records in the database are loaded into the ElastiCube and are now available for reporting.
5. After cube is created, open the log file in the /TeamConnect_BI/autoCube_logs/ directory and check for any error keywords such as error and fail.

You can now run a report to display the updated records.

Synchronizing Semantic Schema between TeamConnect and Sisense

Note: During the initial configuration of the integration between TeamConnect and Sisense, you do not need to manually sync the schema but you do need to schedule the task for automatic sync.

A schema sync loads the database schema updates (including new, modified, or deleted custom fields and objects) from the TeamConnect database into the ElastiCube. The schema can take hours to complete.

Before scheduling or running a sync, take note of the following:

• Schedule schema syncs during non-business hours to prevent the following issues:
  • Changes made to the database during the schema sync can cause the sync to fail and render the ElastiCube unusable.
  • Reporting functionality is not available during the schema sync and users will receive an error. 
• Never have two or more Elasticubes syncing against the same database at the same time.

Optimizing Performance

For optimal TeamConnect Business Intelligence performance and lower synchronization,  (and sync updates), we recommend:

• Only include the objects and fields that users need in their reports. Each additional object will add around 3 minutes to the sync, on average small object. For more information, see Including and Removing Object and Fields from Reporting.
• Limit the number of users with reporting rights to less than 100. This number includes both view only and designer users combined.
• Google Chrome is the fastest browser. Mozilla Firefox and Microsoft Internet Explorer may be slower to load.
• Limit the number of widgets per dashboard to 6.
• Watch out for many-to-many relationships and queries.

Connections

Why do I receive an error when trying add an ElastiCube to the report integration?
Confirm that the ElastiCube is shared with Sisense admin user.

Why is TeamConnect not connecting with Sisense?

• Verify that the Sisense web server is running.
• Verify the ElastiCube Manager is running.
• Ping the machines from the TeamConnect server to make sure the server is available from the TeamConnect server.
• Check the logs and make sure the shared secret, admin user, and password are correct.
• If the logs show this error when configuring the report integration using the Report Integration Tool in TeamConnect:
  "Caused by: java.lang.LinkageError: loader constraint violation: when resolving method 'org.springframework.http.client.HttpComponentsClientHttpRequestFactory.<init>(Lorg/apache/http/client/HttpClient;)V' "
  Verify that you do not have a conflicting version of the HttpClient JAR in System\Libraries. If so, remove it and retry.

Why do I receive a message stating that it looks like I'm lost?
The system may be upgrading or installing licenses. Try again later.

What can I do if I'm having trouble accessing Sisense through TeamConnect?

The Sisense Web Admin can bypass SSO and directly access the Sisense machine using this URL template: http://localhost:8081/app/account#/login

The variables in this URL are the SSL, Port number and localhost. Make sure to replace these variables to reflect your Sisense instance.

Users

Why isn't a user appearing in Sisense?

Users must have an email address in TeamConnect in order to integrate with Sisense correctly. If a user with reporting rights in TeamConnect does not have an email address, the user will not have an account created in Sisense.  If a user does not have an email address, the integration will create a "dummy" email address for the user and log an error in the user/group sync. You should create a validly formatted primary email address for the contact record for the user and the "dummy" email address will be replaced with the new email address after a user/group sync occurs.

Why aren’t users created in a second ElastiCube when they were already created in one?
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.
The fix is to have separate Sisense servers for each TC instance in the environment (for example, one Sisense server for TEST and one for PROD.) (TC-16847)

Why are User/Group syncs failing?

Sisense validates email addresses using a standardized w3 format. Any email in TeamConnect must either be blank (in which case the integration assigns a properly formatted generated email address) or follow this format, or you will receive an error during User/Group sync.

ElastiCube Syncs and Builds

Why did Sisense fail when creating a new ElastiCube or rebuilding an existing one?

• This issue may be caused by a possible caching issue in Sisense. Make sure that the ElastiCube was removed after the failed attempt, that the folder in which was created is deleted, and that it is not appearing on the Server Console Use the Sisense Server Console to stop and restart the Sisense server and try again. TC-18801, TC-19219
• Note that the values in the autocube.properties file are case sensitive and an incorrect value can cause errors when a ElastiCube is built.
• If you are manually building a cube, make sure you are running the command as an administrator. Verify that you have Admin privilege on your machine, otherwise you may still be running it as a non-privileged user.
• If you are running low on RAM or receiving the error "Accumulative Changes Log Creation Failed Error Create Patch [The requested operation could not be completed due to a file system limitation ]", this may be due to the fact that Sisense saves temporary copies of the ElastiCube during the build on the C:\ drive in C:\ProgramData\Sisense\PrismServer\Patches.To move this location to another drive:
  • Using a text editor, open C:\ProgramData\Sisense\PrismServer\ElastiCubeConfiguration.xml.
  • On the second to last line, just before the </ElastiCubeConfiguration> closing tag add the the following text:
    <DefaultPatchesDirectory>D:\</DefaultPatchesDirectory>
    where D:\ is the new patches location.
  • Restart the ElastiCubeManagementService.
  • If the problem still occurs, navigate to the Sisense Server Console preferences and deselect Compress on build or defragment the affected drive.

Why are my syncs failing?

• Make sure the server has enough disk space.
• Make sure the server has enough available RAM.
• Make sure that the sisense.includeObject property in autoCube.properties file in the TeamConnect_BI directory on the Sisense server does not include any objects that are not reportable (for example, CSM$ is not reportable).
• Because the data sync and the schema sync tasks cannot run at the same time, configure the scheduler to not run the task if the other job is running.
• Make sure no custom objects in TeamConnect have a Unique Code starting with numeric value.
• Make sure a reportable field has not been deleted and then re-added. If this happens, perform a schema sync before performing a data sync.
• If you are manually syncing a cube, make sure you are running the command as an administrator. Verify that you have Admin privilege on your machine, otherwise you may still be running it as a non-privileged user.

After I updated a report integration, why can’t I sync the data?
When updating the report integration in the Report Integration tool, re-type any hidden field such as Password or SharedSecret to make sure that the integration saves the field correctly.

Why am I receiving this error when attempting to sync the ElastiCube:

• ERROR java.io.FileNotFoundException: D:\ElastiCube.xml (The system cannot find the file specified)
  • Use localhost or 127.0.0.1 (loopback address) as the Sisense host instead of the IP address of the server on which you are running the sync.
• GetTableSchema failed: unknown result
  • Check that the database user name is specified in all uppercase if using an Oracle data source.
• ORA-00600: internal error code, arguments: [kkogbro: no kkoaptyp], [0], [], [], [], [], [], [], [], [], [], []
  • Option 1: The best way to resolve this corruption is to restore a copy of the affected datafile from before the error and to recover the database so these changes are applied to the restored datafile and brought forward to the current time. This action is possible only if the archivelog feature for your database is enabled. (Enabling this feature ensures that all changes to the database are saved in archived redo logs.)
  • Option 2: Create a copy of the corrupted schema into a new schema using Oracle’s export/import feature.

How do I regenerate a Sisense catalog?

Sisense requires some free space on the C: drive, otherwise the catalog can become corrupted. To recover a catalog:

1. Open the Start menu or Task Manager and search for Services.
2. Stop the ElastiCubeManagementService service.
3. Open C:\ProgramData\Sisense\PrismServer and rename the ElastiCubeCatalog to ElastiCubeCatalog1.
4. Restart the ElastiCubeManagementService service. Starting the service may take longer than normal as Sisense creates a new catalog folder.

How do I clear the RabbitMQ queue?

The data cache of RabbitMQ can fill the drive. The lack of disk space can cause all cubes to detach from Sisense and all builds to fail. To clear the RabbitMQ queue:

1. Open a command prompt as an administrator.
2. Navigate to the folder C:\Program Files\Sisense\Infra\Rabbitmq\sbin.
3. Type the following command: rabbitmq-plugins enable rabbitmq_management
4. If you receive an error, restart the pulse/broker service and proceed to the next step.
5. Navigate to "http://localhost:15672" (on the server) and log in with the Username: guest and Password: guest.
6. Open the Queues tab and if you see 0 messages, the issue is not related.
7. If you see many hanging massages, clear the massages by clicking Queue sisense.ecs.build.logs.persist.
8. Click Purge and repeat until there are no more in the queue.

Reports

Why does nothing appear on the Reports tab?
If you are using SSL, make sure that both TeamConnect and Sisense are configured using SSL. 

Why is there a null record on my report?
An addition dummy/null record is created on the core system entities to support outer joins in Sisense. This record has blank values across the table except for system_modified_on. You can avoid this record on your reports by adding a filter on one of the entity fields:
1.    In the report, select the entity field in the filters (for example, Contact Name)
2.    On the list of values that appears, deselect N/A values to filter out the null values. 

Why isn't data appearing on my report?
If one of the ElastiCubes in an ElastiCube set is showing “Invalid Status” due to a failed schema sync, data will not appear in the reports that involve that ElastiCube set. Run a schema sync on the failed ElastiCube.

Duplicate reports on the same dashboard can also cause a report to fail. In rare instances, a "ghost widget" may appear on a dashboard, which is a duplicate but hidden widget. It can be removed by editing the .dash file of the dashboard or deleting it in the Application Database. 

Why are deleted records still appearing in reports? Why is outdated information shown on the report?

Schedule schema syncs on a regular basis for maintenance of the system, even if no structural changes are made (changing/adding objects & fields), to ensure deletions and updates are handled properly.

Why are newly created users unable to view reporting data even after schema sync?

TeamConnect Business Intelligence components must be on the same minor release. For an x.y.z release, the components must match the x.y version. For example, for a TeamConnect 6.1.2 release, the report adapter must be at 6.1.z and the BI code must also be at 6.1.z.

Why are dates in the reports off by one day as compared to the value you see in the TeamConnect Ui?

Sisense gets the data directly from the database, which is stored in GMT time. Convert any custom fields from timezone dependent into timezone independent (via script), then perform a schema sync to update the data.

Version Information

How do I find the version number of my TeamConnect Business Intelligence installation?

In the TeamConnect_BI directory where the autoCube.properties file is located, open the version.txt file

Updating an Integration

In rare instances, you may need to update the report integration. For example, the TeamConnect instance might need to be integrated with a different ElastiCube, or the ClientID/Environment  might need to be changed.

To update an integration:

1. In the Sisense Web Application, delete all the existing users for the original TeamConnect environment. Otherwise, a user duplicate email issue will occur.
2. In TeamConnect, click Tools and select Report Integration.
3. In the Report Integrations table, click the Edit icon in the Actions column for the Sisense integration.
4. Type your updates in the appropriate fields.
5. Re-type any secured fields such as Password or SharedSecret. Even though the fields appear to have stored hidden values, the values must be re-entered manually.

Logging

How can I configure the reporting logs?
In TeamConnect, on the Admin tab, click Logging and then System. Here, you can change the logging level for Reports.

Common data and log file paths

    Prism Server logs: C:\ProgramData\SiSense\PrismServer\PrismServerLogs

    Sisense Web log: C:\ProgramData\Sisense\PrismWeb\Logs

    ElastCube build logs: C:\ProgramData\SiSense\PrismServer\ElastiCubeProcessLogs<YourElastCubeName>

    MongoDB: C:\ProgramData\Sisense\PrismWeb\DB

    iisnode (web server) logs: C:\Program Files\Sisense\PrismWeb\vnext\iisnode

Creating Separate Admin Accounts on Sisense

Each TeamConnect instance requires its own Sisense administrative account.

In order for reports to appear on portal panes, the TeamConnect user has to share the report with their admin. Since a single Sisense Server can have shared tenancy, you can create a separate Sisense Admin for each TeamConnect instance so that the reports can be shared with the appropriate Admin.

To add a new admin account: 

1. On the Admin tab in Sisense, click the + User button.
2. Type the user's information in the appropriate fields.
3. In the Role drop-down list, select Admin.
4. To set the password, toggle the Define Password option to ON and enter a password. If you enter a password for the admin, the system does not notify the admin of the password; notify the admin directly.
5. Click Save. The admin then receives an email from Sisense that redirects the user to activate their account.
6. Add the Admin to the tenant group.

Removing Parts of a Reporting Integration