Wednesday, February 29, 2012

Using Oracle Crystal Ball API in your windows applications

[We are moving away from the API solution mentioned in this blog post because of various reasons. This blog post is available for historical reasons, but the API solution mentioned in this post is not available for either sales or support. We might still be able to help you in your desktop application by using Excel as the intermediary. If you want a discussion on that front, please feel free to post your questions in our Support channels (LinkedIn or CBUG).]

In this post, I wanted to mention an often overlooked gem in the Oracle Crystal Ball suite: the Open Crystal Ball API, or OCB API in short. If you are a Crystal Ball customer (i.e., running Crystal Ball on your desktop with Excel), then you can use the OCB API in your own applications.

OCB API is at the core of the magic that happens when you run Crystal Ball to simulate, optimize or forecast. Excel is but one of the front ends for this API (albeit, a very powerful one), but you can go far beyond what you can do within the Excel environment. Using the OCB API, you can directly access all the mathematics and analytics in your own application, in the way you want it.

OCB API is developed in C#, which is a Microsoft .NET language. That means - you are limited to using C# or other .NET languages for developing with the OCB API. Depending on your perspective, that may or may not be limiting. For example, you can use newer .NET languages with OCB API, e.g., F# or IronPython. Crystal Ball with Python - how cool is that?

Development platform and requirements
Although using OCB API does not require Excel, the OS requirements are the same as desktop Crystal Ball. Also, OCB assemblies (DLL’s) are built as platform agnostic, meaning that they can execute as part of either a 32-bit or 64-bit application.

One would presumably use Visual Studio for developing applications using OCB API, but one of the free/open source editors, like SharpDevelop and MonoDevelop, can also be used. Currently, the minimum requirement for using our API is .NET2.0 - which means you have to use at least Visual Studio 2008 to develop apps using the OCB API. Other later versions of .NET and Visual Studio are all supported.

We have never tried compiling OCB API with Mono library, but applications cannot possibly be developed with Mono and ported on Unix variants or Mac. If anyone wants to test this further, let me know.

If you have an Oracle Crystal Ball desktop license, you are also licensed to use the OCB API, there is no extra charge. You can use the same username and serial number in your code for licensing purposes. Note that, if you want to run a simulation using OCB API, you will have to provide a valid licensing code.

Availability and miscellaneous items
As mentioned, the API is available to use with a valid Crystal Ball desktop license. You have to reference the DLL’s in the installation folder in order to develop using OCB API. You can also contact sales and directly purchase a license for OCB API only.

The OCB API does not include charting. The amazing charts in Crystal Ball are generated using ChartFX library, available from SoftwareFX. They have a free lite version, which you can use for your charting needs. .NET4.0 includes charting APIs, which can also be used for generating charts.

Additionally, The OCB API does not include access to OptQuest libraries for optimization, due to licensing restrictions. The time-series forecasting part is included, however, in another DLL.

The core OCB API DLL’s are obfuscated to protect our intellectual property.

Our OCB API is well documented, and comes with a large set of sample codes to get you started. We have examples for both simulation and time series forecasting. The documentation does not get installed automatically though. Please contact an Oracle Crystal Ball sales representative to get access to the documentation.

The support process is same as desktop Crystal Ball, i.e., through Oracle Tech Support; however, this is for issues found in the OCB API, not for assistance in developing your application. We work with integration partners, who can help develop and deploy custom applications using OCB API. Other community support channels like CBUG or LinkedIn can also be used to ask questions.

Use Cases
Why might you want to use OCB API’s? Here are a few reasons.
  • If you have existing custom applications, you can use OCB API to enhance the analytics in the application, without re-building the model in Excel.
  • You can overcome some of the limitations of Excel by writing custom applications directly using OCB API:
    • Data volume/transfer
    • Modeling complex business rules or workflow
    • Model security/distribution
    • Automation
  • If you want to generate charts specific to your industry, that are not provided by Crystal Ball, or cannot be generated easily in Excel, then you can use the API along with other charting libraries.
  • Although, our Excel add-in code has really good performance, if you want to squeeze out even more performance for large datasets, you can skip the Excel overhead and directly use the OCB API on your data. Writing a .NET app with OCB API will also enable you to take advantage of the Task Parallel Library (TPL) to utilize multiple cores in modern machines. TPL is available as an extension with .NET 3.5, and as part of .NET4.0 and above.
  • If your data is not in Excel and resides in some other system (like external database etc.), then directly connecting to the data source using .NET and using the OCB API to run simulations or time-series forecasting would be easier. Alternative is to get the data in Excel using ODBC or other importing techniques.
  • Using OCB API, you can also expose simulation methods to a web service. Please contact an Oracle Crystal Ball sales representative before taking this route, since there might be additional license implications here.
Update (06/19/2013): Announcing the unavailability of API solution.

    Friday, February 24, 2012

    Monte Carlo simulation examples in healthcare

    I have written about examples of Monte Carlo simulation in the past. For a recent presentation on Monte Carlo simulation and stochastic optimization using Oracle Crystal Ball, I did some research on using Monte Carlo (MC) simulation in health-care. This blog post covers a few such references and applications I have seen so far.

    From my limited research, there seems to be three major types of uses of MC simulation in health-care industries.
    • It seems that large (or small) health-care organizations are using MC simulation and stochastic optimization for better understanding and managing their budgets in various departments. That makes sense, since cost control is one of the main thrusts of health-care systems nationwide.
      • I have seen quite a few articles on using MC simulation in the context of managing preventive care in mental health situations. The article from SAMSHA (The Substance Abuse and Mental Health Services Administration) I mention later is a great example.
    • A few companies, which are manufacturers of health-care equipments, are using MC simulation in the manufacturing context, as in DFSS (Design for Six-Sigma), process capability optimization etc.
    • Finally, a few other companies are using MC simulation for controlling parameters of certain health-care processes, e.g., simulating to find out what would be the optimal level of staffing to achieve certain quality of service.
    Note that there are other uses of generating random numbers from distributions, where you simulate the inter-arrival time for patients in a hospital emergency room etc. This type of problems are better handled using the discrete event simulation paradigm, rather than the MC simulation paradigm, so I will leave them out.

    I found quite a few research papers or article references in the literature which talk about application of MC simulations in the areas I mentioned above. I have to confess though; these papers are somewhat hard to penetrate in limited time by an outsider (to health-care) like me, since they seemed pretty heavy with terms used frequently in the health-care world and rarely outside. One such example of a term is FTE or full-time-equivalent, used in the context of work hours of health-care staffs.

    Here are a few articles which talk about MC simulation in healthcare. None of the links are now available however, feel free to search the articles on one of the search engines.
    • Speaking the Language of Finance - nursing leadership - Statistical Data Included by Pamela S. Hunt 
    • Estimating the cost of preventive services in mental health and substance abuse under managed care by Anthony Broskowski and Shelagh Smith (SAMSHA Pub ID: SMA02-3617R) 
    • Making sure your operating asset allocation is on target by Thomas H. Todd. Strategic Financial Planning. Winter 2009.
    • Application of Decision Sciences to Mental Health Policy by Center for Health Decision Science at Harvard School of Public Health. The page contains links to a few relevant papers.
    Finally, here is a link to my slides which talk about the resources mentioned above and describe some of the problems mentioned in these articles. For a link to my complete presentation on the introduction to Monte Carlo simulation, see here.

    Update (03/15/2012): Links.
    Update (04/01/2019): Removed dead links.

    Saturday, February 18, 2012

    New and improved online resource portal for Crystal Ball

    We have a new and improved Online Resource Portal, which is an awesome resource for all things Crystal Ball. If you are a member of our LinkedIn users group, you might have noticed this message from my colleague Hilary. Otherwise, here is an excerpt of the message:
    We wanted to let everyone know about a big change/improvement we’ve made to our online Crystal Ball resources. We have a brand new Solution Factory (that’s Oracle-speak for online resource portal) that has a ton of information and resources. We’ve grouped them into collections so (we hope!) it’s easier for you to find what you’re looking for – example models, white papers, recorded demos, and all that. We’d also like to invite you to sign up to get more regular updates on Crystal Ball. There’s a big “Sign Up” button on the Solution Factory. We hope you’ll sign up; and more importantly, we hope you find the revamped Solution Factory useful.
    The URL for the portal is:
    If you have comments or questions about the portal, please comment to this post (or send comments to the LinkedIn post), and I will follow up.