The data consistency team has been working on updating the Cross Database Comparison tool to support new source types. We are pleased to announce that the tool now supports OData V2+ services for the ATOM format. (Available with Solution Manager 7.1 SP14 and above)
Overview
The Cross Database Comparison (CDC) application is used to compare data sources with a complex structure or hierarchy across different systems. By doing so, you check whether the data in the source and target systems is consistent, for example, whether updates in the source system have been correctly replicated to the target system. Examples of complex data sources are sales orders with several items or a master data records distributed across several tables. You can read more about CDC here.
With the advent of the cloud, REST web services are used frequently to access data from the web. Enter OData: The OData (Open Data Protocol) is a standard that defines the best practice for building and consuming RESTful APIs. It is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web. Various kinds of libraries and tools can be used to consume OData services.
Why OData?
OData provides access to just the data you want. It has scalability built right in to the protocol. Using the conventions of OData, you can make highly specific requests to get a single data item or you can use the feature of linked-data to quickly uncover relationships. It works essentially like ODBC for the web
You can read more about OData services here.
OData as a new source type for CDC
Since many organizations are migrating data to the cloud, it becomes essential to check for consistency between the cloud systems and the onPremise systems. For this purpose, we make use of the new OData source type.
| CDC Source Type | Web Service used and flow |
|---|---|
| OData Service | CDC->OData Request->Cloud ->OData Response-> CDC |
Currently, CDC supports only the ATOM format of OData, version 2.0+.
Prerequisites
There are a few prerequisites the OData service must have in order to be compatible with CDC. It implies that few features of the OData service must be accessible. The features are:
- Metadata
- Count
- OData querying, filtering and expand operations
Metadata
A Service Metadata Document describes the data model (i.e. structure and organization of all the resources) exposed as HTTP endpoints by the service.
Example:
http://services.odata.org/V2/OData/OData.svc/$metadataCount
The count as well as 'inlinecount' function is accessible and is for counting the number of records extracted.
Example:
http://services.odata.org/V2/OData/OData.svc/Products/$count
http://services.odata.org/V2/OData/OData.svc/Products?$inlinecount=allpagesOData querying, filtering and expand operations
The ability to query entities, filter and expand associated entities should be working and accessible.
Example:
http://services.odata.org/V2/OData/OData.svc/Products?$select=ID,Name
http://services.odata.org/V2/OData/OData.svc/Products?$filter=ID eq 0
http://services.odata.org/V2/OData/OData.svc/Products?$expand=CategoryFeatures
Since we are comparing with ABAP systems or native databases or CSV or BI systems, the new source type supports multiple features:
Metadata
CDC is able to interpret this metadata and convert it to a format that can be understood and presented. For this purpose, as the Service metadata document describes entities. Entities are instances of Entity Types (for example, Customer, Employee, and so on) which are structured records consisting of named and typed properties and with a key. CDC interprets each entity as a table.
For example, the entities displayed in the metadata are:
CDC will interpret this as 3 tables: Products, Categories and Suppliers. So, on selecting add table to the data model in CDC:
As of now in CDC , the OData source type OData supports only one primary entity (table) selection at one time. If it has relations with other entities, the fields will be displayed as part of the same entity as explained below.
OData Entities and properties in CDC (Columns)
From the metadata, we can also see the each entity set is linked to an Entity type. An entity type has properties, keys and or navigation properties (Relationships) to other entities.
For example, the metadata for the entity Product is:
The product contains the fields ID, Name, Description, Price, Rating, DiscontinuedDate and ReleaseDate. It has the field ID declared as a key field and it has a relation to Category and Supplier as shown in the metadata above.
The property fields are displayed in CDC as follows:
- For fields of the given entity having a standard EDM type (like Edm.Int32, Edm.String etc.), the field followed by an equivalent ABAP DDIC type is shown.
- If the property has an attribute that has KeepInContent = "false", CDC will search for the Target path and display the column name as: PropertyName_S_TargetName. Example : Name_S_Title
- If the property has a complex type, CDC treats it as a structure and displays it as follows: ComplexTypeName___ComplexTypePropertyName. Example : Address___Street
- If the property is part of an association (relation) then the column name is displayed as follows: AssociatedEntityName_N_AssociatedEntityPropoertyName. Example: Category_N_ID
- A combination of the above types is also possible, for example in Products, the Products Supplier's Address city is displayed as: Supplier_N_Address___City
- Key fields defined in the OData metadata is also displayed in the fields for table.
Supported and equivalent datatypes
All EDM types have been given an DDIC equivalent. The OData response is always treated as strings, but conversions to a specific type for comparison can be specified by the standard CDC conversion routines.
| OData Type | ABAP Equivalent Type | Supported? |
|---|---|---|
Null | Not-Available | Yes, as (' ') |
Edm.Binary | D16R | Yes |
Edm.Boolean | CHAR | No |
Edm.Byte | INT1 | Yes |
Edm.DateTime | DATS | Yes |
Edm.DateTimeOffset | DATS | Yes |
Edm.Decimal | FLTP | Yes |
Edm.Double | FLTP | Yes |
Edm.Single | FLTP | Yes |
Edm.Guid | LCHR | Yes |
Edm.Int16 | INT2 | Yes |
| Edm.Int32 | INT4 | Yes |
Edm.Int64 | INT4 | Yes |
Edm.SByte | LRAW | Yes |
Edm.String | SSTRING | Yes |
Edm.Date | DATS | Yes |
| Edm.Time | TIMS | Yes |
Extractor generation
Depending on the data model defined, CDC will generate an OData query. This query will select the desired fields and order them based on the key fields defined and filter them based on the filters provided in the data model.
Caution:
Please be careful on the number of fields selected and the number of conditions added to the filters. The query has a fixed length of 2048 characters, if it exceeds this length then the query will not be able to execute and the comparison will fail.
Conclusion
So concluding, we hope this will help you in checking consistency between your onPremise and cloud systems and also between two cloud systems. Have fun trying it out and let us know what you think!
Links
Data Consistency Management Blog: http://scn.sap.com/blogs/data_consistency_management
Data Consistency Management Wiki: Data Consistency Management - Solution Manager - SCN Wiki
- SAP Managed Tags:
