Connect to an Azure database in Business Objects Information Design Tool using ODBC

I recently had to connect a Business objects-universe to an Azure databas using ODBC.

In short, the following steps needs to be taken. Each step will be explained more thoroughly below.

  1. Set up the ODBC data source on BO server
  2. Set up the ODBC data source on the client/development machine
  3. Set up connection in Universe Design Tool
  4. (Optional) Add access rights in Business Objects

Step 1: Set up the ODBC data source on BO server

  1. On the server where your Business Objects is installed, set up an ODBC data source. You need to add ODBC 64-bit data source, which is located at %windir%\system32\odbcad32.exe
  2. Select “System DSN” and “Add”
  3. Select your driver. In the case of an Azure database, select “SQL Server”
  4. Set your data source name and Server. Format for Azure server address is “tcp:SERVERNAME.database.windows.net”
  5. Select the way you wish to connect to the database. Most likely you will use an SQL authentication, as shown in the picture. There is currently no default database setting for Azure accounts, so you do not want to connect to SQL server to get default settings. (If you use default settings, you will only connect to “master” database!)
  6. Change the default database to the name of the database you wish to connect to
  7. Click “Finish”
  8. If you try to test your data source now you will get an error. This is normal, as you have no credentials entered on the ODBC data source. For now, just press “OK”.

Step 2: Set up the ODBC data source on BO client

  1. On the client/development machine where your Business Objects Information Design Tool is installed, set up an ODBC data source. You need to add ODBC 32-bit data source, which is located at SYSTEMDRIVE:\Windows\SysWOW64\odbcad32.exe (Example: C:\Windows\SysWOW64\odbcad32.exe)
    Remember you will need to do this on ALL client/development machines where you want to use the connection!
  2. Follow steps 2-8 in section above

Step 3: Set up connection in Universe Design Tool

  1. In “Repository Resources”, right-click “Connections” and select “Insert Relational Connection”
  2. Enter a connection name
  3. Select driver. In this case, you can choose Microsoft / MS SQL Server 2008 / ODBC Drivers
  4. Enter database SQL authentication user name and password.
    Enter the data source name (the name you entered in section 1 and 2, step 4)

    You can test your connection if you want
  5. When you are done, click finish. Your connection is now ready to use in universes.

Step 4: (Optional) Add access rights in Business Objects

  1. This sections is here as a reminder: Remember to add access rights to the connection in Business Objects, if there are any specific groups that need to use the connection.
  2. Go to BO Central Management Console / Connections.
    Right click on your newly created connection and select “User Security”
  3. Here you can see which users and groups have access to the connection. If you want to add more users or groups, click “Add principals”.

Good luck!

Advertisements

Bug: Inconsistent results in WebI report

Today I had an issue with a WebI report. It was a small, simple report that acted strangely: Whenever the report was refreshed the result showing in the report was different each time. Some dimensions and measures kept appearing/disappearing in WebI for each refresh.

Background:
WebI report, unx universe. Business Objects 4.2 SP 3
* No limitation on universe in rows returned or time run
* Small, limited sql on only one table
* Sql server query works ok – returns same results every time
* Query window in webi works ok – returns same results every time
* Refresh in webi reports does not work – returns different measures and dimensions appearing/reappearing with refresh

The problem was not the universe or the sql, it was the report filter.
We filtered out a list of values, example “Customer” with a similar filter:
“Customer IN LIST CustomerA;CustomerB;CustomerB ;CustomerC;Customer C ”
This filter was manually selected from a list of values.

Note the spaces at the end of some of the values? This seems to mess up how WebI handles the returned results.
Ok, so what if we change the filter?
“Customer IN LIST CustomerA;CustomerB;CustomerC”
With the new list of values, both “CustomerB” and “CustomerB ” STILL shows up – inconsistently! Sometimes, “CustomerB ” will be missing on refresh and sometimes it will be there.

Solution:
The solution was to trim the dimension from whitespace in universe: rtrim(Customer)

This removed the inconsistencies completely.

I’ve worked with BO for a few years and this is the first time I’ve seen this kind of behaviour from a dimension so unfortunately I’m not sure what triggers it. If anyone can shed some light on why it happens I’d be grateful.