Skip to Content
Author's profile photo Ajay Gupta
Ajay Gupta
August 22, 2015 5 minute read

Migrating SAP BI4 Sample Database & Universe to MS SQL Server 2012

Migrating SAP BI4 Sample Database & Universe to Microsoft SQL Server 2012

Introduction

Two sample Microsoft Access databases and Universe come with SAP BI 4.1.x eFashion and Club. This document describes the step-by-step instruction to migration the eFashion database from Access to Microsoft SQL Server 2012, as well updating the eFashion Universe. The Club database and Universe can be migrated and updated in a similar manner.

Overview

To migrate the database we will use SQL Server Management Studio (SSMS), a tool that creates NEW tables in a SQL Server 2012 schema and imports data from an Access database. Once the database has been migrated we then need to make a several updates to the eFashion universe to use SQL Server 2012 rather than Access syntax.

01_BI4_SampleLocation.png

The sample Access databases, Universes and Web Intelligence documents


D:\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Samples\webi

Prerequisites

First we need to download SQL Server Express 2012 and SQL Server Management Studio tools (FREE). If you already have a licensed site for SQL Server 2012 no download is required. The great thing with using SQL Server Express 2012, it will support 64bit ODBC Drives now required by BI4 Platform.  The SQL Server Management Studio (SSMS) does not need to be installed on BI4 Platform Server as long as the SSMS tool can read the eFashion Access Database.  You can even copy the efashion.mdb file to SQL Server2012 Server.

Link to download SQL Server Express 2012 + Tools [FREE]

http://www.microsoft.com/en-ca/download/details.aspx?id=29062

02_MS Download SQL2012 Express.png

Second we need to installed SQL Server 2012 64bit Native Clients ver11 on all BI4 Platform Server and create 64bit System DSN called eFashion [ http://www.microsoft.com/en-ca/download/details.aspx?id=36434 ].

Download - SQL2012 Client.PNG

Make sure you have proper permissions to MS SQL Server Express 2012 / SQL Server 2012 to create tables and insert data.  You will need a BLANK SQL Server 2012 database called “eFashion”.

Migrate eFashion Database

Import_00.png

Import_01.png

Import_02.png

Import_03.png

Import_04.png

Import_05.png

Import_08.png

Migrate / Update eFashion Universe

Now that we have successfully migrated the eFashion database to SQL Server2012 we need to update the eFashion universe to utilize the SQL Server2012 datasource.

We need update SQL for several Objects and Predefined Condition, see below

Step 01: Launch Designer and go to <Tools>, <Connections> to make sure proper connection exist using SQL Server 2012 driver, called “eFashion_SQL”.  If you already have another connection, you can utilize that one instead.

Step 02: After validating connection, <import> universe from BI4. Import the eFashion universe found under “webi universes”. Optional take back-up of eFashion using Access DB

Step 03: Next go to <File>, <Parameters>, Definition Tab <Connection> and update to use “eFashion_SQL”.

Step 04: Update Description so that we can identify our updated universe, for example:

  • Add comment about using MS SQL Server 2012 Database
  • Update when last updated

Step 05: Next we need to <View>, <Refresh Structure> to confirm all the Tables are valid. You may need to update the table owner depending on UserID being used to connect to SQL Server. Select all tables, right click and select rename table with owner, if needed.

Step 06: Click <Ok> to save changes

Step 07: Run an Integrity Check to view error’s prior to making changes

Access-to-SQL2008_IntegrityCheck.png

Step 08: Update the SQL Select &/or Where clause for list of objects as shown below.

ALL_Objectsa.png

Update Invalid Objects and Conditions

Objects/Condition

Access

SQL2008+

Holiday (y/n)

ucase(Calendar_year_lookup.Holiday_Flag)

upper(Calendar_year_lookup.Holiday_Flag)

Region

N / A

CASE
WHEN @Select(Store\State) IN (‘IllInois’, ‘Massachusetts’) THEN ‘Mid West’
WHEN @Select(Store\State) IN (‘DC’, ‘New York’)  THEN ‘East’
WHEN @Select(Store\State) IN (‘Florida’, ‘Texas ‘) THEN ‘South’
WHEN @Select(Store\State) IN (‘California’, ‘Colorado’) THEN ‘West’
ELSE ‘UNKNOWN’ END

Sales floor size group

IIf(Outlet_Lookup.Floor_space>=1000, IIf(Outlet_Lookup.Floor_space>=2000, IIf(Outlet_Lookup.Floor_space>=3000, IIf(Outlet_Lookup.Floor_space>=4000, IIf(Outlet_Lookup.Floor_space>=5000, ‘5000 +’,’4000-4999′),’3000-3999′), ‘2000-2999′),’1000-1999′) ,’0-999’)

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

Sold at (unit price)

IIf(@Select(Measures\Sales revenue)>0,
IIf(@Select(Measures\Quantity sold)>=0,
@Select(Measures\Sales revenue)/@Select(Measures\Quantity sold)))

CASE
WHEN @Select(Measures\Sales revenue)>0 AND
@Select(Measures\Quantity sold)>=0
THEN @Select(Measures\Sales revenue)/
@Select(Measures\Quantity sold)
ELSE 0
END

Holiday period

ucase(Calendar_year_lookup.Holiday_Flag) = ‘Y’

UPPER (Calendar_year_lookup.Holiday_Flag) = ‘Y’

Sales floor size?

IIf(Outlet_Lookup.Floor_space>=100, IIf(Outlet_Lookup.Floor_space>=200, IIf(Outlet_Lookup.Floor_space>=300, IIf(Outlet_Lookup.Floor_space>=400, IIf(Outlet_Lookup.Floor_space>=500, ‘500 +’,’400-499′),’300-399′), ‘200-299′),’100-199′) ,’0-99’) IN @Prompt (‘Sales flloor size sqFt?’,’A’,{‘0-99′,’100-199′,’200-299′,’300-399′,’400-499′,’500+’},MULTI,CONSTRAINED)

CASE
WHEN Outlet_Lookup.Floor_space  > 5000
THEN ‘5000+’
WHEN Outlet_Lookup.Floor_space >= 4000
THEN ‘4000-4999’
WHEN Outlet_Lookup.Floor_space >= 3000
THEN ‘3000-3999’
WHEN Outlet_Lookup.Floor_space >= 2000
THEN ‘2000-2999’
WHEN Outlet_Lookup.Floor_space >= 1000
THEN ‘1000-1999’
ELSE ‘0-999’
END IN @Prompt (‘Sales flloor size sqFt?’,’A’,{‘0-99′,’100-199′,’200-299′,’300-399′,’400-499′,’500+’},MULTI,CONSTRAINED)

Owned stores

@select(Store details\Owned (y/n)) = ‘Y’

@Select(Store details\Owned (y/n)) = ‘Y’

Stores with long opening hours

@select(Store details\Long opening hours) = ‘Y’

@Select(Store details\Long opening hours) = ‘Y’

Step 09: After update then <Save> and check <Integrity> to make sure all is working properly.

Step 10: Last <Export> to CMS to test updated eFashion Universe.

Step 11: FINISHED

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.  You are also provided with list of samples located at D:\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Samples\web


NOTE: See attached FIXED UNIVERSE & Excel showing SQL changes

Download attached file containing UNV & Excel table.  You may need to change extension from TXT to ZIP for ZIPPED file. !!!

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gavin Lange
      Gavin Lange

      Great stuff,

      Thanks Ajay

      GL

      Author's profile photo Ajay Gupta
      Ajay Gupta
      Blog Post Author

      Gavin,

          I just update my document and added the files to make your life easier.

      • Download ZIP file
      • unzip file
      • change extension from TXT to ZIP
      • Unzip again
      • you will see UNV and Excel file

      Ajay

      Author's profile photo Former Member
      Former Member

      Hello Ajay i installed server and client tools of SAP BO 4.1 SP6 but how do i start to make my 1st report using efashion data which i have no knowledge,is there a step by step procedure all am trying to do here is to create webi report using efashion data.

      Author's profile photo Ajay Gupta
      Ajay Gupta
      Blog Post Author

      Your best option is to either view self teach videos for easy learning &/or other options is to read the how to WebI material:

       

      Tutorial Videos

      https://blogs.sap.com/2010/08/21/official-product-tutorials-sap-businessobjects-web-intelligence-4x

      Product Documents to self Teach

      http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp6_webisl_dev_guide_en.pdf

      http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp6_webi_getting_started_en.pdf

      http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp6_webi_user_guide_en.pdf

       

      Regards,

       

      Ajay

      Author's profile photo Former Member
      Former Member

      Thank you so much, And I do have a question i installed SQL Server Everything looks pretty fine But When i open UDT on 4.1 BO under the tools and connections i couldn’t find the ”eFashion_SQL” So when i opened the existing Connection Which is ”eFashion” in UDT it works But when i test the connection while creating New Universe on the details it says DBMS Engine Is Ms Access 2007, and Network layer is ODBC and Strategies Not Defined So i Am wondering how can i make the Use of SQL Server I installed.

      Author's profile photo Ajay Gupta
      Ajay Gupta
      Blog Post Author

       

      Have you properly Imported your eFashion MS Access DB file to MS SQL Server 2012 DB ?   You will still need to create SQL Server Native 11 32bit & 64bit System DSN to this new MS SQL Server 2012 DB on the BI4 Server.  You can read the IDT Users Guide, these steps are basic 101 for IDT.

      http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp6_info_design_tool_en.pdf

      http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp6_universe_design_tool_en.pdf

      You the have TWO options:

      • Change current eFashion connection from using Access DB to use MS SQL Server 2012 driver
      • Create NEW eFashion_SQL connection and update it to the revised eFashion Universe

       

      Regards,

      Ajay

      Author's profile photo Former Member
      Former Member

      I attempted to follow the instructions above on an installation of BO 4.1 SP1 Edge and BO 4.1 client.  I was able to convert the database to SQL Server, but I wasn't able to perform "Step 02: After validating connection, <import> universe from BI4. Import the eFashion universe found under “webi universes”.

       

      That option would only let me import current respository universes, and I only have the eFashion.unv file.  I have been unable to convert the unv file to unx and publish to the respository, as the data types seem to be different.  For example, the Article_lookup.Article_id has "INTEGER" type in the eFashion SQL database, but has type "NUMERIC" in the Data Foundations (eFashion.dfx).  Every table has multiple incidents such as this, so the "Check Table Structure" integrity checks fail.

       

      I wasn't sure if the file you uploaded had a universe that could be published to the repository, but I wasn't able to see any attachments

       

      Regards,

      Tim Gall

      Author's profile photo Ajay Gupta
      Ajay Gupta
      Blog Post Author

      Tim,

      This blog to help how to update your UNV to new SQL Server.  Once the UNV is working properly then you use IDT to convert the UNV to UNX.   You need to get your Universe working properly first.

       

      Ajay