Migrate SAP BusinessObjects sample databases to Microsoft SQL Server

Introduction

Two sample Microsoft Access databases come with SAP BusinessObjects Enterprise: eFashion and Club. This article describes the steps involved in migrating the eFashion database to Microsoft SQL Server. The Club database can be migrated in a similar manner.

Overview

To migrate the database we use Microsoft’s SQL Server Migration Assistant, a tool that creates tables in a SQL Server schema and imports data from an Access database. Once the database has been migrated we then need to make a few updates to the eFashion universe to use SQL Server rather than Access syntax.

An easier method would be to use an ETL tool such as SAP BusinessObjects Data Services however purchasing such a tool for a fairly straight forward migration is probably not worth it. Certainly if you already have this tool in your company then use it otherwise the procedure below works just as well.

Prerequisites

First we need to download the SQL Server Migration Assistant (SSMA) software from Microsoft. This software requires Data Access Objects connectivity in order to read the Microsoft access database. If you do not have this then when installing SSMA it’ll prompt to say so and provide a download link.

SSMA does not need to be installed on the machine hosting SQL Server nor on the BOE server. If installed on your desktop you will need a copy of the eFashion Access database file on your desktop and also to be able to connect to SQL Server.

Once installed you can launch the application and the first time it is used it will prompt for a license. This license is free an can be obtained if you have a Microsoft Live ID or you have to sign up.

The last requirement is to create a target database in your SQL Server database server called say ‘eFashion’ or something suitable. Also make sure you have an account which has permissions to create tables and insert data.

Migrate Database

Once all software has been installed we can proceed with the migration,

1. Launch SSMA and a Migration Wizard will start.
2. Enter a name and location for migration project – this can be anything.
3. In next window click to add an Access database. Browse to your eFashion database, there are two that come with BusinessObjects and these can be found in the Samples folder in these locations,

[install folder]BusinessObjects Enterprise 12.0Sampleswebi
[install folder]Business ObjectsBusinessObjects Enterprise 12.0SamplesenUniverseSamples

4. We want to use the eFashion database in the webi folder so add this *.mdb file.
5. Click next and select all tables from eFashion database. Note the temporary table whose name begins “~tmp” isn’t required and so can be omitted.
6. Click next and log on to SQL Server. Log on to the eFashion database we created earlier using the database’s owner account. Note, you may need to use full server name such as [server name]SQLEXPRESS
7. Click Next. Don’t check Link Tables option and click next again
8. The application will prepare the migration after which a dialog will display a list of tables that will be migrated.
9. Click OK and the migration process will now execute
10. Once complete you can now exit

Migrate Universe

Now that we have created an eFashion database in SQL Server we need to update the eFashion universe to point to SQL Server and we also need to update some objects and conditions that are using Access syntax

Update Universe

First we should backup the original Access based universe

1. Launch Designer, import universe fro m BOE. Import the eFashion universe found under “webi universes”
2. From file menu select Save As and save under a suitable name such as ‘eFashion Access.unv’
3. Close this universe and import the Web Intelligence eFashion universe once more.
4. From Tools menu select Connections and create a new connection pointing to you SQL Server database server. Note, if may already have such a connection for another universe and if so we should reuse that
5. Once created we can close the Connections dialog
6. From File menu select Parameters.
7. Change connection to use this SQL Server connection.
8. Update Description so that we can identify our updated universe, for example, increase version number or add “(SQL Server)” at end.
9. Click OK to save changes
10. Select all tables, right click and select rename table
11. Update owner to dbo and qualifier to ‘eFashion’ or whatever name you used for your SQL Server database you imported to.
12. Run an Integrity Check.

Update Invalid Objects and Conditions

The integrity check should flag all objects and conditions that fail parsing. It should also pick up that a table has changed. The steps below describe how to fix these issues.

1. Update Product_Promotion_Facts table. From View menu select Refresh Structure. The table should be updated with new column. This column is not used by any object and so no further updates are required.
2. Expand Time Period class and edit the Holiday (y/n) object. Replace the ucase() function in the select clause with the SQL Server equivalent upper() function.
3. Edit the object Store / Store Details / Sales floor size group. Here Access uses an iif() function and we convert this to use the SQL Server CASE statement. Replace the select clause with,

CASE
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space  > 5000
    THEN '5000+'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 4000
    THEN '4000-4999'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 3000
    THEN '3000-3999'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 2000
    THEN '2000-2999'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 1000
    THEN '1000-1999'
  ELSE '0-999'
END

4. Edit the object Product / Sold at (unit price) and again we replace the iif() function with,

CASE
  WHEN @Select(MeasuresSales revenue)>0 AND
       @Select(MeasuresQuantity sold)>=0
  THEN @Select(MeasuresSales revenue)/
       @Select(MeasuresQuantity sold)
  ELSE 0
END

5. Remove the object Number of Stores – this is not used.
6. Edit the Condition Store / Sales floor size. This uses an if statement and an @prompt() to allow the user to chose a floor space size. Note, the if condition groups the floor size in hundreds but the data in the table is in the order of thousands. So we replace the if statement with the following,

CASE
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space  > 5000
    THEN '5000+'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 4000
    THEN '4000-4999'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 3000
    THEN '3000-3999'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 2000
    THEN '2000-2999'
  WHEN eFashion.dbo.Outlet_Lookup.Floor_space >= 1000
    THEN '1000-1999'
  ELSE '0-999'
END IN @Prompt('Sales floor size?', 'A',
    {'0-999', '1000-1999', '2000-2999', '3000-3999', '4000-4999', '5000+'},
    Multi, Constrained, Persistent)

7. For Time Period / Holiday period replace ucase() function with upper().
8. The condition Store / Owned stores – this condition fails parse but is valid SQL. This may be due to an invalid character from the migration however by making any trivial change to the entered formula, e.g. adding and remove a space at end, will fix the issue
9. The Store / Stores with long opening hours condition also has same issue as Owned stores above and fix in same way.
10. Integrity Check once more and if all is well then we are done.
11. Finally export universe back to repository.

If you have any documents that are created against this universe then check that these are OK by editing the query and executing. Remember to save updated report.

What about conversion to Oracle?

It is also possible to convert Access database to Oracle and this article Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2 describes the steps involved. I haven’t tried this but the process looks well documented in the article. Again once the database has been converted the universe will require updating to use Oracle syntax.