One day I was browsing around Amazon looking for a book about some boring technical stuff (diving into the company pocket is always a good way to start a lazy morning).
When I finally found the book I was looking for, I immediately got a list of products who were bought by other customers who also bought my book (try saying that three times fast). Amazon have had this feature for as long as I can remember (it's called Collaborative Filtering), but being a bit of a data freak - that feature was always extra cool to me.
Now coming back from fantasy land - from a Business Intelligence perspective, this reminded me of Basket Analysis. In simple terms, Basket Analysis is the idea of finding products that will yield more profit if they are sold together - like shampoo and conditioner. Should they be sold together for maximum profit? Or should they be sold separately?
I was wondering if it's possible to perform similar analysis with Prism and how long it would take me. Soon enough, I had it up and running. It looks something like the following image. You can download the sample PSM file here: Basket Analysis.psm
This PSM document lets you select a product, and in turn see what products users (who bought this product) also bought.
-- The data --
This is the example table we're going to analyze. It's pretty straightforward and describes daily product sales per customer.
For my example, I used an Oracle table but you can download the data in Excel format and it will look and feel just the same: Basket Analysis.xls. If you need help connecting to it, you can watch the Connecting To Data video that explains it.
-- The Prism Data Source --
After connecting to the table, it looks like this:
-- Step 1: Add Widgets and Data --
This one's pretty easy. Just select the Product ID dimension and drag-n-drop it onto the canvas. Repeat this action for the Customer ID dimension. Dropping a dimension (or filter) onto the canvas automatically creates a Drop-down list Widget.
To create a pivot, you select the Pivot Widget Widgets Pane and drop it onto the canvas, or by right-clicking the canvas and navigating to the New menu item and selecting "Pivot".
To populate the pivot, select the Product ID dimension and drop it onto the Pivot's Rows axis in the Data Layout Editor. Finally, select the Measures node and drop it onto the Columns axis - and click update. If you wish, you can watch the Working With Pivots video to see how this is done.
-- Step 2: Add Background to Widgets --
Now here's the whole trick. Prism's Background (or Scope) feature basically lets you connect one Widget to another to indicate that the results of the target Widget should be limited to the selection in the input Widget. To activate the Background feature, just hover with the mouse of the Customer ID Drop-down Widget and click on the Background button (see image below).
Selecting Product ID and clicking Apply will cause the Customer ID Drop-down to contain only those Customer IDs that intersect with the selected members in the Product ID Drop-down.
Similarly, click on the pivot's Background button and select the Customer ID Drop-down as its background.
The rest is just text and some formatting!
Now you can select a product in the Product ID Drop-down and see a table with the products bought by others that also bought the selected product!
Did I mention that as you add rows to this Excel file, the Prism document will be automatically updated to reflect this new data?
Have a great day,
Elad
SiSense
