The following procedure explains how to manually import data stored in an Excel spread sheet into Analytics:
...
...
Upload the data into the Data Feed.
...
Flush the data from the Data Feed to the Data Source.
...
...
| Table of Contents | ||||
|---|---|---|---|---|
|
If necessary, you can add fields later in Data Feed or Data Source; calculated fields can be added also in Datamart.
...
The first step is creating a Data Source. The reason is that you have to create a data table with the correct structure to which you will import the data.
Click the
...
Add Source button to create a new Data Source.
Copy the first 10 rows or so from the Excel file, including the header, into clipboard.
Go back to your new Data Source and click
...
Add Fields.
...
Paste the rows from the clipboard into the text area and click
...
Apply
...
.
...
Set each field's Data type if it was not set up correctly from the data sample.
Mark at least one field as 'Key' in the
...
Function
...
column.
Define
...
Measure
...
for Money and Number data types.
...
Click the
...
Deploy
...
button.
When you deploy a Data Source, a Data Feed and a Data Load are generated automatically.
Upload Data into Data Feed
A Data Feed is created for each deployed Data Source, with an auto-generated name, generic field names (attributeX) , and a label, which is the name of the Data Source.
Upload your data into the Data Feed:
Go to
...
Analytics > Data Manager
...
>
...
Data Feeds
...
.
Select the Data Feed generated for your Data Source (it has its name as a label).
...
Make sure the labels are there and exactly match the one used in the Data Source.
Switch to the Data tab and click
...
Upload
...
.
For the file requirements (format, size, delimiters etc.), see Data Feeds.Browse for your Excel file and click
...
OK
...
.
Wait for the upload to complete. This might take a while depending on the size of your data.
Flush Data to Data Source
...
Flush the data from the Data Feed to the Data Source:
Once the data is loaded into the Data Feed, go to
...
Analytics > Data Manager
...
>
...
Data Loads
...
.
...
Find and open the Flush task (the task whose 'Type' has the value 'Flush') with the label of your Data Source.
(Optional) Go to the
...
Calculation
...
step and select a logic to manipulate the data.
...
By clicking the
...
Default Formula
...
button, you will create a new calculation logic containing all fields from the Data Feed as elements. Edit the logic as necessary.
Click the
...
Run Data Load button and wait for the flush to complete. This might take a while depending on the size of your data.
Create a Datamart
Create and deploy a new Datamart:
Go to
...
Analytics > Data Manager
...
>
...
Datamarts
...
.
Click the
...
Add Datamart button.
Specify the name and label and open the newly created Datamart
...
.
Set the
...
Source
...
of the Datamart to be your Data Source.
Select the reporting currencies. All transactions are converted (based on the currency Data Source) to the selected currencies.
For more details on currencies, see /wiki/spaces/KB/pages/22183992.Pull in all the necessary fields from the Field Picker (
...
select and click Add to fields).
If you have Per-Unit fields in the Data Source, make sure you have a Per Unit Basis field function set.
If you are using Date fields, make sure you have a Pricing Date field function set.
If you are using Currency fields, make sure you have a Pricing Date field set and the Currency (ccy) data source populated.
If you are using UoM fields, make sure you have the UoM (uom) data source populated.
...
Click the
...
Deploy
...
button.
...
Load the Data from Data Source to Datamart
...
If you are using Date fields, do the following:
Go to
...
Analytics > Data Manager
...
>
...
Data Loads
...
.
...
Find and select the Calendar task
...
with the
...
Calendar Type.
Click the
...
Run Data Load button at the
...
bottom of the
...
table.
Wait for the Calendar task to finish. This will populate the Calendar data source with Year, Quarter, Month and Week data.
Now you can refresh the Datamart:
Go to
...
Analytics > Data Manager
...
>
...
Data Loads
...
.
Expand the node containing your Datamart.
(Optional) Add a Calculation task and select a calculation logic to manipulate the loaded data.
Create a new Data Load by clicking the
...
Add
...
Data Load button and give it the same name as the target Datamart and set the type to
...
Calculation
...
.
...
In the
...
Target
...
field, select the target Datamart from the drop-down list.
...
In the Target step, you can filter the target's rows so that the logic is only run on certain rows.
In the
...
Source
...
step, you can filter the source's rows so that the logic is only run on certain rows.
Go to the
...
Calculation
...
step and select your logic from the drop-down list.
To test the logic,
...
click the Test Logic
...
button. You will see the results in the
...
Formula Outputs
...
and Results tables.
Select the Refresh task (the task whose 'Type' has the value 'Refresh').
Click
...
the Run Data Load button at the
...
bottom of the
...
table.
Wait for the Refresh task to finish. This might take a while depending on the size of your data and complexity of the setup.