JDBC connectivity in SAP BI 4.0

If you want to connect IDT(Information Design Tool)  to reporting data sources using JDBC you need a .jar file for that relational database.  Here in this example, I am going to show you how to connect to ORACLE database 11g and SQL Server 2008 databases.  First make sure you have the middle ware /client software installed on the machine where you are using IDT. Also every database vendor will provide their own JDBC connectivity driver software to connect to their databases or you can write your own class definition and use it.

The most common errors that you will see are the following ones when you try to connect to a database using JDBC drivers using IDT:

 Java Class not found in classpath and No suitable driver found (Generic)
 java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver (for Oracle only)
oracle.JDBC.OracleDriver (for Oracle only)
com.microsoft.sqlserver.jdbc.SQLServerDriver (for SQLServer only)

Oracle database

1) Lets create a new relational connection in IDT to connect to Oracle using JDBC drivers. If you don’t find ojdbc14.jar(required for oracle 11g) then you can download directly from www.oracle.com for the version you need.

2) Give it a name and click Next, Select the database middleware driver, Oracle 11.

3) In the above screen select JDBC Drivers and click Next to go to next screen

4) In the above screen specify the user name, password, oracle server host name:port # and oracle service.  Click Test Connection, it may display the error discussed above.  To resolve this  error copy the ojdbc14.jar to the following
 directory : <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\oracle\ojdbc14.jar on the BI Server or a client machine.   If you don’t see oracle directory under drivers directory 
 in the said path then create it and save the .jar file under it.

5) Restart IDT and try creating the JDBC connection again, It should be successful and hence we resolved the error.

SQL Server 2008

Similarly trying to connect to SQL Server 2008  using IDT throws an error shown below.

To resolve the error, download the latest jdbc driver for SQL Server 2008 from Microsoft website (http://www.microsoft.com/en-us/download/details.aspx?id=11774) , As of this writing the file available from microsoft is “sqljdbc_4.0.2206.100_enu.exe”, extract this file to a temp folder on your machine and copy sqljdbc4.jar to the following folder: <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv2008.

After saving the sqljdbc4.jar in the right folder, try to create a new JDBC connection to connect to SQL Server 2008 using IDT the connection was successful.

One last thing I would like to bring is that most of jdbc connectivity errors will be resolved if the classes or jar files are found in the path environment variable.  That is all for today.

What you should know about Stored Procedure Universe in BI 4.0 ?

As we all know there are many advantages of using stored procedures, they are always in my problem resolving kit, as a last resort I have to use it to create one of the complex report that needs to be delivered  as Web Intelligence report. Apart from providing many advantages like transaction control, speed, complex logic support, security and data access, they are powerful enough to deliver complex reports.  My all time favorite tool is to use Universe, I have to use stored procedure to develop a report that takes data from multiple excel files including few database tables,  one other thing I would like to mention is the data from excel file changes everyday.  Luckily Business Objects supports stored procedure universes and I have used it to deliver this requirement.

I would like to bring my experience to all my readers who are interested in learning what stored procedure universe can offer to a developer or an end-user. I would like to point out what is possible and what is not possible with Stored Procedure universe and I hope it should give a clear understanding when one can utilize it.  I am using BI 4.0 with Oracle 11g database.

What is a Stored Procedure?: Sets of SQL statements that are saved as executable files in a database fully parsed.
What is a REF Cursor?: A pointer to a work area in which a result set is stored.

What are the other requirements needed at first place ?

• Create package that defines the REF CURSOR. This REF CURSOR must be strongly bound to a static pre-defined record structure.
• The procedure must have a parameter that is a REF CURSOR type.
• The REF CURSOR parameter must be defined as IN OUT (read/write mode).
• The stored procedure can only return one ResultSet
• Test and run the stored procedure in an SQL Tool like TOAD or SQL*PLUS

Once all of the above requirements have been met, verify that the database driver that you are using works with that version of Oracle.

I am using SCOTT schema from Oracle.  Following is a package emp_package declaration for a strongly typed cursor in Oracle using EMP table.  REF CURSOR emp_row_type returns a result set of type emp%rowtype

Following is a structure of the stored procedure, it shows how to use or refer emp_row_type REF CURSOR data type declared in the emp_package.

The name of the stored procedure is getEmployeesByDepartment, It accepts a parameter value for a department column and returns the data from the EMP table. We will use this stored procedure to build a universe in Business Object. First create a secure connection that connects to the database and also make sure you have selected the checkbox ‘Click here to choose stored procedure universe’ as shown in the screenshot below. This will indicate that it is a stored procedure universe

If stored procedure has parameters, it will now ask to enter information for the Parameter, Prompt Label and what it should do on ‘Next execution’ ?. If you see the screenshot below it shows value column, prompt label and Next execution drop down box. Click OK to save and close this window.

Once all the information is provided, it will display a table in the universe pane as shown below, this table contains all the columns that will be returned by the stored procedure. In this example, I have created a class named Employee with two measure objects like Sal(Salary) and Comm(Commission).  Similar to an ad hoc universe one can use the stored procedure without an issue.  There are no joins, no contexts in the universe, all the business layer logic is stored in the stored procedure itself. 

You can save the universe and export it to the repository.  Now, you can use this stored procedure universe to develop Web Intelligence reports.  You can drag and drop the objects similar to any other Universe. There are few things that are not possible in query panel.  You cannot create additional prompts or add additional criteria in query panel. You can refresh and view the returned data from the database.

Now use the Universe to create a Web Intelligence report.  I have selected all the columns that were returned by the stored procedure.

What is possible in a Stored Procedure Universe ?

    You can change the data type of an object
    You can set object properties of an object
    You can do the check integrity of stored procedure universe
    You can use stored procedure objects in result pane while creating report.
    You can apply sort on an object if required.
    You can apply access restriction for a user or group.
    You can set a prompt value if the stored procedure accepts a prompt.

What is not possible in a Stored Procedure Universe ?

     You cannot use a stored procedure object in the criteria pane in query panel of Web Intelligence report.
     You cannot change the definition of a Universe object, it is disabled or grayed out by default.
     You cannot create a pre-defined filter using a stored procedure Universe object, it is disabled or grayed out in the Universe.
    You cannot INSERT another stored procedure in a stored procedure universe.
    You cannot INSERT a table in the stored procedure universe.
    You cannot call a stored procedure from a Derived Table in Universe.
    You cannot apply INDEX AWARENESS on stored procedure Universe objects.
    You cannot create custom or new objects that were created for certain purpose based on stored procedure objects.

In conclusion I would say that there are limitations of using Stored procedure universe, lot of things not possible at the Universe Level and the universe becomes like a read only Universe. There is a little control on the Universe side which is changing a connection such as ODBC to Native.  Would use this feature only as last resort.  If you have any comments please share it through the comments section below.

Free SAP HANA for 30 days

Sign up here and get 30 days access, Follow this link to get more information

http://scn.sap.com/docs/DOC-28191

If you need assistance you may try these options (1) check out the  FAQ (2) a discussion forum  HANA Forum  (3) send an email to inmemorydevcenter@sap.com.

If you are interested to create your own SAP HANA DB, you can do it on Amazon Marketplace but there is a usage expense that you need to pay.

Amazon Marketplace

Enjoy and Good Luck.

SAP BI 4.0 – Central Management Server ports

In BI 4.0 Central Management Server (CMS) uses two different ports:

a) Name Server Port

CMS by default uses port 6400 to communicate with Designer, WebI Rich Client, Information Design Tool, Business View Manager, application server(s) and may be firewall internal to the organization. In other words CMS listens for the requests on this port, this port is referred as name server port. When the BI server starts all the other BI services will register with CMS on name server port, CMS then sends its request port # to other service to communicate after its registration with CMS.

If the name server port is changed then all the BI services that needs to communicate with CMS has to know on which port the CMS is running to register themselves.

b) Request port
Each BI server binds to a request port, for example the Web Intelligence Processing Server  select its request port number dynamically when the server starts or restarts, unless it is configured to use a specific port number. This port is used by CMS to communicate with the Web Intelligence Processing Server.

For CMS the name server port and request port must be different otherwise the server will not start. If you by mistake set these ports with the same port # and saves it, the next time when the BI server restarts then CMS will not start because of port conflict. It may throw this error in CCM.   (@IRFANCLSTR is the cluster name)

 

To resolve the port conflict follow the following steps: 

  stop the SIA in CCM
  go to properties and startup tab
  click the central management server
  click properties and delete the request port shown below.   

Click OK and restart the SIA.

In conclusion, do not assign the same port # for CMS in CMC Launchpad as it is not advisable. You can also assign static ports to each of the BI services in BI 4.0 stack but that is all together a different topic.

How to use undocumented functions of SQL Server ?

Here in this post, I am going to explain some undocumented functions of SQL Server.  These functions are very useful to get the information about a database or a table quickly as a part of maintenance and other daily tasks.  I regularly use sp_MSforeachtable to count the rows of the tables in our data warehouse and the source systems.  I also use sp_MSforeachDB  to get a count of objects in a database when a production database is refreshed on to a UAT or DEV environments.

  To get a count of objects for all the databases on a Host

sp_msforeachdb ‘select ”?” as DatabaseName, count(*) as CountObjects from ?.sys.objects’

  To get a count of rows from all the tables in a database

sp_msforeachtable ‘select ”?”, Count(*) from ?’

Note:  There are single quotes around the question mark(?) in the above syntax.

Drop Foreign Key Constraints in SQL Server 2008

The following SQL generates ALTER TABLE DROP CONSTRAINT sql for each table in the Database.  This script is used mostly when you try to TRUNCATE the tables in a database.

 SELECT ‘ALTER TABLE ‘ +
       ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ +
       ‘ DROP CONSTRAINT [‘ + f.name +’]’
FROM sys.foreign_keys f
INNER JOIN sys.tables t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

The ALTER TABLE sql statements that it has generated on my database is shown below. I made the image little blurred on purpose.

Don’t forget to press Ctrl+T to generate the sql in Text format in SSMS.

What’s new in Web Intelligence Rich Client in BI 4.0 ?

After installing the BI 4.0 and while exploring Web Intelligence Rich Client tool, I found some of the cool features from Classic are back and some are introduced new.  In brief, navigating in Web Intelligence Rich Client is by different tabs instead of menus which is similar to other tools of SAP.  What does it mean by navigating by tab ..?  Here is an example,  Like to add a new table, you need to click the Report Element tab and then click Table button, similarly if you want a new data provider, you need to click the Data Access tab and then click New data provider button.  It will take a while to get used to it….but definitely I like it.

There are also new features introduced and some features of Desktop Intelligence have been added too.  I have not yet explored the tool in full but definitely came across some good features. Lets find out what I have found so far ? 

 New data sources have been introduced to build a report

Apart from Universe and Excel file, Web Intelligence Rich Client now supports various other data sources like Bex Query, Analysis (Cube), a text file and Web Services. 

 Data preview in Query Panel.

To preview the data in query panel, you need to first select Data Preview Panel on the top, a separate Refresh button has to be used to refresh the data in the previous panel.  You can have a first look of the data itself in the Query Panel and judge whether your query is returning the right data or not.  If not then you can then try fixing it then and there.

  Desktop Intelligence data manager is back

You can see how many rows a data provider returned after it is refreshed from Data view button on top right as shown below.  It will also display the time in seconds that it took to refresh for those who wants to improve the performance of the query.

 

Hiding a column in a report block is back. This makes life much more easier.

You can hide a column by making a right click on a column, you will see a context menu that displays Hide option as shown in screen shot below.

 Hiding a section in report is made easy.

Similar to hiding a column, hiding a section is also possible by making a right click on the section column, here my section column is Year.   It shows Hide option with three choices Hide, Hide when Empty, Hide When..

 

  Multiple Input Controls with multiple values

If you see the screen shot below, you will see multiple input controls on the left that I have created, one with Radio buttons control that displays different quarters Q1, Q2, Q3 and Q4 in the report and the other is a Check Box control that displays different years 2004,2005 and 2006.  Data in the report is displayed as per the selected input control data values….. cool.

 

  Line numbers in Query Script Viewer (aka View SQL in Query Panel)

When you try to view the SQL generated by the data provider after adding the required objects and pre-defined filters, you will see line numbers in the SQL code as shown in the screen below.

That is all for today,  Hope you enjoyed the new features or might have gained some new insight in BI 4.0 Web Intelligence Rich Client.  Please do leave your comments, feedback and suggestions if any. Have a great weekend. 

How to display column values in a single cell in Web Intelligence using Oracle database ?

A similar post has been posted earlier using SQL Server here.  The steps for creating such a report are same for Oracle database.

I am using Oracle 11.2.0.1.0 with Business Objects XI 3.1 – Version 12.3.0.601 on Windows 2003 O/S.

Requirement is to the DeptNo and all its employee names in a single line by each department number.

Create a dimension object in the Universe and name ColumnValue as below:

 

Create a dimension object as a Delimiter for separating each value as below:

Next, Create the actual dimension object EmpName – SingleLine that you want to display on a single line in Oracle as below, In this example I am using the ename column from emp table in scott schema:

Set the properties of EmpName – SingleLine Object as shown below:

The WebI report generates the following SQL

When refreshed the report displays the following results.

To remove the last comma use the LEFT function from Web Intelligence tool and extract only LENGTH – 1 characters.  That is all for today.

How to truncate and load a table from SSIS ?

When you use SSIS for loading data into a database or a data warehouse or a staging database, some times you need to wipe out all the records first and load the new data from the source to target.  This can be accomplished in different ways, but I am going to show you a method that I use very often and may be useful for you and this technique do not have a data flow task and no mappings. 

   Truncate and Load a target table.

My source database is AdventureWorksDW and my target database is SampleDB.  SampleDB is a exact copy of AdventureWorksDW.  I copied the ADW database and renamed it because I need a source and target, as well as I need the same structure of the source tables in target database. .  In this method,  I am going to use a table DimAcount from my source database and target database.

(Please note, I’m assuming some basic understanding of SSIS, so I’m skipping the "how to create a project", etc. stuff and just going to the pertinent parts).

Following are the steps that we need to follow: 

         Step 1: Define connections to Source and Target.
         Step 2: Get the table name and save it in a user variable.   
         Step 3: Truncate the table in Step 2.
         Step 4: Load the Target table ?

Step 1:
Following is a screen shot that shows the two connections defined in the connection manager for Source and Target databases in my project.  

Step 2:
In order to complete this step, I am going to use an SQL query to get the table name, and then the returned result will be stored in user variable that I declared as vSourceTable of type String.

 
Add a ‘Execute SQL Task’,  a query is passed to get the “DimAccount” table from source database. This query will return the table name “table_name”, following are the screen shots for the General and Result Set tab.  (renamed this task as Source – Get the table ?)

Step 3: 
Add another ‘Execute SQL Task’, this task is used to send the Truncate command from SSIS to the target database.  Following are the screen shots for the General and Expressions tab. (renamed this task as Target – Truncate table)

Step 4:
Add another Execute SQL Task to send an INSERT command (DML statement) to the target database, this will directly load the data from source to target.  This technique will work when there are same no. of columns exists in both source and target tables.  (renamed this task as Target – Load table )

At last the SSIS job will look like below.  Execute the package, At this point, you should be able to successfully execute your package. Upon successful execution, the package will get the table, truncates and loads the target table. 

That’s all for now. Hopefully this gives you an idea of how easy to dump the source data into the target database using SSIS. 

How to display more than 1000 values in a LOV created in Crystal report or Business View Manager ?

The following resolution involves editing the registry. To increase the maximum number of values available in a dynamic parameter list, you will add a registry key. Contact your Network Services to make changes to the system registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system and whole lot of other required stuff. Use the Registry Editor at your own risk.

It is strongly recommended that you make a backup copy of the registry files (System.dat and User.dat on Win9x/WinXP/2003/2003/Vista/Win 7 computers) before you edit the registry.

   Step 1. Create a registry key LOV under DatabaseOptions in the following path HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal  
       Reports\DatabaseOptions\LOV.  See the screenshot below:

 

   Step 2. Add a string value "MaxRowsetRecords" and set the value to the maximum number of values that you desire for your report. For example, a value of 3000 will return up to 
      3000 values in the lowest level of a cascading LOV parameter. In the above screen a value of 60000 is set as it was required in the environment and it works perfectly fine.

The value 0 (Unlimited) will not work with BusinessObjects Enterprise XI or Crystal Reports Server XI, you must specify another value.

NOTE: After making changes to the registry, restart the affected service or application as required.