Extraction from C4C to BW
I have some questions regarding sending data from C4C to BW.
And some master data.
1. Best practice to extract data from C4C to BW
- How to extract delta data
- How to load full data (40000/day, about 10m/year)
- Can PI be used for sending data from C4C to BW, then how to extract delta, how to load full data
- --> in the interface policy of my customer, they want to use PI as much as possible
2. Enhance the datasource in C4C (add fields in the datasource and include logic to fill the value in those fields).
In my research, the standard way is to extract via ODP ByD datasource in BW and then I think it is necessary to filter the value in Infopackge in BW for "delta" extraction (the field for handling delta is necessary e.g. updated date).
I am not sure if C4C can push data from this datasource, then I think PI can be used to send data in BW. (BW have web service datasource in which FM and web service is created to post the data to this datasource (push)). But if push is not possible in this datasoure, I am not sure PI can be used, or it is not too ineffective to use PI here. (I don't know, but I think too ineffective)
You have to go back to my original statements of what does C4C provide to get data out and into an external data warehouse solution.
You have APIs, BW ODP, and Excel/CSV. Since you want to make it "programatic" then Excel/CSV is not an option.
Thus you are left with APIs and BW ODP.
Since you want to use BW as your data warehouse you should used the BW ODP. The BW ODP is a special high volume SOAP API that can get data out of C4C in a high performance manner.
Here are the steps you need to do
1. The CRMSRQHB is a C4C data source and you can enable it for BW ODP in C4C. You do this by going into C4C as an admin -> Administrator -> Data Sources and search for the data source
If the CRMSRQHB data source does not contain all of the fields you will need to do a few things (I suggest you download the data dictionary for the data source to review what is available)
a. If the fields are extensions, you will need to update the data source using the further usage feature. You do this by going into C4C as an admin -> Adaption -> Open the Ticket UI click on the extension field and open the further usage UI to push it to the data source.
b. If the fields are standard, they may be available in other data sources so you will need to create a custom joined data source that joins one or more standard data sources to create the data set you need
2. Once you do this it will expose an API in C4C which you will need to enable it as a communication arrangement/system in C4C so that BW can consume this API. You do this by going into C4C as an admin -> Administrator -> Communication Systems/Arrangements
3. Once the API is exposed as a communication arrangement/system, then point BW to this end point using BW. BW will automatically create a data source as an exact replica of the C4C data source as a BW data source.
4. You may want to create a batch job in BW to periodically call the BW ODP to get new set of data on a predefined interval which you will need to do delta detection in your ETL process in BW or the initial load of the BW data source is so large, that you may need to "split" up the initial load over multiple runs to get the entire C4C data set you want in BW. This is where in BW you would calculate the last extraction date or whatever the date in BW represents the last date and use this as a selection criteria you call the BW ODP. This would ensure that the data set imported into BW would be a subset (delta) of data
5. Create your info cubes, etc... in BW. If you need a data dictionary of the C4C data source, it is available in step 1 when you open the C4C data source and you can download the data dictionary document available for the C4C data source