Wednesday, June 24, 2015

Building fast and flexible reports using Cloud Data Sources

Because your customers are one of your biggest assets you want know everything there is to know about them. We have learned from experience that not all customer data is always present in one system which makes it challenging to give you a total overview. SAP Cloud for Customer (C4C) gives you easy to use functionality to add this missing data in your SAP C4C reports, giving your business the so much needed 360° overview. It can also be used to enrich other types of reports, you are not limited to your customers.


This feature is called a Cloud Data Source in which you can, without any development work, upload data from other sources into SAP C4C. The upload of data can be done manually or automated which gives you the flexibility to tailor reports to your ever changing needs.

A Cloud Data Source can contain almost any data that you would like to use and it can be changed on the fly. In this blog I want to address some points that can help you speed up the creation of your Cloud Data Sources, giving you more time to focus on creating added value in your reports. This blog will not describe all details of the creation of a Cloud Data Source, the main focus is on:

  • Data Type and Length settings for Key fields
  • Different formats used for the Date Data Types

To setup a new Cloud Data Source you need to follow a few simple steps. Login as an Administrator and from the menu select Business Analytics. Press the New button and select Cloud Data Source. A screen like below opens.


The Data Source ID, Name and Usage are mandatory. Via the Manual Data Upload checkbox you define if the data is uploaded manually (when checked) or via a web service. By pressing the Add Row button you can start adding fields to your Cloud Data Source. Since the adding of fields is described in more detail below let’s switch over to the first point I want to address.

Data Type and Length settings for Key fields

When you want to link the Cloud Data Source to other Data Sources you need to make sure that the Data Type and Length of the Key fields match, otherwise the (Cloud) Data Sources cannot be joined. When the setup is not correct, the system does not give a clear indication of what is wrong. Via below example I want to show you what I mean and what you can do to prevent this.

I will setup an example Cloud Data Source that consists out of 2 Key fields. There is no functional use intended, it is just to get the point across. Both fields will also reference standard delivered Data Sources:
  1. Account ID, (references the “Account Master Data” data source) and
  2. Employee ID (references the “Employee Master Data” data source).
First we are going to check the Data Type and Length. Then we will create the Cloud Data Source. So how can you check what the Data Type and Length of your Key field should be?

Check the Data Type and Length

To check the Data Type and Length a good starting point are the Migration Templates. The fields in the template and corresponding Data Types used are always displayed on the last tab called “Field List”. You download the template as an XML file but you can open it in Excel. 

There are 2 ways to find the Migration Templates. In your Open Project Scope under:
Business Configuration->Implementation Projects->Open Activity List->Prepare for Data Migration->Download migration templates


Or, if you do not have an open project you can use following procedure:

  1. In the Help Center search for “Uploading Data” and select the Uploading Data option.
  2. Follow the procedure mentioned at the end where it states: “If you need to upload additional data in the future….”.
  3. In the Data migration window that opens, on the right hand side select “You can Also” and select “Download Migration Template” as shown in below figure.

Let us check the Account Id. For this we download the “Customers” Migration Template and open it using Excel.

As shown in above figure the Type for Account ID is defined as “Text” with Length 10. Since both the Account Id and Employee Id are roughly the same I am going to assume, for the sake of our example, that the Employee Id is also of the Type “Text” with Length 10.

Creation of the Cloud Data Source

Now that we know the Data Type and Length we can create the new Cloud Data Source. In below figure you can see that I have defined the Account Id and Employee Id fields. Since the Data Type “Text” does not exist we define them as “Char”, with length 10. I have also made the reference to the standard Data Sources (under Referencing Data Source and Referencing Data Source Key Field).

Our Cloud Data Source is ready so we save it. During the save we get following warning message:

"Master data reference of view element has different data type"
As you can see there is not a lot of information on what exactly is wrong. Vital information is missing like:

  • Which field is involved and;
  • is the Data Type and/or;
  • Length wrong.
The warning states that the Data Type is different, but in the Cloud Data Source the combination of Data Type and Length define the data type.
You can however be sure it has something to do with a Key field with a reference to another Data Source because the warning talks about master data reference. Since we already checked the Account Id probably the Employee Id field is incorrect. Let’s check the migration template for the employee data and see if there is a difference.
As you can see the length of the Employee Id field is not the same as the Account Id. Instead of 10 it is 20. So let us change the length of the Employee Id to 20 in our Cloud Data Source. I press the Save button again and the data is saved correctly now.
So the lesson here is that before you create your new Cloud Data Source you need make sure that you check the Data Type and Length for the Key fields you want to use. This saves you a lot of time trying to figure out what could be wrong with your Key field(s).

Formatting for the “Date” Data Types

When you want to upload Dates it is important to get the formatting correct. For most of the Date field options the formatting is as you would expect. However in 3 cases the formatting is a bit different:


Year Quarter

YYYY-Q#

Year Week

YYYY-W##

Quarter

Q#


The Q and W do not represent the numeric positions used for the Quarter and Week. This is represented by the number of # behind the Q or W. The Q and W actually need to be part of the input. There is not a lot of information on this and when you want to upload for instance a “Year Week” value you could get below message which adds to the confusion:

Here the system says that the input for Year Week is YYYY-W<1/2/..53>, but this should be YYYY-W<01/02/…/53>. For your reference please find below the different formats and examples for the Date options:
Data Type
Format
Example data
Week
 WW
01, 02…53
Month
 MM
01, 02…12
Quarter
 Q#
Q1, Q2, Q3, Q4
Year
 YYYY
2014, 2015…
Year Month
 YYYY-MM
2015-01, 2015-02…2015-12
Year Quarter
 YYYY-Q#
2015-Q1, 2015-Q2, 2015-Q3, 2015-Q4
Year Week
 YYYY-W##
2015-W01, 2015-W02…2015-W53

No comments:

Post a Comment