Redshift~<- Denodo!<-- Tableau!<- TDE -Large Data volume Extracts:

Redshift~<- -large="" data="" denodo="" extracts:="" tableau="" tde="" u="" volume="">

1: Creation of Large Extract in Tableau from Data sources with high volume of data at Low grain have challenges in getting the Extract.

2: Analyze the Table Size and time taken for creation of the extract with the data model design and SQL send to different layers in the Process.

3: Tableau Configuration for the creation of Large Data sets in TDE, TDC. ( timeout, DSN parameters etc..) are critical  to overcome the errors.

4: Tableau Server errors while trying to create  extract ( Communication Protocol, Resource limit Reached.. etc) (7200).

5: Creating Extract via virtual Layer takes more time than direct connection using Native Drivers ( 1:2 ) Ratio.

6: Estimate the size of the table and number of columns and how much time it takes to create TDE ( 25m with 350 columns consumes 90 gb space takes 2 hrs).

7: Overcome the tableau server errors by setting the query limit and time out settings as required by our extract ( change & configure).

8: Tweak the Odbc DSN setting for the right combination for the extract creation after the analysis, ( Timeout, UseDeclareFetch & Cache  Size) (4hrs:6:10000).

9: Out of Memory Error while reading Tuples -- support for CURSORS and Amazon Redshift -  Configure and create TDC file as required.

10: using Native driver and direct connection to RS in creation of extract drops connection @ 907 seconds with Protocol error -- Unknown / open  issue

11:Key Aspects to be considered (Optimal, compression, encoding, Dist Key, sort Key, aggregation & apply filters to make use of the keys)

Denodo Server - Setup & configure Cache Database to MySQL:

Denodo Server - Setup & configure Cache Database: - Bottlenecks

Step1:

Install the Denodo Server by clicking the executable and follow the steps with giving inputs and files as required to complete the server install.

There will be 8 steps for the entire process and it will take 10-15 minutes to complete the install

Step2: bottlenecks faced

The Admin tool was giving the connection Error when connecting to the VDP Server due to reasons below           

                      Connection Error -

                      Service on Denodo gets started and stopped immediately

                      Log file shows the license error message

Solution tried was to close all the admin tool of denodo , start the denodo vdp server and scheduler and open the Admin tool to connect to server - it works

Step3 : Denodo Setup activities to import the VQL or create a new data base and data source connection as required ( normal Admin & Developer stuff)

Step 4: Validation of the new database created on denodo and querying the underlying objects to make sure everything is working as expected. Also check the respective denodo Server and scheduler logs respectively to verify the errors.

Step5: by default the Embedded Derby will be configured as cache database for the objects, now we will try to replace with MySQL database for Cache Loading & Performance

Step6 - My SQL - Install and configure database:

Bottleneck. Faced:

                      Prerequisites were not met so ended up in install of Microsfot Dotnet Framwork ( Visual c++ 20003 Kit for install to continue successfully.

                      Command prompt execution of my sql commands to connect, create & configure the cache database with required options

Step7: Configuration of the my sql cache database to Denodo Database Cache options.

Bottleneck faced

                com.jdbc mysql driver error when the new database is configured in denodo

                Could not change the cache parameters- unable to find the jar / driver

Step8: Place the Jar file (my SQL connector JAR) file in both the location of the lib folder in Denodo and My sql it worked and was able to successfully configure the new database

Step9: Configure the cache for the object and Load the cache, invalidate the cache and repeat few iterations to make sure the functionally works and its consistent.