Getting Started – Using Google Sheets with Cloud for Analytics
In the first blog we looked at the different connectivity options that so far exist for Cloud for Analytics and this time we will put this to work by using a Google Sheet and creating a model based on the Google Sheet.
For my example I am going to use a very simple Google Sheet that outlines the Revenue, Cost, and Profit for a set of products broken down by year and month.
In this example we will create a model and load the data into the model. Please note that you could also just connect to the Google Sheet and explore the data.
So lets create the model, upload the data, and then create a visualization.
- Logon to SAP Cloud for Analytics.
- In the menu (top left corner) select the menu item Modeler.
- Now in the top right corner click on the icon “Import Data”.
- Select the option “Import Model From Google Drive”.
- In the next screen you can enter a name and description for your Target Model.
- After entering those details, click on Select Data. In case you are already logged on with your Google Account in the browser you will see a list of data sources, otherwise you will be asked to logon with the Google Account.
- I will then select the Google Sheet C4A_Sample and click on Import.
After we imported the sheet, we are presented with the Data View and we can now configure the details in our model.
You can click on the column header for dimension Product and you can then see the details on the right hand side.
In my example I selected the option “ID”. The other available options are “Description” in case you have a Key and Text in the source data, “Hierarchy” to configure a hierarchy, “Property” to configure a Attribute in BW terms.
Then I select the column Year and also set it to the type “ID” but this time I set the Dimension Type to “Time” and the Time Format to “YYYY”.
Then I move on to column Month and also set the Dimension Type to Time but set the Time Format to “Unique Values.
Here I was hoping for an option to be able to enter a custom format – for example MM or MMM (like in Excel) – but there was only a list of predefined formats available and none of them was only the Month.
For the columns Revenue, Cost, and Profit I will set the Attribute Type to Key Figure.
On the Key Figure part I noticed that there wasn’t an option to configure the number of decimals and a scaling factor – perhaps something in the next release.
After that I click on Create Model and…..
I received the message that I can only have one time dimension per model, so I moved the Month back to a normal dimension.
I guess I will have to solve that issue by setting up a time hierarchy.
The model got created and I was able to go back to the Home page and use the option “Explore Data” and see the data from my model.
In the next step we will setup a proper time hierarchy, fill the descriptions, and setup a product hierarchy.