5 Adding Data Sources for Analyzing and Exploring Data

You can add your own data to visualizations for analysis and exploration.

Topics:

Typical Workflow for Adding Data Sources

Here are the common tasks for adding data from data sources.

Task	Description	More Information
Add a connection	Create a connection if the data source that you want to use is either Oracle Applications or a database.	Creating Oracle Applications Connections Creating Database Connections
Create a data set	Upload data from spreadsheets. Retrieve data from Oracle Applications and databases. Creating a data set from Oracle Applications or a database requires you to create a new connection or use an existing connection.	Adding a Spreadsheet as a Data Source Connecting to Oracle Applications Data Sources Creating Data Sets from Databases
Blend data	Blend data from one data source with data from another data source.	Blending Data that You Added About Changing Data Blending
Refresh data	Refresh data for the files when newer data is available. Or refresh the cache for Oracle Applications and databases if the data is stale.	Refreshing Data that You Added
Extend uploaded data	Add new columns to the data set.	Modifying Uploaded Data Sets
Control sharing of data sets	Specify which users can access the data that you added.	Controlling Sharing of Data You Added
Remove data	Remove data that you added.	Removing Data from a Project

About Data Sources

A data source is any tabular structure. You get to see data source values after you load a file or send a query to a service that returns results (for example, another Oracle Business Intelligence system or a database).

A data source can contain any of the following:

Match columns - These contain values that are found in the match column of another source, which relates this source to the other (for example, Customer ID or Product ID).
Attribute columns - These contain text, dates, or numbers that are required individually and aren’t aggregated (for example, Year, Category Country, Type, or Name).
Measure columns - These contain values that should be aggregated (for example, Revenue or Miles driven).

You can analyze a data source on its own, or you can analyze two or more data sources together, depending on what the data source contains.

When you save a project, the permissions are synchronized between the project and the external sources that it uses. If you share the project with other users, then the external sources are also shared with those same users.

Combining Subject Areas and Data Sources

A subject area either extends a dimension by adding attributes or extends facts by adding measures and optional attributes. Hierarchies can’t be defined in data sources.

A subject area organizes attributes into dimensions, often with hierarchies, and a set of measures, often with complex calculations, that can be analyzed against the dimension attributes. For example, the measure net revenue by customer segment for the current quarter and the same quarter a year ago.

When you use data from a source such as an Excel file, it adds information that is new to the subject area. For example, suppose you purchased demographic information for postal areas or credit risk information for customers and want to use this data in an analysis before adding the data to the data warehouse or an existing subject area.

Using a source as standalone means that the data from the source is used independently of a subject area. It’s either a single file used by itself or it’s several files used together and in both cases a subject area is not involved.

Note the following criteria to extend a dimension by adding attributes from a data source to a subject area:

Matches can be made to a single dimension only.
The set of values in matched columns must be unique in the data source. For example, if the data source matches on ZIP code, then ZIP codes in the source must be unique.
Matches can be between one or composite columns. An example of a one column match is that product key matches product key. For composite columns, an example is that company matches company and business unit matches business unit.
All other columns must be attributes.

Note the following criteria for adding measures from a data source to a subject area:

Matches can be made to one or more dimensions.
The set of values in matched columns doesn’t need to be unique in the data source. For example, if the data source is a set of sales matched to date, customer, and product, then you can have multiple sales of a product to a customer on the same day.
Matches can be between one or composite columns. An example of a one column match is that product key matches product key. For composite columns, an example is that company matches company and business unit matches business unit.

A data source that adds measures can include attributes. You can use these attributes alongside external measures and not alongside curated measures in visualizations. For example, when you add a source with the sales figures for a new business, you can match these new business sales to an existing time dimension and nothing else. The data might include information about the products sold by this new business. You can show the sales for the existing business with those of the new business by time, but you can’t show the old business revenue by new business products, nor can you show new business revenue by old business products. You can show new business revenue by time and new business products.

Working with Sources with no Measures

Note the following if you’re working with sources with no measures.

If a table has no measures, it’s treated as a dimension. Note the following criteria for extending a dimension:

Matches can be between one or composite columns. An example of a one column match is that product key matches product key. For composite columns, an example is that company matches company and business unit matches business unit.
All other columns must be attributes.

Dimension tables can be matched to other dimensions or they can be matched to tables with measures. For example, a table with Customer attributes can be matched to a table with demographic attributes provided both dimensions have unique Customer key columns and Demographic key columns.

Working with Sources with Measures

Note the following if you are working with sources with measures.

You can match tables with measures to other tables with a measure, a dimension, or both.
When you match tables to other tables with measures, they don’t need to be at the same grain. For example, a table of daily sales can be matched to a table with sales by Quarter if the table with the daily sales also includes a Quarter column.

Working with Matching

If you use multiple sources together, then at least one match column must exist in each source. The requirements for matching are:

The sources contain common values (for example, Customer ID or Product ID).
The match must be of the same data type (for example, number with number, date with date, or text with text).

Connecting to Database Data Sources

You can create, edit, and delete database connections and use the connections to create data sources from databases.

Topics:

Creating Database Connections

You can create connections to databases and use those connections to source data in projects.

When you create a connection to an Oracle database to visualize data, Oracle recommends the usage of credentials of users who have access to the required tables in the database. Note that a database user can visualize a maximum number of 10K tables for a schema.

Display the Create Connection dialog using one of these actions:
- In the Home page, Projects page, or the Data page, click Create, then click Connection to display the Create Connection dialog.
- In the Projects Data Elements pane, click Add and select Add Data Set, then click Create Data Set, and click Create Connection.
In the Create Connection dialog, select a database, and enter the connection details, such as host, port, and so on.

Note:
You can create connections only to Oracle databases.
Click Save.
You can now begin creating data sets from the connection. See Creating Data Sets from Databases.
In the Create Connection dialog, click the icon for the connection type that you want to create a connection for (for example Oracle Database ). See Supported Data Sources.
(Optional) When you connect to some database types, you might have to specify the following authentication options on the Create Connection and Edit Connection dialogs:
- Enable Bulk Replication - If you’re loading a data set for a Data Visualization project, then this option should be turned off and you can ignore it. This option is reserved for data analysts and advanced users for replicating data from one database to another database.
- Authentication
  - Select Always use these credentials, so that the login name and password you provide for the connection are always used and users aren’t prompted to log in.
  - Select Require users to enter their own credentials when you want to prompt users to enter their own user name and password for the data source. Users required to log in see only the data that they have the permissions, privileges, and role assignments to see.

Creating Data Sets from Databases

After you create database connections, you can use those connections to create data sets.

You must create the database connection before you can create a data set for it. See Creating Database Connections.

On the Home page click Create and click Data Set to open the Create Data Set dialog. In the Create Data Set dialog, select Create Connection and use the Create Connection dialog to create the connection for your data set.
In the Data Set editor, first browse or search for and double-click a schema, and then choose the table that you want to use in the data set. When you double-click to select a table, a list of its columns is displayed.
You can use breadcrumbs to quickly move back to the table or schema list.
In the column list, browse or search for the columns you want to include in the data set. You can use Shift-click or Ctrl-click to select multiple columns. Click Add Selected to add the columns you selected, or click Add All to include all of the table's columns in the data source.
Alternatively, you can select the Enter SQL option to view or modify the data source’s SQL statement or to write a SQL statement.
You can also optionally perform the following steps:
- After you’ve selected columns, you can go to the Step Editor at the top of the Data Set editor and click the Filter step to add filters to limit the data in the data set. After you’ve added filters, click Get Preview Data to see how the filters limit the data.
- Go to the Step Editor at the top of the Data Set editor and click the last step in the Step Editor to specify a description for the data source.
- Go to the Step Editor at the top of the Data Set editor and click the last step in the Step Editor and go to the Refresh field to specify how you want to refresh the data in the data source. Note the following information:
  - Select Live if you want the data source to use data from the database directly rather than copying the data into the cache. Typically because database tables are large, they shouldn’t be copied to Data Visualization's cache.
  - If your table is small, then select Auto and the data is copied into Data Visualization’s cache if possible. If you select Auto, you must refresh the data when it’s stale.
Click Add. The View Data Source page is displayed.
In the View Data Source page you can optionally view the column properties and specify their formatting. The column type determines the available formatting options. See Modifying Uploaded Data Sets.

Editing Database Connections

You can edit a database connection. For example, you can change the name of the connection.

On the Data page, click Connections.
Mouse over the connection that you want to edit. To the right of the highlighted connection, click Actions menu, and select Edit.
In the Edit Connection dialog, edit the connection details, and then click Save.

Note:
You can’t see the current password, service name, or Logical SQL for your connections. If you need to change these, you must enter a new connection.

Deleting Database Connections

You can delete a database connection. For example, you must delete a database connection and create a new connection when the database's password has changed.

Note:

If the connection contains any data sets, then you must delete the data sets before you can delete the connection.

Go to the Data page and select Connections.
Select the connection that you want to delete and click Actions menu or right-click, then click Delete.
Click Yes.

Connecting to Oracle Applications Data Sources

You can create Oracle Application data sources that help you visualize, explore, and understand the data in your Oracle Fusion Applications with Oracle Transactional Business Intelligence and Oracle BI EE subject areas and analyses.

Topics:

Creating Oracle Applications Connections

You can create connections to Oracle Applications and use those connections to create data sets.

You use the Oracle Applications connection type to create connections to Oracle Fusion Applications with Oracle Transactional Business Intelligence, and to Oracle BI EE. After you create a connection, you can access and use subject areas and analyses as data sets for your projects.

On the Home page, the Projects page, or the Data page, click Create, and then click Connection.
Click the Oracle Applications icon.
In the Create Connection dialog, enter the connection name, the Oracle Fusion Applications with Oracle Transactional Business Intelligence or Oracle BI EE URL, the user name, and password.
Note the following information:
- HTTP and HTTPs URLs are supported.
- Connection names must be unique and also not contain any special characters.
- You can obtain the connection URL.
  1. Open the Oracle application you want to connect to.
  2. Display the catalog folders containing the analysis you want to use as your data set.
  3. Copy the browser URL into the Create Connection dialog.
    
    Make sure that you strip off anything after /analytics. For example, http://www.example.com:9704/analytics.
Click Save.

Now you can create data sets from this connection. See Composing Data Sources From Oracle Application Connections

Note:

The connection is visible only to you (the creator), but you can create and share data sets for it.

Composing Data Sets from Subject Areas

You use the Oracle Applications connection type to access the Oracle Fusion Applications with Oracle Transactional Business Intelligence and Oracle BI EE subject areas that you want to use as data sets.

You must create an Oracle Applications connection before you can create a subject area data set. See Creating Oracle Applications Connections.

In the Data Visualization Home page, Data page, or the Projects page, click Create, click Data Set, then click Connection and use the Create Connection dialog to specify the details for your data set.
In the Data Set editor, choose Select Columns to view, browse, and search the available subject areas and their columns that you include in your data set. You can use breadcrumbs to quickly move back through the directories.
You can also optionally perform the following steps:
- In the breadcrumbs click the Add/Remove Related Subject Areas option to include or exclude related subject areas. Subject areas are related when they use the same underlying business or logical model.
- After you’ve selected columns, go to the Step Editor at the top of the Data Set editor and click the Filter step to add filters to limit the data in the data set. After you’ve added filters, click Get Preview Data to see how the filters limit the data.
- Click Enter SQL to display the logical SQL statement of the data source. View or modify the SQL statement in this field.
  
  Note:
  If you edit the data source’s logical SQL statement, then the SQL statement determines the data set and any of the column-based selection or specifications are disregarded.
- Go to the Step Editor at the top of the Data Set editor and click the last step in the Step Editor to specify a description for the data set.
Before saving the data set, go to the Name field and confirm its name. Click Add.
The Data Set page is displayed.
In the Data Set page you can optionally view the column properties and specify their formatting. The column type determines the available formatting options. See Modifying Uploaded Data Sets.

Composing Data Sets from Analyses

You use the Oracle Applications connection type to access the analyses that were created in Oracle Fusion Applications with Oracle Transactional Business Intelligence and Oracle BI EE subject areas that you want to use as data sources

You must create an Oracle Applications connection before you can create an analysis data set. See Creating Oracle Applications Connections.

On the Home page click Create and click Data Set to open the Create Data Set dialog. In the Create Data Set dialog, select Create Connection and use the Create Connection dialog to create the connection for your data set.
In the Data Set editor, select the Select an Analysis option to view, browse, and search the available analyses to use in your data set.
You can use breadcrumbs to quickly move back through the directories.
Double-click an analysis to use it for your data set. The analysis’ columns are displayed in the Data Set editor.
You can also optionally perform the following steps:
- Click Enter SQL to display the SQL Statement of the data set. View or modify the SQL statement in this field.
- Click a column’s gear icon to modify its attributes, like data type and whether to treat the data as a measure or attribute.
- Go to the Step Editor at the top of the Data Set editor and click the last step in the Step Editor to specify a description for the data set.
Before saving the data set, go to the Name field and confirm its name. Click Add.
The Data Set page is displayed.
In the Data Set page you can optionally view the column properties and specify their formatting. The column type determines the available formatting options. See Modifying Uploaded Data Sets.

Editing Oracle Applications Connections

You can edit Oracle Applications connections. For example, you must edit a connection if your system administrator changed the Oracle Applications login credentials.

In the Data page, click Connections.
Locate the connection that you want to edit and click its Actions menu icon and select Edit.
In the Edit Connection dialog, edit the connection details. Note that you can’t see or edit the password that you entered when you created the connection. If you need to change the connection’s password, then you must create a new connection. See Creating Oracle Applications Connections.
Click Save.

Deleting Oracle Applications Connections

You can delete an Oracle Applications connection. For example, if your list of connections contains unused connections, then you can delete them to help you keep your list organized and easy to navigate.

Note:

If any data sets use the connection, then you must delete the data sets before you can delete the connection. Oracle Applications connections are only visible to the user that creates them (connections aren’t shared), but a user can create data sets using those connections, and share the data sets with others.

In the Data page, click Connections.
To the right of the connection that you want to delete, click Actions menu, and then select Delete.
Click Yes.

Creating Connections to Dropbox

You can create connections to Dropbox and use those connections to source data in projects.

In Data Visualization‘s Data page (or the Home page), click Create, then click Connection to display the Create Connection dialog.
Browse or search for the Dropbox icon. Click the Dropbox icon.

In the Add a New Connection dialog, enter a name for the connection, and then enter the required connection information:

Field	Description
Redirect URL	Confirm that the Dropbox application is open and its Settings area is displaying. Copy the URL in the Redirect URL field and paste it into the Dropbox application’s OAuth 2 Redirect URIs field and then click Add.
Client ID	Go to the Dropbox application, locate the App key field, and copy the key value. Go to Data Visualization and paste this value into the Client ID field.
Client Secret	Go to the Dropbox application, locate the App secret field, click Show to reveal the secret, and copy the secret value. Go to Data Visualization and paste this value into the Client Secret field.

Click Authorize. When prompted by Dropbox to authorize the connection, click Allow.
The Create Connection dialog refreshes and displays the name of the Dropbox account and associated email account.
Click Save.
You can now create data sets from the Dropbox connection. See Adding a Spreadsheet from Dropbox or Google Drive.

Creating Connections to Google Drive or Google Analytics

You can create connections to Google Drive or Google Analytics and use those connections to source data in projects.

Set up a Data Visualization application in Google, if you haven’t done so already.
1. Sign into your Google account, and go to the Developer’s Console.
2. Create a project, then go to the API Manager Developers area of the Google APIs site and click Create app to create and save a Data Visualization application.
3. Enable the application and create credentials for the application by accessing the Analytics API.
4. Open the page displaying the credential information, and paste the redirect URL provided by Data Visualization, and copy the Client ID and Client secret.
  Read the Google documentation for more information about how to perform these tasks.
In Data Visualization’s Data page (or the Home page), click Create, then click Connection to display the Create Connection dialog.
Browse or search for the Google Drive or the Google Analytics icon, and then click the icon.

In the Add a New Connection dialog, enter a connection name and enter the required connection information as described in this table.

Field	Description
Redirect URL	Confirm that the Google application is open and its Credentials area is displaying. Copy the URL in the Redirect URL field and paste it into the Google application’s Authorized redirect URIs field.
Client ID	Go to the Google application’s Credentials area, locate the Client ID field, and copy the key value. Go to Data Visualization and paste this value into the Client ID field.
Client Secret	Go to the Google application’s credential information, locate the Client secret field and copy the secret value. Go to Data Visualization and paste this value into the Client Secret field.

Click Authorize.
When prompted by Google to authorize the connection, click Allow.
The Create Connection dialog refreshes and displays the name of the Google account, and its associated email account.
Click Save.
You can now create data sets from the Google Drive or Google Analytics connection. See Adding a Spreadsheet from Dropbox or Google Drive.

Creating Generic JDBC Connections

You can create generic JDBC connections to databases and use those connections to source data in projects. For example, to connect to databases that aren’t listed with the default connection types.

This method enables you to use drivers in a JDBC Jar file to connect to specific databases.

The JDBC driver version must match the database version. A version mismatch can lead to spurious errors during the data load process. Even using an Oracle database, if the version of the JDBC driver doesn’t match that of the database, then you must download the compatible version of the JDBC driver from Oracle's website and place it in the \lib directory.

Confirm that you have copied the required JDBC driver’s JAR file into Data Visualization Desktop’s \lib directory.

For example, C:\Program Files\Oracle Data Visualization\lib.
In Data Visualization‘s Data page (or the Home page), click Create, then click Connection to display the Create Connection dialog.
In the Create Connection dialog, locate and click the JDBC icon.

In the Create Connection dialog, enter the connection criteria:

Field	Description
New Connection Name	Any name that uniquely identifies the connection. Avoid using instance-specific names such as host names, because the same connection can be configured against different databases in different environments (for example, development and production).
URL	The URL for your JDBC data source. See the documentation for the driver, and the JAR file for details on specifying the URL.
Driver Class Name	The name of the Driver Class. You can find the name in the JAR file, or from wherever you downloaded the JAR file.
Username	The database username.
Password	The database user password.

Click Save.
You can now create data sets from the connection. See Creating Data Sets from Databases.

Note:

If you import a project containing a JDBC connection into a Data Visualization installation where the JDBC driver isn’t installed, the import still works. However, the connection doesn’t work when you try to run the project or Data Flow. You must recreate the JDBC connection, and JDBC driver to a suitable data source.

Creating Generic ODBC Connections

You can create generic ODBC connections to databases and use those connections to source data in projects. For example, to connect to databases and database versions that aren’t listed with the default connection types.

You can only use generic ODBC connections to connect on Windows systems.

Confirm that the appropriate database driver is installed on your computer.

You must have the required database driver installed on your computer to create an ODBC Data Source Name (DSN). If you need to install a database driver, use installation instructions provided by the organization that supplies the database driver.
Create the new ODBC data source in Windows.
1. In Windows, locate and open the ODBC Data Source Administrator dialog.
2. Click the System DSN tab, and then click Add to display the Create New Data Source dialog.
  
  Windows uses ODBC DSNs to access the data source and for query execution.
3. Select the driver appropriate for your data source, and then click Finish.
4. The remaining configuration steps are specific to the data source you want to configure.
  
  Refer to the documentation for your data source.

Create the generic ODBC data source in Data Visualization.

In Data Visualization’s Data page (or the Home page), click Create, then click Connection to display the Create Connection dialog.
In the Create Connection dialog, locate and click the ODBC icon.

In the Create Connection dialog, enter the connection criteria:

Field	Description
Name	Any name that uniquely identifies the connection.
DSN	The name of the system DSN that you set up on your computer.
Username	The database username.
Password	The password for the database user.

Click Save.
You can now create data sets from the connection. See Creating Data Sets from Databases.

Note:

If you import a project containing an ODBC connection into a Data Visualization installation where the ODBC DSN doesn’t exist, and the ODBC driver isn’t installed, the import still works. However, the connection doesn’t work when you try to run the project or Data Flow. You must recreate the ODBC connection, and recreate the ODBC DSN, and ODBC driver to a suitable data source.

Creating Connections to Oracle Autonomous Data Warehouse Cloud

You can create connections to Oracle Autonomous Data Warehouse Cloud and use those connections to source data in projects.

Before you create connections to Oracle Autonomous Data Warehouse Cloud, you must enable the Oracle Autonomous Data Warehouse Cloud connection.
1. Edit the external_providers.json file in the following location
  <Oracle_Home>/bi/providers/external_providers.json.
2. In the supported-providers tag in the BI12C section, add the additional provider DWCS.
  For example:
  "BI12C" : ["NO_OP","DWCS" ,"OSCS", "ORACLE_10G_R2", "CUSTOM", "ORACLE_11G", "ORACLE_12C", "FA", "ELOQUA",
3. Save the file.
4. Restart the BI Services.
Get the client credentials zip file containing the trusted certificates that enable Data Visualization to connect to Oracle Autonomous Data Warehouse Cloud.
1. Obtain the cwallet.sso file from Oracle Autonomous Data Warehouse Cloud.
  
  See Downloading Client Credentials (Wallets) in Using Oracle Autonomous Data Warehouse Cloud.
  
  You extract the cwallet.sso file from wallet file. For example, from the file:
  
  wallet_ADWC1.zip
2. Copy the cwallet.sso file to the Windows Users location:
  
  <Oracle_Home>/user_projects/domains/bi/bidata/components/OBIS/dwcs
  
  If the folder isn't available, create it.
  
  This client credentials zip file contains the trusted certificates for client applications like Data Visualization to connect to Oracle Autonomous Data Warehouse Cloud.
3. Restart the BI Services.
On the Oracle Data Visualization Home page, click Create then click Connection to display the Create Connection dialog.
Click Oracle Autonomous Data Warehouse to display the fields for the connection.
Enter a connection name in the New Connection Name field.
Enter the remaining details as needed.
Click Save to create the connection.
You can now create data sets from the connection.

Creating Connections to Oracle Big Data Cloud

You can create a connection to access data in Oracle Big Data Cloud Compute Edition and use those connections to source data in projects.

You create an Oracle Big Data Cloud Service Compute Edition connection using these steps.

Before you can create connections to Oracle Big Data Cloud Service Compute Edition you must ensure that the connections are secure.
1. Download a certificate and generate a Java Key Store file for the corresponding Oracle Big Data Cloud Service Compute Edition environment.
  See About Accessing Thrift in Using Oracle Big Data Cloud.
2. Place the Java Key Store file in:
  %LOCALAPPDATA%\DVDesktop\components\OBIS\bdcsce
3. Restart Data Visualization Desktop.
In Data Visualization, click Create and then click Connection to display the Create Connection dialog.
Click Oracle Big Data Cloud to display the fields for the connection.
Enter a connection name in the New Connection Name field.
Enter the remaining details as needed.
Click Save to create the connection.
You can now create data sets from the connection.

Creating Connections to Oracle Talent Acquisition Cloud

You can create connections to Oracle Talent Acquisition Cloud (OTAC) to access data for analysis and use those connections to source data in projects.

Click Create and then click Connection to display the Create Connection dialog.
Click Oracle Talent Acquisition Cloud to display the fields for the connection.
Enter your connection name in the New Connection Name field.
Enter the URL for the Oracle Talent Acquisition Cloud connection.
For example, if the Oracle Talent Acquisition Cloud URL is https://example.taleo.net, then the connection URL that you must enter is https://example.taleo.net/smartorg/Bics.jss .
Enter your username and password in the corresponding fields.
Click an Authentication option:
- If you select Always use these credentials, then the login name and password you provide for the connection is always used and users aren’t prompted to log in.
- If you select Require users to enter their own credentials, then users are prompted to enter their user names and passwords to use the data from the Oracle Applications data source. Users who are required to log in see only the data that they have the permissions, privileges, and role assignments to see.
Click Save to create the connection.
You can now create data sets from the connection.

Adding a Spreadsheet as a Data Source

You can add a spreadsheet as a data source. You can browse for and upload spreadsheets from a variety of places, such as your computer, Google Drive, and Dropbox.

Topics:

About Adding a Spreadsheet as a Data Set

Data source files from a Microsoft Excel spreadsheet file must have the XLSX extension (signifying a Microsoft Office Open XML Workbook file). You can also add CSV and TXT files.

Before you can upload a Microsoft Excel file as a data set, you must structure the file in a data-oriented way and it mustn‘t contain pivoted data. Note the following rules for Excel tables:

Tables must start in Row 1 and Column 1 of the Excel file.
Tables must have a regular layout with no gaps or inline headings. An example of an inline heading is one that repeats itself on every page of a printed report.
Row 1 must contain the table’s column names. For example, Customer Given Name, Customer Surname, Year, Product Name, Amount Purchased, and so on. In this example:
- Column 1 has customer given names.
- Column 2 has customer surnames.
- Column 3 has year values.
- Column 4 has product names.
- Column 5 has the amount each customer purchased for the named product.
The names in Row 1 must be unique. Note that if there are two columns that hold year values, then you must add a second word to one or both of the column names to make them unique. For example, if you have two columns named Year Lease, then you can rename the columns to Year Lease Starts and Year Lease Expires.
Rows 2 onward are the data for the table, and they can’t contain column names.
Data in a column must be of the same kind because it’s often processed together. For example, Amount Purchased must have only numbers (and possibly nulls), enabling it to be summed or averaged. Given Name and Surname must be text as they might be concatenated, and you may need to split dates into their months, quarters, or years.
Data must be at the same granularity. A table can’t contain both aggregations and details for those aggregations. For example, if you have a sales table at the granularity of Customer, Product, and Year, and contains the sum of Amount Purchased for each Product by each Customer by Year. In this case, you wouldn’t include Invoice level details or a Daily Summary in the same table, as the sum of Amount Purchased wouldn’t be calculated correctly. If you have to analyze at invoice level, day level, and month level, then you can do either of the following:
- Have a table of invoice details: Invoice Number, Invoice Date, Customer, Product, and Amount Purchased. You can roll these up to day or month or quarter.
- Have multiple tables, one at each granular level (invoice, day, month, quarter, and year).

Adding a Spreadsheet from Your Computer

You can upload an Excel spreadsheet, CSV file, or TXT file dta source located on your computer to use as a data set.

Before you add a spreadsheet as a data set, confirm you’ve done the following:

Confirm that you have either an Excel spreadsheet in .XLSX format or a CSV or TXT file as the data source used to create a data set.
For an Excel spreadsheet, ensure that it contains no pivoted data.
Understand how the spreadsheet needs to be structured for successful import. See About Adding a Spreadsheet as a Data Set.

Follow these steps to add a spreadsheet from your computer and use it as a data source:

From the Home page, click Create, then click Data Set to display the Create Data Set dialog.
Click File and browse to select a suitable (unpivoted) XLSX file, CSV file, or TXT file.
Click Open to upload and open the selected spreadsheet in Data Visualization.
The Data Set editor is displayed.
Make any required changes to Name, Description, or to column attributes.
If you’re uploading a CSV or TXT file, then in the Separated By field, confirm or change the delimiter. If needed, choose Custom and enter the character you want to use as the delimiter. In the CSV or TXT file, a custom delimiter must be one character. The following example uses a pipe (|) as a delimiter: Year|Product|Revenue|Quantity|Target Revenue| Target Quantity.
Click Add to save your changes and create the data set.
If a data set with the same name already exists:
- Click Yes if you want to overwrite the existing data set.
- Click No if you want to update the data set name.

Adding a Spreadsheet from Dropbox or Google Drive

If you’re storing spreadsheets in Dropbox or Google Drive you can add a spreadsheet to create a data set.

Before you add a spreadsheet from Dropbox or Google Drive, do the following:

Confirm that a connection exists. See Creating Connections to Dropbox and Creating Connections to Google Drive or Google Analytics.
Confirm that the spreadsheet you want to use is either an Excel spreadsheet in .XLSX format, a CSV file, or a TXT file.
For an Excel spreadsheet, ensure that it contains no pivoted data.
Understand how the spreadsheet needs to be structured for successful import. See About Adding a Spreadsheet as a Data Set.

Use the following steps to add a spreadsheet.

In the Data Set editor, search or browse the Dropbox or Google Drive directories and locate the spreadsheet that you want to use.
You can use breadcrumbs to quickly move back through the directories.
Double-click a spreadsheet to select it. When you select a spreadsheet, its columns and data values are displayed.
Click Add to create the data set.

Creating a Binning Column When Preparing Data

Binning a measure creates a new column based on the value of the measure. You can assign a value to the bin dynamically by creating the number of equal size bins (such as the same number of values in each bin), or by explicitly specifying the range of values for each bin.

You can create a bin column based on a data element.

Create or open the Data Visualization project in which you want to create a binning column.
Open the data elements, and confirm that you’re working in the Prepare canvas.
Click Options for the selected column, and select Bin.

In the Bin dialog, specify the options for the bin column, as described in the following table:

Field	Description
New element name	Change the name of the bin column.
Number of Bins	Click to select a different number from the list.
Range Histogram	Based on your selection in the Method field, the range (width) and count (height) of the bins are updated. In the Manual method you can move the slider to select the boundary (that is, range and count) of each bin. The number of sliders change based on the Count of Bins. In the Equal Width method, the boundary of each bin is the same but the height (that is, count) differs. Based on your selection in the Bin Labels field, the bin column labels are updated. In the Equal Height method, the height of each bin is the same or very slightly different but the width (that is, range) is equal.
Range List	If you select the Manual method, you can change the name of each bin.
Bin By	If you select the Equal Width method, click to select a dimension (that is, a data element) on which to apply the bin.

Click OK to add the new column, and close the Bin dialog.
Click Save.