Get number of rows in elasticubes

[intapiuser]

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

1. Define the csv file to output the results to
2. Include your credentials (authenticate_by_password used below for testing purposes, you will probably want to use authenticate_by_file)
3. 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.
4. Print the results to csv.

Disclaimer

1. Use this script as guidance; you are advised to test / validate / modify this script as needed.
2. Sisense admin credentials are generally required to access the SQL endpoint. Row level data security rules are not applied.
3. 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)