BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

drill down in distributed heterogenous database environment


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
bo_forum
Forum Member
Forum Member



Joined: 23 Nov 2010

Posts: 2



PostPosted: Tue Nov 23, 2010 2:14 am 
Post subject: drill down in distributed heterogenous database environment

Hi
We have regional DWHs / Data marts that contains detailed transaction level data. We are building a global DWH which contains aggregated data from individual regional DWHs. One of the requirement is to provide the drill down capability to users, within the same region from aggregated DWH to the detail data available in regional DWH.
The challenge is these DWHs are hosted in heterogenous DB environments and they are spread across the globe.
The global DWH would be would Teradata and regional DWHs are in Oracle.
Please let me know
(a) In BusinessObjects how this can be achieved in a generic way (preferred solution), in terms of architecture and design aspects.
(b) if there exists a solution tied with either of the DB platforms (technology spcific solution to Oracle / Teradata and BO), which can be utilized by BO in a seamless fashion
(c) How to overcome the network latency issues.
(d) Also would like to know if this is a common approach followed in major global oraganizations, what are the pros and cons in acheiving this architecture.
Thanks in advance
Back to top
KFonMurphi
Principal Member
Principal Member



Joined: 16 Oct 2007

Posts: 383


flag
PostPosted: Tue Nov 23, 2010 3:58 am 
Post subject: Re: drill down in distributed heterogenous database environm

Some ideas:

Drill down comes from hierarchies created in one universe. A universe has only one connexion, so you can't click on an object and connect on another base.
And a universe has a driver for only one database, and one connection, so you can't even change the DB automatically. They will speak different SQL dialects anyway, and the universe would not adapt.

There is a work-around: create two universes, one on your DWH, the other on the details. A document would have two queries on both universes.

If the users are only interested by the details from their area, you can set the rights on the universe connections, so they see only "their" local Oracle DB.

About the latency problem: you can use a central BO server (and details data will make thousands of kilometers back and forth, and your users will go slowly mad it they are not buddhist monks or your company has huge pipes ) or a server for each location. If the users use the Rich Client, they can query their local base directly without gooing through the server.

Remember that BO can execute reports in the background, that can help some problems. You may set the rights on the DWH universe to forbid the users to refresh at will, only in the background.


Perhaps is Data Federator another option for you, see http://www.forumtopics.com/busobj/viewtopic.php?t=107574
(I have no experience with it.)

_________________
KFM
Back to top
Ottoman
Principal Member
Principal Member



Joined: 04 Oct 2002

Posts: 242
Location: Macclesfield, Cheshire, UK


flag
PostPosted: Tue Nov 23, 2010 4:16 am 
Post subject: Re: drill down in distributed heterogenous database environm

You could have two universes, one on the central DWH and one for regional with different connections/databases.

A master report on the central DWH can contain drill through links using opendocument to "child" regional reports.

The master or child reports can hold prompts (from universe or in designer security) to direct the sql query to different regional DWH, so long as they can all be accessed through the same connection.

This is a complicated area but I have done this type of work before with BO so I know it can be done.

Users can be placed in security groups with connection overrides, to point at different physical databases sharing the same structure.

Universes can also report from different databases, but only if those databases can be accessed from a single host database connection - in effect, the host db passes the query to the remote db. Performance will be key.

The latency problem will always be an issue, and requires a Systems Architect to investigate. It may be that a Citrix type solution may be suitable for viewing, reducing bandwidth requirements.

_________________
Currently working in Sheffield!

still living in "sunny" Macc


Last edited by Ottoman on Tue Nov 23, 2010 4:25 am, edited 1 time in total
Back to top
bo_forum
Forum Member
Forum Member



Joined: 23 Nov 2010

Posts: 2



PostPosted: Tue Nov 23, 2010 4:24 am 
Post subject: Re: drill down in distributed heterogenous database environm

Thanks Ottoman. I thought of a similar solution but as this is a complex one I am trying to check for better options.
As you have already done some work in this area, I am looking for some dos and donts in this area. It would be great if you can provide few tips on this.
Back to top
Ottoman
Principal Member
Principal Member



Joined: 04 Oct 2002

Posts: 242
Location: Macclesfield, Cheshire, UK


flag
PostPosted: Tue Nov 23, 2010 4:34 am 
Post subject: Re: drill down in distributed heterogenous database environm

I think it would be best for you to set aside some time for a very simple proof of concept

-do some research on the opendocument function
-do some research on designer universe security overrides

-set up a very basic universe on teradata
-set up a very basic universe on regional dwh

-set up a couple of users & groups for regional users
-set up the regional connection & security

-build a sample master/detail report pair

I find with this sort of thing you really need to play around & get familiar with the technology & work closely with the dba

sorry its a bit vague at the moment!

happy to help!

ottoman

_________________
Currently working in Sheffield!

still living in "sunny" Macc
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0286 seconds using 18 queries. (SQL 0.0028 Parse 0.0009 Other 0.0249)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo