One of the nice new features for accelerating development with the Real-Time Data Platform (RTDP) in SAP Sybase Event Stream Processor (ESP) 5.1 SP02 is support of HANA- and ESP-aware extensions for the PowerDesigner modeling products.
These extensions enable rapid application development by making it easy to work with data models across the enterprise, including creation of new models from existing models, and coordinating model changes to ensure models stay in synch. Specifically, PowerDesigner enables you to:
- Model ESP schema in PowerDesigner as logical data models
- Import and export ESP logical data models to/from ESP CCL files
- Validate an ESP schema with ESP-specific validity checks
- Convert ESP schema models into corresponding physical data models, and vice versa
- Build an integrated model containing ESP schema as well as physical database tables
- Validate and perform impact and lineage analysis of changes throughout the integrated model
These are key features to enable effective, iterative development with real-time data. Let's walk through an example to illustrate a few of the capabilities.
ESP ships with a Learning Perspective that contains a simple Pre-Paid Biller project to generate billing records from account and plan reference data, and call data records (CDRs). It demonstrates joins, aggregations and working with reference data in ESP. For our purposes, we’re going to extend the project, using PowerDesigner and HANA, to persist the call data records into a HANA table.
First, open the Pre-Pay Biller project in your Sybase ESP Studio workspace. We’ll be importing schema from the Pre-Pay Biller Continuous Computation Language (CCL) file, the file that defines the stream processing logic, into a PowerDesigner Logical Data Model.
Next, open PowerDesigner and, if necessary, create a new PowerDesigner workspace for this tutorial. Then let’s create a blank Logical data model.
In the New Model dialog, select Model types and Logical Data Model. Name it RTDP-ESP-PowerDesigner-Blog, then click the Select Extensions button and check the box next to the ESP Schema extensions.
Click OK.
Click OK again.
Next, we’ll import the schemas from the prepay_biller.ccl into the PowerDesigner ESP Logical Data Model. Right-click the model and select Import CCL from the menu:
You will see the schema of the CDRs input stream in the logical data model. Select and delete all the other imported schemas except for CDRs – that is the only schema we will concern ourselves with here:
Double-click the CDRs schema and click on the Attributes tab. There, click the Customize Columns button:
Click to check the ESP Datatype column and click OK.
Next let’s generate a HANA physical data model from the ESP logical data model. Select Tools | Generate Physical Data Model from the menu. Change the Name of the physical model to RTDP-HANA-PowerDesigner-Blog.
Click on the Selection tab and select only the CDRs schema for export to HANA. This is the only table we intend to store in HANA.
Click on the Detail Tab, then click the Enable Transformations button:
Click OK to close the dialog and OK again to accept the changes to the physical data model.
You now have a HANA physical data model with a single table defined for CDRS. At this point this is just a model, and does not correspond to a table in a HANA database yet. Before creating the table, we need to edit the properties of the CDRS table in order to identify the SCHEMA where the table will be stored. Click the Create Schema button to add a new SCHEMA if necessary. For this type of table, click the Column Radio box to indicate you want a Column table in HANA.
Next, right-click the HANA physical data model and select Apply Model Changes to HANA Repository:
Enter login credentials for the HANA instance and Click Next. Click Next through the following pane, then select the CDRs physical data model:
Click Next. Click Finish.
Now the HANA CDRS table exists in the HANA database itself. So let's modify the prepay_biller project to publish call data records to HANA.
Here is the visual editor view of the original prepay_biller project. The call data records come in through Adapter3 into the input stream CDRs.
First thing we will do here is define an output adapter and connect it to the CDRS HANA table. Click on the HANA Output Adapter from the Output Adapter drawer and click again to it to the canvas. You will need to have configured your service.xml file (in %ESP_HOME%\bin\service.xml by default) with a connection to HANA. And of course you will need to have installed the HANA ODBC driver and DSN entry configured for the HANA database. Here we are using a DSN called "rtes". On Windows the service definition might look something like:
<Service Name="HANA-RTES" Type="DB">
<Parameter Name="DriverLibrary">esp_db_odbc_lib</Parameter>
<Parameter Name="DSN">rtes</Parameter>
<Parameter Name="User">RTES</Parameter>
<Parameter Name="Password">********</Parameter>
</Service>
Edit the properties of the adapter. Set the Database Service Name property for the adapter to refer to the Service Name above, and set the Target Database Table Name to CDRS.
That’s all we need for basic connectivity.
Now, depending on how PowerDesigner and HANA are configured, the case of the target table column names may differ from the case of the ESP input stream column names. In such case some sort of mapping is required, either by defining the Field Mapping property in the adapter (sometimes referred to as a permutation), or alternatively by defining the output stream column names to match the column names HANA expects. In this case, the latter is an easier course, so let’s do that.
Since the output adapter is now configured to talk to HANA, we can use schema discovery to derive a new ESP schema that uses the HANA column names. Click the Schema Discovery button on the adapter:
Choose the CDRS table and click Next. Several options are made available for extracting the schema, including options that generate the output stream as well.
Choose to create a new output stream with an attached schema.
This will generate a standalone schema and an output stream that refers to the schema, both of which will be in synch with the destination HANA table. I chose to name the new schema schCDRsToHANA and the new output stream osCDRsToHANA.
Next, we need to get the CDRs arriving on the input stream to the output stream. Click the Connector on the palette, and then connect the CDRs input stream to the osCDRsToHANA output stream. Compile the project, and you’re ready.
Here’s an updated visual view of the project, with the new items highlighted:
When you run the project, all incoming CDRs will be persisted to HANA. A quick check from HANA Studio shows that all demo records arrived:
Having used PowerDesigner to generate HANA tables from event stream schema, PowerDesigner's Impact and Lineage Analysis features let you see what tables and columns relate to each other. Prior to making a change to a HANA model, you could right-click the model, and select Impact and Lineage Analysis to bring up the corresponding wizard.
PowerDesigner will then generate a diagram indicating which models are depenent on which others, e.g.:
If you click the table with the plus sign in the wizard, and click to add all Entities to the diagram, PowerDesigner will list the dependencies down to the column level. This is a great way to explore the possible impact of changes on other systems, at an early modeling stage, so the impacts can be accommodated prior to implementation.
There is much more you can do with PowerDesigner, ESP and HANA, but hopefully this gives you a headstart leveraging PowerDesigner with RTDP.
For detailed documentation about the PowerDesigner features for ESP, please see the online documentation here.
- SAP Managed Tags:
