topic Re: Forecasting in R Script Showcase

Forecasting

Sharon — Wed, 16 Nov 2016 09:27:27 GMT

Description

Forecasting is used to predict next points in time series data sets. The prediction in this example is based on trend and seasonality.

Prerequisites (The sample .pbix files will not work without these prerequites completed)

1. Install R Engine

Power BI Desktop does not include, deploy or install the R engine. To run R scripts in Power BI Desktop, you must separately installR on your local computer. You can download and install R for free from many locations, including the Revolution Open download page, and the CRAN Repository.

2. Install the required R packages.

Download the R script attached to this message and run it to install all required packages on your local machine.

Required R packages:

forecast, graphics, scales, zoo

Tested on:

CRAN 3.3.1, MRO 3.3.0, powerbi.com

Legal Disclaimers:

Terms of Service and Third Party Programs.

Re: Forecasting

hohlick — Thu, 18 Aug 2016 22:22:54 GMT

Do not sure why, but visual don't work:

Loading required package: scales
Loading required package: forecast
Loading required package: zoo

Attaching package: 'zoo'

The following objects are masked from 'package:base':

as.Date, as.Date.numeric

Loading required package: timeDate
Loading required package: methods
This is forecast 7.1

Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :

Re: Forecasting

HMonteiro — Thu, 18 Aug 2016 23:29:33 GMT

Hi, great piece of software,

All other models work, except this one, probably with the ts code part.

Posting the error message file here:

Error Message:

Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo

Attaching package: 'zoo'

The following objects are masked from 'package:base':

as.Date, as.Date.numeric

Loading required package: timeDate
Loading required package: methods
This is forecast 7.1

Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted

Stack Trace:

Invocation Stack Trace:

ID de atividade
e5ab37b4-ff68-4eeb-9ac7-d3cc346b8037

Hora
Fri Aug 19 2016 00:14:08 GMT+0100 (Hora de Verão de GMT)

Versão
2.37.4464.361 (PBIDesktop)

Client Error Code
ServiceErrorToClientError

Error Details
Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo

Attaching package: 'zoo'

The following objects are masked from 'package:base':

as.Date, as.Date.numeric

Loading required package: timeDate
Loading required package: methods
This is forecast 7.1

Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted

Stack Trace
Microsoft.PowerBI.ExploreServiceCommon.ScriptHandlerException: Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo

Attaching package: 'zoo'

The following objects are masked from 'package:base':

as.Date, as.Date.numeric

Loading required package: timeDate
Loading required package: methods
This is forecast 7.1

Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted
---> Microsoft.PowerBI.Radio.RScriptRuntimeException: Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo

Attaching package: 'zoo'

The following objects are masked from 'package:base':

as.Date, as.Date.numeric

Loading required package: timeDate
Loading required package: methods
This is forecast 7.1

Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted

em Microsoft.PowerBI.Radio.RScriptWrapper.RunScript(String originalScript, Int32 timeoutMs)
em Microsoft.PowerBI.Client.Windows.R.RScriptHandler.GenerateVisual(String script, String inputVariableName, IDataReader dataReader, Nullable`1 viewportWidthPx, Nullable`1 viewportHeightPx)
--- Fim do rastreio da pilha de excepção interna ---
em Microsoft.PowerBI.Client.Windows.R.RScriptHandler.GenerateVisual(String script, String inputVariableName, IDataReader dataReader, Nullable`1 viewportWidthPx, Nullable`1 viewportHeightPx)
em Microsoft.PowerBI.ExploreServiceCommon.ScriptVisualCommandFlow.RunInternal(Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
em Microsoft.PowerBI.ExploreServiceCommon.ScriptVisualCommandFlow.Run(Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
em Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.TransformDataShapeResult(QueryCommand transformCommand, String dataShapeId, SemanticQueryDataShapeCommand command, Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
em Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteDataQuery(IQueryResultDataWriter queryResultDataWriter, DataShapeGenerationContext dsqGenContext, EntityDataModel model, DataQuery query, ServiceErrorStatusCode& serviceErrorStatusCode)
em Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteSemanticQueryCommands(IQueryResultsWriter queryResultsWriter, ExecuteSemanticQueryRequest request, IConceptualSchema conceptualSchema, EntityDataModel model)

If only one time series selected, visual does appear, but prediction model transforms into an useless predictive model.

Keep it up with the good work.

Re: Forecasting

Anonymous — Fri, 19 Aug 2016 08:11:32 GMT

To those who have issues with "Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :" it turns out R uses your local language setting to determine a month name...

If you add the following to you R-script it should force the code to use english setting:

Sys.setlocale("LC_ALL","English")

Make sure to add it the to top of the code

Hope this helps you too :

Re: Forecasting

Anonymous — Fri, 19 Aug 2016 08:12:25 GMT

If you add the following to you R-script it should force the code to use english setting:

Sys.setlocale("LC_ALL","English")

Make sure to add it the to top of the code

Hope this helps you too :

/Chris

Re: Forecasting

stlecho — Fri, 19 Aug 2016 09:48:05 GMT

When running the ScriptInstallPackagesForForecast.R script, I'm getting the following warnings:

1: package 'forecast' was built under R version 3.3.1
2: package 'zoo' was built under R version 3.3.1

How can this be solved please ?

Re: Forecasting

stlecho — Fri, 19 Aug 2016 09:51:12 GMT

When running the ScriptInstallPackagesForForecast.R script, I'm getting the following warnings:

1: package 'forecast' was built under R version 3.3.1
2: package 'zoo' was built under R version 3.3.1

How can this be solved please ?

Re: Forecasting

stlecho — Fri, 19 Aug 2016 09:53:18 GMT

When running the ScriptInstallPackagesForForecast.R script, I'm getting the following warnings:

-package ‘forecast’ was built under R version 3.3.1
-package ‘zoo’ was built under R version 3.3.1

What should I do to solve those warnings ?

Re: Forecasting

hohlick — Fri, 19 Aug 2016 11:44:39 GMT

Thank you Anonymous, it works now!

Re: Forecasting

hohlick — Fri, 19 Aug 2016 11:44:09 GMT

stlecho, nothing, it works with this warnings too

Re: Forecasting

HMonteiro — Fri, 19 Aug 2016 18:38:34 GMT

Solved! Many thanks!

Re: Forecasting

JuliaYebra — Fri, 19 Aug 2016 18:59:35 GMT

Anonymous excellent!!!! Thank you!!!

Re: Forecasting

shreekishore — Tue, 20 Sep 2016 16:26:49 GMT

Could you send any one the source file births.csv file.

Thanks,

Re: Forecasting

shreekishore — Tue, 20 Sep 2016 16:31:34 GMT

Could you send any one the source file births.csv file.

Thanks,

Re: Forecasting

shreekishore — Tue, 20 Sep 2016 16:32:56 GMT

please send Births.csv file

Re: Forecasting

Jacob_Minsky — Wed, 21 Sep 2016 07:52:48 GMT

I am attaching the births.xlsx file used to create this visual.

Re: Forecasting

rohuyn — Mon, 10 Oct 2016 19:06:28 GMT

This is great! Appreciate the time and effort it took to build and share this out!

I've been exploring the forecasting using the R integration and PowerBI, and was wondering if there was a way to explicitly show the predicted values and confidence intervals in a dynamic way. For example, in this situation, if I clicked on "Cuba" for this specific forecast example, how would I obtain the values shown in the R visualization on the Dashboard?

Thanks,

Ronnie

Re: Forecasting

TravisKale — Thu, 10 Nov 2016 21:42:17 GMT

Thanks for the great visual.

I am getting weird x Axis (date) labels. 2016.2, 2016.4 and they don't seem to line up with February 2016 and April 2016. Attached is a png image of what i am seeing. I also included a standard pbi visual to show the correct dates.

Am I missing a formatting option?

Re: Forecasting

mjc — Wed, 16 Nov 2016 12:38:02 GMT

Attached is a copy of the actual data used in the original births.csv file which I obtained from the pbix.

I got this by simply creating a new table on the report page, added all columns with no summarisation then used the export data option from that table.
As dates are exported from Power BI using a date-time format I used excel to reformat the Calendar Month column as just a date. I deliberately used a US format (mm//dd/yyyy) which might be more widely usable though this should automatically be recognised by Power BI anyway. If it is not and you use something different then just format in excel to suit your needs.

Obvioulsy this doesn't really gain you anything since the information was all already embedded in the pbix but it does allow you to fix the errors seen when looking at queries.

Of course, the technique is something that might be useful in other situations where you are missing a source file and want to recreate it so hopefully this helps someone.

(attached as a zip file since csv attachments are not supported on the forum)

Re: Forecasting

Anonymous — Fri, 23 Dec 2016 15:54:53 GMT

Hi there!

First of all, awesome visual! It took me a week to script the ets forecasting in R on my data, and I've been able to get what I want, but then the problem is that it generates a completely new set of data, unrealted to the existing model in Power BI. Your visual solves this problem by integrating the R script (very similar to what I have) into Power BI, so now I can use measures. Great work.

Afew things missing here: the Y-axis is poorly marked, if we're talking about millions of dollars on the Y axis. Also, no labels. So, I decided to clone this visual and create my small variation. Standing on the shoulders of giants, as they say

All I've done so far is adjusted the R script to also populate a table of values along with the graph. I left the rest untouched.

I got to the point that suits me for now (I hope to keep improving) and I can see needed results on the web when I run pbiviz start command.

But when I try to package the visual, the packaged custom visual for PBI Desktop doesn't have any of those changes... What am I doing wrong?