MicroStrategy ONE

Revenue Forecasting Example (Using Linear and Seasonal Regression)

To aid in setting goals for next year, you would like to establish a forecast of your company's revenue based on existing trends.

You will use the revenue values already available in your MicroStrategy project. Predictions of future revenue will be determined at quarterly intervals. The quarters for the future dates are already defined in your MicroStrategy project, but revenue figures for each are not yet available.

Your model will require a continuous input of quarters to recognize a regression pattern. A Quarter attribute is commonly formatted to represent a year followed by a quarter, and therefore cannot be used as a continuous index into the quarters. To address this requirement, create a Quarter Index metric using the Quarter attribute. Since a year followed by a quarter is still a sequential list of values, you can often create a Quarter Index metric with the following simple expression:

Rank(Max(Quarter@ID) {Quarter})

This uses the ID values for a Quarter attribute, and creates a sequential list of values by ranking them. For example, assume you are using values dating back to the beginning of 2022. Quarter 202202 (Q2 in year 2022) has an index of 2 since it is the second quarter overall. Quarter 202403 (Q3 in year 2024) has an index of 11.

If the ID values for your attribute representing quarter data are not sequential, you must determine a way to convert the ID values into a sequential list of values.

The example Tutorial project includes a Quarter Index metric, along with other reports and metrics created for this forecasting example. Some of the definitions of metrics and reports within the Tutorial project are different than the simplified descriptions in this example. You can use the definitions provided in the steps here, or the definitions in the Tutorial project, depending on what works best for your reporting environment.

To begin your data mining analysis, use the Training Metric Wizard to design a training metric, following the steps described below.

To Create a Training Metric for Linear Regression Analysis

  1. In MicroStrategy Developer, choose Tools > Training Metric Wizard.
  2. Click Next.
  3. Select Linear regression as the type of analysis.
  4. Click Next.
  5. Add Revenue as the Dependent Metric.
  6. Add the Quarter Index metric to the list of Independent Metrics.
  7. Clear the Show advanced options check box to use the default settings for variable reduction and other variable settings.
  8. Click Next.
  9. Select the Automatically create on report execution check box.
  10. Within the Predictive metrics to generate area, select Predicted Value.
  11. Click Finish.

For more information on the Training Metric Wizard, see Creating a Predictive Model Using MicroStrategy or refer to the MicroStrategy online help.

Next, create a report that includes the new training metric and the Quarter attribute. Include the Revenue metric to compare the values calculated by the training metric with the original values. Review the Report Data Options and VLDB properties for your report to ensure that outer join results are displayed for the metrics of your report. Execute the report.

The training metric generates a straight line that best fits the Revenue data. The report, converted into a dashboard to display this trend, is shown below.

A predictive metric is created in the folder you specified in the Training Metric Wizard. The default location is the My Objects folder.

The predictive metric accurately depicts a linear line through the Revenue data, but for this example, assume that you are not satisfied with the predictions. Your data is seasonal and you need to forecast the fluctuations that will occur throughout the year.

Seasonality is recognized by adding another independent metric to the training metric. This additional metric specifies the quarter within the year associated with each Quarter Index value. For example, the Quarter Index values of 1, 5, and 9 are all from the first quarter. The Quarter of Year metric uses the same basic formula as Quarter Index. The BreakBy parameter is defined as year so that the ranking is restarted for each year, allowing each quarter to be numbered 1 through 4 for a given year. The formula is shown below:

Rank<BreakBy={Year}>(Max(Quarter@ID) {Quarter} )

To include seasonality in your data mining model, complete the following steps.

To Add Seasonality to the Data Mining Model

  1. In MicroStrategy Developer, double-click the training metric you created in To Create a Training Metric for Linear Regression Analysis to open the Training Metric Wizard to the Introduction page.
  2. Click Next.
  3. Do not change any of the values on this page. Click Next.
  4. Add the Quarter of Year metric to the list of Independent Metrics, which already includes the Quarter Index metric.
  5. Click Next.
  6. Rename the predictive metric so that the existing linear predictive metric is not overwritten.
  7. Save the training metric with a new name to distinguish it as a seasonal prediction.

    For more information on the Training Metric Wizard, see Creating a Predictive Model Using MicroStrategy or refer to the MicroStrategy online help.

    You can now re-execute the report you created earlier that included the training metric, Quarter, and Revenue. The results of the training metric now recognize the fluctuations in revenue throughout each year and predict values accordingly. Notice that the data accounts for seasonality and is no longer a straight line, as shown in the report below.

    A predictive metric is created in the folder you specified in the Training Metric Wizard. The default location is the My Objects folder.