Sisense FAQs

-------------------------------------------------------

See updates at the end of this post

-------------------------------------------------------

A heads-up, Chrome has announced a browser update, to be included in the Chrome 80 release, scheduled for February 2020. The version will change the default cross-domain (SameSite) behavior of cookies. The change is a security enhancement that will affect Sisense deployments that rely on cookies, such as those that use cross-domain embedded IFrames or SisenseJS.

The Chrome Platform Status post available here, explains the changes to the SameSite attribute of cookies, and its effect on cross-domain behavior. The change is explained there as follows: ““SameSite” is a reasonably robust defense against some classes of cross-site request forgery (CSRF) attacks, but developers currently need to opt-into its protections by specifying a SameSite attribute. In other words, developers are vulnerable to CSRF attacks by default. This change would allow developers to be protected by default, while allowing sites that require state in cross-site requests to opt-in to the status quo’s less-secure model. In addition, forcing sites to opt-in to SameSite=None gives the user agent the ability to provide users more transparency and control over tracking.”

Additional information on this change is available on the Chromium blog.

What Does This Mean for Me?

Web applications that includedcross-domainembedded dashboards (for example using IFrames) that rely on cookies might no longer work, when accessed using the Google Chrome browser, until configured to use the “sameSite=None” attribute and to use secure browser access (HTTPS).

What Do I Need to Do?

Configure your system to use HTTPS instead of HTTP. Instructions for configuring SSL for Sisense are available here.

We will be providing two fix options, you can choose from:

Service Pack: We will be providing a service pack (upgrade to 8.1.1 SP1) for Sisense shortly. The service pack will allow configuring the value of the “sameSite” attribute. Once the service pack is available, you must upgrade your Sisense server to the service pack version.Use the Admin settings available at Admin -> System Configuration -> Settings -> Security settings, and change the value of the sameSite attribute to “none”.

Fix script: We will be providing a fix script that will automatically configure the system to use sameSite attribute "none".

Please keep a lookout for updates to this announcement.

Resources

Chrome Update Schedule

Chromium Blog: Developers: Get Ready for New SameSite=None; Secure Cookie Settings

Chrome Platform Status: Cookies default to SameSite=Lax

Chrome Platform Status: Reject insecure SameSite=None cookies

OWASP.Org post SameSite

NetSparker Blog post Using the Same-Site Cookie Attribute to Prevent CSRF Attacks

Mozilla documentation regarding the Set-Cookie header Set-Cookie HTTP

Regards,

Ahuva Hazan-Fuchs

Product Director, Platform & Cloud

-------------------------------------------------------

Updates Feb 4, 2020

-------------------------------------------------------

We have now provided resolutions for all Windows versions starting from version 6.7.1.You can choose between multiple resolution options:

Upgrading to Windows 8.1.1. service pack - available for all Windows versions.

Version 7.2 and later: installing one of the fix scripts.

Versions 6.7.1 - 7.13: Manual guidelines for resolution

Installing 8.1.1. Service pack 1: this option is available for all Sisense Windows versions

These are the instructions for installing version 8.1.1 service pack 1:

Ensure your system is configured to use HTTPS.

Download the 8.1.1 service pack 1 from here.

Upgrade your Sisense servers to version 8.1.1 service pack 1, using the regular Sisense upgrade process.

Use the Admin Screen -> System Configuration -> Settings -> Security Settings option to configure your system to use SameSite=None.

All users will be automatically logged out after clicking on the Save button. This can be done manually by running an API request using the POST method by /authentication/admin/logout_all_users. This command must be run by an Administrator or Sys. Admin user.

Sisense deployments running version 7.2 and later with a load balancer or any other Proxy server (all client-side requests go through SSL termination; Proxy forwarding all requests to api-gateway using HTTP) must be configured to force Secure flag in all cookies by changing theForce secure cookies attributeavailable for update using the Configuration Manager.

Installing a fix script: this option is available for versions 7.2 and later:

For versions 7.2 and later, two script versions are available:

A version for Sisense deployments running version 7.2 and later, available for download here.

A version for Sisense deployments running version 7.2 and later with a Load Balancer or any other Proxy server (all client-side requests go throughSSL termination; Proxy forwarding all requests to api-gateway using HTTP), available for download here.

Instructions for running the scripts are as follows:

Configure your system to use HTTPS.

Download the relevant script.

Unzip the script.

From the command line (using Administrator permissions), run: run_patch.cmd

The script will configure the system to use “sameSite” value “none”.

The script for deployments using a load balancer or proxy servers will additionally force the Secure flag for all cookies.

All users can be logged out byrunning API request using the POST method by/authentication/admin/logout_all_users path under Admin or System Admin user.After their next login, every user will be generated a new cookie with the correct values.

Manual fix: This option is available for versions 6.7.1 and later until 7.2 (not including 7.2):

For versions 6.7.1 and later until version 7.2 (not including version 7.2), a manual process is provided. The process is relevant for all Sisense deployments, including those using a load balancer, or SSL/TLS termination proxy.

The following manual steps are available to handle the Chrome changes:

Configure the system to use HTTPS.

Define Cookie Security according to the instructions in the documentation here.

Manually update theWeb.Config file located at: C:\Program Files\Sisense\PrismWeb as follows:

In the <system.web> element, add the following element to set the Secure flag on an ASP.NET Session Cookie:

<httpCookies httpOnlyCookies="true" requireSSL="true" />

In the <system.webServer> <rewrite> element, add the following elements with SameSite=None (to allow cross-domainembedding) or SameSite=Lax value.

<outboundRules>

<clear />

<rule name="Add SameSite" preCondition="No SameSite">

<match serverVariable="RESPONSE_Set_Cookie" pattern=".*" negate="false" />

<action type="Rewrite" value="{R:0}; SameSite=None" />

</rule>

<preConditions>

<preCondition name="No SameSite">

<add input="{RESPONSE_Set_Cookie}" pattern="." />

<add input="{RESPONSE_Set_Cookie}" pattern="; SameSite=None" negate="true" />

</preCondition>

</preConditions>

</outboundRules>

3. Replace<anonymousIdentification cookieless="UseCookies" enabled="true" /> by

<anonymousIdentification cookieless="UseCookies" enabled="true" cookieRequireSSL="true"/>

4. Manually update authCookieRequireSsl key the<securityConfiguration> element in security.config file located at C:\Program Files\Sisense\PrismWeb\App_Data\Configurations as follows:

authCookieRequireSsl="true"

5. Logout all users by removing the device key in the user's DB model. After login, every user will get a new cookies with correct values. Please contact our support for any question.

These fixes have been verified to work with the Okta SSO provider. They do not yet work for the OneLogin SSO provider, as OneLogin has not yet added support for the sameSite cookie option, which will be required by Google Chrome version 80. We expect OneLogin to be adding this support shortly, as the issue will affect many of their other customers.

We expect the Linux service pack to be available later this week.

I will continue to post updates about this issue.

Regards,

Ahuva Hazan-Fuchs

Product Director, Platform & Cloud

This article will explain how to find the data and log files on your Sisense server.

All of the Sisense data and log files are located in sub-folders under:

C:\Program Files\Sisense

Or

C:\ProgramData\Sisense

Where is my C:\ProgramData Folder?

By default,C:\ProgramData is a hidden folder in Windows.

There are two options for accessing this folder.

Using the \%ProgramData\% Environment Variable

Open File Explorer

In the Address Bar type in \%ProgramData\%

How to export .ecdata and .dash files

Changing your File Explorer settings

Open the Control Panel and click on "File Explorer Options"

In the File Explorer Options window Click on the "View" tab

Go to the "Hidden files and folders" option in the Advance Options pane

Select "Show hidden files, folder, or Drives"

Click "Apply"

The "Program Data" folder will now show in you File Explorer

Common data and log file paths

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

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

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

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

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

Related Topics

Browsing our site, speaking to our team, or reading about us in analyst reviews, youmight have noticed that we dig technology here at Sisense. That’s why when clients andprospects desire to pushthe limits, both in terms of data complexity and quantity, wehappily oblige.

After asking what our recommendations would be for the most data to host in a singleSisense server, one newly signed client (a prospect at the time) passed usone billiontransactional records andthree milliondimensional recordsto hostin a single Sisensenodethat’s500gbof data to test with100 concurrent userslogging in and bangingaround on the server.We used a 32 CPU core and 244gb RAMcloud machinefor the job,inagreement withour straightforward specs.We’ll cut to the chase and share the details from Load Impact below.

Tested Setup

AWS Instance-r4.8xlarge (32CPU, 244gb RAM)

100 Concurrent Users

120 minutes

38 Max Concurrent Queries

Sisense Concurrency isdefined as querying within the same millisecond

2 types of usage scenarios

50\%of users returned results from the entire billion record dataset

50\%of the users viewed a subset of data, simulating use by clients whosee only their own data

Conclusions

Query response time averaged 0.1 seconds and maxed at 3.1 seconds. Thisrepresents the time for Sisense to receive a query from the web application andreturn a result set to the client application.

The Sisense ElasticubeRAM consumption remained stable at approximately100gb despite the 500gb+ of data loaded into disk of the Elasticube Server.

The average CPU usage during the load test was approximately 10-20\%.This is spread across all of the distinctCPUcores.

Performance Details

We used a tool calledlogz.ioto analyze the server performance during the load test to aggregate logs into kpi’s which we can analyze to determine the impact on the server and determine impact in production.

Here’s what thosequery performanceresults looked like across the hour-long test. To summarize, no query took longer than3.1secondsto return results to the web front end.

When it comes to theserver usage, we passed the test in flying colors as well. Ouramazing in-chip technology was on full display-we hosted500gb of data withoututilizing more than 128gb of RAM. CPU utilization during query times never rose above75\%throughout the load test, and it averaged less than 20\%.

Methodology

We used a tool calledLoad impactto create artificial users that log in and interact with dashboards to mimic production. That includes the following types of actions in Sisense:

Loading a dashboard with nine widgets

Changing filter from one account to another and from one year to two years

Filtering by clicking on context from one chart to control the others

Drilling from country town to region-level data

Downloading a .csv of the information in a Sisense widget

Switch dashboard, repeat all steps above.

The two different user types (scenarios 1 and 2 below) performed the same steps. One group, however, had a where clause appended to all their queries to limit their view to one out of the seven customer accounts. This simulates the external, OEM use case for deploying to clients to view your dashboards.

Here is a visualization describing the usage pattern over the timeframe. Across the two hours on the x-axis, the number of virtual users (VUs) is displayed on the y-axis.As you can see, the number of users ramped for 50 minutes, remained steady for 10 minutes,and then did the same thing during the second hour.

The concurrentnumber of queries over the two-hour test increased throughout the period of testing, asshown below. In Sisense, concurrency represents two or more users initiating a query within the same millisecond.

Data Details

The data represented one billion purchasesonawebsite, each with its own unique transaction ID. The purchases were splitinto three categories-planes, trains, and automobiles. Furthermore, the analysts wanted to kick the tires on Sisense’s ability to join large tables on demand. On user request, a three millionrecord dimension table would join with that one billion recordfact table to provide revenues from the fact table grouped by origin/destination combinations, contained in the dimension table.

TheElasticube lookedlike this:

Dashboard Details

At the end of the day,the clientwanted dashboards that tracked revenues, bookings, and average revenues per booking across time, across client types and fee types.

Here's oneof thedashboardsused during the testing:

Connection Table.ecdata Analytical Need

There are cases where we need to define an association between two entities. This association is comprised of the entities and not by each of them separately.

For example, students & courses. We need to have a list of each student, a list of all courses and a list of which student is listed at which course (just like the example in Table Types )

Modeling Challenge

A table in our cube will hold a connection between the two identifiers from 2 different tables. Each identifier is not unique in this table but the combination is unique.

For example, We have a fact table in which every ticket has a start date (and no end date) and we want to count how many open tickets we have per day.

We don't have a daily data of how many open tickets, we only have the open date.

Solution

Example: We have the following two tickets:

Tickets Table

Id

Start Date

Resolved Date

1

5-15-2016

5-17-2016

2

5-17-2016

Null

Logic:

Step 1 : New field for Resolved Date

Create a new field as temporary Resolved date, that will assign today's date in case the Resolved Date is Null. This field will be populated daily based on today's date. Let's say today's date is 5-18-2016. The updated Tickets table will looks like this :

Tickets Table

Id

Start Date

Resolved Date

New Resolved Date

1

5-15-2016

5-17-2016

5-17-2016

2

5-17-2016

Null

5-18-2016

SQL for the new custom field:

CASE WHEN isnull([Resolved Date])

THEN createdate(getyear(now()),getmonth(now()),getday(now()))

ELSE [Resolved Date] end

Remember to:

Name the field 'New Resolved Date'

Set the field to be of type Date-Time

Set the original Resolved Date field as invisible.

Step 2:Relationship table

Creating a relationship table connecting the Date Dimension and the Tickets tables.

The relationship table will hold a record per each date in between the Start & Resolved dates of each ticket.

Relationship Table

Date

Start Date

New Resolved Date

5-15-2016

5-15-2016

5-17-2016

5-16-2016

5-15-2016

5-17-2016

5-17-2016

5-15-2016

5-17-2016

5-17-2016

5-17-2016

5-18-2016

5-18-2016

5-17-2016

5-18-2016

SQL for the new Relationship table:

SELECT DISTINCT d.Date, t.[Start Date], t.[New Resolved Date]

FROM [Tickets] t INNER JOIN DimDate d

ON d.Date >= t.[Start Date] AND d.Date <=t.[New Resolved Date]

Step 3: Connections between the tables

Chart 1. Connection table Schema

Dashboard Result:

Chart 2. Dashboard : # of tickets per day

Attachments

-22 KB - TicketsPerDay.dash

-429 KB -

When data is imported into a table, and a column doesn't have any values. it is imported as NULL. Within databases, a NULL value signifies that the value is unknown. It does not indicate a value of 0. This article will go over different scenarios to explain how Sisense performs calculations that involve NULL values.

The Scenarios

SUM(NULL)='No Results'

When you SUM values that are NULL, Sisense finds unknown information, rather than 0. This is an important distinction to have, because the sets [1,-1] and [NULL,NULL] should have different results when you aggregate them. 1+(-1)=0. Unknown+Unknown=Unknown.

SUM(NULL) + 1 = ‘No Results’

This one could be interpreted as unknown value + 1=unknown. You could think of this in algebra terms, where SUM(NULL)+1 is equivalent to x+1. There are infinite possible values for that equation, since x is an unknown variable. Unknown+1 is still unknown.

KeyTable.xlsx Analytical Need

In some situations, we will have a transaction / fact table, that is having two or more of its columns referencing to another dimension table, such as order having pre-sale and sales reps involved in it or package that is assembled from several parts.

We will have to link between the tables in order to be able to analyse the sales according to the Employee dimension table.

Image 1. Original Tables

Modeling Challenge

These type of tables cannot be linked by a regular link since a field from 1 table can only be linked to one field in an another table. Whereas in this scenario, we need to link one field to two fields or more. Duplicating the Key field in the dimension table or separating it to additional link tables won't help here, and will result in displaying partial data.

Image 2. Incorrect Solutions

Solution

We will link the tables by using a “key” table that will associate each OrderID with the relevant Employees. For each combination of Order and Employee, we will add “Type” field, that represents the Employee type (Presale or Sales Rep):

Image 3. New Relationship/Key table

Implementation steps:

Create a Custom SQL Expression in the ElastiCube with the following Syntax:

SELECT Distinct

[orders].orderid,

[orders].[presale employee id] AS [Employee ID],

'Presale' AS [Employee Type]

FROM [orders]

UNION

SELECT Distinct

[orders].orderid,

[orders].[sales rep employee id] AS [Employee ID],

'SalesRep' AS [Employee Type]

FROM [orders]

Link the Orders table to the key table by the OrderID field, and the Employees table by Employee ID

Image 4. Result Schema

We will now be able to select an employee from the employees table and query the orders he/she was involved in. We can also filter the employees by the employee type field we added to the key table and analyzes the orders by presale and sales reps.

Attachments

-187 KB - KeyTable.ecdata

-10 KB -

The Accordion plugin might not load for many reasons. The following steps will help you troubleshoot Accordion related issues.

Solution:

IP instead of localhost- Many times users might put in the dashboard URL the IP of their Sisense server instead of localhost. The problem is that the server doesn't know it's external IP, thus the dashboard won't load. The right server address should be either localhost or the server's alias.

Http instead of Https (or vise versa)- Make sure that the dashboard URL is set to https if the user is using SSL and http if not.

Iframes are disabled- As the Accordion plugin opens the sub dashboard as an iframe, if the customer's browser is set to disable iframes, then it won't be able to load. The error received in this case would probably be as follows: refused to display 'URL' in a frame because it set 'X-frame-options' to 'DENY'.

The Accordion contains no data, even when the filters are turned off - This problem in this case is probably that the customer is using background filters. Background filters will work even when turned off, as we don't want viewers to be able to modify these filters. In this case, the solution would be to set the filters which affect the Accordion to all but the background filter.

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, typenet 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 Admin

Access to the most recent data is critical for effective decision making.WithSisense Live Connect, a new capability that was introduced in V. 6.6, you can Bring together historical data from the Elasticube and live data directly from the source in a single dashboard to provide a completeview of yourbusiness.

To have an in-depth understanding of this capability,itsbenefits and limitations andthe setup process, you are welcome to watch the "Connecting to Live Data" webinar (~ 30 minutes).

Additional resources include:

" Connecting to Live Data " webinar in pdf version.

In this post, we show the formula to calculate the shortest distance between two points using Latitude and Longitude.

This calculation can be useful when trying to determine the distance for logistical purposes (ie delivery service, flights, distance between customers, etc).

Example

The file will need to be set up using the following columns Start_place, Start_latitude, Start_longitude, End_place, End_Latitude, End_Longitude. The calculation will need to be done in the ElastiCube using a custom field(s). Replace the Lat_start, Lat_end, Long_start, and Long_end with the relevant fields.

Note:

The radius of Earth in Miles is 3963 (part of the calculation)

The radius of Earth in Kilometers is 6378 (part of the calculation)

The Calculation in Miles:

ACOS(SIN(PI()*[Lat_start]/180.0)*SIN(PI()*[Lat_end]/180.0)+COS(PI()*[Lat_start]/180.0)*COS(PI()*[Lat_end]/180.0)*COS(PI()*[Long_start]/180.0-PI()*[Long_end]/180.0))*3963

The Calculation in Kilometers

ACOS(SIN(PI()*[Lat_start]/180.0)*SIN(PI()*[Lat_end]/180.0)+COS(PI()*[Lat_start]/180.0)*COS(PI()*[Lat_end]/180.0)*COS(PI()*[Long_start]/180.0-PI()*[Long_end]/180.0))*6378

The Business requirements gathering step aims to identify the end-users' key business requirements and KPI's to build customized dashboards that meet their needs. Well-defined requirements are the foundation for a clear, comprehensible dashboard, that enables the end-user to gain business insights.

To help you get started, we've gathered a list of questions that will help you gather the information required for your project from your end-users.

Required Information - Questions List

TIP: Just like a good story, requirements should answer all your important questions. Think abouttheWhat, When, Who, and How:

Define the dashboard’s business need

Who is going to use the dashboards?

What are the dashboard business’ needs? (Summarize/ analyze/ monitor)

When will the dashboard be used? How timely, must the data be?

Describe success and set KPIs

Describe success: what is your desired business result?

What KPIs represents this business result?

Identify cause and effectchain

What behaviors or activities will drive desired business success?

What are the expected intrinsic outcomes?

Build dashboards hierarchy and KPI architecture

What measures representintrinsic outcomes (leading & lagging)?

How would you describe it? Build dash hierarchy

This list should help you define requirements for your dashboard that clearly visualizes your businessobjectives. In addition, we've created a step-by-step process on how to gather the information you need. From planning and getting ready to conduct an informationresearch session. To read further please refer to "How to gather the information?" document attached to this article.

Download Links:

How to gather information.pdf

Business Requirements Gathering.doc

Different Granularity.ecdata Analytical Need

There are many cases where our data comes in with different granularity levels. For instance, Actual vs. Target where the actuals are at the product level and the targets are per sub category (higher hierarchy of the product). How can we connect them?

Modeling Challenge

Logically the connection between two tables are on the same level, but here we need to find a way to make sense of the data while the model needs to support it.

Solution

Example: Sales vs. Targets

This is how the schema looks before our changes:

Image 1. Schema with different Granularity levels. The product only appears in one fact & the subcategory appears in another fact.

We will create a new dim product and it will connect to both fact tables based on the product ID (the current connection of ProductSubCategoryID will be removed).

This will be achieved by creating a master record for each of the sub categories and calling its ID as Product ID.

So in fact the data will hold product sub categories but will be called product ID.So now the products table will contain 2 types of data - Product & sub category in one field (productID).

SQL:

SELECT ProductId, Name, ProductSubCategoryId FROM [Dim_Product] P

union

SELECT -S.ProductSubcategoryID as ProductId, 'N/A' as Name, ProductSubcategoryID FROM [ProductSubcategory] S

SQL explanation:

The query takes the current dim product (first query) and adds new lines to it asmaster records per each of the sub categories.

Ordinarily the subcategories will have a different set of identifiers than the products, but in this case they are both small numbers so we added a minus sign to the subcategories.

The minus sign (-S.ProductSubcategoryID) is used in order to define a new set of identifiers for the subcategories, so it won't create any duplicates with the actual Product IDs - they're in the same column). This needs to be done on the other side of the connection (the fact table).

Image 2. Results from this query. The 1st & 3rd column are the same for the sub categories (except for the minus sign).

Now our schema will look like this:

Image 3. Result Schema

Attachments

-1.2 MB -

Introduction

This forum post will demonstrate how to save your ElastiCube .ecdata filewhich contains your ElastiCube schema and data, and the .dash file of your desired dashboard.

Purpose/Benefits

The .ecdata file contains both the data and the logic structure of the ElastiCube model, the .dash file contains the widgets structure logic and the relation to relevant ElastiCube.

Exporting the .ecdata and .dash files will allow you to easily import the files and run your ElastiCube/Dashboard environment. Please note that security settings and users are stored on MongoDB and will not be stored within these files.

To avoid confusion between .ecdata and .ecube files please see the following post:

Difference between .ecdata and .ecube files

Steps

exporting .ecdata (.sdata for Linux)

Windows:

Step 1 - Open the ElastiCube Manager, 'Servers' and choose 'Manage Servers' to open Sisense Server Console

https://documentation.sisense.com/latest/linux/clicom.htm#gsc.tab=0

Step 2 - Stop the relevant ElastiCube and Choose Export

This will open a window allowing you to choose the location of the .ecdata file

Note: in case your ElastiCube is very large, export can take a while to complete. We recommend stopping your Web server (IIS / IIS Express) to avoid the ElastiCube from automatically starting during this time, as a result of incoming queries.

Linux:

Use Sisense CLI to export the .sdata file:

The commandexport should be used.

Example:

si elelasticubes export -name MyCube -path \"/opt/path/to/NameOfCube\" -file cubeName.sdata

exporting .dash

It will be the same for Windows and Linux.

Open Sisense web, click the more options of the relevant dashboard and choose Export Dashboard

The dashboard will be downloaded to your local drive.

Creating a Sample Set of your ElastiCube

It will be the same for Windows and Linux.

If the .ecdata file is too large to transfer, you might want to create a smaller ElastiCube sample data.

To do so, duplicate the model in order to not to impact your dashboards:

And build the duplicated model with a limited amount of rows:

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?

Log Files

When you clickDownload PDFor 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 clickingSave.

The next step is to clickDownload 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 toAdmin PluginsunderSystem Configurationand toggle the switch to disable each plug-in.

To disable all plugins at once, select each Plug-in and toggle theDisable Selectedswitch:

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, clickEdit Script.

For a specific widget, click on the widget inEditmode.

From the widget’s menu, clickEdit Script.

7.2 and later further troubleshooting

If you are using Sisense installation which is on V7.2 and aboveand 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.2and 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 to

host: "test.dashboards.com"

port: 80

protocol: "http"

After making the changes, save the file, and restart IIS.

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 assistance

Analytical Need

As a dashboard designer I want to present yesterday’s data since the data is built every night.

But in cases where yesterday is not a working day (Saturday / Holiday / etc.), then I would like to present the latest data.

Solution

In order to filter on the latest day that has data you can use the dashboard date filter, please follow the next steps:

Add a date dashboard / Widget filter (Make sure it’s in a day granularity)

Edit the date filter, Click on the 'Advanced' option and paste the following:

{

"last": {

"count": 1,

"offset": 0,

"anchor": "last"

},

"custom": true

}

3. Test the filter - It should present the latest date available.

Introduction

This article provides additional information and guidance about the System Configuration page in Sisense.

Information is up-to-date as of Sisense 8.0. For official documentation about the page, please see this documentation: System Configuration

This article primarily focuses on the configuration page within the Admin tab of Sisense web. There are additional settings stored within the Configuration Manager starting on Sisense 7.2. These settings are described in this article: Configuration Manager

Accessing the Page

The System Configuration page is visible to Sys. Admin and Admin users in the Sisense web application. It can be accessed by clicking Admin in the top bar, then Settings (under System Configuration).

Screenshot of access path:

Account Lockout Thresholds

Close-up of available settings:

Walkthrough

This section walks through each field in System Configuration, with information about each field.

Alias

When you send emails in Sisense (such as new user activation, dashboard reports, Pulse alert etc.), the emails contain links back to your Sisense instance.

Email screenshot with URL highlighted:

The Alias field defines what to use to as the host in this URL. If the field is blank, Sisense will use the private IP address for the server. If you want to use something different (for example, the server's public IP address or domain name), enter it here. Do not include the protocol (http vs. https). The protocol is set in the SSL property below.

Sisense will automatically append the port number if it is something outside of 80 on http or 443 on https. Where the port setting lives depends on which version of Sisense your environment is one.

Sisense Version 7.2 and above:

You must designate the port in the alias if it is not port 80 (no SSL) or port 443 (SSL).

Sisense Version 7.1 and below:

The port number is taken from the C:\Program Files\Sisense\PrismWeb\vnext\config\default.yaml webServer->iisPort property, which is itselfset in the Customize Installation screen on installation. Only include the port number if you need to deliberately use something different (for example, in the load balancer example below) or if you are running Sisense over https on a port besides 443.

If you are unsure of what to use, review the URL you're using to access Sisense outside the server in your web browser's address bar.

For customers using a high-availability configuration with a load balancer, enter the IP address/host name for the load balancer, so that users accessing Sisense from an email are routed through the load balancer rather than straight to the web node that issued the email.

Examples

Assume a Sisense instance bound to port 8081 with the private IP address192.168.0.1, the public IP address 216.58.201.206, and a host name example.com associated to that public IP address.

Settings

Alias: blank

Email URL result: http://192.168.0.1:8081/ app/main#/dashboards/{id}

Settings

Alias: 216.58.201.206

Email URL result: http://216.58.201.206:8081 / app/main#/dashboards/{id}

Settings

Alias: example.com

Email URL result: http://example.com:8081 / app/main#/dashboards/{id}

First Day of Week

This property defines the weekday used to start the week in week-level queries.

By default, the first day of the week is Monday when querying anElastiCube.Sisensedefines Monday as the first day according to ISO 8601. This means that when selecting filters such as Week 1, Week 2, etc., the first day of the week is not that defined by first day of that year, but rather the first Monday of the year. Each week starts on a Monday and ends on a Sunday for 52-53 full weeks in a year.

More information about Sisense's method of calculating weeks is available in the official documentation. Valid values are Monday-Sunday.

This property may be set via the REST API on the api/v1/settings/system endpoint. Take care to POST only valid values; other values (such as 'string') will cause week-level web queries to fail.

Default Language

This defines the language used in the web application by default. When creating users, administrators can optionally set the default language per user. The default language selected here will be used in the absence of user-level language preferences. For more information about Sisense's support for language localization, please see Internationalization and Localization.

Send Emails

This is a toggle for turning off and on emails across the entire environment. Emails are sent by default in scenarios such as when a new user is added, a user is shared with a dashboard, or a Pulse alert is fired. For a complete list of emails, see here: Rebranding Sisense Automated Emails

Toggling this property off suppresses these emails from Sisense entirely. For guidance on disabling only select emails, please see How To Disable Emails From Sisense.

Pulse

Toggle to remove thePulsepage from the top menu bar and the Pulse Notification Feed. This option hides thePulsepage from all users in your system and stops sending Pulse notifications.

Dashboard Administration

Toggle to enable/disable the dashboard administration feature. Administrators and the Sys Admin have access to the "All Dashboards" button which allows them to search for and access any dashboard in the environment.

Turn this feature off if you do not want Administrators to have access to all of the dashboards of every user in the system in theAnalyticspage.

Widget Narratives

Some Sisense customers use a feature that provides written-language descriptions of widgets' data. This property determines whether the feature is on and what token to use for it. More information about this feature is here: Sisense Narratives. Please note this feature needs to be enabled in the customer's license by the Customer Success Manager.

Field Suggestions

Sisense uses technology to provide smart suggestions to users when they are selecting ElastiCube fields to use in their dashboard widgets. This property allows users to turn on or off this feature.

Screenshot:

Exploration Paths

Toggle to enable Viewers to receive exploration widgets in the dashboard.Sisense’s AI (Artificial Intelligence) Exploration Paths automatically generate visualizations and insights that anticipate your Viewers’ questions without requiring a Dashboard Designer to create more widgets.

This feature is constantly improving. Refer to this documentation on how to set up and use it: Leverage AI to Uncover Hidden Insights.

Session Management

Sisense supports user session management via either of two methods: Cookies or Session Inactivity.

Cookies: Default setting. Allows you to define how much time must pass before a user’s session is ended according to the user’s cookie. Expiration is in Days

Session Inactivity:Allows you to define how many minutes of inactivity must pass before a user or an Administrator is logged out. Expiration is in minutes

Note: If you make changes to these settings, after saving users may have their sessions revoked.

For more information on set up, refer to the set up guide: ManageUser Sessions

Embedded Domain White List

You can define which domains can embed your dashboards into iFrames on their site.If you do not add any domains, then your dashboards can be embedded into any site.

Please refer to this article for more information on how this setting works in combination with other security settings: Optional Security Hardening for Sisense Web Pages

This page had a number of enhancements and fixes within the 7.4 release. Please refer to the Release Notes page for more information.

Account Lockout

By popular demand, the ability to enforce account lockout after a number of failed logins was added. You can define the following settings for account lockout:

Toggle On/Off

Number of failed login attempts before lockout

Lockout duration (minutes)

Refer to the following article for additional settings and information:

When pulling together data from multiple sources, you’ll have a number of different dates. For instance, the marketing department has a Campaign Date, Sales department may have an Opportunity Date and Finance has a GL Date.

Modeling Challenge

This type of data leaves us with three sets of dates. Modeling it properly will allow you to select from one common date field while still leaving you the ability to choose from one of the three date fields individually.

Solution

We need to come up with a Custom SQL Expression that retrieves a unique list of the dates used between the three tables.

We create a Custom SQL Expression with the following Syntax:

Select Distinct x.[Common Date] from (

Select [GL Date] AS [Common Date]

FROM [GL Entries]

UnionAll

Select [Marketing Campaign Date] AS [Common Date]

FROM [Marketing Campaigns]

UnionAll

Select [Opportunity Date] AS [Common Date]

FROM [Sales Opportunities]

) as x

Lastly, we link the four tables together:

Results

This gives us the common date field to use. This will allow us, for example, to choose a month and it will narrow down the selections across the three tables.

OFFICIALLY SUPPORTED ADD-ONS

*denotes Premium Add-On

Plugin Documentation

Description

Community Forum

Accordion

Useful for providing large amounts of information within a limited space.

Link

Advanced Formula *

Create descriptive, dynamic values within your pivot usingCASE/IF functions.

N/A

Aggregated Table

Extends the functionality of the Table widget by supporting aggregation across the whole table.

Link

Analyze with Insight Miner

Uncover hidden insights with the power of machine learning in a click.

N/A

ArcGIS Map Integration *

Creates a new widget type that displays configurable and filterable ArcGIS web maps

N/A

BloX

Create custom business applications and widgets inside your dashboard.

Link

Comments

Enables you to add a comment box within the Sisense dashboard using the iFrame widget.

N/A

Dynamic Buckets

Aggregate a column chart's results into buckets.

Link

Dynamic Elasticubes

Redirect a dashboard to different Elasticubes on the fly.

Link

Embed Images

Insert any image into a table or pivot cell.

Link

Filtered Measure

Adds the ability tocreate additional selection states for filters.

Link

Forecasting

Displays linear, exponential, and logarithmic trend lines in your dashboard charts.

N/A

Funnel Widget

Visualize stages in a process.

Link

Gantt Chart *

Visualize your tasks and resources against a timeline.

N/A

Histogram Widget

Adds a histogram widget type to show theunderlying frequency distribution (shape) of a set of continuous data.

Link

Interactive Pivot *

Provides the option to enable/disable rows, columns, and values on the dashboard level from a cell's menu.

N/A

Jump to Dashboard

Creates shortcuts from a widget in one dashboard to another widget in another dashboard via a new browser tab or a popup window.

Link

K-Means Clustering

Easily create cluster visualizations.

N/A

Limit Autocomplete

Limit dashboard sharing autocomplete to a user group.

Link

Monitor Sensitive Data *

Maintain a log of who is viewing sensitive data.

N/A

Print Widget

Add a "Print" action to the widget menu so that you can print any widget in your dashboard.

Link

Report Manager *

Send custom reports on a pre-defined schedule.

N/A

Sticky Filters *

Configure filters to remain active as you navigate from dashboard to dashboard.

N/A

Switchable Dimensions

Toggle between any dimension displayed in the widget.

Link

Tabber

Allows you to switch between views easily and quickly without changing the dashboard.

Link

Trellis Chart Widget *

Uses the break-by dimension to split each dimension value into its own chart.

N/A

Visualize Queries (JAQLine)

Visualize JAQLine widget queries.

Link

COMMUNITY PLUGINS BY Functionality OR Visualization

Disclaimer

Plugin Forum Page

(newest on top)

Description

Column Switcher Plugin

Switch between columns, instead of rows, for calculation.

Limited Data Return Widget

Can easily limit the data for a specific Elasticube\Table\Column.

Dynamic Dimension/Value Chart

Allows dashboard designers to predefine multiple dimension and/or values providing viewers the ability to switch dimensions/values on-the-fly.

Variable Radius Pie chart

Circular chart divided into Sectors (categories) which are proportional to the quantity or volume they represent.

Activity Gauage

Demonstrates multiple stacked values and provides at a glance comparisons of multiple indicators.

Hello World Widget Example

Download

Sparklines in Pivots

Download

Financial Functions

Download

Toggle Plugins

Download

Route Map Widget

Download

Replace Icons

Download

Calculation from Previous Value

Download

MapboxGL Widget

Download

Color HeatMap

Download

Seconds Formatting

Download

Additional header buttons

Download

Hide Export Options

Download

Elasticube Build Button

Download

Giphy Widget

Download

Improved (Enhanced) Color Manager Plugin

Download

(Google Drive)

Trellis Widget

Download

Funnel Conversion Widget

Download

(Google Drive)

Export Tables To CSV Post Plugin

Download

Filter Buttons

Download

Sparkline

Download

Tracking Sisense Usage Via Google Analytics

Download

Open Ended Survey Analysis

Download

Sorting Filter Entities Using the Remove Digits Prefix From Filter

Download

Data Write-Back/Closed Loop BI

DBWriteback / JavaRestEndPointWebApp

Second Value to Cartesian Charts

Download

Pareto Chart

Download

Execute SQL Command

Download

Indicator with dates, Time And Duration

Download

Limit Filter

Download

MultipleFact Tables - Advanced Filtering

Download

Dynamic Filter Generation

Download

Auto Add Filter Based on User

Download

Parallel Coordinates Plugin

Download

Scatter Plot Matrix

Download

Managed Dashboards Panel

Download

D3 Force Directed Network Diagram

Download

Histogram

Download

Drilling and Filters Interaction

Download

ZoomableSunburst

Download

Cumulative Running Sum

Download

Send Emails From Widget Titles

Download

Correctly Order Legend by Weekday

Download

Tile Widget

Download

3D Pie Chart Plugin (Donut3D)

Download

Add Financial Formatting to Pivots/Indicators

Download

Trend Lines (Menu Based)

Download

Transpose A Pivot Table Plugin

Download

Quadrants Chart

Download

Data Dictionary

Download

Logging Users Out Of Sisense From Your Site

Download

Disable 100\% Zoom Warning

Download

Multi Level Sankey Diagram

Download

Bookmarks

Download ( v2 beta )

Set Default Dashboard Per User Group

Sisense v6 Download

Sisense v7 Download

Customize Value Labels

Download

Input Parameters (version 2)

Download

Grand/Subtotals for specific values in Pivot Widget

Download

Add Widget Script Indicator

Download

Auto-Zoom Scatter Map

Download

Add Checkboxes to Pivot Table

Download

Center Zero Intersect on Bar Charts

Download

Show value labels as the variance

Download

Adding a Clickthrough Link to Logo

Download

Custom Filters

Download

Filter chart by underlying members of an aggregate

Download

Clone Field

Download

The Measure Changer Widget

Download

Copying Data from a Pivot Table Widget to Excel

Download

Floating input text parameter with independent numeric values

Download

Additional Data Chart Tooltip Plugin

Download

Shared Tooltip for Scatter Chart

Download

Filter Header

Download

Filter Measure

Download

'Percent Rank' functionality for chart widgets

Download

Filter drill down fields per widget

Download

Gantt Chart

Download

D3 Tree Ring Chart

Download

Waterfall Chart (Highcharts)

Download

iFrame Widget

Download

Show More / Less Filters

Download

The Smart Label

Download

Custom currency symbols

Download

Google Maps heatmap - Custom widget

Download

Hide Filters

Download

Filter Members Limitation

Download

Visualization

3D Scatter Plot

Download

Collapsible Tree Node with Zoom&Drag

Download

Images in Bar Chart

Download

Indicators - Setting Colors, Icons, And Progress Bar

Download

Overlapping Column Chart

Download

Distribute Equally The Widgets In A Dashboard Row

Download

Dynamic Color Palettes

Download

Custom Style

Download

Conditionally Format Pivot Foreground

Download

Time Series Chart - Visualizing Hrs/Min/Sec

Download

Animated Liquid Fill Gauge

Download

Color Manager

Download

Widget Title Style

Download

Highlight Selected Slice in A Pie Chart

Download

Remove Expand Button

Download

Changing Scatter Chart Point Marker Shape

Download

Bullet Chart

Download

Chart Label Order Changer

Download

Hide Widget Titles

Download

Zoomable Line Chart (Highcharts)

Download

Custom Map Widget

Download

D3 Word Cloud

Download

Add Image Indicator to a Pivot Table

Download

Adding Logo/Image to a dashboard

Download

Indicator Theme

Download

Regression Line

Download

Advanced Custom Filter

Download

Custom UI Colors

Download

Custom UI Colors for V7.2

Download

Disclaimer

Sisense has designed the Plugins Forum to help us enhance our community and assist users in understanding what types of configurations and extendabilities can be accomplished in the Sisense software’s framework. Our engaged community is constantly posting new and relevant content to help our users achieve exciting new configurations. With this in mind, please note that not all of the configurations or extendabilities in these posts have been fully tested by Sisense, and we cannot guarantee they will work seamlessly in every scenario and with every Sisense software version. We do our best to maintain and moderate these posts, but we strongly advise you to do some testing in your own environment to make sure that these extendabilities function properly in production. Further, configurations and extendabilities reflected in this forum are outside the Sisense product development environment and, therefore, are not covered by Support Services.

Star Schema.ecdata Analytical Need

We have one central source for our measures and multiple sources to slice & dice these measures.

Modeling Challenge

How do we build a schema that will enable us to perform our needed analysis?

We will need to connect all of our tables so every criteria will be able to filter the population for the measures

Solution

We will build a Star Schema. This schema contains one fact table and multiple dimensions that are connected to it. This is the most recommended model performance wise (when possible) and the simplest one to understand (Except for a schema with one table).

The elasticube schema will look like this:

Image 1. Typical Star Schema

Attachments

-2.7 MB-

Sometimes we'd like to define variables to hold specific metrics and use them throughout the dashboard in several places, with the ability to change / control them in one repository without the need to manually change each formula or widget.

A good use for variables can be setting global targets or setting Min / Max limits for the gauge widget.

For example, we want to set a target of 10\% net revenue (which may change in the future),and we’d like to check for each country the gap from that target.

Variables.ecdata

In addition, we want to set the gauge widget barriers using variables in the Min / Max definition. This barriers may change and we don't want to change it manually for each and every gauge.

Steps

The trick of holding the variables is by using an excel sheet which contains the variables in the columns. In order to be able to use this excel we must connect it fictively to the transactions table/s that contain/s the calculated values (to which we want to compare the variables).

Step 1 - Create variables excel

Create an excel sheet containing ‘Link’ column and the required variables columns. The first column, ‘Link’ will hold the value: 1. The rest of the columns will hold the values of the desired variables.

Import this excel file into the Elasticube you're working with.

Step 2 - Create link fields in the fact tables

Create “Link” fields in the transactions table/s, if its a regular table use a custom field and define 1 as its value:

if its a custom SQL expression table, add “1 as link” to the final extraction of the fields:

SELECT*, 1 AS Link FROM [Order Details]

Define the “Link” field to be Int type .

Step 3 - Creating a relationship between the tables

Create a relation between the tables and rebuild with schema changes only.

Step 4 - Using the variables

After building the Elasticube successfully, we now can use these variables with the fact table it is linked to.

we can call a variable using the max() / min() function.

In a regular formula:

In Min / Max barriers definition:

Note:

Do not use the sum() function it will bring the value according to the relationships it has with the transaction table - we just need to bring it once.

Download:

Variables.dash

Vars.xlsx

Analytical Need

With a Parent Child hierarchy that has 3 levels, there must be a series of dependent filters for all levels individually but, affects the other levels below it too.

Business defined hierarchy

Level I - Line of Business (LOB) { CL11, CL3, CL9 }

Level II - Sub LOB { CL1, CL8, CL10, CL12, CL6, CL7 }

Level III Platform { CL2, CL5 }

We need to have 1 dashboard filter for each level, so a total of 3 filters (dependent).

Example:

If you select “CL12” from Level II (sub LOB) -> it filters transactions of classes CL12, CL2 and CL5

If you select “CL9” from Level I (LOB) -> it filters transactions of classes CL9, CL10, CL12, CL2, CL5, CL6 and CL7

Modeling Challenge

Flattening the Parent Child Hierarchy in the Elasticube, keeping in mind to create & effect filters on the three levels, hierarchically.

In the Dimension table, the Class ID is the column that contains all the Levels of the hierarchy in the same column and each Class ID has a Parent ID next associated to it. (If there is no Parent ID, then it means that is Level I topmost level). This Class ID is the key for this dimension table.

In the Fact table, there is only one column for the Class ID which has members from all 3 levels and which will connect with the Fact Table.

Solution

Step I Create a copy of the Dim table Backup Table

We need to create a copy of the Dim table with only Class ID and Parent ID pair for all classes For Look-Up purposes (Please note that, we could use the same Dim table for looking up. But, for better understanding of this use case, I have decided to duplicate the Dim table for look-up purposes)

Step II Identifying Hierarchy levels in Dim Class

For each Class ID in the DIM table, we need to identify which levels it belongs to LOB, sub LOB or Platform. So, to do that, using Class ID and Parent ID, let’s do a quick background check to find the Grand Parent ID! Using Look-Up function, look what is the Parent ID in the Backup table, of the Parent ID in the Dim table.

Create a Level Flag to identify which level each Class ID belongs to. The logic to create the flags is:

Parent ID null and GrandParent ID null: Level 1/LOB

Parent ID not null and GrandParent ID null: Level 2/sub LOB

Parent ID not null and GrandParent ID not null: Level 3/Platform

Step III Create custom columns for filtering

We need to assign each Class ID with LOB, Sub LOB and Platform it is associated with.

Example:

let us consider CL 10 & CL 5.

Since CL 10 is a Sub LOB, LOB : CL 9 ; Sub LOB : CL 10 ; Platform : null

Since CL 5 is a Platform, LOB : CL 9 ; Sub LOB : CL 12 ; Platform : CL 5

This assignment is based on the Level Flag of the Class ID:

If the flag ID is:

LOB LOB = Class ID | Sub LOB = null | Platform = null

Sub LOB LOB = Parent ID | Sub LOB = Class ID | Platform = null

Platform LOB = GrandParent ID | Sub LOB = Parent ID | Platform = Class ID

As a part of the last step, join the Class Dimension table and the Fact table using the Class ID as the key and perform a build.

Creating Filters

Create three dependent filters LOB, sub-LOB and Platforms using the dedicated columns created for each of them.

At times, we are using flat files as a source to rely on for our data analysis.

As flat files can be generated by another program or manually, it is often hard to control and expect the data structure these files hold.

On our scenario, every month we are getting atxt file name 'User Logins <MonthNumber>m.txt':

This file contains the user logins to our application with the userId and an aggregation of total logins for that month:

On our dashboard, we are interested todo an analysisof the total logins per month and compare the different month files.

Option 1: Add each fileto the ElastiCube and use a custom SQL expression to UNION all the files into one table. For example:

Select Id, Logins, 'March' as Month from [User logins 3m.txt]

Union All

Select Id, Logins, 'Other Month' from [User logins Xm.txt]

Option 2:Prepare the data using python prior to bringing it to the ElastiCube - Saves disk size and build time:

The following python script uses the file name to extract the month (3) and add it to the end of every line in the file itself:

import os

for filename in os.listdir(os.getcwd()):

#if the filename ends with m.txt

if filename[-5:] == 'm.txt':

#open the file

with open(filename) as fp:

lines = fp.read().splitlines()

with open(filename, "w") as fp:

for line in lines:

#add the month's number as another column in the file to each line

print(line + " " +str(int(filename[-8:][:-5]) \% 12), file=fp)

#print the file was modified.

print(filename + ' was modified')

#change the file name to reduce mistakes of re-using the file.

os.rename(filename, filename[:-5] + '.txt')

fp.close()

At the end of the process of altering the file lines with adding the month number to them, we are changing the file name to include '3.txt' instead of '3m.txt' to make sure that we do not alter the files anymore using the script.

Result:

That's it!

Need

A common requirement for self service BI is to enable the viewers to save their own report. This can be achievable in Sisense if you are a designer of a dashboard.

However, when you are a viewer, you cannot even re-order the pivot columns as you please.

Challenge

The default behaviour of the viewer is only to interact with the filters and view the numbers. No layout changes are allowed for a viewer.

Solution

We can change the default behaviour of the viewer and allow him to re-order the rows, values & columns.

Using the API of custom roles we can achieve that.

Solution:

Go to Admin ==> REST API ==> 0.9

PUT /roles/{idOrName}/manifest/{path} API

Fill in the following parameters:

idOrName: consumer

path: /widgets.items

manifest:

{

"reorder": true

}

Execute the API and that's it!

Note: the re-order option is enabled for all widget types (not just pivots), however, pivots are the main use case for this need.

The following Python script allows exporting dashboard data to CSV files.

The script will export each widget to a separate CSV file.

This new version of the script uses Sisense's REST API an eliminates the direct connection to the mongodb.

Important: Starting with Sisense V8.0.1The GET /dashboards/{id} endpoint returns only the Dashboard Owner ID and no other information about dashboard owners when called by non-Admin users (see Sisense Release Notes.)

Therefore the user used to run this scrip must have admin rights for the script to work properly.

Download

exportDashboardToCSV_01272020.zip - contains the script executable and configuration file and does not require Python installation on the server.

Release Notes.txt - updated 1/27/2020.

Settings

The script uses a settings.yaml file. You should configure the following settings:

email

Admin email address.

If you're using the api token to authenticate leave this value blank.

password

Admin password.

If you're using the api token to authenticate leave this value blank.

api_token

The APItoken can be use instead of the password.

To retrieve you API token use the

POST /authentication/loginREST API command.

See here form more info.

Note: The admin email and password are used for retrieving the API token.

The token is required for running API command.

See the REST API documentation for more information.

file_logLevel

Log level for the log file. Available options:

DEBUG

INFO

WARNING

ERROR

CRITICAL

console_logLevel

Log level for the console. Available options:

DEBUG

INFO

WARNING

ERROR

CRITICAL

logFileName

Path to the script log file.

protocol

Protocol for accessing Sisense http or https.

Default: http

verify

Set verify to False toignore verifying the SSL certificate.

Default: true

port

Sisense port.

Default 8081

host

Sisense host.

Default: localhost

csvFilePath

CSV files output directory

csvFileNameConvention

Naming convention for the generated CSV files.

Options are:

WIDGET_NAME (e.g. Totalcsv)

WIDGET_AND_DASHBOARD_NAME (e.g. TotalRevenue_SalesDashboard.csv)

WIDGET_AND_DASHBOARD_ID (e.g.5783b9eb25ddaf78420000a6_56f1aa1dbc4b62043a000028.csv)

Usage

exportDashboardToCSV.exe [-h] [-dashboards [DASHBOARDS ...]]

[-widgets [WIDGETS ...]]

optional arguments:

-h, --help show this help message and exit

-dashboards DASHBOARDS [DASHBOARDS ...] List of dashboard IDs to export separated by space.

-widgets WIDGETS [WIDGETS ...]List of widget IDs to export separated by space.

Each widget ID should be in the format of <dashboard id>.<widget id>

Output

The script will create an output directory 'csv' in the scripts directory.

Each widget in the dashboard will be exported to a separate CSV file named as:dashboardID_widgetID.csv

Examples

Export an entire dashboard:

exportDashboardToCSV.exe -dashboards 5e2a1bccbc60327e98fb4635

Export multiple dashboards:

exportDashboardToCSV.exe -dashboards 5e30922fa9ab7536081a6ee9 5e3087a78d9e7f0e3cb3e6f8

Export individual widget:

exportDashboardToCSV.exe -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9

Export multiple widgets:

exportDashboardToCSV.exe -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f95e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9

Export a combination of dashboards and widgets:

exportDashboardToCSV.exe -dashboards 5e30922fa9ab7536081a6ee9 5e3087a78d9e7f0e3cb3e6f8 -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9

link Analytical Need

Aggregation functions are used to group a set of values to perform calculations that return a single value. SUM(), MEDIAN(), and COUNT() functions are some of the most common aggregation functions examples. Simple aggregation functions require only a numeric field to perform the aggregation upon: Function (<Numeric Field>).

Multi-pass is a type of an aggregation function which performs an additional calculation over the data and group it by a specific field: Function (<Group By>, <Aggregation>). More information about using multi-passcan be found on this .

Certain business needs, such as performing conditional calculation by group in a pivot tablewhile keeping and allowing flexibility in the front end might require more advanced calculations. Therefore, we would like to do the calculation on the dashboard levelrather than in the ElastiCube.

Modeling Challenge

How do we perform grouping in the dashboard while keeping the ElastiCubeflexibility

Let's assume that a Sales Manager would like to answer the following business question:

Restaurantsare sorted into 3 categorizesby number of customer visits;0-100 customers, 100-200 customer or 200+ customers. What is the total # of customervisits each category of restaurantreceives?

Solution

First, it is crucial to break down the question into components:

Count # total customers.

Group by restaurants.

Filter by groups.

As there are several filters over the same value, instead of creating a filter on a widget level, we will create a filter over the measured value for each group.

Let's start by grouping our values and creating a new predefined value for each group. In our example, Group 1:0-100 customers, Group 2:100-200 customers, and so on. Then we will use multi-pass aggregation to calculate the specific group by the relevant filter.

Let's break it down to understand how to create the correct formula that fits for this scenario:

1) To answer the need, we will use a CASE WHEN statement to create a range of # customerID to be between 0-100 customers which we would like to eventually sum. We would like to start with examining the filter first, to make sure we are filtering out the correct data. To do so, we will add a new value and create a formula by pressing on 'fx'. We will add the following formula:

Essentially, return the # customers, if the # customers is in the predefined range, else return 0.

To verify the correctness of the filter, let us verify the data:

We can see that when the total # of customers of a restaurant are within our filter's range, the total is added to the 'Formula' column and when it isn't in range, the column will get the value of 0.

2) The next step will be to add the multi-pass function in order to group the results by Restaurant and sum all results. This will sum all the values already filtered for all restaurants, including ones that received a value of 0 from the initial filter. This will lead to the correct result which is summing up only the # customers for 0-100 restaurants. Here's the final query:

Here's the pivot now:

As the grouping duplicates the data for each row, we would remove the RestaurantID row, and get the final result of the data:

3) Apply the same logic with only changing the range for the rest of the group in a different measured value.

Summary: The CASE WHEN statement performs the filter into group and for every RestaurantID that its total amount of unique CustomerID is between 0-100, we return the # of unique customers. If the total amount is not in range, we count nothing.

This is a collaborative document listing build errors and respective possible solutions as shared by Sisense community members.

Please note that this is not an exhaustive list of all possible solutions and in general there are many possible ways to solve a build error. Therefore, we recommend using this list as a tool to independently learn possible causes of and try different solutions to errors as shared by other users.This list will be continuously updated with new entries to help you troubleshoot build errors on your own. If you can't find a solution to your error please either ask for help in the community or contact our support team directly.

You can help us grow this list by submitting new community posts with build errors you've encountered. For more information, please visit the Build Errors and Solutions post in the community.

Error

Build Stage

Approach

"Index is out of range"

Importing Data

MongoDB Build Failures Troubleshooting

"Memory allocation has failed..."

Importing Data

MongoDB Build Failures Troubleshooting

"OutOfMemoryException"

Importing Data

Out Of Memory Error During Build

"object reference not set to an instance of an object"

Importing Data

Elasticube Build Failure: Object Reference Not Set To An Instance Of An Object

"Import failed. cannot open database "northwind" requested by the login. The login failed.Login failed for user 'NT AUTHORITY\SYSTEM'"

Importing Data

Sisense's Build Failed When Importing Data

"web request to 'https://accounts.google.com/0/oauth2/token' failed.

Importing Data

Google Oauth2/Token Failed

"Cannot open database "NORTHWIND" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM"

Importing Data

Build Error: Elasticube Build Fail

"UNSUPPORTED_CLIENT: TLS 1.0 has been disabled in this organizaiton. Please use TLS 1.1 or higher when connecting to Salesforce using https."

Importing Data

Direct link to the troubleshooting comment

"Import failed - Specified Cast is not valid"

Importing Data

Direct link to the troubleshooting comment

There was no endpoint listening at net.pipe://localhost/CloudManagementService that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details."

Importing Data

Direct link to the troubleshooting comment

"Exception in DirectCommand.ExecuteReader(): DirectCommand.ExecuteReader failed: Query number 0: MALException:algebra.join:GDK reported error.

ERROR: bunfastins: bat too large"

Importing Data

Direct link to the troubleshooting comment

"Import Failed ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."

Importing Data

Direct link to the troubleshooting comment

"Establishing Connection: Host = Quickbooks Desktop; Database = Default; User = sisense' Password = ****; URL = http://example.com ; < 1 sec"

Importing Data

Build Error: Establishing Connection

"Timeout. Session ... - could not receive data in 1800 seconds."

Importing Data

Can't Able To Extract The Data With Where And Inner Joins Using Custom Query In Sisense EC

"Elasticube xxxx is already being built."

Initializing

Error in Build Elasticube

"Accumulative Changes Log Creation Failed Error Create Patch[The requested operation could not be completed due to a file system limitation ]"

Initializing

Direct link to the troubleshooting comment

Exception in DirectCommand.ExecuteReader(): DirectCommand.ExecuteReader failed: Query number 0: SQLException:int:conversion of string '...' failed

Custom Calculation

Int:Conversion Of String Build Failure Error

"The ElastiCube failed to complete query request (Connection lost)"

Custom Calculation

Direct link to the troubleshooting comment

"Exception in DirectCommand.ExecuteReader(): DirectCommand.ExecuteReader failed: Query number 0: MALException:algebra.join:GDK reported error.

ERROR: bunfastins: bat too large"

Custom Calculation

Direct link to the troubleshooting comment

"ElastiCube <Name> is having difficulties closing."

Finalizing

Direct link to the troubleshooting comment

Introduction

Sisense allows importing widgets and filters as components to be presented in different websites. This is done by SisenseJS. Sometimes, it is required to modify the widgets filters based on website attributes such as logged in user, location, previous user selections etc.

Using the below scripts, you will be able to modify the dashboard filters and save them to the server so datawill be saved even when refreshing the page.

Code Snippet:

1. Modifyingdashboard filters:

// Find the filter we want to change based on the dim

var filterToChange = dash.filters.$$filters.find(item => item.jaql.dim == "[Commerce.Age Range]");

if (filterToChange && filterToChange.$$model.jaql.filter){

var members = filterToChange.$$model.jaql.filter.members;

// Check if we already have the member selected

if (members && !members.includes("65+")) {

// Push a new member to the filter

members.push("65+");

// Save the dashboard

dash.$$model.$dashboard.updateDashboard(dash.$$model,"filters");

}

}

2. Save filters changes to the server is done by sending "true" as the second parameter to the "connect" function:

Sisense.connect('http://10.20.4.155:8081',true).then(function(app) {

.

.

.

});

3. Create and add new filter to the dashboard:

var applyFilter = function (dim, value, activeDashboard) {

// Get the active dashboard, only run if sisense is loaded

if (activeDashboard) {

// Create the filter options

var filterOptions = {

save: true,

refresh: true,

unionIfSameDimensionAndSameType: false

};

// Create the jaql for the filter

var jaql = {

'datatype': 'text',

'dim': dim,

'filter': {

'multiSelection': true,

'members': value,

'explicit': true

},

"title": dim

};

// Create the filter jaql

var applyJaql = {

jaql: jaql

};

// Set the new filter

activeDashboard.$$model.filters.update(applyJaql, filterOptions);

}

};

Download:

ModifyFiltersSisenseJS.html

Download Column Switcher Plugin

INTRODUCTION

The Column Switcher Plugin (CWP) allows a user to use a dashboard filter to switch between columns, instead of rows, for calculation.

Challenge

A designer of a dashboard may want to calculate the revenue for a product over time for an international audience. In common convention, the fact table of this scenario may have the transaction amounts in different columns for different currencies. The designer wants the end-user to toggle between different currencies.

Traditionally, to achieve this, aggregation from a dimension table with daily conversion rates is required. This involves complex modeling and formula building.

ProductID

Date

USD_Revenue

CAD_Revenue

1

1/21/2020

10

12.30

1

1/22/2020

10

12.60

1

1/23/2020

10

12.10

Solution

With the CWP enabled, you can apply a dashboard-level filter that allows you to select which column will be used in a calculation. With this approach, modeling is not required. This solution involves using a simple aliased formula with a specific naming convention and a detached custom SQL table used for the dashboard filter.

SETUP

Download and place the plugin folder into the Sisense plugin folder.

Program files > Sisense > app > plugins

1. Elasticube Setup

Setup is based on this fact table example

ProductID

Date

USD_Revenue

CAD_Revenue

1

1/21/2020

10

12.30

1

1/22/2020

10

12.60

1

1/23/2020

10

12.10

Create a custom SQL table and name it ‘columnToUse’. The table does not need to be joined to any other table.

[If you want to name the table something else, update the column_select_table variable in the javascript file]

Create a record for each column you want to switch between using. The values should reflect the column names.

columnToUse Table

<ANY COLUMN NAME>

USD_Revenue

CAD_Revenue

SELECT 'USD_Revenue'

UNION ALL

SELECT 'CAD_Revenue'

2. Dashboard Setup

Writing the Aliased Function

Write a function that adds the column’s aggregations you want to swap between.

They don’t need to be the same aggregation type

[Total USD_Revenue] + [Total CAD_Revenue]

Save the function as an Aliased function using the function editor’s star icon. Prefix the function with ‘swap_’

[you can configure what the prefix should be in the javascript file by editing the variable special_alias_prefix]

Use this aliased function where you want to swap the columns

On the dashboard apply a filter from the columnToUse with single selection.

USAGE AND LIMITATIONS

Usage with other values

[swap_currencySwap] / COUNT([product_id])

If the aliased function is used in conjunction with other values then no further setup is required. Simply add the aliased function and write the formula.

Usage independently

[swap_currencySwap]

If the aliased formula is the only element of the formula then an extra step must be taken to avoid Sisense flattening the formula and removing the aliased function format.

Sisense automatically flattens aliased functions when they are used independently, so you have to options to avoid this unwanted behavior:

Add a 0 (e.g. [swap_currencies] + 0 ) so that it is not flattened.

Otherwise, rename the function and add a dummy suffix. This will also prevent it from being flattened.

Current Version (1.0) Limitations

The plugin can only select one column from each table

This means you cannot select CAD_costs and CAD_revenue for comparison if they are in the same table using the plugin. For this use case, another fact table is required.

This is a Community Plugin and is not officially supported by customer support.

For inquiries about having this plugin supported, please reach out to your dedicated CSM and [email protected]

PLUGIN CODE WITH COMMENTS

const column_select_table = 'columnToUse';

const special_alias_prefix = 'swap_';

prism.on('dashboardloaded', function (args) {

// when a widget queries execute the code

args.targetScope.dashboard.on('widgetbuildquery', (a, b) => {

// find the filter element who's base table matches the column_select_table and get the filter member that is selected. In V1 it is assumed the user set the selection to single select.

let filterColumnSelector = args.targetScope.dashboard.filters.$$items.filter(i => i.jaql.table === column_select_table)[0].jaql.filter.members[0];

// in the widget, run this code on every mesaure

b.query.metadata.filter(val => val.jaql.type == 'measure').forEach(i => {

// get the function context for the function.

let queryContext = i.jaql.context;

// go through every context element of the query

for (let [k, v] of Object.entries(queryContext)) {

// if an element has a title it is either renamed or an aliased function. Find a function that has the special alias prefix

if (v.title.includes(special_alias_prefix)) {

// in the matched function, it has a context element for each of its aggregations.

for (let [k2, v2] of Object.entries(v.context)) {

// Find the function who's column comes from the name present in the filter.

if (v2.column == filterColumnSelector) {

// set the entire aliased function formula to equal the single aggregation.

v.formula = k2;

// console.log(i.jaql, 'updated successfully');

}

}

}

}

})

})

})

SYMPTOMS

When attempting to install Sisense the validation step will fail with a license validation error:

DIAGNOSIS

The user can sussefully login to activate the license at the begining of the installation process.

After clicking 'Skip', the user can susseccfully login to Sisense Activation.

Restarting the Sisense.Oxygen service does not resolve the issue.

The server is not behind a firewall, or disableing the firewall does not resolve the issue.

The following error shows in the iisnode log file:

<Exception handler="OxygenLicensing.GetLicense()" type="WebException" msg="Unable to connect to the remote server" thread="(6)" date="08-01-2016" time="16:15:17:574">

<StackTrace> at Prism.Runtime.Base.Oxygen.OxygenLicensing.GetLicense()</StackTrace>

<InnerException>

<Exception handler="" type="SocketException" msg="No connection could be made because the target machine actively refused it 10.1.11.105:31112">

<StackTrace> at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)

at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)</StackTrace>

</Exception>

</InnerException>

</Exception>

When opening the ElastiCube Manger you get the following error:

When you run the following command (as admin):

C:\>netsh http sh iplisten

The output contains a list of IPs. For example:

IP addresses present in the IP listen list:

-------------------------------------------

127.0.0.1

100.76.72.126

Cause

The machine you are looking at isconfigured to listen only on an explicit set of IP addresses. This configuration would override thedefaultconfiguration to listen to all IP addresses.

Resolution

There are two approaches to fixing this issue:

Add a listener for the refused IP address. To do so follow these steps:

Run the command:

netsh http add iplisten ipaddress=<IP address to add>

Restart IIS by running:

iisreset

Another approach is toremove the definedlisteners so there were no explicit IP addresses configured, which would mean it would listen on all IP addresses. To do so follow these steps:

Run the command:

netsh http del iplisten ipaddress=<IP address to remove>

Restart IIS by running:

iisreset

Sisense Version 7.1.3 and below:

Sisense Web Application is installed by default on port 8081.When you want your users to simply enter a domain name without a specific port, this can be done by changing the default port of IIS to port 80.

You can do so by conducting the following steps:

Make sure the port is not being used by the IIS default website, if it is - change it to a different port such as 81 .

Go to Control Panel -> Programs and Features.

Find Sisense, right-click on it and select “Change”. localhost:3030

Choose your account and press “Change Features”.

Sisense Version 7.2 and above:

1. Navigate to and update to the port# you would like to.

2. Once complete click 'Save' and the services will restart and thats it!

**Please refer to the following article for extended functionality: Performing a Front End Left Join **

This articlepresents a data modeling based solution that may allow you to create a simple Left join within onecertain widget.

In order to create a LeftJoin in Sisense Elasticube, Please create a Custom SQL Table and perform the LeftJoin connection there.

In data modeling, the action of merging tables together (usually for querying purposes) is called a join. It is a very common action that happens behind the scenes every time you create a widget with fields based on more than one table. You can find more information about joins and join types in the "Joining Multiple Sources 101: Inner and Outer Joins" blog.

The following article will explain how you can create a simple 1 dim to 1 fact left join in your widgets.

The Challenge

By default, the queries generated by Sisense are creating an inner join, thus excluding all values that do not exist in both of the joined tables. Imagine the following scenario:

In our Elasticube, we have a Products dimension table and a sales-details fact table.

Due to the inner join, every widget that will fetch fields from both tables will only fetch records that have a full match. For example: If we try to query a list of products and the revenue yielded from their sales, we will not see any reference to products that were never sold.

In most cases, this is the required behavior. However, in others, the lack of activity can tell us a lot about our business and low performing items.

Implementing the Solution

1. In your Elasticube, find the dimension, or the master table from which you want to initiate the left join.

2. Add a new custom field of typeInt.The valueof this field should be a hardcoded 0:

3. Build your Elasticube to submit the changes.

4. Open your dashboard of choice and create a new widget. In the Categories\Rows panel, choose any field from the master dimension table

5. Create a new value and go to the formula menu:

6. Find the dummy field from your dimension table, and sum up its value. Add that aggregation up to the original measure from the fact table:

This combination will provide you with all of the values from your dimension, whether they have recorded activity or not:

Identify Items with no Activity

Once the Left join field is added and the "left" value is defined, you can create a widget that would list all items that have seen no activity. In our example, products that were not sold at all.

To do so, follow these steps:

1. Hover over the newly created value and click on the funnel icon to create a value-basedwidget filter:

2. set your filter to 0:

Your widget will now list all items with no activity.

If you would like, you can now disable the value and leave only the "Rows" active. As long as you don't change the filter, it will still remain valid.

Data accumulation is SiSense's method for building ElastiCube data without completely refreshing a table. This post describes the data accumulation functionality andgives a user the ability to customize and optimize their ElastiCube build process.

There are two options for accumulating data: by table or by index.In general, accumulate by table appends all of the source data to the ElastiCube table, and accumulate by index will use an index in an Elasticube table to filter the source data that is migrated.

Accumulate by Table

This option will append all of the data selected for the build onto the current table. No comparisons are made, and no data from the source is omitted. This option can be selected by selecting the additional preferences options for a table in the ElastiCube manager, and selecting 'Accumulate Data'.

If your source data contains rows that have already been loaded, the ElastiCube table will contain duplicate rows. The image below demonstrates a scenario where duplicate rows would be inserted.

For contrast- the following example shows usage of accumulate by table that doesn't generate duplicate rows.

Accumulate by Index

The index is a column in an ElastiCube table that is used to determine whether or not a row from the source data should be inserted. It must have a data type of either an integer or a date.

When you select an integer, only source rows with a value greater than the maximum index value in the ElastiCube table will be inserted. This option will never cause current data in the ElastiCube table to be modified or deleted. The following image demonstrates this logic:

After Load 1, the maximum index value is 3. In Load 2, the source index value of 2 is not inserted (since it is less than 3), but the source index value of 4 is inserted (since it is greater than 3).

Partner

HQ Country

Contact Person

Argo Logic

Australia

DeanKrowitz

Ayantek, LLC

USA, India

Praveen Ramanathan

Azuka

South Africa

Henry McCraken

BlueMetrics

Brazil, USA

DenisPesa

Business Wise Solutions

Israel

Dan Postar

CAUSE + EFFECT Strategy

USA

Michael Sutton

D. A. Team

Israel

Efi Goldman

Datore

UK

Graham Perry

Izi'nov

France

David Hertzberg

Paldi Solutions

USA, USA

Ravid Paldi

Profusion

UK, Middle East

Nathan Swaminathan

QBeeQ

Poland

Michael Becker

Rossi Labs

USA

David Rossi

Silutions Consulting

USA

Jim Thomas

It is possible to modify the cookie expiration period and by that to determine how for how long the user will remained logged in when checking the "Remember Me".

When a user logs in to Sisense and does not check the “Remember Me” check-box a session cookie is created leaving the user logged in, it will be automatically deleted when closing the browser.

When checking the “Remember me”, a cookie called prism will be created. This cookie expires after 30 days by default but this parameter can be easily modified using the REST API under “settings\security”.

Posting the following syntax will determine the number of days until the cookie expires.

The following syntax will set the cookie expiration to one day:

{

"cookieExp": 1

}

Please note that the parameter has to be an integer. Posting a decimal value (i.e. 0.5) will cause a “Redirect Loop” error which can be solved by deleting the configuration from the MongoDB with a MongoDB GUI tool. Restarting the IIS will be required.

This page is intended to keep a focus on Dashboard design in terms of:

Planning

Implementation

User experience and functionality

Dashboard training for end users

Dashboard Planning

Dashboard planning goes hand in hand with project planning, and will ensure an efficient, optimal and smooth project implementation.

Dashboard Implementation

The first stages of Dashboard implementation should initiate by testing and verifying data in early EC development stages. These tests should give initial immediate indications that the data is as expected, whether on complete data sets, or test tests, and should be done with straightforward widgets:

Numeric Indicators - for complete totals, averages and counts

Pivot tables - for segmentation and dimension table connectivity

Pie charts - for ratios

Trend charts - for time trends

These initial widgets can be an excellent base for initial OBP demos, for teaching basic to advanced front end features such as filtering (basic and advanced), measured values, data security multi-pass aggregations and much more.

After completing and verifying initial tests the initial test widgets can be a basis for creating formulas, and for the initial dashboard altogether.

Dashboard view

Twomain dashboard design thumb-rules:

Top to bottom - Left to right

Cognitive research proves that upon initial view of written and visual material, the eyes will tend to focus at the top left hand side and move to the bottom right, as when reading a book, the most important KPI's should be shown at the top left hand side, and deteriorate with meaning/importance as you move to the bottom right.

Publish and release in iterations

Don't hesitate to share and publish dashboards right at the beginning of your development. You will always be able to introduce additional future requirements and releases by re-publishing dashboards, this will be transparent and seamless to the end users. This will also enable you to receive feedback and iteratively improve and optimize your dashboards.

Certain KPI's together with their respective visualizations should be formatted with the end user's point of view and use case. The use case too is to be planned and conveyed to the user during dashboard usage training. A dashboard should have a structured view with immediate values at the top, moving down introduce certain trends which too can be immediate indications for monitoring, and higher resolution informative detail visualizations and the bottom. Remember not to overload on the end user with too many visualizations, and introduce user experience plugins when needed.

Navigation Homepage

Choose your visualization, Data visualization Wizard

User Experience and functionality

Throughout the entire BI project implementation, during both EC and dashboard, one must think of the end user's use case and user experience. (For example, the manner in which a user will filter will determine the difference between standard text columns, to entire dimension tables in the EC)

Following Sisense features, and more, should be discussed and considered during first stages of planning and development, in order to see the end user's ultimate experience.

Hierarchies - guide your user through their drilling and investigation, provide them with a guided path of drilling into their data and consider disabling 'drill to anywhere', preventing them from drilling to illogical paths

Data security - Understand your disparate users' data's sources and structure. Disparate data can be consolidated and distributed among your various users and user groups using data security.

User groups - Apply data security rules, share dashboards and apply additional processes on large amounts of users by use of group

Star formulas and filters - Create predetermined formulas and complex features for future use of your dashboard designers.

Dashboard functionality plugins

Various plugins provide much enhanced functionality for Sisense web highly benefiting the overall user experience:

The Accordion plugin

The Jump to dashboard

These plugins improve UX for a few reasons:

Show more with less! - Instead of displaying one extremely robust and overloaded dashboard with many many widgets, give the user a birds-eye view of the initial information and enable drilling and previewing of much more data

Take optimal advantage of valuable dashboard real estate

Load upon demand! - with an accordion plugin, eventually, you'll be providing many widgets from various dashboards on one, BUT not all widgets will load upon first refresh/load. Only when the accordion/jump to dashboards are called upon, will the widgets require resources to load.

Introduction

This Post Explains how to create a widget hierarchy that includes ‘week of’ as a member of the hierarchy. (Calculates first day of that week)

Business Case

As a business Analyst, I would like to drill down in a Bar Chart with Dated Sales Data from year to quarter, month, week and Day. In addition, in our organization week start on Tuesday.

Steps

Create Custom Field based on existing Date field to define ‘week of’ date. Depending on your approach to calculating week of, use one of the below formulas.

Day of week

Formula

Sunday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],0))),7)

*-1)

Monday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],-1))),7)

*-1)

Tuesday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],-2))),7)

*-1)

Wednesday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],-3))),7)

*-1)

Thursday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],-4))),7)

*-1)

Friday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],-5))),7)

*-1)

Saturday

AddDays([ShippedDate],

Mod((DayOfWeek(adddays([ShippedDate],-6))),7)

*-1)

2. Rebuild Elasticube

3. Define Hierarchy using the shipped date for year, quarter, month and day, use the new ‘week of’ field created for the week field.

4. Hierarchy now will be available to be used in widgets.

Multi Fact.ecdata Analytical Need

We have multiple sources for our measures and multiple sources to slice & dice these measures.

Modeling Challenge

How do we build a schema that will enable us to perform our needed analysis?

We will need to connect all of our tables so every criteria will be able to filter the population for the measures

Solution

We will build a Multi Fact Schema. This schema contains multiple fact table and multiple dimensions that are connected to them. Best practice recommends that all dimension tables will be directly connected to all Fact tables (this is in order to avoid indirect connections between tables - which can cause unwanted results. Would you like to know more? please refer to Handling Relationship Cycles ).

The keys from the fact tables will need to become invisible, in order to prevent confusion in the dashboard design, where several fields will mean the same. That is why we will only keep the key field from the dimension tables and the filters in the dashboard will only use fields from the dim tables.

The elasticube schema will look like this:

Image 1. Typical Multi Fact Schema (Facts on the left, Dims on the right)

Attachments

-473 KB -

This article is meant to provide initial troubleshooting steps for resolving Generic JDBC driver issues when trying to set this up in Sisense.

The JDBC connector allows you to:

Establish a connection from Sisense to a datasource that may not have a built-in connector available

Send queries between Sisense and the datasource (importing data, custom import queries)

Potentially update your connection from ODBC (.NET) to JDBC (Java)

When troubleshooting it is helpful to understand at what point you are running into the connection issue

Some notable points in the workflow are:

On attempting to establish a connection

here

On listing the databases to connect to

On listing the schema(s) and tables

Resources:

Connecting to JDBC - Sisense documentation on configuring a Generic JDBC data source

CDATA - Drivers with certified Sisense connectivity

Getting Help:

Please review the topics below in order to troubleshoot the connection. If you are continuing to see issues with connecting the JDBC driver, please create a ticket with our Support Team with the following information:

Datasource you are trying to connect to

Zipped up JAR file(s) for the driver

Connection info (if you are able to share that info)

Connection string

What step you are getting stuck on in the Sisense add data workflow

What you have tried so far to resolve

Any other datasource-specific info

Topics:

Verify Sisense Set Up

Configure Connection String

Check Logs

Compare Behavior with DBeaver Database Tool

Verify Sisense Set Up

Folder Location

Be sure to save off your JDBC driver into a folder that Sisense can access.

By default we recommend you save off your .jar (and associated files) in the following folder:

...\ProgramData\Sisense\DataConnectors\jdbcdrivers\

If you have downloaded a driver from a resource like CDATA, you can use the folder created by unpacking the files according to their instructions:

...\Program Files\CData\<driver name>\lib

If your driver requests you use a particular folder path that is ok as long as you reference the path in this parameter in the connection: "JDBC JARs Folder"

Using the JDBC Connection

The ability to use Generic JDBC connections in Sisense is supported in Sisense Web. If you do not see the generic JDBC icon in your list of available connectors in Sisense web, please log into your sever to establish the connection. This instability was resolved on Sisense Version 7.3+.

To set the connection up, please reference this documentation for specific steps: Connecting to JDBC

You will need the following information to set up the connection:

Connection String

JDBC JARs Folder

Driver's Class Name

Username

Password

Configure Connection String

Each JDBC driver will have specific information that is required to be passed to make a connection to the database. Typically there will be information such as a URL and target server IP or host name. Beyond these pieces of information, each driver will require varying parameters.

To formulate the connection string, take a look at the documentation that comes with the driver. The connection string will typically start with "jdbc:" and each parameter will be separated by a semicolon (;)

If you are having trouble establishing a connection, please review the documentation of the driver to see if there are any parameters that you can use to adjust the connection such as:

Connection Timeout: Sometimes there are parameters that can set the timeout manually

Logging: Some drivers allow you to designate an additional log file path (such as C:\temp) and verbosity

Streaming Method: Some datasources have configuration on which type of streaming is needed to pull data (such as via API)

Authentication Method: Some sources (such as AWS drivers) allow multiple authentication methods

Port: Some connections require a port. Be sure the port you are using is open on both the Sisense server and the target datasource server

Check Logs

The JDBC driver writes to the following folder:

\%ProgramData\%\Sisense\DataConnectors\JVMContainer\Connectors\GenericJDBC

These logs are helpful to determine at what point in the connection process the driver is failing. Additionally, some of the red text errors you may see when the connection to the datasource fails () are written to this log.

Compare the Behavior with DBeaver Database Tool

If the steps above do not assist in getting you closer to successfully connecting to your datasource through a JDBC connection, we recommend testing the connection with a free database connectivity tool. Any tool that allows JDBC connectivity is sufficient, below are instructions to set this up on DBeaver.

Using this tool will help narrow down if there are connectivity issues regardless of the tool to connect to the database or if the issue is specific to connecting with Sisense.

In a multi-server deployment set up, this tool should installed on the node where builds are being performed.

Steps to Download and Set up the JDBC connection:

Download DBeaver Community

Run the installer

Open DBeaver and navigate to Database > Driver Manager

Click New

Enter a Driver Name (you choose the name) and the Class Name (as determined by the driver you are using). The driver type should be "Generic"

Then click "Add File" and select the driver file(s) you put in the jdbcdrivers folder (or location you put the JDBC driver). Then click "OK"

Once the database driver is set up, then click on Database > New Database Connection

Select your driver (in this case "TestConnection") then click "Next >"

Enter the connection string in JDBC URL as well as the Username and Password (if applicable). Then click "Finish"

On the left-hand side of the window you should now see a new connection. Double click on it to attempt to connect.

If the connection is successful (you can connect to the database, preview data in tables, etc) there is troubleshooting that will need to be done specifically between Sisense and the datasource.

If the connection is not successful in one or more of the steps to view tables, this means there is likely additional troubleshooting that is necessary for the connection string/connection/database settings.

Symptom

If you are unable to connect Sisense with your Redshift data this might be caused by high MTU settings.

Explanation

Some Amazon EC2 servers are set by default to an MTU value of 9001 (Ethernet "Jumbo frame"), this setting might cause connectivity issues and you will not be able to connect Sisense with your Redshift data. What you will see when trying to connect to Redshift is a none responsive connection causing The import-data dialog box to hang.

Solution

In order to solve this, you will need to change the MTU settings to 1500 using the following command in your CMD:

1. From your start menu, run the command prompt by typing 'cmd' and 'enter'.

2. Copy and paste the following command:

netsh interface ipv4 set subinterface "Ethernet" mtu=1500 store=persistent

3. Hit Enter and restart your Amazon instance.

4. Run the ElastiCube Manager again and import the Redshift data.

For more information please see the following link: http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-drop-issues.html

If you still encounter issues wit the above, please don't hesitate to submit a support request.

Introduction:

This post describes how live interactive dashboards can be embedded into Microsoft PowerPoint presentations.

A common use for this ability isto leverage theSisense dashboards for storytelling, allowing the presenter of the dashboard to answer questions on-the-fly be filtering and drilling into the data during the meeting itself.

Big Plus: Whenever the presentation is opened and presented, the data and dashboards will be updated with the most up to date data in the ElastiCube = No need to manually update your presentations anymore!

In order to embed a Sisense dashboard into a Microsoft PowerPoint presentation, the following steps are required.

A short video example of this can be seen in the following video:

Prerequisites:

1. Sisense installation and dashboards

2. Sisense server enabled with SSL - Setting-Up-SSL

3. PowerPoint 2013 / 2016 or Later

Installation:

Install the "Web Viewer" add-on on the PC running the PowerPoint presentation (this can be found at the MSFT Office Store).

Embedding-dashboards-widgets

Note: The "Web Viewer" add-on is a 3rd party SW and is not developed or supported by Sisense.

After completing the installation, in any slide in which you wish to embed a dashboard or widget, press on Insert --> My Add-ins --> Web Viewer:

In the URL box, enter the URL of the dashboard or widget you would like to embed.

Note: Make sure to remove the https://at the beginning of the URL and add the embed=true switch to the end. (more on iframe embedding options can be found at:).

For example:

Note: The computer running the PowerPoint slideshow must have network access to the Sisense server (in case a firewall is in place, please create the appropriate rules to allow access to the ElastiCube server).

Due to a limitation in the "Web Viewer" add on, in order to update the dashboards / widgets, close the PowerPoint presentation and open it again.

This page is meant to provide an initial reference for website loading issues.

Topics:

1. Page Doesn't Show Up

2.No Dashboard Panel on Left Side of Analytics Page/Bottom of Analytics Page

3.Queries are not Returning Data (Including Dashboards)

1. Page Doesn't Show Up

When the Sisense web page fails to load.

The symptoms could be that you see a 500 error when navigating to your web page.

Note:If you have just set up SSL on 7.2 refer to our SSL troubleshooting guide here.

Sisense Version 7.2 and above

On Sisense 7.2 and above, this error could look like the screenshot below:

Sisense Version 7.1 and below

To resolve, follow steps below:

On your Sisense webserver, open the Control Panel

Click on 'Administrative Tools'

Click on 'Services'

For each of the services, please find them in the list and repeat steps 5&6, in the following order:

Sisense.Gateway

Sisense.Broker

Sisense.Galaxy

Right-click on it and select 'Stop' (make sure its status field becomes blank)

Right-click on it and select 'Start' (make sure its status field changes to "Running")

If this does not resolve the issue you are facing, please create a ticket with our support team that includes the application-logs directory on your Sisense webserver (located C:\ProgramData\Sisense\application-logs).

Sisense Version 7.1 and below

Sisense Verison 7.1 and below runs on the Microsoft program Internet Information Services (IIS). Sisense Version 7.2 runs on NodeJS and most of the site no longer runs on IIS which means you are not likely to run into this same issue on Sisense 7.2 and above.

On Sisense 7.1 and below, this error could look like the screenshot below:

To resolve, follow the steps below to restart IIS:

On your Sisense webserver, go to the Start Menu and 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.

Close the command prompt window.

Try to re-load the website, keep in mind it can take a few minutes for the site to come back up (longer when there are very large cubes or a large number of cubes).

You'll know the site is back up when where are 7+ node.exe processes within Task Manager

If this does not resolve the issue you are facing, please create a ticket with our support team that includes the iisnode directory on your Sisense webserver (located C:\Program Files\Sisense\PrismWeb\vnext\iisnode).

2. No Dashboard Panel on Left Side of Analytics Page/Bottom of Analytics Page

This issuecould look like the screenshot below where you are not able to see any of your dashboards in the left panel or in the section at the bottom of the Analytics page:

Sisense Version 7.2 and above

To resolve, follow the steps below to start services:

On your Sisense webserver, open the Control Panel

Click on 'Administrative Tools'

Click on 'Services'

For each of the services, please find them in the list and repeat steps 5&6, in the following order:

Sisense.Galaxy

Sisense.Gateway

Sisense.Broker

Right-click on it and select 'Stop' (make sure its status field becomes blank)

Right-click on it and select 'Start' (make sure its status field changes to "Running")

Open your Sisense website within a new browser window

If this does not resolve the issue you are facing, navigate to the following URL: /app/test (for example: http://localhost:8081/app/test) and see which services (if any) are failing.

Please create a ticket with our Support Team that includes the application-logs directory on your Sisense webserver (located C:\ProgramData\Sisense\application-logs) and a screenshot of the results of the /app/test site. Please also mention if your organization uses AD within Sisense.

Sisense Version 7.1 and below

This is unlikely to happen on Sisense Version 7.1 and below.

If you do run into this issue, please create a ticket with our support team that includes the iisnode directory on your Sisense webserver (located C:\Program Files\Sisense\PrismWeb\vnext\iisnode) and the DB directory on your Sisense webserver (located C:\ProgramData\Sisense\PrismWeb\Repository\DB).

3. Queries are not Returning Data (Including Dashboards)

The primary symptom is that the dashboard returns yellow diamonds for every widget even through the ElastiCube exists and is running as shown in the screenshot below:

The dashboard could also show the loading icon for a very long time.

Sisense Version 7.2 and above

Disclaimer: If your organization is using Active Directory (AD) please open a ticket with our support team to assist in troubleshooting.

To troubleshoot On your Sisense webserver, navigate to the following URL:http://localhost:14996/paths and see what the site responds with:

If you see data returned, like shown in the screenshot below:

Restart the ECMS service by following the steps below:

On your Sisense webserver, open the Control Panel

Click on 'Administrative Tools'

Click on 'Services'

For each of the services, please find them in the list and repeat steps 5&6, in the following order:

Sisense.ECMS

Right-click on it and select 'Stop' (make sure its status field becomes blank)

Right-click on it and select 'Start' (make sure its status field changes to "Running")

If you do not see data returned,like shown in the screenshot below:

Restart IIS by following the steps below:

On your Sisense webserver, go to the Start Menu and 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.

Close the command prompt window.

Try to reload the dashboard.

If the steps above did not resolve your issue, please do the following to create a new catalog folder which will most likely resolve the issue:

Important note: Please be aware that your build-scheduling settings will be erased after the operation, so you will need to re-configure these on all your ElastiCubes.

Open the start menu or Task Manager and search for 'Services'

Find the following services and stop them

'Sisense.ECMS'

'Sisense.ECMServer'

Open the following path - C:\ProgramData\Sisense\PrismServer and rename the 'ElastiCubeCatalog' to 'ElastiCubeCatalog1'.

Open the 'Services' as previously done and start the 'Sisense.ECMS' and 'Sisense.ECMServer' service once again, this will take a bit longer than usual forcing Sisense to create a new catalog folder.

Once the services are back up, try to reload the dashboards

If this does not resolve the issue you are facing, please create a ticket with our support team that includes The ECS logs (located here:C:\ProgramData\Sisense\PrismServer\PrismServerLogs). Also inform the agent what you have tried to do to resolve the issue so far and whether your organization has integrated Active Directory (AD) with Sisense.

To troubleshoot, follow the steps to restart the ElastiCube Management Service:

On your Sisense webserver, open the Control Panel

Click on 'Administrative Tools'

Click on 'Services'

For each of the services, please find them in the list and repeat steps 5&6, in the following order:

ElastiCubeManagementService

Right-click on it and select 'Stop' (make sure its status field becomes blank)

Right-click on it and select 'Start' (make sure its status field changes to "Running")

If the steps above did not resolve your issue, please do the following to create a new catalog folder which will most likely resolve the issue:

Important note: Please be aware that your build-scheduling settings will be erased after the operation, so you will need to re-configure these on all your ElastiCubes.

Open the start menu or Task Manager and search for 'Services'

Find the 'ElastiCubeManagementService' and stop it

Open the following path - C:\ProgramData\Sisense\PrismServer and rename the 'ElastiCubeCatalog' to 'ElastiCubeCatalog1'.

Open the 'Services' as previously done and start the 'ElastiCubeManagementService' service once again, this will take a bit longer than usual forcing Sisense to create a new catalog folder.

Try to reload the dashboard.

If this does not resolve the issue you are facing, please create a ticket with our support team that includes The ECS logs (located here:C:\ProgramData\Sisense\PrismServer\PrismServerLogs). Also inform the agent what you have tried to do to resolve the issue so far and whether your organization has integrated Active Directory (AD) with Sisense.

Analytical Need

Gauges are types of widgets that are very helpful in presenting progress or whether a KPI is met such as current state vs. expected state based on previous calculated value.

At times, we might be interested to show a target that needs to be met and present it on the Gauge.

For example, the sales manager wants to show the current sales progress of its team and how far the team is from the monthly target.

Solution

To present a target, such as a sales or score target on a Gauge widget we can use the 'Change Value Colors' on the relevant value and edit the coloring conditions of the Gauge:

The 'equals' will represent the value on the Gauge to be placed with a black mark.

You may use more complex formulas to determine the area to mark in black such as a 'Target' value in your dataset.

In the widget design pane, make sure to select the Gauge option with Skin #2:

Result:

That's it!

This article will cover two methods you can use to connect Python to the Elasticube.

Method 1: Using ODBC

Method 2: Using the REST API

Method 1: Connect to the EC with ODBC

Download Sisense ODBC

This method will require you to download the ODBC driver.

Follow the instructions on the ODBC documentation page before starting to work in Python.

Connect to the system DSN ODBC with pyODBC

The following libraries are required for this step (both are standard with anaconda)

pyodbc

pandas

Step 1: Import libraries

import pyodbc

import pandas as pd

Step 2: Establish a connection to the EC

cnxn = pyodbc.connect('DSN=Sisense ODBC Driver')

Step 3: (optional) Print out available tables

# Create a cursor that can execute commands against the connection

cursor = cnxn.cursor()

# Print all available tables

for t in cursor.tables():

print(t[2])

Step 4: Put the data into a dataframe

Substitute <Table Name> with the table you want to query

# Bring the data into a dataframe called df

df = pd.read_sql('select * from <Table Name>', cnxn)

# Print out the top records from df

df.head()

Method 2: Query the EC with the REST API

This method will require the following python libraries:

requests

urllib.parse

pandas

from pandas.compat StringIO, BytesIO

Step 1: Import the libraries

import requests

import urllib.parse as parse

import pandas as pd

from pandas.compat import StringIO, BytesIO

Step 2: Define the datasource and your query

# Your elasticube name is your dataSource

dataSource = 'leapYearUseCase'

# Query the data as if you were writing a custom SQL query

query = 'select Years, min(Amount) as maxAmount from <Your Table> group by Years'

Step 3: Parse the queries

dataSourceURI = parse.quote(dataSource)

queryRUI = parse.quote(query)

Step 4: Establish your connection

To retrieve your Bearer token follow instructions under USING THE SISENSE API - Authentication

# http://localhost:8081 may vary depending on your configuration

# The request asks for the data formatted as a csv for convenience

endPoint = 'http://localhost:8081/api/elasticubes/{}/Sql?format=csv&query={}'.format(dataSourceURI, queryRUI)

# Enter your bearer token in place of <your bearer token>

header = {'Authorization': 'Bearer <your bearer token>'}

Step 5: Post and Get the Response

# Post to get the response

response = requests.get(endPoint, headers=header)

# Collect the response text formatted as a CSV for convenience

data = response.text

# Put the data into a dataframe

df = pd.read_csv(StringIO(data))

Cheers!

This article will show how to set conditional formatting on a specific value based on another one which doesn’t directly relate to it. For example, when one wants to show a total of revenue in a specific region and set its color to green in case the market share in the same region has reached the goal of 15\%. measured values

To format the first value based on the second value performance, we will create a function that returns a number greater than 1 in case we didn’t achieve the goal and smaller than 1 else.

Step 1 - Create the indicator

In this case, the first value is the revenue of USA. In the above example, we used the pre-saved Revenue formula and the country as filter, using .

The secondary value in this case will be the contribution of the USA number of orders. starring this formula is recommended for future usage.

Step 2 - Set the conditional formatting

For this example, the goal for USA market share is 15\%. The function we will use will be our goal divided by the secondary value. If we reach the goal, this function will return a fraction, otherwise it will be greater than 1 because the denominator will be smaller than the numerator.

We will multiply the primary value by this number and use it for our conditions.

the result:

When we don’t reach the goal the function will return a greater than 1 number the product will be greater than the first value the first condition will apply and the indicator will present red text

When we reach the goal the function will return a fraction the product will be smaller than the first value the second condition will apply and the indicator will present red text

The mathematical explanation for the formula will be:

USA REVENUE Vs. (USA REVENUE)*(GOAL/USA MARKET SHARE)

After dividing the formula by ‘USA REVENUE’ this formula is equivalent to:

1 Vs. GOAL/USA MARKET SHARE

Apply it and you are set.

If you would like to use two unrelated filtering conditions on one column, you will need to create a advanced filter in the widget. An example of this would be reporting the quantity sold for the top 5 selling categories excluding one category. In order to do this, "AND" based logic would need to be used to combine the two filtering conditions.

Solution:

Create each filter individually, and copy the relevantscript from the "advanced" section.

Select 1st the filer script that includes more values - "Main Filter" ( In our scenario the "top 5" includes within the category we want to exclude).

Add Filter Syntax - ", "filter":{ }

Within the filter brackets add the secondary filter that will act similar to WHERE.

Wrap the entire script in brackets{}

Finally, test the JS

MultiFactNotAllDims.ecdata Analytical Need

In a dashboard, we wish to analyze how our entities (sales, inventory, tickets etc.) behave and break them by several categories. However, not all entities share the same categories.

We wish to avoid a situation in which selecting a value in a certain category will produce illogical/incorrect results due to the random path mechanism (because there is no direct path between each dim & each fact).

Modeling Challenge

In the image below, getting from the DimStatus to ClosedProjects can be achieved via several paths:

From

Via

To

DimStatus

In Progress

Dim Date

ClosedProjects

In Progress

Dim Project

In Progress

Dim Account

In Progress

Dim PM

Open Projects

Dim Date

Open Projects

Dim Project

Open Projects

Dim Account

Open Projects

Dim PM

Image 1. No direct path between Dim Status & Closed Projects fact table. In red are the status dimension & fields.

** Random path mechanism (based on Handling Relationship Cycles ):

When Sisense encounters numerous possible paths (examples described above), it chooses the shortest path, which will have less impact performance-wise on thequery.

The logic in choosing the path is as follows:

Sisense will prefer relationships in the following order: 1:1, 1:m, m:m.

If all possible paths contain many-to-many relationships, the path with the least number of m2m connections is preferred.

If more than one possible path still exists, it willprefer the path containing the least number of tables.

Finally, if numerous paths are still possible, one is picked at random.

Solution

We will add a fake key to our schema in a 3 step solution. This will ensure the direct path between the Status & Closed Projects tables:

Step 1: Add a fake key to the Dim Status table.

SQL:

SELECT DISTINCT p.Status FROM [InProgress] p

union

SELECT DISTINCT o.Status FROM [OpenProjects] o

union

SELECT '-1'

The last union ( Select '-1' ) will add another record to this dimension. This is one side of the direct connection to our Closed Projects table.

As a rule of thumb, if there is a description for the key, put down a value that symbolizes that is it a fake record, like 'No Status'/ 'Fake Status' etc.

Step 2:Add the fake key to the Closed Projects fact table (either by the below example query or by adding a new custom field with the value of -1):

SQL:

SELECT p.*,

'-1' AS Status

FROM [ClosedProjects] p

Adding this '-1' as a new field will be the other side of the direct connection to the dim status table.

Step 3:

Connect the dim & fact table. The schema will look like this:

Image 2. Updated schema. Marked in red are the changes that have been made.

Include/exclude options:

Include : If nothing is selected in the filter (taken from the fake linked DimStatus) then the measure from the connected fact will still be calculated. When any value is selected then no results/null would appear for the measure - This is the solution described above.

Exclude : If there is a filter, then the measure would never be calculated - Skip step 1 in the solution.

Attachments

-545 KB -

Overview:

This article aims to cover preventing, diagnosing, and resolving performance issues in Sisense.

In Sisense,performance is generally referred to as the time it takes to load a dashboard or build a cube. The end goal for many organizations is for users to have a smooth and enjoyable experience when working with their data.

The primary method for avoiding performance issues is maintaininga clean environment and and ensuring that your servers meet theminimum requirementsfor working with Sisense.

Not all performance issues can be prevented with a clean environment alone, so this article also covers some of the basics of maximizing and optimizing Sisense performance.

Topics:

1. Maintaining a Clean Environment

2. Maximize Dashboard Performance

3. Optimize ElastiCube Performance:

A. Decreasing Build Time

B. Optimizing Queries

4. Getting Additional Assistance

RelatedArticles:

Minimum Requirements and Supported Platforms

Sisense Monitor

Sisense Performance: A Billion Records In A Single Server

Software Performance Issues? It Might be Your Architecture.

Maintaining a Clean Environment

To maintain a clean and stable environment, we recommend the following best-practices:

End Users:

Inform end users of new features and any errors you see within the logs related to their dashboards or Elasticubes

Hold end users accountable for removing old and unused dashboards if they have dashboard creation abilities

Dashboard Designers:

Organize dashboards into folders to more easily manage dashboards

Remove test dashboards when you're done using them

Turn off scheduled email reports that are no longer relevant

Data Designers:

Remove old Elasticubes that are not in use from the Sisense Server Console

Space out builds so there are no more than 4 concurrent builds at one time

When adding data to the cube only import the tables and columns you expect to use in the data model and remove tables you no longer need

Administrators

Remove users who no longer work at your organization or should no longer have access to Sisense

Power down and remove Sisense from servers that you are no longer using in your deployment

Turn off and remove any plugins that your organization is not using. Also test plugins in non-production environments when possible

Clean old Elasticube version C:\ProgramData\Sisense\PrismServer\ElastiCubeData

Manage your plugins

Maximizing Dashboard Performance

Please refer to the following associated article that takes an in-depth look at dashboard performance: Maximize Dashboard Performance

Optimize ElastiCube Performance

To optimize Elasticube performance there are two focus areas:

Decreasing Build Times

Optimize Queries

Decreasing Build Time

In order to optimize the build time, reduce:

Many fields, long strings:

Don't import long string fields if they will not be used in the model

Always question the need for columns with long strings before adding them to the model (URLs, very long comments)

Many dates:

Removing time-based data that is not needed will reduce build time (ie. don't import old data if you don't need to)

Consider the date range in the requirements of the dashboard and data model

Import a Dates File instead of a Custom table to create a date dimension

Use the source database when possible:

Create views to replace custom tables and import the View

Filter out irrelevant data (history, in-active, etc.)

Customize the query when adding the data to the Elasticube

Optimize custom tables

Avoid Processing Power and Time Expensive Operations:

Replace UNION with UNION ALL when possible

Left and Right joins

Consider lookups

Filter data within the table

Avoid Redundant Operations:

Consider sub queries

Avoid SELECT *

Order by

Optimizing Queries

In order to optimize the queries in the ElastiCube, do the following:

Consolidate:

Look up “translation” tables

Avoid unnecessary joins

Consolidate Facts

Calculate custom columns:

In large data sets it may be significant

Sum and DUPCOUNT are faster than count

Joins on indexed fields:

Check for casting in custom tables

Cast fields in source tables instead of using casting functions

Join on numeric fields:

Numeric dates

Join on Date with no Time component

Surrogate Keys:

When possible, create in the DB

Avoid surrogate keys when big data

(consider numeric using this article )

Getting Additional Assistance

If you are still not able to resolve the issue with the information above, please make sure to submit a support ticket clarifying the following points:

When did the issue first begin? Has it ever worked as expected in the past?

Have you conducted any recent changes, such as system changes or Sisense version upgrade?

Does the issue reproduce at all times, or only in a specific scenario?

Does the issue occur across the board, or only on specific dashboards/cubes/users?

During the time of the issue, do you find the system under high RAM/CPU utilization?

It would be very helpful to receive step by step screenshots, or a short video of the occurring issue, in order to make sure we fully understand the scenario. You can use Windows steps recorder in order to do so (press WIN+R,then type psr and press enter).

How do I reset my password?

If you need to reset the password to access your dashboards, navigate to the URL for your dashboards and click the hyperlink for "Forgot your password? Click here":

Automatically Backup Sisense Web Data

Then enter your email address and click send:

You should receive an email with a temporary link to reset your password. If you do not receive an email, please check your spam/junk folder.

For some customers, we are aware of issues with password reset emails not being sent. If you do not receive a password reset email after checking your email filters, please contact a Sisense administrator at your organization who can change your password for you. If you are the Sisense administrator and have not received a password reset email, please contact Support for assistance.

If you have forgotten the password for your Sisense.com or license account,go to https://www.sisense.com/si-login/ and click Forgot password. Follow the steps to enter your email address and send a reset email. Check your inbox and click the reset password link to proceed and enter your new password. If you have issues in the process, please contact Support.

How do I change the package owner for my license?

Please open a ticket with Support specifying your organization, the current package owner, and the desired package owner.

How do I change the Sys. Admin user for my license?

Because Sisense is limited to one Sys. Admin user per license, you cannot easily move the Sys. Admin designation from one user to another. Under Admin -> Users, you can manually change the properties for the Sys. Admin user to another user.

For more complicated scenarios such as switching between two users or other scenarios, please open a ticket with Support and provide the relevant details.

My dashboards are not loading. Widgets spin indefinitely or connection to the ElastiCube cannot be found.

Dashboard widgets:

Widget error:

Admin -> Data Sources

Sisense Server Console:

Please try to restart your Sisense services by opening the Task Manager on the server, clicking the Services tab, and restarting the following services:

Sisense.ECMS and Sisense.ECMServer (or ElastiCubeManagementService on Sisense Version 7.1 and below)

Sisense.Repository

Sisense.Oxygen

In addition, open the command prompt as an administrator and restart the IIS web server by running “iisreset” command.

It may take a few minutes for Sisense to resume as usual.

Please note that these steps do not cover every scenario. This behavior can be caused by many-to-many relationships, heavy queries or user concurrency, firewall/port issues communicating with the ElastiCube Server, missing Elasticubes, network issues, etc.

Please refer to this article with additional troubleshooting steps: Troubleshooting Guide: General Website Loading Issues.

How do I import data from a file using a network path or a mapped drive?

Unfortunately, Sisense currently does not officially support importing files (Excel/xls/xlsx, csv, txt) using a network path or from a mapped drive. For some cases, the troubleshooting guide here can help: Adding Data From A Shared Network Folder. Many of our customers set up processes to move files to a local drive before starting the build. This can be accomplished, for example, via a batch script and/or a pre-build plugin: ElastiCube Plugins.

What are the Sisense services and what do they do?

Please refer to this article for an overview of Sisense services: Identifying Sisense Services

My ElastiCube build failed with an error. How do I troubleshoot it?

We are working to build out a list of common build errors and troubleshooting tips here: Build Errors Troubleshooting. This

A legacy document with older build errors can be accessed here: Build Errors List

You can add your own build errors here: Build Errors And Solutions

I'm having issues with emails, reports, or dashboard exports

Please review our email troubleshooting guides:

Email Troubleshooting

Email Troubleshooting - Sisense 7.2+

For PDF Export Troubleshooting, please review the following guide: Troubleshooting Export To Excel Issues

If you are still experiencing issues after reviewing the articles, please open a ticket with Support, describing the issue you are experiencing and the steps you have taken to troubleshoot.

My upgrade or installation to Sisense 6.7 or later failed in the testing stage with the message “Failed to connect to to web server”

This is a known issue with the installer that generally does not indicate an issue with the installation. Please skip the error and verify you are able to access Sisense afterwards.

How do I reduce the amount of space Sisense takes up on my drive? Can I change the default installation settings?

Sisense currently supports installation only on the C:\ drive. You can store ElastiCubes on a secondary drive using the instructions here. In addition, you can periodically clear out logs in the following locations such as the below that tend to take up the most space:

C:\ProgramData\Sisense\PrismServer\PrismServerLogs

C:\ProgramData\Sisense\PrismServer\LogsPersistence\logs

C:\ProgramData\Sisense\PrismWeb\Logs

C:\ProgramData\Sisense\Monitoring\LOGS

How do I use time in my dashboards?

Sisenseintroduced time resolution (hours and minutes) in dashboards in version 7.0.1, which was released in January 2018. In previous versions of Sisense, the time will be stripped off in the dashboard. There are a few posts in our community with scripts and queries to help handle time-based display and operations:

Numeric Representation Of Date Fields

Convert Seconds To Time Format In Pivot Tables

Represent Time In Widgets

Indicator With Dates, Time And Duration

The results in my dashboard/widget don’t match what’s in my ElastiCube (wrong/incorrect results)

Confirm that your field names in the dashboard match the correct field names in the ElastiCube, the cube has been built with the data you expect, and there are no other variables accounting for the difference. Beyond that, we are aware of occasional issues caused by Sisense’s indexing process that is applied on text fields in the ElastiCube, predominantly on Custom SQL Expression tables. Please remove the indexes on these fields, rebuild the cube, and check again.

How do I customize Sisense dashboards beyond what is natively available in the product?

Sisense can be customized and modified using our JavaScript API. Developer documentation is available here: JavaScript API & Plugins

Our community board for questions and examples of using the JavaScript API is available here: JavaScript API Community Forum. There are also many Sisense and customer-developed plugin and script examples available online across Sisense.

We do not officially support custom scripts and customer-developed plugins, and we recommend thoroughly testing these scripts and plugins before deploying or upgrading in production environments.

Finally, for customers who need to extend the functionality or appearance of Sisense and do not have development resources on hand, please ask your Customer Success Manager about Sisense’s Professional Services team, which provides custom development work for a fee.

My dashboard is loading slowly. How can I troubleshoot this?

Before reading through these legacy recommendations, please take a look at this article: Performance Issues Troubleshooting

First, verify that you are comfortably meeting Sisense’s minimum hardware requirements: Minimum Requirements and Supported Platforms. Precise hardware requirements will vary based on many factors, and Sisense’s recommendations are basic guidelines. If your server’s resources are consistently taxed in periods of normal operation, consider upgrading your machine’s hardware.

Second, exceptionally slow or failing widgets can suggest heavy queries, high query concurrency, or data modeling issues such as many-to-many relationships. If the memory usage on the server spikes on dashboards associated with particular ElastiCubes, carefully review those ElastiCubes for many-to-many relationships ( Many-to-Many Relationships ).

In addition to testing for M2M relationships with the SQL queries in that article, we recommend using our official JAQLine plugin to view the relationships and identify many-to-many relationships within your widgets: JAQLineVisualize Widget’s Queries. Please note that a many-to-many relationship can sometimes manifest only after applying a filter when the filter introduces a join path with a M2M relationship. In addition, Sisense 7.0 and later automatically handles and minimizes the impact of many M2M relationships.

Third, review the dashboards themselves. The most frequent causes of slow front-end performance are heavy left-hand navver panes (for customers with many dashboards, placing them in folders will help), too many widgets on one dashboard, complex widgets, wide pivot tables (with many measures going across the pivot table), exceptionally long pivot tables (thousands and thousands of rows), many dashboard filters (especially on unindexed text fields), complex nested front-end formulas, data modeling issues, multiple well-populated mapping widgets, and heavy use of custom plugins and scripts. Please see the link below (“Maximize Dashboard Performance”) for more information and tips. We generally recommend streamlining dashboards and using plugins like Jump to Dashboard, Switchable Dimensions, and Accordion to break up load time.

Lastly, outside of Sisense, network, connection speed, web browser, processes running on the server, and client machine resources can all impact load time.

The following article provides more information about contributors to performance and troubleshooting tips: Maximize Dashboard Performance

Finally, many customers have access to our monitoring tool, which provides a robust picture of Sisense usage and behavior. This tool is described here: Sisense Monitor

If your organization is interested in adding this feature onto your license, please reach out to your Customer Success Manager.

My pivot table widgets' columns are cut off in reports

When preparing a PDF or email report based on a dashboard with table or pivot table widgets, Sisense will try to fit the widgets on the page. However, wide tables will sometimes be cut off. This behavior has been reported by many customers and it is currently under review by our Product team for improvement.

A few suggestions to minimize this behavior:

When previewing a PDF report, click Edit at the top of the page to resize or move widgets for best fit

Increase the paper size or change the orientation

Reduce the number of columns in the widget

Use the Table with Aggregation plugin as an alternative - it tends to fit better on reports because of its auto-sizing feature.

How do I undo changes in my dashboards/front-end?

Changes in the front-end web application (dashboards, users, shares, etc.) are live and cannot be undone or rolled back. For example, if a user who owns dashboards is deleted, the dashboards will be deleted along with the user. This change is irreversible and there is no way to recover this data.

Customers can back up their Sisense front-end data by using the steps here:

Introduction

Sisense automatically sends emails when certain actions occur, such as a new user being added, a dashboard being shared with a user, or a Pulse alert being fired. For a complete list of Sisense automated emails, see the top of Rebranding Sisense Automated Emails. Additionally, Sisense sends recurring email reports if configured by the dashboard owner. For more information, see Sending Email Dashboard Reports.

Email reports are sent by default from [email protected]. This page provides guidance on how to change the email sender to a personalized email address.

For general information about how to customize email report content, see Rebranding Sisense Automated Emails. For troubleshooting email report issues, see Email Troubleshooting. For how to set up the System Configuration alias that determines the URL back to Sisense in your email reports, see System Configuration Walkthrough (Alias and SSL properties).

Available Options

Sisense offers three options for the email sender:

Default: Use the default sender ( [email protected] ) and Sisense's built-in email platform ( Mandrill by Mailchimp)

Use Sisense's email platform Mandrill but send emails from your own domain ( [email protected] ) *

Use your own email server

The steps below detail how to set up options #2 and #3. Option #1 is native to Sisense and requires no additional steps to implement.

* White-labeling Sisense must be included in your license

Send from Mandrill with Custom Domain

To use Mandrill as the email server but send from a custom domain (for example, [email protected] ), there are three separate steps that must be completed - updating the DNS record for the domain to use Mandrill, rebranding Sisense to use the domain through Sisense's REST API, and verifying the domain.

Updating your domain's DNS record

As a prerequisite to use Mandrill, your Sisense server must be able to communicate with Mandrill. For more information, please see Configuring Email Server To Use Mandrill's Static Server. Confirm you are able to ping mandrillapp.com (or Mandrill's static IPs if used).

You must add a Mandrill-specific SPF record and DKIM TXT record to your sending domain's DNS record. Please see Mandrill's documentation for the requirements: https://mandrill.zendesk.com/hc/en-us/articles/205582277-How-to-Add-DNS-Records-for-Sending-Domains.

Implementation steps will vary by hosting provider. Mandrill's page links to SPF and DKIM validator tools. Please validate both items - any issues will prevent emails from being delivered by Mandrill. Sisense does not assist with these steps; please contact your IT group or hosting tool for assistance.

Applying Email Branding via the REST API

For general information about email rebranding, please see Rebranding Sisense Automated Emails. For general information about the REST API and how to generate an authentication token to run API calls outside of the Sisense web application, please see Using the REST API.

Sisense is bundled with a Swagger UI so you can easily run these API calls in a graphic interface.

Steps to Implement

1. Sign into Sisense as a sys. admin or admin user

2. Click Admin at the top bar:

https://support.sanebox.com/hc/en-us/articles/219991548-SaneFwd-Common-Email-Host-Settings

3. Click REST API in the left pane:

4. Click REST API Reference:

5. After the page loads, click 0.9 in the upper-right corner:

6. Click branding, then POST /branding:

7. In the body section, copy and paste the following JSON:

{

"emails":{

"senderEmail":"[email protected]",

"senderName":"BI Admin"

}

}

Replace the senderEmail with the email address you want to send emails from. You must have access to this email account. Replace senderName with the email sender name you want to use in Sisense emails.

8. Click Run:

A successful response is an empty response body and a 200 response code:

Troubleshooting:

You must have rebranding available in your license; contact your Customer Success Manager for more information

To resolve a 500 - Internal Server Error response, run your body through a JSON validator

If your POST body conflicts with an existing rebranding scheme, run a GET call on branding to see what rules are currently in place

Verifying your domain in Mandrill

After completing both of the above steps, please contact Sisense Support by creating a new request ticket.

Communicate that you need to verify your custom email address in Mandrill and provide the email address you wish to use. A support consultant will send a domain verification email to the specified email. Follow the instructions in the email to complete the verification process.

Afterwards, send a test email and confirm the behavior is working as expected.

Use your own custom email server

Using your own email server allows you to administrate email transactions without using Mandrill. For steps to implement, please see Setting Up a Custom Email Server.

To obtain the host for the email server, please see this page for hosts for common email providers:.

Typically port 25 is used for unencrypted email communication, port 465 for SSL, and 587 for StartTLS (recommended). Contact your email server for more information about the host and port to use.

Troubleshooting

Use the steps in Setting Up a Custom Email Server to enable logging and debug mode. Try to send an email and review the logs. The logs to examine depend on the Sisense version:

Version 7.1 and below: C:\Program Files\Sisense\PrismWeb\vnext\iisnode

Version 7.2 and later:C:\ProgramData\Sisense\application-logs\galaxy

For error messages related to OpenSSL when connecting to a host using StartTLS, use the following values:

"secure": false,

"ignoreTLS": false,

"requireTLS": false

This should avoid authentication protocol errors and your web server will automatically update the communication to StartTLS.

For general information on types of data relationships, potential complications and how to resolve them, see the following articles:

Data Relationships: Introduction to Many-to-Many

Data Relationships: Check Type of relationship

Data Relationships: One-to-Many Relationship

Data Relationships: The Invisible Many to Many (You are here)

Data Relationships: Many-to-Many Relationship Resolutions

The Invisible Many to Many

You may find instances where you have common symptoms of a many to many (M2M) relationship,but cannot find the root cause of the relationship. Such symptoms of a many to many relationship include:

High RAM usage during query,

ElastiCube size on disk increasing during query,

Build failing with 100\% RAM

The common check for a M2M is the below query.

Select count(a.id), distinct_count(a.id) From dim as a

However, the numbers may be identical which may not initially indicate a M2M so the below query must be run for a stronger check:

Select count(a.ID), distinct_count(a.ID),

sum(case when a.ID is null then 1 else 0 end) as [null count]

From dim as a

If the null count field shows more than 1 in both tables then you have a many-to-many relationship on null values.

Cause

There could be many reasons why null values get to the dimension tables, such as cases when the dimension table is a custom table that's taken from the fact table or if the data is imported with null values from the data source itself.

Proactive Solutions

Dimension tables should always be unique, even if they have null values, there shouldn't be more than one null value.

If the dimension table is a custom table that's taken from the fact table, make sure you use the "distinct" expression.

If the nulls are imported from the data source try to remove them either in the import query or by creating a custom table that's taking only unique values from the table.