Skip to main content

Using linear and seasonal regression for Revenue Forecasting

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 2012. Quarter 201202 (Q2 in year 2012) has an index of 2 since it is the second quarter overall. Quarter 201403 (Q3 in year 2014) 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

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

1In 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.
2Click Next. The Select Type of Analysis page opens.
3Do not change any of the values on this page. Click Next. The Select Metrics page opens.
4Add the Quarter of Year metric to the list of Independent Metrics, which already includes the Quarter Index metric.
5Click Next. The Select Output page opens.
6Rename the predictive metric so that the existing linear predictive metric is not overwritten.
7Save 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.

Comments

Post a Comment

Popular posts from this blog

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...

Optimizing queries in Microstrategy using VLDB properties

Optimizing queries in  Microstrategy using VLDB properties #vldb #vldbproperties The table b elow summarizes the Query Optimizations VLDB properties. Additional details about each property, including examples where necessary, are provided in the sections following the table. Property Description Possible Values Default Value Additional Final Pass Option Determines whether the Engine calculates...

Microstrategy "Error type: Odbc error. Odbc operation attempted

 "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...

Control the display of null and zero metric values

Show   Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the  Hide Nulls/Zeros  icon  in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the  Tools  menu, select  Report Options . The Report Options...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level   Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

System Manager workflow to execute on a schedule

Creating a System Manager workflow to execute on a schedule System Manager workflow can execute on a schedule or after an event has been triggered. This can be accomplished by creating a simple batch file, and scheduling that batch file to execute with a third-party tool like Microsoft Task Scheduler.   Note : To avoid user permission conflicts, the following steps must be performed with highest privileges.   In the below example, the workflow makes the i-server restarts every day.   1. The user must first have a valid workflow. This particular workflow is a template that is delivered out-of-the-box with System Manager.   2. Save the workflow in  .smw  format.   3. In a text editor (such as Notepad), enter the command line statement that the task scheduler should execute.     MASysMgr.exe -w C:\filename.smw” “UserName=User1 “Password=1234”   4. Save the file in  .bat  ...

Multi-Table Data Import(MTDI) from one or more supported data sources

Multi-Table Data Import(MTDI) from one or more supported data sources In MicroStrategy Analytics Enterprise Web 10 onewards, users can now simultaneously import two or more tables from one or more supported data sources, this feature is called Multi-Table Data Import (MTDI) which has been renamed as Super Cubes in MSTR 2019 (Does it sound like multisourcing for all the users without admin help?) Currently, all connectors in MicroStrategy Web 10 except " OLAP " and " Search Engine Indices " support Multi-Table Data Import. Users are able to add multiple tables/files when doing data import from single connector, as shown below: Users are also able to combine multiple tables/files from different sources and store them into one single Intelligent Cube, as shown below:

mstrio – Python and R wrappers for the MicroStrategy

mstrio – Python and R wrappers for the MicroStrategy REST APIs Connecting to MicroStrategy  Create a connection to the Intelligence Server using   Connection()   and    connect()  in Python and R, respectively. Required arguments for the   Connection()  function are the URL for the MicroStrategy REST API server, MicroStrategy Intelligence Server username and password, as well as the MicroStrategy project name. By default, the   connect()  function anticipates your MicroStrategy Intelligence Server username and password. LDAP authentication is also supported. Use the optional argument    login_mode=16    in the    connect()  function for LDAP authentication.  Extract data from cubes and reports  To extract data from MicroStrategy cubes and reports, use the   get_cube()  and   get_report()  functions. Use...
MicroStrategy Developer Preferences options are expanded so big that some options are being cutoff. Show the hidden objects in the  Microstrategy  developer MicroStrategy Developer Preferences options are expanded so big that some options are being cut off. The steps below given in the MSTR article may not work. This can be simple handled by using the steps below:  In the Microstrategy Developer go to Tools -> Preferences (Not my prefernces :) ) Under Developer category -> select Browsing on the browsing tab you see all the options like below: 3. Now using the mouse place the cursor on text box of 10000 which is next to 'Maximum number of monitoring objects displayed per page. 4. Then Hit Tab on Keyboard and hit another Tab on keyboard 5. Then press the space or down arrow on keyboard and click on OK or Enter. That will show the hidden objects in the Microstrategy developer   Normal Version ...