Datasphere change a remote table to a view

[liskb]

• I have a space where I replicate tables from an ECC system then use these tables in the SQL data models. 
• Now I have a need to create a historical version and current version of those tables and replicate to Datapshere using Filters. 
• Take BSEG for instance.  I will have a h_BSEG remote table replicated with data <= 2024.  Then a c_BSEG replicating data >= 2025.  I will only have to do updates to the c_BSEG saving time in replications. 
• I need to do this because our version of SAC does not support realtime replication and we don't have anything like SLT.
• Then I will union the historical data h_BSEG with the current data c_BSEG in a view BSEG.  I will do this all in a different space. 
• The problem I have is all my queries use the remote table in the original space and I will have to do a lot of work to update the queries replacing the source table reference.
• I was wondering if I could replace my remote table with a view pointing to my union view.  I know I could do it if the remote table does not exist but I cannot delete it since there are dependencies.

Anyone know a way to replace my Remote table so it stays the same name in the same space so all my queries it is shared to in other spaces still work?

Appreciate any suggestions.

Bart

[enrique_franco]

Hi,

In our Datasphere setup, we replicate tables from ECC and use them across multiple SQL models and views. Due to limitations in our SAC version (no real-time replication support) and the absence of SLT, we had to optimize replication by splitting large remote tables into historical and current datasets.

Here’s a simplified version of what we did using BSEG as an example:

• We created:

  • h_BSEG: remote table with data <= 2024

  • c_BSEG: remote table with data >= 2025

• Our goal: only replicate changes in c_BSEG, saving time and load.

Then we created a view:

SELECT * FROM h_BSEG
UNION ALL
SELECT * FROM c_BSEG

This gives us a unified view of the full dataset, which we intended to replace the original BSEG remote table with — so that existing queries and views across other spaces would continue working.

The Challenge:

The original BSEG remote table has many dependencies (queries, models, shares), so deleting or renaming it wasn’t straightforward.

Solutions Explored:

1. Rename/Delete + Replace:

   • Delete BSEG, reimport it as z_BSEG_original.

   • Then create a new view named BSEG (with union logic).

   • Re-share it to consuming spaces.

   • Requires managing dependencies during the switch.

2. Alias View (BSEG_union)

   • Create a union view under a new name (BSEG_union).

   • Update queries manually to use it.

   • Less risk, more manual work downstream.

3. Replace Shared Object

   • If BSEG is only used as a shared artifact:

     • Unshare → delete remote table → create view → share under same name.

     • Consumers still see BSEG — now backed by the union view.

Key Lessons:

• A stable semantic layer (views with fixed names) is invaluable — you can evolve logic behind the scenes without disrupting consumers.

• Planning for decoupled access and layering helps maintain agility and avoid heavy refactoring.

If anyone’s faced something similar or has additional strategies for managing dependencies when replacing remote tables with views — would love to hear them!

[liskb]

Thansk for the feedback. Unfortunately, we started setting this up in early 2021 when we were new to DWC and some of the features weren't fully mature and knowledge a little lacking. We are actually one of only 10 customers still on the orignal SKU and SAP met with us to get us on the most recent SKU of datasphere. So, I totally agree with a semantic layer of views. Fortunately, all remote replicated tables are in their own space and shared out to other spaces where they are used in SQL or graphical views. Are you saying if I unshared the table to the spaces, it won;t be views by the system as haviong dependencies anymore and I can deleted the remote table and replace it with a view like discussed? The share the view out to the spaces? This won't break any queries except if they are called during the transition?

[liskb]

BTW, if I can unshare the remote table. delete it and recreate a view in the space and share the view out, that will save a ton of time versus fixing all the queries.

[liskb]

I created a test scenario where I shared a table to another space, adding it to a SQL view and then tried to unshare. The system sores not allow me to unshare. Says "We could not unshare your Remote Table (Relational Dataset) because it is used by the following model in a different space." Maybe this could be done in the past but seems not now.