Getting Somewhere with Sybase SQL Anywhere and SAP BusinessObjects BI 4.1

SAP Sybase SQL Anywhere 12 is now the default CMS database for

  • SAP BusinessObjects Enterprise XI 3.1 Service Pack 5 and higher
  • SAP BusinessObjects Business Intelligence 4.1

To learn more, I installed SAP BusinessObjects Business Intelligence 4.1 SP2 sandbox using the default integrated database. If you’ve used the bundled mySQL or Microsoft SQL Server Express Edition on previous releases, you’ll want to look into adopting Sybase SQL Anywhere. There is quite a bit of documentation, much of it on SAP web sites. If you use Google’s search engine, you will occasionally find references on the Sybase web site. But it is a shell of its pre-merger self and mostly contains links back to SAP. Here are the basics that a SAP BusinessObjects BI administrator will need to get going with SAP Sybase SQL Anywhere.

I’m new to Sybase SQL Anywhere. How does it compare to Sybase ASE and Sybase IQ?

I found this high-level summary on the Sybase web site.

Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ are complementary products, with architectures designed to suit their distinct purposes.

Sybase IQ is a high-performance decision-support server designed specifically for data warehousing and analytic processing. SQL Anywhere works well as a workgroup or departmental server requiring little administration, and as a personal database. Adaptive Server Enterprise works well as an enterprise-level server for large databases, with a focus on transaction processing.

This section describes architectural differences among the three products. It also describes the Adaptive Server Enterprise-like tools that Sybase IQ and SQL Anywhere include for compatible database management.

For more details, take a look at the SQL Anywhere Community on SCN- specifically this overview shared with me by Lori Vanourek, who works in product management for Data and Analytic Engines at SAP Labs.

The default Sybase SQL Anywhere footprint looks pretty basic. Any administrative tools?

Patrick Perrier has written a tutorial for Installing and Using SAP Sybase SQL Anywhere Database Client on the SAP Community Network

Database Administration Tools for SQL Anywhere CMS and Audit DBs by Jose Ramos on SAP Community Network. An overview targeted for SAP BusinessObjects administrators.

Sybase SQL Anywhere Database Client Download by Jose Ramos on SAP Community Network. This is where you can find the client tools, particularly Sybase Central and Interactive SQL. Sybase Central is a visual interface for administrative tasks and Interactive SQL allows you to run scripts, such as the script SAP provides for the Monitoring Trend DB schema. The client tools also includes 32-bit middleware if you want to do semantic layer design with either the Information Design Tool or Universe Design Tool, both of which require 32-bit middleware.

SAP Sybase SQL Anywhere Developer Center by Karin Schattka on SAP Community Network. You won’t need the developer tools for BI administration, but may want them if you want to become dangerous with SQL Anywhere.

I’m new to Sybase SQL Anywhere. How do I back up the CMS and Audit database?

SAP KB 1790402 – How-to: Backing up (Sybase SQL Anywhere) CMS Repository database

Backing Up the CMS and Audit Databases Using SQL Anywhere Maintenance Plans by Jose Ramos on SAP Community Network

By default, the CMS and Audit databases are created using the all-powerful DBA user. Can SQL Anywhere be secured?

Yes. Visit the SQL Anywhere Community on SCN for additional details.

Can Sybase SQL Anywhere be used in multi-node deployments?

Yes. There’s a veiled reference in section 11.1.1 of the SAP BusinessObjects BI Administrators Guide, entitled To select SQL Anywhere as a CMS database. However, just like any other database platform, we would recommend using the Central Configuration Manager (CCM) to migrate the default CMS and Audit databases on the first node to a dedicated server running only SAP Sybase Anywhere. Of course, the Central Configuration Manager can also be used to migrate to an entirely different database platform as well.

Is there a sample Audit universe for Sybase SQL Anywhere?

Yes, see SAP KB 1776254 – Where to find Activity universe for Sybase SQL Anywhere. If you’re running the Information Design Tool on the BI 4.1 server, be sure that 32-bit SQL Anywhere middleware is installed and create 32-bit ODBC DSN’s for CMS and Audit that match the 64-bit ODBC DSN’s created by the SAP BusinessObjects BI installation program. There’s probably other ways to install the 32-bit middleware, but I checked the box while installing Sybase Central, the administrative tool.

Can I use Sybase SQL Anywhere as the Trending DB for monitoring in place of Apache Derby?

Yes. The script is located at [Install Dir]\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB\sqlanywhere.sql. Install Sybase Central, the graphical administration tool that includes the Interactive DB tool. Then run the sqlanywhere.sql script from Interactive DB. The BI 4.1 Administrators Guide contains the rest of the usual details you’ll need to convert monitoring from Apache Derby to Sybase SQL Anywhere.

What are your plans for Sybase SQL Anywhere and SAP BusinessObjects Business Intelligence 4.1?

One thought on “Getting Somewhere with Sybase SQL Anywhere and SAP BusinessObjects BI 4.1

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.