David Taylors Business Intelligence Blog

Are you used to the new Xcelsius 2008 yet?  The release of SP1 has definately been a big improvement.  Like many others, I struggled with the new and improved Xcelsius.  There were a few too many quirks for me and until the release of SP1, I went back to Xcelsius 4.5.

Besides the fact that there are several “little things” that work differently between Xcelsius 2008 and 4.5, I did notice a couple of bigger things.  One in particular was the Refresh on Load feature.  I found that there was a big difference between setting the data connection to refresh on open and setting the Connection Refresh button to refresh on load.

Although I did discover a whitepaper that eventually explained it all to me, I ended up figuring this one out on my own.  I should have asked myself early on why there was a ‘refresh on open’ on both the data connection AND the connection refresh.

Data Connection – Refresh on Load

When the SWF file is launched, data is retrieved from the spreadsheet and any data connections, and then dashboard components are generated.  The Refresh On Load option forces the Data Connection to retrieve data from the source before the components are generated.

What’s the downside? The downside I discovered is that you cannot use values from the controls within the Xcelsius model to drive the prompts in your web services because in reality, the controls haven’t been ‘created’ yet.  I found that even though I had default values of “All” hard-coded in my model, I could NOT get my web services queries to fire.  Instead I would get the message:  All prompts are not filled.

In order to avoid this message, you can NOT refresh the data connection on open.  Instead you much create a connection refresh as an object on your Xcelsius model.

Connection Refresh – Refresh on Load

Connection Refresh works like it did in Xcelsius 4.5.  I wrongly assumed that I would no longer need to use a connection refresh in Xcelsius 2008, but due to the issue of not being able to set values for prompts, I found they were still necessary.

BUG?  I’m not sure if it’s a bug, but I also found that if I evoke the dynamic visibility on a Connection Refresh and the object is hidden, then the refresh on open does not work.

Here is an example (the connection refresh buttons are hidden)

Then the user clicks on the setup icon and they are exposed

From a demonstration perspective, I like to hide my Connection Refresh buttons and only display them once the user clicks in a specific area to view them.  In my case I was unable to get the refresh on open to fire on a Connection Refresh if at the time the model was opened, the object was hidden.

NOTE:  To work around this bug, I added a “transparent” button at the top of my model which was NOT hidden.  It was transparent the user could not see it.   When the model opened, the transparent CR button refreshed my web services.  This allowed me to keep my buttons “hidden” but get past the refresh bug.

Final Alternative

Creating an extra step in the lookup logic will make it possible for you to be able to use the data connection – refresh on load AND have the prompts work correctly.  Instead of having the prompt use the target cell of a selector (e.g. B2) , create a second cell that says:  =IF(ISBLANK(B2), “Default Value”, B2) and have the prompt use it instead.  This way, even if the selector value is blank, because the object has not yet been created, the prompt will always have a valid value.

Click here to download sample. BTW,I did not include the database and web service for this sample.

In Crystal Xcelsius 4.5, I was so accustomed to having my web service query prompts feed from the target cells, that it took me a little while to realized that this formula would fix the problem as well.

Summary

Based upon these findings, here are my thoughts:

• If you want the model to come up faster (before the data has been retrieved), then always use a connection refresh – refresh on open.
• If you want to model to have the latest data at the time it opens, then always use a data connection – refresh on open.

In general, my personal preference is to use the connection refresh because I think it’s better to allow the user to see the model as soon as possible and then wait for the refresh of the data.  I also like it because it means i don’t have to update my old Xcelsius 4.5 models to use the interim calculation I describe above.

Again, here is the whitepaper just in case you want to read the full details.

Hope this helps you as much as it helped me.

«Good BI»

So you may be asking…

What is Crystal Reports Embedded? Crystal Reports embedded is an architecture which allows the Crystal Reports Rendering engine to run within the confides of the application environment.  BusinessObjects provides a .NET runtime and a Java Report Component (JRC) which allows developers to embed Crystal Reports in their applications.  Crystal Reports Embedded is included free with every version of Crystal Reports we sell.

So why do I need Crystal Reports Server or BusinessObjects Enterprise?  Well, that leads me into a discussion of the limitations that existing within the “free” embedded report architecture.

Limitations of Embedded Reporting

The current Crystal Reports Embedded Runtime is limited to three concurrent user requests (e.g. three simultaneous processing threads).  These threads are queued such that if the .NET application requests a 4th thread, it must wait until one of the existing three completes before it will be processed by the embedded engine.

Here is a quick view of the Embedded Architecture:

In case you were not aware of this limitation, let me point you do an excerpt I found on the Crystal Reports Developer website:

• Report engine is embedded.

  In Crystal Reports for Visual Studio, the report engine is embedded in the application.

  This embedded report engine is limited to a maximum of three simultaneous user requests. This is not an arbitrary licensing limit; it is a limitation of the embedded report engine architecture.
• Report engine is extracted into a separate report server process

  In the solution that uses the unmanaged Report Application Server (RAS), the report engine is extracted into a separate server process. That server process can run on the same machine or be placed on separate physical hardware, to increase performance by offloading processing from your application server. An unmanaged RAS server is no longer available as an upgrade option. It is recommended that you upgrade to Crystal Reports Server instead. For more information, see Upgrade Options.

  The Crystal Reports Advanced Developer edition provided a performance-governed version of the unmanaged Report Application Server (RAS), which is suitable for smaller deployments with less complex reports and fewer user interaction requirements. This product is no longer available.

  Or, it was possible to license the unmana ged Report Application Server (RAS) on a processor basis, where it takes full advantage of your hardware. This product is no longer available.
• The separate report server process is encapsulated into a complete Enterprise architecture

  In the Crystal Reports Server or BusinessObjects Enterprise solution, an entire Enterprise architecture is added, which encapsulates the separate report server process (RAS). The managed Report Application Server (RAS) becomes only one server, among many other servers in the Enterprise architecture. This architecture provides a rich set of additional features, such as scheduling, load balancing, fail-over, and both vertical and horizontal scalability.

NOTE:  The red was added by me for emphasis.  For more information click here.

So there it is.  There is a limit of three simultaneous processing threads when you use Crystal Reports Embedded within a .NET or Java based application.

What if I need more scalability?

If you look over the product offerings available from SAP BusinessObjects, you will see that all the current offerings include the BusinessObjects platform. This means that you will need to purchase one of our server based product offerings in order to provide additional scalability.

There are a number of advantages to this platform.  The first is unlimited scalability.  One common problem we had in moving customers from an unmanaged environment to a managed environment was that certain parts of their application had to be rewritten.  With the platform, you need to “login”, retrieve your report from a “repository” (instead of a filesystem).  You also wanted to make sure you are using the Page Server instead of the RAS Server (which had more overhead).

Now by requiring customers to move to a repository/platform based platform earlier, they are able to reap the benefits of a dedicated reporting environment

Here is what a basic architecture might look like if you upgrade to CR Server:

And here is one with BusinessObjects Enterprise:

How does this affect Runtime Distribution?

This does NOT affect your ability to create Crystal Reports and embedded them into your Visual Studio or Java based applications and distribute them out.  It is important however that you only distribute out the DLLs and JAR files listed in the RUNTIME.TXT file under the …/Crystal Reports/Help/en directory.

For more information you can also check out my previous post about this topic here.

Enjoy!

Even though the SIA has been setup to automatically start services, another alternative is to control the starting of these services through scripts. One colleague of mine setup his BusinessObjects Enterprise SIA so that only the CMS and the FRS Input and Output services started up automatically.  All other services were setup to start manually.

In his experience, this made the starting of the SIA require less resources making startup (even) less error-prone that it already is.

Batch File for Starting Services

Below I have included all the contents of the batch file which you can download here.  Enjoy…

«Good BI»

:: Cleaning up
del /f /q "C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\Logging\*.log

:: Starting up dependencies
net start "SQL Server (MSSQLSERVER)"

:: CMS configured to startup automatically with SIA
:: See Control Panel Services for startup configuration (automatic)
net start "Server Intelligence Agent (cdi6BOE)"
net start "Apache Tomcat 5.5.20"

:: add timeout to allow CMS to initialize
:: sleep 90

cd "C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86"
:: CMS and FRS configured to startup automatically with SIA
:: (CMC > Servers > Properties)
:: ccm.exe -managedstart cdi6BOE.CentralManagementServer
:: ccm.exe -managedstart cdi6BOE.InputFileRepository
:: ccm.exe -managedstart cdi6BOE.OutputFileRepository
ccm.exe -managedstart cdi6BOE.AdaptiveProcessingServer
ccm.exe -managedstart cdi6BOE.DestinationJobServer
ccm.exe -managedstart cdi6BOE.EventServer
ccm.exe -managedstart cdi6BOE.ProgramJobServer
ccm.exe -managedstart cdi6BOE.PublicationJobServer

:: Crystal Reports
ccm.exe -managedstart cdi6BOE.CrystalReportsCacheServer
ccm.exe -managedstart cdi6BOE.CrystalReportsJobServer
ccm.exe -managedstart cdi6BOE.CrystalReportsProcessingServer
ccm.exe -managedstart cdi6BOE.ListOfValuesJobServer
ccm.exe -managedstart cdi6BOE.ReportApplicationServer

:: Desktop Intelligence
:: ccm.exe -managedstart cdi6BOE.ConnectionServer
:: ccm.exe -managedstart cdi6BOE.DesktopIntelligenceCacheServer
:: ccm.exe -managedstart cdi6BOE.DesktopIntelligenceJobServer
:: ccm.exe -managedstart cdi6BOE.DesktopIntelligenceProcessingServer

:: Performance Management
ccm.exe -managedstart cdi6BOE.DashboardAnalyticsServer
ccm.exe -managedstart cdi6BOE.DashboardServer
ccm.exe -managedstart cdi6BOE.PMMetricsServer
ccm.exe -managedstart cdi6BOE.PMRepositoryServer
ccm.exe -managedstart cdi6BOE.PMRulesServer
:: ccm.exe -managedstart cdi6BOE.PredictiveAnalysisServer
:: ccm.exe -managedstart cdi6BOE.ProcessAnalysisServer
:: ccm.exe -managedstart cdi6BOE.SetsProfileServer
:: ccm.exe -managedstart cdi6BOE.SetsQueryServer

:: Voyager
:: ccm.exe -managedstart cdi6BOE.MultiDimensionalAnalysisServicesServer

:: Web Intelligence
ccm.exe -managedstart cdi6BOE.AdaptiveJobServer
ccm.exe -managedstart cdi6BOE.WebIntelligenceProcessingServer

If you are running a development environment in which you have the BusinessObjects XI 3.0 server running on the same physical box as your BusinessObjectcs repository, you have run into a common problem.  In XI 3.0, the SIA is dependent on the repository and if the repository database is unavailable the SIA will failed to start and not retry.

Service Dependencies in Windows

In my case, I am using Microsoft SQL Server as my BusinessObjects repository database.  Therefore I need to make sure that Microsoft SQL Server is up and running before the SIA attempts to start.  Microsoft Windows accommodates this by allowing you to create service dependencies.  In our case, it will guarantee that  Microsoft SQL Server service is running before the SIA service attempts to start.

Changing the Configuration

Let’s walk through the process of setting up a dependency to the SIA will start automatically.

1. Start the CCM and select the SIA Service. The SIA service must be stopped before you make your changes.

   

2. Next, Choose Properties and Dependency

   

3. Lastly choose Add and select SQL Server (MSSQLSERVER) from the list of available services.

   

4. After you add the dependency you will see:

   

Now each time the SIA attempts to start, it will first check and verify that in addition to the Event Log servcie and the RPC service are running, the SQL Server service must also running before it attempts to start.

Do you have Multiple SIAs?

If you have multiple SIA’s, you should add this dependency to each one so that anytime the server reboots, the SIA will start correctly.

One Last Thing You’ll Want to Know

You may also which to modify the “Recovery” properties of your SIA Service so that in case it fails to start, it will attempt to restart.  To change these properties you will need to go into to Windows Services.

1. Start > Run and type in services.msc

   

2. Go to the Recover Tab and change the Restart service after value to 1.

   

Getting It Right Every Time

Adding these few dependencies will help to make sure that you never have any problems getting your CMS to start during the normal system restart.  If you have made any modifications to the start-up process of your BusinessObjects server, I would love to hear what you’ve done!

«Good BI»