Search code examples
sap-iqpowerdesigner

PowerDesigner fails to generate database from physical data model

I am trying to use a Powerdesigner tool for a DDL script generation. There is a prepared Physical Data Model which describes a structure of a Data Warehouse (contains tables, views, domains, dbspaces ...) based on Sybase IQ. Detailed versioning: PowerDesigner 16.6 (SP6), Sybase IQ 12.4.3

This model has a lot of sub-packages in it and under these one can find actual physical diagram, which holds Tables, References, Views and View References.

PowerDesigner window

If I describe it on the above picture, there is a "FINANCIAL" Physical Data Model, it has sub-package "BANK_MAIN", this has another sub-package "BK_ACCT_KEY_MEAS" and under this an opened Physical Diagram with the name "BK_ACCT_KEY_MEAS". There can be seen several dimension tables and one fact table.

So I press CTRL + G (or menu Database / Generate Database) to proceed with generating DDL. I tried to restrict different settings (all objects, just tables and views and others) but none of them are showing desired result.

If I restrict to "tables and views" I can see this content:

    disconnect;

    drop database 'BASE_MODEL.db';

    /*==============================================================*/
    /* Database: BASE_MODEL                                         */
    /*==============================================================*/
    create database 'BASE_MODEL.db';

    start database BASE_MODEL;
    connect database BASE_MODEL;

There is basically missing a reference to any table or view. I am still reading through corresponding documentation, but I am not finding any relevant information that could guide me to the result.

Can anybody help me?

Solution

  • The right approach to generate a DDL for database is to open any physical model that is under main package ("FINANCIAL" in this case). Next step is hitting CTRL + G (or menu Database / Generate Database) and then properly setting up the environment to generate. As this model pack contains many various packages, there is a magic button "Include Sub-Objects" under Selection tab. Activating this button allows to select all objects that are mentioned in the whole model pack (in tabs "tables" and "views"). Below picture shows this description in a brief.

    Selection tab Below is part of valid and desired DDL output:

    disconnect;

drop database 'BASE_MODEL.db';

/*==============================================================*/
/* Database: BASE_MODEL                                         */
/*==============================================================*/
create database 'BASE_MODEL.db';

start database BASE_MODEL;
connect database BASE_MODEL;

/*==============================================================*/
/* Table: A_ACCT_DETAIL_SUMM                                    */
/*==============================================================*/
create table _O_W_N_E_R_.A_ACCT_DETAIL_SUMM (
   ACCOUNT_ID           SYB_ID          not null,
   ACCT_TXN_TYP_ID      SYB_ID          not null,
   ACCT_STATUS_ID       SYB_ID          not null,
   PRODUCT_ID           SYB_ID          not null,
   GEO_ID               SYB_ID          not null,
   MONTH_ID             SYB_ID          not null,
   DEMO_ID              SYB_ID,
   ISO_CRCY_ID          SYB_ID,
   REF_ISO_CRCY_ID      SYB_ID,
 ...