Connections Source

[former_member402770]

Hi,

  I have an requirement where I have multiple source systems as SAP BI(OLAP Conn), Oracle(Existing), MSSQL, Excel...etc..

We know that reconciliation hapens between source and target.

I have source DB as Oracle(New) and target DB as SAP BI(OLAP Conn), Oracle(Existing), MSSQL, Excel

Now, i have all my Source and Target Systems IDT Connectivity, what i wanted to understand and help me is:

there are 5 reporting reqds where,

3 reports is build on Oracle(New) Universe & 2 reports are reconciliation-base reports for which Oracle(New) is as we explained above highlighted...

So, In IDT 4.1, i have

My Connectivity are:

Oracle(New).cns

SAP BI(OLAP Conn).cns

Oracle(Existing view) 1.cns

Oracle(Existing view) 2.cns

MSSQL 1.cns

MSSQL 2.cns

Single-Source Conn where Oracle(New) Universe is developed.for 3 reports (Adhoc)

Multisource-Source Conn where 2 reconciliation reports needs to be developed..(Canned)

The issue is Multisource-Source Conn where Canned reports should be developed between

Oracle(New) Vs SAP BI(OLAP Conn)

Oracle(New) Vs Oracle(Existing view) 1

Oracle(New) Vs Oracle(Existing view) 2

Oracle(New) Vs MSSQL,1

Oracle(New) Vs MSSQL,2

The question is can i utlize the adhoc universe which is developed for 3 reports? or should create again Oracle(New) Universe for our canned report reqd?

how about datafoundation layer. Is it going to be one for each source vs target for above connectivity's or not?

how about business layer. Is it going to be one for each source vs target above connectivity's or not?

Pls. note as this is reconciliation report both field from source vs target must me merged at report level.

Appreciate your help with details or screenshots.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

how's the relationship between tables going to be in the multi-source universe? like oracle view1 to view2 join is required and possible? or all connections tables are going to standalone?

The question is can i utlize the adhoc universe which is developed for 3 reports? or should create again Oracle(New) Universe for our canned report reqd?

What do you mean by Adhoc universe? are you going to be discontinue this universe? is this universe specific to three reports ?

how about data foundation layer. Is it going to be one for each source vs target for above connectivity's or not?

if there is no relationalship going to be happen between the tables then it will not make sense to create one DF layer for all.More tables in the single DF means more time to refresh the structure  in case required.Depend on the tables joins between the source and requirement you can create multiple DF's.

Even you are going to create single DF for each one,select the multisource data foundation option so in future you can add more connections if required.

how about business layer. Is it going to be one for each source vs target above connectivity's or not?

According to DF's, create the business layers.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

oracle view1 to view2 join is not required, the relationship/join reqd is between

Oracle(New) Vs SAP BI(OLAP Conn)

Oracle(New) Vs Oracle(Existing view) 1

Oracle(New) Vs Oracle(Existing view) 2

Oracle(New) Vs MSSQL,1

Oracle(New) Vs MSSQL,2

adhoc universe is specific to three reports ie. Adhoc report reqd. for 3 reports based on single-source universe Oracle(New)

5 canned reports/tabs in one single report if possibile based on Mulitsource univ. Below is:

Oracle(New) Vs SAP BI(OLAP Conn)  3 bex queris

Oracle(New) Vs Oracle(Existing view) 1  only 1 view

Oracle(New) Vs Oracle(Existing view) 2  only 1 view

Oracle(New) Vs MSSQL,1   4 tables

Oracle(New) Vs MSSQL,2   4 tables

There is no relationship between views but there is one source which is default Oracle(New)  Vs Targets are SAP BI(OLAP Conn) , Oracle(Existing view) 1, Oracle(Existing view) 2, MSSQL,1, MSSQL,2. No relationship between Oracle(Existing view) 1 & Oracle(Existing view) 2, but there exist relationship always between Oracle(New) Vs Oracle(Existing view) 1, Oracle(New) Vs Oracle(Existing view) 2..

Pls. note all 6 connections are standalone.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

In this case you can create one multi source universe based on the Oracle(New) , Oracle(Existing view) 1, Oracle(Existing view) 2, MSSQL,1, MSSQL,2.As you mentioned all views are going to linked with Oracle (New).create One DF layer and one Business layer for this.

SAP BI(OLAP Conn)  3 bex queris

You can't join the OLAP sources in the Multisource universe.If i am not wrong then  you are using BICS connections on top of BW queries.Either to create relational connection and use BW Cube/DSO/Multiprovider in the mulitsource universe or keep separate this part.At webi level add multiple queries and merge the  objects at report level.

About the single source universe which you have already built,Once you  build the multisource universe then later change the data source of three reports from single source universe to multisource universe.in this case single universe will be the source for all your reporting.

Amit