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

/wp-content/uploads/2016/06/information_sign_clip_art_7730_980839.jpgCurrently, 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/$metadata


Count

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=allpages


OData 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=Category


Features

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:

/wp-content/uploads/2016/06/001_980873.png

CDC will interpret this as 3 tables: Products, Categories and Suppliers. So, on selecting add table to the data model in CDC:


/wp-content/uploads/2016/06/002_980875.png

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.

/wp-content/uploads/2016/06/003_980879.png

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.

/wp-content/uploads/2016/06/004_980880.png


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


To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply