Source System Staging Table

building reports desktopintelligence
1

How do you know whether any data comes from the source system staging tables or not in a report ?

Developer_Designer (BOB member since 2009-08-09)

2

Hi,

From the data itself (visually checking it), I think it’s not possible. Unless there is some piece of data/information that you know for sure that it’s only available in the source system and it’s not from a DWH.

You need to have some kind of flag or metadata that would tell that this data comes from this source and this data comes from that source.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Really thanks. Is it also the same for DWH as a source ?

Lets say, I have report which was built on the following free hand SQL. Now, how will you know whether it comes from the souce system or the data warehouse or the universe:

select acctno, form_incoming_code, create_dt, form_received_dt,

case when
dw_util.calc_business_days(form_received_dt,create_dt) <= 3
then 1
end two,

case when
dw_util.calc_business_days(form_received_dt,create_dt) > 3
and
dw_util.calc_business_days(form_received_dt,create_dt) <= 6
then 1
end five,

case when
dw_util.calc_business_days(form_received_dt,create_dt) > 6
and
dw_util.calc_business_days(form_received_dt,create_dt) <= 11
then 1
end ten,

case when
dw_util.calc_business_days(form_received_dt,create_dt) > 11
and
dw_util.calc_business_days(form_received_dt,create_dt) <= 31
then 1
end thirty,

case when
dw_util.calc_business_days(form_received_dt,create_dt) > 31
then 1
end too_long

from dw_gsk.gsk_pt_form_fact
where trunc(form_received_dt) between trunc(add_months(sysdate,-2),‘mm’) and
last_day(trunc(add_months(sysdate,-1),‘mm’))
and form_incoming_code in (‘CE’, ‘DA’, ‘DB’, ‘DC’, ‘DD’, ‘DG’, ‘DH’, ‘DI’, ‘DJ’, ‘DK’, ‘DL’, ‘DM’,
‘DN’, ‘EC’, ‘EE’, ‘EG’, ‘EH’, ‘EJ’, ‘EK’, ‘EL’, ‘EM’, ‘EN’, ‘EP’, ‘HA’, ‘IC’, ‘ID’, ‘IE’, ‘II’, ‘IP’, ‘IZ’, ‘LB’,
‘LC’, ‘LD’, ‘LE’, ‘LF’, ‘LG’, ‘LH’, ‘LI’, ‘LJ’, ‘LK’, ‘LL’, ‘LM’, ‘ME’, ‘MP’, ‘OD’, ‘RE’, ‘RX’, ‘VI’, ‘ZI’)

group by acctno, form_incoming_code, create_dt, form_received_dt

Developer_Designer (BOB member since 2009-08-09)

4

One report can have more data providers. It means that you need to check all data providers in a report.

A data provider can be based on:

  • a BO universe
  • a free-hand SQL
  • VBA procedure
  • XML
  • a stored procedure
  • a personal data file (txt, csv)

You can identify the source of the data provider by checking its icon in the Data Manager window (go to menu Data → View Data). But you need to know these icons in order to be able to tell that a data provider is based on a universe or based on a free-hand SQL.
You can also edit the data provider and based on what is open (either a query panel or free-hand SQL window or anything else) you can find out how the data provider was built.

Regarding your question whether a data provider gets data from a source system or from a data warehouse:
Nobody can tell this but the one that knows your source systems and/or your data warehouse. We don’t know tables that are uses in your reports so we can’t help with this.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5 
from dw_gsk.gsk_pt_form_fact

This is the line that tells you where it has come from.

6

Hi,
As Mak said, just gather the information about your source,staging and DW schema name and then determine from which schema / area the data comes from.

Omkar Paranjpe :us: (BOB member since 2006-02-13)