Skip to content
NextLytics
Megamenü_2023_Über-uns

Shaping Business Intelligence

Whether clever add-on products for SAP BI, development of meaningful dashboards or implementation of AI-based applications - we shape the future of Business Intelligence together with you. 

Megamenü_2023_Über-uns_1

About us

As a partner with deep process know-how, knowledge of the latest SAP technologies as well as high social competence and many years of project experience, we shape the future of Business Intelligence in your company too.

Megamenü_2023_Methodik

Our Methodology

The mixture of classic waterfall model and agile methodology guarantees our projects a high level of efficiency and satisfaction on both sides. Learn more about our project approach.

Products
Megamenü_2023_NextTables

NextTables

Edit data in SAP BW out of the box: NextTables makes editing tables easier, faster and more intuitive, whether you use SAP BW on HANA, SAP S/4HANA or SAP BW 4/HANA.

Megamenü_2023_Connector

NextLytics Connectors

The increasing automation of processes requires the connectivity of IT systems. NextLytics Connectors allow you to connect your SAP ecosystem with various open-source technologies.

IT-Services
Megamenü_2023_Data-Science

Data Science & Engineering

Ready for the future? As a strong partner, we will support you in the design, implementation and optimization of your AI application.

Megamenü_2023_Planning

SAP Planning

We design new planning applications using SAP BPC Embedded, IP or SAC Planning which create added value for your company.

Megamenü_2023_Dashboarding

Dashboarding

We help you with our expertise to create meaningful dashboards based on Tableau, Power BI, SAP Analytics Cloud or SAP Lumira. 

Megamenü_2023_Data-Warehouse-1

SAP Data Warehouse

Are you planning a migration to SAP HANA? We show you the challenges and which advantages a migration provides.

Business Analytics
Megamenü_2023_Procurement

Procurement Analytics

Transparent and valid figures are important, especially in companies with a decentralized structure. SAP Procurement Analytics allows you to evaluate SAP ERP data in SAP BI.

Megamenü_2023_Reporting

SAP HR Reporting & Analytics

With our standard model for reporting from SAP HCM with SAP BW, you accelerate business activities and make data from various systems available centrally and validly.

Megamenü_2023_Dataquality

Data Quality Management

In times of Big Data and IoT, maintaining high data quality is of the utmost importance. With our Data Quality Management (DQM) solution, you always keep the overview.

Career
Megamenü_2023_Karriere-2b

Working at NextLytics

If you would like to work with pleasure and don't want to miss out on your professional and personal development, we are the right choice for you!

Megamenü_2023_Karriere-1

Senior

Time for a change? Take your next professional step and work with us to shape innovation and growth in an exciting business environment!

Megamenü_2023_Karriere-5

Junior

Enough of grey theory - time to get to know the colourful reality! Start your working life with us and enjoy your work with interesting projects.

Megamenü_2023_Karriere-4-1

Students

You don't just want to study theory, but also want to experience it in practice? Check out theory and practice with us and experience where the differences are made.

Megamenü_2023_Karriere-3

Jobs

You can find all open vacancies here. Look around and submit your application - we look forward to it! If there is no matching position, please send us your unsolicited application.

Blog
NextLytics Newsletter Teaser
Sign up now for our monthly newsletter!
Sign up for newsletter
 

SAP Datasphere JSON Hack: Transform Graphical to SQL View

Graphical and SQL views are essential tools for data modelling in SAP Datasphere, each offering distinct strengths and weaknesses. Graphical views provide a visual, user-friendly interface ideal for straightforward data processing tasks for non-technical users, while SQL views support more complex logic and enhanced performance optimization. Both types have their place within a data landscape, but as business needs evolve, it may become necessary to switch from one to the other. A common reason for this shift is the limitations associated with graphical views compared to SQL views. For more background, the "SAP Datasphere: SQL or Graphical Views? Making an educated decision" provides an in-depth comparison of these view types in SAP Datasphere.

Blog Purpose

This blog presents a method to convert graphical views to SQL views without disrupting any dependent objects by having to delete/edit and recreate existing objects. By leveraging a technique involving CSN/JSON file editing, this approach enables a seamless transition without breaking existing connections, a crucial benefit for mature data projects. Almost all objects in Datasphere contain a CSN/JSON file available for extraction with all the necessary information about the object. When editing an object in Datasphere, it essentially modifies the CSN file and then redeploys it. By exporting the CSN file from Datasphere, you can take advantage of this process by directly manipulating the JSON, saving time. One common use case is outlined in this blog. The goal of this blog is to provide a step-by-step approach with the hack of converting graphical views to SQL views in SAP Datasphere while keeping all dependent objects stable and unchanged.

Scenario

In complex data models, views often have numerous dependencies, making it challenging to delete and recreate them without disrupting related objects, in a late-stage development environment, where many objects are connected via associations or joins.

Problem: Imagine a mature Datasphere Space where a graphical view is linked to multiple dependent objects and layers. Directly deleting this view to rebuild it as an SQL view could result in dozens of broken dependencies, and that’s the reason why Datasphere doesn’t let you delete it. If you could do so, that would lead to both data loss and a lengthy reconfiguration process.

Standard Solution: One solution here would be to use an intermediate object that can serve as a temporary placeholder for the graphical view. To do this, you could preview the SQL code of the graphical view and use it to create a new SQL version with a new unique name (e.g, "Products_2").  

Blog_Im1_JSON_Hack

 

Once the SQL view is ready, you cannot delete the original view immediately to replace it with, due to its dependencies. You would need to replace each reference of the original view with the new SQL version across all dependent objects. After doing this, you can now delete the original graphical view, then recreate a new version of it with the original name ("Products") and the SQL desired form. Finally, you would replace instances of the temporary table with the newly recreated view.

However, if your view is used in many objects, this approach can be very time-consuming.

Hack Solution

The following steps describe how to replace the existing graphical view with an SQL version without impacting any other objects.

Step 1
In Datasphere, you can export a view’s CSN file. So the first step would be to locally export the CSN file of a view by pressing the ‘Export to CSN/JSON File’ in the view you want to convert. This file contains all configurations and logic in JSON Format

Blog_Im2_JSON_Hack

 

Step 2
Once you have the JSON file saved locally, open it and locate the section at the end called "editorSettings." All you need to do is remove the part related to your specific view. In this example, remove rows 731–737. Those lines of code are missing in a SQL View.

Blog_Im3_JSON_Hack
Then save this modified file with a new name (e.g., "Products_2") to keep the original CSN file unchanged in case you need it later in case of an unexpected scenario.

Note/Warnings: The "editorSettings" section includes the graphical configuration for any other graphical views linked to your object in the same SPACE. Only delete the lines specific to your object (in this example, "PRODUCTS") and leave the rest intact. Deleting the entire block would remove graphical functionality from all related objects.


Download the whitepaper and find out
which product is best for your data warehousing strategy

Neuer Call-to-Action


Step 3
Now we have to reimport the newly edited file back to Datasphere.
In Data Builder you select "Import Objects from CSN/JSON File" and you select the file to upload.

Blog_Im4_JSON_Hack

In the list of objects, select only the one you want (in this case, "PRODUCTS") and click "Import CSN File".

Blog_Im5_JSON_Hack

Notes/Warnings: After clicking "Import CSN File", you may receive a prompt asking if you want to update dependent objects. Choose "Yes" or "No" depending on your requirements. You probably want to press “No” to that since you usually don't want to interfere with other objects beside the one you priorly edited. 

Final Step

Within a few seconds, you should receive a notification that the JSON file import is complete. You can now open your view and preview it in SQL format and you can deploy your changes.

Blog_Im6_JSON_Hack

Ensure the outcome meets your needs. If not, you can use the same import process (Step 3) to re-import the original file, restoring the object to its original state and starting the process all over again.

Limitations

By previewing the JSON file of a Graphical View it’s easy to understand that switching a view from SQL to Graphical is almost impossible since you have to manually recreate the code you deleted at Step 2.

Alternative CSN Capabilities

In scenarios similar to the one above that capability can be applied to any Datasphere object with another one of a compatible structure. You could replace a table with another table that has additional columns only by editing the object name in the JSON file.

Using this CSN technique requires careful steps, but it can potentially save significant time and effort. 

While that hack can be highly time efficient, it also carries substantial risks. Without careful attention, users can accidentally manipulate and alter hundreds of objects at once, potentially causing unintended inconsistencies across a project. We recommend using this approach only if there is a failsafe in place and with full awareness of the risks involved.

Do you have questions about SAP Datasphere? Are you trying to build up the necessary know-how in your department or do you need support with a specific issue? Please do not hesitate to contact us. We look forward to exchanging ideas with you! 

Learn more about  SAP Datasphere

,

avatar

Dimitrios

Dimitrios has been working with Native HANA and SAP products since January 2020, and he joined NextLytics as an SAP consultant in early 2022. Thus, he has already gained a lot of hands-on experience with SAP Datasphere. In his free time, he enjoys hiking, watching movies and spending time with friends.

Got a question about this blog?
Ask Dimitrios

SAP Datasphere JSON Hack: Transform Graphical to SQL View
6:59

Blog - NextLytics AG 

Welcome to our blog. In this section we regularly report on news and background information on topics such as SAP Business Intelligence (BI), SAP Dashboarding with Lumira Designer or SAP Analytics Cloud, Machine Learning with SAP BW, Data Science and Planning with SAP Business Planning and Consolidation (BPC), SAP Integrated Planning (IP) and SAC Planning and much more.

Subscribe to our newsletter

Related Posts

Recent Posts