CData Virtuality [Archived]

At present, Data Virtuality Server only supports integers in FROM_UNIXTIME and TIMESTAMPADD. As 2147483647 is the maximum positive value for a 32-bit signed binary integer, this means that larger values won't work. But we have a workaround for that.Let's consider an example where the value is greater than 2147483647:select from_unixtime( 4154192899 ) This query return the following:Error: TEIID30070 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'from_unixtime(4154192899)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.SQLState: TEIID30070ErrorCode: 0 And here's where we can use our workaround:selecttimestampadd(  SQ

In some cases, query performance can be speedied up thanks to the filter parameter. In this guide, we'll consider an example where it helped to reduce load time from 30 minutes to just one minute.Here's the original request:SELECT parseDate(w."date",'yyyyMMdd') AS Datum ,       w."date",       w.hour,       'WINDELBAR' AS "Reason Code" ,       w.pagePath,       w.landingPagePath,       w.exitPagePath,       w.previousPagePath,       w.nextPagePath,       CAST(entrances as integer) AS entrances,       CAST(exits as integer) AS exits,       CAST(pageviews as integer) AS pageviewsFROM (    EXEC analytics.get(        profile=>'1234567',        startDate=> '2014-01-01',        endDate=> '2014-01-07',        metrics=>'entrances,exits,pageValue,pageviews',        dimensions=>'date, hour, pagePath,landingPagePath,exitPagePath,prev

Let's suppose that we have a Google Analytics query like this, which however is subject to sampling by Google Analytics because the data are just too large. SELECT visitors, newVisits, percentNewVisits, visitorType, visitCount, daysSinceLastVisit, userDefinedValue ,parseDate("date",'yyyyMMdd') as tdateFROM table( exec GoogleAnalytics1.get( profile=>'12345678', startDate=>'2013-08-01', endDate=>curdate(), metrics=>'visitors,newVisits,percentNewVisits', dimensions=>'date,visitorType,visitCount,daysSinceLastVisit,userDefinedValue' )) aThe solution is to get the data batchwise into a data warehouse table.Step 1: execute this code. It gets the initial part of the data, for example one month. After this step, please check that the table dwh.google_history contains at least one data row. SELECT visitors, newVisits, percentNewVisits, visit

When native queries are enabled in your Amazon Redshift data warehouse (this can be done by adding supportsNativeQueries=true as a translator property to the data source configuration), you can easily view the 200 most recent queries sent to your data warehouse:SELECT x.* FROM table( call "dwh.native"("request" => 'select query, pid, elapsed, substring from svl_qlog ORDER BY starttime DESClimit 200') ) w,ARRAYTABLE( w.tuple COLUMNS query string, pid integer , elapsed string, "substring" string ) x; For more detail, you can see Redshift documentation: View Recent Queries and SVL_QLOG.

Googles response from the server is in UTF-8 but some columns maintain their original charset, so your Umlauts or other special charaters will look defect ( e.g. campaignName).To fix it, you can use TO_CHARS and TO_BYTES.In Germany these columns are often encoded in windows-1252, so your view can be fixed like in this Example: create view views.adwords_clicks_per_campaignAS select        adwords.customer_id.customerId        ,campaignId        ,CAST( TO_CHARS( TO_BYTES( "campaignName", 'windows-1252' ) , 'UTF-8' )  as string ) as "campaignName"        ,accountId        ,clicks    from        adwords.customer_id        ,table (            exec "adwords"."getCAMPAIGN_PERFORMANCE_REPORT" (                adwords.customer_id.customerId                ,'2013-01-01'

If you're using Facebook API, it's important to remember that by default, most responses to API requests are paginated by default. In this article, we describe how to work with paged results.Facebook will return a next and previous element in every request, depending on if there are pages left to fetch:next: the Graph API endpoint that will return the next page of data. If not included, this is the last page of data. Due to how pagination works with visibility and privacy it is possible that a page may be empty but contain a 'next' paging link - you should stop paging when the 'next' link no longer appears. previous: the Graph API endpoint that will return the previous page of data. If not included, this is the first page of data.To handle this in D

After adding a Salesforce data source, you can access your Salesforce account's data in a relational manner. However, the Salesforce data model itself has a few limitations which do not exist in regular relational databases.For example, Salesforce does not support JOINs between arbitrary tables. Tables involed in a JOIN must be in a parent-to-child or child-to-parent relationship. Otherwise, you will get the following error message:ERROR at Row:1:Column:3071Didn't understand relationship 'custom_table__cs' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.There are two options to circumvent this error:Disable JOIN pushdown: By default, all JOINs are pushed down to Salesforce. You can disable this behaviour to let DataVirtuality calculate the JOIN in memory instead. This can be se

Dependent replication jobs can help you to improve the running time and system resource consumption for queries and views that are either often required or involve data sources with a huge amount of data (several millions of rows). In this article, we'll walk you through their implementation using an example based on the Adventure Works sample database. In this scenario, there are two data sources hosted on a MySQL server and we want to improve the performance of a simple join between two tables from these sources.The guide is a step by step walkthrough and by no means complete. In the environment used for the explanation, there is a fresh setup of Data Virtuality Server and Studio with just the data warehouse connection and two data sources. If you are reading this and have an environment already in use with several data sources, materialized tables, and more, you will find some steps that you might skip (tables already materialized or replication jobs already existing and j

Depending on the locale of the server's operating system, the WEEK function used on a Sunday may return the next week, as Sunday is set as the first day of the week.This can be tested withSELECT WEEK('2014-01-05')which will return 1 on a German locale and 2 on a US locale.In order to set Data Virtuality Server to the German locale, append the following line to standalone.conf.props.bat (Windows):set "JAVA_OPTS=%JAVA_OPTS% -Duser.country=DE -Duser.language=de"On UNIX systems, append the following line to standalone.conf.props:JAVA_OPTS="$JAVA_OPTS -Duser.country=DE -Duser.language=de"

When materializing Salesforce tables, or views involving a Salesforce table, you may see the following error: Next Exception: ERROR: value too long for type character varying(50)0 The reason for this is a mismatch between the table's metadata reported by Salesforce and the actual data. In some cases, Salesforce describes a column to be of type varchar(50), although this column does contain strings longer than 50 characters. To work around this issue, you may create a new view, which is based on the original Salesforce table. This view must cast the erroneous column to its correct maximum length. Then, materialize that view instead of the original Salesforce table and use only this view in all further queries. CREATE VIEW salesforce_data_model.customer__c ASSELECT CAST(Name AS varchar(500)) AS NameFROM salesforce.customer__c

When writing data to PostgreSQL from a MySQL source, e.g. when materializing a table or view, you may encounter the following error: TEIID30504 TEIID30020 Processing exception for request -8112269768431953610.-1358838137594235897 'TEIID30504 dwhSOU:Next Exception: ERROR: invalid byte sequence for encoding "UTF8": 0x000 TEIID11013:BatchUpdateException-Batch entry 1,583 INSERT INTO dwh.mat_table_588_st0 Strings in MySQL may contain NULL characters (0x000) which are not supported by PostgreSQL. Therefore, those NULL characters need to be replaced before writing the string to PostgreSQL. This can be achieved by editing the PostgreSQL data source and adding translator parameter replaceNullCharsWith="". This will replace the NULL characters with an empty string so that the string can now be saved to PostgreSQL.

You can't find a column in your mongodb data source, but you think its there? Please check first, if it really exists. Please open your favorite MongoDB client and type the following to find "myElement"db.collection.find( {myElement: { $exists: true } } )If this is the case, the column was added recently and is not present in the first rows, so you have to adjust your data source Parameters, and increase the mongodb.maxRowsNumber model property. Please refer to 13.9 MongoDB Connector in the Reference Guide for more information.To find out how many rows you have in your MongoDB collection typedb.collection.count()

In this article, we'll describe how to connect an OLAP cube and how to query it.PrerequisitesInstalled SSAS with a processed OLAP cube. To create an OLAP cube, you can use this tutorial, and Microsoft SQL Server Data Tools can be downloaded here; A user account with full permissions on Analysis Services database and cube; IIS Web Server providing an application which points to the SSAS instance. For detailed information on implementation, please see Microsoft documentation; Network acces from Data Virtuality to the web server.Connection Click 'Add Data Source', scroll down to Other and

If you have an error likeUnexpected multi-valued result was returned for XMLTable column ...you try to address a PATH in an XML document, that occurs multiple times.Example. Imagine you have this JSON document: { "data": [{ "type": "articles", "id": "1", "attributes": { "title": "JSON API paints my bikeshed!", "body": "The shortest article. Ever.", "created": "2015-05-22T14:56:29.000Z", "updated": "2015-05-22T14:56:28.000Z" }, "relationships": { "author": { "data": {"id": "42", "type": "people"} } } }], "included": [ { "attributes": "a", "attributes": "b", "attributes": "c" } ]}this will be processed to XML using jsontoxml into 

Creating a Unix timestamp for the current timestamp: SELECT TIMESTAMPDIFF(SQL_TSI_SECOND, FROM_UNIXTIME(0), NOW());;  Creating a Unix timestamp for any given date or timestamp:  SELECT TIMESTAMPDIFF(SQL_TSI_SECOND, FROM_UNIXTIME(0), mydateOrTimestamp);;

This is an error which occurs if the given object cannot be found. Typical causes and Solutions are:Syntax error -> correct the typo. The object was only temporary, like temporary tables -> Use the query in combination with a persistent table or check if you actually want to refer to the table "foo.bar" or maybe another one. The object refers to a table which Data Virtuality shows as belonging to a data source -> perform a metadata refresh of the data source to check if the object was removed or renamed at the other end Point. The object refers to an element created by yourself and you are sure that there are no typos -> Check the table SYSADMIN.ViewDefinitions, SYSADMIN.ProcDefinitions and SYS.Tables for any errors explaining why the object's definition exists but it is not available.

The following limitations are set by DHL and you have to ensure that your queries (especially subqueries) do not violate them. Otherwise, you will receive error messages from the server. The limitations are as follows :1000 requests per day (00:00 - 23:59) 10,000 requested IDs per day (00:00 - 23:59) 3 requests per second (you might want to use the Sleep Functionality) dGetPiece can be used with up to 20 IDs (separated with ';') Please be careful that your queries always comply with the caps. If not, you may risk that the DHL server will temporarily (or permanently) block your IP address for requests. We strongly recommend to make use of the allowance for 20 IDs per requests. Hence, you can get information of ~60 shipments per second within the limits.This article also contains API Information concerning the Track and Trac

When enabling optimizations, you might want to make sure that each optimization has at least one schedule. This ensures that optimizations are refreshed regularly in order to return recent data.For verifying that every optimizations is covered by a schedule, you can run the following code which will return all optimizations that currently lack a schedule:SELECT ro.id   ,ro.Matchdescriptor   ,sj.description   ,sd.* FROM   "SYSADMIN.RecommendedOptimizations" ro   left join "SYSADMIN.ScheduleJobs" sj   on ro.id = sj.groupId   left join "SYSADMIN.Schedules" sd   on sj.ID = sd.jobID WHERE   "ro"."Enabled" = true   and sd.ID IS NULL;;

(A note: for passing variable number of parameteres into a stored procedure, you can also use the approach described here )To pass key value pairs to a procedure, you can use multi-dimensional arrays - here's an example:BEGINDECLARE object params = ARRAY( ARRAY( 'a', 'b') , ARRAY( 'c', 'd')  );DECLARE integer VARIABLES.i = 0;CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" object, "value" object);WHILE( i < array_length(params)) BEGIN INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number", params[i+1][1] AS "key", params[i+1][2] as "value"; VARIABLES.i =  VARIABLES.i + 1;END

The following error message "Streaming result set com.mysql.jdbc.RowDataDynamic@XXXXXX is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries" comes from MySQL.The reason for this lies in the streaming mode of MySQL. Streaming mode is the fastest way to get data from MySQL, and Data Virtuality uses this mode to transfer the data. However, streaming mode has one limitation: When a resultset is open in streaming mode on a MySQL connection, you cannot use that same database connection to run other queries. This is exactly what the error message above tells. The solution to the problem would be to change the MySQL connection settings to use CursorFetch mode instead of Streaming mode.To do this, you need to edit the MySQL connection in Data Virtuality to do the followingPress Edit on yo

Data Virtuality Suite contains a dedicated JDBC Driver in order to enable reporting tools and the Data Virtuality Studio to connect to the Data Virtuality Server using the JDBC protocol. Prior to using such BI tools, the Data Virtuality JDBC Driver must be installed. To install the JDBC Driver, simply put the datavirtuality-jdbc.jar file, which is downloadable from your data virtuality server, into a folder where the particular Reporting Tool can find it. To connect to Data Virtuality Server via JDBC make your BI / Reporting tool registering the supplied datavirtuality-jdbc.jar driver.For connecting use, for example, the following URLs:JDBC connection stringjdbc:datavirtuality:datavirtuality@mm://localhost:31000JDBC over SSL connection stringjdbc:datavirtuality:datavirtuality@mms://localhost:31001Example using the SQuirre

This is a short guide on how to get data from Data Virtuality in Qlik.Please note that:Qlik uses Windows standard language settings; The ODBC- Driver Package from Data Virtuality must be installed up front; Qlik only works with the 64Bit ODBC connector. To connect Qlik via ODBC, please do the following:Download the Qlik application; Install and open Qlik; Create a new workspace (called APP in Qlik); Click on "Add new data":Choose ODBC:Use the 64 Bit ODBC

This is a short introduction on how to connect Data Virtuality Server to MS Excel via an ODBC connection. It is important to you use matching Excel and driver. For example, if you have installed the 64 Bit ODBC driver, then you need to have a 64 Bit Excel. Otherwise, it will not work.The Excel which was used to take the snapshots was in German.To connect Data Virtuality Server to MS Excel via ODBC, please do the following:Click "Data"; Click "Other source"; Click "Microsoft query" for example: Choose the correct ODBC driver and click "OK":

Motivation:This is a short example to demonstrate how to access Data Virtuality via an ODBC connection in F#. Please keep in mind that this is not an F#/.NET code and the sample just shows a basic approach for the ODBC connection.Requirements:The system which shall access Data Virtuality Server needs to have at least the ODBC drivers installed. A DSN connection is also possible but requires the ODBC drivers as well. They can be obtained from the web server which is implemented in Data Virtuality (see https://datavirtuality.zendesk.com/hc/en-us/articles/115002027906-Components-downloadable-from-DV-site) Simple code sample:The following sample creates a connection string based on a DSN. It is then used for a new ODBC connection and the program executes the query "SELECT * FROM dwh.foo" on this connection.Afterwards, the n

The following snippet can be used with Data Virtuality Server 2.0.We assume the the FTP / SFTP server is already used as a data source in Data Virtuality. Therefore we can use the FTP data source directly as the target schema.call "UTILS.csvExport"("sourceSchema" => '<sourceSchema>',"sourceTable" => '<sourceTable>',"targetSchema" => '<NameOfFTPdatasource>',"targetFile" => '<targetFile>',"delimiter" => '<delimiter>',"quote" => '<quote>',"header" => <trueORfalse>,"orderBy" => 'string_orderBy',"encoding" => '<encoding>');;The following parameters can be deleted if not used:targetFile delimiter quote header order by encoding