How 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.Maximize 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.Connecting to Oracle DB with Service Name (instead of Service ID - SID)
Connecting to Oracle DB with Service Name (instead of Service ID - SID) When a customer tries to connect to an Oracle DB using a Direct Connection option, there's no option in the GUI in the ElastiCube Manager to use a Service Name, only a Service ID: Solution 1) Uncheck the Use Direct Connection checkbox. 2) In the Database server location textbox, enter the following: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$your_ip)(PORT=$your_port))(CONNECT_DATA=(SERVICE_NAME=$service_name))) The variables you need to input are: $your_ip $your_port $service_name 3) Connect to the source.
Customizing a Pivot 1.0 Widget
Introduction The following document provides code snippets for customizing a Pivot 1.0 widget Table of Contents How to Use This Page? To implement the customizations on this page you’ll have to be familiar with the following: Basic JavaScript and JQuery Widget Lifecycle How to add a widget script Widget Customizations The easy way to reference cells is by referencing their class type or ID via JQuery: All headers are identified using unique class names The table contents (data cells) are referenced by a table object (identified using a special ID) The following section introduces the different table structures and classes used for pivot charts Layout #1 - Single Row, Single Value, No Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines a single value (Sum Revenue) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Total Revenue”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #2 - Single Row, Multiple Values, No Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines two values (Sum Revenue & Sum Quantity) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #3 - Multiple Rows, Single Value, No Columns This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines a value (Sum Revenue) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cell (“Total Revenue“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Total Revenue”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #4 - Multiple Rows, Multiple Values, No Columns This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines two values (Sum Revenue & Sum Quantity) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #5 - Single Row, Single Value, Single Column This layout includes the following configuration: The user has defined a single row (Months) The user defines a single value (Sum Revenue) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cell (“19-24” & “25-34“) A class of a <td> object td.p-measure-head “Grand Total Column Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #6 - Multiple Rows, Single Value, Single Column This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines a single value (Sum Revenue) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cells (“19-24” & “25-34“) A class of a <td> object td.p-measure-head “Grand Total Column Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #7 - Single Row, Multiple Values, Single Column This layout includes the following configuration: The user has defined a single row (Months) The user defines multiple values (Sum Revenue & Sum Quantity) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Column Label Column Header” cells (“19-24” & “25-34“) A class of a <td> object td.p-dim-member-head Individual “Column Value Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-measure-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #8 - Multiple Rows, Multiple Values, Single Column This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines multiple values (Sum Revenue & Sum Quantity) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Column Label Column Header” cells (“19-24” & “25-34“) A class of a <td> object td.p-dim-member-head Individual “Column Value Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-measure-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cells (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #9 - Single Row, Single Value, Multiple Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines a single value (Sum Revenue) The user defines multiple columns (Age Range & Brand ID) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) A class of a <td> object td.p-dim-member-head Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) A class of a <td> object td.p-total-head Global “Grand Total Column Header” cell (“Grand Total“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #10 - Single Row, Multiple Values, Multiple Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines multiple values (Sum Revenue & Sum Quantity) The user defines multiple columns (Age Range & Brand ID) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) A class of a <td> object td.p-dim-member-head “Value Label Column Header” cell (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-measure-head Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) & (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-total-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #11 - Multiple Rows, Single Value, Multiple Columns This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines a single value (Sum Revenue) The user defines multiple columns (Age Range & Brand ID) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) A class of a <td> object td.p-dim-member-head Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) A class of a <td> object td.p-total-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cells (quarter and month names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cells (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Manipulating the Table Structure Table Alignment The table is aligned to the left-hand side of the screen by default. To align the table to the right-hand or the left-hand sides we’ll first have to get rid of the “fake” phantom column. Before After To align the table location use the following snippet: // This script aligns the table to the center of the widget function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Remove the phantom column and cells $('td.phantom',element).remove(); // Center the table doc = getWidgetDocumentObject(widget); doc.querySelector('.p-container').style.display='block'; doc.querySelector('.p-container').style.marginLeft='auto'; doc.querySelector('.p-container').style.marginRight='auto'; doc.querySelector('.p-container').style.width='fit-content'; }); Manipulating the Header Cells Aligning Text To align headers' cell text alignment use the following snippet: (note - Change the JQuery element name to align with the correct header you wish to format) // This script modifies the “Row Label Column Header” cells' alignment widget.on('domready', function(widget) { $('td.p-dim-head',element).css('text-align','center'); // Horizontal $('td.p-dim-head',element).css('vertical-align','middle'); // Vertical }); Changing the Caption of the “Grand Total” Header To replace the default text of “Grand Total” use the following snippet: // This script replaces the header of the "Grand Total" row header to "Total" function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { doc.querySelector('td.p-grand-total-row-head').querySelector('div.p-head-content').innerText = "Total" }); Manipulating the Data Cells Aligning Text To align all data cells' text alignment use the following snippet: // This script modifies the data cells' alignment widget.on('domready', function(widget) { $('td.p-value',element).css('text-align','center'); // Horizontal $('td.p-value',element).css('vertical-align','middle'); // Vertical }); Replacing the Contents of (All) Empty Cells To replace the blank space in the empty cells using the following snippet: // This script modifies the contents of blank data cells to '-' // Note - A blank cell contents are the character '\xa0' function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Replace blank cells doc.querySelectorAll('div.p-value').forEach((element) => { if (typeof element === "object" && element.innerText == '\xa0') element.innerText = '-' }); }); Modifying the Contents of Cells From Specific Columns Color Based on "Top-Level Header" Cells The term “Top-Level Header Cells” refers to one of the following: If the table has no columns - “Value” headers If the table has one column - “Column” headers If the table has multiple columns - Not covered The following snippet colors the cells under a Top-Level Header named 'No': // This script colors the cells under a Top-Level Header called 'No' function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Define the column title columnToColorValue = 'No' widgetDoc = getWidgetDocumentObject(widget); // Calculate the amount of columns in the table dimColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-head').length; // Calculate the amount of "Values" in every "Column" measureColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-measure-head').length; // Scan all columns in the header row for (let i = 0; i < widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head').length; i++) { // Retrieve the <i>'th column in the table col = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head')[i]; // Check if column should be colored red shouldColorRed = (col.innerText === columnToColorValue); // Check the column's width (as it might contain multiple sub-columns (values) underneath it columnWidth = col.getAttribute('colspan'); if (columnWidth === null) columnWidth = 1; // If it contains a single column there's no colspan attribute // For every value column for (let j = 0; j < columnWidth; j++) { // Calculate offset from end of table (this value changes in different rows) colOffset = measureColumns - ((i * columnWidth) + j); // Extract the table structure rows = getWidgetDocumentObject(widget).querySelector('#pivot_').rows; // Iterate through all data cell rows for(let r = 1; r < rows.length; r++) { cells = rows[r].cells; rowCellCount = Object.values(cells).length; cellToHandle = rowCellCount - colOffset; if (cells[cellToHandle].getElementsByClassName('p-value').length > 0) { cellToManipulate = cells[cellToHandle].querySelector('.p-value'); if (shouldColorRed) cellToManipulate.style.color = 'red' } } } } }); Alter Text Based on "Bottom-Level Header" Cells The term “Bottom-Level Header Cells” refers to one of the following: If the table has one column - “Value” headers (applies to the “Value” column in each “Column”) If the table has multiple columns - Not covered The following snippet alters the text of cells under a Bottom-Level Header named 'No': This script alters the text of cells under a Top-Level Header called 'Average Cost' (changes empty cells to '---'): // This script alters the text of cells under a Bottom-Level Header called 'Average Cost' // Note - A blank cell contents are the character '\xa0' function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Define the column title, old text, and new text columnToFixText = 'Average Cost' originalText = '\xa0' newText = '---' widgetDoc = getWidgetDocumentObject(widget); // Calculate the amount of columns in the table dimColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-head').length; // Calculate the amount of "Values" in every "Column" measureColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-measure-head').length; // Scan all columns in the header row for (let i = 0; i < widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head').length; i++) { // Retrieve the <i>'th column in the table col = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head')[i]; // Check the column's width (as it might contain multiple sub-columns (values) underneath it columnWidth = col.getAttribute('colspan'); if (columnWidth === null) columnWidth = 1; // If it contains a single column there's no colspan attribute // console.log('found column ' + i + ' width = ' + columnWidth + ' that fits the condition'); // For every "Value" for (let j = 0; j < columnWidth; j++) { // Calculate offset from end of table (this value changes in different rows) colOffset = measureColumns - ((i * columnWidth) + j); // Extract the table structure rows = widgetDoc.querySelector('#pivot_').rows; // Iterate through all rows shouldFixText = false; for(let r = 1; r < rows.length; r++) { cells = rows[r].cells; rowCellCount = Object.values(cells).length; cellToHandle = rowCellCount - colOffset; if (cells[cellToHandle].getElementsByClassName('p-head-content').length > 0 && cells[cellToHandle].querySelector('.p-head-content').innerText === columnToFixText) shouldFixText = true; if (cells[cellToHandle].getElementsByClassName('p-value').length > 0) { cellToManipulate = cells[cellToHandle].querySelector('.p-value'); if (shouldMakeBold) cellToManipulate.style.fontWeight = 'bold'; if (shouldFixText && cellToManipulate.innerText === originalText) cellToManipulate.innerText = newText; } } } } });Analyze Period over Period - MoM / WoW / DoD
Analytical Need There are cases where we want to analyze our measures as a comparison between periods of time. For instance, ee want to know how did our measures behave on January this year vs. previous year. This means that we have to put down in our x axis the common period (Month, Week, Day). Modeling Challenge But when we select the time dimension as the x axis, it will show us a sequential order of it, like so : Image 1. dashboard without period over period We would like to see the day name / month name/ week no. (without the year). In order to do this, we will need to create it in advance in the cube. Solution In our dim date table, we will create a designated field for month name, day name, week #. SQL for the dim date table: SELECT DISTINCT s.OrderDate AS Date, CASE WHEN getMonth([OrderDate]) = 1 THEN '01Jan' WHEN getMonth([OrderDate]) = 2 THEN '02Feb' WHEN getMonth([OrderDate]) = 3 THEN '03Mar' WHEN getMonth([OrderDate]) = 4 THEN '04Apr' WHEN getMonth([OrderDate]) = 5 THEN '05May' WHEN getMonth([OrderDate]) = 6 THEN '06Jun' WHEN getMonth([OrderDate]) = 7 THEN '07Jul' WHEN getMonth([OrderDate]) = 8 THEN '08Aug' WHEN getMonth([OrderDate]) = 9 THEN '09Sep' WHEN getMonth([OrderDate]) = 10 THEN '10Oct' WHEN getMonth([OrderDate]) = 11 THEN '11Nov' WHEN getMonth([OrderDate]) = 12 THEN '12Dec' ELSE '' END AS OrderMonthWithNum, CASE WHEN DayOfWeek(OrderDate) = 1 THEN '01Sunday' WHEN DayOfWeek(OrderDate) = 2 THEN '02Monday' WHEN DayOfWeek(OrderDate) = 3 THEN '03Tuesday' WHEN DayOfWeek(OrderDate) = 4 THEN '04Wednesday' WHEN DayOfWeek(OrderDate) = 5 THEN '05Thursday' WHEN DayOfWeek(OrderDate) = 6 THEN '06Friday' WHEN DayOfWeek(OrderDate) = 7 THEN '07Saturday' ELSE '' END AS Day, WeekOfYear(s.OrderDate) AS Week FROM [SalesOrderHeader] s We will use the new fields as our x axis fields. The reason we have created the month & day fields with the number prefix, is in order for them to be sorted correctly in the dashboard (we used the Chart Label Order Changer plugin for that purpose). Dashboard: Image 2. Dashboard display of period over period Attachments - 71 KB - PeriodOverPeriod.dash - 536 KB - PoP.ecdataTroubleshooting Export to Excel Issues
Introduction In version 6.7, Sisense introduced support for exporting pivot table widgets to Microsoft Excel. For general information about the feature and how to use it, please review https://docs.sisense.com/win/SisenseWin/downloading-pivot-tables-to-excel.htm. We strongly recommend using version 6.7.1.15016 or later to successfully export widgets to Excel. Please see our release notes for more information about issues on earlier versions. Issues and Troubleshooting Issue: Export fails with the error "The file could not be generated" Root Cause: Multiple Resolution: For customers using a reverse proxy, load balancer, or bound host name in IIS, please follow Step 6 on in our SSL documentation page (https://documentation.sisense.com/setting-up-ssl/) and try again In your web browserNavigate to http{s}://{host:port}/engine/test and confirm the Excel engine is working. Expected result: {"test":"engine works properly"} Review for operating system processes that occupy the same port as your Sisense site by running "netsh http show urlacl" in the Command Prompt (cmd.exe). If a conflict is identified, run "netsh http delete urlacl url=http://*:{port}/". For example, "netsh http delete urlacl url=http://*:{8081}/". Afterwards, restart IIS by running 'iisreset' in the Command Prompt as an administrator. For further assistance, please contact Sisense support and include your logs (see information below) --- Issue: Excel export is generated but content contains garbled HTML Root Cause: Multiple Resolution: Same steps as above --- Issue: Excel exports are generally successful but fail on widgets that utilize the metadata plugin (https://docs.sisense.com/main/SisenseLinux/translating-sisense-metadata-on-linux.htm) Root cause: Conflicts between the Metadata plugin and Sisense's Excel export generation Resolution: Please contact Sisense Support --- Issue: Excel export is successful but contains incorrect formatting or styling Root cause: Limitation in Sisense version 6.7-7.0 Resolution: Upgrade to Sisense 7.0.1 or later --- Issue: Export export is successful but column sorting is incorrect Root cause: Limitation in Sisense version 6.7-7.0 Resolution: When available, upgrade to Sisense 7.1 or later --- Issue: Excel export contents do not match the pivot table contents Root cause: Issues with Sisense's Excel export engine Resolution: Our developers are aware and currently working to resolve these uncommon issues. For more information, please contact Sisense Support. Exporting Programmatically (including embedded analytics/OEM scenarios) For customers embedding Sisense who want to replicate the export to Excel functionality, please see the information available in the 1.0 version of our REST API documentation for a sample script: https://sisense.dev/guides/restApi/. To generally export widgets to Excel via the REST API, use the api/v1/engine/excelExport endpoint. The response is binary content; catch and write it to a .xlsx file in your script. Export Logs Excel export logs are located by default at C:\ProgramData\Sisense\PrismWeb\Logs\engineLogs.log. Disabling Exports Customers who wish to disable Excel exports can disable CSV and Excel exports through the Sisense REST API. The endpoint is api/roles. For more information, see https://docs.sisense.com/main/SisenseLinux/customizing-user-roles.htm. Run a PUT call to set the value for the export_csv property under Widgets to false. This change will need to be run for each role you want to affect.Ranking range
Introduction Many times we'd like to analyze our data according to top/bottom ranking positions, for this we have out-of-the-box ranking filters. At other times, we'd like to see certain ranges, such as the ranking range between certain ranks, i.e. positions 5-10 or 11-50. Our standard top/bottom ranking filters will not suffice for this analysis. Example In this example, we'd like to focus our attention on our product categories ranking 4-6 to see how to improve our medium-selling items. For this, we'll use a combination of the RANK() function (https://docs.sisense.com/win/SisenseWin/function-references.htm) and then determine which ranking indexes we'd like to display: Steps 1. Regardless of the ultimate widget you'd like to use, tart with a pivot table to see all the categories and actual data. 2. Add the RANK() function, the first argument within this function should be the measure according to which you'd like to apply the ranking, in our case SUM(Quantity). The second argument is the manner in which we'd like to arrange our ranking, "ASC"/"DESC" in our case we'll use "DESC": This will create the Categories' indexing according to our top ranking. 3. We will now apply a filter by value on our newly created RANK function, use the range which you'd like to display, in our case, we'd like to show the ranking values that are between 4-6: That's it! you will now be able to show the specific ranking range required, you can even go ahead and disable the RANK function toggle switch so it will not be displayed within the widget.Error exporting to PDF
Sisense allows you to generate a PDF report of your dashboard when you need to take copies of your dashboards with you for meetings or sharing them with others. This article gives detailed troubleshooting steps on how to handle PDF export failures on all Sisense versions. How Does It Work? When you click Download PDF or the PDF icon in the menu bar , a preview of your report is opened. After you have defined the appearance of your report, you can save the layout of the dashboard by clicking Save. The next step is to click Download PDF, your saved layout will be downloaded into a PDF file. Disable All Plugins One of the most common causes of a PDF export failure is due to a faulty plugin. If you cannot export any report to PDF, then the issue is most likely due to plugins. Firstly, disable all the plugins. To do so, go to Admin → Plugins under System Configuration and toggle the switch to disable each plug-in. To disable all plugins at once, select each Plug-in and toggle the Disable Selected switch: If the report is exported to PDF successfully, then you can switch on the plug-ins one-by-one to find the problematic one. When you find a problematic plug-in, make sure you have the latest version of the plug-in installed. If disabling all plugins does not work we recommend going to the actual "plugins" folder on the Sisense server and removing all the plugin folders manually. For versions 7.2 and higher the "plugins" folder can be found in C:\Program Files\Sisense\app\plugins For versions 7.1 and lower the "plugins" folder can be found in C:\Program Files\Sisense\PrismWeb\plugins Disable All Scripts An easy way to determine where the issue is, is to create a new simple widget and try to export it. If the dashboard is exported successfully, it means that the issue is local, and you need to check the scripts applied to your widgets and dashboards. Try to disable your scripts to understand if it affects your PDF exports. Once you find the broken script, you will need to fix it. To disable a script, you need to open the Script Editor and disable your script by commenting it out. It can be commented out by adding ‘//’ to the beginning of each row. For dashboard scripts, for the dashboard, in your dashboard, click Edit Script For a specific widget, click on the widget in Edit mode. 7.2 And Later Further Troubleshooting If you are using Sisense installation which is on V7.2 and above and still experiencing errors with PDF exports, it is possible that it is related to a Configuration Manager setting. To troubleshoot this, on a Sisense webserver take the following steps: (please note that this could make your site unavailable for a short period of time so take care to do this off-hours if in a production environment) In a web browser (Chrome is preferred but any of the supported web browsers are OK), navigate to http://localhost:3030 You will see the Configuration Manager appear Scroll down to the "Domain Binding" section. If this field is populated, remove the entry. There are only a few specific reasons to have this set. If your Sisense domain is attached to the server via a DNS entry you typically do not need to set this. Click the 'Save' button at the top right-hand corner 7.1 And Earlier Further Troubleshooting If you are using Sisense installation which is below V7.2 and still experiencing errors with PDF exports, it is possible that it is related to a local configuration file issue. To verify the configuration is correct, please follow the below: If you have an domain defined on your server, make sure the setting in 'Admin'-->'system configuration' does not include "http://" Make sure that you have access to the domain URL from the local server (ie. http://bi.my.company.com/) If you have SSL configured - please make sure you can browse to the secure site locally as well (ie. https://bi.my.company.com/) Check bindings: Open the Windows IIS Manager -> Navigate to each of the websites -> "Bindings" and make sure that no two websites bind to same port (double bindings) Go to "Add or Remove Programs" -> Right click on Sisense and choose "Change..." -> In installation window "Continue" -> "Change Settings" and verify that the port configured matches the port specified in the IIS Manager for Sisense Web If these troubleshooting steps do not fix the issue, you may need to edit the exporting section of the default.yaml file. Find the file here: C:\Program Files\Sisense\PrismWeb\vnext\config The exporting section is located at the end of the file, and there are 3 variables you may have to change- host, port, and protocol. Please set these variables to the values that are used to access Sisense Web on the server. For example, if you access Sisense Web by going to http://test.dashboards.com, you would set the variables tohost: "test.dashboards.com" port: 80 protocol: "http" After making the changes, save the file, and restart IIS. Log Files For further investigation detailed logs on the any PDF related errors can be found below For Sisense V7.1.3 and earlier: C:\Program Files\Sisense\PrismWeb\vnext\iisnode For Sisense V7.2 and later: C:\ProgramData\Sisense\application-logs If you are still seeing issues after following this guide please feel free to contact Sisense Support for further assistanceHost a New Webpage on IIS (Windows only)
It some situations, it may be useful to be able to use IIS to host website that work with Sisense. A common example would be hosting a webpage that contains the code for an embedded dashboard. Additionally, it could be useful in scenarios when you want to host a web page to be used as your custom home page during rebranding, or when hosting a webpage for the iframe widget This article shows a simple way to host your own webpages using IIS. 1. Add a New Directory to IIS The directory we will add to IIS needs to exist like any other file path, and needs to contain the webpage we want to use. We will create a reference to this directory in IIS so it can be accessed by other users, and parsed correctly by a browser. To do this, open IIS, right click on SiSenseWeb, and go to Add Virtual Directory For this example, I created a new folder to hold all of my webpages at C:\embedded, and I gave it the alias of 'testpages' in IIS. Since I used the default installation settings (localhost:8081), the file C:\embedded\adventure_works.html will be accessible at localhost:8081/testpages/embedded/adventure_works.html. After this, right click the new virtual directory you've created, go to Edit Permissions..., go to the Sharing tab, and click share. From here, use the drop down list to find Everyone, and give them Read access permissions. If a popup appears asking if you like to turn on network discovery, click Yes. In this example, I'm going to host a simple html web page that uses an iframe to display an embedded dashboard. This webpage can be created with notepad. Just be sure to save it with a .html file extension. If you are unfamiliar with embedding dashboards, you can find more information about them here: https://docs.sisense.com/main/SisenseLinux/embedding-dashboards-and-widgets.htm In the example from the documentation, the basic iframe code to embed a dashboard will look something like this: <iframe id=”ifm” name=”ifm” width=”100%” height=”100%” frameborder=”0″ src=“http://mysite.com/app/main#/dashboards/536f3a54d093e26c28000114/widgets/536f3a54d093e26c2800011b?embed=true″ scrolling=”auto”></iframe> However, you'll need to wrap this code in some standard html tags, so it gets parsed correctly by the browser. These tags will give you the format of: <html> <head> </head> <body> <iframe id=”ifm” name=”ifm” width=”100%” height=”100%” frameborder=”0″ src=“http://mysite.com/app/main#/dashboards/536f3a54d093e26c28000114/widgets/536f3a54d093e26c2800011b?embed=true″ scrolling=”auto”></iframe> </body> <html> Now, save the this file in the physical directory you created to be used by IIS. (In my case its C:\embedded). Now we can access this embedded dashboard, with the html code that's attached to this post.
