SAP Analytics Cloud: Scheduling CSV/XLSX data and importing new master data
This blog addresses two common questions with regards to modelling data in SAP Analytics Cloud based off csv/xlsx file data.
- How do you schedule file uploads into the SAP Analytics Cloud?
- How you do upload new master data into your model, once you’ve already created your model and already added master data into it?
Put simply you need to use the SAP Analytics Cloud Agent to upload the data in a ‘scheduled’ way, though you can also use the ‘run once’ option.
This blog provides a step by step guide for how I achieved this.
[UPDATE March 2017: We plan to introduce a feature in wave 2017.03 that means new master data can also be updated for all workflows. Today, new master data requires the use of the SAC Agent, however we plan from wave 2017.03 that this is no longer necessary and that you’ll be able to update master data when you directly upload an updated XLS/CSV file from your computer]
Step 1: SAP Analytics Cloud Agent
Assuming the HANA Cloud Connector has already been installed and configure you need to configure the SAP Analytics Cloud Agent as per the SAP Analytics Cloud instructions Help> Connections > Import Data Connection > Allowing Data Import from File Server
I set the Java Options environment variable
to have the value:
I then set the
variable to have the same value
As I’m using Windows I did this in the System Properties>Environment Variables:
I created a 2 folders:
I placed into the E:\csv folder a text file (of UTF-8 format) called ‘Whitelist.txt’ which contained:
This means the E:\csv\data folder is ‘white listed’. Any files in this folder (and sub folders) can be read by the SAP BusinessObjects Cloud.
I then restarted the SAP BusinessObjects Cloud Agent.
Step 2: SAP Analytics Cloud Connection
Before we can create a model off a CSV/XLSX file held on E:\csv\data we need to setup a connection.
So I logged into the SAP Analytics Cloud and selected ‘Connection’ from the menu option:
When I then clicked on the ‘+’ and selected ‘Import Data Connection’ I was then able to select the option ‘File Server’. This option was only shown because I had completed step 1 above.
Having selected ‘File Server’ I was then able to complete the following dialogue:
Step 3: SAP Analytics Cloud Model
Having created the connection to the File Server, I can now create a model. However, I first placed a xlsx file into E:\csv\data called Matthew.xlsx to based the model off. The file contained the following data:
I selected ‘Create > Model’
And I then selected the option ‘Import a file from your computer’
Because I had completed step 1 above I was able to select the option ‘File Server’.
Because I had completed step 2 above I was able to select the ‘File Server on BOC’ connection:
I could then pick my Matthew.xlsx file and click Import:
The model was presented to me and I then clicked ‘Create Model’:
Step 4: SAP Analytics Cloud Story
I then created a very simple story off this model by selecting Create > Story:
There are lots of ways to do this, but I selected ‘Add a Canvas Page’:
Then I added a chart:
And I selected the model ‘Matthew’ and pressed ok.
I added the measure and dimension to form my bar chart:
Notice that the master data for the dimension has values A, B, C and D.
I then saved the story. Later we shall re-open this story to see new values added to it.
Step 5: Reload SAP Analytics Cloud Model with new data
Now I want to upload new data into my model. So I updated the E:\csv\data\Matthew.xlsx file with the following values:
I clicked on ‘Browse > Models’:
The master data for my model currently contains the dimension values A, B, C and D.
If the master data in my updated file is the same as the master data in my model, then I could import new records, including new values, by clicking on ‘Import Data’ – ‘Import Data From File’
And again I can use my Connection to the file share to import the file again:
However, the master data has changed. There are new values: E, F, G and H in my file that I want to upload into my model. So I must upload this new master data via a different means.
On the model list, I clicked on ‘View More’:
I could then click on ‘Run’:
After a few moments the data loaded successfully and the display changed to:
I could optionally double-click on the Import which takes me to this screen:
From here I can setup a schedule and chose different import settings.
[Update November 2017: Hourly schedules can now be selected]
Step 6: Open existing Story
I then opened my story I created in step 4 and was able to see the model was updated with new master data. The dimension has additional values E, F, G and H:
- To upload new master data (i.e. new dimension values) then you must do so via the SAP Analytics Agent in a ‘schedule’ means (either ‘Run’ [once] or on a scheduled basis)[UPDATE: March 2017. We plan from wave 2017.03 that this is no longer necessary. We plan, that you’ll be able to update master data even when you upload a file directly from your computer. You’ll need to use the ‘model options’]
- The filename needs to remain the same for all subsequent uploads.