Sisense Community

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

Categories

Elasticube

How-Tos & FAQs
LAG / LEAD Functions

Question LAG & LEAD Functions are functions that give access to multiple rows within a table, without the need for a self-join. The LAG function is used to access data from a previous row, while the LEAD function is used to return data from rows further down the result set. A perfect example of these functions in use is with the “Sales Order” scenario. A department store is trying to monitor the purchase trends of its customers; more specifically, per customer, what’s the average time it took to repeat an order? For us to do so, we must take a record in the table (Order Date) and compare it to another record in the same table. What are some ways we can do that? In our example, we will analyze & compare the efficiency between the multiple solutions. We will start by addressing the problem in the most common/traditional of solving a problem like this. Answer Steps: 1. Create A New ElastiCube Add Data > Microsoft SQL Server > Connect To Server Pull in Sales Order Header Table "How are we going to manipulate that table to get it into a format that we would want to work with?" Uncheck fields that you don’t need; looking for high-level/important data BUILD! 2. Let's Create A Custom SQL Expression What we are getting here a single customer’s activity. It also orders the Order Date in ascending order (from first date to last) "For this specific customer, I want to be able to see __ amount of days between each order. How can we compute / compare those dates? In order to do so, we want to take the order table and join it to itself, where the customer is the same and the order date is offset to the previous or future date." So, let’s start by leveraging the rank function to assign an order number to every date… The Rank function is important here because within the Customer ID, we want to rank based off the order date, group it per customer, and order by the order date. This basically creates create a system that makes it so that it is comparing itself to itself minus 1. This is what sets up our ranking per order. In order for us to join the table to itself, let’s create a sub query and inside of it, put the entire Customer’s table: (Type Of Join:) What it’s getting joined on: Result: 3. Now That We Have The Order Date & The Total Due, We Can Start Subtracting Them Off Each Other & Start Doing Differences Now you would want to go back to the top; the first “Select” of the sub query and change the claims to: Preview: Now that we saw that this function is working, we can now delete our customer filter, order by filter. Now we get all the differences across all of the customers. Issue With This Solution The main issue with this solution is that it requires a lot of processing due to the two tables. Although we are really using one table, we still have two and the same data is repeated between them. This extra storage isn’t necessary, creates the long processing, and can also create confusion for the customer. Solution 2: Doing A Lookup Function On Itself (Own Data Set). To improve upon the last solution, is there a way to pull in one table and do the manipulation in Sisense, without creating a second table? Answer: Yes! What we can do is take the custom table and add it as a view to the source; that way we only import one table Steps: 1. Duplicate "SalesOrderHeader 1", Rename "SalesOrderHeader 2" BUILD! For us to do a look up on this table, we need to have need to have one key to join. The key is a combination of the customer ID + ranking, that’s what makes it unique. 2. So First, Create A New Field, Record_Rank (Int), Input: BUILD Add Data > Custom SQL Table 3. Head Back Into SalesOrderHeader 2, And Set Up The Different Fields To Create The Key + New Field > CurrCustomerRankKey (Text) Do Entire Build 4. Let's Create A Test Function + Custom SQL Expression > Test Function 5. Go Back To SalesOrderHeaders 2 Table, Do Lookup In There + New Field > PrevOrderDate (Date-Time) + New Field > PrevTotalDue (Float) + New Field > DaysBetweenOrders(Int) + New Field > DiffOrderAmount Schema Build: Solution 3: LAG & LEAD Functions This solution does not include a self-join Steps: 1. Open Up SQL Server (TC SQL) Go to same database (Adventure Works 2012) > create new query Let’s start off the new query as: Now we want to dynamically compare one against the other. Instead of doing a self-join, it tells itself to look at the look at the record/number that is above or below and just pull that value in. So it’s almost like a lookup , but it doesn’t require a join , it just requires you to tell it how to sort the data and just dynamically go up and go down and pull the field that you want in to the current record. This is where the LAG & LEAD functions step in To go back a record, we call the LAG function. Within the LAG function, we must define the field that we want to pull in (order date) and how many records back do you want to go (1). Next we want to group by (partition by) and Order By a type of variety and then order it either ASC / DES. To make our “look up” a bit more distinct we can Result: If we want to compare up against future dates, we would use: And its result would be: What we can also pull in the Total Due and calculate the previous Total Due: Which would require a sub query: To get of the first null, we would enter: Pull in everything from t2.* and now we can calculate our differences (Date Diff, etc): As for getting this entire query into Sisense, we want to create a View
Community_AdminPosted 4 years ago • Last reply 1 month ago
1
APIs
Connection Tool - Programmatically Remove Unused Datasource Connections, and List All Connections

Connection Tool - A Tool to Programmatically Remove Unused Datasource Connections, and List All Connections Managing connections within your Sisense server can become complex over time, if there are a large number of connections, and connections are often added, and replace earlier datasource connections. In some scenarios unused connections can accumulate, potentially cluttering the Connection Manager UI with no longer relevant connections. Although unused connections typically represent minimal direct security risk, it's considered best practice to maintain a clean, organized list of connections, and in some scenarios it can be desired to remove all unused connections. Sisense prevents the deletion of connections actively used in datasources, safeguarding your dashboards and datasources from disruptions. However, inactive or "orphaned" connections remain after datasources are deleted or a connection is replaced, potentially contributing to unnecessary UI complexity in the connection manager UI. Connections can be of any type Sisense supports, common types include various SQL connections, Excel files, and CSV files, as well as many data providers, such as Big Panda. This tool can also be used to list all connections, with no automatic deletion of unused connections. Introducing the Sisense Connection Prune Tool The Sisense Connection Prune Tool is a Python-based Sisense API based tool designed to programmatically identify and delete unused connections. It generates a CSV report listing all connections and their associated datasources, streamlining your connection management process. If desired, it can automatically remove all unused connections automatically from a Sisense server. Using the Tool, sourcing the Virtual Environment, generating the Connection CSV CSV Output of Used Connections and Associated Datasources CSV opened visually to view as Table, Excel and other programs and text editors can open CSV files Sisense Connection Prune Tool README Here's the full README included with the tool: # Sisense Connection Prune Tool A command-line tool to list used data connections and prune unused Sisense connections via CSV. It allows you to generate a CSV file of all connections and their dependencies, then delete those connections if needed, after removing the connections to keep from the CSV. ## Features - **Dry Run Mode**: Simulate deletions without making any changes. - **CSV-Based Flow**: Easily inspect and list connections to remove before deletion. - **Logging**: Extensive logs if needed. - **Error Handling**: Clear and descriptive messages for issues encountered during execution. ## Usage 1. **Activate the Virtual Environment** After downloading this project folder, activate the Python virtual environment bundled with it that includes all Python dependencies. - **Windows**: `venv\Scripts\activate` - **macOS/Linux**: `source venv/bin/activate` 2. **Configure the Tool** Open the `config.yaml` file and set your Sisense server URL, bearer token, CSV file path, and log file path. For example: ```yaml server_url: "https://your.sisense.server" bearer_token: "your_bearer_token_here" dry_run: true csv_file_path: "connections.csv" log_file_path: "connection_tool.log" ``` - **server_url**: The URL of your Sisense instance. - **bearer_token**: Your Sisense API token for authentication. - **dry_run**: If set to `true`, deletions will be simulated (no real deletions). - **csv_file_path**: Where the CSV file should be created and read from. - **log_file_path**: Where log file will be stored. 3. **Run the Tool** ```bash python3 ConnectionPruneTool.py ``` You will be prompted to choose an option: 1. **Generate connection CSV** - Fetches all Sisense connections. - Immediately removes (or simulates removing, if `dry_run` is `true`) any connection with no dependencies. - Writes all remaining connections and their dependencies to the CSV file. - **Important**: Inspect the CSV file and remove lines for any connections you want to **keep**. 2. **Delete connections from CSV list** - Reads the CSV file. - Removes or simulates removing each connection still listed. - Provides a summary report of which connections were deleted or bypassed. 4. **Review the Logs** Check the file specified in `log_file_path` for a record of all actions taken or simulated if needed. This is helpful for understanding what happened during each run and diagnosing any issues. ## Example Workflow 1. **Generate CSV** ```bash python3 ConnectionPruneTool.py # Choose option 1 when prompted ``` After generation, open the CSV file and **delete rows** corresponding to any connections you want to **keep**. 2. **Delete Connections** ```bash python3 ConnectionPruneTool.py # Choose option 2 when prompted ``` The tool will read the CSV and delete the remaining listed connections (or simulate deletion, if `dry_run` is enabled). ## Notes - Unused connections are removed automatically in step 1, without a CSV step - To keep a connection, remove its line from the CSV before proceeding with deletion. - If `dry_run` is set to `true`, no actual deletions will occur, only simulated logs and printed messages. - The log file will be cleared at the start of each run, so be sure to review or archive logs (or change log file name in config), if needed. This is a command-line tool to list used data connections and prune unused Sisense connections, in general and via a CSV list. It allows a user with a data admin or higher bearer token to generate a CSV file of all connections and their dependencies, then delete those connections if needed, from the remaining connections in the CSV. Example Output: Deleted unused connection: Old_DB_Connection (ID: 123abc) CSV file generated at connections.csv. It contains 25 row(s) of active connections. Please review and remove lines for connections you want to keep before running deletion step by running tool again. Remaining lines will be deleted in deletion mode. Summary Report: Total lines in CSV (active used connections): 25 Deleted Unused Connections: - Old_DB_Connection No connections were bypassed. API Endpoints Used Retrieve connections: GET /api/v2/connections Retrieve dependencies: GET /api/v2/connections/{connection_id}/getAllDependencies Delete connection: DELETE /api/v2/connections/{connection_id} Full Code connections.py - Uses Sisense API endpoints to: Fetch all connections (GET /api/v2/connections). Retrieve datasource dependencies for a specific connection (GET /api/v2/connections/{connection_id}/getAllDependencies). Delete a specific connection (DELETE /api/v2/connections/{connection_id}). from helperFunctions import load_config, api_get, api_delete config = load_config() headers = {"Authorization": f"Bearer {config['bearer_token']}"} base_url = config["server_url"] # Retrieve all connections from Sisense def get_all_connections(): endpoint = f"{base_url}/api/v2/connections" return api_get(endpoint, headers) # Retrieve dependencies of a specific connection def get_connection_dependencies(connection_id): endpoint = f"{base_url}/api/v2/connections/{connection_id}/getAllDependencies" return api_get(endpoint, headers) # Delete a specific connection def delete_connection(connection_id): endpoint = f"{base_url}/api/v2/connections/{connection_id}" return api_delete(endpoint, headers) helperFunctions.py - Uses the Requests library to handle API requests (GET/DELETE). Catches and logs API errors. Uses PyYAML to read the config.yaml file for configuration. import yaml import requests import logging # Load configuration from YAML file def load_config(): with open("config.yaml", "r") as file: return yaml.safe_load(file) # Configure logging settings def setup_logging(log_file): logging.basicConfig( filename=log_file, level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s", ) # Perform a GET request with error handling def api_get(endpoint, headers): try: response = requests.get(endpoint, headers=headers, verify=False) response.raise_for_status() return response.json() except requests.RequestException as e: logging.error(f"GET request failed: {e}") print(f"Error during GET request: {e}") return None # Perform a DELETE request with error handling def api_delete(endpoint, headers): try: response = requests.delete(endpoint, headers=headers, verify=False) response.raise_for_status() return response except requests.RequestException as e: logging.error(f"DELETE request failed: {e}") print(f"Error during DELETE request: {e}") return None ConnectionPruneTool.py - Serves as the entry point for the entire tool. Implements a CLI for user interaction. Invokes connections.py and helperFunctions.py to retrieve, analyze, and optionally delete Sisense connections. Uses Pandas to build a DataFrame of connection details and writes the results to a CSV file. Reads back the CSV file to remove selected connections after manual edits. Manages logs and prints summary reports to the terminal. import pandas as pd import logging import os from connections import ( get_all_connections, get_connection_dependencies, delete_connection, ) from helperFunctions import load_config, setup_logging # Load config yaml config = load_config() setup_logging(config["log_file_path"]) # Clear log file at start open(config["log_file_path"], "w").close() dry_run = config["dry_run"] csv_path = config["csv_file_path"] def generate_connections_csv(): """ Retrieves all connections from Sisense, deletes any that have no dependencies (unless in dry_run mode), and writes the remaining used connections and their dependent data models to a CSV file. """ # Create or clear existing CSV open(csv_path, "w").close() connections = get_all_connections() if connections is None: logging.error("Failed to retrieve connections.") print("Failed to retrieve connections.") return data = [] deleted = [] bypassed = [] # Go through each connection returned from the API for conn in connections: dependencies = get_connection_dependencies(conn["oid"]) if dependencies is None: logging.warning( f"Failed to retrieve dependencies for: {conn['name']} ({conn['oid']})" ) print( f"Failed to retrieve dependencies for: {conn['name']} ({conn['oid']})" ) bypassed.append(conn["name"]) continue # If no dependencies, optionally delete the connection (not in dry-run) if not dependencies: action_msg = "[Dry Run] Would delete" if dry_run else "Deleted" log_str = ( f"{action_msg} unused connection: {conn['name']} (ID: {conn['oid']})" ) print(log_str) logging.info(log_str) if not dry_run: try: response = delete_connection(conn["oid"]) # If response is None, treat it as a failed deletion if response is None: logging.error( f"Failed to delete unused connection: {conn['name']} ({conn['oid']})." ) print( f"Error deleting unused connection: {conn['name']} ({conn['oid']})" ) bypassed.append(conn["name"]) else: deleted.append(conn["name"]) except Exception as e: logging.error( f"Exception while deleting unused connection: {conn['name']} ({conn['oid']}) - {e}" ) print( f"Error deleting unused connection: {conn['name']} ({conn['oid']})" ) bypassed.append(conn["name"]) else: # If the connection is used, add each dependency row to CSV. # Use a fallback value if 'title' or 'oid' is missing. for dep in dependencies: dep_title = dep.get( "title", "NULL TITLE Share Datasource with associated Bearer Token user to include in CSV", ) dep_oid = dep.get("oid", "NULL OID") data.append( { "Connection Name": conn["name"], "Connection ID": conn["oid"], "Elasticube/Data Model Name": dep_title, "Elasticube/Data Model ID": dep_oid, } ) # Create a DataFrame of only the used connections (with datasource dependencies) df = pd.DataFrame(data) df.to_csv(csv_path, index=False) row_count = len(df) logging.info(f"Generated CSV at {csv_path}") print( f"CSV file generated at {csv_path}. " f"It contains {row_count} row(s) of used connections.\n" "Please review and remove lines for connections you want to keep " "before running deletion step by running tool again. Remaining lines will be deleted in deletion mode." ) # Summaries for auto-deleted (unused) connections print("\nSummary Report:") print(f"Total lines in CSV (active used connections): {row_count}") if deleted: print("\nDeleted Unused Connections:") for d in deleted: print(f" - {d}") else: print("\nNo connections were deleted in this step.") if bypassed: print("\nBypassed Connections:") for b in bypassed: print(f" - {b}") else: print("\nNo connections were bypassed in this step.") def delete_connections_from_csv(): """ Reads the CSV (remaining lines after user review, removing lines for connections to keep), then deletes each connection listed. If a delete fails or returns None, the connection is logged and added to 'bypassed'. """ if not os.path.exists(csv_path): print( "CSV file does not exist. Please generate it first or fix the config path." ) return df = pd.read_csv(csv_path) deleted = [] bypassed = [] for _, row in df.iterrows(): conn_id = row["Connection ID"] conn_name = row["Connection Name"] action_msg = "[Dry Run] Would delete" if dry_run else "Deleted" log_str = f"{action_msg} connection: {conn_name} ({conn_id})" print(log_str) logging.info(log_str) if not dry_run: try: response = delete_connection(conn_id) if response is None: logging.error( f"Failed to delete connection: {conn_name} ({conn_id})" ) print(f"Error deleting connection: {conn_name} ({conn_id})") bypassed.append(conn_name) else: deleted.append(conn_name) except Exception as e: logging.error( f"Exception while deleting connection: {conn_name} ({conn_id}) - {e}" ) print(f"Error deleting connection: {conn_name} ({conn_id})") bypassed.append(conn_name) # Print summary report print("\nSummary Report:") if deleted: print("Deleted Connections:") for d in deleted: print(f" - {d}") else: print("No connections were deleted.") if bypassed: print("\nBypassed:") for b in bypassed: print(f" - {b}") else: print("No connections were bypassed.") if __name__ == "__main__": # If there is no CSV file found, select step 1 automatically if not os.path.exists(csv_path): print( "No CSV file found; defaulting to generating connections CSV. " "Correct config if CSV file name has changed." ) generate_connections_csv() else: choice = input( "Choose an option:\n" "1 - Generate connection CSV\n" "2 - Delete connections from CSV list\n" "Enter your choice (1 or 2): " ) if choice == "1": generate_connections_csv() elif choice == "2": delete_connections_from_csv() else: print("Invalid option. Please enter '1' or '2'.") Conclusion By automating the detection of inactive connections and simplifying their removal, the Sisense Connection Prune Tool reduces clutter in the Sisense server Connection Manager UI while minimizing the risk of unintentionally impacting active datasources. Whether you opt for a dry-run mode to review potential deletions in a generated CSV file, or to simply list connections, or proceed with the full removal of unused connections, this tool offers a clear, flexible, and reliable approach to keeping your connections organized. A full copy of the tool, is attached below.
Jeremy FriedelPosted 1 year ago • Last reply 4 months ago
4
APIs
UserReplaceTool - Automating Dashboard Ownership Transfers - Useful for Deleting User Accounts

Automating Dashboard Ownership Transfer in Sisense with UserReplaceTool Managing and deleting user accounts in Sisense can create manual processes when users leave an organization or change roles. A frequent issue is the reassignment of dashboard ownership to prevent losing Sisense dashboards when a given user account is deleted, as deleting a Sisense user will delete all dashboards owned by that user. The UserReplaceTool addresses this task by automating the transfer of dashboard ownership of all dashboards owned by a given user, ensuring continuity and data integrity. Overview UserReplaceTool is a Python-based, API-based Tool solution designed to seamlessly transfer the ownership of dashboards and data models from one user to another in Sisense. This tool simplifies and automates this process, allowing organizations to reassign dashboard ownership without manual processes or the risk of losing dashboards and widgets. All components are accomplished by using Sisense API endpoint requests. Key Features Automated Dashboard Transfer : Reassigns ownership of all dashboards from the current user to a designated replacement user. Data Model Sharing : Ensures that all data models accessible and editable by the previous user are shared with the replacement user. Batch User Processing : Capable of handling multiple user transfers to a replacement user in a single operation, enhancing efficiency. Complete Logging: All dashboards and datasource transferred are logged both in the console and in a separate log file. Setup Instructions 1. Setting Up the Environment To run the tool within a Python virtual environment , follow these steps: Activate the Python Virtual Environment : source /venv/bin/activate Create a Virtual Environment (if not already present): python3 -m venv .venv Install Dependencies : pip3 install -r requirements.txt For manual installation, including without using a Python virtual environment: pip3 install urllib3 jsonpath_ng pyyaml requests colorama 2. Configuration Edit the settings.yaml file to configure the tool. Key parameters include: Sisense Domain and Port : Specify the URL and port of your Sisense server, which is used for making API requests. API Bearer Token : Provide an admin-level bearer token for API authentication. See the Sisense API Bearer Token Documentation for instructions on generating and using Bearer Tokens. Users to Replace : List the user IDs to be replaced. User IDs can be retrieved via the Users API or using the console command prism .user._id. Replacement User : Specify the user ID of the new replacement owner of all dashboards. This should typically be an admin level user. Data Model Sharing : Enable or disable the sharing of data models with the Replacement user (True or False), usually True. Dashboard Ownership Transfer : Enable or disable the transfer of dashboard ownership (True or False), usually True. Unlike Dashboards, if a user is deleted, the datasources themselves are not deleted from the server, ownership is automatically transferred to the main System Admin of the Sisense server. 3. Running the Tool Run the tool with the following command: python3 replaceUser.py Practical Considerations Admin and Network Access : Ensure you have admin-level API access to the Sisense instance. Python Environment : Python3 and pip should be installed on the machine running the tool. All dependencies can be installed using Pip, and a Python virtual environment can be used. Once a Python Virtual Environment folder is set up it can be shared with the Tool and run directly on all systems using the same OS, but it is not cross OS compatible. By automating the transfer of dashboard ownership, UserReplaceTool provides a reliable and efficient solution for managing user transitions in Sisense. This ensures that datasources and dashboards remain accessible and under the control of the appropriate users, maintaining the continuity of Sisense resources. For further customization and configuration details, refer to the attached full Python Tool, which includes a README file and modify the "settings.yaml" file as necessary.
Jeremy FriedelPosted 1 year ago • Last reply 4 months ago
3
Help and How-To

kgupta_egencia
Ecube build SQL tagging

Hello, We are working with snowflake data source and have requirement to capture meta for ecube build query from data source. Please suggest how I can set query tagging ? SET QUERY_TAG = "{Team_Name:TEAM, Application:Sisense ecubename}" I did check pre build plugins but did not get option to set data source session settings or initial sql settings in python script. Please suggest.
5 months agolast reply 5 months ago
2
Help and How-To

Tim von Ahsen
Does build failure leave orphaned queries?

My ElastiCube build failed. It was querying a SQL Server database at the time. I looked on that database after the failure: the build still had a query open. Is that normal? Does Sisense attempt to cancel queries as part of a build failure?
6 months agolast reply 5 months ago
2
Help and How-To

Tim von Ahsen
What does an ElastiCube put in RAM

I heard "An Elasticube, once built and set to the Querying state, is designed to keep its data in memory for fast query access." But I noticed that my cube is 90GB on disk, only 8GB in RAM after I refreshed one dashboard, and the RAM use goes jumped to 21GB when I opened a dashboard that queries a larger quantity of the cube's values. So what's in RAM and what remains on disk? Cached query results? Indexes? Just the rows that have been used since the last build? I'm curious because RAM use of large cubes is sometimes an issue; sometimes we redesign the cube to be smaller and sometimes we get more RAM. The more I understand, the better we can discuss and estimate.
6 months agolast reply 6 months ago
1
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

jt_ibp2
Semantic Layer tables stack up like a deck of cards

Hi All, About once a month, all of the tables in our semantic layer stack on top of one another. I'm not sure why. It takes me about an hour to put them back in their right places. Has anyone else had this problem, and if so, how did you stop it from recurring? Cheers.
9 months agolast reply 6 months ago
8
Blox
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.
kh-swathiPosted 1 year ago
0
Widget & Dashboard Scripts
Redirect users to different dashboards based on dashboard filters

Redirect users to different dashboards based on dashboard filters This article discusses and shares the full code of a dashboard script that redirects users to a different dashboard ID based on the user's filter selections or initial loaded filter state. In the particular example shared in this article, the script checks whether the selected date filter (either from a members filter or a filter date range) includes an earlier date than the earliest date in the current dashboard's data source. If this is the case, the script redirects the user to a specified alternate dashboard, preserving any additional URL segments and query parameters in the URL. Any other type of filter state can also be used to determine when the script should redirect, including non-date filters using similar scripts. Example Use Case Limited vs. Full Dataset : One dashboard data source might serve only recent data, while another dashboard data source stores all historical data. If a user selects a date that pre-dates one dataset, this script will seamlessly redirect them to the alternate dashboard that includes older data. Improved User Experience : Instead of displaying empty or invalid data for older dates, users are smoothly guided to the correct “all data” view dashboard. Full Script /** * Main function that checks a single-level date filter of day-level granularity * (either 'members' or 'from/to') for a single selected by variable date dimension. The dashboard script determines * whether the selected date is earlier than the earliest date in the current dashboard’s * datasource. If so, the function redirects to another dashboard while preserving the rest * of the URL parameters and path segments. * * Use Case: * - One dashboard has a datasource with only recent data (no older dates). * - Another dashboard has a datasource with all historical data. * - If a user selects a date earlier than the “recent” dataset supports, the script redirects * them to the “all data” dashboard, retaining any query parameters or path info in the URL. */ function checkAndRedirectIfNeeded() { /** * Toggle console logging for clarity or debugging. */ const enableLogging = true; /** * The dimension string for the date filter in the current dashboard's datasource. * Example: "[Commerce.Date (Calendar)]". * This code checks a single-level date filter of day-level granularity for this dimension. Change as needed */ const dateDim = "[Commerce.Date (Calendar)]"; /** * The alternate dashboard ID to which a user is redirected if they select a date * older than what the current datasource includes. This dashboard ID replaces the current * dashboard ID in the URL, preserving any extra path or query parameters. */ const alternateDashboardId = "67bebb6863199f002a7b0906"; /** * Logs to the console only if enableLogging is true. * @param {...any} msgs - Items to log. */ function log(...msgs) { if (enableLogging) { console.log(...msgs); } } /** * Searches the dashboard's filters for a single-level date filter matching dimStr. * Returns the filter object if found, otherwise null. * @param {string} dimStr - The date dimension to look for. */ function findDateFilter(dimStr) { if (!dashboard.filters || !dashboard.filters.$$items) { log("No filters accessible on this dashboard."); return null; } for (const filter of dashboard.filters.$$items) { if (filter.jaql && filter.jaql.dim === dimStr) { log("Found date filter for dimension:", dimStr); return filter; } } return null; } /** * Builds a minimal JAQL query to fetch the earliest date (sorted ascending) from the specified dimension. * Uses the current dashboard's datasource, and returns only 1 row (count: 1). * * This ensures we get the earliest available date in the dataset for the dimension in question, * typically returned as an ISO-like string in the response data. * * @param {string} dimStr - The dimension string representing the date field. */ function buildEarliestDateQuery(dimStr) { return { datasource: dashboard.datasource, metadata: [ { jaql: { dim: dimStr, datatype: "datetime", level: "days", sort: "asc" } } ], count: 1 }; } /** * Runs the JAQL query using Sisense's internal HTTP service if available, otherwise jQuery.ajax. * The request is made synchronous (async: false). If the internal service doesn't exist, fallback is standard AJAX. * * @param {Object} jaql - The JAQL query object. * @returns {Promise} - Resolves with the server response or rejects on error. */ function runHTTP(jaql) { const $internalHttp = prism.$injector.has("base.factories.internalHttp") ? prism.$injector.get("base.factories.internalHttp") : null; const ajaxConfig = { url: `/api/datasources/${encodeURIComponent(jaql.datasource.title)}/jaql`, method: "POST", data: JSON.stringify(jaql), contentType: "application/json", dataType: "json", async: false, xhrFields: { withCredentials: true } }; return $internalHttp ? $internalHttp(ajaxConfig, false) : $.ajax(ajaxConfig); } /** * Replaces only the dashboard ID in the current URL with alternateDashboardId, * preserving the rest of the URL (query parameters, path segments, etc.). * If the current dashboard ID is not found, the redirect is canceled. */ function redirectToAlternateDashboard() { const currentDashId = dashboard.oid; if (!currentDashId) { log("Cannot determine current dashboard ID. Redirect canceled."); return; } const currentUrl = window.location.href; if (!currentUrl.includes(currentDashId)) { log("Current URL does not contain the expected dashboard ID. Redirect canceled."); return; } const newUrl = currentUrl.replace(currentDashId, alternateDashboardId); log("Redirecting to alternate dashboard:", newUrl); // Perform the actual redirect window.location.href = newUrl; } /** * Main logic flow: * 1) Fetch the earliest date in the dataset for dateDim by building and running a JAQL query. * 2) Locate the single-level date filter for dateDim in the dashboard filters. * 3) Check the user's selected date: * - If 'members' type, use the first member. * - If 'from/to' type, use 'from'. * 4) Compare the selected date (JS Date) to the earliest date (JS Date). * 5) If selected date is earlier, redirect to the alternate dashboard; otherwise, do nothing. */ const jaqlQuery = buildEarliestDateQuery(dateDim); runHTTP(jaqlQuery) .then(response => { if (!response?.data?.values?.length) { log("No data returned for earliest date query. No redirect needed."); return; } // The earliest date is stored in .data, typically an ISO-like date string (e.g. "2023-01-05T00:00:00"). const earliestDateString = response.data.values[0][0].data; log("Earliest date in the dataset:", earliestDateString); // Convert the earliest date string to a JS Date object for comparison const earliestDate = new Date(earliestDateString); if (isNaN(earliestDate.valueOf())) { log("Earliest date is invalid or unrecognized:", earliestDateString); return; } // Find the single-level date filter on the dashboard const dateFilter = findDateFilter(dateDim); if (!dateFilter || !dateFilter.jaql.filter) { log("Date filter not found or missing filter object. Skipping redirect check."); return; } let selectedDateStr = null; // If it's a 'members' type filter, use the first member, this is the earliest if ( Array.isArray(dateFilter.jaql.filter.members) && dateFilter.jaql.filter.members.length > 0 ) { selectedDateStr = dateFilter.jaql.filter.members[0]; log("Filter type: 'members'. Selected date string:", selectedDateStr); // If it has 'from' (and possibly 'to'), use 'from' } else if (dateFilter.jaql.filter.from) { selectedDateStr = dateFilter.jaql.filter.from; log("Filter type: 'from/to'. Selected 'from' date string:", selectedDateStr); } else { log("Filter is not recognized or has no selected date. Skipping redirect."); return; } // Convert the filter's date string to a JS Date const selectedDate = new Date(selectedDateStr); if (isNaN(selectedDate.valueOf())) { log("Selected date is invalid or unrecognized:", selectedDateStr); return; } // Compare for earliest date if (selectedDate < earliestDate) { log("Selected date is earlier than the earliest dataset date. Redirecting..."); redirectToAlternateDashboard(); } else { log("Selected date is within or after earliest dataset date. No redirect needed."); } }) .catch(error => { log("Error retrieving earliest date or comparing filter date:", error); }); } /** * Calls the main logic function after the dashboard is initialized. */ dashboard.on("initialized", function () { checkAndRedirectIfNeeded(); }); /** * Also calls the main logic function whenever filters change. */ dashboard.on("filterschanged", function () { checkAndRedirectIfNeeded(); }); How It Works Earliest Date Lookup The script first builds a small custom secondary JAQL query (sorted in ascending order, limited to one result) to determine the earliest date in the data source. It executes this query using Sisense’s internal service function, using the native Sisense cookies. This type of scripting functionality has been discussed in more detail in previous knowledge base articles, such as this article . Filter Inspection After fetching the earliest date, the script searches for a single-level date filter matching the configured date dimension. If the filter uses a members array, the script reads the first member. If the filter uses a from and to range structure, it uses the from date, as this is always the earliest. Comparison The script converts both the earliest date and the selected date into JavaScript Date objects. If the selected date is earlier than the earliest date, the script triggers a redirect. Redirect Logic Only the dashboard ID portion of the current URL is replaced with the specified alternateDashboardId. Path segments, query parameters, and any other pre-existing parts of the URL remain the same. Use Cases Limited vs. Full Dataset When one Sisense dashboard is restricted to recent data (for example, the last 30 days) and another includes all historical data. This script diverts users to the appropriate dashboard seamlessly. Consistent User Experience Avoids confusion when a date filter extends beyond a limited dataset’s timeframe. Instead of showing empty or invalid data, the user is sent to a more comprehensive dashboard. Multi-Dashboard Navigation Ensures the user can continue with the same URL parameters and path segments, simply swapping the restricted dashboard for the alternate “all data” one. Conclusion By employing this or similar scripts based on this form of customization (this example is more advanced in using a custom secondary JAQL request to fetch the first data value, but this is not necessary in many scenarios), it is possible to streamline date-based filter navigation or other filter-based navigation between dashboards or data sources, guaranteeing for example that if a date selection falls outside the scope of a limited dataset, the user automatically sees data in a broader data source dashboard. This functionality improves user experience and allows for wide customizability. Example of a date selection including dates before the first date in dataset [A LT Text: A digital interface showing a table titled "Days in Date." Below the title, there are three dates listed: 11/26/20, 11/25/20, and 11/27/20. The date 11/26/20 is highlighted in yellow, along with a toggle switch on the right side of the image.] Example Output with logging variable enabled [ ALT Text: A screenshot of a computer terminal showing error messages related to date filters for a calendar dimension in a dataset. The messages indicate that a selected date of November 26, 2020, is valid while a date of November 25, 2020, is earlier than the earliest date in the dataset (November 11, 2020). The terminal displays a redirecting notice to an alternate dashboard.]
Jeremy FriedelPosted 1 year ago
0