Floating Precision Limitations
Sisense supports floating-point numbers (IEEE 754 standard) and allows you to perform an arithmetic calculation on these numbers. Floating-point numbers suffer from a loss of precision when represented with a fixed number of bits (e.g., 32-bit or 64-bit). This is because there is an infinite amount of real numbers, even within a small range like 0.0 to 0.1. Irrational numbers, such as π (pie) or √2 (square root of 2), or non-terminating rational numbers (like 0.333...), must be approximated, and calculations that are done by a computer Central Processing Unit (CPU) will need to be rounded. It is important to note that due to this loss in precision, you should almost never directly compare two floating-point numbers. A better way to do it is to compare numbers with some precision epsilon. For example: if (a == b) – problematic! if f (Abs(a – b) < epsilon) – correct! Floating-point numbers lose precision even when you are just working with such seemingly harmless numbers like 0.2 or 76.5. You should be extra careful when working with a large amount of floating-point operations over the same data as errors may build up rather quickly. If you are getting unexpected results and you suspect rounding errors, try to use a different approach to minimize errors. To do that, please consider the following: In the world of floating-point arithmetic, multiplication is not associative: a * (b * c) is not always equal to (a * b) * c. Additional measures should be taken if you are working with either extremely large values, extremely small numbers, and/or numbers close to zero: in case of overflow or underflow those values will be transformed into +Infinty, -Infinity or 0. Numeric limits for single-precision floating-point numbers are approximately 1.175494e-38 to 3.402823e+38. Sisense production deployments that include more than one CPU will use parallel calculation algorithms to speed the calculations and spread the arithmetic operation between several CPUs. Given that each CPU may result in a rounded calculation, the total arithmetic result can be different every time. This means that while running the calculation on a single CPU the result is approximated, but the same result every time, while the same calculation on a multi-CPU system may produce different results every time. This is normal behavior and should be addressed by using an epsilon during the compression of results from different iterations.Some Causes Of Build Failures
One common reason your dashboards may not be working as intended is that your ElastiCube builds are not designed properly. This isn’t a catch-all problem, and though there are multiple reasons why it can occur, the outcome is the same: insights aren’t accurate, dashboards can’t access data, or information is displayed improperly. The issue with build failures is that they aren’t always readily visible and discovering them requires understanding underlying causes for errors. Fortunately, these errors are usually not catastrophic, and each have straightforward fixes. [H2] The Top 4 Reasons ElastiCubes Fail, and How to Solve Them These are some of the most common reasons your ElastiCubes can malfunction and the best ways to quickly resolve problems as they arise: Data Modeling Issues - Many-to-Many Relationships Smart Data Modeling is the backbone of a healthy deployment but some common mistakes, such as Many-to-Many relationships, can undermine your analysis and put unnecessary load on your Elasticube performance. A Many-to-Many relationship exists when the value in each field used to create a relationship between tables is included multiple times in each table. Many-to-Many relationships can cause duplications in the returned datasets, which can result in incorrect results and might consume excessive computing resources. The Solution: Test your schema to see if it includes many-to-many relationships Understand which scenario best fits your current schema According to your schema logic, applying the respective solution Check out these assets to dig deeper: Design A Schema With One Fact - Star Schema Many-to-Many Relationships The Invisible Many-to-Many Many-to-Many by Design Connectivity Issues – Problems Communicating This occurs during the initial setup and can lead to accessibility issues as well as problems gathering data. Connectivity issues can be triggered by anything from incorrect credentials, improperly set up connections and servers, and other similar errors. The Solution: Check for different connectivity issues and make sure your ElastiCubes are properly configured. Another area to investigate is your database availability, for example scheduled maintenance or database restrictions (too many database connections). Check out DbVisualizer, a database tool that let’s you explore your data sets and database as related to the ElastiCube. Not Enough Disk Space – Memory Issues Disk space problems come in two varieties: a lack of storage space, or the more common issue of insufficient RAM to process the existing ElastiCube. The latter is a common error when your ElastiCube schema include too many columns, tables, and connections, and can cause your system to crash. You can notice this during the creation process if there are disk space errors. The Solution: Start by trimming your ElastiCube schema by removing tables, data, and fields that are not essential. Removing unwanted data can show a quick turnaround. Otherwise, you can add more RAM to your machine to handle the larger load. Testing ElastiCube Models on Live Servers – Performance Problems It’s difficult to know exactly how an ElastiCube will perform before deploying it, as errors may not be apparent until it’s interacting with other programs. Deploying the ElastiCube on a live server, then, is an unknown element that can have adverse effects on the larger system. These can include improper functions, broken systems, and in some cases fatal loss of data. The Solution: Before placing anything on your live server, you should always test it on a development server to avoid breaking anything before you know how to resolve it. Testing on a dev server also gives you the chance to test for unexpected errors and make tweaks before uploading an ElastiCube to the live servers. What To Do If Your ElastiCube Fails When you notice a failure in your builds, it’s not always easy to identify the culprit. If you do notice an issue, try the following: Explore Sisense’s Build Errors list to review some of the most common errors for ElastiCube builds. The chart features known errors and their standard solutions. Read through the Sisense documentation for strategies and tips on how to troubleshoot build errors. If you can’t find a solution on your own, submit a support ticket to Sisense’s staff. Speak directly with your Customer Success Manager, who will be able to give you guidance on how to resolve your issues.How to export .ecdata and .dash files
Introduction This forum post will demonstrate how to save your ElastiCube .ecdata file which contains your ElastiCube schema and data, and the .dash file of your desired dashboard. Purpose/Benefits The .ecdata file contains both the data and the logic structure of the ElastiCube model, the .dash file contains the widgets structure logic and the relation to relevant ElastiCube. Exporting the .ecdata and .dash files will allow you to easily import the files and run your ElastiCube/Dashboard environment. Please note that security settings and users are stored on MongoDB and will not be stored within these files. To avoid confusion between .ecdata and .ecube files please see the following post: Difference between .ecdata and .ecube files Steps exporting .ecdata (.sdata for Linux) Windows: Step 1 - Open the ElastiCube Manager, 'Servers' and choose 'Manage Servers' to open Sisense Server Console Step 2 - Stop the relevant ElastiCube and Choose Export This will open a window allowing you to choose the location of the .ecdata file Note: in case your ElastiCube is very large, export can take a while to complete. We recommend stopping your Web server (IIS / IIS Express) to avoid the ElastiCube from automatically starting during this time, as a result of incoming queries. Linux: Use Sisense CLI to export the .sdata file: https://docs.sisense.com/main/SisenseLinux/using-sisense-cli-commands.htm The command export should be used. Example: si elasticubes export -name MyCube -path \"/opt/path/to/NameOfCube\" -file cubeName.sdata Another option to export - using v2 of REST API: To get the ID of the model, use another API call: Or to do the same from GUI: Open the Data page. For the relevant ElastiCube, select > Export Model. Select to export the model schema and the data. Click Export. exporting .dash It will be the same for Windows and Linux. Open Sisense web, click the more options of the relevant dashboard and choose Export Dashboard The dashboard will be downloaded to your local drive. Creating a Sample Set of your ElastiCube It will be the same for Windows and Linux. If the .ecdata file is too large to transfer, you might want to create a smaller ElastiCube sample data. To do so, duplicate the model in order to not to impact your dashboards: And build the duplicated model with a limited amount of rows:Schedule sequential ElastiCube builds using windows task scheduler
In many cases we would like to schedule our ElastiCubes builds to run one by one, using just one command, or to run in a specific hour so our users won't create or watch dashboards while the cube is building and also will have recently updated data. Although you can schedule a cube to build in a specific hour through the scheduled build settings in the ElastiCube Manager, this post can help you to define more complex and specific terms to run the build. For example building all your ElastiCubes in one process and determining a specific hour for the build. Step 1: Copy the script and save it as .bat file This script contains a few steps to activate a cube build. The following lines are the commands to build the cubes. Add a new line for every ElastiCube you want to build. It is crucial to add the "/wait" part so every command start in its turn only after the previous is finished. @echo off start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\<My_Cube_A>.ecube" serverAddress="LocalHost" mode="restart" start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\<My_Cube_B>.ecube" serverAddress="LocalHost" mode="restart" @end For more information about available command parameters, please see our PSM documentation at https://sisense.dev/guides/legacy/psm/#sisense-shell-psm-exe. Using the PSM commands, you can build from either a .ecube file or an existing ElastiCube. Example to build from a .ecube file: start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\Users\Administrator\Documents\My ElastiCubes\Northwind.ecube" serverAddress="LocalHost" mode="restart" Example to build from an existing ElastiCube in the ElastiCube Server Console: start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="Northwind" serverAddress="LocalHost" mode="restart" Here are the available build types: mode="restart": Rebuilds the ElastiCube entirely. mode="full": Rebuilds the ElastiCube and accumulates data for tables marked as accumulative. This mode should only be used for accumulative builds. mode="changes": Rebuilds from scratch tables that have changed in the ElastiCube schema. mode="metadataonly": Updates the ElastiCube server with the ElastiCube schema, without building. Step 2 : Creating a new task Open the windows task scheduler and create a new task. Name the task and set security filters as followed. *note that the user account running the task MUST BE AN ADMINISTRATOR USER Step 3: Setting the Triggers In the triggers tab create a new trigger and define the time to activate the command, this can be an interval for every few hours or run on a specific hour. In case you want to run the task a few times a day in predetermined hours, for every build occurrence create a new trigger. (i.e one occurrence in the morning and one at night). Step 4: Specifying the build action and finishing In the actions tab create a new one and choose "Start a Program" action, then choose the .bat file you created. Edit the Condition and Settings tabs as required (optional) and hit OK. Test the task to see if it's working by right clicking on the task and choosing "Run". In cases you want one cube to run every hour and another cube only once a day at a specific time, and don't want the builds to run in the same time, you can create a task with the following script to run every hour, with a loop that contains a time window to run a second cube. the following script will run CUBE_A every time the task starts, but CUBE_B will run only if the time is between 4pm and 5pm: @echo off start /wait /d "C:\Program Files\Sisense\Prism" psm ecs stopstart /wait /d "C:\Program Files\Sisense\Prism" psm ecs startstart /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="CUBE_A" serverAddress="LocalHost" mode="restart" set "now=%time: =0%" if "%now%" geq "16:00:00,00" if "%now%" lss "17:00:00,00" ( start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="CUBE_B" serverAddress="LocalHost" mode="restart" ) @end In the “if” line you should change the time window to run the other cubes. Add a line for each cube, add a new loop for every time window (notice the parenthesis) **NOTE: if a build fails for some reason, the following ones in the script will not run.Server Console Preferences & Configuration
Note: Sisense's default ElastiCube Server Console configuration is optimally tuned for ElastiCube and dashboard performance. It is generally not recommended to change these settings (except for Default server data folder and Rserve). This article identifies and defines the preferences available for the Sisense ElastiCube Server. In Sisense 6.7 or earlier, you can access the Sisense server preferences by clicking the cog in the Sisense Server Console: Setting Default Description Default server data folder C:\ProgramData\Sisense\PrismServer\ElastiCubeData Directory where ElastiCubes are stored (see here for details) # of simultaneous query executions Number of CPU logical cores / 2 Maximum number of queries that will be processed at once. Automatically set in the installation to half the number of logical cores on the server Query TCP port 812 Port used to query the ElastiCube server from the web application Rserve 120.0.0.1:6311 IP address and port to connect R with Sisense (more information here) Query timeout (in seconds) 300 Time alloted to a non-responding query before it times out. Query Retries on Failure 0 After a query times out, how many retries to attempt ElastiCube port 50000 Port the ElastiCube runs through Backup ElastiCube before each new build Unchecked Option to save a .ecdata file of the ElastiCube before each build Data-import chunk size 100000 Number of records imported at once in a build ElastiCube restart timeout (seconds) 3 When an ElastiCube times out, how long before it restarts Free memory percentile to auto-restart 30 Minimum free memory needed in order to restart the service automatically in case it fails (no longer in use) Server memory percentile to auto-restart 0 Minimum server memory needed in order to restart the service automatically in case it fails (no longer in use) Remove EOF Character from textual fields Checked Removes hidden characters from text files Compress on build Checked Compress data when performing builds Query CPU cores Number of CPU logical cores / 2 Maximum number of CPU cores that can be leveraged to process one query ElastiCube memory usage Dynamic Percentage of RAM on the machine that the ElastiCube server can use. Dynamic is the default setting and will use Sisense's built-in processes to manage and clear memory. Skip data import on connection errors Unchecked Continue with a build if there is a connection error on a specific data source Recycle queries Checked Store query metadata for later use In Sisense 7.0 or later, it is available in the Admin screen, Data Management - Data Sources, and by clicking the context menu beside the applicable ElastiCube server (fewer options are available than from the Sisense Server Console):Export a table from the elasticube to a CSV
* Disclaimer: This works for Windows only. It will work in future with Linux versions as well. Once it does, I will update the post. Sometimes, we need to export a table from the Elasticube to a CSV file. We can then import it to the next build, to send it to a colleague, to back up or any other usage. (In the Sisense Desktop version, we could have done it using the export to CSV post plugin. However, this is not available in the WEB ECM). The method described below utilities the API call: http://localhost:8081/api/datasources/LocalHost/ECName/sql?query=select%20*%20from%20TableName&format=csv If there are spaces in the name of the EC or of the table name, use %20. Happy exporting 🙂Schedule Download Of SQL API Queries To CSV
Introduction With the Python script below and the Window Scheduler, you can automate the download of csv files based on queries of the SQL API to the elasticube. This type of process comes in handy when a user needs to download information regularly to be consumed by other processes. The script takes takes care of the authentication process and keeps the definition of the cube, query, and response file. Prerequisites 1. install python 3.4 (download here) 2. python c++ compiler (download here) 3. python dependencies jwt (pip module PyJWT) parse (pip module parse) requests (pip module requests) django (pip module django) All python dependencies can be downloaded with pip. In the command line, type the following for downloading PyJWT for instance: pip install PyJWT Script The sql_to_csv.py script is attached. Note the Settings section in the script to setup server location, name of the cube, query, user email and password, REST API token, and response file. Windows Scheduler See here how to schedule a task in Windows. You can read this stack overflow post about scheduling the task every hour. The command that you need to set in the Scheduler is the following: python sql_to_csv.pyGet number of rows in elasticubes
Though there is no direct API endpoint to get the number of rows in an elasticube, there is an endpoint to run SQL directly against a cube. Using PySense, it is straightforward to iterate through all your elasticubes, run SELECT COUNT(*) from each table in those cubes and export the results. How it works Define the csv file to output the results to Include your credentials (authenticate_by_password used below for testing purposes, you will probably want to use authenticate_by_file) The code retrieves all elasticubes. For each cube, iterate through all tables in the data model, and run SELECT COUNT(*) FROM table_name. Store the cube name, table name, and row count. Print the results to csv. Disclaimer Use this script as guidance; you are advised to test / validate / modify this script as needed. Sisense admin credentials are generally required to access the SQL endpoint. Row level data security rules are not applied. This script was built for a Linux environment. from PySense import PySense import json import csv #Parameters report_name = 'C:\\Users\\blahblah\\Downloads\\rowcount.csv' py_client = PySense.authenticate_by_password('url','username','password','Linux') report_map = {} elasticubes = py_client.get_elasticubes() for cube in elasticubes: cube_name = cube.get_title() print('Starting ' + cube_name) try: data_model = cube.get_model() for data_set in data_model.get_data_sets(): for table in data_set.get_tables(): table_name= table.get_name() cube_table_key = cube_name + '_' + table_name query = "SELECT COUNT(*) FROM \"" + table_name + '\"' sql_result_json = cube.run_sql(query, "") sql_result_dict = json.loads(sql_result_json) for key, value in sql_result_dict.items(): if key == 'values': str_value = str(value) str_value_trim = str_value[2:len(str_value)-2] report_map[cube_table_key] = [cube_name,table_name,str_value_trim] print('Finished ' + cube_name) except: report_map[cube_name + '_error'] = [cube_name,'error','0'] print('Error ' + cube_name) print('Start export') with open(report_name, 'w', newline='') as csv_file: dashboard_writer = csv.writer(csv_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL) dashboard_writer.writerow( ['Data Model', 'Table','Row Count'] ) for report in report_map.values(): dashboard_writer.writerow(report)Monitoring Your Builds
Part of having a successful BI tool is having your data refreshed on the time you need it. To ensure your builds are working as expected, you can follow the steps provided below. Step 1 - Setting Up a Build Alert Follow the documentation of Sisense Pulse Creating build alerts. Build alerts are created from Sisense and are sent following an ElastiCube build. Step 2 - Build Alert In case of a build alert, an alert will pop up letting you know what cube had failed and when Step 3 - But now what? how do I know why it failed? Go to the Data tab where you will see the cube that failed and open the schema of the cube Click on option > View Latest Logs This will show you the stage/table it failed on and the exception message In our case, the build failed due to a missing file used in table1 table Step 4 - Can't understand the exception message Look at the Build Errors Troubleshooting to better understand the build mechanism and errors. Still can't figure out what went wrong. Submit a ticket and don't forget to add the following details - Name of cube Exception message Did the cube ever built successfully? Time of failure and your time zoneCreating a list of Elasticubes and Replace Padding Letters In EC Names With Clear Characters
The names of the ECs are often accompanied by mysterious letters that make them hard to read. If you have a need to create a list of ECs with their clear names and the dates when they were last queried, you may follow the following steps: Create a text file which contains the names of the elasticubes: C:\ProgramData\Sisense\PrismServer\ElastiCubeData>dir > C:\Users\admin\Downloads\ECs.txt Clean the EC names by using the following Find and Replace strings 1. Remove timestamp with regex 2. Replace characters according to the below table Example: Before... 08/02/2020 22:50 <DIR> aADVATIXXwAaADILKAPOORXwAaECXwAav3 15/06/2020 15:39 <DIR> aAdvIAAaCertificationIAAaLQAaIAAaUseIAAaCaseIAAaLQAaIAAaDataIAAaSecurity 15/06/2020 15:39 <DIR> aAdvIAAaCertificationIAAaLQAaIAAaUseIAAaCaseIAAaLQAaIAAaDataIAAaSecurity_Alternative 15/06/2020 09:22 <DIR> aAdvIAAaCertificationIAAaLQAaIAAaUseIAAaCaseIAAaLQAaIAAaLeads 27/04/2020 18:06 <DIR> aBAsketAnalysisTest_2020-4-27_11-13-36 27/04/2020 18:06 <DIR> aBAsketAnalysisTest_2020-4-27_11-13-36_Alternative 07/03/2020 20:22 <DIR> aClio_2020-3-7_19-22-5 04/05/2020 10:38 <DIR> aCovid19IAAaCitiesAbove50K_2020-5-4_10-38-48 17/03/2020 18:49 <DIR> aDataflixTechnologiesXwAaHemaSaiKanakamedalaXwAaECDATA 06/03/2020 13:35 <DIR> aDataflixTechnologiesXwAaJyothiSaiKrishnaUppalapati 10/03/2020 12:10 <DIR> aDataflixXwAaSreenivas(1) 10/03/2020 12:10 <DIR> aDataflixXwAaSreenivas(1)_Alternative 09/08/2020 16:10 <DIR> aDatesCaseWhen_2020-8-9_16-10-2 07/05/2020 16:44 <DIR> aDay2DayIAAaCompare_2020-5-7_16-44-26 08/02/2020 15:18 <DIR> aDevLQAaSisenseLQAa1LgAa2IAAaExam After... 08/02/2020 22:50 <DIR> ADVATIX_ADILKAPOOR_EC_v3 15/06/2020 15:39 <DIR> Adv Certification - Use Case - Data Security 15/06/2020 15:39 <DIR> Adv Certification - Use Case - Data Security_Alternative 15/06/2020 09:22 <DIR> Adv Certification - Use Case - Leads 27/04/2020 18:06 <DIR> BAsketAnalysisTest 27/04/2020 18:06 <DIR> BAsketAnalysisTest 07/03/2020 20:22 <DIR> Clio 04/05/2020 10:38 <DIR> Covid19 CitiesAbove50K 17/03/2020 18:49 <DIR> DataflixTechnologies_HemaSaiKanakamedala_ECDATA 06/03/2020 13:35 <DIR> DataflixTechnologies_JyothiSaiKrishnaUppalapati 10/03/2020 12:10 <DIR> Dataflix_Sreenivas(1) 10/03/2020 12:10 <DIR> Dataflix_Sreenivas(1)_Alternative 09/08/2020 16:10 <DIR> DatesCaseWhen 07/05/2020 16:44 <DIR> Day2Day Compare 08/02/2020 15:18 <DIR> Dev-Sisense-1.2 Exam Using further Find and Replace we can get the following text: 08/02/2020, ADVATIX_ADILKAPOOR_EC_v3 15/06/2020, Adv Certification - Use Case - Data Security 15/06/2020, Adv Certification - Use Case - Data Security_Alternative 15/06/2020, Adv Certification - Use Case - Leads 27/04/2020, BAsketAnalysisTest 27/04/2020, BAsketAnalysisTest 07/03/2020, Clio 04/05/2020, Covid19 CitiesAbove50K 17/03/2020, DataflixTechnologies_HemaSaiKanakamedala_ECDATA 06/03/2020, DataflixTechnologies_JyothiSaiKrishnaUppalapati 10/03/2020, Dataflix_Sreenivas(1) 10/03/2020, Dataflix_Sreenivas(1)_Alternative 09/08/2020, DatesCaseWhen 07/05/2020, Day2Day Compare 08/02/2020, Dev-Sisense-1.2 Exam We can then save it as a CSV...and from here... use it with APIs for example. Viva clarity 🙂 Tamir