topic Re: Association Rules in R Script Showcase

Association Rules

Sharon — Sun, 08 Apr 2018 08:51:08 GMT

Description

Association rules are ideal to quicly derive insights from large datasets. The rules are autmatically detected and visualized.

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:

arules, arulesViz, grDevices, gridExtra, grid, methods

Tested on:

CRAN 3.3.1, MRO 3.3.0, , powerbi.com

Legal Disclaimers:

Terms of Service and Third Party Programs.

Re: Association Rules

Anonymous — Sun, 21 Aug 2016 08:45:13 GMT

I wonder if you can advice that what Im trying to acheive is possible.

I wanted to transform my dataset to transactions using a simple R script below
library(arules)

dd = as(dataset, "transactions")
df = as.data.frame(as(dd, "matrix"))

However I got an error message beolow. Can you please help what this error actually mean and how to fix it?

DataSource.Error: ADO.NET: R script error.
Loading required package: Matrix

Attaching package: 'arules'

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

abbreviate, write

Error: could not find function "as"
Execution halted

Details:
    DataSourceKind=R
    DataSourcePath=R
    Message=R script error.
Loading required package: Matrix

Attaching package: 'arules'

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

abbreviate, write

Error: could not find function "as"
Execution halted

ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Radio.RScriptRuntimeException

Re: Association Rules

Anonymous — Mon, 22 Aug 2016 19:19:34 GMT

Answered by own question!

library(arules)
library(methods)
data(dataset)
dd1=as(dataset,"transactions")
trans=dd1
AdultData=as.data.frame(as(dd1, "matrix"))
AdultData=cbind(id=1:nrow(AdultData),AdultData)

replace data(Adult) with data(dataset) and R does the rest!.. didnt have eyes for details there.

Re: Association Rules

patoduck — Fri, 11 Nov 2016 23:32:10 GMT

Hi,

I have a table with 2 Columns ID and Product.

I want to Edit the Script in R, but I can't get it to work

How should it be the read.transactions statement?

> txn <- read.transactions(dataset, rm.duplicates= TRUE,format='basket',sep='','',cols =1)

But I get this error...

Error in readLines(file, encoding = encoding) : 'con' is not a connection

Re: Association Rules

boefraty — Sun, 13 Nov 2016 15:41:25 GMT

Hi,

The function "read.transactions" reads a transaction data file from disk and creates a transactions object. The error you get indicates that R could not open the file for reading.

1) Save your excel as CSV for example to : "c:\\Users\\someone\\Downloads\\try.csv
2) Run
txn <- read.transactions("c:\\Users\\boefraty\\Downloads\\try.csv", rm.duplicates= TRUE,format='single', sep='','',cols =c(2,1))

Beside this, you should make sure that your data is a transaction object: to store a binary incidence matrix, item labels, and optionally transaction IDs and user IDs. Check your format is "single" or "basket"

You are welcome to continue and discuss this example with me via email: boefraty@microsoft.com or here

Re: Association Rules

boefraty — Sun, 08 Apr 2018 08:35:41 GMT

The data with 2 columns: "TransactionID" and "Product" is indeed very natural. Attached is the PBIX with R code suited to such input.

The relevant data transformation is in lines

nnn = names(dataset)

transData <- as(split(dataset[,nnn[2]], dataset[,nnn[1]]), "transactions")

The data in PBIX is synthetic.

Re: Association Rules

baferrie — Thu, 26 Jan 2017 19:18:17 GMT

Hello, I downloaded the association rules custom viz and succesfully have it working with the data. I was wondering though if there was any way we could sort the rules based on the RHS? I tried adding a filter, but what that did was exclude the data from ever getting to the association rules algorithm. Is there a way to sort the association rules without excluding data?

Re: Association Rules

boefraty — Sun, 29 Jan 2017 09:10:39 GMT

Do you want to sort by second column?

Re: Association Rules

karaoan — Mon, 06 Feb 2017 02:17:42 GMT

Hi, I have installed r (default version and one that I used is 3.3.2 though) as per provided links and ran the script. When refreshing the PBIX I get the following error:

Re: Association Rules

boefraty — Mon, 06 Feb 2017 15:19:07 GMT

The problem is with "ISLR" package it is required by R connector (not R visual), so you have two workarounds:

1) Don't do refresh

2) call command
install.packages("ISLR")

from any R console

Re: Association Rules

boefraty — Wed, 08 Feb 2017 11:44:12 GMT

Side branch solution for this feature:
https://github.com/boefraty74/PowerBI-visuals-assorules-1/tree/assorulesWithTextFilters

I added two text parameters, comma separated strings to be searched inside RHS string rule (using OR operator)

Re: Association Rules

surething — Fri, 17 Mar 2017 11:39:41 GMT

Hi,

I'm trying to use the R script to create association rules for transaction ID and item reference in PBI. I used the script you have in the example but it didn't seem to work. any idea why?

Thanks

Re: Association Rules

boefraty — Sun, 19 Mar 2017 09:34:50 GMT

It did not fail, it says "No rules generated" .

Try to relax thresholds of the rules. If you can to send me PBIX, I will have a look (boefraty@microsoft.com)

Re: Association Rules

HowardCC — Fri, 24 Mar 2017 09:34:39 GMT

I get the same msg when using the Showcase .pbix, "No rules generated"

Tried using different tresholds. I think it's due to using R 3.3.3 / 3.3.2.

Some packages won't load/install under R 3.3.1 using the install-script (were build under R 3.3.3 etc)

Any thoughts?

Re: Association Rules

ironryan77 — Mon, 10 Apr 2017 15:42:53 GMT

Great job on this visual! But I can't figure out how you created your dataset? I'm looking at the sample PBIX file and it looks like the selected fields are total counts. So in my PBIX file I counted the total values by creating a measure:

MPPL2 = CALCULATE(COUNT(IRC_Metric_RSA_Refresh[PPL2]), FILTER(IRC_Metric_RSA_Refresh, [PPL2]="AUTO"))

But when I add this measure to the visual in my own PBIX file, it doesn't allow Basic filtering and it doesn't display in my PBIX. How can I mimic your behavior in my PBIX file?

Re: Association Rules

ironryan77 — Mon, 10 Apr 2017 16:11:03 GMT

Also this is the error I'm getting:

Re: Association Rules

boefraty — Sun, 23 Apr 2017 07:42:46 GMT

Hi ironryan77,

Please send me a PBIX, I will see what can be done

Re: Association Rules

ironryan77 — Mon, 24 Apr 2017 22:15:56 GMT

Thanks!

Re: Association Rules

boefraty — Mon, 22 May 2017 13:44:30 GMT

Hi @ironryan77,

I am really sorry for late reply 😞

Apparently some changes/ updates in "arules" package caused the sub-function "cleanRedundant" to work diffrently without backward compatability. We will need to solve it both for R visual and R custom visual. I will update and ping you after update.

Re: Association Rules

MAAbdullah47 — Sun, 31 Dec 2017 06:02:36 GMT

Please tell us how you draw this table.