Maximize Dashboard Performance
What is good dashboard performance? Dashboard performance is the time it takes to load a Sisense dashboard. We want our users to have a good experience when working with our data. The time taken to load the dashboard is the first step. Each individual widget in a dashboard is a separate query. The dashboard needs to send these queries through the Microsoft IIS web server to the ElastiCube, pull back the results and populate the data visualizations. The questions we will address in this post are what is a reasonable dashboard load time and how can we enhance performance. So, what is good dashboard performance? This is a slightly subjective question. Users always want the fastest load time. However, we must think about load time relative to the data we are processing. If the dashboard is aggregating billions of rows of data, it is unrealistic to expect two-second load times. The converse is true as well — if a dashboard is based on a few thousand rows of data, the dashboard should not take five minutes to load. The goal, to put it simply, is to optimize the load time relative to the amount of data the dashboard is processing. Sisense’s architecture is designed to return queries very quickly, and there are ways to help it. How to monitor performance Timing First and foremost, how long does the dashboard take to load after the user navigates to the URL? Does the load time inhibit the user experience? As discussed before, the answer will vary by the use case, ElastiCube size, and end user. However, as a general guideline we want the load time to be under a minute if the dashboard is not crunching enormous amounts of data. System Resources Monitoring system resources during dashboard load is the first step to identifying possible issues. These can be monitored live by opening the Task Manager > Performance tab. RAM and CPU should not be maxed out during the dashboard load. If they are, it could be hindering the process. More system resources might be necessary (see here for Sisense's minimum hardware guidelines). Components ElastiCube.exe – if this process is taking CPU and RAM then the query is too heavy. This could be a possible many to many occurrence (covered below) ElastiCubeManagement Service – if this process is taking CPU and RAM, you most likely have a build occurring at the same time Java(TM) Platform SE Binary - This process transmits Sisense monitoring data. Heavy use of hardware is expected when restarting the service after a while (as it catches up on transmitting data), but if you experience continued consumption, confirm you are using the latest version of the Sisense Monitor Node.js - If this process is heavily consuming resources, check whether you have recently published dashboards to a large number of users. w3wp / iisexpress – if this process is taking CPU and RAM you may be overloading your IIS server. This is mostly likely when presenting too much data (covered below) Another way of monitoring system resources is by using the Windows Performance monitor. This will help analyze system resources over time. The post here should help: https://learn.microsoft.com/en-us/windows-server/identity/ad-fs/deployment/configure-performance-monitoring Timing vs other dashboards Using other dashboards that use similar data sets as a baseline can also be useful. For example, two dashboards are using the same ElastiCube. Dashboard-A takes 30 seconds to load. Dashboard-B takes five minutes. Knowing that Dashboard-A loads normally, we can tell that Dashboard B has some kind of an issue. Possible issues could be a bad join, many-to-many relationship, or specific troublesome widget that Dashboard A did not have. Compare the differences to narrow down possible issues. Web Developer Console Developer Tools within your web browser allow us to track each dashboard component's load time. In Chrome before loading the dashboard, open the developer console (F12 in Chrome). Navigate to the Network tab. From there you can see the timeline and the long-running requests. Most likely it will be the JAQL queries that take the longest. You can then click those records for more information. Under Headers>Request Payload there is information about the widget ID and Name. This will help identify the long-running queries. Under Timing, you can view what composed the query response time. Stalled represents one query waiting for other queries to finish (there is a limit to the number of concurrent queries your web browser can run). Waiting (TTFB) represents time waiting for the ElastiCube to respond. Content Download represents the amount of time retrieving the query results. If the JAQL queries are not the longest running components, you may have connectivity issues to your server, or are using too much Javascript (covered below). Causes of slowness System Resources A reasonable query time depends on a few factors. First and foremost, system resources. The recommended system resources for Sisense can be found here: https://docs.sisense.com/win/SisenseWin/windows-minimum-requirements.htm. Amount of data The amount of data crunched/returned in a query has an impact on performance. Logically, this makes sense. Crunching one million rows will, of course, return faster than one billion rows. The way to affect this is by adjusting filters to limit the data returned. Furthermore, limiting the amount of data in the ElastiCube is useful. This can be done by editing either the tables that Sisense is connected to, or by editing the SQL statement that is used to query the original data source. Schema Design Schema design is a very important factor in query performance. Each data connection within the ElastiCube will become an inner join when the dashboard queries this data. Joins are costly when it comes to query time. When designing the schema for performance, the ElastiCube designer should de-normalize the schema as much as possible. De-normalization means having a fewer amount of joins, and including redundant information in one table. For more on schema de-normalization, see here. Custom Calculations in the Dashboard Custom calculations are often useful when manipulating data to answer business questions. They allow users to aggregate data in various ways to present visually. However, they do take time to process. Each custom calculation is a separate query that is processed by Sisense. To enhance query performance we can cut down the number of queries generated by the dashboard. This is done by moving some calculations to the ElastiCube, thus moving that process time to the build time instead of dashboard load. The best example is a row calculation, such as the addition of two separate columns — FieldA + Field B. This type of calculation can be easily added to a table using the same exact formula as a custom field. If it is from a different table, we can use the LOOKUP function in the ElastiCube Manager. Presenting too much data Presenting large amounts of data is costly on dashboard load time as well. More specifically this causes slowness when using a pivot or table widget with many rows. The web server has to process the large amount of data and store it to allow the tool to present it visually. If it is possible, limit the number of dimensions that break the data to such a granular form. Sisense generally recommends 6-8 widgets per dashboard for optimal performance. Each widget incurs incremental query and rendering time. Sisense's official add-ons such as Accordion, Jump to Dashboard, and Switchable Dimensions may be leveraged to reduce the number of widgets while preserving the amount of data accessible to end users. When an end user loads the first dashboard in a session, performance will be slower against subsequent dashboard loads, as common web application elements such as logos, toolbars, and code templates are loaded for the first time. Later navigation between dashboards once these resources are cached will be faster. Other causes of query slowness and slow dashboard performance Many to Many If RAM grows exponentially, and the dashboard takes a very long time to load, you could have a possible many to many relationship. A many to many relationship (M2M) can cause extreme slowness in dashboard load time. A M2M is a relationship in the ElastiCube where neither side of the relationship is unique. This creates a Cartesian product, thus consuming all of the machine's resources. Relationship Cycle A relationship cycle exists when there are multiple join paths between two tables in a data model. Sisense will generally opt for the shortest path, but depending on the dimensions used (in widgets or filters), Sisense can take join paths through tables that are unexpected to dashboard designers. While not as deleterious to performance, an additional join on a large table can reduce performance and possibly lead to unexpected results. For more information on relationship cycles in data modeling, please visit the Handling Relationship Cycles documentation page. For the detection of data model issues such as many-to-many relationships and cycles, we recommend using the official Visualize Queries plugin. Data Security If some users are seeing slow performance but others are not, it is worthwhile to check the data security of the user in question. Some questions to consider: Are there complex queries occurring on the ElastiCube due to competing data security settings? Does the data brought in by data security cause pivot tables or other widgets to display a larger amount of data than expected? Please reference the official documentation on data security here. Browser Compatibility If some users are seeing slower dashboard performance on certain machines or browsers, check to make sure they are using a support browser outlined in the Minimum Requirements and Supported Platforms document.Maintain Percentage Contribution Using ALL Function
Analytical Need Even though this requirement sounds fairly simple, a lot of times, this use case gets confusing. If there are 10 stores for a Retail Chain, sometimes the Management would only want to compare 3 stores and split the entire contribution within the 3 retail stores. But there would be instances where the business would want to see the numbers of 3 selected stores but at the same time, would want to see the contribution of each store to the overall sales figure (sum of 10 stores). In the case of the latter, the selected three stores will be filtered, but the contribution percentage should not be altered only for the 3 stores, but it should be calculated with respect to all the stores of the Retail Chain. Mathematically, the denominator should be the same number (Total Sales across 10 stores) before and after selecting the 3 specific stores. Modeling Challenge The first case where the contribution percentage gets altered based on the filtered values, we could make use of the ‘Contribution’ function and achieve the same. Contribution function could be used from the Functions tab or by simply using the ‘Quick Functions’ of the Value menu. (or) But to achieve the latter use case, we cannot make use of the ‘CONTRIBUTION’ function but try working this out mathematically. Solution MATHEMATICAL SOLUTION: Irrespective of the filter applied on the Store, the percentage contribution should not change. So, Percentage Contribution of Store A = Total Sales $ of Store A / (Total Sales of All Stores), Percentage Contribution of Store B = Total Sales $ of Store B / (Total Sales of All Stores), and so on… To maintain the percentage contribution unchanged when filtering out few members, we need to have the denominator constant. SISENSE SOLUTION: We make use of the ALL function to create the denominator as mentioned in the documentation. This way, the widget filter to pick specific stores will not affect the percentage contribution value. So, depending on what the use case is, we could choose which formula needs to be utilized.Advanced Dynamic Filtering
Get quick, comparative insights from large datasets by creating flexible pivot tables or widgets. Whether comparing time periods, countries, or sales figures, you can leverage a single filter to handle multiple comparisons without clutter. This method uses custom fields, measured values, ranking filters, and the smart label plugin to streamline comparisons across various dimensions for clean, dynamic visualizations.Ordering My Pie Chart From Largest Percentage To Smallest
Modeling Need: My pie chart is ordering the data in alphabetical order by category. I am looking to order the data from largest to smallest percentage. Solution: I can change the widget type from pie chart to column chart. Then order the values in descending order: Then change the chart type back to pie chart to see the representation I am looking for:Compare A Time Window Against The Immediately Preceding Time Window Of The Same Size.
Users could sometimes want to compare performance on a particular time period vs the immediately preceding period. Sometimes those periods are not of a common size like months or quarters. It could be a campaign that lasts a few days, or an arbitrary date range based on topics trending on social media. In those cases, you would want to select any time range and be able to compare to a previous period of the same arbitrary size, and be able to change that time range and see the comparison adjust dynamically. Challenges In Sisense, the RANGE function receives literal numbers, but it doesn’t accept numeric expressions (like Count([Days in Date]). Sisense has last, next and offset operators for filters, but no out of the box ways to adjust them according to the number of members in a filter. Solution: By using the Javascript API and in formula filters, we can make a formula go back the number of days in the filter, and also apply an offset of the same number of days, to get the same data for the preceding time window. Example: We want to be able to get the number of orders in an N day date range, and the growth vs the previous N days. For example, if the filter is 2/11/2020 to 2/20/2020, we want the growth vs the 2/1/2020 to 2/10/2020 period. We'll use an indicator widget for this, with the next formulas: Value: Count([OrderID) Secondary: (Count([OrderID]) - (Count([OrderID]),[Days in Date]))/(Count([OrderID]),[Days in Date]) And set the formula filters to one of the predefined “Last N” options: To learn about filtering in formulas, check the documentation: Document Here Finally, open the Edit Script editor for the widget, and enter the following script. The script will search for formula filters that use the table and column specified at the beginning of the script (and use the last operator), and replace the count and offset with the number of members in the filter. If the calendar was used to set a days filter, it will calculate the difference in days to come up with the number of members: If you change the filter to a different date range, the filters in the growth formula will adjust dynamically to the new time window. Script: var TABLE = "Date"; var COLUMN = "Date"; widget.on("beforequery", function(scope, widget) { // getting item var item = widget.query.metadata.find(function(i) { return i.panel === "scope" && $$get(i, "jaql.table") === TABLE && $$get(i, "jaql.column") === COLUMN }); if (!defined(item)) { console.log("DynamicLastX: Can't find a filter item with '" + TABLE + "." + COLUMN +"' dimension. Aborting."); return; } var calFilter = false; var calCount = 0; var dateTo = null; // getting member var members = $$get(item, "jaql.filter.members"); if (!defined(members)) { console.log("DynamicLastX: No item filter. Assuming calendar was used."); calFilter=true; var dateFrom = new Date(item.jaql.filter.from + "T00:00:00"); var dateTo = new Date(item.jaql.filter.to + "T00:00:00"); var diffTime = dateTo.getTime() - dateFrom.getTime(); calCount = diffTime / (1000 * 3600 * 24) + 1; } var formulaFilters = []; var filterMember = 0 var filterCount = 0 if (!calFilter) { filterMember = members[0]; filterCount = members.length } else { filterMember = item.jaql.filter.to + "T00:00:00"; filterCount = calCount; } widget.query.metadata.forEach(function(item) { if ((item.source === 'value' || item.source === 'secondary') && item.jaql && item.jaql.context) { for (var lmnt in item.jaql.context) { if (item.jaql.context[lmnt].table === TABLE && item.jaql.context[lmnt].column === COLUMN && item.jaql.context[lmnt].filter && item.jaql.context[lmnt].filter.last) { formulaFilters.push(item.jaql.context[lmnt].filter); } } } }) if (formulaFilters && filterMember) { formulaFilters.forEach(formulaFilter => { formulaFilter.last = { offset: filterCount, count: filterCount, anchor: filterMember }; }); } });Creating 'From-To' Date using BloX
Analytical Need Attached below is a BloX template. A common request is to have a 'From To' input at the top of the dashboard to affect a calendar range selection of dates. To achieve this need we can utilize BloX's form capabilities. Picking a date range in a calendar of a 'Date' filter and looking into the jaql created by it shows the following: We can use input fields to change the date range values selected in the from:to keys. Solution We will use the skeleton that creates an action that affects the selected filters: Make sure to change the "filterName" to the right filter column name. Also, make sure to use the right jaql that needs to be populated. Example: { "type": "ActionSet", "actions": [ { "type": "Filters", "title": "Submit", "data": { "filters": [ { "filterName": "Days in Date", "filterJaql": { "from": "", "to": "", "custom": true } } ] } } ] } Then, make sure to create the required Input field to pass on the input value and populate the from/to: { "type": "Input.Date", "id": "data.filters[0].filterJaql.from", "placeholder": "yyyy-mm-dd", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" } The "id" should hold the entire path of the key that needs to be populated with the value. If you prefer you can use this JSON template file for the full script: json script { "card": { "style": "", "script": "", "title": "", "showCarousel": true, "body": [ { "type": "Container", "width": "90%", "style": { "margin": "0 auto" }, "items": [ { "spacing": "large", "type": "TextBlock", "text": "From Date", "weight": "light", "color": "black" }, { "type": "Input.Date", "id": "data.filters[0].filterJaql.from", "placeholder": "yyyy-mm-dd", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" }, { "spacing": "medium", "type": "TextBlock", "text": "To Date", "color": "black" }, { "type": "Input.Date", "id": "data.filters[0].filterJaql.to", "placeholder": "yyyy-mm-dd", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" }, { "type": "ActionSet", "actions": [ { "type": "Filters", "title": "Submit", "data": { "filters": [ { "filterName": "Days in Date", "filterJaql": { "from": "", "to": "", "custom": true } } ] } } ] } ] } ] }, "config": { "fontFamily": "Open Sans", "fontSizes": { "default": 16, "small": 14, "medium": 22, "large": 32, "extraLarge": 50 }, "fontWeights": { "default": 400, "light": 100, "bold": 800 }, "containerStyles": { "default": { "backgroundColor": "#ffffff", "foregroundColors": { "default": { "normal": "#3A4356" }, "white": { "normal": "#ffffff" }, "grey": { "normal": "#9EA2AB" }, "orange": { "normal": "#f2B900" }, "yellow": { "normal": "#ffcb05" }, "black": { "normal": "#000000" }, "lightGreen": { "normal": "#93c0c0" }, "green": { "normal": "#2BCC7F" }, "red": { "normal": "#FA5656" }, "accent": { "normal": "#2E89FC" }, "good": { "normal": "#54a254" }, "warning": { "normal": "#e69500" }, "attention": { "normal": "#cc3300" } } } }, "imageSizes": { "default": 40, "small": 40, "medium": 80, "large": 120 }, "imageSet": { "imageSize": "medium", "maxImageHeight": 100 }, "actions": { "color": "", "backgroundColor": "", "maxActions": 5, "spacing": "large", "buttonSpacing": 20, "actionsOrientation": "horizontal", "actionAlignment": "right", "margin": "0", "showCard": { "actionMode": "inline", "inlineTopMargin": 16, "style": "default" } }, "spacing": { "default": 5, "small": 5, "medium": 10, "large": 20, "extraLarge": 40, "padding": 0 }, "separator": { "lineThickness": 1, "lineColor": "#D8D8D8" }, "factSet": { "title": { "size": "default", "color": "default", "weight": "bold", "warp": true }, "value": { "size": "default", "color": "default", "weight": "default", "warp": true }, "spacing": 20 }, "supportsInteractivity": true, "imageBaseUrl": "", "height": 197 } }Change multiple dashboards owner
In case when you need to change the owner of multiple dashboards there are few ways: 1) In case all the dashboards are in 1 folder then you can select the folder and export the dashboards. After what, import the dashboards back under the user to whom you would like to transfer the dashboards. In this case you will need to republish the dashboards to users to whom they were shared before. 2) Change the user email in the user profile under Admin tab-> Users - ONLY to a new user. This option will not merge the accounts! 3)Use the script below under Sys.Admin in Web Developers console (F12 in Chrome) Note, Dashboards and Elasicubes must be shared in advance! var newOwnersId = "5e5cdc80ecebb629e0c8de85" //New owner, use the REST API to get user ID $.ajax({ url: '/api/v1/dashboards?fields=oid,owner', success: function(res) { console.log(res) var dashboards = res.filter(function(dash) { return prism.user._id === dash.owner }) var dashboardsList = []; dashboards.forEach(function(dashboard) { dashboardsList.push(dashboard.oid) }) if (dashboardsList.length) { changeOwnership(dashboardsList) } } }) function changeOwnership(dashboardsList) { var succeed = []; var failed = [] dashboardsList.forEach(function(dash) { $.ajax({ method: 'POST', contentType: 'application/json', data: JSON.stringify( { "ownerId": newOwnersId, "originalOwnerRule": "edit" } ), url: '/api/v1/dashboards/' + dash + '/change_owner?adminAccess=true', async: false, success: function(res) { succeed.push(dash) }, error: function(res) { failed.push(dash) } }) }); console.log('Dashboard\'s owner was changed successfully for: '); console.log(succeed); console.log('Dashboard\'s owner wasn\t changed successfully. Please, try to change owner manually for: '); console.log(failed); }Pivot2. How to set up styles with the scripts?
Currently, in Linux installation, you can face widget [Pivot2]. This is a new realization of the widget [Pivot]. This means that it has new styles, new classes, etc. Almost all the scripts to change styles of the resulting table used DOM selectors to find elements that will be changed. Since a structure of a new pivot differs from the previous realization, then scripts developed for the first implementation of the pivot are not applicable to the new one. Below, I will explain a common approach on how to change styles in Pivot2. First of all, now Pivot is not fully generated HTML code. Data are retrieved via WebSocket protocol and each pivot has three parts: Headers; Categories; Values. These parts are loaded independently but will be displayed as an entire pivot. The second - in a new pivot attribute [fidx=i] does not exist, so you cannot use this attribute as a selector to select some column. The third - values are displayed in HTML element <div> (not in <td> as it was before in the first version) New pivot structure: FORMATTING EXAMPLES 1. Highlight text in column 2 $("div[class*='--col-1'] div.table-grid__content__inner").css('backgroundColor','red’) $("td[class*='--col-1'] div.table-grid__content__inner") Two things are going on here: $("td[class*='--col-1’]”) // select any td cell with classname that contains --col-1 $(“x y”) // select x first, then within the x select the y. Specifically, find all cells with a classname that contains --col-1. Then, inside those, only select the div with the classname table-grid__content__inner 2. Highlight text + padding in column 2 $("div[class*='--col-1'] div.table-grid__content__inner").parent().css('backgroundColor','red') 3. Highlight all cells in column 2 $("div[class*='--col-1'] div.table-grid__content__inner").parent().parent().css('backgroundColor','red') < jQuery Tip > $("DIV[CLASS*='--COL-1'] DIV.TABLE-GRID__CONTENT__INNER") is the lowest level <div>, which contains the displayed text. .parent() – one level up – another <div>. .PARENT().PARENT() – (2 LEVELS UP) – THE CELL. 3 levels up, .parent().parent().parent() is the entire row. 4. Highlight entire rows of values $("div[class*='--col-1'] div.table-grid__content__inner").parent().parent().parent().css('backgroundColor','yellow') 5. Highlight the first dimension column $("td[class*='--col-0'] div.table-grid__content__inner").css('backgroundColor','yellow') 6. Highlight row 2 of the dimension (country) $("td[class*='--row-1']").css('backgroundColor','yellow') 7. Color Entire Rows Highlight the entire 7th row: $("[class*='--row-7']").css('backgroundColor','yellow') Color rows 2 and 3: $(".table-grid__row-1").css('backgroundColor','yellow'); $(".table-grid__row-2").css('backgroundColor','lightgray') 8. Highlighting Entire First Row Of Data If we try the same approach with the top row, we will end up grabbing the header and the data - two rows: $(".table-grid__row-0").css('backgroundColor','yellow') That is not what we want, so let's be more selective in our jQuery. After checking the DOM structure of the pivot, let’s only look for our rows under the div.multi-grid__bottom-part, which contains dims /categories (country names) $("div.multi-grid__bottom-part .table-grid__row-0").css('backgroundColor','yellow') < jQuery Tip > $("DIV.MULTI-GRID__BOTTOM-PART .TABLE-GRID__ROW-0") FIRST, FIND DIV.MULTI-GRID__BOTTOM-PART. THEN, WITHIN THAT, ELEMENTS OF CLASS .TABLE-GRID__ROW-0. GENERALLY: $(“X Y“) – FIND Y WITHIN X Plus also get the top row of the values: $("div.scroll-elem .table-grid__row-0").css('backgroundColor','yellow') Now, let's combine these two separate jQuery selectors: $("div.scroll-elem .table-grid__row-0, div.multi-grid__bottom-part .table-grid__row-0").css('backgroundColor','yellow') < jQuery Tip > $("DIV.SCROLL-ELEM .TABLE-GRID__ROW-0, DIV.MULTI-GRID__BOTTOM-PART .TABLE-GRID__ROW-0") TWO THINGS ARE GOING ON HERE: $(“A B, C D”) // SELECT $(“A B”), ALSO SELECT $(“C D”), COMBINE THE RESULT $(“X Y”) // SELECT EVERY X, THEN SELECT EVERY Y INSIDE THE X CONDITIONAL FORMATTING 9. Let's turn yellow every number greater than 1000 in column 2: $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) {var myItemNum = parseFloat($(item).text() .replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().css('backgroundColor','yellow') }; }); 10. Now, same, but let's color the entire cell (not just its text): $("div[class*='--col-1'] div.table-grid__content__inner") .each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().parent().css('backgroundColor','yellow')}; }); 11. Now, for every item > 1000 in column 2, let's color the entire row of values: $("div[class*='--col-1'] div.table-grid__content__inner") .each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().parent().parent().css('backgroundColor','yellow’)}; }); 12. Now, for each of the values > 1000, let's color the entire row, including values and the country dimension This is more difficult because there is no way to find the right text that's > 1000, then navigate from that cell to the corresponding row of the country (the way we used to do this in Pivot 1), because now they exist in different table structures. $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',', '').replace('$', '')); var myRowNum; var myDimSelector = ""; if (myItemNum > 1000) { $(item).parent().parent().parent().css('backgroundColor', 'yellow'); // row of values myRowNum = parseInt($(item).parent().parent().parent().attr('class').slice(-1)) + 1; myDimSelector = "td.table-grid__cell--row-" + myRowNum; $(myDimSelector).css('backgroundColor', 'lightgreen'); //row of dimension headers }; }); Explanation: To better understand this, let’s do this step by step. $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {$(item).parent().parent().parent().css('backgroundColor','yellow') }; }); This returns 3 rows that match, each row is like this: div.table-grid__row.table-grid_row-1 $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {console.log($(item).parent().parent().parent() ) }; }); The jQuery we want to execute is this: $("td.table-grid__cell--row-1").css('backgroundColor’,’lightgreen’) , but where row-1 is replaced with the current value of the row Running: $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',','').replace('$','')); if (myItemNum > 1000) {console.log($(item).parent().parent().parent().attr('class') ) }; }); Returns: table-grid__row table-grid__row-1 table-grid__row table-grid__row-3 table-grid__row table-grid__row-5 Let's grab 1 character from the right using slice(-1) (1 char from the right). FINAL SCRIPT: $("div[class*='--col-1'] div.table-grid__content__inner").each(function(index, item) { var myItemNum = parseFloat($(item).text().replace(',', '').replace('$', '')); var myRowNum; var myDimSelector = ""; if (myItemNum > 1000) { $(item).parent().parent().parent().css('backgroundColor', 'yellow'); // row of values myRowNum = parseInt($(item).parent().parent().parent().attr('class').slice(-1)) + 1; myDimSelector = "td.table-grid__cell--row-" + myRowNum; $(myDimSelector).css('backgroundColor', 'lightgreen'); //row of dimension headers }; });How to create a Personalized Navigation Landing Page
Requirement In this article, we will look to create a personalized dashboard navigation landing page that shows: The logged user's most popular dashboards. Most popular dashboards on the company level. Consider further enhancing the logic presented in this article. Solution Steps: 1) To address the need, we will first need to learn how to implement a landing page - Follow this article to learn more on that. The reasoning behind it is we will use data-security where we will give a logged user permissions to see: His own records - Retrieving his popular dashboards The dummy user records - Retrieving the most popular dashboards on a company level. 4) Add data-security rules to the model that enables the user to see his own UserID/UserName from DimUsers table + UserId/UserName '-1'. 5) Find the attached BloX template that introduced action buttons to jump to a specific dashboard. LandingPage.json: { "card": { "style": "", "script": "", "title": "", "showCarousel": false, "body": [ { "spacing": "", "type": "Container", "items": [ { "type": "TextBlock", "text": "{panel:Dashboard Name}", "horizontalAlignment": "center", "size": "medium", "style": { "color": "#117899" } }, { "spacing": "small", "type": "TextBlock", "text": "{panel:Total Column} # of Actions", "color": "default", "horizontalAlignment": "center" } ] } ], "actions": [ { "type": "Action.OpenUrl", "title": "{panel:Dashboard Name}", "url": "http://10.50.57.72:30845/app/main#/dashboards/{panel:Dashboard ID}" } ] }, "config": { "fontFamily": "Open Sans", "fontSizes": { "default": 16, "small": 12, "medium": 22, "large": 32, "extraLarge": 50 }, "fontWeights": { "default": 500, "light": 100, "bold": 1000 }, "containerStyles": { "default": { "backgroundColor": "lightgrey", "foregroundColors": { "default": { "normal": "#3A4356" }, "white": { "normal": "#ffff" }, "grey": { "normal": "#5C6372" }, "orange": { "normal": "#f2B900" }, "yellow": { "normal": "#ffcb05" }, "black": { "normal": "#000000" }, "lightGreen": { "normal": "#3ADCCA" }, "green": { "normal": "#54a254" }, "red": { "normal": "#dd1111" }, "accent": { "normal": "#2E89FC" }, "good": { "normal": "#54a254" }, "warning": { "normal": "#e69500" }, "attention": { "normal": "#cc3300" } } } }, "imageSizes": { "default": 40, "small": 40, "medium": 80, "large": 160 }, "imageSet": { "imageSize": "medium", "maxImageHeight": 100 }, "actions": { "color": "", "backgroundColor": "#10a5F5", "maxActions": 5, "spacing": "small", "buttonSpacing": 20, "actionsOrientation": "horizontal", "actionAlignment": "center", "showCard": { "actionMode": "inline", "inlineTopMargin": 16, "style": "default" } }, "spacing": { "default": 5, "small": 20, "medium": 40, "large": 60, "extraLarge": 40, "padding": 25 }, "separator": { "lineThickness": 1, "lineColor": "#eeeeee" }, "factSet": { "title": { "size": "default", "color": "default", "weight": "bold", "warp": true }, "value": { "size": "default", "color": "default", "weight": "default", "warp": true }, "spacing": 20 }, "supportsInteractivity": true, "height": 842, "imageBaseUrl": "" } } 6) Import the attached dashboard example NavigationDashboard (1).dash. You can learn more on how to customize BloX in the following documentation page: https://www.sisense.com/marketplace/blox-widgets/ That's it!Table Vs Table With Aggregation Vs Pivot Vs Blox: Which One Is Right For You?
Sisense has several different ways to present table data. Often we pick whichever one we find first that seems to work, instead of picking the table that best meets our requirements. This can result in unnecessarily slow dashboards. Table: A table is the simplest widget. It displays raw data with no transformations done to it. It lacks things like aggregations, jump to dashboard, and selection. It is excellent for creating dashboards that will be primarily used as reports as there are not many ways to interact with a table. The table is the fastest performing widget on this list. Table with Aggregation: Much like a table, it lacks most of the features of a pivot but does allow aggregation. Use this when creating simple reports that need aggregation. Pivot: Pivot tables are the most flexible of our table widgets capable of aggregations, column, sub totals, drill down, and more. Pivots are the most time consuming widget to render on Sisense and so great care should be paid to the performance of dashboards using large amounts of pivots. *Pivot is fundamentally different between Windows and Linux. Linux pivot (Pivot2) performs better and has additional features. Pivot2 also has an API to easily customize your pivot widgets* Blox: It is possible to create a table in Blox! This table can do aggregation and selections (like jump to dashboard and filtering) but this requires writing specific widget logic. You will also have to do more to style Blox like a table. It loads about as quickly as a generic widget like a column chart. Blox is a good choice when you need the aggregations and selection of a pivot table but can't afford the performance hit, but it will require much more time to develop and maintain on the customer end. This approach is not generally recommended. We have summarised the information in this post into a table for quick digestion and future reference. Feature\Widget Table Table w/ Aggregation Pivot Blox Aggregations No Yes Yes Must be done in widget Drill down No No Yes No Selections (filters, jump to dashboard) No No Yes Yes Excel export No No Yes No Sub totals/Grand totals No No Yes Must be done in widget Performance Fastest Faster than pivot, slower than table Slowest As fast as a generic widget Uses For exporting user reports Best for reports that require aggregation Totals, Rows & Columns; Drill-in Make a table with aggregation including selections Customization Widget scripts Widget scripts Widget scripts Blox and widget scripts