Common Questions about Connectivity with SAP

Now that SAP has clearly and unambiguously stated that BusinessObjects is the new face of BI for SAP, we have many customers who want more information about connectivity was SAP and are surprised when we recommend that they connect to an existing BW Query instead of to the entire Infocube.

Why is connecting to SAP BI/BW Queries a best practice?

There are several reasons for connecting via SAP BW Queries:

• Performance - The BW OLAP Engine performs its processing against the query cube. In the event, you connect an external analysis tool directly to an InfoCube, a query cube consisting of all the characteristics and key figures in that InfoCube is generated on the fly in order to handle the request processing. This process adds significantly to the processing time of the request made by the end-user when compared to reporting against a pre-defined BW Query.
• Capabilities - There are several capabilities exposed via the interface to BW Queries that are not exposed when connecting directly to the cube such as calculated and restricted key figures to name a few.
• Flexbility - The BW Query Designer offers an extension to the data modeling environment in which changes are relatively easy to make as compared to the effort required to change an InfoCube.

Is this required across all the reporting tools? (WebI, Voyager, Crystal Reports)

Its mostly applicable to WebI and Voyager. There’s more flexibility with Crystal since it has more connectivity interfaces to SAP and it is not designed to be an ad-hoc reporting and analysis tool. Xcelsius will typically be built against scheduled content since most dashboard data is usually only updated every 24 hours.

What are the advantages and disadvantages of this different approaches to SAP Connectivity?

Advantages of BW Query - The BW Query can take advantage of Query capabilities such as CKF and RKF. The BI Team can also provide better control over how much data user can request.

Advantages of InfoCube - The BI Team can expose entire dataset to user without additional work from BI team

Disadvantages of BW Query - Using BW Queries requires additional work from BI team to setup queries specifically around areas of the business, e.g. marketing, sales, etc. It limits amount of information available to user for a given query. That being said, the adhoc report designer can combine multiple queries within a single report. In addition a well defined BW Query can serve as the data sources for multiple reports. (whereas traditionally in SAP BW, we saw 1 query = 1 report)

Disadvantages of InfoCube -Users can get lost in the large amount of data available to them. There is a lack of CKF and RKF, which will lead to users creating more filters and calculations at the report level. These efforts will likely be duplicated across multiple reports. Duplication leads to duplicate work and the potential for different answers based on different formulas (i.e., no “single source of the truth” for those formulas)

Performance of BW Query - Better. Because of the constraint on information available to the end-user, the query design can test a subset of data and guarantee a certain level of performance.

Performance of InfoCube - Vulnerable. Because the entire cube is visible, there is no what to know what combinations of information the user might pick. Without specific controls over the type and amount of data returned, it is impossible to determine who long the query might run.

Flexibility of BW Query - The BW Query is more flexibility in regards to exposing “processed” data (CKF and RKF). It also provides flexibility when it comes to making changes to underlying data because not every change to the InfoCube will impact a BW Query.

Flexibility of InfoCube - The Infocube is the most flexible with regards to the data exposed.

What data structures in SAP are supported within BusinessObjects?

WebIntelligence/Voyager supports: Standard and Transactional InfoCubes, Remote InfoCube (not recommended due to performance), MultiCube, MultiProvider.

Crystal Reports supports: Standard and Transactional InfoCubes, Remote InfoCube (not recommended due to performance), MultiCube, MultiProvider, ODS, R/3 Infosets, ABAP Queries, ABAP Functions. ABAP Data Clusters, Transparent Tables, Pool Tables, Cluster Tables, zTables, InfoViews

Conclusion

It’s been exciting to see how quickly SAP customers have been able to unlock the data that they have in there Business Warehouse to a whole new groups of users.  Users can now create their own reports from scratch using the WebIntelligence interface.  Executive users and front-line contributors can have their personalized data delivered directly to the desktop.

I can’t wait to see what will unfold within our next generation of tools.

Set Analysis Part 5a: Custom Groups in WebIntelligence

I recently had a customer who was in the process of migrating from a legacy version of Microstrategy to BusinessObjects. They had been able to migrate over most of their core reports, but there were several reports that required a number of custom groups.

Microstrategy’s approach was to allow the customer to define any number of groups and filters from with the Microstrategy Desktop tool. These global filters and group definitions can then be leveraged by the report editor for building the report. Microstrategy’s SQL generation engine uses multi-pass SQL when creating the reports. Multi-pass SQL means that a single SQL statement cannot deliver back the correct results, so the report data is written into temporary tables. These temporary tables are then combined with additional queries to obtain the final results.

In the case of BusinessObjects, it wasn’t going to be possible to take the same approach. BusinessObjects does not generate multi-pass SQL. Instead, BusinessObjects focuses on creating extremely complex SQL in a single SQL statement. In most cases, this works just as well, but this was an exception. This sales report used a combination of multiple filters at multiple levels in the heirarchy. The Heirarchy was also defined within the custom groupings and not within the database.

The right answer is: Modify the Data Warehouse and ETL processes to make the underlying data better fit your reporting requirements. You see, the problem was that these custom groups should be defined within the data warehouse natively. This would allow for simplified SQL that can be run in a single pass. Translation? Reports the use custom groups will run a heck of a lot faster.

In my situation, the customer wasn’t going to be able to make these changes, but still wanted to migrate the report to BusinessObjects.

The best answer is: Use Sets. Sets gives you the ability to generate those custom grouping within the data warehouse. Unlike Microstrategy which will run the same multi-pass SQL over and over again, sets will allow us to “cache” the previous passes using sets and run single-pass SQL. We can then tie the set tables into the data warehouse and add the custom groupings to our universe.

The advantage of sets over ETL is that it can be much more dynamic. Sets can allow an analyst to create a new set in a matter of minutes and then see a report broken down by the new custom group.

In my case it was just what the doctor ordered. By introducing sets and adding the set tables to the BusinessObjects semantic layer, we now have access to the sets, which in this case, the customer called custom groups. It was perfect.

Now, not only will the customer be able to migrated this complex report from Microstrategy to BusinessObjects, but they will also be able to run the report much faster than ever before because the report will be generated through a single-pass process.

Tomorrow, we’ll take a look at how to set this up using our simplified data.

APOS: Providing the Missing Piece

When I was growing up I always enjoyed putting puzzles together. I loved the challenge of putting all those pieces together so that I could see the whole picture. Sometimes I would borrow a puzzle from a friend and as I was close to finishing the puzzle, I would discover 1 or 2 puzzle pieces were missing. The puzzle was incomplete.

From a BI perspective, APOS has been a long partner of BusinessObjects. My first opportunity to work with APOS came while I was working at Crystal Decisions. Our next generation Crystal Enterprise product was new and although it provided a great framework for the future, there were a few pieces missing from our complete BI picture. Thankfully, APOS developed a number of powerful solutions to fill those gaps so that our customers didn’t have to wait until tomorrow’s release for the functionality they needed today.

Do you feel you are missing a piece from BusinessObjects XI 3.0?

Business Objects has many great partners, but probably non-better than APOS. In January of this year, APOS was awarded the Business Objects Technology Partner of the Year Award for 2007. They have used the BusinessObjects SDK more extensively than any other BusinessObjects partner. They don’t just one solution. Their tools provide dozens of potential missing pieces that might fit your BI puzzle.

My Favorite APOS Tools

So what’s so great about APOS? Well, here are my top 3 list favorite solutions:

1. InfoScheduler - mass scheduling - dead simple. Scheduling has always been one of those areas were BusinessObjects added a few new features with ever release. Unfortunately however it’s not an area that tends to get a lot of attention. It was only in XI 3.0 that we introduced native bursting within the platform. InfoScheduler provides an Excel-based report scheduling tool that allows mass scheduling of reports from an Excel spreadsheet. How easy is that? Can you say zero training? Users can copy paste, import, export data into Excel to provide scheduling for 10, 100 or 1,000s of reports.
2. Object Manager - Although the CMC is a comprehensive administration console, administrators of large reporting environment often struggle with managing this volume of information. In XI 3.0, BusinessObjects introduced a number of new enhancements which made working with multiple objects much easier; however Object Manager takes it to the next level. You can manage users, groups, reports, security and even the report life cycle process. Object Manager provides power, ease-of-use and flexibility.
3. KPI - One of the most common requests I hear from customers is the ability to do reporting off their CMS. BusinessObjects XI provides an auditing database to allow customers to report off of user activity, but how do administrators report off the objects within the CMS? With KPI, APOS allows you to extract all the XML metadata that is stored within the CMS to an external relational database. Now you can create reports that allow you to:

• Analyze Business View and Universe configuration information
• Examine Crystal and Desktop Intelligence report structure
• See How many reports use the database field profit
• Show CMS Statistics
• Discover how many reports are scheduled to run tomorrow.

You can even set an automatic schedule to refresh the external metadata database on a regular basis. It’s your own mini-datamart for reporting off your BI system. You have to see it to believe it.

Southwest is using all three of these tools and several more!

Can’t Find Your Missing Piece?

If you’ve look through the APOS website and you can’t find the type of functionality that you feel is missing from BusinessObjects, call APOS anyway. Necessity is the mother of invention. Each of the solutions that APOS provides today was developed from the real-world needs of customers just like you. APOS would love to talk to you about extending BusinessObjects in a way that they may not have thought of.

Puzzle Complete

As a kid I was rarely able to find the missing puzzle piece, but in the area of extending BusinessObjects, APOS continues to deliver. Their depth of experience means they understand how BusinessObjects works today and how to make it work better for you tomorrow.

Tip: XI 3.0 Administration Best Practice

If you’ve had a chance to use the new BusinessObjects XI 3.0, one of the first things you will notice is that all your services are now managed by a local process called a Server Intelligence Agent. This agent is responsible for starting and stopping all the services that it manages.

After installing BusinessObjects XI 3.0, the first thing I did was start the CCM (Central Configuration Manager). I noticed I only had two services running.

Where are my services?

They are now managed based on how the Server Intelligence Agent. I don’t want to get into the details of what the Service Intelligent Agent does. This is clearly explained in the documentation. Instead I would like recommend to you a new best practice with the SIA.

But the CMS is King right?

In the XI 3.0 system there is a bit of a catch 22. The CMS is still king. Nothing works without the CMS. When the SIA starts up, it checks to see if it has a local CMS or not. If it does, it will start the CMS. After the CMS has started, it will then read the list of servers from the CMS database that it manages. Finally, it will start all those services. If there is no local CMS for a SIA, the SIA will wait for a remote CMS to start so it can read it’s list of managed services from the CMS and start them up.

But what if the CMS won’t start? What if I only want to start the CMS?

We need to manage this potential occurence.

Recommended Best Practice

Create a new NODE with a CMS to be used for maintenance only.

I strongly recommend that you create a second NODE for each server which will have a local CMS. There may be a situation when you want to bring the system up in maintenance mode and do some work without all the other services starting.

This also gives you the opportunity to start another CMS should you have a problem with your primary CMS. Here are the steps for creating a new NODE with only a CMS.

From within the CCM choose the Add Service Intelligence Agent from the menu. The SIA, like the CMS, needs a specific port to be assigned to it. It cannot pick a random available port. For the name, use the same name as your primary NODE but add the “_MAINT” to the end. Next pick an available port. In my case I used 6501.

Choose Next and fill out the CMS login information so that the configuration information for your new NODE can be written to the CMS database and choose Finish.

Although services can be added from the web based Central Management Console (CMC) interface, let’s start the SIA now. If you look at the properties of this SIA, you will see that it is associated with a remote CMS. We want to add a new “local” CMS to this SIA. (This means the SIA will have it’s own CMS to manage).

Go to the web based CMC and navigate to the Servers screen and see the new NODE we just created. Now we are going to add a new CMS server.

There are a number of ways to add a new server. Here I have highlighted three.

You can select the new NODE and choose the icon, drop-down menu or the new right-click menu. I love this new Web 2.0 that makes the interface work like a traditional windows environment. It provides a huge productivity gain.

Next, you are prompted to choose the server to add. We want to choose Core Services and Central Management Services. Next you will have an opportunity to add optional, additional services. The CMS supports an additional single sign-on service. I recommend you add that as well.

Now you need to tell which NODE to associate this service too. We want to chose our new _MAINT NODE.

The message is warning us that without the correct database configuration and setup, the CMS will not start. In our case, we have never set up a CMS on this NODE, so this is a warning we should heed. We will not be able to successfully start the CMS without additional setup.

Choose Create.

Unfortunately when setting up this service we don’t get a chance to change our final settings before the service is created, so after we create the service, we need to got back into the properties of the service and adjust two important settings.

Open up the properties of the new CMS and modify the listening port and the automatic start-up settings. The default port for the CMS is 6400, however my default CMS might already be running on this port, to temporarily change this to port 6401 - ONLY for testing. I will change it back to 6400 as soon as testing is complete.

NOTE: if you are ONLY going to use this SIA when the other SIA has been stopped, then you may use 6400 but understand that when you attempt to start this CMS, if another CMS is already running on port 6400, then this CMS will not be able to start. If you will want to use two CMSes at the same time, then you will need to use two different ports.

In addition, make sure and automatically start this server when the Server Intelligence Agent starts. Remember, when I manually start my new SIA, (MYXI30_MAINT) I will need a CMS to start as well.

Configuring the Database Connection for the CMS

Now there are TWO WAYS to properly configure the database connection. The fast and automatic way or the slow and manual way.

Option 1 - Fast and Automatic

The fastest way to setup the connection information for your CMS is to find an existing .dbinfo file for a working CMS and NODE combination and copy the file and rename it to the name of your new CMS.

In our case, the name of my working NODE with an associated CMS was MYXI30_ALL so the name of the associated .dbinfo file was found under: C:\Program Files\BusinessObjects 12.0\win32_x86 and is called _boe_MYXI30_ALL.dbinfo.

Copy this file and rename the file to the name of your new NODE with the _MAINT. In my case the node will be called _boe_MYXI30_MAINT.dbinfo.

Now when you start the CMS it will have the data necessary to connect to the CMS. Next time your new NODE starts, so will this CMS.

Option 2 - Slow and Manual (and ultimately the “right” way)

In order to manually configure the connection information for the CMS so that it will know how to connect to the CMS, you need to use the CCM. The CCM is the only tool that will allow you to configure the connection information. This cannot be done from within the CMC. From the CCM on the server, make sure the new SIA is NOT running.

Next, choose the Specify CMS Database icon so that we can define the CMS connection information for our new NODE.

You may receive an error message. This is normal.

Just choose OK to continue. Eventually you will get the prompt to configure the connection for the CMS database. We do NOT want to create or recreate our CMS, we simply want to update the data sourse settings, so choose the first option and choose OK.

You will be prompted to choose the associated database driver for your CMS and to enter the credentials.

Once this as been completed, a .new file will be created in the C:\Program Files\BusinessObjects 12.0\win32_x86. The file will be called _boe_NODENAME.dbinfo. In my case it is called _boe_MYXI30_MAINT.dbinfo.

If you are using an auditing database you may also wish to configure this by choosing the auditing configuration and following similar steps for that database.

When you are finished start the SIA and the CMS will start as well.

Conclusion

Remember, if the CMS does not start automatically, check the settings in the CMC for the service and make sure that the new CMS service is configured to start automatically when the SIA starts. This is a parameter that can be set for each service.

Hopefully this has been helpful in explaining some of the new best practices around XI 3.0 Administration. In addition I hope this will give you a quick introduction to the SIA and it’s relationship to the CMS. Your new “CMS-only” node will be great when you need to just bring up the CMS so you can access the CMC.

Enjoy.

The Big Picture

I recently ran across a post of a good friend of mine who was talking about the BusinessObjects Product Suite at a very high level. Overall in this blog I assume that you are familiar with many of the tools in SAP BusinessObjects, but in case you are not, I wanted to refer you to this nice overview article.

You can access it here.

When we talk with customers, we always talk about the solution that SAP BusinessObjetcts is going to provide to their business problem. That’s what it’s all about. How do we help an organization: increase shareholder value, decrease expenses, reduce risk, increase productivity and performance, etc. We do that through leveraging the technologies of the BusinessObjects Suite to deliver intelligent information (and ultimately value) to the customer.