( Geek2Live.net )

This week I presented at the Mastering BusinessObjects 2010 conference, hosted in Melbourne, Australia.  The conference was organised by Eventful Management, who did a fantastic job.  International presenters included Timo Elliott, Ingo Hilgefort, Dave Rathbun, Rajeev Kapur and Tom Nather.  You can see some photos of the event by Timo here.

My topic was called ‘SAP BusinessObjects XI 3.1 End-to-End Demonstration’, in which I wanted to do a live demonstration of one dataset being used with most of the BOBJ tools, including Data Integrator, Universe Designer, Web Intelligence, Xcelsius, Live Office, QaaWS and Explorer.

Here is a link to the PowerPoint deck, though there’s not many slides.. (Fletcher_EndtoEndSBO_MBO)

I want to provide a walkthrough of the presentation, and include links to the datasets I used.  This should hopefully allow anyone (who has access to the SAP BusinessObjects XI 3.1 environment) to follow my steps.

To begin with, I introduced the dataset I used.  I downloaded it from http://data.australia.gov.au/, and it shows the Number of Crimes in New South Wales, Australia, broken down by Local Government Authority (LGA), Month/Year (over 12 years), Offence Category and Subcategory.  All in all it represents over 1.7 million records, so not a small dataset.  I also extended the dataset by introducing State Region (multiple LGA’s belong to a Region) in another CSV file.

To start with, the first part of the presentation was to use Data Integrator to import data from two CSV files into a SQL Server database, including de-normalising the data into a dimensional model.  You can download the CSV files from the Datasets page of my blog, under the NSW Crimes heading.  You will also need to create a database (I used SQL Server) to host the tables in the Target Datastore.

Here are the steps I following to build the ETL job to move the data from the CSV files to the SQL Server database.

1. Create a new Flat File Format for the ‘NSW Crimes Data 95-08.csv’ (CSV#1) for extracting data
2. Create a new Flat File Format for the ‘Regions and LGAs.csv’ (CSV#2) for extracting data
3. Create a new database to store the extracted data in, and create a new Datastore (I called mine DEMO_E2E_SBO)
4. Create a new Project and Job
5. Within the new Job, create a new dataflow for staging the main source file, using a template table
6. Run the job to stage the data
7. Import the template table and set to overwrite
8. Add a new dataflow for creating the DIM_DATE table, using the Date_Generation transform
   1. Set the date generation from 1995.01.01 to 2008.12.31, monthly
   2. Add the following output columns to the Query transform
      1. Date_SK (julian of Date)
      2. Year
      3. Quarter
      4. Month
      5. Date
9. Add a new dataflow for DIM_LGA, using the CSV#1 flat file format as a source
   1. Add the following output columns to the Query transform
      1. LGA_SK (use gen_row_number function)
      2. State_Region (leave blank for now)
      3. Local_Government Authority
   2. Group by the LGA column
   3. Add in the CSV#2 as another source, combining in another Query transform, with the following changes
      1. Populate State_Region
      2. Make sure the WHERE clause includes LGA=LGA
10. Create a new dataflow for DIM_OFFENCE_TYPE, using the following columns in the Query transform
    1. Offence_Type_SK (use gen_row_num to populate)
    2. Offence Category
    3. Offence Subcategory
    4. Make sure to GROUP BY Offence Category and Subcategory
11. Run the job to create and load the dimension tables, then import each of the dimension tables and set them to overwrite values
12. Create a dataflow for loading the FACT_CRIMES
13. Add in a pivot transform, using the following settings
    1. Pivot sequence column called ‘Seq_Number’
    2. Non-pivot Columns
       1. LGA
       2. Offence Category
       3. Offence Subcategory
    3. Pivot Columns
       1. All month columns
    4. Data field column called ‘Number_of_Crimes’
    5. Header column called’ Month’
14. Add in a query transform, with settings as follows
    1. Add the following columns
       1. Crime _SK (use gen_row_num to populate)
       2. Date_SK – use this mapping = julian(to_date(’01 ‘ || Pivot.Month,’DD MON YYYY’))
       3. LGA_SK – use lookup_ext function to populate with the LGA_SK from the DIM_LGA table
       4. Offence_Type_SK – use lookup_ext function to populate with the Offence_Type_SK from the DIM_OFFENCE_TYPE table
       5. Number_of_Crimes – direct mapping
15. Run the job
16. Import fact table and set to overwrite, and you’re done!

I’ve also exported the entire Target Datastore to an Access DB (also available on the the Datasets page of my blog), as well as exporting the entire job to an ATL file which you can also download.  I created the job in Data Services XI 3.2, so the ATL file won’t be compatible with versions prior to that.

I hope that someone finds the above of value if they are learning Data Services for the first time.  In the next post I will cover the rest of the presentation, so stay tuned!

Service Pack 2 for SAP BusinessObjects Enterprise was recently released, and included some great new features like Web Intelligence Input Controls – see Coy Yonce’s blog post here (needs an SAP login).

However, my frustrations lie in that no SP2 for Edge XI 3.1 was announced.  Remembering back several years, service packs for BOE could be applied to Edge and even Crystal Reports Server, as they were all compatible.

However, it seems that the codebase has started to diverge, or the teams responsible for service packs aren’t coordinated across the different platform offerings.

As a consultant who deploys Edge frequently, this is frustrating considering that there are no discernible differences when using BO Edge compared to BO Enterprise.  Having no programming background at all, I may be swinging wild here, but it seems to me that using license keys to switch features on and off (like clustering, or Federation) would be simpler than diverging what is essentially the same codebase and maintaining separate service pack and fix packs.

I’m not across the success of SAP BusinessObjects Edge in the greater world community, however in Australia it is becoming a very successful platform, and I often need to tell our clients that they have to wait another (estimated) 6 months for a service pack, when the features they require are already available to Enterprise users.

Does anyone else see a need for coordinated delivery of service packs for the BI platform?  Or perhaps someone from SAP can weigh in on why they are delivered separately?

If there are good reasons, I’d love to hear them as most of the annoyance comes from the lack of communication.

– Josh

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.