Get 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)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.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.