SAP Data Warehouse Cloud Community

This is your platform to ask questions, post answers and share your experience with other customers and experts from SAP.

Graphical or SQL view - what's your preference?

In the Data Builder, you can choose to model data by creating a graphical or a SQL view. Both have different ways of doing the same tasks: creating a data output. Have you tried both ways of data modeling? What is your preferred way to model your data in SAP Data Warehouse Cloud? Do you find one better than the other? Why?

Comments

  • I prefer the graphical approach. Not only because it is easier for me to create a data model, but also for everybody following me to operate/extend/bugfix it.

    SQL Script only if the necessary functionality is not supported in the graphical modeling.

    December 2, 2019
  • Hi @Maddie Martin, I have tried both and fully agree with @Sven Knoepfler. I am not an SQL expert and for me it is easier and quicker the graphical drag-and-drop approach. As Sven says, I would only use the SQL approach for specific cases where the functionality is not supported in the graphical approach. In any case, I think it has been a great decision to include both approaches in this tool.

    December 4, 2019
  • SQL Views: it would be great, if we could use HANA SQL script functionalities to add some logic to the views

    SQLScript addresses the following problems:

    • Decomposing an SQL query can only be performed by using views. However, when decomposing complex queries by using views, all intermediate results are visible and must be explicitly typed. Moreover, SQL views cannot be parameterized, which limits their reuse. In particular they can only be used like tables and embedded into other SQL statements.
    • SQL queries do not have features to express business logic (for example a complex currency conversion). As a consequence, such business logic cannot be pushed down into the database (even if it is mainly based on standard aggregations like SUM(Sales), and so on).
    • An SQL query can only return one result at a time. As a consequence, the computation of related result sets must be split into separate, usually unrelated, queries.
    • As SQLScript encourages developers to implement algorithms using a set-oriented paradigm and not using a one-tuple-at-a-time paradigm, imperative logic is required, for example by iterative approximation algorithms. Thus, it is possible to mix imperative constructs known from stored procedures with declarative ones.


    December 8, 2019
  • harikrishnansharikrishnans BI Solutions Developer India

    @Maddie Martin, I have tried both, as @Sven Knoepfler and @Carlos Pinto said, choosing SQL or graphical view is completely scenario based. If we couldn't achieve some logic in graphical view (for example intermediate aggregations, nested or sub-query scenarios and self join scenarios) then, we can use SQL views to achieve them.

    However, In SQL views, there isn't any final output variable (like VAR_OUT variable in SQL-based HANA calculation views), this can be an advantage too, the return type of the SQL views in DWC are dynamically getting changed based on the select statement (columns) that we have used in it. Since we can't use the intermediate table variables (which is possible in SQL-based HANA calculation views), we have to write everything in a single SQL statement (nesting is possible).

    December 9, 2019
Sign In or Register to comment.