Ophir_Buchman
Data Integration
Joined 10-19-2021
User Profile
User Widgets
Contributions
Choosing the Right Data Model
This post has become outdated. You can find guidance on choosing a data model on our documentation site here. https://docs.sisense.com/main/SisenseLinux/choosing-the-right-data-model.htm Introduction Customers often run into the question of which data model they should use (an ElastiCube, a Live model, or a Build-to-Destination). The following article presents some of the aspects you should consider when choosing between them. Sisense recommends that you discuss your needs and requirements with Sisense's technical team during the Jumpstart process, so the result will best meet your business expectations. Table of Contents Definitions The ElastiCube Data Model Importing data into an ElastiCube data model allows the customer to pull data from multiple data sources on-demand or at a scheduled time, and create a single source of truth inside Sisense. The imported data can then be transformed and aggregated to meet your business needs. Once imported, the data snapshot is used to generate analytical information. The process of importing the data, known as a "Build", includes the following steps: Extract the data: Query the different data source(s) for data. Load the data: Write the data extracted to Sisense (the local MonetDB). Transform the data: Transform the local MonetDB (using SQL queries). To read more about ElastiCubes, see Introducing ElastiCubes. The Live Data Model Using a Live data model does not require importing data. Only the data's schema needs to be defined. Once configured, analytical information required by the user is queried directly against the backend data source. To read more about Live models, see Introducing Live Models. Determining Factors Refresh Rate One of the most fundamental aspects of determining your data model is your data's refresh rate. The data refresh rate refers to the age of the data in your dashboards: For Live models, the data displayed on your dashboards is near-real-time, as every query is passed directly to the backend database. A good example of using a live model (due to refresh rate requirements) is a dashboard that shows stock prices. For ElastiCubes, the data displayed on your dashboard is current to the last successful build event. Every query is passed to the local database for execution. A good example of using an ElastiCube (due to refresh rate requirements) is a dashboard that shows historical stock prices. In this case, a daily ETL process will provide results that are good enough. To make a choice based on this factor, answer the following questions: How frequently do I need to pull new data from the database? Do all my widgets require the same data refresh frequency? How long does an entire ETL process take? Data Transformation Options The ETL process includes a "Transformation" phase. This transformation phase usually includes: Migrating the data tables into a dim-fact schema Enriching your data Pre-aggregating the data to meet your business needs The amount of data transformation on Sisense helps determine the suitable data model: For Live models, Sisense allows minimal to no data transformation. Data is not imported before a query is issued from the front end. Therefore, data cannot be pre-conditioned or pre-aggregated. Most data sources used by Live models are data warehouses that may perform all data preparations themselves. For ElastiCubes, data is imported before a query is issued from the front end. Therefore, it may be pre-conditioned and pre-aggregated. A user may customize the data model to optimally answer their business questions. To make a choice based on this factor, answer the following questions: Is my data in a fact-dim schema? Does my data require enriching or pre-conditioning? Can my data be pre-aggregated? Operational Database Load Your operational databases do more than serve your analytical system. Any application loading the operational databases should be closely examined: For Live models, Sisense will constantly query information from your operational databases, and feed it into your dashboard widgets. This occurs every time a user loads a dashboard. For ElastiCubes, Sisense highly stresses your operational databases during an ETL process while reading all tables. To make a choice based on this factor, answer the following questions: Does the analytical system stress my operational database(s)? Can the query load be avoided by using a "database replica"? Operational Database Availability Your operational database(s) availability is critical for collecting information for your analytical system. For Live models, all queries are redirected to your data sources. If the data source is not available, widgets will generate errors and not present any data. For ElastiCubes, data source availability is critical during the ETL process. If the data source is not available, the data in your widgets will always be available, but not necessarily be up to date. To make a choice based on this factor, answer the following questions: How frequently are analytical data sources offline? How critical is my analytical system? Is being offline (showing out-of-date information) acceptable? Additional Vendor Costs Various database vendors use a chargeback charging model, meaning that you will be charged by the amount of data you pull from the database or the computational power required to process your data. For Live models, every time a user loads a dashboard, each widget will trigger (at least) one database query. A combination of a chargeback charging model and a large user load may result in high costs. For ElastiCubes, every time the user triggers an ETL process, a large amount of data is queried from the database and loaded into Sisense. To make a choice based on this factor, answer the following questions: What is the number of users using my dashboards / What is my "build" frequency? Which data model will result in lower costs? What is the tipping point? Are you willing to pay more for real-time data? Database Size For ElastiCubes, please refer to these documents: Introducing ElastiCubes Minimum Requirements for Sisense in Linux Environments For Live models, there is no limitation as data is not imported to Sisense, only the data's schema. To make a choice based on this factor, answer the following questions: What is the amount of data I need in my data model? What is the amount of history I need to store? Can I reduce the amount of data (e.g., trimming historical data? reducing the number of columns? etc.) Query Performance Query performance depends on the underlying work required to fetch data and process it. Although every widget generates a query, the underlying data model will determine the work necessary to execute it. For ElastiCubes, every query is handled inside Sisense: The client-side widget sends a JAQL query to the Sisense analytical system. The query Is translated into SQL syntax, and run against an internal database. The query result is transformed back to JAQL syntax and returned to the client-side. For Live models, every query is forwarded to an external database and then processed internally: The client-side widget sends a JAQL query to the Sisense analytical system. The query Is translated into SQL syntax, and run against an external database. Sisense waits for the query to execute. Once returned, the query result is transformed back into JAQL syntax and returned to the client-side. To make a choice based on this factor, answer the following questions: How sensitive is the client to a delay in the query's result? When showing real-time data, is this extra latency acceptable? Connector Availability Sisense supports hundreds of data connectors (see Data Connectors). However, not all connectors are available for live data models. The reasoning behind this has to do with the connector's performance. A "slow connector" or one that requires a significant amount of processing may lead to a bad user experience when using Live models (that is, widgets take a long time to load): For ElastiCubes, Sisense allows the user to utilize all the data connectors. For Live models, Sisense limits the number of data connectors to a few high-performing ones (including most data warehouses and high-performing databases). To make a choice based on this factor, answer the following questions: Does my data source's connector support both data model types? Should I consider moving my data to a different data source to allow live connectivity? Caching Optimization Sisense optimizes performance by caching query results. In other words, query results are stored in memory for easier retrieval, in case they are re-executed. This ability provides a great benefit and improves the end-user experience: For ElastiCubes, Sisense recycles (caches) query results. For Live models, Sisense performs minimal caching to make sure data is near real-time. (Note that caching can be turned off upon request.) To make a choice based on this factor, answer the following questions: Do I want to leverage Sienese's query caching? How long do I want to cache data? Dashboard Design Limitations Specific formulas (such as Mode and Standard Deviation) and widget types (such as Box plots or Whisker plots) may result in "heavy" database queries: For Live models, Sisense limits the use of these functions and visualizations as the results of these formulas and visualizations may take a long time, causing a bad user experience. For ElastiCubes, Sisense allows the user to use them, as processing them is internal to Sisense. To make a choice based on this factor, answer the following questions: Do I need these functions and visualizations? Can I pre-aggregate the data and move these calculations to the data source instead of Sisense? See also Choosing a Data Strategy for Embedded Self-Service.Pivot 2.0 - Manipulating a Pivot Chart
Pivot 2.0 - Manipulating a Pivot Chart Introduction The following article discusses how to manipulate the data and styling of your Pivot 2.0 widget. Please refer to the following article first: https://sisense.dev/guides/customJs/jsApiRef/widgetClass/pivot2.html. Cell Identification To manipulate a pivot cell, we'll have to learn the different identifiers of each cell in the table. Cell Types Each cell is linked with a 'type' that represents the data it contains: A member cell refers to a Column/Row header A value cell refers to a table value cell A subtotal cell refers to a subtitle row (title + values) A grandtotal cell refers to a grand-total rows & columns (titles + values) A cell may have more than one 'type': A cell that has a subtotal and a member type represents the subtitle row title A cell that has a subtotal and a value type represents the subtitle row values (including values + column grand total values) A cell that has a grandtotal' and a member type represents the grand-total row and column titles A cell that has a grandtotal' and a value type represents the grand-total values (including row & column grand total values) A cell that has a grandtotal, a subtotal, and a value type represents the grand total values in the subtitle rows See the following pivot table and the corresponding cell types Original Table Cell Types Manipulating a Cell (Based on its Type) Here are two examples of how to manipulate a cell based on its type: widget.transformPivot( { type: ['value'] }, function (metadata, cell) { // Manuipulation code } ); widget.transformPivot( {}, function (metadata, cell) { if (metadata.type.includes('value')) { // Manuipulation code } } ); H2 - Cell Indexes Each cell is represented by three indexes: Metadata Index - Representing the logical column ID in the table (aligns with the selected rows/values/columns) Column Index - Representing the column number in the table Row Index - Representing the row number in the table Metadata Index See the following pivot table, the pivot configuration pane, and the corresponding metadata indexes: Original Table Metadata Index Here is an example of how to manipulate a cell based on its metadata index: widget.transformPivot( {}, function (metadata, cell) { if (metadata.index == 1) { // Manuipulation code } } ); Column/Row Index See the following pivot table and the corresponding row/column indexes Original Table Row/Column Indexes Here is an example of how to manipulate a cell based on its columns/row index: widget.transformPivot( {}, function (metadata, cell) { if (metadata.colIndex == 3 && metadata.rowIndex == 2) { // Manuipulation code } } ); Cell Row/Column/Measure Name Each cell may be affiliated with three metadata values: Measure - The measure calculated in this cell (name & formula) Column(s) - The column(s) this cell is under (field, title, & value) Row(s) - The rows(s) this cell belongs to (field, title, & value) Manipulating a Cell (Based on their Measure) Here is an example of how to manipulate a value cell based on the measure's name: widget.transformPivot( {}, function (metadata, cell) { if (metadata.measure.title === 'SUM') { // Manuipulation code } } ); Manipulating a Cell (Based on Their Row) Here is an example of how to manipulate a value cell based on the row's value: widget.transformPivot( {}, function (metadata, cell) { // Format based on the value of the a row's name and value metadata.rows.forEach(function(row) { if (row.title === 'Year' && row.member === '2012-01-01T00:00:00.000') { // Manuipulation code } }) } ); Manipulating a Cell (Based on Their Column) Here is an example of how to manipulate a value cell based on the column's value: widget.transformPivot( {}, function (metadata, cell) { // Format based on the value of the a row's name and value metadata.columns.forEach(function(column) { if (column.title === 'Online' && column.member === 'False') { // Manuipulation code } }) } ); Cell Manipulation The possible manipulation options of a cell include: value - Raw value of the cell from query response (manipulating this value is useless) content - The HTML contents of this cell style - The cell formatting Here is an example of how to manipulate value cells' style: widget.transformPivot( {}, function (metadata, cell) { cell.style = { backgroundColor : 'lightgray', fontSize : 14, fontWeight : 'bold', fontStyle : 'italic', textAlign : 'center', color : 'black', borderColor : 'black', borderWidth : '3px', minWidth : '150px', maxWidth : '200px' }; } ); Here is an example of how to manipulate value cells' value: widget.transformPivot( {}, function (metadata, cell) { if (cell.content == '') cell.content = '---' } ); Check out this related contentWidget Events - Figure out the events taking place in your dashboard
While creating your scripts you might run into situations where you'd like to analyze what events take place and determine how to best implement your scripts. The following scripts will allow you to do so by dumping the information into your console window (Developer Tools):Customizing a Pivot 1.0 Widget
Introduction The following document provides code snippets for customizing a Pivot 1.0 widget Table of Contents How to Use This Page? To implement the customizations on this page you’ll have to be familiar with the following: Basic JavaScript and JQuery Widget Lifecycle How to add a widget script Widget Customizations The easy way to reference cells is by referencing their class type or ID via JQuery: All headers are identified using unique class names The table contents (data cells) are referenced by a table object (identified using a special ID) The following section introduces the different table structures and classes used for pivot charts Layout #1 - Single Row, Single Value, No Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines a single value (Sum Revenue) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Total Revenue”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #2 - Single Row, Multiple Values, No Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines two values (Sum Revenue & Sum Quantity) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #3 - Multiple Rows, Single Value, No Columns This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines a value (Sum Revenue) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cell (“Total Revenue“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Total Revenue”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #4 - Multiple Rows, Multiple Values, No Columns This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines two values (Sum Revenue & Sum Quantity) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Value Label Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #5 - Single Row, Single Value, Single Column This layout includes the following configuration: The user has defined a single row (Months) The user defines a single value (Sum Revenue) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cell (“19-24” & “25-34“) A class of a <td> object td.p-measure-head “Grand Total Column Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #6 - Multiple Rows, Single Value, Single Column This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines a single value (Sum Revenue) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cells (“19-24” & “25-34“) A class of a <td> object td.p-measure-head “Grand Total Column Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #7 - Single Row, Multiple Values, Single Column This layout includes the following configuration: The user has defined a single row (Months) The user defines multiple values (Sum Revenue & Sum Quantity) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Column Label Column Header” cells (“19-24” & “25-34“) A class of a <td> object td.p-dim-member-head Individual “Column Value Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-measure-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #8 - Multiple Rows, Multiple Values, Single Column This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines multiple values (Sum Revenue & Sum Quantity) The user defines a single column (Age Range) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Column Label Column Header” cells (“19-24” & “25-34“) A class of a <td> object td.p-dim-member-head Individual “Column Value Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-measure-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month and quarter names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cells (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #9 - Single Row, Single Value, Multiple Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines a single value (Sum Revenue) The user defines multiple columns (Age Range & Brand ID) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) A class of a <td> object td.p-dim-member-head Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) A class of a <td> object td.p-total-head Global “Grand Total Column Header” cell (“Grand Total“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #10 - Single Row, Multiple Values, Multiple Columns This layout includes the following configuration: The user has defined a single row (Months) The user defines multiple values (Sum Revenue & Sum Quantity) The user defines multiple columns (Age Range & Brand ID) Participating classes are as follows: Cell Category Object Reference “Row Label Column Header” cell (“Months in Date”) A class of a <td> object td.p-dim-head “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) A class of a <td> object td.p-dim-member-head “Value Label Column Header” cell (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-measure-head Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) & (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-total-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cell (month names) A class of a <td> object td.p-dim-member “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Layout #11 - Multiple Rows, Single Value, Multiple Columns This layout includes the following configuration: The user has defined multiple rows (Quarters & Months) The user defines a single value (Sum Revenue) The user defines multiple columns (Age Range & Brand ID) Participating classes are as follows: Cell Category Object Reference “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) A class of a <td> object td.p-fake-dim-head “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) A class of a <td> object td.p-dim-head “Fake Value Label Column Header” cell (“Total Revenue”) A class of a <td> object td.p-fake-measure-head “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) A class of a <td> object td.p-dim-member-head Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) A class of a <td> object td.p-total-head Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) A class of a <td> object td.p-grand-total-head “Phantom Column Header” cell (fake header column at the right-side) A class of a <td> object td.phantom “Row Label Row Headers” cells (quarter and month names) A class of a <td> object td.p-dim-member “Sub-Total Row Header” cells (“2009 Q4 Total”, “2010 Q1 Total”, etc.) A class of a <td> object td.p-total-row-head “Grand Total Row Header” cell (“Grand Total”) A class of a <td> object td.p-grand-total-row-head Data cells (holding the revenue values) An object with an ID #pivot_ Manipulating the Table Structure Table Alignment The table is aligned to the left-hand side of the screen by default. To align the table to the right-hand or the left-hand sides we’ll first have to get rid of the “fake” phantom column. Before After To align the table location use the following snippet: // This script aligns the table to the center of the widget function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Remove the phantom column and cells $('td.phantom',element).remove(); // Center the table doc = getWidgetDocumentObject(widget); doc.querySelector('.p-container').style.display='block'; doc.querySelector('.p-container').style.marginLeft='auto'; doc.querySelector('.p-container').style.marginRight='auto'; doc.querySelector('.p-container').style.width='fit-content'; }); Manipulating the Header Cells Aligning Text To align headers' cell text alignment use the following snippet: (note - Change the JQuery element name to align with the correct header you wish to format) // This script modifies the “Row Label Column Header” cells' alignment widget.on('domready', function(widget) { $('td.p-dim-head',element).css('text-align','center'); // Horizontal $('td.p-dim-head',element).css('vertical-align','middle'); // Vertical }); Changing the Caption of the “Grand Total” Header To replace the default text of “Grand Total” use the following snippet: // This script replaces the header of the "Grand Total" row header to "Total" function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { doc.querySelector('td.p-grand-total-row-head').querySelector('div.p-head-content').innerText = "Total" }); Manipulating the Data Cells Aligning Text To align all data cells' text alignment use the following snippet: // This script modifies the data cells' alignment widget.on('domready', function(widget) { $('td.p-value',element).css('text-align','center'); // Horizontal $('td.p-value',element).css('vertical-align','middle'); // Vertical }); Replacing the Contents of (All) Empty Cells To replace the blank space in the empty cells using the following snippet: // This script modifies the contents of blank data cells to '-' // Note - A blank cell contents are the character '\xa0' function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Replace blank cells doc.querySelectorAll('div.p-value').forEach((element) => { if (typeof element === "object" && element.innerText == '\xa0') element.innerText = '-' }); }); Modifying the Contents of Cells From Specific Columns Color Based on "Top-Level Header" Cells The term “Top-Level Header Cells” refers to one of the following: If the table has no columns - “Value” headers If the table has one column - “Column” headers If the table has multiple columns - Not covered The following snippet colors the cells under a Top-Level Header named 'No': // This script colors the cells under a Top-Level Header called 'No' function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Define the column title columnToColorValue = 'No' widgetDoc = getWidgetDocumentObject(widget); // Calculate the amount of columns in the table dimColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-head').length; // Calculate the amount of "Values" in every "Column" measureColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-measure-head').length; // Scan all columns in the header row for (let i = 0; i < widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head').length; i++) { // Retrieve the <i>'th column in the table col = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head')[i]; // Check if column should be colored red shouldColorRed = (col.innerText === columnToColorValue); // Check the column's width (as it might contain multiple sub-columns (values) underneath it columnWidth = col.getAttribute('colspan'); if (columnWidth === null) columnWidth = 1; // If it contains a single column there's no colspan attribute // For every value column for (let j = 0; j < columnWidth; j++) { // Calculate offset from end of table (this value changes in different rows) colOffset = measureColumns - ((i * columnWidth) + j); // Extract the table structure rows = getWidgetDocumentObject(widget).querySelector('#pivot_').rows; // Iterate through all data cell rows for(let r = 1; r < rows.length; r++) { cells = rows[r].cells; rowCellCount = Object.values(cells).length; cellToHandle = rowCellCount - colOffset; if (cells[cellToHandle].getElementsByClassName('p-value').length > 0) { cellToManipulate = cells[cellToHandle].querySelector('.p-value'); if (shouldColorRed) cellToManipulate.style.color = 'red' } } } } }); Alter Text Based on "Bottom-Level Header" Cells The term “Bottom-Level Header Cells” refers to one of the following: If the table has one column - “Value” headers (applies to the “Value” column in each “Column”) If the table has multiple columns - Not covered The following snippet alters the text of cells under a Bottom-Level Header named 'No': This script alters the text of cells under a Top-Level Header called 'Average Cost' (changes empty cells to '---'): // This script alters the text of cells under a Bottom-Level Header called 'Average Cost' // Note - A blank cell contents are the character '\xa0' function getWidgetDocumentObject(widget) { // This function determines the document object context (different when in a widget-view or a dashboard-view mode) if (prism.activeWidget == null) return document.querySelector('[widgetid="' + widget.oid + '"]'); else return document; } widget.on('domready', function(widget) { // Define the column title, old text, and new text columnToFixText = 'Average Cost' originalText = '\xa0' newText = '---' widgetDoc = getWidgetDocumentObject(widget); // Calculate the amount of columns in the table dimColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-head').length; // Calculate the amount of "Values" in every "Column" measureColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-measure-head').length; // Scan all columns in the header row for (let i = 0; i < widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head').length; i++) { // Retrieve the <i>'th column in the table col = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head')[i]; // Check the column's width (as it might contain multiple sub-columns (values) underneath it columnWidth = col.getAttribute('colspan'); if (columnWidth === null) columnWidth = 1; // If it contains a single column there's no colspan attribute // console.log('found column ' + i + ' width = ' + columnWidth + ' that fits the condition'); // For every "Value" for (let j = 0; j < columnWidth; j++) { // Calculate offset from end of table (this value changes in different rows) colOffset = measureColumns - ((i * columnWidth) + j); // Extract the table structure rows = widgetDoc.querySelector('#pivot_').rows; // Iterate through all rows shouldFixText = false; for(let r = 1; r < rows.length; r++) { cells = rows[r].cells; rowCellCount = Object.values(cells).length; cellToHandle = rowCellCount - colOffset; if (cells[cellToHandle].getElementsByClassName('p-head-content').length > 0 && cells[cellToHandle].querySelector('.p-head-content').innerText === columnToFixText) shouldFixText = true; if (cells[cellToHandle].getElementsByClassName('p-value').length > 0) { cellToManipulate = cells[cellToHandle].querySelector('.p-value'); if (shouldMakeBold) cellToManipulate.style.fontWeight = 'bold'; if (shouldFixText && cellToManipulate.innerText === originalText) cellToManipulate.innerText = newText; } } } } });Assessing the Quality of Your Dashboard
Introduction The following article discusses how to assess the quality and adoption of your dashboard. Table of Contents How to Measure a Dashboard's Quality? Let's begin by defining what a "Good Dashboard" is. To assess the quality of your dashboard, you should measure the following aspects: Is the dashboard practical? Does it serve its purpose? Does the dashboard display relevant and correct information? Is the information in the dashboard displayed correctly? Was the dashboard adopted by the end-users? Is the dashboard intuitive for use? Planning Makes Perfect Correct dashboard planning is the key to its success! I recommend you read this article discussing a dashboard's (high-level) development cycle. It breaks the process into easy measurable steps that start from the initial KPI planning to maintaining and adjusting your end product. Reading through it might help identify flaws during the dashboard's initial rollout process. Assessing a Dashboard's Quality The "Practical" Test The practicality of a dashboard focuses on whether the dashboard serves its purpose. Going back to a dashboard's planning phase, try to recall the reason for building this dashboard. What were the end-users looking to achieve? What insight were they seeking? Possible answers are: Monitoring a person's or a group's achievements Monitoring a measurable process Help decide what to do next based on historical information Assess future steps in various scenarios (What-If Analysis) A good dashboard: Answers its purpose! How to Test This? To check whether your dashboard is practical, you should: Set up a call with one (or more) end-users and/or stakeholders using the dashboard Ask them whether they can make the intended decision based on it If they are - What is the process of making the decision? If they aren't - What are the obstacles that stand in the way of making it? (e.g., missing/incorrect data, bad user experience, etc.) Next, revisit the dashboard's goals: What question should it answer? What action can the users make based on it? Compare the information you got from your stakeholders and the plan made when designing the dashboard - Define the gaps. How to Resolve This Type of Issue? Work towards re-planning your dashboard to meet its goals. Focus on: Aligning the "Call for Action" Redefining the KPIs and the user story Redesigning and restructuring the KPIs Optimizing/adjusting the data model to be able to answer the different KPIs Resolving this type of issue will most likely require a complete dashboard redesign, followed by an entire UAT cycle. The "Data Relevance" Test The term "Data Relevance" breaks into two aspects: Am I displaying relevant data - Considering the audience of the dashboard? Am I displaying relevant data - Considering the decision to be made? Does It Address the Right Crowd? Like any deliverable, one of the fundamental things to know is its audience. The answer to this question will affect the following: The type of dashboard you create The granularity of data you present The way information is presented A good dashboard: Is built with the right audience in mind. Does it Contain the Right Amount of Data? Making a decision requires having the right amount of data: Too little data - Could prevent the person from making the right call. Too much data - Could confuse the person and throw them off-track. A good dashboard: Has the right amount of data required to serve its purpose. How to Test This? To check whether your dashboard has relevant data, you'll have to: Assess who are the end-users and/or stakeholders using this dashboard Set up a call with one (or more) end-users and/or stakeholders using it Ask them what the process of making their decision is? Check whether the information they require is presented in the dashboard. Check whether the dashboard has redundant or irrelevant data. How to Resolve This Type of Issue? Incorrect Dashboard Type Check your dashboard's type - Based on the person using this dashboard, should it be operational, analytical, tactical, or strategic? Compare the dashboard type to the intended crowd to determine if it was designed correctly (for example, a "Tactical Dashboard" is aimed towards upper management and will usually present long-term KPIs and high-level metrics). Resolving this type of issue will most likely require a complete dashboard redesign, followed by an entire UAT cycle. Missing/Redundant Information If you've identified that certain information is redundant or missing, consider adding, modifying, or removing widgets from the dashboard. Doing so might be a simple task (especially when removing data). However, it might also end up as a more extensive project requiring a partial redesign of the dashboard and a partial/complete UAT cycle. The "Data Correctness" Test Presenting inaccurate or outdated data is worse than displaying partial or excessive data. Two possible outcomes of stakeholders, basing their decisions on incorrect data, are: Experiencing negative events (such as revenue loss) Compromising the trust relationship between Sisense and the end-users Frequent causes for showing outdated data in your dashboard include: An ETL process that is not run frequently enough An ETL process that often/occasionally fails An ETL process using an incorrect table update behavior (e.g., "Accumulative" where it should be "Full") An ETL process that isn't synchronized with your Data Warehouse A source database being offline for an extended period Frequent causes for showing incorrect data in your dashboard: Wrong data modeling leading to unexpected Many-to-Many relationships Business questions that don't align with the data model (e.g., causing "Random Paths") Unexpected / Missing inheritance of filters Wrong data security configuration The use of multiple data models (in the same dashboard) built at different schedules A good dashboard: Displays precise and up-to-date data How to Test This? To check whether your dashboard has correct data, you'll have to: Assess the different widgets on the dashboard to see the granularity of data displayed For each widget, check what data model it relies on - Use the Usage Analytics "Usage - Builds" dashboard to monitor the behavior of historical builds. Set up a call with one (or more) end-users and/or stakeholders using this dashboard Ask them if they trust the data on the dashboard and if the data refresh frequency is sufficient. How to Resolve This Type of Issue? Showing Outdated Data Ask your stakeholder how frequently they expect the data to be refreshed. Perform the following actions: Check the data model's build frequency - Should the build frequency be modified? Check the build time - Should the data model be optimized? Is the data model too heavy? Check the build success rate - Why are builds failing? Is the system running low on resources? Should "Data Groups" be applied? If using a Data Warehouse (DWH) and an Elasticube - Check the DWH build frequency; Check the synchronization between the DWH ETL finish and the Sisense ETL beginning. If you can't achieve the required build frequency, check whether all widgets require the same data refresh rate - Can a "Live Model" or a "Hybrid Dashboard" be considered? Showing Incorrect Information Ask a stakeholder to generate a report with correct data or ask them to point you to "defective widgets." Perform the following actions: Check the formula behind the figure(s) showing the incorrect data and correct them. Consider the different filters, inheritance behavior, etc. Examine the query path used to calculate each figure (look out for unexpected "Many-to-Many relationships" or "Random Paths") - Use the "Visualize Queries" add-on (Link) to compare Sisense behavior against the expected query path. Run the calculation in the data model using a custom table and a SQL statement that emulates the dashboard's calculation. The "Visual Correctness" Test Data alone isn't enough; it must be visualized correctly. Visualizing data requires using the correct widget type and visual aids to convey the message. For example, say a KPI is showing the company's revenue. See the different visualization options below: Option #1 Option #2 Option #3 Transitioning from option #1 to #2 provides an added value of "Good" / "Bad" Transitioning from option #2 to #3 provides an added value of revenue ranges A good dashboard: Has widgets that are visualized correctly and convey a clear message. How to Test This? To check whether your widgets are visualized correctly, you'll have to analyze each widget individually: Categorize each widget to find out its aim (e.g., show a single figure, compare values, show behavior over time, visualize data to show distribution, etc.) Verify the visualization matches the widget type (e.g., An indicator widget is perfect for displaying a single figure, a line chart is ideal for showing a behavior over time, etc.) Make sure each widget has visual aids (such as conditional formatting) to convey its message clearly - Each widget should tell a small puzzle of the story (which the dashboard should put together to a larger picture) Set up a call with people who are not familiar with the dashboard Ask them to describe each widget (separately) and their conclusion from looking at it. How to Resolve This Type of Issue? Wrong Widget Type Resolve the issue by fixing the visualization type - Use the following chart to help out: Type Use Case Indicator Show a single figure (numerical) Show a single figure and a gauge representing its range Column Chart Show a comparison among different sets of data Track data sets over time Track individual values + Their sum (stacking) Bar Chart Compare many items Track individual values + Their sum (stacking) Line Chart Reveal trends, progress, or changes that occur over time The data set is continuous rather than full of starts and stops Area Chart Displaying absolute or relative (stacked) values over a time period Analyzing a Part-to-whole Relationship Pie Chart One static number, divided into categories that constitute Represent numerical amounts in percentages Table Display RAW granular data Pivot Display RAW granular data Display aggregative data in a table format Scatter Plot Comparing large numbers of data points without regard to time Identify a potential relationship between two variables Calendar Heatmap Show relative number of events for each day in a calendar view Missing Visual Aids Visual aids help the end-users identify "Good" or "Bad" values. Possible visual aids include: Adding colors to numerical labels (e.g., Green figure vs. a red one) Adding background colors to table cells (e.g., Color negative cells red) Adding a line chart representing a threshold (e.g., A red line to indicate a lower threshold) Converting numerical values with text (e.g., Showing a "Revenue increased" label rather than a positive figure) Adding trend lines Adding a forecast range The "Intuitiveness" Test Dashboard intuitiveness refers to the ability of a non-technical person to: Access the dashboard Understand and conclude the dashboard Understand how to customize the dashboard (using filters, drilling, etc.) Easy Access to Data There are two methods of accessing a dashboard: Sisense offers a complete HTML5 platform (a.k.a. Sisense Web Application) that can be white-labeled and customized to meet the company's look & feel (i.e., color pallet, logo, links to internal support and documentation pages, etc.). Sisense offers three different embedding deployment options (conventional iFrames, an embedding SDK, and a complete JS-based embedding solution). Embedding the dashboard (or individual widgets) allows infusing analytics into web pages and applications. Choosing the proper access method will affect how end-users access data and benefit the BI solution. To improve intuitiveness - Make sure to streamline the process of consuming data as much as possible. The need to switch between multiple applications will result in a lack of efficiency, a complex adoption, or even a lack of adoption. Ease of Comprehension The comprehension of individual widgets was discussed earlier. However, is "the whole" greater than the sum of the parts? Does the dashboard tell a story? An excellent example for a dashboard: Widget #1 shows the revenue is low. Widget #2 shows a revenue breakdown per department and points out one department losing money. Widget #3 provides an income/expanse category breakdown and points out non-proportional marketing expenses. Widget #4 provides a detailed transactional income/expanse breakdown and points out the individual expenses Customizability and Interactivity Stale reports tell one story. However, a person looking at the dashboard may want to filter, sort, and pivot the data to tell the same story about a specific segment or individual. The tools for customizing/interacting with a dashboard include: Filtering data to a specific segment of interest (Link) Drilling into a measure to be able to extract more information about it (Link) Adding explanations (Link) and narratives (Link) A good dashboard: Is easy to access, self-explanatory, easy to customize, and play around with. How to Test This? To check whether your dashboard is interactive, you'll have to: Set up a call with one (or more) end-users and/or stakeholders using it Ask them when they use this dashboard and their workflow of consuming its information (e.g., A customer requires the data when building a financial report, and his workflow includes opening another browser tab and logging into the Sisense Web Application). Open the dashboard and ask them to explain it. Try to identify widgets that were over-explained and widgets they skipped. Track the questions you have to ask to understand what you see. Track the amount of "mouse scrolling" they perform when explaining the dashboard flow. Ask them what interaction they have with their dashboard and lay out the tools to increase interaction. Note the tools they are interested in (e.g., extra filters, adding narratives to widgets, etc.) How to Resolve This Type of Issue? Data is Hard to Access Ease of access is easy to measure - Count the number of clicks the user has to make when they want to consume the data in this dashboard - Fewer clicks = Easier to access. If data is hard to access or requires too much "Clicking around": Integrate SSO or WAT to prevent the user from logging in to the Sisense Web Application Embed the dashboard (or a single widget) into the application/webpage Have additional data imported to Sienese - Allowing the user to have a "One Stop Shop" for their entire workflow Enable sending periodic reports to the user's email Enable NLQ to allow the user to interact with Sisense easily (Link) Infuse data into G-Suite applications (Link) Enable Pulse alerts to send push notifications to the user (Link) Data is Hard to Comprehend If your widgets tell the right story, but the complete picture doesn't make sense: Move widgets around to make the story clearer Add visual separators between certain widgets (requires scripting) Reduce the number of widgets by splitting your dashboard into multiple stand-alone dashboards Use add-ons to simplify dashboard navigation (e.g., Accordion) Bring in a UI/UX designer to help visualize correctly Redesign the dashboard to avoid scrolling the mouse The Dashboard isn't Customizable / Interactive Make the dashboard more customizable by allowing the user to: Filter data based on predefined filters Define hierarchies to make filtering more intuitive Integrate filtering abilities into the dashboard (e.g., BloX buttons, drilling options, clickable widget values) Add additional widgets (e.g., Accordion, Switchable Dimensions, Tabber, etc.) Add premium widgets (e.g., Advanced Input Parameters - Link) The "Adoption" Test So you've created a great dashboard; it has all the correct data and visualizations, a person can access it quickly and draw the proper conclusion by just looking at it, but it wasn't adopted. Has the company done enough to drive its adoption (e.g., sufficient training, decommissioning the old dashboard, integrating it into the users' application, etc.)? A good dashboard: Is measured by its adoption and usage. How to Test This? To check whether your dashboard has correct data, you'll have to: Find out who this dashboard's audience is Use the Usage Analytics "Usage - Dashboards" dashboard to monitor who accesses this dashboard and how often. Set up a call with one (or more) end-users and/or stakeholders who are not using this dashboard Find out why this dashboard isn't being used. Find out what alternative ways they use to collect the data How to Resolve This Type of Issue? Once you're sure the dashboard is perfect and the only thing missing is adoption: Involve users in the design meetings and UAT loop to make them feel they are part of the process Create an adoption plan! Get executives to buy-in - Causing them to promote their usage Monitor what people are using the dashboard and which aren't - Target the right people Reeducate your end-users on the benefits of the dashboard and the value of using it Decommission old dashboards and reports Refresh your dashboards from time to time (design, contents, etc.)
