You don’t create a Data Warehouse (DW) through a wizard. And as you have so much freedom to create your DW in one way or another, you need a design. The question is, how and where do you make it? In this blog post I make the argument for Enterprise Architect Designer, the tool that fits agile DW designs.
Let’s take a step back though: in the first blog I outlined the large changes SAP went through to provide the SAP HANA SQL Data Warehouse. Now it’s time to introduce the tools to design and build the SAP HANA SQL DW. To guide you through it, the first posts of this blog series represent the “key steps” a data warehouse practitioner takes to build a DW: Design, Develop, Deploy, and Run.
The new kid on the block
So, design. Where do you do that? Well, you will find most DW designs in spreadsheets, text editors, PowerPoint…but you might be better off with a tool that’s meant to design data.
At the end of 2016, SAP Enterprise Architect Designer (EA Designer) was introduced. It’s a native SAP HANA 2.0 application, which runs on XS Advanced, is cloud-enabled and completely web-based. Oh, and you can use it to design your data warehouse. But not just that – EA Designer is an application for enterprise wide modeling. It goes beyond the DW scope, with features like business process modeling and landscape and application architecture. For designing your DW, the following tasks are supported:
- Design your conceptual data model;
- Design your physical data model in detail, convert and publish to any DB schema, manage schema changes, and link your data objects to other designs like business processes;
- Reverse engineer existing data definitions, to study your source system model, or copy definitions. (This also supports you in migrations);
- Design data transformations;
- Make an impact analysis of design changes, or use data lineage to understand and explain data flows.
Figure 1: Reverse engineering an existing schema in EA Designer
But why is this the tool for my agile DW?
Working in “agile mode” has the result, or requirement, that you cycle faster and more often through your design, develop and deploy phase. You want to minimize errors in transferring design to development, and you want to make it fast. You also want to provide a fluent user experience from one phase to the other. After all, the architect might also be the developer. Or the data definition designer the ETL designer. Not least important, with the business as product owner, you need regular interaction between business and IT.
To make the DW process effective and efficient, you need tools that allow an easy hop from one phase to another, and which foster communication between IT and the business audience. You can see EA Designer as the tool enabling this agile mode of working as follows:
- There are conceptual models, which allow a design that is easily understood by the business, and which links to the more detailed models.
- Easy sharing: models can be easily shared across the enterprise, as you only need a web browser to access it. That’s how data stewards, product owners, or other business persons can see what’s designed. Besides that, EA Designer supports commenting and reviewing features to support your design process.
- There is integration with GIT, which means, there is repository integration with any other tool that supports this open source repository. Most notably that is the HANA2 WebIDE where you do most of your DW development.
- The HANA SQL DW tooling is supported: you can design and export your data models as Core Data Services (CDS), a great language to do define your data in HANA. The same goes for Calculation Views, Flowgraphs, virtual tables, and Native DataStoreObjects. You wouldn’t develop all the nitty witty details here, but you would design them, and push the framework to the WebIDE to develop them further.
Figure 2: Design a Calculation View in EA Designer
What about SAP PowerDesigner?
If you are not familiar with SAP PowerDesigner: it has a proven DW track record as DW design tool. It is used by countless DW projects, was born long before HANA, and works with many databases. EA Designer is the new kid on the block that addresses the same personas, but you will recognize several features reborn in EA Designer. If you are running PowerDesigner already, you might like to know that you can even connect it to the repository of EA Designer, even though not fully compatible.
Figure 3: Design transformations in EA Designer
Not just for SAP HANA
EA Designer supports a variety of databases, not just HANA, not just SAP, and not just SQL. It can be the design tool in a heterogeneous data landscape, which is where DWs are usually sitting. Even if your DW does not run on HANA, EA Designer can be your tool.
And this is how it looks like…
This short video, created with the HANA Academy, provides a short introduction on EA Designer, for the DW features that are described in this blog.
If you’d like more info, the SAP HANA Help pages provide a pretty thorough overview of EA Designer. Also, check out this recent blog on the SAP HANA Data Warehouse foundation with plenty of practical information.
Explore SAP’s next generation data warehousing solutions.