BO 4.0 FP3: get eFashion and other MS Access datasources working

I’ve just noticed a problem with our IT-Logix Migration Assessment Environment. The problem is with eFashion and other MS Access based demo databases, namely that you get the following error in (online) Webi, both on BO 4.0 SP02 as well as with FP3 – due to 64bit connectivity problems:

You don’t get the error in Webi Rich Client usually. In this post I will quickly outline the reasons for this and how to solve it:

First of all: Others got these errors too:

http://scn.sap.com/thread/2118132

http://scn.sap.com/thread/2043784

The answers from SAP (namely http://scn.sap.com/people/henry.banks) are not really satisfying. Of course it is not very clever to use Access as a demo datasource – but why SAP then provides these (access based) samples in BO 4.0 and not e.g. within the database they include within the setup? Anyway, there are three options you can choose:

  1. Move your efashion and other MS Access databases to a “real” database like SQL Server (Express), MySQL etc. It must be just accessible by 32 AND 64 bit drivers.
  2. Migrate to BO4 FP3 – and read the rest of the blog of how you can get Access databases running…
  3. If you are on BO4 SP2 – sorry, I don’t know a way how to get Access running on a 64bit driver – if you are interested in the reason, read on… (If you know another solution, please post a comment!)

In BO 4.0 still all the client tools (like the Webi Rich Client) use 32bit drivers. Regarding eFashion this is not a problem as any default Windows XP / 7 / Server will provide preinstalled drivers. The BOE setup will automatically create the corresponding 32bit-ODBC datasources. Therefore you’re all fine.

On server side it is important to note that e.g. Webi Processing Server always uses 64bit drivers. As far as I can overlook it as well for MS Access. But these 64bit drivers seem not to be installed by default, at least they weren’t on my cloudshare.com environments. In addition there is a strange thing that the BOE setup creates both, 32bit as well as 64bit ODBC connections for eFashion and club.The below screenshot shows the 64bit ODBC Admin (trust me 🙂

But be careful: Whereas the 32bit ODBC connections work fine at least on my side I got the following errors when I wanted to modify e.g. the efashion connection:

If you want to create a new ODBC connection you will notify there are no 64bit drivers installed for MS Access:

My suggestion to solve this is to go here and download the Microsoft Access Database Engine 2010 Redistributable – because there is a 64bit setup / drivers for this:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

Download the 64bit setup… and run it:

Finally your 64bit ODBC Admin “Add connection” dialog should look like this:

Now you can create the efashion, efashion-webi etc. data sources. Make sure you write it absolutely identical as it is written in the 32bit ODBC connection!

So far everything works fine for both, BO 4.0 SP02 as well as FP3. As usual there is a big BUT: You will still get the same errors shown right at the beginning of this post. Remember, you just installed the Access 2010 redistributable. This means you have to change your universe connection to use the appropriate driver (for this log in to Universe Design Tool and choose Tools – Connections). And here is, where at least I had to say there is no (obvious) way of how to solve it with SP02:

Sorry guys, no Access 2010 support in BO 4.0 SP02. But at least FP3 provides something for us:

And finally it should work. To sum up:

  1. On a BO 4.0 FP3 server install MS Access 2010 Redistributable 64bit
  2. Create necessary 64bit ODBC connection
  3. Modify your universe connections to point to the Access2010 driver
  4. have fun with efashion 😉

PS: I don’t have any issues with our BO 4.0 SP02 environment which has SP02 installed only as a Patch. We installed this environment during ramp-up for SP02 (in these times Webi was still labeled Interactive Analysis, that’s why I noticed the difference…) and only later applied SP02. I didn’t investigate, but it seems like Webi Proc servers uses 32bit drivers here… (no 64bit drivers for access installed on this system…)

PPS: Don’t have FP3 available but you ‘d like to test yourself? I can get you easily access to copy on cloudshare.com – see the corresponding blog post.

Do you have similar experiences? Any other hint I missed? Please post your comment.

Advertisements

10 Responses to BO 4.0 FP3: get eFashion and other MS Access datasources working

  1. Dallas Marks says:

    Thanks for digging to the bottom of this. I guess I assumed Webi made a 32-bit exception for Access (Crystal Reports 2011 is still 32-bit data access), BI4 was buggy, or both. Looking forward to trying your tips over weekend.

  2. hb says:

    Useful content definitely. But I’m not a massive fan of character assasination on blogs.. especially since FP03 / Access 2010 combo just came out…

  3. Our BO 4.0 Sp02 Patch 11 has no issues connecting to Access database using 32 bit drivers. The moment I rename the 32 bit driver, web intelligence throws an error realted to ODBC architecture mismatch between application and the driver. So I assume that Access Db connections are supported only using 32 bit drivers.

    But today I came across accessing these MS-Access DB based universes (UNX ofcourse)using Explorer and Dashboard Design. The error message is “The dataSource cannot be generated for the dataSource name “.

    It makes me fuming and frustrated. The same UNX universe works fine with Web Intelligence (thin and rich). There is no error log generated as well.

    • rbranger says:

      Hi Mohanray

      I had a similar ‘issue’ that it worked somehow with 32bit. But this was only with a system which was based on BO4.0 Rampup version. It never worked for me on systems having used the full install BO 4.0 SP02 (say the version published after the Rampup)

      What base setup did you use?

      Regards
      Raphael

  4. Hi Raphael,

    The base is BO 4.0 SP02. We never had issues with MS-Access UNX universes.

    Mohan

  5. Pingback: Converting eFashion from UNV to UNX

  6. Pingback: SAP BusinessObjects BI 4 is (Mostly) 64-bit

  7. Pingback: Converting eFashion from UNV to UNX | Dallas Marks on Business Intelligence

  8. Pingback: SAP BusinessObjects BI 4 is (Mostly) 64-bit | Dallas Marks on Business Intelligence

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: