akaplan
Sisense Employee
Joined 09-21-2023
User Profile
User Widgets
Contributions
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.Re: Calculated Value Filter in Widget Error
Hi HQ_Dev_Prod​, I reproduced this on an internal machine and found the specific error message: OVER: only possible within the selection Since this relates to Window functions, I looked into Sisense's configuration and found this translation setting: applyMeasureGrouperOptimizationForWindowFunction It appears that it's intended to optimize or safeguard what might otherwise be highly resource-intensive formulas involving window functions. Disabling the toggle alleviates the error. I searched internally for further documentation on this config and haven't found anything so far, so I can't guarantee how else this might affect your deployment.Re: Count (Boolean)
Hi BalaR​, You're on the right track. Similar to your attempts, I would use a formula filter on Segment_A to accomplish this. You'll see these referred to as Measured Values in Sisense documentation. I created a sample file containing 500 participant ids and randomized Yes/No segment values, structured like this: I was able to build an indicator showing total count (and contribution %) of the records with Segment_A = "Yes" by using the following formula: (count([Participant Id]), [Segment_A]) / count([Participant Id]) The numerator pulls the total count of participant_id, with a filter on Segment_A = "Yes". You right-click the filter field (Segment_A) and select "Filter" to assign the filter member(s). The denominator pulls the total count of participant_id, without any additional filters. Let me know if this solves your issue, or if any clarification is needed (or if I've misunderstood the requirement!).Re: Filtered Measures Using Snapshots and Sisense Date Calculations
npatel_02 - how many inventory records are involved, and how many stock dates are relevant to the use case? The reason I ask is because a potential modeling solution involves pre-calculating the stock status for each product/date combination. It makes things quite straight-forward, but there are potential limitations depending on data volume / disk size. To limit the size as much as possible, the example would employ 3 tables: Date Dimension: unique list of dates, for dashboard filtering purposes Inventory: collection of products in inventory, along with dbt_valid_from and dbt_valid_to (like your attached sample data) Inventory Status: Cartesian product of date & inventory UID. This table contains Date, Inventory UID, and a calculated IsActive flag, which joins to the inventory table to check if the Date falls between dbt_valid_to and dbt_valid_from, producing a 1 or 0 for filtering purposes. Inventory Status would then serve as the key between your date dimension and inventory tables. Considerations: Size of Inventory Status table This is a supervised many-to-many, so you'd need to restrict the Date dashboard filter to single-select (not multi-select or range or anything like that). Ensure the widget(s) or dashboard are filtered to IsActive = 1 to return the desired inventory records If this approach isn't feasible, I do believe we could come up with something a bit more dynamic with a combination of scripting and/or BloX actions. But this may require a more formal engagement with our Field Engineering Services team.Re: Gradient Conditional Formatting In Pivot Tables
Hello angupta, you should be able to use the built-in Range coloring for this. You can find more information at this link. Note that you can choose between "step" and "gradient" coloring with this option. Let me know if you have any questions or do not see this as a coloring option in your pivot table(s).Re: Calculating average units for two years
Hello, you should be able to use Measured Values for this. In the widget, you can create a formula that is something like: ( DUPCOUNT([AccountId]), [Years in CreatedDate] ) And if you right-click [CreatedDate], you can apply a filter to select the appropriate year. This can be combined as needed for a larger run rate formula. ---- Alternatively, in the ElastiCube, you could create yearly custom columns like this: AccountCreated_2022 = CASE WHEN GETYEAR([CreatedDate]) = 2022 THEN 1 ELSE 0 END AccountCreated_2023 = CASE WHEN GETYEAR([CreatedDate]) = 2023 THEN 1 ELSE 0 END etc. You could then SUM() those columns within the widgets to use 2022 or 2023 counts within formulas. This logic could also be added to an import query directly, as opposed to creating custom columns. But this all depends on the exact way that your data is laid out.Using HTML for Style and Formatting in Pivots
Using HTML for Style and Formatting in Pivots Introduction A common use case within Pivot and Table widgets is to display a clickable hyperlink (see: this community post). However, the ability to render HTML within these widgets can be used for more than just links. Any HTML can be rendered, which enables a variety of styling and formatting options. In this post, we'll walk through an example project management use case in Sisense, which includes a Pivot containing project status information. In this example, each project has three health statuses: Customer, Technical, and Timeline. Each one exists as a separate column, with a widget script to color-code the statuses: Three Separate Status Columns widget.transformPivot({}, function(metadata, cell) { if (cell.value == 'Red') { cell.style = { color: 'red' } } else if (cell.value == 'Green') { cell.style = { color: 'green' } } else if (cell.value == 'Yellow') { cell.style = { color: 'yellow' } }; } ); Problem The product team has requested we combine the three statuses into a single column. Solution 1 One option is to create a dimension table in our data model that normalizes the three statuses into key/value pairs of StatusType and StatusValue, then create a concatenation of {StatusType}: {StatusValue}. The Pivot output might look something like this: Concatenated Key/Value Status Column You can see that there are two drawbacks: The Notes (and any subsequent columns) are duplicated, once for each Status We’ve lost the color coding and would need to elevate the complexity of our widget script to account for this. Solution 2 A second option, which is the subject of this article, is to use a combination of SQL and HTML to produce the combined Status column, which eliminates the duplication issue above (#1) and arguably makes our styling implementation much simpler (#2). Rendered HTML Status Column Here is an example of the SQL/HTML within the data model's custom column, which produces the HTML that is rendered in the Pivot. '<b><p style="font-size:12px">Customer: ' + CASE WHEN CustomerStatus LIKE 'Red%' THEN '<font color="red">' WHEN CustomerStatus LIKE 'Yellow%' THEN '<font color="yellow">' WHEN CustomerStatus LIKE 'Green%' THEN '<font color="green">' ELSE '<font color = #black' END + CustomerStatus + '</font><br>' + 'Technical: ' + CASE WHEN TechnicalStatus LIKE 'Red%' THEN '<font color="red">' WHEN TechnicalStatus LIKE 'Yellow%' THEN '<font color="yellow">' WHEN TechnicalStatus LIKE 'Green%' THEN '<font color="green">' ELSE '<font color = black' END + TechnicalStatus + '</font><br>' + 'Timeline: ' + CASE WHEN TimelineStatus LIKE 'Red%' THEN '<font color="red">' WHEN TimelineStatus LIKE 'Yellow%' THEN '<font color="yellow">' WHEN TimelineStatus LIKE 'Green%' THEN '<font color="green">' ELSE '<font color = black' END + TimelineStatus + '</font></p></b>' Reminder: The configuration option Allows rendering Pivot Table content as HTML must be enabled within the Admin menu. The link at the beginning of this post describes the process in more detail. Keep the size of the table and text field in mind, to avoid negatively impacting build or query performance. This is just one small example of employing HTML for a stylistic use case, as opposed to a clickable hyperlink.
