Load CSV Files to a Workspace with the LDM Modeler
For workspace administrators only
You can upload data to your workspace using the LDM Modeler and start immediately analyzing it.
To do so, upload a CSV file with data in the LDM Modeler. You may or may not have a logical data model (LDM) in your workspace already.
When uploading a CSV file, you can either add it as a new dataset to your LDM or update an existing dataset with the data from this file. The data is immediately available in your workspace, and you can start analyzing it in Analytical Designer.
If you do not have an LDM yet, you can create it:
- Manually (see Create a Logical Data Model Manually)
- From CSV files with data (see Create a Logical Data Model from CSV Files)
- From your cloud data warehouse (see Create a Logical Data Model from Your Cloud Data Warehouse)
- From the Output Stage if you use it (see Create a Logical Data Model from the Output Stage)
Make sure that your CSV files meet the requirements described in CSV File Requirements. When mapping your LDM objects, you cannot map more than one field to the same source column.
Here is an example of what your CSV file may look like:
order_line_id,order_id,order_status,date,product_id,price,quantity 10668-9VYN74-2,10668-9VYN74,Cancelled,2016-07-12,210,100.33,1.00 10697-7GBN87-1,10697-7GBN87,Delivered,2017-12-05,310,39.35,1.00 10907-0TPH53-3,10907-0TPH53,Delivered,2015-03-28,220,50.40,1.00 10778-7INQ32-1,10778-7INQ32,Delivered,2018-02-08,230,78.40,1.00 10240-3SBQ40-3,10240-3SBQ40,Delivered,2017-05-07,160,21.49,1.00 10356-7ZBU60-1,10356-7ZBU60,Cancelled,2015-03-01,140,21.49,1.00 10700-0ACT49-1,10700-0ACT49,Returned,2019-02-20,150,21.82,1.00 10175-0YRN35-3,10175-0YRN35,Delivered,2015-04-11,150,19.15,3.00 10152-6HOB25-1,10152-6HOB25,Cancelled,2016-03-04,140,29.67,1.00
On the top navigation bar, select Data. The LDM Modeler opens in view mode.
Click Edit. The LDM Modeler is switched to edit mode.
Drag your CSV file and drop it in the canvas area. The file preview opens. For example:This preview shows the data from your file. Based on the column names and the contents of those columns in the CSV file, the column names and the types of the data are suggested for the dataset.
Non-numerical data is auto-detected as attributes (see Attributes in Logical Data Models).
Numbers are auto-detected as facts (see Facts in Logical Data Models).
Dates are detected as dates (see Dates in Logical Data Models).In an LDM, dates are managed through a separate object, the Date dataset (see Dates in Logical Data Models). If your CSV file contains dates, not one but two datasets will be added to your LDM: one Date dataset for the dates, and the other one with the rest of information. These two datasets will be automatically connected with a relationship (see Connection Points in Logical Data Models).
Decide how you want to import the CSV file. The option of creating a new dataset is selected by default.
- If you want to create a new dataset from the CSV file, keep the Create a new dataset option selected. The name for the new dataset is predefined based on the name of the CSV file. If needed, edit the name.
- If you have a dataset in your LDM and you want to update it with the data from the CSV file, select Modify the existing dataset structure, and select the dataset that you want to update. The structure of the dataset will be replaced with the structure of the CSV file, and the actual data from the CSV file will be uploaded to the dataset at publishing.
Review the suggested column names and the data types for the columns. If needed, change the names and/or the types of data. For example, a column containing numeric identifiers can be detected as a fact. However, the identifiers are usually non-measurable descriptive data, and therefore must be an attribute. In this case, you need to change the type of the data for this column.You can also do the following:
Set some attribute as a primary key. The primary key defines the unique identifier for a row of data in a file and serves as a connection point that allows you to connect this dataset to another dataset. In other words, you will be able to create a relationship between the dataset that you are going to create and another dataset in the LDM. A relationship between two datasets allows you to use information from one dataset to slice the data in the other dataset. For more information about primary keys, see Primary and Foreign Keys and Connection Points in Logical Data Models.If you do not set the primary key now, you will be able to do it after the dataset has been created (see Update a Logical Data Model).
Set some attribute as a reference to another dataset (this dataset must already exist in the LDM). The reference serves as a foreign key, and a relationship between these two datasets will be created after you import the CSV file. For more information about references, see Primary and Foreign Keys and Connection Points in Logical Data Models.
When you are done reviewing, click Import. The CSV file is imported.
- If you chose to import the CSV file as a new dataset, the new dataset is added.
- If you chose to use the CSV file to update the structure of an existing dataset, this dataset is updated.
Notice the following in this dataset:
- Depending on whether the CSV file contained facts, the dataset is marked with either orange (the dataset contains only attributes and no facts) or green (the dataset contains at least one fact). For more details, see Datasets in Logical Data Models.
- If the CSV file contained dates, the second dataset - the Date dataset - is also added and is connected to your dataset.
- If you set some attribute to be a primary key, this attribute is noted with an orange key icon ().
- If you set some attribute to be a reference to another dataset, this attribute is noted with a gray key icon (), and a relationship is created between the newly created dataset and the referenced dataset.
If you have other datasets in your LDM, decide whether you want to create a relationship between the newly added dataset and any other dataset in your LDM. If yes, do so (see Update a Logical Data Model).
When you are done with updating the LDM, publish it to the workspace. To publish the LDM, follow the instructions from Publish a Logical Data Model. Keep the Upload data from imported CSV files checkbox selected.
The data has been uploaded to your workspace, and you can immediately start analyzing it. To do so, click Analyze on the top navigation bar. You are redirected to Analytical Designer, where you can create insights from your data. For more information, see Create Insights.
When you get an update for the data that you have uploaded to the dataset, you can upload the new data to the dataset (see Update Data in a Dataset from a CSV File).