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.How To Find Your Sisense Version? (Windows only)
Question: How can I find the exact version and build of my Sisense environment? Solution: There are 3 easy ways to find the exact version of your environment: On the web application Open your Sisense web application On the top toolbar, click on the person icon. The version will be the last item on the menu which pops up: On the Desktop ECM Note: Not relevant for Sisense Linux Open the desktop Elasticube Manager Click the Help & How-To's button on the top scroll bar Click the About button The version will be here: On the web application Open dev tools - console (F12) Copy this and press enter: prism.version The version will be displayed as output:Sharing a file from a local machine in a dashboard
Sisense has the capability to share a local file (excel, word, pdf etc.) with dashboard users. Note: The file must be saved on the server in the following path: %programfiles%\Sisense\PrismWeb. You can create a new folder or save it under 'Resources'. Solution: Add a text widget and select to add a hyperlink. Under 'Target URL' in the following format: http\\:<IP adress:Port>/folder_path/file (The ip can be obtained from the 'ipconfig' command or copying the link under dashboard republish) Example: The URL for the file 'widget.csv' that in located at %programfiles%\Sisense\PrismWeb\Documents is: http://192.168.5.124:8081/documents/widget.csv.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.Get ElastiCube from Process ID
Question I would like to be able to track memory consumption of my ElastiCubes, but I can't find an easy way to link the process ID of my ElastiCube to the cube name. Answer If you open the Task Manager, go to the Details tab and right-click on one of the columns then click on the Select columns option. Find the Command line option. Still on the Details tab, if you search for ElastiCube.exe processes, you can see the ElastiCube name as one of the process arguments (dbname) You can use the following PowerShell (make sure to run as Admin) script to extract all of these into a CSV file: $process = "elasticube.exe" Get-CimInstance Win32_Process -Filter "name = '$process'" | Select CommandLine | Export-Csv -Path "c:\temp\reports.csv"Color a column chart based on another formula
If you need to color columns in a column chart based on a different formula you will need to use the following solution. For this example, we will color the columns that calculate [Total Cost] for the [City] by another value, that is calculated by the formula count([# Visits]) You need to create 5 additional formulas using the function QUARTILE In the formula specify the following: QUARTILE([Dimension], [Formula], 0) [Dimension] - [City] For the formula, it will be - [# of unique # Visits] In the first formula, you need to define 0 quartile - it will return the minimum value Copy the formula 4 times to get the values for all the quartiles. In each formula change the number of the quartile from 0 until 4. You will have the following formulas: QUARTILE([Dimension], [Formula], 0) QUARTILE([Dimension], [Formula], 1) QUARTILE([Dimension], [Formula], 2) QUARTILE([Dimension], [Formula], 3) QUARTILE([Dimension], [Formula], 4) Make sure you save all the formulas: Go to the conditional formatting of the main formula in our case sum([Cost]) and modify the conditional formatting: 6. You will need to specify 5 conditions. Specify the following formula for the first condition: CASE WHEN [# of unique # Visits] = [Quart0] THEN [Total Cost] ELSE 0 END Second condition: CASE WHEN [# of unique # Visits] <= [Qurt1] THEN [Total Cost] ELSE 0 END Third condition: CASE WHEN [# of unique # Visits] <= [Quart2] THEN [Total Cost] ELSE 0 END Fourth condition: CASE WHEN [# of unique # Visits]<=[Quart3] THEN [Total Cost] ELSE 0 END Fifth condition: CASE WHEN [# of unique # Visits] <= [Quart4] THEN [Total Cost] ELSE 0 END The [Quart0-4] (is the saved formula for the 0-4 quartile that we saved at step 4) Specify the colors accordingly: Make sure that all the conditions are set to equal: Then you can disable the formulas to leave only the relevant columns on the widget: As a result, you have columns that are colored according to the calculation in another formula:Pixel Perfect Reporting Using Crystal Reports and Open Sisense (ODBC)
While Sisense provides powerful analytics and simple report printing, if pixel perfect report printing is required, additional tools like SAP Crystal Reports must be used. Purpose/Benefit Pixel Perfect reports can be designed which use data from a Sisense Elasticube. These reports contain all Crystal Reports features including exact control of size and position of report elements, headers, footers, etc. Prerequisites Sisense and Crystal Reports have been installed. Knowledge of how to design Crystal Reports. Steps Step 1 - Set up ODBC Connection to an existing Elasticube Follow these instructions to set up an ODBC connection to one of your Elasticubes. For our purposes, we will create a connection to our Sample Healthcare Elasticube (Included with Sisense install). We will name the connection "Sisense Healthcare Sample". Note: You must install the 32-bit driver since Crystal Reports uses 32-bit architecture. When setting up your connection, you must do this in ODBC Data Sources (32-bit). Step 2 - Create a new blank report in Crystal Reports and pull in data In the Database Expert dialog box expand the "Create New Connection" selection. Expand the "ODBC (RDO)" section. A dialog box should pop up. Select the Sisense Sample Healthcare data source and click finish. Select the tables you will be using for your reports and click the '>' button to add them to the Crystal Reports data model (you may only need a couple tables for a particular report). Link the tables needed for your report and click OK. Step 3 - Build your report You can now access data from the Elasticube to build a Crystal Report! Step 4 (optional) - Embed Crystal Report into Sisense Dashboard Referring to the following links, you may embed a crystal report into a Sisense Dashboard or export a pixel perfect reprt to PDF from a dashboard: ReCrystallize Pro - Allows viewing of Crystal Reports in a web browser Jump To Dashboard Plugin - Includes different configurable options for pop ups or links. This add-on is pre-installed on Sisense in Linux environments and its version could be different. The download link is for Sisense on Windows.Performing an OR between dashboard filters
Analytical Need A common requirement in data analysis is to be able select values from several filters and to see the results that contains all of those values and not only the results where only selected values appear. In fact, the requirement is to have an OR and not an AND between the filters. For example, I have 2 filters of country & product. I choose country "UK" and product "A": AND will get me results just for product "A" sold in the UK OR will get me results for all the products sold in the UK or all the countries that product "A" has been sold in. Challenge The default behaviour of the filters in a dashboard is AND. We need to find a way to turn this into an OR. Solution We will use the filtered measure plugin to display the OR result. This add-on is pre-installed on Sisense in Linux environments and its version could be different. The download link is for Sisense on Windows. Our data: When we select product A and country UK we don't want to see the second record (B, Greece), but we do want to see all the rest. Dashboard filters: In order to get the desired result we will define the following formula: (sum([value]),[@Country])+(sum([value]),[@Product]) The @ sign means that this part of the formula refers only to the mentioned dashboard filter. The + sign is actually a sum between the values. However, this is only getting us half way, since for the records that contain both UK and product A - we will get the amount doubled like so: We need to divide these result by the amount of filter values the record is associated to. In this case, we need to divide the first row by 2 (because we have UK & A) and the rest by 1 (we have either UK or A). In order to achieve this calculation we will need to use this formula: (([# of unique Country],[@Country])+ ([# of unique Product],[@Product])) So the final formula will be the division between the 2: ((sum([value]), [@Country]) + (sum([value]), [@Product])) / (([# of unique Country], [@Country])+ ([# of unique Product], [@Product])) Now the results are correct: In case you have more than 2 filters, you'll need to add more calculations to the nominator & the denominator. Note: the rest of the dashboard filters that don't participate in the formulas will behave with the normal AND functionality. Cube Detailed calculations The default behavior (no filter measure)
