1 2 3 21 Previous Next

BI Platform

312 Posts

On February 25, 2015, Onapsis released advisories for five SAP BusinessObjects Enterprise/Edge and SAP HANA vulnerabilities.  These vulnerabilities
were responsibly disclosed, allowing SAP to correct the vulnerabilities as quickly as possible.

 

Here is a summary of the advisories and more information around each. Of these five, three are considered "High Risk" and are exploited through the CORBA layer.

 

Vulnerabilities rated High:

 

Unauthorized Audit Information Delete via CORBA (CVE-2015-2075)

 

Exploiting this vulnerability would allow a remote unauthenticated attacker to delete audit information on the BI system before these events are written into the auditing database.

 

Resolution:
Details of the fix are available in SAP Note ID 2011396.  Please update your BusinessObjects BI 4.x  system to one of the following patches, or a subsequent patch or support pack:

  • BI 4.0 Patch 9.2
  • BI 4.0 SP10
  • BI 4.1 Patch 3.1
  • BI 4.1 SP04


SAP Note ID link: http://service.sap.com/sap/support/notes/2011396

 

Unauthorized File Repository Server Write via CORBA (CVE-2015-2074)

 

Exploiting this vulnerability would allow a remote unauthenticated attacker to overwrite files in the File Repository System (FRS), provided the attacker has knowledge of the report ID and path.  For example, “frs://Input/a_103/019/000/4967/1b14796c5b0d5f2c.rpt”.

 

Resolution:
Details of the fix are available in SAP Note ID 2018681.  Please update your BusinessObjects BI 4.x  system to the following support pack, or a subsequent patch or support pack:

  • BI 4.1 SP04

Note: Earlier versions of BI 4.x have a workaround, which is to configure the FRS to run in FIPS mode (add “-fips” to the command line arguments in the CMC) or enable CORBA SSL.

SAP Note ID link: https://service.sap.com/sap/support/notes/2018681


Unauthorized File Repository Server (FRS) Read via CORBA (CVE-2015-2073)


Exploiting this vulnerability would allow a remote unauthenticated attacker to be able to retrieve reports located on the FRS system, provided the attacker has knowledge of the report ID and path.  For example, “frs://Input/a_103/019/000/4967/1b14796c5b0d5f2c.rpt”.

 

Resolution:  Details of the fix are available in SAP Note ID 2018682.  Please update your BusinessObjects BI 4.x  system to the following support pack, or a subsequent patches or support packs:

  • BI 4.1 SP04

Note: Earlier versions of BI 4.x have a workaround, which is to configure the FRS to run in FIPS mode (add “-fips” to the command line arguments in the CMC) or enable CORBA SSL.


SAP Note ID Link: https://service.sap.com/sap/support/notes/2018682

 

Vulnerabilities rated Medium:

 

Multiple Cross Site Scripting Vulnerabilities in SAP HANA XS Administration Tool


Reflected cross site scripting vulnerabilities in this tool may allow an attacker to deface the application or harvest authentication information from users.


Resolution:  Details of the fix are available in SAP Note ID 1993349.  Please update your SAP HANA system to one of the following patches, or a later revision:

  • SAP HANA revision 72 (for SPS07)
  • SAP HANA revision 69 Patch 4 (for SPS06)


SAP Note ID Link:
https://service.sap.com/sap/support/notes/1993349


Unauthorized Audit Information Access via CORBA (CVE-2015-2076)


Exploiting this vulnerability would allow a remote unauthenticated user to gain access to audit events in a BI system.


Resolution:  Details of the fix are available in SAP Note ID 2011395.  Please update your BusinessObjects BI 4.x  system to one of the following patches, or a subsequent patch or support pack:

 

  • BI 4.0 Patch 9.2
  • BI 4.0 SP10
  • BI 4.1 Patch 3.1
  • BI 4.1 SP04


SAP Note ID Link: https://service.sap.com/sap/support/notes/2011395


I strongly recommend keeping up to date on patches and support packs in order to take advantage of the most recent security fixes, but also new features in the product. Each of the vulnerabilities affecting the BI Platform have been resolved in BI 4.1 SP04+. If you haven’t already, this is a good opportunity to build the business case for updating your environment. Vulnerabilities left unaddressed put your business users and data at risk.


Information regarding each of the BI support packs/patches, including Administration guides, release notes, fixed issues in each and known issues in each can be found at http://help.sap.com/bobi/.


Information regarding the latest revision of SAP HANA, including install guides, security information and Administration guides can be found at http://help.sap.com/hana, and choose the HANA link appropriate for your environment.


SAP’s security notes portal can be found here: https://support.sap.com/securitynotes

Other links of interest:


I am a new blogger to SCN, but I’ve been with Business Objects and then SAP for several years.   I’m interested in bringing more transparency around security topics to SCN, so I’m curious to know what the BI Platform community thinks about these types of posts, as well as anything else you’d like to see.


Please feel free to leave a comment below or contact me directly, I’d love to hear from you!

This was an ASUG webcast this past week given by SAP's Thomas Kuruvilla

 

The usual disclaimer applies that things in the future are subject to change.

1fig.png

Figure 1 – Source: SAP

 

Figure 1 provides in introduction to SAP Lumira, Edge.

2fig.png

Figure 2: Source SAP

 

The groups created, shown above in Figure 2, are more for distribution lists

3fig.png

Figure 3: Source SAP

 

Figure 3 shows data acquisition and mashup is in Lumira Desktop; SAP is looking to bring it to the browser to do full workflow in browser

4fi.png

Figure 4: Source: SAP

 

With Lumira Edge, SAP does not want to add software or hardware to the deployment

 

SAP plans to support additional languages in coming releases

5fig.png

Figure 5: Source: SAP

 

The installation is in “three clicks”, including accepting the license

 

You can still create in Lumira Desktop 1.23 but it will not open document in browser

 

The size 699MB of the installation file.

 

Create users using their e-mail ID; similar to Lumira Cloud.

 

Roadmap

6fig.png

Figure 6: Source: SAP

 

Figure 6 is the roadmap it shows what is coming in the first half.  Second half is still in planning.  Next release is April and June.

 

Coming is the support for refreshing additional data – 1.25

 

Universe refresh in the team server (in case you do not want to use BI Platform) – you connect using the extension framework (planned for 1.24 release).

 

In 1.25, plan to have save as for personal use.

 

In coming release, will provide a story viewer, similar to Lumira Cloud

 

Only go to visualize/compose room if have edit rights – next release

 

Next release will included active directory (planned)

 

In June timeframe will provide Mobile BI support (iPad only, June timeline)

 

They will not constrain any upgrade release without intermediate updates

 

They plan to have auto fill functionality to remember e-mail ids; you start typing a name and it auto completes.  The sharing becomes easier

 

Today – can’t share to group; coming release share to groups and large number of users in one workflow

 

Lumira server for BI Platform is coming in Q2

 

April 1.25 – server for teams, server for BI platform and teams at the same time

 

Q&A Session for SAP Lumira Server for Teams: Deep Dive and Roadmap

 

Q: Is this running on a proprietary SAP WACS?  Does the portal run on other Web App servers?

A: WACS is bundled with the installer, doesn’t support deployment on other Web apps as this would be too technical for Business user

________________________________________________________________

Q: Was the browser refresh by the user leveraging a DSN defined on the server or on the client?

A: The connection defined in client for a Lumira Document is saved to the server along with the Lumira Document

________________________________________________________________

Q: Can I distribute the story boards on a predefined interval automatically?

A: Scheduling is planned for future release

________________________________________________________________

Q: Is Team Server compatible with 1.23, now available.

A: Hi Josh - he addressed this - you can create the document in 1.23 but not open in browser

________________________________________________________________

Q: Win 8.1 not touch enabled, does that mean it excludes MS surface?

A: Yes, touch is not enabled.

________________________________________________________________

Q: Is this included with the BI Suite license from SAP?

A: Lumira Server for Teams (Edge Edition) is not covered under BI Suite License. However, Lumira Server for BI Platform (RTC Planned in April) is covered under BI Suite licenses

________________________________________________________________

Q: browser needs to be IE 11 only? Not below IE versions

A: Yes, we only support IE11 with the existing release. Plan to support IE 10 with Q2 release‑

________________________________________________________________

Q: Inclusion with BI Suite would be very nice, as many LOB team want autonomy from central managed BI Platform.

A: Lumira Server for Teams (edge Edition) is not included but Lumira Server for BI Platform (RTC in April) is included under BI Suite‑

________________________________________________________________

Q: For Universe Support via DA Extension... is the expectation that Customers build these Extensions themselves, or will SAP be providing such an Extension?

A: SAP would be providing extensions for Universe. Universe support via DA extension is planned with Q2 release‑

________________________________________________________________

Q: When will support for BW BEx data source be available?

A: Currently planned to be supported with June release‑

________________________________________________________________

Q: Will we need to upgrade our BI Platform to add Lumira, or will it be an add-on like for Design Studio?

A: It will be an Add-On like Design Studio. Supported from BI 4.1 SP03 onwards (may need latest patch) ‑

________________________________________________________________

 

Q: does that mean, we don’t need to rely Hana server when server for BI is available right?

A: Ramp-up - today Lumira Server relies on HANA - feedback is need something easy to maintain - new solution not require HANA‑

________________________________________________________________

 

Q: Does Lumira Edge have any additional functionality that Lumira Server for BI Platform will not have?

A: Game is to keep at the same level; may see certain scenarios where BIP may have functionality earlier - BIP won't have less than team. Admin functionality is different for both solutions‑

A: Scheduling will come to BIP first‑

________________________________________________________________

Q: What about the BW platform?

A: 7.x and higher‑

________________________________________________________________

 

Q: When we say BI platform, you mean BEX queries, or directly the OLAP cubes

A: BI platform is the BOE‑

________________________________________________________________

Q: What BW level is required?

A: BW7x as a data source‑

A: 7.x and higher‑

________________________________________________________________

 

References:

ASUG Annual Conference Pre-conference: Register here:  - featuring Hands-on SAP BusinessObjects BI 4.1 w/ SAP NetWeaver BW Powered by SAP HANA – Deep Dive includes SAP Lumira, Design Studio, and Analysis

Hi All,

 

Can someone point me to the above patch doc?

 

I see patch 3 was release on 2/27/2015 but cannot find the document listing the fixes included:

 

http://service.sap.com/sap/support/notes/2136486

 

Thanks,

Mike.

Hi,

 

On this blog, I’ll explain the step by step of how to configure the Windows AD authentication when BO is installed on a Unix server.

 

This how-to was done with this environment:

  • SO: AIX version 6.1, TL 9
  • BO: 4.1 SP4 Patch 3

 

These steps were done following the steps described on SAP Note  1245218 - How to connect the LDAP plugin to Active Directory

 

The “Distinguished Name”

 

When we are configuring Windows AD authentication in one BOE Unix Environment, there is parameter that we need to insert called “Distinguished Name”. This information is not easy to find when we don’t have access to the Active Directory server for example. To find this information, we used one tool Active Directory Explorer that will show for us what is the Distinguished Name of the user that we need. Below, I will show how to find this parameter and apply in the AD authentication configurations on BO CMC.

 

Attention: the Distinguished Name of the user is not the user itself


To download the Active Directory Explorer: https://technet.microsoft.com/en-us/library/bb963907.aspx

 

After download the AD Explorer, it’s necessary to logon on the AD server with an allowed user:

1.png

 

After that, we should do a search for the user that we need the distinguished name using the parameter sAMAccountName. After we added the Search Criteria sAMAccountName is <user name>, we do a double-click on the search results below:

2.JPG

 

After a double click, you can see selected the Distinguished Name of our needed user, this is what we need to insert on BO AD authentication configuration on CMC:

3.JPG

 

 

The LDAP Configuration in CMC:

 

To use the AD authentication in Unix, we will need to use the LDAP plugin selecting on the configurations that it will be AD based

 

Below are the configurations that we need on LDAP Authentication plugin config screen through CMC:

 

Select LDAP

4.png

 

Click on Start Configuration Wizard

5.png

 

 

Inform all your AD servers that you would like to able users to be authenticated

6.JPG

 

Select Microsoft Active Directory Application on LDAP Server type parameter and then click on Show Attribute Mappings

7.png 

On Attribute Mappings, inform these parameters:

8.png

 

After that, inform you Base LDAP Distinguished name, what usually is the FQDN of server domain on “DC” tags

9.JPG

 

And then, the Distinguished Name that we found using the AD Explorer tool:

10.png

 

After, click on Next

11.png

 

click on Next

12.png

 

click on Next

13.png

 

And then, Finish

14.png

 

After that, the AD authentication configuration is done and the users will be able to logon using their AD users in an UNIX environment.

15.JPG

Though the option to retry failed instances of a publication has been around for sometime now, there are still some confusions around this option.

 

If you right click on any of the failed instances of a Publication, you will find three options

  1. Run Now
  2. Reschedule
  3. Retry

 

While other options are well documented, "retry" is not still very clear

 

Retry synopsis:

 

  1. Overwrites the "failed" instance (run now and reschedule will create new instances, but retry will use the failed instance itself)
  2. In case of partial failure – retry option will process only the failed recipients.
  3. In case of complete failure – the full job runs and is same as run now option- except for the fact that a new instance is NOT created when we retry.
  4. In case if the server stops abruptly (example, you try to force restart SIA or the full box), the progress is not saved and so when the server comes up again, the instance that was running while the server was shutdown will be restarted from the beginning.
  5. Auto-Retry
    1. We can automate it using the “number of retries allowed” under the “recurrence” property of the publication.
    2. In case of a failure, it will wait for the specified duration and then will attempt to run the publication again.
  6. SAP note:
    1. https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F64653D3030312669765F7361706E6F7465735F6E756D6265723D3139353137313026

 

How can you test this?

 

If you want to replicate the partial failure scenario, you can follow the below steps.


Publication Properties

  • Source Documents: 16 Crystal reports(simple ones) - Just to make sure that we have enough time to stop the publication in the middle.
  • Dynamic Recipients: 24 recipients (web-I) - you can also use an excel file to build the Dynamic recipient report for testing.
  • Format: PDF
  • Destination: Email
  • Merged PDF: Yes
  • Personalization: Enabled


Steps:

  • Start the publication(preferably in the test mode – end users will not be annoyed)
  • After receiving few emails bring down the file repository services
  • Publication instance will go to failed state
  • Move the received files to a new outlook folder (optional – to make it easier)
  • Bring up the file repo services again. Wait for couple of minutes after this is up.
  • Right click on the failed instance and click on Retry
  • The job will continue from the point it failed and the status will change to “Running”.
  • Wait till the status becomes “success” and then check the emails received.


Screenshots

 

  • The list of documents

2.png

  • Dynamic recipient web-intelligence report

3.png

  • Emails received before stopping the repository services

4.png

  • Select the services and click on stop. (this is replicate the partial failure scenario)

5.png

  • Instance fails and the below message is displayed

6.png

  • Move received emails to a new folder (optional – to makes things easier)

7.png

  • Start file repository services using CMC/CCM

8.png

  • Once the services are up, right click on the failed instance and click on “Retry”

9.png

  • Wait till the instance status becomes “Success”

10.png

  • Now you will see that the platform processed only 16 recipients (those who did not get the email during the initial run). Hence all 24 recipients are processed and there are no duplicate emails.

11.png

  • Auto retry Option:

1.png

Hello everyone,

I'm new to blogging on SCN but I have been a Support Engineer for many years supporting several components in the BI Platform.  Currently I am part of the WebI team.


Some of the hardest issues to troubleshoot are those intermittent issues that seem to occur with no pattern.  We need to examine logs to see what happened when the failure occurred but how do you capture relevant logs if you can't predict when it will happen?

 

With the introduction of the End to End trace utility, we were able to get specific logs for a specific workflow.  This has been a huge timesaver when collecting logs for a workflow that was easily reproducible.  But what about those other issues - in particular schedules that fail intermittently?

 

I have recently learned that you can use End to End trace to gather traces for schedules also.

 

If you are "lucky" enough to have a schedule that always fails, you can use End to End trace while doing a "Schedule Now".  Most likely, however, you will have a daily schedule that fails once a week or so with no apparent pattern.  How do you trace just this schedule?

 

While it is not possible to trace only the failures, you can set the End to End trace on a specific Recurring schedule.

 

WARNING:  Please note that turning on this trace may cause unwanted performance hits and disk space usage.  Use with caution.

 

In this example, I have two Web Intelligence (WebI) reports:


2WebiReportsCapture.PNG

Report AAAAA is scheduled to run every 5 minutes.

Report BBBBB is scheduled to run every 15 minutes.

 

At this point, if you are not familiar with End to End trace, you may want to visit SAP KBase 1861180 or the Remote Supportability blog that introduces the tool.  I prepared the system by editing the BO_Trace.ini setting append to false and the keep_num to 50.

 

I only want to trace BBBBB's schedule so I do the following steps:

 

Close all browsers

  1. Start the SAP Client Plug-in (End to End trace utility)
  2. Click on Launch to open Internet Explorer
  3. Give the Business Transaction Name a meaningful name and set the TraceLevel to High

SAPClientPlugIn.png


Now, before clicking on Start Transaction, do the following steps:

  1. Log into CMC
  2. Navigate to the Recurring Schedule
  3. Pause the Recurring
  4. Right Click on the Paused Recurring and Select
    Reschedule

Reschedule.PNG


5. Rename the Instance Title to something easily recognizable

RenameInstanceTitle.PNG

6. Choose Create new schedule from existing schedule

CreateNewScheduleFromExistingCapture.PNG

7. Click Start Transaction in End to End Trace utility

8  Click on Schedule to finish creating the Recurring

You should immediately see the Sent bytes and Received Bytes increasing in the End to End Utility as the CMS should be actively logging the creation of the new recurring.

9. After a few minutes, click Stop Transaction in the trace utility.  (****Note: This does not turn off the tracing for the recurring****)

At this point, the BBBBB report has two recurring schedules:  The old one is paused and the new one is active:

BBBBBHistoryAndTraceUtilCapture.PNG


If we check the properties in QueryBuilder,  there is a property SI_TRACELOG_CONTEXT that is different in the new recurring (after the End to End trace was activated)

I ran the following query in QueryBuilder to return the encrypted properties stored in the CMS database.

6968 is the object ID (SI_ID)  of the BBBBB report.  The recurrings are children of the parent report.

 

select SI_ID, SI_NAME, SI_TRACELOG_CONTEXT from CI_INFOOBJECTS where SI_PARENTID = 6968 and
SI_RECURRING = 1

QueryBuilder.png

QueryBuilderResults.png

 

In the BusinessTransaction.xml created from the End to End trace, the ID is 0050560100EB1EE4ABCA32A4509F8648

 

BusinessTransactionXML_ID.PNG


In the SI_TRACELOG_CONTEXT property of the BBBBB-EtoETrace, we see that this ID is embedded into the passport value.  This means anytime that this instance runs, it will turn on End to End trace.  So even though we stopped the trace in the utility, the End to End trace will start up again when the instance runs!

 

{tick=26;depth=2;root={name={component="CMC";method="WebApp";};id={host="BIPW08R2";pid=1180;tid=89;data_id=3356;step_id=1;};};caller={name={component="BIPSDK";method="InfoStore:schedule";};id={host="BIPW08R2";pid=1180;tid=89;data_id=3356;step_id=11;};};callee={name={component="cms_BIPW08R2.CentralManagementServer";method="commitEx4";};id={host="localhost";pid=4104;tid=8416;data_id=15958;step_id=1;};};vars=[{key="ActionID";value="ClU0nNrLbUO4j6T0giTH4Mgd1a";}];settings=[];passport="2A54482A03010D9F0D5341505F4532455F54415F506C7567496E2020202020

2020202020202020202000005341505F4532455F54415F5573657220202020202020202020

202020202020205341505F4532455F54415F52657175657374202020202020202020202020

20202020202020202020000553424F5020454E54455250524953455C42495057303852325F

363430302D636D303035303536303130304542314545344142434133343233354544464136

343820202000070050560100EB1EE4ABCA32A4509F86480050560100EB1EE4ABCA34356F29

464800000000000100E22A54482A01002701000200030002000104000858000200020400083

20002000302000B000000002A54482A";}

 

After I have paused the BBBBB-EToETrace recurring and resumed the original BBBBB recurring, the history page looks like this:


BBBBBHistoryUnalteredCapture.PNG

 

Meanwhile, schedule AAAAA has continued to run every 5 minutes.  We don't want all those traces in the logs!AAAAAHistoryCapture.PNG

 

So now we collect all the logs and check that only the BBBBB-EToETrace schedule traced….

 

To simplify, I’ll just look for START INCOMING CALL Incoming:processDPCommandsEx in the WebiLogs which gets generated when the webi report refreshes.

 

GLFViewerFiltered.png

These “Information” traces occurred at 14:44, 14:51, and 15:06.

If you look at the BBBBB History page, you see that three instances were traced.

BBBBBHistory.png

In this example, I don't have a failure so I do not need to analyze the logs. For more information on analyzing End to End trace files see Ted's blog on identifying root cause.

How to turn off the trace?    The safest way is to delete the recurring BBBBB-EToETrace.


When the SI_TRACELOG_CONTEXT property contains the TransactionID from the BusinessTransaction.xml created by the End to End trace, that schedule will continue to turn on End to End trace anytime it is run.  If that recurring schedule is migrated to a new system, it could also turn on an unwanted End to End trace there as well.  This could potentially cause a lot of mysterious and unwanted logging.

 

In my next blog, I'll investigate how End to End trace can be use with recurring Publication schedules.

Overview:

Monitoring is an out of the box solution in BI 4.x, to display the live server metrics exposed via BOE SDK on CMC. ‘Monitoring Service’ (part of APS container) captures the monitoring data and passes it on to the Monitoring Application within CMC. Monitoring application extends the functionality of default server metrics to configure watches, custom metrics, alerts, KPIs and probes.

Server metrics are collected for individual Process IDs (PIDs) of each BOE service type. Essentially the metrics visible in ‘Servers’ menu of CMC -->Service Categories --> Right click on a <server name> --> select ‘Metrics’, is same as what is visible in ‘Monitoring’ menu --> Metrics --> Servers --> Expand a specific server. Example screenshots given below:
ServerMetrics_Servers.JPG
ServerMetrics_Monitoring.JPG
Monitoring or Trending database comes into play, if the option is selected on a specific watch to ‘write to trending database’. Unless the trending database is used, historical trend of monitoring data will not be available.
WatchEdit_Settings.JPG
Monitoring data is relevant from an administration perspective to keep a track on the health of the BOE system and get automated alerts when the configured caution or danger threshold is breached. Reporting can be done on the Monitoring database using the default ‘Monitoring TrendData Universe.unv’ universe provided with BI 4.x installation or a custom universe can be built.
The starting points of understanding how monitoring works and how it is configured, refer to the relevant chapter in the BI Platform admin guide, downloadable at: http://help.sap.com/boall_en/. E.g. In ‘sbo41sp3_bip_admin_en.pdf’, chapters 20, 31 and 34 talk about monitoring and metrics. There are also several insightful blog posts on monitoring e.g. by 'Manikandan Elumalai' and ‘Toby Johnston’ on SCN. Any SQL examples shown in this blog post are based on trending database hosted in Apache Derby. However, the same can easily be adapted to any other query language syntax, as the table structures remain same.

Choice of Monitoring (Trending) database:

Two choices are offered in terms of monitoring database in BI 4.x:
  • Using the embedded java database requiring minimal administration: Apache Derby (installed along with BI 4.x)
  • Re-using the Audit data store for storing monitoring data

These options can be set in the properties of ‘Monitoring Application’ in the ‘Application’ menu of CMC. If the retention duration of monitoring data is few hours or until it reaches few GBs of file space, it is best to use Apache Derby. For longer retention and handling large volume of data, using audit data store is advisable. The default ‘Monitoring TrendData Universe.unv’ is based on trending database hosted in Derby. Steps for migrating from Derby to Audit Data Store are described in BI Platform Admin guide.

Connecting to Monitoring database (Apache Derby) with SQuirrel Client

The best way to analyze monitoring database hosted in Apache Derby, is to use a GUI based database client like SQuirrel. Derby natively provides command line sql client tool: ‘ij’. Steps for installing SQuirrel and Derby client is described in:

For connecting SQuirrel client with Monitoring database in Derby, following should be used for defining the alias:
Driver: Apache Embedded
URL: jdbc:derby:\\<FQDN for the remote server>\TrendingDB\Derby;create=false
                     
Blue Underline Font: Alias URL (Path) for the Monitoring Database
**Note:
  • Trending DB is installed in BI 4.x in the following location:
         <drive>:\<Parent directory of BI 4.x>\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB/Derby
         **Derby: Name of the Monitoring / Trending Database)
  • To shorten the path for defining Alias URL in SQuirrel, the path ‘<drive>:\<Install path of BI 4.x>\SAP BusinessObjects Enterprise  XI 4.0/Data/TrendingDB’ can be shared with the network user who will be accessing it remotely via SQuirrel client.
  • The path ‘<drive>:\<Install path of BI 4.x>\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB’ also contains DDL for table creation for other database platforms like Oracle, SQL Server, DB2 etc.

Monitoring Data Model

The table names vary if the trending database is implemented in Derby vs. Audit data store. However the table structures are identical. Refer screenshots
below:

Monitoring Data Model in Apache Derby

MonitoringDataModel_Derby.jpg


Description of tables in Monitoring Database

Table NameDescription
TREND_DETAILSThe table records
information about metrics, probes and managed entities
TREND_DATAThe table records
information on the metric values, timestamp (epoch time in milliseconds) when data was collected and error message key
MANAGED_ENTITY_STATUS_DETAILSThis table contains information of configured thresholds (caution & danger) - subscription
breaches and alerts. Subscription check timestamp (epoch time in milliseconds) is also stored
MANAGED_ENTITY_STATUS_METRICSThis is a lookup table for watches

Monitoring Data Model in Audit Data Store

MonitoringDataModel_ADS.jpg

Data Dictionary for Monitoring Database

 

 

For analyzing data dictionary in SQuirrel client, the create table scripts can be generated along with all constraints / indexes:

 

 

Generate_DDL_Derby.JPG

 

Refer to the attached file 'create_table_trendingdb_derby.sql' for the generated DDL.

 

 

Alternatively following queries can be used to extract the data dictionary:

 

select t.TABLENAME, t.TABLETYPE, s.SCHEMANAME 
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where t.schemaid = s.schemaid
and s.schemaname='APP';







----
select t.TABLENAME, c.CONSTRAINTNAME, c.TYPE, s.SCHEMANAME
from SYS.SYSCONSTRAINTS c, SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where c.schemaid = s.schemaid
and c.tableid = t.tableid
and s.schemaname='APP';








---
select s.SCHEMANAME, t.TABLENAME, g.conglomeratename, g.isindex, g.isconstraint
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s, SYS.SYSCONGLOMERATES g
where g.schemaid = s.schemaid
and g.tableid = t.tableid
and s.schemaname='APP'
and (g.isindex = 'true' or g.isconstraint='true')
order by t.TABLENAME;








---

 

**Note: Default row limit in SQuirrel client is 100. This limit is configurable or the setting can be turned off altogether (no limits). The setting is present in the
SQuirrel client on the SQL tab towards top right.

 

 

Rowlimit_SQuirrel.jpg

 

 

A clear trend which comes up based on the output of the above queries / script:

 

  • Only tables, indexes and constraints are present in monitoring database. No views, procedures, materialized views etc. exists
  • Auto-generated sequence keys are used as Primary Keys for all the four tables
  • Enforced referential integrity i.e. PK-FK relationship exists between
    • TREND_DETAILS (PK) and MANAGED_ENTITY_STATUS_DETAILS (FK)
    • TREND_DETAILS (PK) and TREND_DATA (FK)
  • Index type is either unique or non-unique
  • Timestamp is stored in BIGINT format (epoch time) in TREND_DATA and MANAGED_ENTITY_STATUS_DETAILS table

 

 

 

Building Monitoring Report Queries

 

 

Some common monitoring reporting scenarios are listed below:

 

 

Example scenarios:

 

 

 

  • List of different metrics available in the BOE system:

 

select distinct td.METRICNAME, td.TYPE

from TREND_DETAILS td

where td.TYPE='Metric';

------

 

  • List of watches

 

select distinct w.CUID, w.NAME, td.METRICNAME, td.TYPE

from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w

where td.CUID = w.CUID;

----

 

  • List of watches associated with metrics

 

select distinct w.NAME, td.METRICNAME, td.TYPE

from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w

where td.DETAILSID = w.DETAILSID

--and td.TYPE='Metric' --Optional filter

order by w.NAME;

----

 

  • Trend values of metrics for a specific watch since 09-Feb-2015

 

select w.NAME, td.METRICNAME, t.MESSAGEKEY, t.TIME,

{fn TIMESTAMPADD( SQL_TSI_SECOND, t.TIME/1000, timestamp('1970-01-01-00.00.00.000000'))} UTC ,

t.VALUE

from TREND_DETAILS td, TREND_DATA t, MANAGED_ENTITY_STATUS_METRICS w

where td.DETAILSID = t.DETAILSID

and td.DETAILSID = w.DETAILSID

and w.NAME='<Node>. InputFileRepository Watch'  ---This is an example

and t.TIME >= 1423440000000; ---equivalent epoch time in milliseconds for 09-Feb-2015 00:00:00 UTC

----

 

**The above query converts epoch time to regular time in UTC.

 

 

 

Concluding Remarks

 

The above write-up is not an exhaustive reference on monitoring database or monitoring functionality. The readers are encouraged to validate the above contents in line with standard BI Platform admin guide. Comments are welcome to further enhance the contents of this blog post. Thanks for your time

    When you start to Business Objects Design from the origin, nobody usually is thinking of that the standard naming for reports , connections, universes , folders etc...  But if you have thousands of objects in your company , it is getting more hard to handle and search the objects. So I’m gonna try to give you some tips about how should be the naming convention of BO Applications.

1- Connections

Conn.pngAs you know, there are two different types of connection in Business objects which are Relational and OLAP connection. 

First of all, the main issue that I have faced with is writing the type of the objects as a prefix such as CON_EFASHION. You don’t have to identify the type of object in the name, it is obvious from the icon and the folder it is connection.  Even in BO Audit Database you can classify with the object type.  But, for the trace logs maybe it is good to identify but it is rare condition.


a. Relational Connection


SOURCE

DB or SCHEMA ( Preferable )

MODULE(DEPARTMENT)

NUMBER

DETAIL

 

    In the Beginning, to understand which data source is being used for that connection, we should write the abbreviation of data source such as HANA, BWP and BIP etc…

    Besides, if datasources has more than one database or schema, it could be good to write the name of DB or SCHEMA. This condition depends on the structure of datasource.

    relat.jpgAfter we get the datasource information, then we should understand what kind of data that we get from the data source.  For that reason, we should classify the type of data. Depends the classification of your company, you should write the abbreviation of module or Department such as CRM, FI, HR etc…  Then we should give a number for each connection in a module.  At the end, we need some information about the data in the datasource so we should give a short explanation of the data.

Examples:  HANA_CRM_01_CUSTOMER, HANA_CRM_02_ADDRESS,

                        BIP_MM_01_MATERIAL

 

    b. OLAP Connection


SOURCE_NAME

BEX QUERY TECHNICAL NAME

 

 

 

    Olap.jpgIn OLAP Connection, I will just explain for the BW Bex Queries. It is easy to define this. First the abbreviation of datasource, then the technical name of the Query, it is so easyIt is recommended that writing the description of the query in the description part.

Example:    HWP-ZQY_YQBI0001_Q001 

 

 

klasörler.jpgOn the other hand you can create folder for each datasource in the repository, Even if you have so many connections for each you can create sub-folders under the datasource folder and put the connections in the folder according to the module and datasource to organise them better.   

 

 

 

2- Universes

 

              a. Connection

 

    I have already explained the connection. Shortcut connection can remain the same name as original connection.

 

 

        b. Data Foundation


DF

UNIVERSENAME

 

Naming Convention for Data Foundation is not so important to handle because it is obvious to which Business Layer use it and which connection is connected. But I am generally using this structure.

Example:  DF_CRM01_SEGMENTATION

 

 

          c. Business Layer


MODULE

NUMBER

EXPLANATION

 

BusinessLayer.jpgWhen you define a name for universe (business layer), you should consider that the name will be shown by end-users. So the name should be understandable for business users as well.

That’s why we need to define the good explanation of the universe. I also add the module name as a prefix to find the universe easily when you search it.

The main issue in this, ‘BL’ is written as a prefix for the name of universe. It is totally unnecessary. End-Users don’t understand what ‘BL’ is.

Example: CRM01-SEGMENTATION

On the other hand, the dimensions and measures that will be used in universe also clearly understandable and some of them requires some description to end user should understand easily.

 

 

 

3- Reports

 

NUMBER

MODULE

EXPLANATION

 

You should have different folders or sub-folders for each department or module in the repository. It is good to give a number for each report in a folder( module ) . Because the order of reports do not change when you add another reports in a folder. Besides,  You don’t have to write the “report” in the name of report such as “Balance Sheet Report”, It is better not to write unneccesary words. 

                                Example: 01-CRM : Daily Performance

                                Example: 01-FICA: Balance Sheet

Example: 02-FICA: Cash Flow

 

 

  This blog is not what you have to follow , it should give you a perspective to how you should make your naming convention, so according to the organization of company, it could change.

I hope this blog gives you some tips of how you should look at the naming convention in Business Objects.  If you have more idea about the subject , I will be glad to add to the blog. Feel  free to write your comment.


Sincerely

Today SAP Insider hosted this online chat with SAP's Sathish Rajagopal Harjeet Judge Maheshwar Singh Gowda Timma Ramu

 

For the full Q&A check the replay here.  Also check out the BI 2015 event in March.

 

Below is a small subset of the Q&A edited, and reprinted with SAP Insider permission:

 

Question and Answer:

 

 

Q: Will BW and BO merge in the future? As HANA is positioning BODS as a primary component for Data services and Lumira is on the horizon, how will BO future roadmap look like?

A: There will be more / tighter integration between these two - SAP BW and SAP BusinessObjects. There is no plan in the roadmap to merge these two technologies. SAP BusinessObjects will continue to be our Enterprise BI platform, which will be the foundation for all future innovations around Analytics. Whereas BW will continue to leverage the power of SAP HANA to store and process enterprise data.

 

 

Q: Is there a straightforward approach to check which reports are created on Universe?

A: There is no straightforward approach to get this information. You will have to write queries in query builder to find out the reports that are associated with a universe. You may have to potentially write more than one query to get the information you are looking. The other option would be to write a SDK script with some logic to run the queries. You can also explore the use of Information Steward tool that you posted in your other question. I will add value in extracting metadata from BI system database.

 

 

Q: Can you tell us more about the Free Hand SQL capabilities added to BI 4.1 SP5?

A: Currently support deski fhsql document migration to webi and refresh on webi, supported via extension point framework.

Plan is to fully integrate in UI in the future releases with asstional connection and query management capabilities.

Part of Free Hand SQL support was introduced in BI 4.1 SP05 release but we are working on to support the remaining parts in SP06 and 4.2 releases

 

Q: I would like to know which is the best way to connect a Dashboard to the cubes

A: If you are using BW my suggestion would be explore using SAP Design Studio to build your dashboard. Design Studio is designed from group up to support BW scenarios. You can use SAP Business Objects dashboards as well using various connectivity options:

1) Direct BICS connection to BW if you plan on hosting the dashboard on netweaver portal

2) Build Webi report on BW query and expose the block as webservice and use BIWS connection in dashboards.

 

 

 

Q: What are the main benefits to move from BI4.0SP5 to BI4.1SP0 ?

A: Depends on the usage of BI Platform. the following SCN blog lists the enhancements on platform and client tools in BI 4.1 SP05.

SAP BusinessObjects BI4.1 SP05 What's New

 

 

Q: We are planning upgrade to BI4.1 sometime next year (fingers x'd), anything in particular we should look out for?

A:  The answer would depend upon which version you are upgrading from. Few things to pay attention to:

1) Know that BI4.x is 64 bit architecture so the hardware requirements may be different

2) Understand the BI4.x offers 32 bit and 64 bit db connectivity depending upon which client you are using for reporting. You will have to configure both 32 bit and 64 bit db connectivity

3) Pay attention to sizing your system. If you are on 3.x don't expect to run your BI4.1 system on the same hardware

4) Split your Adaptive Processing Server as this will impact system stability. You can find document on SCN on how to do this

 

Q: Can a webi report connect straight to a HANA view , without need for a universe. Any plans to deliver this functionality ?

A: Direct access to HANA views from Webi is planned with BI 4.2

 

Q: We are on Bex3.5 and trying to decide whether to move to Bex7, Analysis for Office, or another product. We will likely not install the entire Bobj suite, but have a ton of workbooks

A: I would suggest you to check the differences and importantly gaps between these options and then decide. Because you may be using unique or certain functionalities in your environment. It won't be wise to suggest one way or other. But ultimately you need to upgrade from 3.5 for sure.

 

 

Q: Most of the Clients are on and will be in XI 3.1 and BI 4.1 parallel. 3.1 Infoview supports 1.6 Build 32 but 4.1 BI Launchpad does not. This would mean developers and users cant be login to both environments unless we do some manual overides (which is not supported by Network/Security teams). Is there any alternative to this?

A: I assume you are referring to the java version on the client. There is no easy to way to deal with this. Couple options:

1) Use Citrix and have some clients go through citrix which has different version of JVM

2) Explore the use of HTML query panel for Web Intelligence

 

 

Q: We use a portal to present our reports to customers using opendoc. We have one server with one webi processing server. We are always running into issues where users sessions are stuck and busobj is not timing out the sessions. We also have an issue with webi processing server when at specific time at night its always throwing warnings that its high on memory or maximum user connections are logged, when there are zero users logged in and using the webi processing server. Any advice or insight on these issues?

A: Opendoc sessions timeout by default at about 20 mins. This time is configurable. You could also use the kill session in the CMC to release the idle sessions. However you need to be on 4.1 sp3 or greater

 

Q: Which BusinessObjects BI 4.1 tools to use for an access to Transient Provider?

A: The tools like CR4E, Webi and Analysis clients etc.. using BICS for Data Access can access Transient Provider.

 

 

Q: I am missing a functionality to add comments to the reports which can be entered by users. ist there a Standard solution available?

A: BI4.1 has collaboration feature that supports integration with SAP JAM

 

 

Q: When will the UNV go out the door and when will UNX take over? Should we panic now and convert all our UNVs to UNXs?

A: Our goal is to support innovation without disruption. We are not planning to end .unv support any time soon which is why you still see the universe designer in BI4.1. Having said that, most of the new functionality is only added to .unx universes to entice you to eventually make the conversion to .unx universe. My advice is continue to use .unv universes for your existing content and do you new development on .unx. You should also have a mid to long term plan to convert your universes to .unx to take advantage of the new features.

 

 

Q: I would like to put my results on a world map using Design Studio. Which Tools do you offer? Will there be a full map Integration of Dashboards available with Googlemaps or an own SAP world map?

A: You can use SDK components delivered by partners

List of Design Studio SDK Components

A: The full geo map support in Design Studio is planned for future release.

 

Q: Is audit functionality has improved in 4.1 as compare to 3.1 if yes what has improved?

A: We introduced additional functionalities such as more events to capture etc. in BI 4.1 release and the schema itself has been improved with totally a new structure for better performance etc..

 

 

Q: Do you know the release date for Design Studio with offline data support?

A: We don't have a timeline for this, but this is a roadmap item for the future. I would encourage to put this idea on idea place if it's not already there. You can also vote on the idea...more customers that vote the idea the more likely you will see the feature in the product.

 

 

Q: Global Input Controls (one set of Input Controls,controls all tabs) is it happening SP6? or is it avalaibel in any earlier Fix Packs. This should have been logical addon feature in 4.1 as it was Pending Idea in ideas.sap.com for very long

A: Yes, Global Input Controls is planned for BI 4.1 SP06.

 

 

Q: So are you saying we can link universes in 4.1, i thought this feature is no more there in 4.1?

A: You are correct. it is planned for the future release most likely BI4.2

 

Look for more in March at BI 2015

A fantastic opportunity for you to learn more about BusinessObjects BI 4 is currently being offered by SAP via OpenSAP.

 

Enroll now:

 

https://open.sap.com/courses/bifour1-1

 

Here is the course summary:

 

“We live in a world where big data, people, machines and processes are interlinked in an internet of everything. Immense value can be unleashed by connecting this information to the work we do every day, enabling us to quickly discover what is happening and then act with the power of collective insight. Learn how to unleash this power by implementing SAP BI 4 with our new SAP BusinessObjects BI 4 Platform Innovation & Implementation Training course offered through openSAP.

Successful deployments require proper sizing, hardware, configuration, security and administration. This course, designed for experienced BI system administrators, is brought to you by the Strategic Customer Engagements Team, who are SAP’s most senior SAP BusinessObjects BI specialists.”

 

Enjoy the learning experience!

Dear SCN Community Members,

 

We are please to announce the availability of the SAP BusinessObjects BI4 Custom Implementation Report. With this report, we will help you understanding the best option to implement your SAP BusinessObjects BI4 deployment based on your organisational requirements. Based on a set of questions and your input, an Implementation Report will be generated containing a long list of recommendations and links to relevant content to further enable you in deploying SAP BusinessObjects BI4 successfully.

 

Implement | SAP BusinessObjects Business Intelligence Solutions 2015-01-20 13-19-20.jpg

 

You can run your own Custom Implementation report via : https://www.sapbusinessobjectsbi.com/implement/

 

Please share your feedback with us!

Regards

Merlijn Ekkel

 

Director BI Solutions | SAP GMT BI | Solution Management

Communication to Identity providers like Active Directory, LDAP and SAP was covered in part 1, and securing the web tier was covered in part 2.

Now let's look at the actually BI servers, like the Central Management Server, (CMS), File repository Server (FRS) and others.

 

We'll look at port restrictions, potential firewall setups, SSL/TLS and other configuration switches.

 

FIPS 140-2

By now you may have read about the -fips parameter on the SIA.  FIPS stands for Federal Information Processing Standard.  I cover this mode more in my data security blog.  The quick summary is that BI4 uses FIPS certified encryption libraries to perform its encryption.  


Turning this switch on (add a "-fips" on the SIA command line), prevents usage of older clients and disables some older functionality.  If you do not have any xir3 clients or custom applications running against your BI4 system, there is no reason NOT to have this switch on.  Do expect this to become the default in upcoming maintenance releases, where you will instead need a special switch to turn ON old functionality, but by default, and xir3 or older client will NOT be able to connect.

 

It is not just about enforcing stronger BI4 security.  By disabling older functionality, you again reduce the attack surface, where a server not accepting calls based on older functionality will be harder to exploit.  If you're familiar with the POODLE attack, you'll know for example that the latest recommendation is to outright disable SSLv3 protocols and use strictly TLS.   A similar concept applies here . 



Minimum Privileges

Creating a special locked down user to run BOE can be worthwhile.  The built in windows system account is actually quite powerful

 

The rights required on the local computer where the SIA is running are as follows:

 

-Logon As a Service.

-Read/Write to HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0

-Read/Write to Install directory (specifically Write access to the Log Locations).

The important part here is the account should NOT be an Administrator on the local machine.

 

 

Server to Server channel Encryption (CORBA SSL)

The how to steps for server to server communication encryption are detailed well in the BI4 admin guide, as well as in this online wiki for unix

The client configuration is detailed in sap note https://i7p.wdf.sap.corp/sap/support/notes/1642329

How much of a performance hit can you expect?    It really depends on many factors, there is often a tradeoff in performance for security, but a rough guidance can be a 10%-20% impact based on what I have seen so far. 



File Repository Server

This is an important server to protect, because it contains your report content on the file system.  If the reports are saved as PDF or saved with data, that makes them very valuable to attackers.  There are a few additional things you can do to protect the content.

-Secure the FRS OS folders so that only the account that the SIA hosting the FRS can access

-Use file level encryption.  This can protect the content from unauthorized access through the local machine. 

-Virus Scanning.  For large deployments and heavy usage, this can be a big bottleneck on the I/O to the point that performance visibly suffers.    For performance reasons, you may consider running scheduled scans in "off hours" rather than real time virus scanning.  By far, real time virus scan is more secure, but you can further mitigate with locking down what users can upload. 

-Limit content types from being uploaded:

Rather than granting the generic "Add Objects" right, you can actually lock it down to content types, and only permit CR, Webi etc types of documents.  This will prevent a user from uploading a bad executable or batch file, that another user then downloads and executes on their own machine.  Of course one would hope that end users would know better, but prevention is your best defense. 


 

Default Accounts

All BI installations start with a default "Administrator" account.  For a potential attacker, that is one known piece of information for trying a brute force attack.  Enabling auto lockout for failed attempts will certainly help mitigate this, however another thing you can do is to rename the default account.  Instead of "Administrator" use your own naming such as <Company>_BI_Admin.  For example SAP_BI_Admin.

 

Stale Accounts

Have people left the company?  Maybe never even logged in?  The less accounts you have, the less chance of an old stale password falling into the wrong hands, or accounts being misused.  It is again about reducing attack surface.

 

The following query, which you can run using the AdminTools console, will return to you a list of users by the last logon time.

SELECT SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'user' ORDER BY SI_LASTLOGONTIME DESC

Below is a stripped down sample output.  While these users may have content in personal folders you don't want to lose, consider disabling the accounts.

 

Ports, Firewalls

Firewalls help you reduce the attack surface.  In the simplest, happiest (from a security standpoint) workflow, all your users are web users, and will only be connecting to BI Launchpad.  In this case, the BI servers can be fire-walled away from the end users.  However chances are you also have thick clients connecting.  In this case, make sure the thick clients are limited to connecting from a trusted network zone, if networks are partitioned.

You can bind servers to a specific port in the CMC.

 

The CMS has both the the name server and request port that you can configure:

 

By setting a specific range of ports to use or binding to specific ports, you can then use a firewall to further lock down and reduce the attack surface of your servers.

 

Keep in mind that thick clients must be able to communicate with the CMS, as well as the Input and Output file repository server.  There is a fairly complete overview of the server port communication described in the administration guide, section 8.14.2

 

Your IT may have also put your database layer into a separate network zone, inaccessible to regular workstations.  Yes, IT is making your life difficult, but for a good reason in the classical 3 tier architecture.  Clients can and should (for security purposes) connect through the BI platform which in turn connects to the database layer.  This extra hop makes it more difficult for a connection to abuse or attack the database layer directly, where all your valuable data resides. 


Database Encryption

The communication between the BI processing servers and the actual database can, and from a security standpoint should be encrypted.  To help you decide, a threat model should be done.  How sensitive is the data, how isolated are the data sources are just two considerations.   Generally, one should assume that their network HAS been compromised, and build out a security in depth approach.  It is quite easy for someone in your company to fall for a phishing attack.    You can set database encryption at the driver level, below being an example of a sql server driver:

 

CMS DB Encryption

The CMS repository does not store any data in your reports, however it can store sensitive metadata such as connection information.  This is automatically encrypted using a two key mechanism as part of the BI4 build in encryption.  Again, this is described in my encryption & data security blog.

Using your database vendor's built in database encryption to encrypt the whole data may actually be overkill here, and is actually not something that I would strongly recommend as being necessary, but certainly a valuable 'security in depth' principle option.   The advantage of selectively encrypting content, the way the BI4 process does is that you do not suffer performance hits on non essential data encryption, such as the metadata associated with a report's layout.

 

Temporary Files

During document creation and processing, temporary files will be created, and they may contain some data.  Have a look at your temporary folders, and lock these down to the process that the SIA service hosting these servers is running under.   See below for the Crystal Reports processing server as an example.

Placeholders like "%DefaultDataDir% and others are defined under the placeholders tab of your Server Properties.

%DefaultDataDir% defaults to "/SAP BusinessObjects Enterprise XI 4.0/Data/"

 

Overview:


Auditing is an important out of the box solution, to keep a track on the usage pattern of the SAP BOE platform. Audit data is relevant both from an administration perspective, as well as from compliance perspective for maintaining audit trail for a specified interval of time. While sample audit universe acts as a starter kit to start reporting on audit data (http://scn.sap.com/docs/DOC-53904), a knowledge on the underlying data model helps us build our own queries & reports and optimize them better for performance. The starting point of understanding how auditing works and what information is audited, refer to the relevant chapters in the BI Platform admin guide, downloadable at : http://help.sap.com/boall_en/. e.g. in sbo41sp3_bip_admin_en.pdf, chapter 21 and 33 talk about auditing. There are also several insightful blog posts on auditing and audit reporting by 'Manikandan Elumalai' on SCN.

 

Any SQL examples shown in this blog post are based on audit database hosted in Oracle. However, the same can easily be adapted to any other query language syntax, as the table structures remain same.

 

Audit Data Model:

 

Audit database is designed for both transactions and querying. Audit data is continuously being written to this database by BOE and at the same time audit reports / queries can be fired on it to report near real time audit information.

 

There are two main transaction tables in audit database: ADS_EVENT and ADS_EVENT_DETAIL. Remaining tables are either lookup or bridge tables. Any auditable action in BOE is captured as a unique Event_Id stored in ADS_EVENT and each Event_ID will have one or more detail records (Event_Detail_Id) in ADS_EVENT_DETAIL. Both the Event and its corresponding Detail can be of specific types and can have other supporting attributes.

 

This core concept of auditing has remain unchanged since BO XI 3.1, though the number of tables have increased significantly in BI 4.x audit database. The increase in number of tables is primarily due to increase in the attributes being captured and more normalization of the data structures.

 

 

BO XI 3.1 Audit Data Model

BOXI3.1_Audit.JPG

 

BI 4.x Audit Data Model

BI4.1_Audit.JPG

 

 

 

 

Audit Data Dictionary:

 

The best way to analyze audit database, is to use a GUI based database client like Oracle SQL Developer. The following queries are helpful in listing the data dictionary:


-------

select owner, object_name, subobject_name, object_type
from all_objects
where owner = '<Schema Name where audit tables are created>'
order by object_type, object_name;







-----

select owner, index_name, index_type, uniqueness, table_name, table_type
from all_indexes
where owner = '<Schema Name where audit tables are created>';







------

desc <each table name>;







------

A clear trend which comes up, based on the output of the above queries:

  • Only tables and indexes are present in audit database. No views, procedures, materialized views etc. exists
  • There is no enforced referential integrity between the tables i.e. no primary and foreign keys
  • Index type is normal and either unique or non-unique
  • Due to multilingual support being available by default in BI 4.x, all lookup tables (names ending with _STR) have 'Language' as an additional field
  • The field EVENT_DETAIL_VALUE in ADS_EVENT_DETAIL is of datatype CLOB. Remaining columns in all the tables are of either varchar2, numeric or date datatypes.

 

Building Audit Queries:


Common audit reporting scenarios may have metrics like Count of Events, Last <Event Type> Timestamp, Count of Users. All these metrics can be derived from the table ADS_EVENT. Supporting details for an event can be obtained from ADS_EVENT_DETAIL. Description of attributes can be obtained from the lookup tables after joining with either ADS_EVENT or ADS_EVENT_DETAIL tables. It is important to apply suitable filter to the queries to optimize performance. Common filter criteria may be based on date, event type, detail type, language etc.

 

Example scenario:  Reporting user group membership details for users, who have logged into BOE in past 30 days:


----

SELECT DISTINCT USER_NAME, USER_GROUP FROM (
SELECT ae.USER_NAME USER_NAME,
dbms_lob.substr(ad.EVENT_DETAIL_VALUE,2000,1) USER_GROUP
FROM ADS_EVENT ae, ADS_EVENT_DETAIL ad WHERE ae.EVENT_ID = ad.EVENT_ID
AND ad.EVENT_DETAIL_TYPE_ID = 15 --Denotes detail type: User Group Name
AND ad.event_detail_value not like 'Everyone%' --To eliminate the 'Everyone' group records
AND exists
(select 1 from ads_event X where X.event_type_id = 1014 --Denotes event type: Logon 
and X.event_id = ae.event_id and X.start_time >= sysdate-30))
WHERE rownum < 50001
ORDER BY USER_NAME;







------

The above query converts CLOB data type to varchar. Once converted, regular string functions can be applied on the results like order by, distinct etc.

 

Concluding Remarks:

 

The above write-up is not an exhaustive reference on audit database. Readers are encouraged to validate the above contents in line with standard BI Platform admin guide. Comments are welcome to further enhance the contents of this blog post. Thanks for your time

Have you ever seen a log file in your Application Server directory called TraceLog_<pid>_<date_timestamp>.glf?  Ever wondered what that was?

 

This log file is generated from a number of the BI Platform Web Applications and by default will contain on Error level messages.  Here is an example of one I found on my test machine:

 

Found in Directory: C:\Program Files (x86)\SAP BusinessObjects\tomcat\

Filename:  TraceLog_1140_2014_11_20_05_23_52_898_trace.glf

Contents:

 

|64DF6F8D078E466397CBCD8D875B98240|2014 11 20 05:23:52.904|-0800|Error| |==|E| |TraceLog| 1140|  18|Start Level Event Dispatcher| ||||||||||||||||||||com.bo.aa.layout.DashboardManager||underlying implementation doesn't recognize the attribute

java.lang.IllegalArgumentException: http://javax.xml.XMLConstants/feature/secure-processing

  at org.apache.xerces.jaxp.DocumentBuilderFactoryImpl.setAttribute(Unknown Source)

  at com.bo.aa.layout.DashboardManager.setDocBuilderFeaturesForXXE(DashboardManager.java:134)

  at com.bo.aa.layout.DashboardManager.<clinit>(DashboardManager.java:161)

  at com.bo.aa.impl.DBServerImpl.<clinit>(DBServerImpl.java:397)

  at com.bo.aa.servlet.AFBootServlet.InitServers(AFBootServlet.java:80)

  at com.bo.aa.servlet.AFBootServlet.init(AFBootServlet.java:47)

  at com.businessobjects.http.servlet.internal.ServletRegistration.init(ServletRegistration.java:81)

  at com.businessobjects.http.servlet.internal.digester.WebXmlRegistrationManager.loadServlets(WebXmlRegistrationManager.java:127)

  at com.businessobjects.http.servlet.internal.digester.WebXmlRegistrationManager.registerRest(WebXmlRegistrationManager.java:209)

  at com.businessobjects.http.servlet.internal.ProxyServlet.readXml(ProxyServlet.java:368)

  at com.businessobjects.http.servlet.internal.ProxyServlet.registerInternal(ProxyServlet.java:395)

  at com.businessobjects.http.servlet.internal.ProxyServlet.register(ProxyServlet.java:317)

  at com.businessobjects.http.servlet.config.WebXmlConfigurator.register(WebXmlConfigurator.java:60)

  at com.businessobjects.bip.core.web.bundle.CoreWebXmlActivator.start(CoreWebXmlActivator.java:66)

  at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:782)

  at java.security.AccessController.doPrivileged(Native Method)

  at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:773)

  at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:754)

  at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:352)

  at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:280)

  at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:272)

  at com.businessobjects.http.servlet.Activator.startBundle(Activator.java:129)

  at com.businessobjects.http.servlet.Activator.start(Activator.java:116)

  at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:782)

  at java.security.AccessController.doPrivileged(Native Method)

  at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:773)

  at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:754)

  at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:352)

  at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:370)

  at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1068)

  at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:557)

  at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:464)

  at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:248)

  at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:445)

  at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:220)

  at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:330)

 

For those of you that have spent time looking at these types of messages, you will likely recognize a few things.  The first, is that the bulk of this error message is a Java backtrace. Backtraces are often read from the bottom up and it gives you an idea of the sequence of calls that occurred leading up to the error.  In this case, we can see the error: 

 

com.bo.aa.layout.DashboardManager||underlying implementation doesn't recognize the attribute

java.lang.IllegalArgumentException: http://javax.xml.XMLConstants/feature/secure-processing


Which tells us what caused the error trace log entry, but we might be more interested in what happened leading up to this error. For that, we can traverse the backtrace to get an idea of what was going on before this error. 


In this case, I have no idea what actually caused this error.  I just found it on my test machine from around 3 weeks ago.  But from the backtrace, I can made an educated guess that the cause was related to a Dashboard layout of some sort.  Regardless, this is not the purpose of this blog so I will move on.


The error messages found in these TraceLog*.glf files are not usually enough to properly troubleshoot an issue.  To get proper details around what causes an issue, we need have more verbose logging.

 

One way we can enable verbose logging for the BI Platform Web Apps is by enabling it in the CMC.  Section 25.4.1 in the BIP Administrator's guide covers how to do this.  In the CMC, you can enable traces for the BI Launchpad, CMC, Open Document, Promotion Management, Version Management, Visual Difference and Web Services applications.

 

Another way to enable tracing for the BI Platform Web Apps is to follow the below steps.  I have found added details in the these log files that wasn't available through the CMC enabled logs:

 

Steps to setup Verbose logging for the TraceLog Application server traces (example for Tomcat)


  1. Go to this folder and copy the BO_Trace.ini:  C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\BOE\WEB-INF\TraceLog
  2. Paste this file in the C:\Program Files (x86)\SAP BusinessObjects\tomcat directory and rename it to TraceLog_trace.ini
  3. Edit this file and change the line:
    sap_trace_level = trace_error;

        to

    sap_trace_level = trace_debug;
  4. Find the line below it and change it as well:
    sap_log_level = log_error;

        to

    sap_log_level = log_info;
  5. I also like to set the append = true; to append = false; which will use the Process ID and Date/Time stamp in the naming convention of the log files.
  6. Save the TraceLog_trace.ini file and within a minute, you should start seeing some log files growing in the Tomcat directory.

 

Here is an example of what my log files contain after enabling the above log levels:

 

|039A2887DCF24130ADA77A3BA3DBF3A6155|2014 12 17 14:57:25.015|-0800|Information| |==| | |TraceLog| 1144|  47|http-bio-8080-exec-7| ||||||||||||||||||||com.businessobjects.bip.core.web.bridge.ServletBridgeLoggingDelegate||servletbridge.relative.url.prefix.out.of.bundle: ../..

 

|039A2887DCF24130ADA77A3BA3DBF3A6156|2014 12 17 14:57:25.015|-0800|Information| |==| | |TraceLog| 1144|  47|http-bio-8080-exec-7| ||||||||||||||||||||com.businessobjects.bip.core.web.bridge.ServletBridgeLoggingDelegate||servletbridge.relative.url.prefix.to.root.of.bundle: ../../IVExplorer

We can see that the type of entry is "Information" now which tells us our settings are being used.

 

Now, this trace is quite verbose so really the only time I would recommend using it is when you can reproduce an issue in a short period of time.  To deactivate the trace, you just edit the TraceLog_trace.ini file and set the trace/log levels back to *_error. 

 

Do not delete the file as this will not deactivate the current trace levels.  Just edit and save the file to deactivate it.  If you do delete the file, you will need to restart Tomcat to disable the traces again.

 

Any ways, this trace can sometimes give you additional details that are not available in other tracing methods.  Be sure to deactivate it as soon as you are done using it though as it does have a slight impact on performance.

 

Thanks,

Jb

In my previous blog, I covered securing of the communication of your authentication providers.

In this posting, we will cover the configuration of the web tier.   It is your war file deployment, and probably the most exposed part of your deployment, especially if you're facing the public web.

 

Reduce the attack surface.

The less you have deployed, the less that can be attacked.   Although the default BI install will deploy a number of components, you likely don't need them all.

You may see a list like this of war files deployed:

AdminTools - designed for running advanced direct queries against the BI repository.   If you don't use this, remove it.   You could also consider running it on a separate, local access only deployment.

 

BOE - This is the core of the BI deployment, includes CMC, BI Launchapd and OpenDocument functionality.  Note that using wdeploy, you can split the CMC and BI Launchpad deployment, and put the CMC functionality on another, more locked down application server.

 

dswsbobje - web service used by Crystal Reports for Enterprise, Dashboard designer, and your custom applications.  Again something you can remove if none of the above apply to you.

 

BusinessProcessBI - this is an SDK which is not needed for core functionality.  If you're not deploying custom applications that make use of this, this is something you can remove from your deployment.

 

clientAPI - contains Crystal Reports ActiveX controls for custom application deployment.  You can almost certainly remove this.

 

MobiServer & MobileBIService - if you are not deploying mobile, you should have no need for these.

 

docs - This is the default tomcat documentation.  They are also available online, so there should not be any need for these to be deployed.  They contain information about the version of tomcat which is not necessary.

 

Tomcat Security

Refer to your tomcat guide.  The following is an excerpt from the tomcat guide on default web applications:

Tomcat ships with a number of web applications that are enabled by default. Vulnerabilities have been discovered in these applications in the past. Applications that are not required should be removed so the system will not be at risk if another vulnerability is discovered.

http://tomcat.apache.org/tomcat-7.0-doc/security-howto.html#Default_web_applications

 

Apache regularly publishes its list of fixed vulnerabilities here:

http://tomcat.apache.org/security-7.html

BI SP's regularly bundle updates of Tomcat.  SAP continually monitors the bundled applications and works to deliver any updates as part of the regular maintenance cycle.  We regularly monitor the security listings of tomcat, and use that to drive our updates.

If you are unable to stay on the latest support packages, you may want to consider reviewing the list of vulnerabilities and using your own update of Tomcat at least until such time when you can deploy the latest BI4.x support pack.

 

Tomcat User Account

The user account only needs to read files under tomcat.  Create a user for the tomcat service account, give the service account "Logon as a User" rights, and read only rights on the tomcat folder.

 

Hide CMS Information

The single biggest benefit is usability, because users will not accidentally lose the information or put in a typo and try to connect to the wrong place.

There is no reason why someone should try to communicate anywhere other than the CMS. so set the CMS.visbile=false setting in the BIlaunchpad.properties file and the CmcApp.properties files.

Change the following:

 

# You can specify the default CMS machine name here

cms.default=VANTGVMWIN902:6400

 

# Choose whether to let the user change the CMS name

cms.visible=false

 

Now, there is less chance to redirect any shared secrets, credentials or other information to a server of their choosing.

 

Secure the communication channel - Use TLS

This should be a fairly well accepted policy already.

While terms like HTTPS and SSL are thrown around, this should really mean "TLS" behind the scenes.  TLS is a newer protocol for secure communication.  SSLv3 has now been rendered insecure, and you should be configuring your application servers to use the TLSv1 or higher protocol.

If you are not using SSO exclusively to logon to the BI web apps, (likely to be the case with CMC which does not support SSO), you should be encrypting the traffic and logging on with HTTPS.   Otherwise, the logon credentials will be passed from the browser to Tomcat or the application server of you choice in clear text over the wire.

 

You've heard of POODLE?  Disable SSLv3 in Tomcat while you're at it.

 

 

Do you use flash?  Dashboarding, aka XCelsius

The BI install installs a file called crossdomain.xml.  It's an XML document that grants a web client—such as Adobe Flash Player, Adobe Reader, etc.—permission to handle data across multiple domains.

The default is very inclusive,

<cross-domain-policy>

    <site-control permitted-cross-domain-policies="all"/>

    <allow-http-request-headers-from domain="*" headers="*" secure="false" />

    <allow-access-from domain="*" secure="false" />

</cross-domain-policy>

and you should take steps to lock it down if you will allow hosting of flash based content.

As this configuration file is completely outside of the SAP BI control, please refer to Adobe's documentation for crossdomain.xml

 

 

Protect Credentials

If you're setting up Active Directory SSO, make sure you're not storing the credentials as a java option, but protect the password with a keytab instead.

Don't do this (notice the wedgetail.idm.sso password in clear text):

 

Do this instead:

 

1. Create a keytab with the ktpass command

The details for this are contained in the whitepaper attached to sap note http://service.sap.com/sap/support/notes/1631734

The whitepaper is a must for anyone setting up AD for the first time.

 

2. Copy the.keytab file to the c:\windows\ directory of the application server

3. Add the following line to C:\Program Files (x86)\SAP BusinessObjects\Tomcat\webapps\BOE\WEB-INF\config\custom\global.properties idm.keytab=C:/WINDOWS/<your keytab file name>


If you're using Trusted Authentication, make sure you secure the shared secret file, so that only the process that your web application server is running as can access it.   Consider using OS file level encryption to further lock this file down.



Web Application Container Server

If you are using the WACS, to host your restful web services, or possibly the CMC, the configuration for secure communication is done through server properties in the CMC.

 

What about Cross Site Scripting, SQL Injection, OWASP TOP 10?!   IS IT SAFE!!??

 

SAP has a very strict release criteria, and a secure development cycle implemented.  Testing includes, and is not limited to, static code scanning, dynamic analysis tools, manual penetration testing and security architecture reviews.   You can find out more about our security processes here:

 

Conclusion

The secure approach is to treat your internal network that all your end users access as compromised.   Just think of the latest Sony attack as an example, to see the value of encrypting the communication channels.

 

Additionally, leveraging firewalls to block off parts of the network to would be attackers is also valuable.  Firewalls and server communication are covered in part 3.

 

 

 

Feel free to add you comments/questions on other areas, the blog will get updated with any additional bits that may have been missed here.


 

Actions

Filter Blog

By author:
By date:
By tag: