New project – Powerful free BI Solution for the Small Companies (Part 1)

  • Facebook
  • Twitter
  • LinkedIn
+
  • Facebook
  • Twitter
  • LinkedIn
+
  • Facebook
  • Twitter
  • LinkedIn
+
  • Facebook
  • Twitter
  • LinkedIn
+
  • Facebook
  • Twitter
  • LinkedIn

(From left to right: Visual Basic Studio, Visual Basic for Applications, Excel 2013, Powerpivot, Windows Task Scheduler) 

=

Most cost-effective BI solution ever

By now, you’ve probably heard about Cloud Powerpivot, SSAS Tabular and even MOLAP or plain MDX in SSRS all either deployed in Sharepoint or using the classic reporting server but… have you ever heard about the cheapest BI solution using Powerpivot?

If your company doesn’t have the money to run a full BI Site in-house or get a hosting service for your Powerpivot Workbooks then you might want to read the rest of this post which will lead you to a free solution aimed towards the small to mid-size companies.

If you’ve been reading this far then you won’t regret what’s next.

The following is a project that I’m working on in order to give out this solution to small companies that don’t have the money to do any type of BI projects. In my case, I live in Panama City, Panama which has tons of small to midsize companies that urgently need BI but don’t have the financial resource to actually go all the way and get it… and that’s where I come in.

First, let’s set a map of what we want our solution to be:

  1. We want a solution with Powerpivot for Excel 2013
  2. We want the solution to be hosted in a local network or local machine (local excel file in one of the machines or in a network drive)
  3. Last but not least, we want this solution to update automatically at a given schedule

And what do we need to make this happen?

  1. Excel 2013
  2. Powerpivot
  3. A VBS script (in a .txt file)
  4. A VBA code to refresh the tables and pivot tables inside Excel (on-demand only)
  5. A Task scheduler for Windows

How will it work?

The steps that will take place are the following:

  • The scheduled task will be triggered by an specific daily schedule
  • This will then run a vbs script to copy the file that has our data model
  • paste it into our local machine (the one that’s running the task)
  • open the file
  • run the VBA code
  • once its done, save a copy in our local machine and then
  • save another copy into the network drive or specific machine where the file resides

     

If you find this post interesting and have any ideas on what I could do better or what could be simplified let me know in the comments section below!Note: Part 2 should be posted within 2 weeks after checking all the comments.

Posted in:

14 Comments

  1. @ Miguel
    I’m not sure where the VBS fits in? I’ve successfully done a similar thing using Windows Task Scheduler and a bit of VBA to open the file on the network, refresh said file then save back to the network.

    My view is that at this point desktop PowerPivot is MASSIVELY under rated as a total BI solution for smaller businesses.

    @ Jeff Standen
    In 2013 the VBA thing is straightforward as the model is exposed. In 2010 its less straightforward but can be done: http://blog.gobansaor.com/2011/09/01/automating-powerpivot-refresh-operation-from-vba-the-code/

    • Hey Jacob! Thanks for the comment, glad to see you in my blog
      The reason behind the vbs is to create a script that will copy the file, understanding that the file is currently inside a network and I want to save a copy of that file locally in my desktop that will act as a “server”. Once it’s copied into my local system, then I will open the file and run the vba to refresh all the tables.

      But in a few words, it’s just to open the file. Could this be done in a much easier way using VBA?

      • Miguel, in a word yes!

        Windows task scheduler will happily open a specific file. You can then place the VBA that refreshes the model etc. in the ‘ThisWorkbook’ module and use the Workbook_Open event to trigger the code on opening.

        Let me know if you have any issues getting this to work.
        Jacob

  2. Hi Miguel, Your idea is very good. In fact, one of my customers were asking me about this solution of the common PowerPivot XL in a shared drive and finding a way to refresh. Let me know how well it is working for you.

    Have you given thought as to how this would work when the same shared Excel file is used by multiple people at the same time? Would it scale at least for 5-10 concurrent users?

    • You really caught me in a good time! I’m working on this but the original idea is this:
      – Use a machine that would act as the “server” and have it do the following:
      1. get a copy of the file wherever its located and save it locally
      2. do whatever it needs to be done (refreshing, running other stuff, etc)
      3. save a copy locally and
      4. a copy that will overwrite the previous one in the original location
      IF we have an error when trying the step 4 then just end the process

      I’m working on the VBA for the refreshing process as I’m typing this and also working on a personal project of mine.

      Also, I was thinking about having the file open on that specific machine full time. So that nobody could change anything but they could create their own reports from it.

      Let me know if you have any ideas!

      • Cool – good timing, I guess :). Your approach sounds good. Let us see what real-life issues you face when you deploy. I will be keen to hear about how this is done as it can help some of my clients as well.

        How would the VBA program running on the server know all the PowerPivot files to be refreshed?

        Why do you need to keep the Excel file open to avoid overwriting? Can’t you just lock the file? Do you have a process to rename the file so that the person reading the file knows when it was last refreshed?

  3. well, the idea is that this needs to be deployed using Excel 2013 so you can choose what connections to refresh as this is pretty straightforward. The Excel file to be opened it’s just a way to have a full control of the file in the “server” .

    That idea about letting the users know when it was last refreshed sounds cool! I could use something similar to get the info from “date modified” as a timestamp somewhere in the spreadsheet.

    keep the ideas coming!

  4. I have been thinking of a way to do what you explain on your post though I didn’t went too far as our company is considering deploying a Sharepoint solution. However, I would love to see any progress you make on this matter.

    From my previous internet research, I found there is a little piece of sw aimed at automatically refresh powerpivot models acting as a windows service.
    https://excelrefresher.codeplex.com/

    I didn’t make it work, though 🙁

    Also, Data Models could be refreshed upon opening of the file in Excel 2013 as this blog explains:
    http://prologika.com/CS/blogs/blog/archive/2013/02/04/powerpivot-data-refresh-in-excel-2013.aspx

    Looking forward to hearing from your results.

    Saludos,
    David

    • Amazing! I’m reading the posts but I got my laptop with some overheating problems so I can’t test any of them and this old laptop that I’m using its on Vista (not cool).

      Thanks for the posts, David!

      I’ll try to create just something a little more basic than whats in the excelrefresher and go from there. It’ll probably be just the code within the wkbk and all the other operations within the task scheduler to make it more simple but will only work with Excel 2013 (way easier than with Excel 2010).

      saludos!

      • My pleasure.

        You should keep trying definitely! You might use the “Refresh Data When Opening the File” functionality on Excel 2013 by setting a windows task to open the file, wait for some time (the aprox time the workbook needs for the refresh) and then save and close the workbook with the refreshed data. I don’t know how this is done and that is why I found so interesting your approach to the problem.

        It seems PowerPivot users are posting more and more interesting articles on it so i keep feeding myself with them 🙂

        Thanks to this tool and the reports I showed to my boss I was taken back to my company’s HQ (from China to Spain) so I am pretty fond of PP as you can imagine 😉

        Seguimos en contacto,
        David

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.