( Geek2Live.net )

After trying to think of of another useful topic, I realised that configuring Single Sign On with Active Directory and Vintela in XI 3.1 is something that is rarely covered, and I used to have quite a lot of trouble with it.

By adapting a document on the SAP Support Portal, I now use a sure-fire method to configure AD SSO with Tomcat, the default web application server that ships with BusinessObjects Enterprise/Edge XI 3.1.  It’s worked every time I’ve used it.

Firstly, let’s define our server names and IPs (you must obviously adjust these and the commands below to reflect your server names and IPs – I have underlined commands that need to be changed to help):

• Domain Name: POWI (FQDN: POWER.INTERNAL)
• Service Account: bo.service (password: admin)
• Domain Controller: vs-dev-ad-dc.POWER.INTERNAL (IP: 192.168.5.1)
• BO Server: vs-dev-ad-bo.POWER.INTERNAL (IP: 192.168.5.2)
• BusinessObjects AD Group: POWI\Business Objects

Step 1

Create an Active Directory service account, bo.service (pass: admin).  On the BusinessObjects server, add the POWI/bo.service user to the Administrators group.  Also assign them the following rights in the Local Security Policy snap-in:
•    Act as part of Operating System
•    Log on as a Batch Job
•    Log on as a Service
•    Replace a Process Level Token

Step 2

Run the following command on the Active Directory server:

ktpass -out BOSSO.keytab –princ BOSSO/bo.service.power.internal@POWER.INTERNAL -mapuser bo.service@POWER.INTERNAL -pass admin -kvno 255 -ptype KRB5_NT_PRINCIPAL -crypto RC4-HMAC-NT

The output from the above command should be similar to:

Targeting domain controller: vs-dev-ad-dc.POWER.INTERNAL
Using legacy password setting method
Successfully mapped BOSSO/bo.service.power.internal to bo.service.
Key created.
Output keytab to BOSSO.keytab:
Keytab version: 0x502
keysize 81 BOSSO/bo.service.power.internal@POWER.INTERNAL ptype 1 (KRB5_NT_PRINCIPAL) vno 255 etype 0x17 (RC4-HMAC) keylength 16 (0x209c6174da490caeb422f3fa5a7ae634)

Step 3

Run the following command on the Active Directory server:

setspn -l bo.service

The output should be similar to:

Registered ServicePrincipalNames for CN=bo.service,CN=Users,DC=POWER,DC=INTERNAL:
BOSSO/bo.service.power.internal

Step 4

Go to properties of the ‘bo.service’ user in Active Directory and under the Delegation tab, set ”Trust this user for delegation to any service (Kerberos only)’ to on.

Step 5

Move the BOSSO.keytab file that was created on the Active Directory server (refer Step 2) to c:\winnt\ of the BusinessObjects server.

Step 6

Generate the requisite SPN’s by running the following commands on the Active Directory server:

setspn -a HTTP/vs-dev-ad-bo bo.service
setspn -a HTTP/vs-dev-ad-bo.power.internal bo.service
setspn -a HTTP/192.168.5.2 bo.service

The output from the above commands should be similar to:

HTTP/vs-dev-ad-bo
Updated object
Registering ServicePrincipalNames for CN=bo.service,CN=Users,DC=POWER,DC=INTERNAL
HTTP/vs-dev-ad-bo.power.internal
Updated object
Registering ServicePrincipalNames for CN=bo.service,CN=Users,DC=POWER,DC=INTERNAL
HTTP/192.168.5.2
Updated object

Step 7

Run the following command on the Active Directory server to view all of the created SPNs:

setspn -l bo.service

The output should be similar to:

Registered ServicePrincipalNames for CN=bo.service,CN=Users,DC=POWER,DC=INTERNAL:
HTTP/192.168.5.2
HTTP/vs-dev-ad-bo.power.internal
HTTP/vs-dev-ad-bo
BOSSO/bo.service.power.internal

Step 8

Within the BusinessObjects Central Management Console, within the Windows AD Authentication area, do the following:

1. Enable Windows AD
2. Set the AD Administration Name: POWI\bo.service
3. Set the Default AD Domain: POWER.INTERNAL
4. Add AD Group: POWI\Business Objects
5. Set ‘Use Kerberos Authentication’
6. Set the Service Principal Name: BOSSO/bo.service.power.internal
7. Set ‘Enable SSO for Selected Authentication Mode’

Step 9

Modify the SIA service on the BusinessObjects server to run as the POWI\bo.service domain user.

Step 10

You should now be able to get SSO onto locally installed tools (ie Designer, Webi Rich Client) by starting the application, selecting the authentication method to be Windows AD, and without inputting a username and password, clicking OK.  You should be logged in as your AD user.

Step 11

Create a file called c:\winnt\bsclogin.conf on the BusinsesObjects server, and put in it the following text:

com.businessobjects.security.jgss.initiate {
com.sun.security.auth.module.Krb5LoginModule required debug=true;
};

Step 12

Create a file called c:\winnt\krb5.ini on the BusinessObjects server, and put in it the following text:

[libdefaults]
default_realm = POWER.INTERNAL
dns_lookup_kdc = true
dns_lookup_realm = true
udp_preference_limit = 1
[realms]
POWER.INTERNAL = {
kdc = VS-DEV-AD-DC.POWER.INTERNAL
default_domain = POWER.INTERNAL
}

Step 13

To test that the krb5.ini file was created successfully, undertake the following:

1. Navigate to \Program Files\Business Objects\javasdk\bin on the command line
2. Execute ‘kinit bo.service‘, then input your password
3. A ticket should be created

Step 14

On the BusinessObjects server, open up the Tomcat Configuration application, then go to the Java Options input, and add the following lines (restart Tomcat once done):

-Djava.security.auth.login.config=C:\winnt\bscLogin.conf
-Djava.security.krb5.conf=C:\winnt\Krb5.ini

Step 15

Modify the \Program Files\Business Objects\Tomcat55\webapps\InfoViewApp\WEB-INF\web.xml file and allow users to see authentication options by changing the authentication.visible tag to true.

Step 16

Modify the \Program Files\Business Objects\Tomcat55\conf\server.xml file, by change the following line to increase the MaxHttpHeaderSize element to ’16384′:

<Connector URIEncoding="UTF-8" acceptCount="100" connectionTimeout="20000" disableUploadTimeout="true" enableLookups="false" maxHttpHeaderSize="16384" maxSpareThreads="75" maxThreads="150" minSpareThreads="25" port="80" redirectPort="8443"/>

Step 17

Modify the \Program Files\Business Objects\Tomcat55\webapps\InfoViewApp\WEB-INF\web.xml file as follows:

1. Change authentication.default to ‘secWinAD’
2. Change siteminder.enabled to ‘false’
3. Change vintela.enabled to ‘true’
4. Remove comment tags (<!–, –>) from around the authFilter filter element
5. Change idm.realm to ‘POWER.INTERNAL’
6. Change idm.princ to ‘BOSSO/bo.service.power.internal‘
7. Remove comment tags (<!–, –>) from around the authFilter filter-mapping element

Step 18

On the BusinessObjects server, open up the Tomcat Configuration application, then go to the Java Options input, and add the following lines:

-Dcom.wedgetail.idm.sso.password=admin (password for bo.service user)
-Djcsi.kerberos.maxpacketsize=0
-Djcsi.kerberos.debug=true

Step 19

Remove the following from the Java Options input in the Tomcat Configuration (if they exist):
•    Debug =true in the bsclogin.conf (set by default)
•    -Dbobj.logging.log4j.config=verbose.properties (may have been added to Java Options)
•    -Dcrystal.enterprise.trace.configuration=verbose (may have been added to Java Options)
•    -Djcsi.kerberos.debug=true (may have been added to Java Options)
•    Dcom.wedgetail.idm.sso.password=admin (only remove if you have a valid keytab configured)
•    Switch Tomcat 5.5 back to run as the local system (if running under service account for verbose tracing)

Step 20

Encrypt your service account password by coping the BOSSO.keytab (created during Step 2) to the c:\winnt directory on the BusinessObjects server, then specify the following in the \Program Files\Business Objects\Tomcat55\webapps\InfoViewApp\WEB-INF\web.xml (after the idm.princ setting):

<init-param>
<param-name>idm.keytab</param-name>
<param-value>c:\winnt\BOSSO.keytab</param-value>
</init-param>

Step 21

Remove the wedgetail.password option from the Tomcat Configuration Java Options. At this point your Vintela SSO should work with InfoView.

References

I was only able to document the above using the (very) detailed PDF document on Vintela SSO provided by Tim Ziemba at the following SAP Support Note: http://service.sap.com/sap/sapnotes/display/1261835.

If any SAP BusinessObjects staff read this post, it would be fantastic if all this knowledge that is being captured in the SAP Support Portal could be filtered and pushed back into the standard documentation, as this sorely lacks the detail required to implement Vintela SSO.

Yesterday I presented at the SAP Australian User Group’s (SAUG) Business Objects Special Interest Group in Perth, on Summary and Highlights of the MBO 2009 Conference.  As quite a few SAP BO users in Perth weren’t able to make the conference, I wanted to highlight several key presentations I enjoyed.

Here is a PDF of my presentation to view.

Some other highlights of the conference were:

• Pioneer is due in 2010 (combining Voyager with Bex Analyzer)
• Support for 64bit environments coming soon
• New release of BOE due in 2010 as well (XI 4.0?)
• SAP BWA (in-memory datastore) will be updated to allow hardware acceleration of other data sources, not just SAP BW
• BO Text Analysis (as presented by Plaut) looks very interesting, turns unstructured data (blogs, emails) into structed relational data
• Timo Elliott’s recommendation – don’t just implement software, aim high and look to change the business
• Dave Rathbun presented on universe tips and Web Intelligence essentials, such as calculation contexts (often misunderstood), multiple data providers and sub-queries
• Data Services 3.2 due in Q3 2009, will support 64bit Linux, and next release of DS will support 64bit Windows
• Data Services 4.0 will potentially migrate to use the BOE architecture – fingers crossed on my part

It was a great conference with a variety of users and partners present, and I hope to be there at the MBO 2010 conference.

- Josh

I recently had to provide a list of best practices in Universe Design – partly compiled from the SAP BusinessObjects training material, as well as my own experiences.  I believe that the universe is the foundation of the BusinessObjects system.  With poor universe design, required reports are often difficult or even impossible in some cases.

Poor universe design can also lead to wrong data, non-optimal SQL generation, incompatible objects and/or Cartesian products (or very wrong data).

I’ve categorised the best practices into two groups, End-User and Cosmetic methods, as well as Functional or Optimisation methods.

End-User / Cosmetic

1. The universe should be created from the end-user perspective, not from a developer’s perspective. This should be kept in mind whether designing new universes, or modifying existing universes.
2. All metadata (class names, object names and descriptions) should use business terminology. This allows the end users to easily use the universe without understanding the underlying database structure. It also speeds up development time, as header cells do not need to be edited. Users can then also search the universe when building a report to quickly find an object. For example, instead of calling a dimension object ‘Commit Cuml’, it should be called ‘Cumulative Commitment’.
3. All development objects should be removed before promoting to Production. Universes should not contain old, legacy or development objects (and classes) – these should be removed before promoting to Production. If absolutely necessary, these should be hidden and placed in a ‘Legacy Objects’ class so no mistake can be made regarding their status.
4. Objects should not be both dimensions and measures unless absolutely necessary. This is confusing to the user, and unnecessary. Providing row-level data is possible by introducing a unique object, like the ID.
5. Object formatting should always be specified, especially with measures. Formatting like dates, currency and so forth is important to provide objects that the user doesn’t need to format themselves.
6. Date fields should be expanded using SQL functions, and split into separate objects like Calendar Year, Calendar Month, Calendar Month Name etc. A library of date functions for both T-SQL and PL-SQL should be built up and re-used where possible.
7. Classes and objects should be based on user requirements, not table-centric. Classes might contain objects from many different tables, and should not be based on the columns within a table.
8. Custom hierarchies should always be defined and done according to the end-user’s requirements. This prevents users from drilling using default hierarchies, and allows control over what users can drill into.
9. Pre-defined conditions should be used to accelerate report development and encode difficult query filters into an object that the user can use easily.
10. Object names should be unique, and never repeated, regardless of the object classification.

Functional / Optimisation

1. SQL aggregation functions should always be present in measures. All measures should have both a SQL aggregation function (ie SUM, COUNT and AVERAGE), then a projection aggregation function assigned (respectively SUM, SUM and NONE). This ensures that GROUP BYs are used and the processing power of the database is leveraged, returning fewer rows quicker. This also has additional performance benefits at the data provider level, where less RAM will be used on the server, and lower network traffic of data.
2. Tables should not be unconnected. This will generate a Cartesian product for the end-user, and leads to very confusing universes. If tables have no way of being joined at all, they should be placed in separate universes.
3. Left outer joins should be avoided where possible. This is sometimes impossible with transactional schemas, but left outer joins lead to NULLs and slower SQL queries. If left outer joins are necessary, universe objects should contain ISNULL functions or similar so that users aren’t presented with NULL values when using objects.
4. Cardinalities on joins should always be set to allow for loop and SQL trap detection, this includes self-joins that should be marked as 1-1. Always set cardinalities manually, not automatically.
5. Contexts should be used, and if too complex, the universe should be split up. This allows for path navigation and allows a universe to have more functionality.
6. Loops and SQL traps should always be resolved to avoid Cartesian products and incorrect data. This is a very important component of universe design, especially with transactional schemas.
7. Integrity checks and refreshes of the database structure should be done regularly to ensure the universe has the up-to-date view of the database.
8. Views should be used in preference to derived tables, and kept to a minimum. This ensures optimal SQL generation.
9. When aliasing tables, the original should be placed in the top left of the schema window and noted with ‘Aliased Table – Do Not Remove’. This will ensure that an original table isn’t removed by mistake, which will also remove all aliases of the table as well.
10. The query limits should always be set, to ensure that runaway queries or Cartesian product queries don’t bring down the database. Both length of query time and number of rows should be set. The limits will differ per database.
11. Multiple SQL statements for each measure should be turned off, to generate optimum SQL.
12. Index awareness should be used where possible, to generate optimal SQL.
13. List of values should be disabled for all dimension and detail objects that don’t require LOVs, to avoid users doing queries like all IDs for a given table.

Conclusion

Having compiled this list in a short time, some of the best practices are notably short.  For my next post, I will endeavour to single out a few of the above methods that aren’t covered as often and focus on them in more detail.

If you have any suggestions, I’d love to hear them.

- Josh

Update

I received quite a few comments on additional best practices, so I’ve included them here.

• Lock the universe when editing to prevent other users from editing the same universe.  This can be controlled with the security model, so only the user who locked the universe can unlock it.  However, beware that using the Import Wizard still allows you to overwrite a universe that is locked (Jansi).
• Use universe parameters to control how the SQL is generated.  You can control SQL generation so it is ANSI-92 compliant, as well as use JOIN_BY_SQL to allow separate sub-queries to be combined at the query level instead of in the report (Andreas).
• All ratios should be set as database delegated/smart measures.  This ensures that the ratio isn’t calculated incorrectly (ie average of an average) and is calculated back at the database (Andreas).
• Generic time objects for Today, Yesterday, This Month, Last Month etc. should be provided (as dimension objects as well as filters).  These dimension objects can be used in filtering objects (Some Date Object = Today Object).  Using filters may depend on the number of dates present in your data, however you could still create filters for the most common date objects (Andreas).
• As a rule of thumb do not use the WHERE clause in any measure, dimension, or detail object. Use CASE WHEN … SQL syntax instead.  Combining two objects that have WHERE clauses filtering the same column(s) will return zero results, which the user won’t expect (Andreas).

I also recently stumbled across the same kind of post over at the Business Intelligence blog.  There are some differences in our lists, so you may wish to refer to that post as well.

Updated 22/07/2009.

- Josh