SQL Query running forever in BO while it runs in SQL developer Client and Power BI in 2 sec

[usman1158]

I am having a weird issue in our UAT environment. There is a specific table that when we run in ether BI Launchpad, Universe, Rich Client, it runs forever.The same query when we run in SQL developer Client or even in Power BI runs fine, The query also runs fine in PROD server and the table has like only 150k rows.

Till now I have tired creating new connections, new universe and even tried to run the query as a FHSQL, but it just runs forever. I have tired to restart the entire server as well, clear cache ( under catlina folder).

I have also talked to the DBA and he says the query just runs fine at their end as well.

Ofc when I add the filter the data comes back, but that too it takes like alot of time. Like without filter the results is coming back in Powerbi and SQL Developer tool in like 2 secs, and this UAT BO env it runs forever.

attaching the screenhots.

SAP BusinessObjects BI Platform 4.2 Support Pack 8 Patch 5

Version: 14.2.8.3642

DATABASE : DB2

fhsql-based-query.jpg universe-based-query.jpg

[DellSC]

16 GB RAM is bare bones minimum for the system, it works better with 32 GB or more depending on the workload.

Is the DSL Bridge Service running in the AdaptiveProcessingServer (APS) as well or did you split that into multiple servers, which is the best practice.

Also, if you're running only Webi reports, here are the servers you need to have running:

CentralManagementServer
InputFileRepositoryServer
OutputFileRepositoryServer
WebiProcessingServer
AdaptiveJobServer
ConnectionServer
ConnectionServer32 (only if you have a database that requires a 32-bit connection
APS_Webi (split from the AdaptiveProcessingServer) contains the following
Custom Data Access Service
Document Recovery Service
Excel Data Access Service
Rebean Service
VisualizationService
Web Intelligence Monitoring Service
TraceLogService (will be added automatically.)
APS_Core (split from the AdaptiveProcessingServer)
Monitoring Service
Platform Search Service
Promotion Management Service
Publishing Service (If you're using Publications)
Pubishing Post Processing Service (If you're using Publications)
Version Management Service
Visual Difference Service
Client Auditing Proxy Service
TraceLogService (will be added automatically.)
Event Server (If you're scheduling based on events)

You don't need any of the Crystal Reports services unless you're running Crystal Reports.

You don't need the Data Federation Services unless you're using multi-source universes.

You don't need any Analysis Services unless you're using cubes as data sources.

By shutting down the servers/services you're not using, you'll free up RAM.

I believe the issues you're running into are due to a lack of RAM and the memory/processing overhead from the services that are needed to run queries on the server.

-Dell