Data Asset node
You can use the Data Asset import node to pull in data from remote data sources using connections or from your local computer. First, you must create the connection.
Note for connections to a Planning Analytics database, you must choose a view (not a cube).
You can also pull in data from a local data file (.csv, .txt, .json, .xls, .xlsx, and .sav are supported). Only the first sheet is imported from spreadsheets. In the node's properties, under DATA, select one or more data files to upload. You can also simply drag-and-drop the data file from your local file system onto your canvas.
Setting the field delimiter, quote character, and decimal symbol
Importing data from an SPSS Statistics file
If you import data from an SPSS Statistics file (.sav), the following options are available:
- Read names and labels. Select to read both variable names and labels into SPSS Modeler. This is the default option, and variable names are displayed in the Type node. Labels may be displayed in charts, model browsers, and other types of output. By default, the display of labels in output is disabled.
- Read labels as names. Select to read the descriptive variable labels from the SPSS Statistics .sav file rather than the short field names, and use these labels as variable names in SPSS Modeler.
- Read data and labels. Select to read both actual values and value labels into SPSS Modeler. This is the default option, and values themselves are displayed in the Type node. Value labels may be displayed in the Expression Builder, charts, model browsers, and other types of output.
- Read labels as data. Select if you want to use the value labels from the
.sav file rather than the numerical or symbolic codes used to represent the
values. For example, selecting this option for data with a gender field whose values of
2actually represent male and female, respectively, will convert the field to a string and import
femaleas the actual values.
It's important to consider missing values in your SPSS Statistics data before selecting this option. For example, if a numeric field uses labels only for missing values (
0= No Answer,
–99= Unknown), then selecting the Read labels as data option will import only the value labels No Answer and Unknown and will convert the field to a string. In such cases, you should import the values themselves and set missing values in a Type node.
Use field format information to determine storage. If you deselect this option, field values that are formatted in the .sav file as integers (i.e., fields specified as Fn.0 in the Variable View in IBM SPSS Statistics) are imported using integer storage. All other field values except strings are imported as real numbers.
If you select this option (default), all field values except strings are imported as real numbers, whether formatted in the .sav file as integers or not.
Read timestamp as date. By default, all timestamp values are shown as dates. Deselect this option to override this behavior.
Using SQL to pull in data
SELECTstatement to pull in rows or columns of data from a database. Note that the Source path field doesn't apply if you're using the SQL Query mode.
select * from GOSALES.ORDER_DETAILS where UNIT_COST > 40,000 LIMIT 4
select QUANTITY, UNIT_COST, UNIT_PRICE from GOSALES.ORDER_DETAILS
select "Age", "Sex" from testuser.canvas_drug
This SQL feature should only be used to pull in data. Use caution so as not to manipulate the data in your database.
- Amazon Redshift
- Apache Hive
- Cloudera Impala
- Compose for PostgreSQL
- Db2 on Cloud
- Db2 Warehouse
- Google BigQuery
- Microsoft SQL Server
- Pivotal Greenplum
- Sybase IQ
- Add or modify the following line in sqlnet.ora of your Oracle
- Restart the database.
- Create a new user and set a password for it, or change the password of an existing user.
- Use the following query to verify that the user has
PASSWORD_VERSIONS. If it doesn't, add it.
select USERNAME,ACCOUNT_STATUS,PASSWORD_VERSIONS from dba_users;
- In sqlnet.ora, change the value from
11in the following line.
- Verify that the user can still connect to the database.