Powerpivot Success Case: The Motion Picture Association in Central America and the Caribbean

Recently, I resigned my position as a Sales Analyst for the 20Th Century Fox in Central America to chase my interests as a Technology Evangelist for the Tabular Model and Powerpivot (specially Powerpivot) and now, with this spare time, I have time and the right granted access to tell you how I managed to save the MPA and the members of the MPA (Fox, Sony, Disney, Paramount, etc, etc) around $40K and still counting in terms of a BI Solution.

Read more about this! It’s my masterpiece so far

Smile
  • Facebook
  • Twitter
  • LinkedIn

The Project

In essence, I needed to perform the following tasks:

  1. Develop a new DataBase using the best fit for our case (needed to choose between MySQL, SQL Express or Oracle XE)
  2. Develop an application to perform transactions against the DB (Role based access for every country in Central America)
  3. Have a centralized cube that the MPA admin could use either on site or on the web to develop the reports needed on an On-Demand basis.

Having that said, this is how I jumped into action:

First! I created the database model (using Access), all the tables needed, fields in each table, relationships between each other and always trying to keep it as simple as I could.

Second… I needed to dig more on how the users wanted to interact with the database and how much ($$$) would each application development cost. (this is an art and its money well spent)

Third.. I went for open source BI like Pentaho and some other cube managers/creators and was also thinking about Powerpivot but v2.0 wasn’t out yet.

Please take in consideration that this was a low budget project so we couldn’t spend that much on pretty much anything and we needed to have the database hosted on the cloud somehow. (A LOT of constrains for these big companies)

The Solution!

In the end, I chose Oracle XE as my RDBMS and APEX as my application. It was what my users wanted and, most importantly, what they needed to edit/modify/input records and consume simple reports in a fast and easy way.

But, what BI system did I choose?… YUP! I ended up not choosing one and creating a Powerpivot solution by myself…but, why?

This is an interesting case, and it lead me to do an informal comparison of the capabilities of

  • Facebook
  • Twitter
  • LinkedIn

VS

  • Facebook
  • Twitter
  • LinkedIn

Check out this informal and really quick comparison that I made (I’m into 6-Sigma tools so I used a Pugh Matrix for this case:

Pugh Matrix
 

Solutions and Concepts

 
Evaluation Criteria Desired Tool Powerpivot
clip_image002
  • Facebook
  • Twitter
  • LinkedIn
Pentaho Criteria Importance Weighting
Performance Datum + 40
Language + S 30
Ease of Use + + 10
Themes and aesthetics + 20
Sum of Positives   4 1  
Sum of Negatives     2  
Sum of Sames     1  
Weighted Sum of Positives   100 10  
Weighted Sum of Negatives   60  
Difference Between Weighted Sum of Positives and Weighted Sum of Negatives 100 -50  
         
Concept Selection Legend        
Better +      
Same S      
Worse      

In short.. Pentaho didn’t stand a chance due to the knowledge that our users had and also because just right then, one of the CTP for Powerpivot v2 came out with the RANK() and the DISTINCTCOUNT() functions!

Summary:

  • Oracle XE as the RDBMS
  • Oracle APEX as the application system
  • Powerpivot as our cube for any reporting needed (management asks the MPA for new reports of Box Office all the time…they are into creating BIG news from ANY data)

That decision of not using Pentaho helped the company not spend any money on the licensing of Pentaho so it can perform and do as good as Powerpivot. This is a solution that is continuously saving money as the MPA Admin plays with the cube as new measures are created almost every month in order to fulfill new dimensions in order to create new box office and admissions news in the market.

If you are a movie fanatic you probably check boxofficemojo.com and their news section…and if you’ve ever read about the international news for Central America or the Caribbean within the past year then you’ve probably read a figure that came out of one of the MPA’s reports.

Open-mouthed smile
  • Facebook
  • Twitter
  • LinkedIn

If you have any success stories leave it in the comments section below!

Posted in:

Leave a Reply

Your email address will not be published. Required fields are marked *