himanshu_negi
Sisense Employee
Joined 4 years ago
User Profile
User Widgets
Contributions
Re: Issue creating tables via POST/datamodels/{datamodelId}/schema/datasets/{datasetId}/tables
Hello, It seems the issue might be the payload which would need further analysis. Meanwhile, if you prefer you can try adding the table using this below SDK method. https://github.com/sisense/pysisense/blob/main/examples/datamodel_example.md#:~:text=Example%209%3A%20Create%20Table Thanks,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.Automated Machine Learning with Sisense Fusion: A Practical Guide
Automated Machine Learning with Sisense Fusion: A Practical Guide In this article, we’ll explore how using Sisense and AutoML (Automated Machine Learning) can simplify the process of applying machine learning to real-world business problems. AutoML takes care of tasks such as data preprocessing, model selection, and hyperparameter optimization without requiring deep expertise in machine learning. Let’s dive into some practical business challenges where machine learning can make a significant impact. Understanding the Business Use Cases To illustrate how machine learning (ML) solves business challenges, we’ll look at two real-world use cases: Optimizing Inventory for a Popular Retail Product (Regression Problem): Imagine a popular clothing store trying to manage stock for a trendy item that frequently sells out. By applying machine learning, the store could predict the future demand for this product. This is an example of a regression problem, where the model forecasts continuous values—such as the number of items to stock—based on historical sales patterns, seasonal trends, and customer behaviors. This allows the store to optimize its inventory, avoid shortages, and maximize sales, demonstrating the power of machine learning to enhance operational efficiencyto stock) based on historical data and other influencing factors. Improving Customer Retention in Subscription Services (Classification Problem): For subscription-based businesses, predicting customer churn is essential. By analyzing data such as usage patterns, customer engagement, and support history, machine learning can predict whether a customer is likely to cancel their subscription. This classification model enables businesses to proactively target at-risk customers with personalized offers or support, helping to improve retention and customer satisfaction. The predictive power of machine learning transforms how businesses engage with their users, reducing churn and increasing long-term loyalty. Difference Between Regression and Classification Regression models are used to predict continuous values, such as quantities (e.g., how many products to stock) or prices. Classification models are used to predict categorical outcomes, such as Yes/No (e.g., whether a customer will churn) or other distinct categories (e.g., fraud/no fraud). Hands-On: Using Sisense Fusion and AutoML Everything demonstrated in this video, including the integration of machine learning models, is powered by Sisense Fusion’s native APIs and features. The web app is simply a wrapper that adds a shiny interface, but all actions performed in the demo—whether selecting data models, training machine learning models, or making predictions—can be done entirely within Sisense Fusion’s native platform without the need for external code or API calls. The power of Sisense Fusion lies in its seamless ability to manage and integrate these machines' learning tasks natively, making it easy for users to build, deploy, and interact with models without needing deep technical expertise or external integrations. The web app just provides a visually engaging way to demonstrate the capabilities of the Sisense Fusion platform. Step 1: Selecting the Data Model and Dataset In the first part of the video, we use the web app that leverages Sisense Fusion’s native API features to select the data model we want to work with. Here’s a breakdown of what happens: Selecting a Data Model: After starting the app, we selected the data model available in Sisense that contains our data. Choosing the Dataset: Once the data model is selected, the app displays all the tables or datasets contained in that model. We then select the dataset we want to train the machine learning model on. Target Variable Selection: After selecting the dataset, the app presents all the columns within the dataset. We select the target variable (the column we want to predict). For example, in customer churn prediction, this could be the Exited column, which indicates whether a customer has churned (1) or not (0). Selecting the Prediction Type: Next, we select whether the task is a regression or classification problem, based on the target variable. Since we are predicting customer churn, we select a classification. Storing Information: Once all selections are made, the app stores this information. This data will later be used when we select the machine learning model for training. Step 2: Exploratory Data Analysis (EDA) After submitting behind the scenes, a Flask application generates an Exploratory Data Analysis (EDA) report based on the dataset. This report provides important insights, such as: Number of customer records. Missing values in the dataset. Relationships between variables. These insights help us select relevant columns to ensure the machine learning model performs optimally. Step 3: Model Training Options We can have multiple options for training our model within Sisense Fusion for example: Auto-Sklearn: This is an open-source AutoML library that automates the model training process. Since it runs locally within Sisense, data never leaves the platform, ensuring data security. However, model training can be computationally expensive, meaning your Sisense cluster should have adequate resources to handle it. AWS Autopilot: This option leverages Amazon Web Services (AWS) infrastructure to train the model, offering more reliable performance. However, it incurs additional costs and requires your data to be sent to AWS for processing. After selecting the model training method, the process begins automatically, and you’ll see the status on the screen as it progresses. Part 4: Integration with AWS SageMaker and Dashboard Creation After selecting the AWS option for model training in the web app a new Custom Code Table is added to the Sisense data model. This Custom Code Table automates the training and deployment of the machine learning model using AWS SageMaker Autopilot. Here’s how it works: Input Parameters for the Notebook The custom code notebook contains a set of input parameters that are passed based on the selections made earlier in Part 1 (dataset and target column). Other parameters include: Dataset: The table you selected for model training. Target Column: The data column you want to predict (churn, in this case). Drop Feature: Columns you wish to exclude from model training (optional). AWS Credentials: Paths to AWS access keys and secret keys to authenticate with AWS. S3 Bucket Name: A unique S3 bucket where the dataset is stored for training. AWS Role ARN: The role with the necessary permissions to access S3 and SageMaker. The notebook code reads these parameters and uses them to call the AWS SageMaker Autopilot API, which automates the model training and deployment process. The trained model is deployed as an endpoint on SageMaker, allowing for online predictions. Creating a Blox widget with dynamic input fields The custom code notebook also contains code that dynamically creates a Sisense dashboard and a Blox widget based on the dataset selected for model training. Here’s what happens: Dynamic Input Fields: Based on the feature columns in the dataset, the Blox widget dynamically generates input fields (boxes) for each feature. This is crucial for online predictions, as it allows users to input new data for the model to predict outcomes in real time. Predict Button: A predict button is added to the widget. When a user inputs new data into the input boxes and clicks the predict button, the system requests the SageMaker endpoint, passing the input data. The model processes this data and returns the prediction, which is displayed in the widget. This setup enables real-time, online predictions directly from the Sisense dashboard, with predictions being powered by the AWS SageMaker endpoint. The dynamic nature of the widget allows the interface to adjust based on the dataset used for training, making the system flexible and user-friendly. The custom code table outputs key information about the trained model and its deployment status, which includes the following columns: Model Name: The name assigned to the trained machine learning model. Metric Name: The evaluation metric used to assess the model's performance, such as accuracy, precision, or recall. Score: The metric score that indicates how well the model performed during evaluation. Local Path: The path within the Sisense environment where the model is stored. Model S3 Location: The S3 location where the trained model is saved after deployment. AWS Model Name: The name of the model is registered in AWS SageMaker. Endpoint Name: The name of the deployed SageMaker endpoint used for making real-time predictions. This output allows users to track key details about the model, including where it's stored, its performance, and the endpoint used for predictions. Step 5: Saving Model Versions The notebook not only trains the model but also saves important metadata within Sisense’s file management storage. This allows you to maintain version control over your models. For each model training session, I store details such as the model metrics (accuracy, precision, etc.) and save each model in a folder based on the timestamp. This ensures easy traceability and allows for multiple versions of models to be stored and retrieved as needed. Step 6: Making Predictions Once the model is trained, we move on to predictions. There are two ways to handle predictions: Batch Predictions (Offline): This method allows you to process thousands of records at once. It's suitable for scenarios where real-time predictions are not required, and predictions can be generated in bulk. Online Predictions (Real-Time): For real-time applications, you can provide individual customer records and receive immediate predictions. This is ideal for real-time decision-making, such as predicting whether a new customer will churn based on their current attributes. Online Predictions (Real-Time): When a custom code table was built it automatically generated a Sisense dashboard and a Blox widget based on the input features used during model training. This integration allows predictions to be embedded directly into Sisense dashboards, enabling users to interact with the model seamlessly. Here’s how it works: The Blox widget takes the input data from the user and sends an API request to Sisense’s custom code transformation. In the case of Auto-Sklearn, the pre-trained model is loaded locally within Sisense, as the model was trained and stored in the local environment. For AWS SageMaker, instead of loading a local model, the system sends a request to the SageMaker endpoint (where the model is deployed) for predictions. The prediction results, whether generated locally with Auto-Sklearn or through the SageMaker API, are returned to the dashboard and displayed within the Blox widget in real-time. This process ensures that predictions are fully integrated into the Sisense environment, providing an interactive and real-time experience for users, with the flexibility to use either local or cloud-based models depending on their needs. Conclusion Sisense Fusion, combined with AutoML, offers an efficient and powerful way to integrate machine learning into real-world business applications. Whether using Auto-Sklearn for local, cost-efficient model training or AWS Autopilot for cloud-based scalability, Sisense provides seamless version control and easy integration into dashboards, making it a comprehensive platform for automating machine learning at scale. If you’re interested in integrating this solution into your Sisense deployment, please reach out to your dedicated Customer Success Manager (CSM) for further assistance. Related Content: https://docs.sisense.com/main/SisenseLinux/ai-overview.htm https://academy.sisense.com/gen-ai Related Content:Predictive analytics with AutoML for time-series forecasting using Custom Code
Sisense forecasting relies on Sisense’s Cloud Service and machine-learning algorithms. With Sisense, users have the ability to forecast based on historical data in the Sisense widget. Using Custom Code Notebooks, a user can define their input parameters based on their data and train their machine learning model for use as predictive analytics locally meaning without sending the data to Sisense’s Cloud Services.Ensuring Elasticube is in Sync with Data Source (Deletion & Updates in Accumulate Build)
Capturing accurate business transactions is imperative in understanding business operations. If data is constantly changing, such as being deleted or updated, how do you ensure your Elasticube is in sync with your data source? Depending on the use case, sometimes “Replace All” data could be an option, but that might not be the most efficient option if you have a huge amount of data. You also have “Accumulated By” as an option, but that could result in duplicated records and deleted records still being shown in Elasticube. Given these potential results, another path you should consider is a custom code solution. Custom Code Solution Leverage the demo below to learn more. Note in the demo, we will be using an oracle database with a transaction table. The transaction table has an updated_date column that captures the latest data changes and an audit table where deleted transaction IDs are stored with timestamps via a trigger. Please note, all tables in the demo contain dummy records. You can find the DDL for table and trigger here. Using custom code to create new tables in Elasticube will allow the Elasticube to be in sync with your data source table. This new table will only show the latest records, while also removing any deleted records via trigger. The audit table will capture deleted records. (NOTE: Adding triggers could impact database performance as every time a record is deleted there needs to be a write operation). Additionally, this custom code solution provides the ability for change data capture (CDC) to answer business questions like at what intervals the record was updated and/or deleted via the audit table. Let’s begin by building an Elasticube. Use updated_date as “Accumulated By” for both the tables and build the Elasticube by selecting BY TABLE. After this, we will add Custom Code that will contain all the logic required to obtain the latest records and also remove any deleted records. Step 1: Create tables, triggers, and records in Source Database. Sample code is available here. Step 2: Create Elasticube by adding both tables and selecting deleted_on column as accumulated by for audit table and updated_on for fact_sales table. Then build using “BY Table” (Note: The audit table will be empty since we haven’t deleted any records yet) Step 3: Create Custom Code Table If not enabled, go to Admin > Feature Management > Custom Code and save. Select Create your own Notebook In the input parameter, add both base tables, making sure you select the correct parameters and add columns for the output schema. Then hit the open code editor to open a jupyter notebook in a new tab where you will be able to add the logic. Once the jupyter notebook is open, clear all the cells and copy-paste the code from the .ipynb file from the repo. Make changes to the notebook based on your use case. For example, if the Elasticube and table names are different, provide them in cell 2. For this example, the date_change_threshold is the parameter we are choosing to restrict. Save the notebook, return to the previous tab, select the table for input parameters, and click done to create a new Custom Code table in Elasticube. First Build Changes Only which will create the data for the Custom Code Table Step 4: Insert a new record, update an old record, and/or delete a record from the Fact Sales table in your data source. The update trigger will change the Updated_On value for id 9 while the delete trigger will create a new record in the Audit Table Step 5: Build the Elasticube by selecting “By Table”. Now, in the Elasticube you have a fact_sales table which contains all the history/changes of the record and the Custom Code table which has the latest record that are in sync with the data source table. Example below of Table capturing all data changes: Example below of Table in-sync with data source: These steps should result in accurate data in your Elasticube leading to smarter business decisions. Please note, this is one possible workaround for users with similar use cases. Keep in mind that editing the jupyter code is another possibility. For example, you can try to use dask dataframe instead of pandas and see which performance is better with your data. 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 own 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 not covered by Sisense warranty and support services.
