Sisense Community

Community Feedback
Chapters
Events
Forums
Blogs
KB Docs
Support
Learning
Use Case Gallery

Categories

Notebooks

Use Case Gallery
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.
akaplanPosted 6 months ago
0
Help and How-To

kamal123
Kamal Hinduja Swiss: How to use REST APIs to push data into Sisense?

Hi All, I'm Kamal Hinduja, based in Geneva, Switzerland(Swiss) . Can anyone explain in details How to use REST APIs to push data into Sisense? Thanks, Regards Kamal Hinduja Geneva, Switzerland
10 months agolast reply 10 months ago
4
Help and How-To

ram
date range

Hi Everyone I have created a Calendar table based on Custom code option currently i am showing the count of records days stating from 10th to 17th but i want to show the count from dates 15th and 22nd How can i do that
1 year agolast reply 1 year ago
2
Help and How-To

giovanna
Delete a custom column from a custom table

I did a custom table with two "original" columns - Date and weekday_number - and previously i added a bigint custom column, but now i want to delete this bigint column and i cant find where to do this
1 year agolast reply 1 year ago
2
Sisense Intelligence & AI
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.
Himanshu NegiPosted 1 year ago
0
Troubleshooting
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
nataliia_khPosted 1 year ago
0
Product Feedback Forum
Allow modifying columns width in notebook results
omeronPosted 1 year ago

0
Product Feedback Forum
Dashboard Filters Applied to Notebooks and Non-Notebooks
gray_davidsonPosted 2 years ago • Last reply 1 year ago

1
Best Practices
Leveraging Sisense Notebooks with Compose SDK

Leveraging Sisense Notebooks with Compose SDK In today’s developer ecosystem, delivering a tailored creator experience is essential for success, especially when integrating analytics into customer-facing applications. Many existing solutions offer a generic, one-size-fits-all approach, typically centered around drag-and-drop interfaces. While these tools may be convenient, they often limit the depth of customization needed for unique business use cases. Additionally, platforms that offer more advanced capabilities usually require adopting proprietary syntax, which can drive up costs by necessitating specialized expertise. Sisense, however, provides a powerful code-first analytics solution that eliminates these barriers. It allows your data teams to leverage their existing skills—such as SQL, Python, and R—to create highly customized insights that align with your organization's specific needs, without needing proprietary languages or systems. In this article, we’ll explore how you can use SQL specifically to create custom datasets and charts, and then embed those visualizations into your web applications. This workflow emphasizes the creator experience for data teams, empowering them to build meaningful, business-specific analytics that enhances your product’s value. Building Custom Datasets with SQL Let’s dive into a specific use case. Assume you have a commerce table with two key columns: `transaction_amount` and `transaction_status`. You need to analyze revenue, categorizing it by transaction type (e.g., paid, refunded, or canceled). This provides insight into gross revenue versus potential revenue lost to refunds or cancellations. Below is the SQL query used to create a custom dataset for this scenario: ( Dataset Git Repo ) ( Data Model Repo ) SELECT TO_CHAR(DATE_TRUNC('month', transaction_date), 'YYYY-MM') AS "Date", transaction_id, SUM(CASE WHEN transaction_status = 'Paid' THEN transaction_amount ELSE 0 END) AS "Actual Revenue", SUM(CASE WHEN transaction_status IN ('Refunded', 'Cancelled') THEN transaction_amount ELSE 0 END) AS "Potential Revenue" FROM commerce GROUP BY DATE_TRUNC('month', transaction_date), transaction_id; This query creates a monthly summary of actual revenue and potential revenue lost due to refunds or cancellations. After running the query, you can preview the output in Sisense and immediately take action. Integrating Custom Data into Fusion Once your custom table is generated, you have several options for incorporating it into your analytics workflow: Adding Notebooks Charts to Dashboards 1. Integrate within the Semantic Model: The SQL table can be added to Sisense’s semantic layer, allowing you to build charts and dashboards on top of it using Fusion Dashboards or Compose SDK. 2. Build Charts for Integration : You can also directly build charts based on this table and pass them to your frontend team for integration into customer-facing applications. Streamlining Developer Workflows with Compose SDK Once your data team has completed their SQL-based analysis and crafted custom datasets and charts, the true value lies in how effortlessly developers can integrate these insights into customer-facing applications. By publishing the dataset to the Fusion semantic model , developers can use Sisense’s command-line tools (like '@sisense/sdk-cli') to dynamically generate TypeScript representations of the dataset right within their IDE. This streamlined process ensures that the work done by analysts is directly accessible to developers, enhancing the builder experience by providing immediate access to the datasets. This integration allows developers to efficiently embed highly customized, meaningful analytics into the front end, ensuring that the insights are not only visually compelling but also tailored to the specific needs of the end users. Creating Custom Visualizations Once charts have been built in Sisense Notebooks and published to Fusion , developers can begin integrating them seamlessly into front-end web applications using the Compose SDK . This process allows developers to embed dynamic, SQL-powered visualizations directly into customer-facing apps. By leveraging the Compose SDK, developers can access the published charts, configure them for specific frontend needs, and ensure a consistent, interactive user experience. This workflow streamlines the integration of custom analytics into web apps, providing flexibility and efficiency for development teams of all varieties. This code snippet demonstrates how to integrate both a custom column chart and an existing dashboard widget into a React application using Sisense’s Compose SDK. The `ColumnChart` component displays actual and potential revenue by year, while the `DashboardWidget` component integrates a pre-existing Sisense dashboard into the application. DashboardWidget Sisense CLI import { ColumnChart, DashboardById, DashboardWidget } from '@sisense/sdk-ui'; import * as DM from "../notebooks-tutorial" import { measureFactory } from '@sisense/sdk-data'; function ComposeSDK() { return ( <> <ColumnChart dataSet={DM.DataSource} dataOptions={{ category: [DM.ActualVsPotential.transaction_date.Years], value: [ measureFactory.sum(DM.ActualVsPotential.actual_revenue, 'Actual Revenue'), measureFactory.sum(DM.ActualVsPotential.potential_revenue, 'Potential Revenue'), ], breakBy: [DM.commerce.age_range], }} styleOptions={{ height: 500, }} /> <DashboardWidget dashboardOid="66e8aa5e724a0b00338cbed9" widgetOid="66e8c716724a0b00338cbefc" /> </> ); } export default ComposeSDK; Conclusion: This code-first approach to analytics meets technical creators where they are, empowering data teams and developers alike. Whether your analysts are leveraging their existing skills in SQL through the powerful Notebooks environment or your developers are dynamically generating insights within their IDE using the Compose SDK , Sisense offers a seamless and collaborative experience. By enabling teams to work with familiar tools and environments, Sisense allows you to deliver powerful, tailored analytics that fit your organization’s unique needs. This fluid, creator-friendly workflow ensures that both data teams and developers can focus on building impactful insights without the friction of proprietary systems, enhancing the overall efficiency and creativity of your analytics solutions.
Sisense UserPosted 1 year ago
0
Troubleshooting
Resolving SystemError and Memory Allocation Issues in Notebooks

Resolving SystemError and Memory Allocation Issues in Notebooks Summary This article addresses common issues encountered when running notebooks, specifically SystemError and memory allocation errors. It provides step-by-step instructions to resolve these issues and ensure smooth notebook functionality. Main Content Step-by-Step Instructions to Resolve SystemError Identify the Failing Line: Review the notebook to locate the line in the configuration cell that is causing the SystemError. If the line has been commented out, uncomment it to identify the exact issue. Check for Syntax or Logic Errors: Ensure that there are no syntax errors in the notebook code. Verify the logic of the code to ensure it aligns with the intended operations. Run the Notebook Again: Execute the notebook to see if the SystemError persists. If a different error appears, proceed to the next section. Troubleshooting Memory Allocation Issues Identify the Memory Allocation Error: If the error message indicates that the output exceeds the resources allocated to the Notebooks Compute Instance, note the memory allocation details. Increase Memory Allocation: Access the Configuration Manager: Navigate to Admin > System Configuration / System Management > Configuration. Select the 5-click config menu > Warehouse-Service > notebooks.compute.defaultMemoryInMibs. Increase the memory allocation from the default value (e.g., 256 MiB) to a higher value (e.g., 1024 MiB). Verify the Changes: After increasing the memory allocation, run the notebook again to ensure the error is resolved. Additional Tips Testing with Real Data: When testing notebooks with real data, ensure that the sample size is manageable within the allocated resources. For larger datasets, consider increasing the memory allocation proportionally. Feedback and Support: If the issue persists, contact support with detailed information about the error and the context of its occurrence. Providing feedback on your experience can help improve the support process. Check out this related content: Academy Documentation
Vlad SolodkyiPosted 1 year ago
0