Input Parameters using BloX for What-If Analysis
Input Parameters using BloX for What-If Analysis This is an alternative to the paid or community Input Parameter plugins. This new BloX Input Parameter custom action: supports multiple levers (input parameters) with just one Apply button supports all widget types remembers previous selections even after refreshing/closing the dashboard loops through all widgets so dashboard designers do not have to specify widget IDs in the BloX code. Note: Be mindful of performance when considering this option as this will execute the BloX action on all widgets including those that the input parameters may not necessarily be relevant to. Input Parameter Implementation Instructions: Import the example dashboard attached below. Before importing the file, change the file extension from .txt to .dash. Make sure that you still have the Sample ECommerce Elasticube for this dashboard to properly display the data. Open the BloX Lever Configuration widget, then add the custom action given below to your Sisense environment. Go to the Actions tab, click the three-dot menu, then click Create Action. Copy the custom action code from below and paste it into your BloX custom action editor. Give this name for the action: ParameterSwap_V2_AllWidgets. You can also use a different name, but make sure the name of the action matches with the name referenced in the BloX code. Click Next, then Create. Click the Apply button to close the widget. Enter values to the levers and click Apply. The number on the widgets is now recalculated based on your input. There are three input parameters in this example. To add another input parameter: In the BloX code of the Lever Configuration widget, modify the number of input parameters in the BloX code under the paramsToModify parameter. For example, if you need 4 input parameters (levers), update the value to 4. On the left panel, add the additional swap_levers to the Values panel, i.e. swap_lever4, swap_lever5, etc. You can assign any arbitrary hard-coded number to these swap levers. To add the input box to the widget, copy one of the column elements then add it to the BloX code. Modify the id parameter to selectVal<lever number>, e.g. if this is the fourth lever, the id should be selectVal4. Similarly, modify the value parameter to match the swap_lever name you added in the previous step, e.g. [swap_lever4]. Update the title text as well and give it a meaningful description. Save the widget by clicking the Apply button. Add the additional levers to your widget formulas as needed so that they get recalculated when you enter the input values. Input Parameter BloX action: //initialize variables var swapParam = []; //create an array of swap_levers for (d = 0; d < payload.data.paramsToModify; d++) { swapParam[d] = "swap_lever" + (d + 1); } //loop through each of the specified widgets payload.widget.dashboard.widgets.$$widgets .forEach(function (widget) { //loop through each panel in the widget //exclude the filter panel (last panel) for (p = 0; p < widget.metadata.panels.length - 1; p++) { //loop through each item in the panel for (i = 0; i < widget.metadata.panels[p].items.length; i++) { //check if the panel item contains context (i.e. a formula) if (widget.metadata.panels[p].items[i].jaql.context != undefined) { var queryContext = widget.metadata.panels[p].items[i].jaql.context; //loop through each context in the item for (let [k, v] of Object.entries(queryContext)) { //loop through each swap_lever for (s = 0; s < swapParam.length; s++) { //check if the formula contains the swap_lever if (v.title == swapParam[s]) { var val = 'selectVal' + (s + 1); //update the formula with the swap_lever value that the user entered v.formula = payload.data[val]; } } } } } } //apply and save changes to the widget widget.changesMade('plugin-BloX', ['metadata']) //refresh the widget widget.refresh(); }) Resetting to Default Values The attached dashboard example includes a Reset button to reset the input parameters to pre-set default values. If you do not need this option, you can remove the second action from the BloX code, as shown in the highlighted part of the screenshot below. Instructions to implement the Reset button: Open the BloX Lever Configuration widget, then add the custom action given below to your Sisense environment. Go to the Actions tab, click the three-dot menu, then click Create Action. Copy the custom action code from below and paste it into your BloX custom action editor. Give this name for the action: ParameterSwap_V2_AllWidgets_Reset. You can also use a different name, but make sure the name of the action matches with the name referenced in the BloX code. Click Next, then Create. In the BloX code, update the defaultValues array with your required default values. See screenshot below for reference. There are three input parameters in this example, if you have more/less parameters, update the paramsToModify value to the correct number of parameters that you have. See screenshot below for reference. Click the Apply button to close the widget. Reset Input Parameter BloX action: //initialize variables var leverValues = payload.data.defaultValues; var swapParam = []; //create an array of swap_levers for (d = 0; d < payload.data.paramsToModify; d++) { swapParam[d] = "swap_lever" + (d + 1); } //loop through all widgets in the dashboard payload.widget.dashboard.widgets.$$widgets .forEach(function (widget) { //loop through each panel in the widget //exclude the filter panel (last panel) for (p = 0; p < widget.metadata.panels.length - 1; p++) { //loop through each item in the panel for (i = 0; i < widget.metadata.panels[p].items.length; i++) { //check if the panel item contains context (i.e. a formula) if (widget.metadata.panels[p].items[i].jaql.context != undefined) { var queryContext = widget.metadata.panels[p].items[i].jaql.context; //loop through each context in the item for (let [k, v] of Object.entries(queryContext)) { //loop through each swap_lever for (s = 0; s < swapParam.length; s++) { //check if the formula contains the swap_lever if (v.title == swapParam[s]) { var val = 'selectVal' + (s + 1); //update the formula with the default value v.formula = leverValues[s]; } } } } } } //apply and save changes to the widget widget.changesMade('plugin-BloX', ['metadata']) //refresh the widget widget.refresh(); }) We hope this is a helpful article and would love to hear about your experience in the comments!
Blox - Multi-Select Dropdown List Filter
Blox - Multi-Select Dropdown List Filter This article will take you step by step on how to create a multi-select dropdown filter using Blox and JavaScript. ElastiCube: 1. For each field you want to use in multi-select filter, you need to add a custom column. For instance, in our Sample ECommerce ElastiCube, add a custom column to the "Category" table: For Sisense on Windows add the below string in order to create the column: '<li><input type="checkbox" />'+[Category].[Category]+'</li>' For Sisense on Linux: '<li><input type=checkbox>'+[Category].[Category] + '</li>' 2. Change its name to [CategoryHTML]. 3. Do the same for the column [Country] from the table [Country] and name it [CountryHTML]. 3. Perform the 'Changes Only' build. Dashboard: 1. Download the dashboard attached and import it to your application. 2. Create a custom action in BloX and name it MultiBoxSelection: 3. Add the action's code below: var outputFilters = []; var widgetid = payload.widget.oid; var widgetElement = $('[widgetid="' + widgetid + '"]'); widgetElement.find($('blox input:checked')).parent().each(function () { var values = $('.fillmeup').attr('value') + $(this).text(); $('.fillmeup').attr('value', values); }).each((i, lmnt) => { outputFilters.push($(lmnt).text()); }) payload.widget.dashboard.filters.update( { 'jaql': { 'dim': payload.data.dim, 'title': payload.data.title, 'filter': { 'members': outputFilters }, 'datatype': 'text' } }, { 'save': true, 'refresh': true } ) 4. Action's snippet: { "type": "MultiBoxSelection", "title": "Apply", "data": { "dim": "FilterDimension", "title": "FilterTitle" } } 5. Add the widget's script. For each widget you need to change identifiers [CategoryList] and [CategoryItems] - these identifiers should be unique per each widget on a page: widget.on('ready', function() { var checkList = document.getElementById('CategoryList'); var items = document.getElementById('CategoryItems'); checkList.getElementsByClassName('anchor')[0].onclick = function(evt) { if (items.classList.contains('visible')) { items.classList.remove('visible'); items.style.display = "none"; } else { items.classList.add('visible'); items.style.display = "block"; } } items.onblur = function(evt) { items.classList.remove('visible'); } }); widget.on('processresult', function(a, b) { b.result.slice(1, b.result.length).forEach(function(i) { b.result[0][0].Text = b.result[0][0].Text + ' ' + i[0].Text }) }); These identifiers should be the same as you have in the widget in the value of [text]: { "type": "TextBlock", "spacing": "large", "id": "", "class": "", "text": "<div id='CategoryList' class='dropdown-check-list' tabindex='100'> <span class='anchor'>Select Category</span> <ul id='CategoryItems' class='items'>{panel:CategoryHTML}</ul> </div>" } 5. Click Apply on the widget and refresh the dashboard. Important Notes: Make sure you have the Category in the items (The new column was created) and name the Item "Category". Make sure you have a Category Filter (The actual category field and name it "Category") on the dashboard level. Make sure to replace the field and table names with the relevant field/table in the Action, in the editor of the Blox widget in the Blox items and in the dashboard filter. Disclaimer: Please note that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you "as-is" and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.Passing Filters via URL Parameters for Dashboards with Separate Datasources
Sisense includes a native included feature and format for passing URL filters via URL parameters, as documented here. By default, this functionality copies filters in full, including the datasource parameter of the filter, and includes every filter automatically. It results in very long URL's, and includes many parameters that are not always required, as the full filter object is included. Previous Knowledge Base articles articles have discussed how similar behavior can be recreated customized via scripting for more flexible usage. However, those approaches applied only to member-type filters, excluding other filter types and multi-level dependent filters. The code shared below demonstrates a more flexible filter modification via URL modification approach. It includes both creating URL parameters and reading URL parameters for filter modification, whether this code is in a script or plugin. This method applies to all filter types and can be used to transfer filters between dashboards using different datasources. This code works in both dashboard and widget scripts as well as plugins. If your datasources use different dimension names, this code can be adopted to map and match the aligned dimensions.
Reset to Default Filters Button using BloX
This article will explain how to create reset to default filters button in BloX This helps in case you want to provide this icon functionality on the dashboard and not just in the Filters panel. Create a new custom action with the below code: *Make sure you're naming the action 'Click' if you're using the attached dashfile in section 2 var dashboard = payload.widget.dashboard dashboard.filters.update(dashboard.defaultFilters,{refresh:true}) You can use the dashfile attached (Backtodefaultfilters.dash ) OR Copy & Paste the below JSON code in the Editor: { "style": "", "script": "", "title": "", "showCarousel": true, "body": [], "actions": [ { "type": "Click", "title": "Reset Filters" } ] }Blox - Multi-Select Dropdown List Filter
Blox - Multi-Select Dropdown List Filter This article will take you step by step on how to create a multi-select dropdown filter using Blox and JavaScript. ElastiCube: 1. For each field you want to use in multi-select filter, you need to add a custom column. For instance, in our Sample ECommerce ElastiCube, add a custom column to the "Category" table: For Sisense on Windows add the below string in order to create the column: '<li><input type="checkbox" />'+[Category].[Category]+'</li>' For Sisense on Linux: '<li><input type=checkbox>'+[Category].[Category] + '</li>' 2. Change its name to [CategoryHTML]. 3. Do the same for the column [Country] from the table [Country] and name it [CountryHTML]. 3. Perform the 'Changes Only' build. Dashboard: 1. Download the dashboard attached and import it to your application. 2. Create a custom action in BloX and name it MultiBoxSelection: 3. Add the action's code below: var outputFilters = []; var widgetid = payload.widget.oid; var widgetElement = $('[widgetid="' + widgetid + '"]'); widgetElement.find($('blox input:checked')).parent().each(function () { var values = $('.fillmeup').attr('value') + $(this).text(); $('.fillmeup').attr('value', values); }).each((i, lmnt) => { outputFilters.push($(lmnt).text()); }) payload.widget.dashboard.filters.update( { 'jaql': { 'dim': payload.data.dim, 'title': payload.data.title, 'filter': { 'members': outputFilters }, 'datatype': 'text' } }, { 'save': true, 'refresh': true } ) 4. Action's snippet: { "type": "MultiBoxSelection", "title": "Apply", "data": { "dim": "FilterDimension", "title": "FilterTitle" } } 5. Add the widget's script. For each widget you need to change identifiers [CategoryList] and [CategoryItems] - these identifiers should be unique per each widget on a page: widget.on('ready', function() { var checkList = document.getElementById('CategoryList'); var items = document.getElementById('CategoryItems'); checkList.getElementsByClassName('anchor')[0].onclick = function(evt) { if (items.classList.contains('visible')) { items.classList.remove('visible'); items.style.display = "none"; } else { items.classList.add('visible'); items.style.display = "block"; } } items.onblur = function(evt) { items.classList.remove('visible'); } }); widget.on('processresult', function(a, b) { b.result.slice(1, b.result.length).forEach(function(i) { b.result[0][0].Text = b.result[0][0].Text + ' ' + i[0].Text }) }); These identifiers should be the same as you have in the widget in the value of [text]: { "type": "TextBlock", "spacing": "large", "id": "", "class": "", "text": "<div id='CategoryList' class='dropdown-check-list' tabindex='100'> <span class='anchor'>Select Category</span> <ul id='CategoryItems' class='items'>{panel:CategoryHTML}</ul> </div>" } 5. Click Apply on the widget and refresh the dashboard. Important Notes: Make sure you have the Category in the items (The new column was created) and name the Item "Category". Make sure you have a Category Filter (The actual category field and name it "Category") on the dashboard level. Make sure to replace the field and table names with the relevant field/table in the Action, in the editor of the Blox widget in the Blox items and in the dashboard filter. Disclaimer: Please note that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you "as-is" and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.Filter BETWEEN two dates in the model
Filter BETWEEN two dates in the model This article explains how to create a filter to return data between two fields in the model. The following cases will be covered: Show only active contracts on the given date; To show the status of the record on the given date. The first challenge is to show only active contracts on the given date. Let’s assume that we have the following columns in the table “History”: Id; From; To. Let's assume that the contract is active if the given date is between [From] and [To]. To filter the dates we will create two filters on the dashboard using these fields. Now, end-users can choose the same date in these filters. This is not a good user experience, because the user will have to update two filters. To improve user experience we will do the following: Lock these filters - a user will not update these filters manually. Use element Input.Date of the widget “BloX” to let a user provide a date; Create a custom action to update the filters [From] and [To]. Let’s create the action “betweenDates”: const { data, widget } = payload; const { selectedDate, fromDateDim, toDateDim } = data; if (!(selectedDate && fromDateDim && toDateDim)) { return; } const fromFilter = widget.dashboard.filters.item(true, fromDateDim); const toFilter = widget.dashboard.filters.item(true, toDateDim); if (!(toFilter && fromFilter)) { return; } setNewDatesInFilters(fromFilter, toFilter, selectedDate); widget.dashboard.refresh(); function setNewDatesInFilters(fromFilter, toFilter, newDate) { toFilter.jaql.filter = { from: newDate }; fromFilter.jaql.filter = { to: newDate }; updateFilter(toFilter.jaql); updateFilter(fromFilter.jaql, true); } function updateFilter(filterObject, save = false) { widget.dashboard.filters.update({ jaql: filterObject }, { save, refresh: false }) } Snippet of this action: { "type": "betweenDates", "title": "Apply", "data": { "selectedDate": "", "fromDateDim": "", "toDateDim": "" } } Let’s use this action in the BloX. Also, we will add the element “Input.Date”. As the result, we will get the following BloX JSON: { "style": "", "script": "", "title": "", "showCarousel": true, "body": [ { "type": "Container", "items": [ { "type": "Input.Date", "id": "data.selectedDate", "class": "" } ] } ], "actions": [ { "type": "betweenDates", "title": "Apply", "data": { "selectedDate": "", "fromDateDim": "[History.From (Calendar)]", "toDateDim": "[History.To (Calendar)]" } } ] } Now, you can choose a date and after clicking “Apply” both filters will be updated with the selected date. The only issue - input field is cleared. We will fix this by adding a simple widget’s script to the widget BloX - this script will persist the selected date in the input field: widget.on('ready', () => { const toFilter = widget.dashboard.filters.item('[History.To (Calendar)]'); const selectedDate = $$get(toFilter, "jaql.filter.from"); if (selectedDate && document.getElementById('data.selectedDate')) { document.getElementById('data.selectedDate').value = selectedDate; } }) Now, when the dashboard is loaded, the script finds the previously selected date and fills in the input field. Also, when a user changes the date, the newly input date is shown. Let’s solve the second challenge to show statuses of the record on the given date. Let’s assume that we have table “Cases”. In this table we have historical information about cases: Id - unique identifier of the record; CaseId - case’s identifier; Status - status of the case; Date - date when case status was changed. Sample of the data in this table: [ALT text: A table displaying case information with the following columns: Id, CaseId, Status, and Date. The table includes rows showing various case entries with status updates such as "New," "Open," "Reopen," and "Closed," along with corresponding timestamps for each case.] Use-case: as an end-user, I want to input the date and Sisense should return the status of the case in the given date. To accomplish this, let’s create a new custom table “History”, where we will compute the first date of the status and its last date. This custom table will be populated with the next SQL query: SELECT CaseId, [Old Status], [New Status], [From], [To] FROM ( SELECT c.[CaseId], c.[Status] "Old status", cc.[Status] "New status", c.[Date] "From", cc.[Date] "To", RankCompetitionAsc(c.CaseId, c.[Status], c.Date, cc.Date) "Rank" FROM Cases c INNER JOIN Cases cc ON c.[CaseId] = cc.[CaseId] and c.date < cc.date ) c WHERE c.Rank = 1 UNION ( SELECT c.[CaseId], c.[Status] "Old Status", '' "New status", c.date "From", CreateDate(2999, 12, 31) "To" FROM Cases c INNER JOIN ( select caseId, max(date) "MaxDate" from cases group by caseId ) latestStatus ON c.[CaseId] = latestStatus.caseId and latestStatus.MaxDate = c.date ) This query contains two parts: The first part returns statuses, which were already changed and we have information about the status after the change; The second part shows the current status of the record. As a result of the data transformation, we will have the following structure: [ALT Text: A table displaying status updates with columns labeled "CaseId," "Old Status," "New Status," "From," and "To." There are five rows of data showing various cases, their old and new statuses, and corresponding timestamps for when the status changes occurred. The "From" and "To" columns indicate the date and time of the changes. Some cases are marked as "New," "Open," "Closed," and "Reopen."] Now, we will use approach from the first part of this article to create dashboard’s filters and utilize BloX to input the date and custom action to update the dashboard’s filters. Conclusion In this article we reviewed a possible way to manipulate date filters. A similar approach you can use for embedding cases. Since the filters will be controlled from the BloX widget and filters will be locked for manual editing, you can even hide these dates filters using the following plugin: https://www.sisense.com/marketplace/add-on/hide-filters Similar Content: Sisense Docs Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this please let us know.Changing Measures In The Entire Dashboard Using Blox User Interface
Objective Display several related values (KPIs/Measures) in multiple widgets in the dashboard based on the user selection, in a simple and easy way, without duplicating widgets/formulas or tables in the Elasticube. As a direct result - the queries behind the widgets are extremely faster - lead to shorter loading time when the end-user needs to change the whole dashboard KPIs. Example files (works with Ecommerce Sample Elasticube): Dashboard Blox Template Blox Action Demonstration: Preparation Create a new Indicator widget and save its WidgetId. Create a custom action with this code below and name it SwitchMeasure var dimIndex = payload.data.selectVal - 1; var dimToSwapTo = payload.widget.metadata.panels[1].items[dimIndex].jaql; var widgetIds = payload.data.widgetToModify; payload.widget.dashboard.widgets.$$widgets .filter(i=>widgetIds.includes(i.oid)) .forEach(function(widget){ if(widget.metadata.panels[1].$$widget.type == 'indicator') { widget.metadata.panels[0].items[0].jaql = dimToSwapTo; } else { widget.metadata.panels[1].items[0].jaql = dimToSwapTo; } widget.changesMade(); widget.refresh(); }) Implementation 1. ADDING MEASURES TO SWITCH BETWEEN The formulas in the values panel define the measures your other widgets will switch between. Create a new Blox widget and add all the formulas (Up to 20 Formulas) you want to switch between into the values panel in the selection of the button Blox widget. Please pay attention to the order (top value to bottom -> left to right buttons) 2. CUSTOMIZING THE BUTTONS SELECTION WIDGET This step will define the values that are visible to the user in the button selection widget. -Add choices that reflect the ORDER and intuitive name the formulas in the values panel. -Add the widget IDs of the widgets you'd like to modify to the script of each option. You can find the widget ids in the URL when in widget editing mode. -Change the button color by editing the Background color for each option. For example, we will be switching between Revenue, Cost, and Quantity, our Blox script would be: { "style": "", "script": "", "title": "", "showCarousel": true, "titleStyle": [ { "display": "none" } ], "carouselAnimation": { "delay": 0, "showButtons": false }, "body": [ { "type": "TextBlock", "id": "", "class": "", "text": " " } ], "actions": [ { "type": "SwitchMeasure", "title": "Revenue", "style": { "backgroundColor": "#298C1A" }, "data": { "widgetToModify": [ "5f1b462e336d9c242cb9e8ea", "5f1b462e336d9c242cb9e8e5", "5f1b462e336d9c242cb9e8e7", "5f1b462e336d9c242cb9e8e9", "5f1b462e336d9c242cb9e8e8" ], "selectVal": "1" } }, { "type": "SwitchMeasure", "title": "Cost", "style": { "backgroundColor": "#A31818" }, "data": { "widgetToModify": [ "5f1b462e336d9c242cb9e8ea", "5f1b462e336d9c242cb9e8e5", "5f1b462e336d9c242cb9e8e7", "5f1b462e336d9c242cb9e8e9", "5f1b462e336d9c242cb9e8e8" ], "selectVal": "2" } }, { "type": "SwitchMeasure", "title": "Quantity", "style": { "backgroundColor": "#708AA5" }, "data": { "widgetToModify": [ "5f1b462e336d9c242cb9e8ea", "5f1b462e336d9c242cb9e8e5", "5f1b462e336d9c242cb9e8e7", "5f1b462e336d9c242cb9e8e9", "5f1b462e336d9c242cb9e8e8" ], "selectVal": "3" } } ] } 3. CUSTOMIZING THE BUTTON SELECTION ANIMATION Add the script (widget script) below to the indicator you created in the preparation section: Change the WidgetID to your indicator Widget ID Change the formula names based on the value list from the buttons selection widget Change the button’s titles based on the titles you selected the buttons selection widget //Widget Script: var ChooseYourUnselectColor = '#D3D3D3'; var Button1Color = '#298C1A'; var Button2Color = '#A31818'; var Button3Color = '#708AA5'; var widgetIndicator = '5f1b462e336d9c242cb9e8ea' widget.on('ready',function(widget, args){ var widgetOID = widgetIndicator; //Get the selected KPI object var widget = prism.activeDashboard.widgets.$$widgets.find(w => w.oid === widgetOID) if(widget.metadata.panels[0].items[0].jaql.title == 'Total Revenue'){ var textOfButtonToFormat1 = 'Cost'; var textOfButtonToFormat2 = 'Quantity'; var selectedButton = 'Revenue' $('button.btn:contains('+textOfButtonToFormat1+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+textOfButtonToFormat2+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+selectedButton+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": Button1Color }); } else if (widget.metadata.panels[0].items[0].jaql.title == 'Total Cost') { var textOfButtonToFormat1 = 'Revenue'; var textOfButtonToFormat2 = 'Quantity'; var selectedButton = 'Cost' $('button.btn:contains('+textOfButtonToFormat1+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+textOfButtonToFormat2+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+selectedButton+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": Button2Color }); } else { var textOfButtonToFormat1 = 'Revenue'; var textOfButtonToFormat2 = 'Cost'; var selectedButton = 'Quantity' $('button.btn:contains('+textOfButtonToFormat1+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+textOfButtonToFormat2+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+selectedButton+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": Button3Color }); } })Pivot and Table Widget Scripting for Replacing Values with Colored Arrows
Sisense table and pivot widgets offer extensive customization options for data presentation, allowing developers to tailor widget content to specific needs, as discussed in detail in previous articles. While the Pivot 2.0 API includes a built-in transformPivot function for data transformation, Table widgets typically require direct DOM manipulation. This article provides a step-by-step guide for replacing numeric (or other) values in both widget types with arrow characters via scripting. It also demonstrates how to assign colors or apply conditional logic to these transformations. Examples include the use of Unicode arrow characters, though any other characters or combinations of characters can of course be utilized.Exploring RAG: A Sisense-Based Approach with BigQuery and Gemini
Exploring RAG: A Sisense-Based Approach with BigQuery and Gemini Continuing from our previous article, Automated Machine Learning with Sisense Fusion: A Practical Guide, where we discussed how Sisense and AutoML simplify complex machine learning workflows, I am now excited to introduce an advanced feature: a chatbot interface powered by Retrieval-Augmented Generation (RAG) and a large language model (LLM) like Gemini. This enhancement allows users to interact with their data in natural language through Sisense's Native Blox widget. Imagine asking questions like "How many products did I sell per category?" and receiving insightful answers instantly. This not only improves data accessibility but also bridges the gap between technical data repositories and business decision-making. In this article, I’ll cover the first step of enabling this functionality: setting up a seamless integration pipeline between Sisense and Google BigQuery, embedding table and column metadata, and preparing the data for efficient querying. Important Note: Experimental Project: This implementation is an experimental project and not an official Sisense feature. It demonstrates how Sisense’s functionalities can be utilized to build a custom Retrieval-Augmented Generation (RAG) pipeline or how you can use this example by importing the provided notebooks. Google Ecosystem: The example provided is specific to the Google ecosystem, utilizing services like BigQuery (BQ) and Gemini for query generation and processing. Associated Costs: Please note that each prompt sent to the chatbot incurs a cost, which will be billed to your Google Cloud Platform (GCP) account. This includes charges for LLM processing and database queries. Building the Foundation: Data Preparation and Embeddings To enable natural language interactions with your data, we first need to establish a robust data infrastructure. This includes creating a vector store for embeddings in Google BigQuery (BQ) and preparing metadata about tables and columns. The process is fully automated using a pre-built Sisense custom code notebook, which simplifies embedding generation and management. Dataset in BigQuery The journey begins with your dataset in Google BigQuery. The notebook retrieves metadata such as table and column names, descriptions, and schema information from BigQuery’s Information_Schema. If any descriptions are missing, the system automatically generates them, ensuring comprehensive metadata coverage. Setting Up the Vector Store in BigQuery The vector store acts as the backbone for similarity searches within the RAG system. Using the custom notebook, the system: Creates the vector store: A structured repository to store embeddings for tables and columns. Organizes metadata: Ensures all table and column descriptions are structured and accessible. Generating Table and Column Descriptions Missing descriptions can hinder data understanding. The notebook includes a Description Agent that automatically generates meaningful text for: Tables: Contextual descriptions based on schema and usage. Columns: Descriptions highlighting their role within the dataset. These enhancements ensure the metadata is both informative and ready for embedding generation. Creating Embeddings with Vertex AI To enable semantic search, metadata descriptions are converted into numerical embeddings using Vertex AI’s TextEmbeddingModel. This is facilitated by the EmbedderAgent, which: Accepts strings or lists of strings (e.g., column descriptions). Generates embeddings through Vertex AI. Handles both single and batch processing for efficiency. Efficient Embedding with Chunked Processing For large datasets, embeddings are generated in chunks using the get_embedding_chunked function. This ensures: Scalability: Handles datasets of all sizes without performance issues. Parallel Processing: Processes text chunks simultaneously to speed up the workflow. Structured Outputs: Embeddings are returned in a structured DataFrame for storage or analysis. Storing Embeddings in the Vector Store The final step is storing these embeddings in the BigQuery vector store. This ensures that: Similarity searches are fast and efficient. Metadata is always accessible for chatbot interactions. ALT text: A screenshot of a data table displaying various columns such as "table_schema," "column_name," "data_type," "source_type," and several other attributes. The table shows sample values and metadata related to a database structure, organized in rows and columns. How the Chatbot Interface Works Now that the foundation for embeddings and metadata storage is set, let’s explore the chatbot interface in action. Imagine opening the chatbot in the Blox widget and asking a question about your dataset. Within moments, the chatbot responds in natural language, providing actionable insights. But what exactly happens under the hood to generate this seamless interaction? RAG Notebook and the Chatbot Workflow The chatbot operates using a pre-built RAG custom code transformation notebook, which orchestrates the end-to-end process. With this notebook, the entire pipeline—from understanding the query to generating the response—is automated. The notebook uses multiple specialized agents, each responsible for a specific task, ensuring precision and efficiency at every step. SQL Query Builder Agent BuildSQLAgent This agent specializes in constructing SQL queries for BigQuery. It uses the LLM to analyze the user’s natural language query and matches it with table schemas and column details from the vector store. It outputs a fully formed SQL query tailored to the user’s dataset and question. SQL Validation Agent ValidateSQLAgent The ValidateSQLAgent validates the SQL query before execution using a Large Language Model (LLM). Validation ensures the query adheres to essential rules, including: The presence of all referenced columns and tables. Proper table relationships and join conditions based on the schema. Formatting and compliance with BigQuery-specific SQL standards. Validation occurs during the debugging process, specifically within the DebugSQLAgent, to identify potential errors before attempting a dry run or execution. It provides a detailed JSON response: If valid, the process moves to the next step (dry run or execution). If invalid, the DebugSQLAgent uses the error details to refine the query iteratively. SQL Debugging Loop Agent DebugSQLAgent This agent runs the debugging loop to refine queries that fail validation or execution. The process includes: Validation: The query is passed to ValidateSQLAgent to check syntax, schema compliance, and structure. If valid, the query is ready for execution. Dry Run: If validation passes, the query is tested using a dry run via the test_sql_plan_execution function to confirm execution readiness. Execution: Once validation and dry runs succeed, the final query is executed using the retrieve_df function, which returns results as a DataFrame. Iterative Refinement: If the query fails either validation or the dry run, the DebugSQLAgent uses the LLM to troubleshoot and generate an alternative query. The loop repeats until a valid query is generated or the maximum debugging rounds are reached. This agent ensures the final query is: Correctly structured and semantically valid. Optimized for performance and aligns with the original user intent. Response Agent ResponseAgent This agent translates the SQL query results into natural language. It bridges the gap between technical SQL outputs and user-friendly communication. By combining the query results with the user’s original question, it crafts a clear and relevant response. How the Workflow Executes Here’s the step-by-step process for generating a response: User Query Embedding The EmbedderAgent converts the user’s natural language question into a numerical embedding. Using BigQuery native vector search, the system retrieves similar embeddings from the vector store created in the first phase. Schema and Content Retrieval Based on the retrieved embeddings, the system fetches relevant table and column schema details from the vector store. SQL Query Generation The BuildSQLAgent uses the retrieved schema details to construct an SQL query that aligns with the user’s question. SQL Validation and Execution The ValidateSQLAgent checks the generated SQL for accuracy and potential errors. If the SQL passes validation, it is executed against the BigQuery database. Debugging (if needed) If the query fails or generates an error, the DebugSQLAgent refines it iteratively until a valid query is produced. Response Generation The ResponseAgent uses the query results and the user’s original prompt to generate a natural language response. If the system fails to generate a valid response, it communicates the issue to the user. Conclusion By combining the foundational embedding process with this RAG-powered workflow, the chatbot transforms how users interact with their data. From seamless SQL query generation to delivering natural language responses, the system exemplifies the power of Sisense Fusion and advanced AI tools to simplify data-driven decision-making. As always, please reach out to your Customer Success Manager (CSM) if you would like to implement this in your own environment.
