Connection Tool - Programmatically Remove Unused Datasource Connections, and List All Connections
Managing connections within your Sisense environment 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.
REST API - Adding Data Level Security
When building code to automate the process of adding users (or groups), it may be beneficial to add security around those users. Follow the steps below to learn how to add data level security through the REST API: Step 1 From your SiSense home page, navigate to the Manage tab and choose the option for REST API. From here, click on the link to the REST API Reference interface. Step 2 From here, choose the 0.9 version and expand the Elasticube section and scroll down to the POST /elasticubes/datasecurity section. Step 3 The sample code below shows a valid JSON object to use as part of the REST API request. Starting from the sample code, replace the value for party with a user identifier that already exists in your system. Then paste the code into the REST API interface and click run. You should see a response of 200, which indicates a successful operation. [{ "server": "LocalHost", "elasticube": "Sample Lead Generation", "table": "Lead Generation", "column": "Country", "datatype": "text", "shares": [{ "party": "5c0f85690ca2f66cc242e266", "type": "user" }], "members": [ "United States", "England" ] }] Notes The server name value is case sensitive, so make sure it matches with your system. The sample provided is specific to one of the sample databases that comes with SiSense. To modify this to a different system, just update necessary fields to include your specifications. The sample provided is an array that consists of a single element. This could be modified to contain several entries all sent at once.UserReplaceTool - Automating Dashboard Ownership Transfers - Useful for Deleting User Accounts
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. 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.The Ability To Manage JDBC Connections
Question How to change the connection strings in an easy way? Answer Using the REST API you can update the data connection strings directly. Returns a list of all your connections: GET http://localhost:8081/api/v1/connection Updates a connection: PATCH http://localhost:8081/api/v1/connection/{id}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.pySample Code for Sharing Dashboards Using the REST API in .NET
Introduction A sample code to demonstrate adding shares using the Sisense API in .NET. The programs flow is a follows: Retrieve the authentication token using: Retrieve userID using: Retrieve the current shares for the dashboard using: Share the dashboard with the user by updating the shares of the dashboard using: Pre-Req. This code uses the RestSharp and the Newtonsoft.Jason.linq packages both available through NuGet. If you are using Microsoft Visual Studio, see here for instructions on how to install the NuGet extension and here for instructions on how to add packages to your solution. Once installed, use the NuGet package manager to install the RestSharp and Newtonsoft.Jason.linq packages. Running the Program To run the program, modify the code as follows: In the main method, replace the following: <User Email> with a user's email <Dashboard ID> with your dashboard's ID static void Main(string[] args) { string token = getToken(); Console.WriteLine(token); string userID = getUserID("<User Email>", token); Console.WriteLine(userID); JArray curr_shares = getDashboardShares("<Dashboard ID>", token); Console.WriteLine(curr_shares.ToString()); addShares("<Dashboard ID>", curr_shares, userID, "user", "view", false, token); } In the getToken() method, replace the following: <Admin User Email> with the admin's userID <Admin User Password> with the admin's password <Host:Port> with you host and port static string getToken() { var client = new RestClient("http://<Host:Port>/api/v1/authentication/login"); var request = new RestRequest(Method.POST); request.AddHeader("cache-control", "no-cache"); request.AddHeader("accept", "application/json"); request.AddHeader("content-type", "application/x-www-form-urlencoded"); request.AddParameter("username", "<Admin User Email>"); request.AddParameter("password", "<Admin User Password>"); IRestResponse response = client.Execute(request); JObject obj = JObject.Parse(response.Content); return obj.GetValue("access_token").ToString(); } The Script C# code attached below. Share_Dahsboard_REST_API.csQuerying the Sisense Elasticube with Python
This article will cover two methods you can use to connect Python to the Elasticube. Method 1: Using ODBC Method 2: Using the REST API Method 1: Connect to the EC with ODBC Download Sisense ODBC This method will require you to download the ODBC driver. Follow the instructions on the ODBC documentation page before starting to work in Python. Connect to the system DSN ODBC with pyODBC The following libraries are required for this step (both are standard with anaconda) pyodbc pandas Step 1: Import libraries import pyodbc import pandas as pd Step 2: Establish a connection to the EC cnxn = pyodbc.connect('DSN=Sisense ODBC Driver') Step 3: (optional) Print out available tables # Create a cursor that can execute commands against the connection cursor = cnxn.cursor() # Print all available tables for t in cursor.tables(): print(t[2]) Step 4: Put the data into a dataframe Substitute <Table Name> with the table you want to query # Bring the data into a dataframe called df df = pd.read_sql('select * from <Table Name>', cnxn) # Print out the top records from df df.head() Method 2: Query the EC with the REST API This method will require the following python libraries: requests urllib.parse pandas Step 1: Import the libraries import requests import urllib.parse as parse import pandas as pd Step 2: Define the datasource and your query # Your elasticube name is your dataSource dataSource = 'leapYearUseCase' # Query the data as if you were writing a custom SQL query query = 'select Years, min(Amount) as maxAmount from <Your Table> group by Years' Step 3: Parse the queries dataSourceURI = parse.quote(dataSource) queryRUI = parse.quote(query) Step 4: Establish your connection To retrieve your Bearer token follow instructions under USING THE SISENSE API - Authentication # http://localhost:8081 may vary depending on your configuration # The request asks for the data formatted as a csv for convenience endPoint = 'http://localhost:8081/api/elasticubes/{}/Sql?format=json&query={}'.format(dataSourceURI, queryRUI) # Enter your bearer token in place of <your bearer token> header = {'Authorization': 'Bearer <your bearer token>'} Step 5: Post and Get the Response # Post to get the response response = requests.get(endPoint, headers=header) # Collect the response text formatted as a JSON data = response.json() # Put the data into a dataframe df = pd.DataFrame(data['values'], columns=data['headers']) Cheers!Python Script to Export Dashboards to CSV File
The following Python script allows exporting dashboard data to CSV files. The script will export each widget to a separate CSV file. ✨This new version of the script uses Sisense's REST API an eliminates the direct connection to the mongodb. Important: Starting with Sisense V8.0.1The GET /dashboards/{id} endpoint returns only the Dashboard Owner ID and no other information about dashboard owners when called by non-Admin users (see Sisense Release Notes.) Therefore the user used to run this scrip must have admin rights for the script to work properly. Download exportDashboardToCSV_01272020.zip - contains the script executable and configuration file and does not require Python installation on the server. Settings The script uses a settings.yaml file. You should configure the following settings: Setting Description email Admin email address. If you're using the api token to authenticate leave this value blank. password Admin password. If you're using the api token to authenticate leave this value blank. api_token The API token can be use instead of the password. To retrieve you API token use the POST /authentication/login REST API command. See here for more info. Note: The admin email and password are used for retrieving the API token. The token is required for running API command. See the REST API documentation for more information. file_logLevel Log level for the log file. Available options: DEBUG INFO WARNING ERROR CRITICAL console_logLevel Log level for the console. Available options: DEBUG INFO WARNING ERROR CRITICAL logFileName Path to the script log file. protocol Protocol for accessing Sisense http or https. Default: http verify Set verify to False toignore verifying the SSL certificate. Default: true port Sisense port. Default 8081 host Sisense host. Default: localhost csvFilePath CSV files output directory csvFileNameConvention Naming convention for the generated CSV files. Options are: WIDGET_NAME (e.g. Total csv) WIDGET_AND_DASHBOARD_NAME (e.g. Total Revenue_Sales Dashboard.csv) WIDGET_AND_DASHBOARD_ID (e.g. 5783b9eb25ddaf78420000a6_56f1aa1dbc4b62043a000028.csv) Usage exportDashboardToCSV.exe [-h] [-dashboards [DASHBOARDS ...]] [-widgets [WIDGETS ...]] optional arguments: -h, --help show this help message and exit -dashboards DASHBOARDS [DASHBOARDS ...] List of dashboard IDs to export separated by space. -widgets WIDGETS [WIDGETS ...] List of widget IDs to export separated by space. Each widget ID should be in the format of <dashboard id>.<widget id> Output The script will create an output directory 'csv' in the scripts directory. Each widget in the dashboard will be exported to a separate CSV file named as: dashboardID_widgetID.csv Examples Export an entire dashboard: exportDashboardToCSV.exe -dashboards 5e2a1bccbc60327e98fb4635 Export multiple dashboards: exportDashboardToCSV.exe -dashboards 5e30922fa9ab7536081a6ee9 5e3087a78d9e7f0e3cb3e6f8 Export individual widget: exportDashboardToCSV.exe -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 Export multiple widgets: exportDashboardToCSV.exe -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f95e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 Export a combination of dashboards and widgets: exportDashboardToCSV.exe -dashboards 5e30922fa9ab7536081a6ee9 5e3087a78d9e7f0e3cb3e6f8 -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9Is it possible to enable/disable a plugin with REST API?
Question: Is it possible to enable/disable a plugin with REST API? Answer: Option 1 It is possible with the /api/plugins endpoint. Here is the script we use for a single enable/disable of a plugin; For enabling set isEnabled to True For disabling set isEnabled to False import requests url = "URL/api/v1/plugins" payload = "[{ \"name\": \"metadata\", \"lastUpdate\": \"2019-08-11T15:21:36.491Z\", \"version\": \"0\", \"isEnabled\": false, \"folderName\": \"metadata\"}]" headers = { 'accept': "application/json", 'Content-Type': "application/json", 'cache-control': "no-cache", 'Authorization': 'Bearer TOKEN' } response = requests.request("PATCH", url, data=payload, headers=headers) print(response.text)Dashboard Dictionary
Use Case My users and designers were looking for an easy way to get insights into how certain dashboards and widgets were built in terms of formulas and filters. Solution For this, I have written a Python script that takes a list of dashboard IDs and converts them to an Excel with 2 worksheets. The first worksheet shows all widgets, while the second shows all dashboard filters. Attached you will find a python-script + a CSV (with dashboardIDs). Replace the dashboardIDs with the IDs of the dashboards that you want to have exported Open the Python script in your favorite IDE and replace the following: base_url --> with your own base url translation_dict --> i use this to translate English to Dutch. Either empty it or replace it with your own language Authorization --> Add your bearer token Run the script The output will be an Excel file that you can import into an Elasticube and create a dashboard with. Ps. In my dashboard I use Paldi's Advanced Filters for easy filtering and Advanced Text Widget for easy-to-style texts. Copied from this post by HamzaJ. Thank you Hamzaj!
