Importing Data

Overview

Importing External Data - Drag and Drop

Importing External Data - Data Sources Tab

Importing External Data - Connecting to an ORACLE Database

Importing External Data - SQL Server Connection Format

Creating an Internal Data Set

 

Overview

There are two key types of datasets

 

1. External Data

In this case a link is established pointing to a database table, or a file and data is imported each time AnalyticsCanvas refreshes the link. This is how you get most data into AnalyticsCanvas- you can pull in tables from lots of different databases, text files and excel files.

 

2. Internal Data

In this case the data has no connection to the original source and is inside AnalyticsCanvas. It is copied into AnalyticsCanvas and is saved with the .ACC file. This type of data set is size limited to a quarter of a million of values per dataset, and if you have a lot of these it will increase the size of your ACC file. But it does let you create small datasets and reference sets instantly. It's a fast way to build little test sets or dimensional tables.

 

Importing External Data - Drag and Drop

One easy way to get data from text files or Excel files is to simply drag one or more files from the Windows file explorer directly onto the AnalyticsCanvas canvas. This will import the files, and create default import blocks on the canvas. To edit the configuration (change text import settings, or excel ranges for example) you can select the Data sources tab, and select the data source, or simply click on any input block using that data source, and the Data sources tab will open, and select the source for editing.

 

Importing External Data - Data Sources Tab

This will bring you to the data sources repository, and by adding a new data set you can import data from a number of different sources. To add a data source use the menu at the top.

The data sources repository holds all the data sets that are available to you on the data canvases, and lets you reuse a given data set as many times as needed by simply adding data table blocks (drag and drop the table from the Data sources tab onto the canvas and a block will be created. In this way, if you change a given data source, it will be changed throughout your AnalyticsCanvas canvases.

Importing External Data - Connecting to an ORACLE Database

You can connect to an Oracle database in one of two ways- either by specifying an alias that is included in your tnsnames.ora file, or by providing the detailed information regarding the server, port, etc.

To use a TNSNAMES.ORA file, a copy of the file should be placed directly into the AnalyticsCanvas program files folder- this is often "C:\Program Files\nModal Solutions Inc\AnalyticsCanvas" AnalyticsCanvas brings its own Oracle client assemblies with it, so there will not be any version issues, however it will look first in this program folder to find the tnsnames.ora file. By putting a copy there, you can use any descriptions for data sources.

Importing External Data - SQL Server Connection Format

If SQL Server Table is selected, the connection dialog for an SQL Server database will be presented;

 

 

There are various formats that can be used in the SERVER field to specify the SQL Server database you wish to connect to.

 

IP Address or Server Name and Port

x.x.x.x,myport

myservername,myport

 

Local Machine - SQL Server or SQL Express

.\ for SQL server installed on the same machine.

.\SQLExpress for SQL server express edition installed on the same machine.

 

Database Security

You can use your windows login, or enter a specific SQL username and password. The SQL Server instance being connected to must allow SQL Server authentication for the second option to work.

 

Selecting a Database and Table

Once you have entered the server and specified the login, then you can select which database within the SQL Server instance using the database combobox.

Once the database is selected, you will see a dialog which allows you to select the table or view to import, or lets you pick the option of using an SQL Query to fetch the data;

 

If you un-check the "Include all columns" check box, you will be prompted for each table and view selected to specify which columns to include- this can be very useful if you have wide tables with columns that are not of interest. From the data store repository you can modify the data sources, convert data types,add calculated columns and reorder the colums. This of course can also be done at any point in the AnalyticsCanvas canvas by using a calculation block.

Selecting the "Use SQL Query" tab will let you define, and test an SQL Query to get the data set from the database.

 

 

And finally, your block appears on the canvas ready to go. You can edit the SQL any time by selecting the source in the Data sources tab.

 

 

Creating an Internal Data Set

Copy and paste data onto the canvas

At any point you can copy data directly onto the AnalyticsCanvas canvas from Excel, or from any data grid within AnalyticsCanvas itself. When you do this an Internal data set block will be created. You can paste onto the canvas by right clicking, or by clicking on the canvas and using the CTRL-V. The following steps summarize this process:

1. First select the data you want in excel, copy it to the clipboard.

 

 

2. Then right click the canvas and select paste.

 

 

Once the data is pasted into the canvas, an internal data set block is created, and editing this block provides the ability to modify the data set and its structure.