BW DSO in Native HANA DataWarehouse with CDS and FlowGraph
As you already know, SAP Native HANA DataWarehousing Foundation tools are introduced With HANA SP 10, then recently with HANA 2.0 SP 2, Native DSO object and DW monitor tools are available in Web IDE to model the data effectively. With this feature, customers can leverage the most popular object in SAP BW in HANA DataWarehouse which is Advanced DSO.
The loading and activation process of NDSO is very similar to BW Advanced DSO, however there are several differences in terms of underlying CDS artifacts created, also the request and log tables creation process. Actually, How to Steps on NDSO creation, loading and activation is nicely explained by Sefan in his blog series and also HANA academy you tube channel 6 videos. I suggest you to go through them first, https://blogs.saphana.com/2017/12/08/web-ide-sap-hana-sql-data-warehouse/
The objective of this document is to share my experience on using this object, different customer use cases, Features comparison with BW DSO and current difficulties in using object.
Why NDSO in HANA DataWarehouse:
Since the inception of HANA platform, there was no native tool provided by SAP for DataWarehouse, it has always been BODS, SLT to perform the ETL and Power Designer to do the modeling. For customers who are not using external ETL tools, SAP suggested Calculation views + SQL Stored procedures for data transformations.
With the introduction of HANA SDI FlowGraph, SAP came up with ETL replacement object for BODS data flows, However, source and target objects for SDI FlowGraphs limited objects, mostly DB tables and Views.
then NDSO came in which can be used as Source or target object in the FlowGraph, with this following major benefits customers can gain.
1. Request/Batch based data loading and maintenance is now possible in native HANA.
2. Change Capture mechanism can be easily handled with help of record mode in Change log
3. Delta loading is handling effectively.
4. Aggregations for measures can be handled using a tool.
5. Task Chains are provided to help in monitoring the Data loading process.
6. FlowGraphs bring the the full power of BODS transformations into Native HANA.
7. NDSO with Dynamic tiering feature, data volume size can be handled effectively.
In Summary we can say, NDSO + FlowGraph in HANA is bringing the capabilities of BW DSO + BODS Transformations to make it more stronger product. Of course, you got CDS as the underlying language used for these objects definitions.
In order to use NDSO, database should be on HANA 2.0 SP 01 or 02, runs on XS Advanced server, configuration and modeling in Web IDE for HANA.
DWF template: SAP provided a template based on DWF to create the project as shown in the below picture, with that some of the sample data files, tables, FlowGraphs and NDSO can be referenced to work on your custom project model.
Core Data Services: With release of HANA 2.0, the Graphical interface for CDS artifact creation has been improved a lot using Web IDE for HANA.
You can create and customize NDSO with Graphical or code editor,
FlowGraphs: Several new features enhanced in the data transformations of FlowGraph, complete details can be found the HANA academy you tube channel.
Task Chain: Task chains are created from the DWF folder created from the template project chosen in above step, monitoring of task chain can be done in DW monitor.
Use Cases for Customers:
1. Planning to build a green field Native HANA DataWarehouse, NDSO + FlowGraph is the best fit use case, with that customer can leverage complete Code push down to HANA data base.
Also, no need to purchase the External ETL tools like BODS. Also getting most popular feature of SAP BW in HANA native.
2. Customers who currently using SQL code, Calculation views to do the ETL+ modeling can implement the new objects for improving the data modeling, maintenance and improved performance.
3. Customer who currently using BODS as external ETL tool can benefit in terms of performance with code push down to HANA and also DSO object as source and target.
4. Customer who wanted to get the close integration to SAP BW and SAP Datahub.
1. Currently when an NDSO is created, there are bunch of Log tables, request tables created they will be automatically added to FlowGraph when NDSO used as target. It makes little difficulty in understanding the relation of these tables, maintenance also will be a challenge in case of issue. Below is the screen shot.
2. Currently in HANA 2.0 SP 02, handling record mode field mapping is not handled by system automatically, some manually adjustments are required. I think, it will get fixed in the next SP releases.
3. I think, Controlling Dynamic Tiering feature for NDSO is not completely available, may be scheduled in the future releases.
SAP DataWarehousing Foundation is improving rapidly in HANA 2.0 releases, every new SP level is enhancing several new features. DWF is going to be very attractive tools for Customers to build Native HANA DataWarehouse, also integrate that with Data hub.
Thank you for reading.
Nice article. Can you throw some light on all the licences to have this flowgraph + NDSO setup ?
Adding to it what would be the need for 'native' data warehouse ? I was with opinion that data warehouse is for consolidated/relational reporting across various fragmented SAP and non-SAP systems.
Hi Mihir, you got to be on HANA 2.0 SP 01 or SP 02 and Running on XS advanced server. Then, you need an add on for DataWarehousing foundation to be installed on this. The detailed documentation of installation guide is available in this link, Installation and SAP note 2435452 explain the tools installation.
Secondly, your understanding is correct, Enterprise Data Warehouse means, extraction of data from SAP and Non SAP sources and model it for Strategic and Operational reporting needs. SAP was offering BW solutions as EDW for many years, now SAP offering another EDW directly in HANA with out having BW application, so that is what it is termed as Native Warehouse or SQL Warehouse.
In Native or SQL warehouse, you are still able to extract data from SAP and non SAP systems. It is just another Data warehousing solution offered by SAP using HANA platform capabilities. It does not mean that, it is only for Non SAP data.
Based on several factors like, Cost, feasibility, requirement, performance etc., customer can decide to choose either to go with BW based EDW or SQL/Native EDW. hope this helps.
can we archive tables from BW DSO´s, aDSO´s... into DFW ?
Thanks for your detailed reply. So you mean to say that by using SDI tool driven flowgraph we can bring in non-sap data into NDSO in SQL/Native EDW. Subsequently we can we also create custom graphical calculation views via join/union for relational/consolidated reporting between SAP and non-SAP data as well ? We can also create BEx queries on these custom created graphical calculation views ?
Is DataWarehousing foundation a separate license ? Also SDI license too might not be part of HANA 2.0 SP 01 or SP 02 and Running on XS advanced server ? Isn't it ? Are SDI flowgraphs just like BW transformations like start routine/Field routine/End routine ?
Hi Mihir, sorry for the delayed response, you would have got the answer to your answer by now. Just writing my comments for others who has this question,
You can use SDI mechanism to get both SAP and Non SAP data into HANA Database native., you can also term it as SQL Enterprise Datawarehouse. hence, you can also build calculation views on these Datawarehouse objects for analytical model preparation purpose.
Creating BEx query(BW Query) on these Calculation views is not something you wanted to do, unless you have BW application running on this HANA database wanted to integrate native HANA data with BW models data for reporting.
SDI will be part of HANA platform edition license, the flowgraphs are very similar to BODS Transformations in the design and deployment.
Can you please check this link?
hello Madhavan, I have provided a reply to your question..