connecting bics datasource with another SQL/DB2 datasource

[Former Member]

I have a use case for connecting a bex query data source (BICS) with data/table from a DB2 database and then use WEBI to report the combined data.

I was wondering what is the best way to achieve this and is there any recommendations.

i hear that universes does not work well with Bex queries (BICS connection).

Thanks.

[former_member285534]

Hi Michael,

Webi can naturally combine different data sources via the creation of the needed data providers and the dimension merge functionality.  Proceed as follows:

1. Create your BEx query and the corresponding BICS OLAP connection

2. Create a relational universe over the DB2 database and the corresponding relational connection

On your webi document:

3. Add a data provider from BEx source

4. Add another data provider from the DB2 universe

5. Combine both providers merging the appropiate dimensions on the resulting microcubes (best practice: use primary/foreign keys instead of text fields).

6. Finally create your report using the merged dimensions

There are of course other alternatives but I think this would one of the easiest to implement.

Regards,

Fernando

( Kindly assign points in case you find this reply is "helpful" or the "correct" answer to your question -- thanx ! )

[Former Member]

Thanks Fernando. I was thinking along the similar lines as you suggested above. Create a Query 1 based on the Bex connection and create a Query 2 in the DB2 universe connection. Then do reporting as you suggested.

Our ITSD indicated that Bex does not work as well as a universe vs using it as a BICS connection. They are suggesting to download the data from the SAP BW (BEx datasource) to the DB2 system and report it there. I would rather not move around so much data and looking for all possible solutions.

On another note, if you have any experience on reporting on SAP BW datasources, there is a 500K cell limit on our SAP BW system to ensure that the system does not go down. I was wondering with much larger companies using SAP BW and BOBJ reporting, how do they get around these type of cell limitations? Do they use WEBI drillthrough reports.

Thanks.

[Former Member]

Hi Michael,

The 500K limit you mentioned, you can change that @ cost of putting more resources on your physical server. I would suggest -

1) Avoid pulling detailed data from BEx query, unless you have no other option.

2) Avoid any non-used object being pulled in the data provider. Try using Query Stripping.

3) You might want to think on the approach you want to take while reporting on BW ie. BICS v/s Univere(.unv). Both has its pros and cons. For eg. you have the flexibility to change aggreagtion for measure object, in case of universe.

4) Follow best practices in the BW. For eg. Enable “Use Selection of Structure Elements” for queries (In transaction RSRT, enter query and select ‘Query Properties). You might want to search such best practices on google, you will get ample of recommendations that are really useful. Also, you might want to know more about limitations in case of BW-BO reporting. For eg. condition in BEx queries are not passed to BO Web-I, etc.

5) Last, you might want to use data federation capability of BusinessObjects and build a Universe (.unx) that joins data from different sources(BW and DB2) at universe level. Personally, I haven't tried it yet. In this case, you must build multi-source universe and use relational connection (instead of OLAP connection) to connect to BW.

If I were you, moving all data from BW to DB2 would be my last resort.

Hope this helps!!

Sameer

PS: Kindly assign points, in case you find this reply is "helpful" or the "correct" answer to your question.

[former_member285534]

I haven't personally modified that limit, but I have used BICS connections and they have worked fine so far.  Only with very high volumes (800,000 rows or so) I saw performance downgrades in the query response.

If you decide to take this approach, try to summarize data as much as possible in both sources before querying and joining them on Webi.

I wouldn't recommend moving BW data to DB2. It wouldn't be a good practice to have duplicated sources (additional effort to establish automatic synchronization (ETL), more administrative tasks, an un-needed risk of inconsistencies, etc.)  If possible, keep copies of the data at most at the original OLTP source plus the analytical EDW.

Fiinally, find a link to an excellent reference, by Ingo Hilgefort, where BICS and universes over BW are contrasted.  Available functionality is compared.  I hope you find it helpful.

Regards,

Fernando