Reporting on multiple reporting database across geographies from one BO clustered environmnet

[Former Member]

Hi All

Really appreciate some advice from BO gurus on a SAP BO architecture issue.

I have a requirement where we have 2 reporting database server (one in UK and another in Netherlands).We have one SAP BO reporting system in Netherlands in the same data center as the reporting database (in Netherlands)

My client wants to use the same BO reporting environment currently in Netherlands to report from the database in UK.  Unfortunately the connectivity between the datacentre in Netherlands and UK is not good. It is difficult to refresh report (specially for adhoc large report). Fortunately the user groups in NL and UK are completely independent.

Is there any architecture i can follow where i can have a clustered BO environment with one machine in Netherlands and one in UK and 'somehow' compel the NL user group to use the Netherlands BO server to run the reports from the Netherlands DB and UK user group to use the UK BO server to run the reports from the UK DB. The only problem is CMC database will be in NL so there may be some access time lag for people logging in from UK.

Appreciate if someone can share some idea for such a scenario which we can test in a POC.

[former_member182521 · Accepted solution]

You can try with multiple option something like Security profile at universe data foundation level (Forcing users to select the connection according to their region) and Server groups (Server group for each region).

I would suggest you to come up with different components that you want to avail in both the regions  and we can recommend you accordingly.

Thanks

Mani

Twitter : @meetmanie

[Former Member · Accepted solution]

Hi Mani

Thanks for your reply.

Basically i will have one BICS connection on a BW environment that will be used by NL users only. Similarly i will have one universe on a SQL server that will be used b UK users only. There is no overlap or cross reporting requirement i.e. no requirement to merge the data between NL and UK. Both NL and UK will have completely different set of reports.

All we are looking at is instead of creating two separate BO production server whether we can have one production server (or a cluser BO server where one machine is in NL and another in UK) so that the BO servers are local to the database server , so that the refresh time can be reduced significantly.

Kind Regards

Sanjit

[Former Member · Accepted solution]

Hi Mani

Thinking about it again, i think you have provided a very valuable point.

If i create Server groups (Server group for each region) and give 'access' or 'deny' rights to user group to particular server group then i can force user groups to use particular services (like webi services) from a particular server.

But what i am not sure is the server groups are logical groups. So if i have webi procroessing service 1 on UK server and webi procroessing service 2 on NL server and each server group has UK and NL Webi processing service 1 and 2 respectively does that mean a NL user group will always get the Webi processing service 2 as access is denied on Webi processing service 1. I am not suer about that about i certainly will try that in POC if we follow that design.

Let me know your thoughts.

Sanjit

[former_member187093]

Hi Sanjit,

you can have BO nodes in UK and NL but required somewhat good network because CMS will check all available services for every one or two minutes and update the repository.

as mani mentioned, server group is the best option and you can have the app server also in UK and NL so that you easily mange the users and control.

as you mentioned, login will take time. processing the report will not take time because your reporting database in located on the same data center but all the status/updates will go to the CMS in another region 🙂

we had a setup like this but network is good. we will use BO even one region is completely down. we did this for one of the bank. it's running very well.

we did POC for BO cluster with Linux and widows, it's also working very well. you can not have CMS on two different OS but processing servers fine. hope this helps!

[Former Member]

Hi Shiva

Thanks for your reply. Can you please explain me a the server group concept.

Lets say i have the CMC and a BO server in NL and another BO server in UK which is pointing to the CMC in NL. If a NL user fires a query CMC can direct the request to any BO server. In this scenario a NL user may have the Webi processing services running on the BO box in UK. The Webi processing server in UK in that case will fetch the data from NL database to the UK BO server which impact the time to refresh (worst it will time out due to bad connectivity between NL and UK). Is there a way we can forge the NL BO user group to access the NL server and UK BO user group to access the UK server ?

Cheers

Sanjit

[Former Member]

I think if it is adhoc reporting the sure shot way of doing it will be to have two different setup.

But I am thinking if we give two different URLs to the UK and NL Users probably will that work. I went through the process flow that happens when a user hits a webi report (listed below) and I am not sure at Step 5 if the request will go to the WIPS on the NL server or the one available at that time.

This workflow describes the process of a user viewing an SAP BusinessObjects Web Intelligence document on demand to see the latest data; for example, from the Web Intelligence viewer in BI launch pad:-

1. A web browser sends the view request to the web application server via the web server.

2. The web application server interprets the request and determines that it is a request to view a Web Intelligence document. The web application server sends a request to the Central Management Server (CMS) to ensure that the user has sufficient rights to view the document.

3. The CMS checks the CMS system database to verify the user has sufficient rights to view the document.

4. The CMS sends a response to the web application server to confirm the user has sufficient rights to view the document.

5. The web application server sends a request to the Web Intelligence Processing Server, requesting the document.

6. The Web Intelligence Processing Server requests the document from the Input File Repository Server (FRS) as well as the universe file on which the requested document is built. The universe file contains metalayer information, including row-level and column-level security.

7. The Input FRS streams a copy of the document to the Web Intelligence Processing Server, as well as the universe file on which the requested document is built.Note

This step also requires communication with the CMS to locate the required server and objects.

8. The Web Intelligence Report Engine (on the Web Intelligence Processing Server) opens the document in memory and launches QT.dll and a Connection Server in process.

9.QT.dll generates, validates, and regenerates the SQL and connects to the database to run the query. The Connection Server uses the SQL to get the data from the database to the Report Engine where the document

is processed.

10. The Web Intelligence Processing Server sends the viewable document page that was requested to the web application server.

11. The web application server sends the document page to the web client via the web server, where the page is rendered and displayed

[Former Member]

Hi Kashif

Thanks for your reply. The web application server unfortunately sends a request to any available Web Intelligence Processing Server. This is done with a good intention of load balancing by SAP but in my very specific case it works against my requirement .

Kind Regards

Sanjit

[denis_konovalov]

if only schedule based reporting is allowed - it'll be possible, as you can isolate/assign all schedules to server groups - make all schedules for UK people to run on UK nodes of your cluster.

It's impossible to do so for Ad-hoc reporting (unless you create special set of reports/universes) for the UK users.

But then you might as well have 2 environments separately.

[Former Member]

Hi Denis

Thanks for your reply. Can you explain me the following please "(unless you create special set of reports/universes) for the UK users."

NL users will use a BICS connection to the BW database and create their own reports. Similarly UK users will have a universe on a SQL data base in UK and will have their own reports. There will be common reports between NL and UK.

Is there a way i can 'force' a BICS connection to run on a particular BO server or a Universe to run on another BO server for adhoc reports. As you mentioned it is possible to do it on scheduled reports but i am not sure whether it is possible to do it on adhoc reports.

Cheers

Sanjit

[denis_konovalov]

unfortunately, for adhoc reports you can't assign server groups.
you can assign them to existing reports only, so schedules and editing/refreshing of existing reports can be restricted to specific set of servers. When creating new report system will pick any available server to do the job.

[Former Member]

Hi Denis

Based on Mani's comments above, if i create Server groups (Server group for each region) and give 'access' or 'deny' rights to user group to particular server group then i can force user groups to use particular services (like webi services) from a particular server.

But what i am not sure is the server groups are logical groups. So if i have webi procroessing service 1 on UK server and webi procroessing service 2 on NL server and each server group has UK and NL Webi processing service 1 and 2 respectively does that mean a NL user group will always get the Webi processing service 2 as access is denied on Webi processing service 1. I am not sure about that about i certainly will try that in POC if we follow that design. Not sure even if i am going in tangent

Sanjit

[denis_konovalov]

denying rights to particular server/service might actually work, but I'm not sure to be honest.

needs to be tested.

you'll still have to work on improving WAN links between CMS in NL and processing servers in UK.

[former_member185603]

Only possible solution is have multiple connections to Universe, one is UK and another one is Netherlands. You can have multiple universes with different connections and save them in different folders. Setup security for those user groups based on the location.

Since CMS DB , and Filestore in UK, you will have some lag in login and retrieving the reports. But the refresh will be much faster, since DB connections pointing to the location where the User base is.

But maintaining 2 universes will be nightmare. Whenever there is an update to the universe, you need to update the universe in other location as well.

[Former Member]

Hi Jawahar

Universe is not a problem as i can have two universe connecting to 2 databases. But the problem is when someone runs a report on a universe on UK database on an adhoc basis. Two things can happen:

1) If the BO server is in NL the request will go from NL to UK and the data will be fetched from UK back to NL BO server and then the HTML page will be send back to UK . This will lead to severe latency specially if the connection between the BO server and DB server is slow as is the case in our case 

2) If we have a clustered environment with one BO server in UK and one in NL the system might not recognize  a NL user group and run the NL report on the NL BO server or a UK user group and run the UK report on the NL BO server specially for adhoc reports (unless the reports are scheduled) . There is no way i can tie groups to services running on a particular server.

Regards

Sanjit