Coinbase Data Connector
Download: Coinbase Connector Introduction: This article will explain how to make a REST API connection to Coinbase's API Purpose/Benefits: This connector gives you the ability to pull in data about cryptocurrencies from Coinbase into your Sisense ElastiCube How to Install and Configure the Coinbase Connector: Step 1: Download the Connector and unzip the contents into your C:\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors folder. *You may need to move your Rest dll file into the connector (replace the current one) - you can do this by going to C:\Program Files\Sisense\DataConnectors\DotNetContainer\Rest and copying the .dll to the Coinbase Connector Step 2: Restart the Sisense.Discovery and Sisense.CLRConnectorsContainer services on the Sisense machine Step 3: The driver has tables already configured for BTC, ETH and LTC You can add/remove depending what would you like to track: Step 4: Open the ElastiCube Manager, create a new ElastiCube and select the Coinbase Connector. Reference/Notes: Coinbase Website API Documentation
How to connect to database directly from Sisense server using SQLLine
Symptoms You need to connect to database, however connection is not going through. You have already check the hostname, username, password and everything seems to be correct. You have whitelisted the database address, hostname, etc. however not sure if the the rules are working properly. Diagnosis In order to check if the necessary database can be reached from Sisense server you may connect to it using jdbc .jar file directly from Sisense server (omitting UI). This was you will be able to make sure the whitelisting works properly and the issue is with connector itself. Solution 1)Download jdbc .jar file for database in question from https://www.cdata.com/solutions/bi/sisense/ (we will use Redshift in this example) 2)Put the .jar file into folder in File Manager. We have create a new 'redshift' folder inside 'data' folder 3)Login to Sisense server 4)Make sure java and SQLLine are installed on Sisense server. If you are doing this for the first time you will need to install them. You may use 'wget' commands for this 5)Connect to SQLLine by using the following command: java -cp <sqlline-jar-file>:<jdbc-driver-jar-file> sqlline.SqlLine Replace <sqlline-jar-file> with the path to the SQLLine JAR file and <jdbc-driver-jar-file> with the path to your JDBC driver JAR file. In our example, we have downloaded SQLLine to the folder we were in. The path to the File manager in Sisense is '/opt/sisense/storage'. Therefore, the connection string for us looked like this: java -cp sqlline.jar:/opt/sisense/storage/data/redshift/cdata.jdbc.redshift.jar sqlline.SqlLine 6)Once SQLLine is running, connect to your database using the !connect command. General syntax is the following: !connect jdbc:<database-url> <username> <password> Following CDATA redshift connection string requirements, in our example the syntax looked like: !connect jdbc:redshift://database_url:port_number/database_name user_name user_password When you are downloading the CDATA jdbc connector, please open the docs section for that connector to check the right connection string format. If connection is established you will be able to use SQL queries to return the data. If connection is refused, please check the database address whitelisting again and make sure all the necessary outbound/inbound rules are setup.Connecting to Oracle DB with Service Name (instead of Service ID - SID)
Connecting to Oracle DB with Service Name (instead of Service ID - SID) When a customer tries to connect to an Oracle DB using a Direct Connection option, there's no option in the GUI in the ElastiCube Manager to use a Service Name, only a Service ID: Solution 1) Uncheck the Use Direct Connection checkbox. 2) In the Database server location textbox, enter the following: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$your_ip)(PORT=$your_port))(CONNECT_DATA=(SERVICE_NAME=$service_name))) The variables you need to input are: $your_ip $your_port $service_name 3) Connect to the source.Tips And Tricks For Configuring The Excel Services ODBC Driver By CDATA
If you organization has Excel documents stored within Sharepoint that you would like to bring into the ElastiCube Manager, you may chose to use a CDATA driver to bring this data in using an ODBC connection. Below are some lessons learned in setting this up that are not documented in CData ODBC Driver for Excel Services documentation. All screenshots are from the ODBC connection interface when configuring a connection Make sure to create the DSN under System DSN (not User DSN) 2. Within the Authentication section, use the screenshot below to guide filling out these settings Special Notes: - URL: Do not include the name of the file (that will be set later), also make sure there is no trailing slash at the end of the URL - Use SSO: Use this guide on whether to set this to True or False 3. When the Folder or Library field is not supplied, the driver will default in the Shared Documents folder in the file path just before the name of the file. If Folder or Library is supplied, Shared Documents will not be added to the URL 4. Verify that you have item-level permissions using the steps outlined in the second reply to the forum post here. 5. Set the verbosity to 4 and designate a file path to a blank.txt file for logging. Verbosity of 4 is useful for troubleshooting, once you get the connection working, make sure to change the Verbosity back to 1 once things are working 6. There are a few ways to obtain the URL to the file, one of the ways is to find the document on the home page of your site to get the URL as shown below: Another is by going to your document list on the site Then click on the 3 dots next to the document name and obtain the URL 7. Use the logs you set up in Step 5 to see what the URL is being formatted as. There should be a GET request to your URL with /_vti_bin/ExcelRest.aspx/ sandwiched into the request. Hope this helps in configuring your Excel Services ODBC driver!Connecting To The Elasticube From Microsoft Excel
Question: The purpose of this post is to demonstrate how to connect to a Sisense Elasticube from Microsoft Excel. Solution: Begin by installing the Sisense ODBC driver. https://www.sisense.com/data-connectors/odbc/ Once it is installed, go to ODBC-64 bit from the windows search (just search odbc and it will pop up), then go to the system tab and add a new connection. Then choose the Sisense ODBC Driver. Add a name and a description and then click on the empty box for elasticube names. This will prompt you for a Sisense login. If working on the same server as Sisense, the server will be localhost:8081. The username and password corresponds to your login in Sisense. Note: If you receive a licensing error, please reach out to your Customer Success Representative. Finally, select an Elasticube and save the connection. Now you are ready to connect to the elasticube from Excel. Now open a new workbook in Excel. Select the Data tab> Get Data> From Other Sources > ODBC. You will be prompted to either select an existing connection or enter a connection string. Select the connection and continue. You may be prompted for credentials. However, because the connection already contains them, they are irrelevant at this stage. You can enter a random value and click continue if it will not let you continue with an empty box. Once the connection is established, you should see all tables available in the elasticube. Please note you can not see hidden fields from the ODBC connection. In the case above, I have selected the Admissions table and loaded it into Excel. From this point you should have a populated table with the ability to refresh. You can append the query to join two tables together and customize the query. Please refer to the documentation for Excel Power Query.How to create JDBC connector on a new Framework
Previously in Linux, Sisense were using so called "Old Framework" for connectors - this means connectors from Windows that were ported to Linux environment. However now Sisense developing the connector designed for Linux specifically. Some portion of connectors already using the new framework i.e if the connector is enabled - this means the New Framework, disabling the connector will enable the Old Framework, BTW switching the connector framework is the technique to troubleshoot the issue. But the JDBC connectors still using the Old connectors framework. Starting from version L2022.0 you can create the JDBC connector on a New Framework. Important to note! The provided solution is not an official feature yet, and currently in BETA, thus, might have issues. The solution will require maintenance and proper testing on your side, we also highly recommend running on a staging environment before upgrading or implementing it on production environments. To create the connector: 1. Download the needed connector from CDATA (Sisense official partner) https://www.cdata.com/solutions/bi/sisense/ 2. Install the connector on your local PC (not on Sisense server) 3. The connector will be installed into C:\Program Files\CData\DRIVER_NAME 4. Open Configuration Manager 5. Scroll down to the bottom of the page 6. Click on Manage Connectors 7. Create JDBC connector 8. Enable connector 9. Give it the required name in the Display name (this will be the name of the connector in the list of connectors) 10. Upload the connector that you installed in the Step 2. Needed *.jar file located in the lib older of your driver C:\Program Files\CData\DRIVER_NAME\lib 11. Provide the class name (which can be found in the documentation of a connector on the CDATA page) 12. Save settings. Now the connector is available in the list of connectorsHow to connect to mongodb with a password protected user
Introduction If attempting to connect to MongoDB with a user protected password, but you aren't able to connect to the database through the Simba DSN or the connection string and getting the following error: Unable to establish a connection with data source. Missing settings: {[PWD]} Resolution Please follow these steps: 1. Create a new entry in the DSN as usual 2. Open the Registry Editor 3. Add a string value with name "PWD" to the registry in the following path: LOCAL_MACHINE --> Software --> ODBC -->ODBC.INI --> <DSN connecton name> 4. Rename the new field added to the name PWD and enter the password of usernameGetting Started With DbVisualizer
Problem There are many data sources (connectors), such as web service APIs where we not familiar with their tables/fields like Facebook, Twitter, Youtube etc. Before importing your data from a web service, you should know the data in advance. This lets you: Slice unnecessary garbage data by generating custom queries Understanding the data you’re dealing with Troubleshoot problems from the data source/connector side Solution DbVisualizer is a database tool that let’s you explore your data sets before importing them into the ElastiCube. HOW DO YOU CONNECT DBVISUALIZER TO YOUR STANDALONE DATA SOURCE CONNECTOR? 1. Go to the DbVisualizer site and download and install the latest version. *Note: You can download a version with Java or without Java. To see if you have Java installed already, open the Command Prompt and type in the following command: java -version. 2. Verify that you downloaded your connector’s driver and you can locate the connection in the following directory: C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors 3. Copy your connector’s connection string in an editor where you can easily view it. For an example of a connection string, see our Facebook example: jdbc:facebook:OAuthClientId=11276856774486;OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0; 4. Open DbVisualizer and force close "JDBC Driver Finder". 5. In DbVisualizer, go to Tools > Driver Manger and add your new connector (a) Click "+" icon to add new driver. (b) Give your connector a name. (c) Click the Driver file path and look for the JDBC driver file, and then click ok. C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\<Your connector>\lib\cdata.jdbc.<connector> (d) Verify that the driver is loaded and you see the driver. Then, close the Driver Manager window. 6. In DbVisualizer, go to Tools>Connection wizard. (a) Give your connector name and click Next. (b) Chose the driver connector that you generated. (c) Open "C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\<Connector>\description.json"; get the "RTK" parameter and add it to your connection string. Before: jdbc:facebook:OAuthClientId=11276856774486;OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0; After (RTK in the end of the string): jdbc:facebook:OAuthClientId=11276856774486;OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0;RTK=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (d) Add the connection string with the RTK to the "Database URL" and click Finish. (e) Verify that it connected successfully and you see all the Tables and views. (f) Go to SQL Commander > New SQL. (g) Change max rows to "-1" and start to explore your connector data. References Sisense connectors list DbVisualizer Users GuideExploring ElastiCube data with ODBC connection to R
Problem You want to determine if there are any problems with your dataset from the ElastiCube and whether the question you are asking for Sisense with R integration can be answered by the data that you have. Solution Installing the Sisense ODBC driver Download and Install Sisense ODBC driver from this link: Sisense ODBC Note: Sisense ODBC is a licensed feature Adding new Sisense Data ElastiCube Source to ODBC Open ODBC -> System DSN Add New Sisense ElastiCube data source. From the ElastiCube drop-down list, select the ElastiCube. Click Test to verify the connection to the ElastiCube. Exploring ElastiCube data in R Environment Open an R console window. Install and load the “RODBC” Package into R > install.packages("RODBC") > library(RODBC) Open the connection to an ODBC ElastiCube Database. >myconn <-odbcConnect("NW-Dan Kushner", uid="", pwd="") Viewing ElastiCube Data. Submit a query and return a result for exploring in R Discussion Exploratory data analysis is the process of exploring your data, and it typically includes examining the structure and components of your dataset, the distributions of individual variables, and the relationships between two or more variables. References http://www.statmethods.net/input/dbinterface.html https://www.sisense.com/data-connectors/odbc/Dremio ODBC Connection
In case you don't know already you can download the Dremio ODBC drivers here: https://www.dremio.com/drivers/odbc/ Information for configuring the driver can be found here: https://docs.dremio.com/current/sonar/client-applications/drivers/arrow-flight-sql-odbc-driver When connecting in Sisense I got an error when trying to use the DSN option. I was only able to connect by 'Connection String' based on the format found here: https://www.connectionstrings.com/dsn/ I was now able to add the tables but running the query returned an error " ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query". This error was due to Sisense putting [] around the table names in the query. The error was fixed by going into Elasticube/Change Connectivity Settings and selecting 'Manual Query Input' which replaced the square brackets with double quotes "".