cancel
Showing results for 
Search instead for 
Did you mean: 

PowerDesigner Not Reverse Engineering View Columns

former_member1144245
Participant
0 Kudos

Appears to be a bug with the PowerDesigner .xdb when it comes to reverse engineering views. It will reverse engineer the view name, but not any of the columns. Checking the .xdb under Script/Objects/View/SqlAttrQuery, it's clear that view columns are not being selected:

select

   t.DatabaseName,

   t.TableName,

   case(substr(lower(trim(t.RequestText)), 1, length('replace'))) when 'replace' then

        cast('create ' || substring(t.RequestText from position('replace' in lower(t.RequestText)) + 😎 as varchar(32000))

   else cast(t.RequestText as varchar(32000))

   end

from

   DBC.Tables t

where

   t.TableKind in ('V', 'I')

[  and t.DatabaseName = %.q:SCHEMA%]

[  and t.TableName = %.q:VIEW%]

This is from a Teradata v12 .xdb file. I have fully customized it to work for Teradata v15. I am using PowerDesigner v15.2.

If anyone can provide the appropriate query to replace this one, it would be very much appreciated.

Accepted Solutions (1)

Accepted Solutions (1)

c_baker
Employee
Employee
0 Kudos

This may depend on the actual DBMS.  If the view is defined as 'select * from ...', some DBMS engines will expand the '*' properly into the underlying table columns.  Some don't.

Does the R.E. fail when you create a view and refer to explicit column names from the underlying table?

Also you are using an older version of PD.  Although PD 16.5 currently only goes up to TD 14, the same query shows up in the View <<Join Index>>/SqlAttrQuery, but the View definition SqlAttrQuery contains:

{OWNER ID, VIEW ID, Create TPL ...}

select

   t.DatabaseName,

   t.TableName,

   case when (t.RequestTxtOverflow = 'R') then

      cast(d.RequestText as varchar(32000))

   else

      cast(t.RequestText as varchar(32000))

   end,

   case when (t.RequestTxtOverflow = 'R') then

      d.LineNo

   else

      0

   end

from

   DBC.Tables t

   left outer join DBC.TableText d on (d.DatabaseName = t.DatabaseName and d.TableName = t.TableName)

where

   t.TableKind in ('V')

[  and t.DatabaseName = %.q:SCHEMA%]

[  and t.TableName = %.q:VIEW%]

order by 1, 2, 4

Are you sure you are selecting the correct view type?  Perhaps you should upgrade to the latest PD and base your TD R.E. on the V14 definition at least.

Chris

former_member1144245
Participant
0 Kudos

Thank you Chris. I appreciate you giving me the SqlAttrQuery from v14. I gave that a try. It only reverse engineers the view name and description, but not the view columns. This is true even for views that were generated from DDL with an explicit column definition. There is no error, only incomplete information.

I will eventually upgrade to a newer version of PD, but I'm not seeing any compelling reason to do so at this point. I experimented with a trial version of v16, but the software is still considerably behind when it comes to Teradata. It didn't solve what I needed in order to adapt PowerDesigner for Teradata so that it could be used to create a complete physical data model and generate DDL.

Some significant changes have been missed in the .xdbs for Teradata for v12 and v13. Teradata has a whole new set of dictionary objects. These have the same names as the legacy tables, but with a V suffix (e.g. DBC.TablesV). I can't see what has been done with v14, but the SqlAttrQuery sample is still referencing the legacy objects. This was causing truncation when reverse engineering tables.

I am so glad that PowerDesigner is customizable. Because of this I have been able to modify a copy of the v12 .xdb to work with v15 of Teradata. Our is a very large organization and not everyone is using PowerDesigner. Been testing the reverse engineering for those that have objects in the DB and want to adopt PD for data modeling. At least the tables can be reversed now. Will have to see about the views.

Thank you for your assistance. I very much appreciate your time.

c_baker
Employee
Employee
0 Kudos

Please raise a support incident to get this fixed so the new catalog tables are used.

Chris

former_member1144245
Participant
0 Kudos

I would love to raise a support incident. Unfortnately, my company does not currently have a support contract for PowerDesigner.

If the board is being monitored, perhaps someone can note this. That has happened before when an issue I posted uncovered a bug.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Monique,

I think that PD reverse-engineers in compliance with its philosophy of view. In other words, it takes the SQL query and View columns are automatically parsed into View columns, as you can see, when you create view for example from table already existing in the model. The view columns are created automatically and are "read-only" (=created automatically and non-deletable). And I think it is the same for reverse engineering. Rather try to focus on the syntax of the view query. There might be something that PD cannot deal with. Even if the syntax is correct. (PD parser is not almighty).

Ondrej

former_member1144245
Participant
0 Kudos

Thank you Ondrej. I don't really understand your comments. It seems that there should be something to control the selection of view columns, similar to how the table columns are reversed.