Install Error Service 'W3SVC' (W3SVC) failed to start
During the install of Sisense, you may run into the following error: We're sorry, There was an error with your Sisense installation Service 'W3SVC' (W3SVC) failed to start. Please restart your computer and try installing again. The W3SVC service is a Windows service that is responsible for making IIS (Internet Information Services) work. The W3SVC service also manages the HTTP protocol and HTTP performance for IIS which needs to be running to allow Sisense (and other sites) to work. This issue is indicating one of two things: IIS or some of the program's components are not installed on the machine and failed to install during the Sisense installation process. One of the IIS services are not currently running (either IIS overall or W3SVC service). Resolution To resolve this issue, please do the following: Installing and Enabling IIS Close out of the Sisense Installation On your machine, search "Turn Windows features on or off" Look for "Internet Information Services", and make sure that all of the components which are checked in this screenshot are checked on your machine: Internet Information Services > World Wide Web Services > Application Development Features Restart your machine. Attempt the Sisense install again If this does not resolve your issue, please reference the Restarting IIS Services section of this article. Restarting IIS Services Restart IIS In your start menu type 'cmd'. Right click the shortcut and choose "Run as Administrator" In the command line, type: iisreset , hit Enter and wait for the IIS server to restart. If you end up getting some sort of error about not having IIS Start the W3SVC Service In the command line, type net start w3svc , hit Enter and wait for the W3SVC service to start If it is already started, the CMD line will tell you Close the command prompt window. Try to run the Sisense installer again as an AdminSetting Up SSO SAML 2.0 With Keycloak
Keycloak supports applications that support SAML 2.0, such as Sisense. This page describes how to add Sisense to Keycloak and configure SSO-support with SAML 2.0 by creating a Keycloak client and connecting it to Sisense. This article assumes you have a Keycloak IdP Server configured. Note: This page reflects a 3rd party’s application which may change. If the steps described here do not match what you see in your Keycloak account, you can use the generic Sisense SAML documentation, along with the IdP’s documentation. Connecting Sisense To Keycloak To connect Sisense to Keycloak: Download sisense_client_config.json to your PC. Log in to your Keycloak's admin console (http://localhost:8080/auth/admin/) Select Clients from the left menu. Click Create. In the Add Client window click Select File. From the file explorer popup window locate the file downloaded in Step 1 and click Open. Back in the Add Client Window click Save. In the Settings tab of the opened Sisense client page expand the Fine Grain SAML Endpoint Configuration section. In the Assertion Consumer Service Redirect Binding URL filed replace "localhost:8081" with your domain name. If using secure connection, replace "http" with "https". Updated value of the Assertion Consumer Service Redirect Binding URL to match the value of the Assertion Consumer Service Redirect Binding URL (from the previous step.) Click Save. Click on the Installation tab. From the Format Option, select Mod Auth Mellon Files and click Download. Extract the downloaded keycloak-mod-auth-mellon-sp-config.zip. Open the idp-metadata.xml file from the extracted folder. Open your Sisense web application. Go to the Admin tab and click on the Single Sign On menu item. Select SAML 2.0 as the Method option. In the idp-metadata.xml file locate the following element. <SingleSignOnService Binding="urn:oasis:names:tc:SAML:2.0:bindings:HTTP-POST" Location="http://localhost:8080/auth/realms/Sisense/protocol/saml" /> <SingleSignOnService> 20. Copy the value of the Location attribute to the Remote Login URL field in Sisense. 21. In the idp-metadata.xml file locate <dsig:X509Certificate>. 22. Copy the value of the X509 certificate to the Public X.509 Certificate filed in Sisense. 23. Enable Single Sign On by clicking the switch button. 24. Click Save.Installing Python On IIS
Question: Some users might want to install python on their IIS, for example in order to establish SSO by using python code. How can I install it? Solution: In order to do so, follow these steps: Insure you have CGI installed on your IIS: Go to Start -> Control Panel -> Programs and Features -> on the left hand side, go to "Turn Windows Features on or off" -> Under the IIS node, make sure CGI is installed: Download Python for Windows, from python.org (doesn't matter 2.7 or python 3, depends on your preference). Make sure you get the x64 version if you have an x64 version of Windows. Unpack and install that python MSI. Choose the default, which puts python into c:\PythonX (X will be replaced with your Python version) Create a directory to hold your "development" python scripts. Eg, c:\dev\python Set the permissions on the files in the directory c:\dev\python to allow IIS to read and execute. Do this by running these two icacls.exe commands from the command line: cd \dev\python icacls . /grant "NT AUTHORITY\IUSR:(OI)(CI)(RX)" icacls . /grant "Builtin\IIS_IUSRS:(OI)(CI)(RX)" Open the IIS manager, and create a new application. Specify the virtual path as /py and the physical path as c:\dev\python: Within the new IIS application, add a script map for *.py , and map it to c:\python27\python.exe %s %s: create a "HelloWorld.py" file in c:\dev\python with this as the content: print('Content-Type: text/plain') print('') print('Hello, world!') Test that the script is working - invoke in the browser http://localhost/py/helloworld.py *You should be able to use all Python libraries you have installed on your system. *In case that you try to open the script and it returns a 502 error, it means that something is wrong in the script. In such cases, debug it on your IDLE instead through the browserHow to Export a Certificate using the Certificate Export Wizard (Windows only)
How to Export a Certificate using the Certificate Export Wizard In Sisense version 7.2 and later, SSL (Secure Sockets Layer) settings have moved from Microsoft IIS into the Sisense Configuration Manager. For organizations moving to Sisense 7.2 and above from an earlier version that would like to continue using SSL, it is necessary to obtain the certificate to make SSL work. See the steps below to attempt to obtain the certificate from the server's certificate store if your organization previously had SSL set up on the Sisense web server. Organizations can also opt to contact their Certificate Authority (CA) to obtain an up-to-date copy of your site's .pfx or .crt and .key files. This explanation utilizes mmc (Microsoft Management Console) to export the certificate. On your webserver, search programs for mmc and select mmc.exe In the window that appears, click on "Add/Remove Snap-in..." In the "Available snap-ins" column, click on Certificates then click "Add >" to add it to the "Selected snap-ins" column In the resulting pop-up, select "Computer account" then click 'Next' Leave the settings as is on the next screen and click Finish Click on OK to add the Certificate snap-in On the left-hand side of the screen, expand Certificate (Local Computer) > Personal > Certificates Right-click on your certificate, go to All Tasks > Export The Certificate Export Wizard will appear which will assist you in exporting your organization's certificate to the appropriate format. Select the Next button. On the Export Private Key screen, select "Yes, export the private key". The private key is necessary for SSL to work in Sisense 7.2 and higher. Then select the Next button. On the Export File Format screen, select the Personal Information Exchange (.PFX) file option and hit the check boxes that correspond to the options below, then select the Next button. ☐ Include all certificates in the certification path if possible ☐ Export all extended properties ☐ Enable certificate privacy On the Security screen, select the Password checkbox and enter a password. Make sure the encryption setting is TripleDES-SHA1. Then select the Next button. Select the file location in which to save the exported certificate. Then select the Next Button. On the last screen select the Finish button to complete the setup. Complete the setup in this guide to set up SSL in Sisense.[Custom SMTP Email Server] - Outlook365 Error `432 4.3.2 STOREDRV.ClientSubmit; Sender Thread Limit Exceeded`
Question: As of version 7.3, If you're using Outlook365 (not on-prem) as your SMTP server, you might encounter the following error when sending batch emails: 432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded Essentially, this error is thrown back from the SMTP server telling us that we've opened too many simultaneous connections which exceeds the limit (3) of the server. Solution: The reporting engine utilizes nodeMailer to send out emails. Its transport is configurable so what we'd need to do is limit the number of connections it opens and utilize a pooled connection. To change these settings: 1) Create a backup of the file C:\Program Files\Sisense\app\galaxy-service\src\features\emails\v1\mailSender.js 2) Open the original and modify the following object: function getSmtpTransport( ){ ... return mailer.createTransport( { maxConnections: 3, //<-----------ADD THIS LINE pool: true, //<-----------ADD THIS LINE host: emailServerConfig.host || 'localhost', port: emailServerConfig.port || 465, secure: emailServerConfig.secure || false, ignoreTLS: emailServerConfig.ignoreTLS || false, requireTLS: emailServerConfig.requireTLS || false, connectionTimeout: emailServerConfig.connectionTimeout || 10000, greetingTimeout: emailServerConfig.greetingTimeout || 5000, socketTimeout: emailServerConfig.socketTimeout || 5000, logger: emailServerConfig.logger || false, debug: emailServerConfig.debug || false, auth: auth } ); } 3) Restart Sisense.Galaxy service. If that does not help you please create a support ticket and our support agents would be happy to help. Sources: https://nodemailer.com/smtp/pooled/ https://support.microsoft.com/en-us/help/4458479/improvements-in-smtp-authenticated-submission-client-protocolUsing Native Javascript Date Calculations To Modify Sisense Date Filters
Sisense natively supports various types of date filter functionalities. However, there are instances where a dynamically updating date filter is desired based on dynamically changing conditions such as the current date. Such a filter may not precisely align with the filters provided in the Sisense UI. One approach to achieve this custom behavior of a dynamically updating filter is through the use of dashboard or widget scripting.Getting Started With DbVisualizer
Problem There are many data sources (connectors), such as web service APIs where we not familiar with their tables/fields like Facebook, Twitter, Youtube etc. Before importing your data from a web service, you should know the data in advance. This lets you: Slice unnecessary garbage data by generating custom queries Understanding the data you’re dealing with Troubleshoot problems from the data source/connector side Solution DbVisualizer is a database tool that let’s you explore your data sets before importing them into the ElastiCube. HOW DO YOU CONNECT DBVISUALIZER TO YOUR STANDALONE DATA SOURCE CONNECTOR? 1. Go to the DbVisualizer site and download and install the latest version. *Note: You can download a version with Java or without Java. To see if you have Java installed already, open the Command Prompt and type in the following command: java -version. 2. Verify that you downloaded your connector’s driver and you can locate the connection in the following directory: C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors 3. Copy your connector’s connection string in an editor where you can easily view it. For an example of a connection string, see our Facebook example: jdbc:facebook:OAuthClientId=11276856774486;OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0; 4. Open DbVisualizer and force close "JDBC Driver Finder". 5. In DbVisualizer, go to Tools > Driver Manger and add your new connector (a) Click "+" icon to add new driver. (b) Give your connector a name. (c) Click the Driver file path and look for the JDBC driver file, and then click ok. C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\<Your connector>\lib\cdata.jdbc.<connector> (d) Verify that the driver is loaded and you see the driver. Then, close the Driver Manager window. 6. In DbVisualizer, go to Tools>Connection wizard. (a) Give your connector name and click Next. (b) Chose the driver connector that you generated. (c) Open "C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\<Connector>\description.json"; get the "RTK" parameter and add it to your connection string. Before: jdbc:facebook:OAuthClientId=11276856774486;OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0; After (RTK in the end of the string): jdbc:facebook:OAuthClientId=11276856774486;OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0;RTK=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (d) Add the connection string with the RTK to the "Database URL" and click Finish. (e) Verify that it connected successfully and you see all the Tables and views. (f) Go to SQL Commander > New SQL. (g) Change max rows to "-1" and start to explore your connector data. References Sisense connectors list DbVisualizer Users GuideMaximize Dashboard Performance
What is good dashboard performance? Dashboard performance is the time it takes to load a Sisense dashboard. We want our users to have a good experience when working with our data. The time taken to load the dashboard is the first step. Each individual widget in a dashboard is a separate query. The dashboard needs to send these queries through the Microsoft IIS web server to the ElastiCube, pull back the results and populate the data visualizations. The questions we will address in this post are what is a reasonable dashboard load time and how can we enhance performance. So, what is good dashboard performance? This is a slightly subjective question. Users always want the fastest load time. However, we must think about load time relative to the data we are processing. If the dashboard is aggregating billions of rows of data, it is unrealistic to expect two-second load times. The converse is true as well — if a dashboard is based on a few thousand rows of data, the dashboard should not take five minutes to load. The goal, to put it simply, is to optimize the load time relative to the amount of data the dashboard is processing. Sisense’s architecture is designed to return queries very quickly, and there are ways to help it. How to monitor performance Timing First and foremost, how long does the dashboard take to load after the user navigates to the URL? Does the load time inhibit the user experience? As discussed before, the answer will vary by the use case, ElastiCube size, and end user. However, as a general guideline we want the load time to be under a minute if the dashboard is not crunching enormous amounts of data. System Resources Monitoring system resources during dashboard load is the first step to identifying possible issues. These can be monitored live by opening the Task Manager > Performance tab. RAM and CPU should not be maxed out during the dashboard load. If they are, it could be hindering the process. More system resources might be necessary (see here for Sisense's minimum hardware guidelines). Components ElastiCube.exe – if this process is taking CPU and RAM then the query is too heavy. This could be a possible many to many occurrence (covered below) ElastiCubeManagement Service – if this process is taking CPU and RAM, you most likely have a build occurring at the same time Java(TM) Platform SE Binary - This process transmits Sisense monitoring data. Heavy use of hardware is expected when restarting the service after a while (as it catches up on transmitting data), but if you experience continued consumption, confirm you are using the latest version of the Sisense Monitor Node.js - If this process is heavily consuming resources, check whether you have recently published dashboards to a large number of users. w3wp / iisexpress – if this process is taking CPU and RAM you may be overloading your IIS server. This is mostly likely when presenting too much data (covered below) Another way of monitoring system resources is by using the Windows Performance monitor. This will help analyze system resources over time. The post here should help: https://learn.microsoft.com/en-us/windows-server/identity/ad-fs/deployment/configure-performance-monitoring Timing vs other dashboards Using other dashboards that use similar data sets as a baseline can also be useful. For example, two dashboards are using the same ElastiCube. Dashboard-A takes 30 seconds to load. Dashboard-B takes five minutes. Knowing that Dashboard-A loads normally, we can tell that Dashboard B has some kind of an issue. Possible issues could be a bad join, many-to-many relationship, or specific troublesome widget that Dashboard A did not have. Compare the differences to narrow down possible issues. Web Developer Console Developer Tools within your web browser allow us to track each dashboard component's load time. In Chrome before loading the dashboard, open the developer console (F12 in Chrome). Navigate to the Network tab. From there you can see the timeline and the long-running requests. Most likely it will be the JAQL queries that take the longest. You can then click those records for more information. Under Headers>Request Payload there is information about the widget ID and Name. This will help identify the long-running queries. Under Timing, you can view what composed the query response time. Stalled represents one query waiting for other queries to finish (there is a limit to the number of concurrent queries your web browser can run). Waiting (TTFB) represents time waiting for the ElastiCube to respond. Content Download represents the amount of time retrieving the query results. If the JAQL queries are not the longest running components, you may have connectivity issues to your server, or are using too much Javascript (covered below). Causes of slowness System Resources A reasonable query time depends on a few factors. First and foremost, system resources. The recommended system resources for Sisense can be found here: https://docs.sisense.com/win/SisenseWin/windows-minimum-requirements.htm. Amount of data The amount of data crunched/returned in a query has an impact on performance. Logically, this makes sense. Crunching one million rows will, of course, return faster than one billion rows. The way to affect this is by adjusting filters to limit the data returned. Furthermore, limiting the amount of data in the ElastiCube is useful. This can be done by editing either the tables that Sisense is connected to, or by editing the SQL statement that is used to query the original data source. Schema Design Schema design is a very important factor in query performance. Each data connection within the ElastiCube will become an inner join when the dashboard queries this data. Joins are costly when it comes to query time. When designing the schema for performance, the ElastiCube designer should de-normalize the schema as much as possible. De-normalization means having a fewer amount of joins, and including redundant information in one table. For more on schema de-normalization, see here. Custom Calculations in the Dashboard Custom calculations are often useful when manipulating data to answer business questions. They allow users to aggregate data in various ways to present visually. However, they do take time to process. Each custom calculation is a separate query that is processed by Sisense. To enhance query performance we can cut down the number of queries generated by the dashboard. This is done by moving some calculations to the ElastiCube, thus moving that process time to the build time instead of dashboard load. The best example is a row calculation, such as the addition of two separate columns — FieldA + Field B. This type of calculation can be easily added to a table using the same exact formula as a custom field. If it is from a different table, we can use the LOOKUP function in the ElastiCube Manager. Presenting too much data Presenting large amounts of data is costly on dashboard load time as well. More specifically this causes slowness when using a pivot or table widget with many rows. The web server has to process the large amount of data and store it to allow the tool to present it visually. If it is possible, limit the number of dimensions that break the data to such a granular form. Sisense generally recommends 6-8 widgets per dashboard for optimal performance. Each widget incurs incremental query and rendering time. Sisense's official add-ons such as Accordion, Jump to Dashboard, and Switchable Dimensions may be leveraged to reduce the number of widgets while preserving the amount of data accessible to end users. When an end user loads the first dashboard in a session, performance will be slower against subsequent dashboard loads, as common web application elements such as logos, toolbars, and code templates are loaded for the first time. Later navigation between dashboards once these resources are cached will be faster. Other causes of query slowness and slow dashboard performance Many to Many If RAM grows exponentially, and the dashboard takes a very long time to load, you could have a possible many to many relationship. A many to many relationship (M2M) can cause extreme slowness in dashboard load time. A M2M is a relationship in the ElastiCube where neither side of the relationship is unique. This creates a Cartesian product, thus consuming all of the machine's resources. Relationship Cycle A relationship cycle exists when there are multiple join paths between two tables in a data model. Sisense will generally opt for the shortest path, but depending on the dimensions used (in widgets or filters), Sisense can take join paths through tables that are unexpected to dashboard designers. While not as deleterious to performance, an additional join on a large table can reduce performance and possibly lead to unexpected results. For more information on relationship cycles in data modeling, please visit the Handling Relationship Cycles documentation page. For the detection of data model issues such as many-to-many relationships and cycles, we recommend using the official Visualize Queries plugin. Data Security If some users are seeing slow performance but others are not, it is worthwhile to check the data security of the user in question. Some questions to consider: Are there complex queries occurring on the ElastiCube due to competing data security settings? Does the data brought in by data security cause pivot tables or other widgets to display a larger amount of data than expected? Please reference the official documentation on data security here. Browser Compatibility If some users are seeing slower dashboard performance on certain machines or browsers, check to make sure they are using a support browser outlined in the Minimum Requirements and Supported Platforms document.Identifying Sisense Services
This article is a reference point for understanding Sisense services and what their purposes are. The notes column contains supplemental information about the service. Please note that this is not an exhaustive list of how you would interact with the services for each scenario. Current List of Sisense Services (as of Version 7.2) Service Name Name Purpose Notes Sisense.Broker Broker Responsible for communication between services. All micro-services utilize the Broker service. In High Availability deployments, this service is associated with RabbitMQ as well. Ensure this service is running as it is a prerequisite for other Sisense services to function. Sisense.CLRConnectorsContainer CLR Connectors Responsible for powering CLR (.NET) connectors. You may restart this service if you're having trouble with particular CLR-based connectors. Sisense.Collector Collector - Sisense Internal Monitoring Responsible for internal monitoring and collection of data about the machine and process performance. This service must be running in order to send logs for Sisense monitoring. Sisense.Configuration Configuration Manager Responsible for powering the Configuration Manager application located at http://localhost:3030 on your Sisense webserver. Also exposes APIs for other configurations. You may restart this service if you find you're having trouble getting the Configuration Manager site to load. Sisense.Discovery Discovery Responsible for saving and storing configuration changes including those made in the Configuration Manager. Typically you will leave this service alone outside of a Sisense migration. Sisense.ECMLogs ElastiCube Manager Logging Responsible for storing build logs from ECM 1.0 and ECM 2.0, PSM, APIS, etc. There should not be any reason for starting this service outside of basic configuration changes (Sisense migration, MongoDB changes, etc). Sisense.ECMS ECM 1.0 (Desktop ElastiCube Manager) Responsible for powering the Desktop ECM. You may restart this service if you're seeing issues building or accessing ElastiCubes on the desktop. Sisense.ECMServer ECM 2.0 (Web ElastiCube Manager) Responsible for powering the Web ECM. You may restart this service if you're seeing issues building or accessing ElastiCubes within Sisense Web. Sisense.Galaxy Galaxy Controls the majority of web server logic. At this time this service also controls: Emails Reporting Export to image You may choose to restart this service in the following example cases: Many API calls that failed with "api not found". User can log in but not able to navigate through dashboards upon login. Emails are not going out. Sisense.Gateway Gateway Main entry point of the Sisense system. This service routes the API between many of the microservices. By default, this services listens on port 8081 and can be changed within the Configuration Manager. You may restart this service if you are having trouble reaching the Sisense site on your designated port. Sisense.HouseKeeper HouseKeeper This service is responsible for memory management of the Sisense application. Typically there is not a need to restart or modify this service. Sisense.Identity Identity Responsible for Sisense groups, roles, users, etc. This service is responsible for authentication and user management. You may restart this service in instances where your user is unable to log in due to errors in authentication (not related to SSO authentication). Sisense.Jobs Jobs Responsible for triggering jobs including emails after a cube build, emailed reports on a schedule, etc. Typically there is not a need to restart or modify this service outside of troubleshooting scheduled jobs. Sisense.JVMConnectorsContainer JVM Connectors Responsible for powering JVM connectors. You may restart this service if you're having trouble with particular JVM-based connectors. Sisense.Orchestrator Orchestration Utilized in HA to synchronize and distribute built ElastiCubes on the build node to the query nodes. You may restart this service changing HA configuration settings. Please consult the HA documentation before restarting this service. Sisense.Oxygen Oxygen (Licensing) Responsible for communicating with Sisense to verify licensing You may need to restart this server if you are having licensing issues within the ECM or Sisense Web. Must be restarted upon license update (along with a logout and log back in). Sisense.Plugins Plugins Management Node.js service which builds plugins. Users do not need to restart this service each time they change plugins. Plugins service watches the plugins folder for changes and runs a build mechanism automatically. Sisense.QueryProxy Query Services Provides a unified way to translate and access the backend SQL Engines. Responsible for query executions, throttling, load balancing, versioning, streaming and results formatting. This service must be running in order for query execution to work. Sisense.Repository Repository Responsible for storing Sisense metadata including: Dashboards Users Data security This service maintains connection to the files contained in MongoDB This service must be running in order for users to view dashboards and log into Sisense Web. Sisense.Shipper Shipper Responsible for shipping logs to Sisense for monitoring. Reads data from C:\ProgramData\Sisense\Monitoring\LOGS folder and ships it. This service must be running in order to send logs for Sisense monitoring. Sisense.SPE Data Prep and Streaming Engine Responsible for powering: Export to Excel Preview table Data Wizard Custom tables and custom columns You should restart this service when changing configuration settings for any of the modules power by it. Sisense.StorageManager Storage Manager Responsible for storing files for file-based connectors such as CSV and Excel files Typically no need to restart or change this service. You should restart this service when changing configuration for this component such as changing the max file size or minimum hard disk space for uploading files.. Sisense.Usage Usage Service This service is responsible for saving off Sisense usage data. Typically no need to restart or change this service. You may restart this service after a change in configuration of the Usage feature. Notable services on Sisense Version 7.1 and below: Service Name Name Purpose Notes ElasticubeManagmentService ElastiCube Management Responsible for powering the Desktop ECM. This service was replaced by Sisense.ECMS and Sisense.ECMServer. You may restart this service if you're seeing issues building or accessing ElastiCubes on the desktop. Sisense.ECMLogsPersistenceService ECM Logs Responsible for storing build logs from the Desktop ECM. This service was replaced by Sisense.ECMLogs. There should not be any reason for starting this service outside of basic configuration changes (Sisense migration, MongoDB changes, etc). Sisense.Pulse Pulse Responsible for powering pulse alerts. This service was replaced by Sisense.Broker. You may restart this service if you are experiencing issues with Pulse across the board.
