Lost data mapping on views

FastChangeCo^TM's data modelers keep experiencing the problem of lost mappings from views to tables in PowerDesigner.

Xuefang Kaya (one of the data modelers on the Data Management Center of Excellence (DMCE) team) noted that this only occurs in the context of Views as Source Data Objects.

What's the problem?

Xuefang explains to the DMCE team how and why PowerDesigner loses mappings with a simple example. She created a ‘standard view’ as a source data object containing a simple SELECT statement. Using the Mapping Editor, Xuefang created a mapping to the target data object ‘standard view’ (table).

SELECT Column_1 AS Column_1
      ,Column_2 AS Column_2
      ,Column_3 AS Column_3
FROM MyDummyOwner.MyTable

"The following figure shows an undamaged mapping. All columns are connected correctly and show the intended data flow from source to target" Xuefang elaborates.

"After changing the simple SELECT statement to a slightly more complex statement, PowerDesigner loses a data item mapping (Column_3). This is because the PowerDesigner - for reasons unknown to me - loses the GUID of the Source Data Item to which the Data Item mapping refers. The actual mapping still exists, only the original source data item (GUID) no longer exists," she explains.

SELECT     
    Column_1 AS Column_1
   ,Column_2 AS Column_2
   ,CASE WHEN Column_3='X' THEN 'Z'
         ELSE Column_3 
    END AS Column_3
FROM MyDummyOwner.MyTable

This phenomenon causes a lot of additional work in the DMCE team and always has a not insignificant impact if the lost mapping is not noticed during testing.

What is the solution?

"My research has yielded the following workarounds, which have proven to be quite stable. Unfortunately, I can't rule out the possibility that mappings will still be lost. But we can't wait until the manufacturer offers us a solution," Xuefang shrugs."

The first thing you should always do is set the appropriate views to User-Defined. You can find this in the View Properties on the General tab," and she demonstrates it right away in PowerDesigner.

"The second important step to prevent mappings from getting lost is to 'encapsulate' the SELECT statement. By this I mean that you either create a table subquery or use a Common Table Expression (CTE). This way the actual (outer) SELECT always remains unchanged, no matter if you add a CASE statement or JOINS to the SELECT."

The team eagerly follows Xuefang's explanations. She illustrates what she means with two examples.

Solution with a table subquery

SELECT 
     Column_1
    ,Column_2
    ,Column_3
FROM (    
SELECT Column_1 
   ,COALESCE(Column_2,'') 
   ,CASE WHEN Column_3='X' THEN 'Z'
         ELSE Column_3 
    END AS Column_3
FROM MyDummyOwner.MyTable
) AS SubTable

Solution with a CTE

WITH
myCte AS (
  SELECT Column_1 AS Column_1
        ,COALESCE(Column_2,'') AS Column_2
        ,CASE WHEN Column_3='X' THEN 'Z'
              ELSE Column_3 
         END AS Column_3
   FROM MyDummyOwner.MyTable
)
SELECT Column_1
      ,Column_2
      ,Column_3
FROM myCte

The DMCE team and Xuefang are not really happy with the workaround, because the root problem is not solved. But they can live with it and get rid of the extra effort caused by the lost mappings.

Then, during the implementation with the CTEs, a new problem appeared. But that is another story. More about this in the next article of the series. You definitely have to visit again.

Until then
yours Dirk