Cognitive Information

Business Intelligence Forecasting in SQL and Cognos

Note: This article is an update of a presentation given when I was at the University of Oklahoma’s Health Sciences Center. At that time, I was using Cognos version 7.3. This article is updated for Cognos 10 and SQL Server Analysis Server 2012.  Revisiting this subject ten years later, my opinion is that Microsoft has finally surpassed IBM Cognos.

Most businesses, including medical clinics, want some method for forecasting business trends. The Cognos BI powerplay presentation tools contain the tools needed for basic forecasting.  The trick in getting accurate forecasting is select the correct algorithm.

I recommend trying the model against only a portion of your data, to see how closely forecasts the remaining data.  For example, if you have 2 years worth of data, try entering only 18 months of data into the model and let it predict the final 6 months.  Then you have some actual data with which to measure ‘fit’ of the prediction.  The new Microsoft SQL tools mentioned below, allow training data and measuring fitness.

Forecasting SSAS vs Cognos

Microsoft SSAS
IBM Cognos Models
Association Algorithm
Clustering Algorithm
Decision Trees Algorithm
Linear Regression Algorithm
Regression Model
Logistic Regression Algorithm
Naive Bayes Algorithm
Neural Network Algorithm
Sequence Clustering Algorithm
Time Series Algorithm

IBM Cognos Forecast Models:

The old Cognos website used to list more details about each model, but that has long since been eaten by IBM’s user un-friendly website.  I’m sure a few of my friends still enjoy ‘Recreational Math’ magazine, so here are some algorithms to play with in your spare time.

Forecast Models

For more details,  read about the terms of service and limitations of liability on their forecasting algorithms pages.

Microsoft SQL Analysis Services

If you are using the Microsoft Business Intelligence stack, there are several data mining options in Analysis Services (SSAS).  The SQL team has worked hard to lower the barriers to entry and learning curves for data mining.   The Excel add-in makes the SSAS data mining models easily accessible to business users at the desktop.  I will have more about these tools soon.

Again, for details and limitations, see the Microsoft articles on each algorithm.


Tags: , , , , ,

Sorry, the comment form is closed at this time.

Business Intelligence & Data Warehouse Consulting

%d bloggers like this: