Tracking ElastiCube size over time
What the solution does ✏️ This solution leverages Custom Code Notebooks and the Sisense REST API to capture ElastiCube size on a desired interval, making the data available for historical analysis. This analysis can be performed via Dashboards, Pulse, or any other method that benefits from a basic flat file structure. Why it’s useful ✅ As mentioned in the Introduction, ElastiCube size is important because it directly impacts performance, hardware resource consumption, build times, and scalability. Efficiently managing cube size is key to maintaining a fast and stable analytics environment. There may also be licensing considerations, requiring the deployment to remain below a sizing threshold. However, it can be challenging to monitor this data on a historical basis for purposes of trending, forecasting, or capturing anomalies. This solution aims to remove that challenge and provide your team with this data in an easy-to-use format. 🔨How it's achieved Create a new ElastiCube and add a Custom Code table. Import the attached Notebook file, getElasticubeSize.ipynb (inside .zip) -- the raw code can also be found below Infer the schema from the Notebook Ensure LastBuildDate and SnapshotDate_UTC are set to DateTime data type “Apply” the schema changes Save the Custom Code table and rename it as desired # Test Cell # When the notebook is executed by the Build process, this cell is ignored. # See the `Test Cell` section below for further details. additional_parameters = '''{}''' from init_sisense import sisense_conn import os import json import requests import pandas as pd from datetime import datetime # Construct the Sisense server base URL server = ( "http://" + os.environ["API_GATEWAY_EXTERNAL_SERVICE_HOST"] + ":" + os.environ["API_GATEWAY_EXTERNAL_SERVICE_PORT"] ) required_columns = [ "ElasticubeName", "TenantId", "SizeInMb", "SizeInGb", "LastBuildDate", "SnapshotDate_UTC", ] def get_elasticube_size(server): """Retrieve Elasticube size and metadata from Sisense API with error handling.""" endpoint = "/api/v1/elasticubes/servers/next" # API call try: response = sisense_conn.call_api_custom("GET", server, endpoint, payload=None) response.raise_for_status() response_json = response.json() except Exception as e: print(f"API error: {e}") return pd.DataFrame(columns=required_columns) # Validate JSON list structure if not isinstance(response_json, list): print(f"Unexpected response format: {type(response_json)}") return pd.DataFrame(columns=required_columns) # Compute snapshot timestamp once for all rows snapshot_timestamp = datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ") ec_size_data = [] for item in response_json: size_mb = item.get("sizeInMb", 0) size_gb = (size_mb or 0) / 1024 ec_size_data.append( { "ElasticubeName": item.get("title") or pd.NA, "TenantId": item.get("tenantId"), "SizeInMb": size_mb, "SizeInGb": size_gb, "LastBuildDate": item.get("lastBuildUtc"), "SnapshotDate_UTC": snapshot_timestamp, } ) df = pd.DataFrame(ec_size_data) # Convert LastBuildDate if not df.empty: df["LastBuildDate"] = pd.to_datetime( df["LastBuildDate"], errors="coerce", utc=True ) # Format to ISO 8601 df["LastBuildDate"] = df["LastBuildDate"].dt.strftime("%Y-%m-%dT%H:%M:%SZ") # Ensure correct column order return df[required_columns] # === Main Execution === df_result = get_elasticube_size(server) print(df_result.head()) # === Write DataFrame to CSV === output_folder = "/opt/sisense/storage/notebooks/ec_size" os.makedirs(output_folder, exist_ok=True) ts = datetime.utcnow().strftime("%Y%m%d_%H%M%S") file_name = f"elasticube_sizes_{ts}.csv" output_path = os.path.join(output_folder, file_name) df_result.to_csv(output_path, index=False) print(f"CSV written: {output_path}") print("Program completed successfully.") This Custom Code hits the Sisense REST API to capture ElastiCube size, along with the capture date (for historical trending purposes). It only serves as a starting point and can be freely edited. Every time the cube is built, it will generate a csv of the data and place it under /opt/sisense/storage/notebooks/ec_size. This location can be accessed in the Sisense UI via File Management – the ec_size folder may need to be created manually. To leverage the data in the csv files, I recommend creating a separate ElastiCube with a csv connection. In the connection: Select “Server Location” Define the Input Folder Path: /opt/sisense/storage/notebooks/ec_size/ Ensure “Union Selected” is enabled. This will combine all of the csv files into a singular data set. The reason I recommend creating a separate data model is so you don’t have to worry about table build order. For example, if the Custom Code and CSV tables exist in the same model, it’s possible for the CSV table to be built before the Custom Code builds/executes, so the latest CSV file’s data would be missed until the next build (and so on). By keeping the Custom Code and csv data models separate, you have more control over the build order by scheduling the builds sequentially. This dataset can be used as-is to build basic historical analyses, or you can enhance it by building separate custom tables that sit on top of it. Further, you can modify the Custom Code table itself to pull whatever data is needed from the Sisense REST API, such as ElastiCube row counts and more. NOTE: Similar data can be sourced from the Usage Analytics data model, using the SummarizeBuild table. But the Custom Code solution provides more flexibility in what is pulled, when, and how long it is retained, without affecting anything else. Additionally, each csv is available for independent review/modification as needed.
Add SQL Charts from Notebooks to a Dashboard
Sisense is happy to announce added functionality that allows users to add SQL charts to a Dashboard directly from Notebooks. This update significantly reduces the steps required to share insights and eliminates task switching between Notebooks, Dashboards, and Models while maintaining data security and data continuity.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.
Builds fail because libraries used in Custom Code tables are not installed
Use case You have specific libraries in Notebooks that have to be installed to run Custom code tables. However, these libraries can be uninstalled automatically (during upgrade or other system changes). Solution Add the following Python code into the first cell of each Custom Table Notebook to check if necessary libraries exist and install them if not, before executing the code: Adjust 'libraries' values to the libraries you need to check. import importlib import pandas as pd # List the libraries you want to check libraries = ['numpy', 'pandas', 'matplotlib', 'openpyxl', 'scipy', 'pyarrow'] # Initialize an empty list to store results results = [] for library in libraries: lib_found = importlib.util.find_spec(library) if lib_found is not None: results.append({"library": library, "status": "Previously Installed"}) else: results.append({"library": library, "status": "Newly Installed"}) print(f"{library} NOT FOUND. Installing now...") !pip install {library} Related Content: Sisense Docs: https://docs.sisense.com/main/SisenseLinux/transforming-data-with-custom-code.htm Sisense Academy: https://academy.sisense.com/notebooks-course
Allow modifying columns width in notebook results
There is no option to modify table columns width in notebook results. This means that if I query a dataset of accessed URLs, where the end of the line matters most I cannot see anything beyond the domain name. Optional workarounds are hovering row by row or exporting all results to CSV. Both are cumbersome. Modifying columns width seems to be a common notebook functionality in similar solutions, so it would be nice if Sisense could support this as well.
Dashboard Filters Applied to Notebooks and Non-Notebooks
Currently Notebooks provide a powerful opportunity to access and visualize data utilizing SQL and to add these to fusion dashboards. Fusion's primary analytics environment allows for powerful dashboard-wide filters. Currently dashboard filters do not apply to charts or tables which are created through notebooks even if the notebook is pointed to the exact same data table and column as other dashboard widgets. Please develop the ability for dashboard filters to filter both fusion dashboard widgets and also notebook-based widgets.Leveraging Sisense Notebooks with Compose SDK
Discover how to enhance your analytics integration with Sisense Notebooks and the Compose SDK. This article explores a code-first approach to creating custom datasets and visualizations using SQL, empowering data teams to leverage their existing skills. Learn how to seamlessly embed tailored insights into customer-facing applications, streamlining workflows for both analysts and developers. Unlock the potential of your data with Sisense, delivering impactful, personalized analytics that meets your organization’s unique needs.
