My colleague martin.donadio has done a great job creating a nice extension to generate Core Data Services for HANA with Power Designer. His work is very well explained in this post: Generating Core Data Services files with Sybase PowerDesigner
As new features are added for Core Data Services in each HANA SPS, and there are some interesting features not supported by the first version of the extension, I've decided to continue and help to improve it.
First of all, let me explain why I found the extension so useful when I've reached Martin's blog.
PowerDesigner already works with HANA DB. Why should I bother about an extension for CDS?
Yes, it's partially true. But PowerDesigner standard features only create the runtime objects in the HANA catalog. This way, all of your artifacts are not transportable and must be created in each system of your landscape. So, this is not the best option, neither it's recommended by SAP. You should use CDS and only change the catalog for features not supported by CDS, like DB constraints, if you want to use them.
PowerDesigner is a great tool for modelling, including Physical Models, which is the feature used by the extension. But it's not nice to design the model with the tool and then create all the entities manually in HANA. So, this is where the extension is useful.
New features added to the extension: associations
Associations are useful to define relationships in the entity source, facilitating the maintenance and simplifying the syntax. They also allow the automatic creation of joins in CDS views, when referring the association. Finally, if you work with XS Data Services, it uses the associations to establish the relation between the entities, making programming easier.
Associations do not generate constraints on the DB, and according to thomas.jung, it's a decision to enforce constraints at application level. So, if you want to use DB constraints, they still need to be defined at runtime level.
Unmanaged associations - why I choose it
HANA can work with managed and unmanaged associations. I'll quote Thomas Jung here to explain the difference between them:
"Managed associations generate additional columns in the source table for the association. Unmanaged, on the other hand, use existing columns and don't actually generate anything in to the underlying database table. This gives you a bit more flexibility to do by-directional associations (Header <-> Item). Most of the more complex association types (Backling and many-to-many) both utilize unmanaged associations for exactly this reason."
So, I've decided to use unmanaged associations, for the following reasons:
- Flexibility
- PowerDesigner automatically adds the foreign key field to the child table when you define a relationship. So, you don't want to have extra fields generated by HANA, as those are being defined in the physical model.
- Style. I rather have just a single field called HeaderId in an Item table, rather than a field called Header.HeaderId, with the parent table as a prefix.
So, let's see what changed...
First, if you haven't read Martin's blog, I'd recommend you to do it, because they're well explained step-by-step and I won't reproduce it here.
Let's take the following diagram as an example:
It will generate the following CDS file, with all association and backlinks:
Not all associations are still supported (many-to-many, for example), but I've tested it for the most used types of relationships and it worked.
After importing it into your HANA project, you can improve it defining a view in the file with this simple syntax:
Define view OrderComplete as select from OrderItem
{
Id,
ItemNr,
OrderHeaderFK.SalesOrg,
OrderHeaderFK.DocType,
OrderHeaderFK.SoldTo,
Material,
ItemValue,
OrderScheduleFK.DelivDate
};
It will generate a view with all the joins based on the associations among the three tables.
I want to add new features. How can I work with PowerDesigner Extensions?
There's a guide called "Customizing and Extending PowerDesigner" (http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38628.1650/doc/pdf/customizing_power...)
This guide explain all possibility of extensions for PowerDesigner. But it has not the objects Metamodel reference. So, after a lot of search, I found out that I could get just accesing the Help menu in PowerDesigner...
With this, and a little knowledge of VBScript, you can do awesome things. Some suggestions:
- Create Series Data (mostly used for IoT)
- Generating sequence files (.hdbsequence)
- Generating XSOData files based on the model
The extension file is attached (just remove the .txt extension after downloading ans unzipping it). You can use it for generate as it is or improve it according to your needs.
I'll also update this blog if I'm able to add new features.
- SAP Managed Tags:
