Optimizing BI Administration in SAP Environments: Q&A on How to Properly Maintain and Secure Complex, Shared Landscapes

Published: 01/May/2016

Reading time: 42 mins

Business intelligence (BI) is the lifeblood of data-driven organizations, enabling you to turn the data in your SAP systems into actionable information that drives better decision making, uncovers opportunity, and improves efficiency. The modern BI landscape is an increasingly complex one, however, with trends such as big data, the cloud, and the Internet of Things dramatically changing the type, volume, and interaction of data and systems in play. Properly managing this complexity is critical, particularly with systems that constantly produce new content or serve as shared platforms for multiple projects.

During this one-hour live chat, Marco Draghi, Principal Consultant at Xoomworks, took readers’ questions on how to properly maintain and secure complex, shared landscapes by optimizing BI administration.

If you missed this live chat session or would like a refresher, we welcome you to view the online chat replay and read the full, edited transcript below.

Meet the panelist:

Marco Draghi, Principal Consultant, Xoomworks
Marco has an extensive background in business intelligence, system architectures, networking, security, virtualization, troubleshooting, sizing, scalability, tuning, and hardening, and has covered complete project life cycles on Windows, Unix, and Linux platforms. Marco is a former Business Objects and SAP consultant, and has worked on some of the largest global escalations, upgrades, and migrations.

Transcript

Melanie Obeid, SAPinsider: Hello! Welcome to today’s live Q&A on optimizing BI administration in SAP environments. I’m Melanie Obeid, Editorial Director of SAPinsider and insiderPROFILES, and I’m excited to introduce today’s panelist, Marco Draghi, Principal Consultant at Xoomworks.

Marco has an extensive background in business intelligence, system architectures, networking, security, virtualization, troubleshooting, sizing, scalability, tuning, and hardening, and has covered complete project life cycles on Windows, Unix, and Linux platforms. Marco is a former Business Objects and SAP consultant, and has worked on some of the largest global escalations, upgrades, and migrations.

Hi, Marco. Thank you so much for being here today to answer readers’ questions!

Marco Draghi, Xoomworks: Welcome all to this BI Administration Q&A live chat. I’ll try to answer all your questions in the most detailed way. I’ll also give answers to those related more to BW than BI; however, I don’t want to go off-topic regarding non-BI related questions. Thanks, all.

Comment From Venkata Sabbasani: How do you minimize refresh timelines for a large-scale BW environment?

Marco Draghi: Which aspect of system refresh are you addressing here? Frequency of system refresh, time for single system refresh, minimizing downtime during refresh, or something else? I can only say that SAP provides quite a comprehensive overview and description of different approaches (see here https://wiki.scn.sap.com/wiki/display/BI/BW%20system%20copy) as well as tools, like the PCA tool, that automate (and thus speed up) many steps during the system copy/refresh procedure.

Comment From Venkata Sabbasani: How do you speed up support pack upgrades when there are several releases lined up for the year?

Marco Draghi: Most time and effort during system updates/upgrades are spent on regression testing of the new (target) version. Therefore, the test process should be clearly defined and automated as much as possible. It should include definitions of critical functionality/processes, respective test cases, expected results and success criteria, tools and resources, etc. Also keep in mind that, in most cases, a BW system exists in complex landscapes and may have various dependencies with other systems and components. Thus, an update of a BW system may require updating at least some of these systems and components. These systems/components, in turn, may also need to be tested. Therefore, even minor BW system updates may be a very expensive, time- and effort-consuming exercise. That’s why it’s important to have a reasonable upgrade strategy (when and what should be upgraded), and this strategy should cover the complete landscape.

Comment From Venkata Sabbasani: We have large BW environment, and we are concerned about performance impacts. We are maintaining housekeeping jobs correctly, yet we are facing performance issues. How can we optimize our environment?

Marco Draghi: Ensuring good performance is a complex puzzle with many pieces, especially when taking into account that E2E processes like ETL, reporting, etc., may span across multiple systems. Housekeeping is only one of the pieces. There are many others, like system sizing, load balancing and distribution, design of your applications, continuous monitoring and optimization, etc. Analysis of performance, whether its overall system performance or performance of a particular process, requires respective knowledge, skills, and experience on how to collect relevant data about performance, interpret that data, and turn it into practical recommendations and actions.

Comment From fredo: Why is there always network and disk activity between the BI servers, even when there aren’t any active sessions?

Marco Draghi: If you’ve already analyzed network traffic and disk usage and seen that it is down to BI4 processes, then it depends if your deployment is distributed or standalone (CORBA background ‘chatting’ between CMS and all other servers always happens, as the CMS needs to know which server is alive and running); also, if you have the Platform Search and Monitoring APS, that one is always operational and impacting disk I/O.

Comment From substring: What is the best way to maintain AD users that are imported into BOE? Adding new users is easy, but removing users can be very tedious.

Marco Draghi: If access is controlled by specific groups, then users removed from these groups should be removed from the system. Don’t add your Domain Users group, for example – add a group which includes only those that still require access to the system. The only way these users would then remain is if they are manually added to an enterprise group. Control access via AD groups and remove them at the AD level in the CMC to remove their access.

Comment From AROY: What would be the recommended approach to implementing two-factor authentication with the BI 4.2 platform, i.e. BI Launchpad?

Marco Draghi: I don’t think SAP supports this with BI4 at the present time, but they have introduced some new options via Trusted Authentication – you might be able to make something work with this angle.

Comment From Srinath Varanasi: We are not able to configure Platform Search in our BO 4.1 SP3 environment, and SAP support couldn’t provide any concrete solution yet. We are on 6 clustered Windows 2012 servers, with a 10,000-person user base and around 10,000 documents.

Marco Draghi: When you say you have not managed to configure it yet, do you mean that it never actually indexes or that you have yet to manage to fully index your system? Do you create a lot of content every day? I’d recommend having a dedicated disk where you store your Index engine and also keeping an eye on these two SAP KB articles: 2069518 – “Multiple List of Values SQL queries are executed on the reporting databases”1625939 – “Ghost queries are run to the reporting database from BO server.”

Comment From robert cuerez: In the context of a multi-tenancies configuration, is it possible to dedicate a server group to a tenant for processing WebI documents in real-time or a scheduled mode (without updating the default settings of documents)?

Marco Draghi: This functionality is coming. It will start in BI4.2 SP3 but will be completed in releases down the line. I believe the initial functionality will allow it to be dedicated to a user group to begin with and will then be extended to be dedicated to folders/servers in a later SP.

Comment From TAMKO: I know one of your colleagues did a presentation at BI HANA 2016 this February on tracing the execution of different report types using HTTP trace, etc. I would appreciate if some details on this can be shared, as we had difficulty setting that up for analysis on some of our long-running queries against HANA database in a side car scenario.

Marco Draghi: Johann’s previous Q&A on performance tracing is still here: https://sapinsider.wispubs.com/Assets/Q-and-As/2014/November/QA-with-Johann-Kottas-on-BI-Reporting-Performance. The Video’s from Dan’s Las Vegas presentation here: https://youtu.be/POPRF_iILpw & https://youtu.be/MdddcMPbiUE. If you want a copy of the presentation, send us an email at: bi@xoomworks.com

Comment From Kris: I am new to BI Administration and would like to know, where do I start to learn about administration? Is there a place where I can find all tutorials or documents or videos (not the long and theoretical BI Admin guide)?

Marco Draghi: Hi Kris, I would start with https://open.sap.com/ and then actually go onto the SAP courses and certifications. It’s a pretty big job to try and learn all of it quickly, so take your time and play with it.

Comment From Anthony: We have a BO platform on top of a BW platform and want to use all the tools provided. Lumira can have all kinds of sources, one of them being flat files. If these files are stored on the BI platform, they can be rather big (as data is saved in the file). How do you manage this and avoid letting users fill your system with (non) BW data ?

Marco Draghi: Hi Anthony. This seems to be a growing problem for a lot of companies opening the platforms to self-service BI. You need to proactively monitor the user inbox sizes (impact on Input FRS). Unfortunately, you cannot limit the user’s inbox size with BI4 default features. It is possible to use a script through the BI4 SDK that runs a program object and then cleans them up, or use the 360 tool suite.

Comment From Daniel: What are your top three recommendations for the BI landscape in a greenfield implementation?

Marco Draghi: Clear and detailed customer requirements to check if SAP BI4 can align with these criteria:

– number of users (active vs. concurrent)

– type of reporting solution chosen

– type of authentication required and level of security/encryption needed to access it

– scheduling jobs/publications requirements

– which datasource will be used, its location/size, and if it’s supported in BI4

This is just scratching the surface, but there’s way more to consider than this.

Comment From David: What is the recommended way to apply permissions and delegate administration for the BI Launch Pad Folders and Categories with a variety of tenants? We have to take into account initial roles (user access rights) that an enterprise group has already set up. We have roles based upon what users can perform (reader, author, admin, analyst, and publisher). The challenge is to reduce the administrative burden for all parties (enterprise team, delegated admins for level 1 folders within the BI Launch Pad) while respecting security and ease of use.

Marco Draghi: Hi David. We have found that it tends to work best with small access levels that are cumulatively applied and built upon. So Level 1 has all the CALs it needs – and they’re only applied in there. Level 2 has the Level 1 CALs applied and the level 2 CALs as well. This allows for more flexibility but also allows you to keep track of where the rights are applied, since they will only ever be in a single CAL. Here’s a helpful link: https://www.xoomworks.com/bi/2016/01/05/sap-businessobjects-security/

Comment From AROY: Knowing that BI 4.2 SP2 was recently released, would you consider implementing this version versus BI 4.1 SP7?

Marco Draghi: It all depends which issues you want to fix, or if you want to explore the new functionality that comes with BI4.2. In both cases, it’s always advisable to do regression testing with respect to the current version you’re on. Personally, I’d go for BI4.2 SP2 as it has longer supportability, new and updated supported connectivity, and the new Tomcat 8, and also because all new BI4.2 software runs on SAP JVM 8.1.

Comment From karl: Can you explain single sign-on mechanisms in BI 4.1?

Marco Draghi: Hi Karl. Please start by viewing the Authentication chapter of the BI4.x Administration Guide, then let me know if you still need further details.

Comment From Sumnesh Nepalia: What best practices should we be following for an environment where we have a heavy load of Crystal and WebI report schedules (more than 1K every night) as report bursts for customers of client.

Marco Draghi: There’s no specific best practice for this. It all depends on the analysis that needs to be done on your process behavior/footprint when running these schedules.

I usually check which processes are used and how (level of CPU/RAM) when one single job/publication runs in order to assess it in isolation mode. Then I do a sizing exercise depending on the job parallelism I want to reach without overloading the server. It’s important to see the behavior, for instance, of the job server child creation, where you might have a CPU overload for a few seconds when kicking off 15-20 parallel jobs at the same time on the same machine, which will have an adverse, unresponsive effect on other possible BI4 processes running on the same machine. Always remember to protect the core processes (CMS/I/O FRS) from any possible overload caused by processing/job servers, i.e. running them on separate/dedicated nodes.

Comment From Sabuj H: What’s the best way to troubleshoot if even simple WebI reports (based on a small excel file) on BI LaunchPad occasionally run very slow? Are there any CMC tools that can be leveraged?

Marco Draghi: Hi Sabuj. It sounds like you’ll need to trace the workflow to understand where the time is being sent. I answered a question earlier on from TAMKO that includes some details that you might find helpful. My colleague Johann’s previous Q&A on performance tracing is still here: https://sapinsider.wispubs.com/Assets/Q-and-As/2014/November/QA-with-Johann-Kottas-on-BI-Reporting-Performance. The Video’s from Dan’s Las Vegas presentation here: https://youtu.be/POPRF_iILpw & https://youtu.be/MdddcMPbiUE. If you want a copy of the presentation, send us an email at: bi@xoomworks.com

Comment From We are currently u…: We are currently using BOB41, SP6 with Patch 03. Will we lose any fixes by moving up to BOB42, SP2?

Marco Draghi: Hello. I would suggest starting by looking at the release notes of the two versions. In theory 4.1 SP06 should have formed the basis of 4.2 SP01, but it is better to check with SAP Support. There could be any number of fixes you rely on that might not be due until 4.2 SP03.

Comment From Kirsten: Where can I find info on the best practices for setting up version control?

Marco Draghi: Hi Kirsten, I’m not sure they actually exist. If they do, you would have to tailor them to your specific requirements. Matt Shaw from SAP has a webinar series that may help you: https://www.youtube.com/watch?v=zy0XbtMxZPU

Comment From fredo: In BI monitoring, what is the interactive analysis probe checking?

Marco Draghi: I think that in BI4.1, this opens a WebI report in the background, and this should be used only to test if the WebiProc server is working fine.

Comment from Sumnesh Nepalia: Is it a good idea to distribute job servers and processing servers on different boxes and keep multiple boxes dedicated to job scheduling only?

A: Hi, Nepalia. It all depends on the amount and type of job scheduling you’re running, and above all, if this will start at the same time. For example, running 15-20 parallel WebI schedules from the same AJS will cause a very big spike in terms of CPU where the AJS runs just to create/load all 15-20 AJS child processes in memory, and if your server reaches 100% CPU for quite some time, then in that time other running BI4 processes (WebI, DSL, etc.) might be unresponsive and cause random performance failures and other adverse effects for your deployment.

Comment from Anthony: How can you manage a mixed scenario where users are able to create reports on production and IT delivers reports in a transported way?

A: Hi, Anthony. It’s not impossible, but it needs real-time monitoring + administration + RCA to avoid the possibility of end users to jeopardizing the stability and availability of the production system. Here’s a real life example: if one end user has been assigned the wrong security rights to see the entire BI user population (about 100k users) when sending a report in the BI4 Inbox, then he might become the root cause of overloading the system and filling up the Input FRS where the inboxes are residing. So it’s important to monitor and catch any glitch or spike at a process level and/or entire server level and folder size to reduce the risk of outages caused by end users.

Comment from David: What is the recommended way to apply permissions and delegate administration for the BI Launch Pad Folders and Categories with a variety of tenants? We have to take into account an enterprise group that has setup initial roles (user access rights). We have roles based upon what users can perform (reader, author, admin, analyst, and publisher). The challenge is to reduce the administrative burden for all parties (enterprise team, delegated admin for level 1 folders within the BI Launch Pad) while respecting security and ease of use.

A: This question does require additional context.

In a shared environment with different business solutions, we recommend the following approach:

Have a common set of Custom Access Levels and assign rights in small building blocks, dividing content and application rights.
Each right will only ever reside in one CAL (with inheritance in mind). Do not use Deny rights (set to unspecified instead).
The CALs will tie into your user profiles with logical paths
Info Consumer Basic (Reader)
Info Consumer Advanced (additional functionality)
Info Worker Basic (Analyst)
Info Worker Advanced (Developer / Designer / Edit capabilities)
Admin Tier (Schedule Admin / Category Admin / Content & Security Admin / Support)
The Security and Content Admin for each business solution will be able to perform delegated tasks but won’t be able to apply granular rights or change the CAL structure.
The View will be kept in a separate CAL

Your users will get functional rights (content / application) based on their profile and receive the view from the group membership that ties into the folder structure. There is sufficient flexibility for each business to decide which blocks to apply; however, you stay in control from a best practice and licensing perspective. The delegated admin will be able to control their own business area.

Comment from robert cuerez: In a context of multi tenancies configuration, is it possible to dedicate a server group to a tenant for WebI documents processing with any execution mode (real time or scheduled) without enforcing the server group in the default settings of the documents?

A: Hi, Robert. This feature will start coming in SAP BI 4.2 SP3 and onwards. As of today it’s not possible to define/dedicate the usage of specific processing servers to a specific set of users/folders/reports.

Comment from Parm: What are the best practices for creating same-deployment scripts and customizations, like settings up request ports or web-apps? Are there any tools provided by SAP to deploy code on each server type, like BI enterprise servers, BI web servers, BI monitoring servers, etc.?

A: Hi, Parm. If you know how to use the BI4 SDK, then you might be able to create some of these automated scripts. If the already default response files are not enough to reach your goal, I personally create only my custom scripts for web-app servers, which will become useful when patching/upgrading because not all BI4 web-apps have .properties files, and they’re reset to their default value in HTTP if, for instance, you’re using HTTPS instead.

I also prefer to do the first manual setup and then maybe replicate/clone its setting on another SIA, just to make sure that new possible BI4.x code versions won’t have adverse effects when running ‘old’ or not up-to-date scripts.

Comment from Samuel Levesque: What is you preferred approach for customers who are looking to migrate their BW data to the HANA platform? Do you recommend a progressive approach by keeping both traditional and in-memory DB and migrating some of the most demanding data to the in-memory one? Or instead, do you suggest performing a complete migration to the in-memory database?

A: As always, there is no simple and straightforward answer, and it all depends on your current situation, objectives, constraints, and many other factors and considerations.

In early days of BW on HANA, running two BW systems in parallel could be a recommended approach to reduce the risk of migration and/or minimize downtime. But nowadays, migration tools and procedures are fairly mature and reliable. They allow performing in-place migration and all required pre- and post-migration tasks with minimal risk, effort, and downtime.

At the same time, running two BW systems in parallel itself may be not an easy task. Often, many BW applications share the same data, meaning that it needs to be loaded and processed in both systems in parallel and probably kept in sync. Additional complexity can be introduced by planning applications where data may be created or changed manually and then used by multiple applications hosted in different systems.

So from this perspective, complete migration of existing BW systems looks to be the more straightforward and reliable approach. But, of course, if you have fully isolated applications that share nothing with other and have clear benefits from running in BW on HANA, then moving such applications into HANA alone could also make sense.

Comment from Samuel Levesque: Are there any known constraints or issues with deploying SAP BI 4.2 on Unix rather than Windows?

A: Hi, Samuel. In order to decide which OS to use to deploy BI4.2, consider these factors:

– As BI4 software is coded on Windows and then compiled for all Unix flavors, you might have bugs which are Unix-specific but not present on Windows installs.

– Do you need to integrate BI4 with other Microsoft software, like Sharepoint, or have a .NET deployment (Kerberos AD SSO can be implemented on Unix as well)?

– Do you have Unix support staff that knows the application architecture and their dependencies to avoid future supportability issues?

– What are your requirements in terms of system security/hardening of the application, as this might be the variable which might push for a Unix OS?

– If you want to have the top of the application performances comparing the same hardware, then I believe Unix is quicker due to its OS process management and file system nature.

Comment from Pierre: We have BW cubes in an APO environment, and we have activated some aggregates in order to speed up some queries. When loading new data to the cubes, the aggregates are recalculated after the data load. When we need to change some characteristics, aggregates need to be deactivated and reactivated after the change (realignment). Is there a way to perform work like what’s done during data loading by recalculating the aggregates without deactivation/activation?

A: Hi, Pierre. Unfortunately I am not familiar with Advanced Planner and Optimizer and cannot fully comment on this question. In terms of SAP BW data loading and aggregates being recalculated, there are some useful docs on SCN.

https://help.sap.com/saphelp_nw70/helpdata/en/c5/40813b680c250fe10000000a114084/content.htm

Comment from David: How do people use Categories to apply security?

A: Hi, David. The security concept is the same. The only difference is that an object in a folder is unique (1 CUID) but that same 1 object can live in multiple Categories.

Comment from BobbyS: Hello. Currently, we are on BO 4.1 SP7. If we have never started indexing after upgrading to SP7 from SP5, what are some of the known challenges to making Platform Search work efficient? Do you have any recommendations for doing so? Please keep in mind that Platform Search was working fine in SP5, but not anymore. Thanks in advance.

A: Hi, Bobby. I personally haven’t tested the difference between SP5 or SP7 and related bugs in between, but I’d suggest checking in the SP7 fixed issues for anything related to Platform search. Then you can trace what’s wrong on the existing index DB or try to rebuild the index in case of error (setting on CMC/Applications/Platform Search), or you can completely reinitialize it by stopping the Platform Search, moving away the current indexing folder, and restarting the Platform Search APS, which will start from scratch. Remember that indexing is still quite a heavy task on the CMS process, and it’ll fire tons of queries to the CMS DB to reindex all content in your system.

I’d also suggest reviewing what’s not needed in your indexing and removing it (ex. for universes: Sap KB 2069518 – Multiple List of Values SQL queries are executed on the reporting databases). If your system is generating more content every day than it can index, then you need to tune your CMS DB to have a quicker response from the Platform Search. It’ll have a better overall performance effect on the system (ex. recreating the DB indexes of a CMS DB running on Oracle makes a huge difference).

Comment from Mareswar Porandla: Hi, Marco. This is regarding the query raised on Platform Search by Srinath Varanasi. We have a separate NAS shared path configured for storing indexes. We have scheduled platform search, but sometimes it indexes documents, sometimes there is no increase in the indexed documents, and sometimes there is a decrease of the amount of indexed documents. We do not create lot of content every day. We don’t see any locks in cms_locks7 table but Platform Search is still not working properly, SAP support suggested running reposcan, which is having issues in BO 4.1 SP3, and suggested going for BO 4.1 SP3 Patch1 or SP7 Patch3.

A: Hi, Porandla. It’s not clear what exactly your issue is, but before doing any upgrade, I’d suggest tracing down the real root cause before making any decision. You can trace what’s wrong on the existing index DB or try to rebuild the index in case of error (setting on CMC/Applications/Platform Search), or you can completely reinitialize it by stopping the Platform Search, moving away the current indexing folder, and restarting the Platform Search APS, which will start from scratch.

Could it be possible that the window selected to run the search indexing is too small? Remember that indexing is still quite a heavy task on the CMS process, and it’ll fire tons of queries to the CMS DB to index all content in your system. I’d also suggest reviewing what’s not needed in your indexing and removing it (ex. for universes: Sap KB 2069518 – Multiple List of Values SQL queries are executed on the reporting databases). If your system is generating more content everyday than it can index it in that timeframe, then you need to tune your CMS DB to have quicker response from the Platform Search. It’ll have a better performance effect overall on the system (ex. recreate the DB indexes of a CMS DB running on Oracle makes a huge difference); also, you might need to increase the scheduling window that you have now.

Comment from Barry: We’re experiencing a number of initialization failures when using an SDK to refresh and export WebI reports in 4.1 SP7. It seems to be linked to a ‘failure to get prompts’. We were looking at a possible solution to disable the ‘Enable Real-time Cache’ setting in the WebIProcServer properties. How useful is this caching option, and has it been linked to any performance/stability problems?

A: Hi, Barry. I’m not sure how you can link a failure to get prompts to the caching mechanism; I’d suggest first doing a tracing exercise and defining if there’s an actual link between the two by looking at the mechanism behind the scenes when running your reports. Caching is always a good feature in general if it is configured correctly on a dedicated file system, which is different from the WebI installation disk.

Comment from Krish: Hi, Marco. Is there a YouTube link for BI Administration?

A: Hi, Krish – if you search on Google “SAP BO 4.1 Administration Tutorials – Part,” you’ll find a bunch of them.

Melanie Obeid: As we come to the end of the Q&A, I’d like to thank you all again for joining us. And a special thank you to Marco Draghi for being here today and for all your informative answers!

Marco Draghi: Thanks to all who have joined this live chat. I’ll make sure to also answer all the remaining questions.

We would like to invite you to visit us at our booth, Nr. 205, at BI2016 in Vienna, June 20-22. For more details and for the registration link, please visit the events page on our website: xoomworks.com/bi.As a thank you for being our guests at this Q&A session, we want to invite you to be the first to participate in our 2016 Business Intelligence Survey – simply go to https://www.xoomworks.com/bi/business-intelligence-survey/ and click the “Start Survey” button. And finally, thank you for attending today’s SAPinsider Q&A session. If you have any further questions, please don’t hesitate and get in touch with bi@xoomworks.com. For more information on our services, go to www.xoomworks.com/bi.