DSXchange

GURUS,,

I need to generate the report for the DS jobs which will go against the jobs and have information as below.

Source Tables Names --- Target Tables Names ---- all the columns names of Target table (may be in one column seperated by deliminator )

or an HTML report like if we click on target table name it drills down to all of names of column.

Thanks in advance....

Thanks
M

Nothing out of the box exists like that. As of v7.5 you can make a report for a single job from designer (clicking on the button looking like internet explorer on the toolbar).

But if you're a bit skilled in any quick and dirty language: perl, python, .... you can easily generate such a list from dsx or xml exports.

Ogmios

Hi,

We are on 7.1 and do not have report facility ..........
But is there is any routine or job code to extract the info from .dsx ..would be gr88 help ..
Thanks

ogmios wrote:Nothing out of the box exists like that. As of v7.5 you can make a report for a single job from designer (clicking on the button looking like internet explorer on the toolbar).

But if you're a bit skilled in any quick and dirty language: perl, python, .... you can easily generate such a list from dsx or xml exports.

Ogmios

shrey3a wrote:Hi,

We are on 7.1 and do not have report facility ..........
But is there is any routine or job code to extract the info from .dsx ..would be gr88 help ..
Thanks

ogmios wrote:Nothing out of the box exists like that. As of v7.5 you can make a report for a single job from designer (clicking on the button looking like internet explorer on the toolbar).

But if you're a bit skilled in any quick and dirty language: perl, python, .... you can easily generate such a list from dsx or xml exports.

Ogmios

Hi,

you can use dsjob -report option to generate report.

I hope this helps,

thanks,
RK

DS 7.1 should have Reporting Assistant (since 7.0 has it) that can be accessed via Tools menu in the Manager. Apart from the bitmap of the job, it gives you the information that you have asked for. Alternatively, search for EtlStats in this forum.

gateleys

EtlStats does not have source and target tables. This can be done in DwNav and MetaStage. I think both are limited to accuracy of the metadata. DSJobReport subroutine will also print columns which belong to the link. All of these print SQL which belongs to a link. This is the real issue. There are 3 types of SQL stored in most inbound links. User defined, Column Generated and one more. All of these are stored but one is active. If you generate or use column generated then the list of tables is accurate. On user defined they can easily get out of sync. So Reporting Assistant and DwNav can get out of sync because of where this data is sourced. I would imagine MetaStage also gets out of sync in the same manor.

To fix this you need to parse the SQL to extract the table names from the SQL. This can be done but is complex. I think all good documentation on ETL should break down to source and target tables and ignore all stops in between. To write ETL and/or re-engineer your ETL then source to target documents are critical. I think all source to targets should also be documented in tables and browsed by end users exactly like you described. We all use tools like Toad or SQL Server Enterprise Manager. To browse all the tables in a database and also see the source tables and columns for these columns is very powerful. The most powerful use of metadata in my opinion.