The challenge
If you worked a bit with SAP Datasphere - you have already used or noticed SAP Datasphere provides Entity -Relationship (E-R/3NF) modelling capabilities, just recently enhanced by certain capabilities such as modeling of hierarchies.
In a discussion brought up by a partner company there were questions on how to migrate data models and model for SAP Datasphere simple and easy without “reinventing the wheel” repeatedly.
The answer to this question is quite simple. SAP Datasphere provides the capability to import models.
Some months ago, I researched for myself how to migrate data models of any schema (3NF, Star, DataVault, ...) to SAP Datasphere.
Of course, doing everything manually seems boring labor, time consuming, prone to errors and finally an expensive task. Hence if there is a method to reduce manual tasks to a minimum who would not welcome it? And indeed, there is a solution to migrate legacy models into SAP Datasphere and to my experience in a few discussions, this neat capability of SAP Datasphere is really overlooked by many. Now, it’s high time to present this feature to the community:
Capability to import and deploy Metadata in SAP Datasphere
In SAP Datasphere Data Builder pane there is a nice little icon with a curved arrow. The “mouse over” will show a tooltip “import”.
There are possibilities to import:
- CSV Files
- CSN / JSON Format
- Entities
- Remote Tables
- Connections
However not all capabilities are available from all services. In this blogpost I will bring a bit of light in one of the most important import functions: "Import Objects from CSN/JSON File"
That is CSN / JSON:
So, what is CSN standing for? CSN (Pronounced “Season”) means CDS (Core Data Services) Schema Notation. A modeling format based on JSON (JavaScript Object Notation. Basically, this is a structure describing objects in a hierarchy of pairs of name and value. But more important, it means we can import data models consisting of tables, relations, entities etc. and of course HANA CDS / HDI (HANA Deployment instance) models in SAP Datasphere.
Next, I will show a simple process to migrate a legacy data model to SAP Datasphere by exporting it in a CDS / JSON format and import and deploy it to SAP Datasphere.
Preparation
SAP provides the modeling tool SAP PowerDesigner featuring the capability of creating and exporting supported data models as CSN file. The capability to create CSN files is available in SAP PowerDesigner Portal. The portal is available both as SAP PowerDesigner PCE service and shipped with the on-premises installation as a component.
Note: The version of SAP PowerDesigner used in this blogpost is 16.7.4 If the version of SAP PowerDesigner Portal is older than this – it might be required to convert to an intermediate model such as SAP HANA HDI data model first to permit export a CSN file.
The process is simple:
- Reverse engineer or take an existing data model.
- If the data model is one of the ones supported by SAP PowerDesigner Portal – export the CSN file.
- If the Model is not supported as CSN directly convert this using e.g. SAP PowerDesigner Windows Application to a supported Model type e.g. SAP HANA 2 or SAP HANA HDI.
- Publish / Check in the model to SAP PowerDesigner Repository and open the model with SAP PowerDesigner Portal
- Export CSN File
- Import CSN File to SAP Datasphere Data Builder E-R Modeler
- Do adjustments to metadata if needed.
- Deploy and populate with data.
Seems simple and straight forward and if you follow the steps as above it should work for you.
Note: SAP Datasphere has the option to import CSN direct in Data Builder but also in the service for:” New Entity Relationship Model”. To import a data model in CSN/JSON notation use the import facility in Entity Relationship modeler.
If you just import single entities without “relations” between tables – then import directly in SAP Datasphere Data Builder like in Pic 1. The existence of relations between table distinguishes between models and “just a bunch” of tables or entities. Importing it in the data modeler allows you to see the graphical representation of the model immediately as shown in Pic 2.
Quite often relations are not defined in a database. Too much the hassle on constraints, loading issues and performance issues on the database. So, reflect the referential integrity on application level instead. I won’t discuss the effects of relations or associations in this
However, in SAP PowerDesigner unless in very rare occasions data models are generated. This means a model consisting of tables / entities and relations or associations, keys, indexes, and other metadata. So, to provide a model – import the model generated from SAP PowerDesigner into SAP Datasphere Data Builder Entity Relationship modeling service.
1. Reverse Engineer from database or use existing data model in a repository
To begin, I will show in a simple example as in Pic 4 based on an existing small Data Vault style model created for an SAP SQL Anywhere 17 database. So reverse engineer the model from database or perhaps it exists already in a model repository. Bear in mind, quite often the referential integrity exists only in a model and is not always deployed or enforced on database level to avoid loading issues or process performance restrictions in data manipulation.
The model is aligned to conventions of Daniel Linstedt to Data Vault methodology. ![Pic 4 model derived from the examples in the book[1] .](/t5/image/serverpage/image-id/80722i7F553DB4EC232FF4/image-size/large?v=v2&px=999 "Picture4.jpg")
SAP Datasphere can cope with both associations like HANA HDI between entities and also tables and references like classic Entity Relationship models. Shown in the screenshot Pic 5, the option “Generate CSN File” is offered.
2. Export CSN File
What to look for in the drop-down menu on the left. If this does not appear you need to upgrade your SAP PowerDesigner to a more recent version or generate to a supported model type e.g. SAP HANA HDI. The generated CSN file will be exported to local file in the download folder and named as the model and suffix json: <modelname>.json
The generated CSN file looks like so (Pic 7) in JSON notation:
Now we have successfully exported a data model. Next step is to import the model in SAP Datasphere.
3. Import JSON File to SAP Datasphere Data Builder
In a first step I created a new folder – DataVault on Datasphere in Data Builder to keep organized.
Next, from the several services offered by Data Builder navigate to New Entity Relationship Model. In this screen on Pic 8 fourth from left and open this service.
The folder just created appears on the left and click on the folders name to make this the one gets the model imported to.
Now import to SAP Datasphere as illustrated in Pics 9 & 10.
Clicking on the Import from CSN/JSON will bring up the import process like in Pic 11. Here the path to file is omitted – either browse / navigate to your local directory or type it into the text box and click next.
Once file parsing is completed assuming no errors in the model, a list of identified objects to import will appear.
It is possible to just import selective by marking the checkboxes of each entity to be imported or all at once by putting a checkmark in the top row. That is what is shown here in the next Pic 12 and Pic 13:
Pic 12 List of objects in CSN file that can be imported.
The objects may be selected selectively or all at once by clicking the top row check box as I did for this blog post:
The list of entities are compared to entities are already in SAP Datasphere Repository. If entities are new then there is a message displayed “Ready to Import” or “already in repository” you may decide to replace existing entities.
So finally clicking on Import CSN File will import the entities into the repository and the model will show up as a diagram like in pic 14. The layout however may be different compared to SAP PowerDesigner. The layout positions are not migrated.
The model is now imported successfully imported. This means you can add now additional business context and adjust attributes or complement metadata. To use the model and load data into the entities you need to deploy it.
4. Deploy model on SAP Datasphere
This is the final step. Just click on the cloud icon like in Pic 15 and deployment of model will be initiated in the background.
At the end of this uncomplicated process, you can now populate your entities with data and start writing queries and perform calculations.
Is there anything that needs to be obeyed or could go wrong? Verify relations and entities before deploying. If any of the entities contain references or associations and try to import it to the Data Builder this may result in parsing errors. Always check your model in PowerDesigner first to verify the model has no errors or warnings and also verify the type of reference cardinality.
Summary
To sum it up, I tried to illustrate the migration of model process as detailed as possible and to save unnecessary work in defining entities. However, when moving to a new platform it is still time to review the model and optimize data types and content. Quite often models were optimized to cope with database performance constraints. Take the time to investigate, adopt and simplify the model to enjoy the power of SAP Datasphere.
References:
Building A Scalable Data Warehouse with DataVault 2.0 (Daniel Linstedt, Michael Olschimke) Morgan Kaufmann Publishers ISBN 978-0-12-802510-9
- SAP Managed Tags:
3 Comments
