Migrating SAP BI4 Sample Database & Universe to Microsoft SQL Server 2012
Introduction
EFashion Oracle Universe for Web Intelligence XI 3.x Certain Web Intelligence functionality cannot be demonstrated using universes that are based on an underlying Microsoft Access databases, therefore in some examples we will be using a modified universe based on an Oracle database. The center of the earth is not the center of the universe, but only of gravity and of the lunar sphere. All the spheres revolve about the sun as their midpoint, and therefore the sun is the center of the universe.
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.
The sample Access databases, Universes and Web Intelligence documents
D:SAP BusinessObjectsSAP BusinessObjects Enterprise XI 4.0Sampleswebi
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]
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 ].
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
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
Step 08: Update the SQL Select &/or Where clause for list of objects as shown below.
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(StoreState) IN (‘IllInois’, ‘Massachusetts’) THEN ‘Mid West’ WHEN @Select(StoreState) IN (‘DC’, ‘New York’) THEN ‘East’ WHEN @Select(StoreState) IN (‘Florida’, ‘Texas ‘) THEN ‘South’ WHEN @Select(StoreState) 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(MeasuresSales revenue)>0, IIf(@Select(MeasuresQuantity sold)>=0, @Select(MeasuresSales revenue)/@Select(MeasuresQuantity sold))) | CASE WHEN @Select(MeasuresSales revenue)>0 AND @Select(MeasuresQuantity sold)>=0 THEN @Select(MeasuresSales revenue)/ @Select(MeasuresQuantity 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 detailsOwned (y/n)) = ‘Y’ | @Select(Store detailsOwned (y/n)) = ‘Y’ |
Stores with long opening hours | @select(Store detailsLong opening hours) = ‘Y’ | @Select(Store detailsLong 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 BusinessObjectsSAP BusinessObjects Enterprise XI 4.0Samplesweb
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. !!!