Acumatica, Power Query, and Power Pivot

Comments

1. George says

   October 31, 2015 at 12:35 pm

   I think you should keep this blog alive, and in your learning curve with acumatica, you should dedicate one hour or two, blogging about that learning experience.

   • Tim Rodman says

     November 1, 2015 at 12:48 am

     Thanks for the comment George. Look for a post this coming Wednesday for my thoughts on what to do moving forward.

2. Gabriel Michaud says

   January 21, 2014 at 6:19 pm

   BTW – have you considered simply starting from an exported inquiry, and then building your PowerPivot from this Excel table?

   • timrodman says

     January 22, 2014 at 10:20 pm

     Gabriel,

     You make a good point about building Power Pivot directly from the Excel worksheet table. However, here are some of the reasons why I would like to use the Power Query method instead:

     1. Choosing columns. Power Query allows me to choose which columns I want. Maybe I only want three out of fifteen available columns and I don’t want to make my Excel file larger than it needs to be.
     2. Filtering. I can filter the data before it comes into Excel. Maybe I’m connecting to an Explore screen that has data for 2010 through 2013, but I only care to bring in the 2013 data. Power Query allows me to apply the filter before the data comes in, again reducing my Excel file size. Now, I could call IT and ask them to add a parameter to the Explore screen, but they’re probably busy and I would have to wait for them to get to it. Also, it would affect all users and other users might not want the parameter added. Power Query enables me as a user to build self-service BI, empowering me to conduct analysis without having to wait to go through the IT bottleneck.
     3. Merge. Maybe I have one Explore screen that has customer sales dollars and another Explore screen that has attributes about that customer like primary salesperson. In traditional Excel, I would have to bring in both sets of data and do a bunch of VLOOKUP formulas to associate the customer attributes with the customer sales dollars. Power Query allows me to link customer sales dollars to customer attributes without writing VLOOKUP formulas and bring the data in as one table. This is a much faster and more efficient query with a smaller resulting data set size. There is a lot of potential with merge since Power Query can pull data from a variety of sources. Maybe I want to merge sales data with temperature data (do I sell more in hot or cold weather?) or household income by zip code data (is our product more of a luxury good or a necessity?). Power Query will let me do that. Lots of possibilities here.
     4. Power Pivot Compression. Power Query can feed the data directly into Power Pivot in Excel 2013, eliminating the need to also store the data in the Excel worksheet. Power Pivot is much more efficient at storing large volumes of data. I did a test where I pasted the word “Acumatica” down 500,000 rows and across 5 columns in an Excel 2013 worksheet, formatted as a table. The resulting file size was 9.443 MB. I pasted the same data into Power Pivot and the resulting file size was .123 MB. If I had to bring that data into an Excel 2013 worksheet first, then point it to Power Pivot, the data would need to be stored in both places. I tried that with my sample data and the file size was 9.567 MB (not surprisingly, basically 9.443 + .123). So, it would be much better to have the data stored only in Power Pivot (.123 MB) instead of in both the Excel worksheet and Power Pivot (9.567 MB). The Power Pivot version file size is less than 2% of the Excel worksheet version file size, but the amount of data stored is the same.

     I’m sure there are more reasons, but I’m still getting myself familiar with Power Query and the possibilities that it opens up. Power Query is a free new feature in Excel and I think that Acumatica is poised to take advantage of it better than most other ERP systems because Acumatica is web-based, giving users a secure way to access their data remotely. Also, Acumatica makes the Explore screens available as data connections so you can take advantage of the business logic built into them without having to take the old-fashioned approach of re-inventing the business logic (hopefully accurately) by going straight to the SQL tables when using Excel to query data.

3. Gabriel Michaud says

   January 21, 2014 at 6:18 pm

   It’s more complex than just copy-pasting the connection string; there are parameters getting posted (screen name, filter and I don’t know if Excel will allow you to set these properties from the UI. Rename xlsx file to .zip, extract it and have a look at connections.xml; you will see what i’m talking about…

   I will ask developer who did this functionality to have a look at your blog post; maybe he won’t be able to provide a solution but that may give us ideas how to improve it in the future 🙂

   Regards,
   Gabriel Michaud
   Director of Product Management
   Acumatica

   • timrodman says

     January 22, 2014 at 10:16 pm

     Thanks for the comment Gabriel! I took a look at the connections.xml file and I can see what you mean. There are a lot of parameters. That would be awesome if you can find out more info from a developer.

     There is a really long string that looks like this:
     %7B%22company%22%3A%22F100%20Examination%22%2C%22parameters%22%3A%7B%22BranchID%22%3A%7B%22view_name%22%3A%22Filter%22%2C%22display_name%22%3A%22Branch%3A%22%2C%22is_default%22%3Atrue%2C%22value%22%3A%22XYZ%22%7D%2C%22LedgerID%22%3A%7B%22view_name%22%3A%22Filter%22%2C%22display_name%22%3A%22Ledger%3A%22%2C%22is_default%22%3Atrue%2C%22value%22%3A%22ACTUAL%22%7D%2C%22FinPeriodID%22%3A%7B%22view_name%22%3A%22Filter%22%2C%22display_name%22%3A%22Financial%20Period%3A%22%2C%22is_default%22%3Afalse%2C%22value%22%3A%22122013%22%7D%2C%22AccountClassID%22%3A%7B%22view_name%22%3A%22Filter%22%2C%22display_name%22%3A%22Account%20Class%3A%22%2C%22is_default%22%3Atrue%2C%22value%22%3Anull%7D%2C%22SubCD%22%3A%7B%22view_name%22%3A%22Filter%22%2C%22display_name%22%3A%22Subaccount%3A%22%2C%22is_default%22%3Atrue%2C%22value%22%3Anull%7D%2C%22ShowCuryDetail%22%3A%7B%22view_name%22%3A%22Filter%22%2C%22display_name%22%3A%22Show%20Currency%20Details%22%2C%22is_default%22%3Atrue%2C%22value%22%3A%22False%22%7D%7D%2C%22filter_name%22%3A%22Saved%20Filter%22%2C%22filters%22%3A%7B%7D%2C%22data%22%3A%7B%22screen_id%22%3A%22GL.40.10.00%22%2C%22view_name%22%3A%22EnqResult%22%2C%22parameters%22%3A%5B%7B%22view_name%22%3A%22Filter%22%2C%22items%22%3A%5B%7B%22name%22%3A%22BranchID%22%2C%22is_key%22%3Afalse%2C%22value%22%3A%22XYZ%22%7D%2C%7B%22name%22%3A%22LedgerID%22%2C%22is_key%22%3Afalse%2C%22value%22%3A%22ACTUAL%22%7D%2C%7B%22name%22%3A%22FinPeriodID%22%2C%22is_key%22%3Afalse%2C%22value%22%3A%22122013%22%7D%2C%7B%22name%22%3A%22AccountClassID%22%2C%22is_key%22%3Afalse%2C%22value%22%3Anull%7D%2C%7B%22name%22%3A%22SubCD%22%2C%22is_key%22%3Afalse%2C%22value%22%3Anull%7D%2C%7B%22name%22%3A%22ShowCuryDetail%22%2C%22is_key%22%3Afalse%2C%22value%22%3A%22False%22%7D%5D%7D%5D%2C%22filters%22%3A%5B%5D%2C%22fields%22%3A%22AccountID%2CType%2CDescription%2CLastActivityPeriod%2CSignBegBalance%2CPtdDebitTotal%2CPtdCreditTotal%2CSignEndBalance%2CConsolAccountCD%2CAccountClassID%22%7D%7D" url="http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination

     I looked up the translations for the percentage characters and found this:
     
%20 = space
%22 = "
%2C = ,
%3A = :
%5B = [
%7B = {
%7D = }


     I then applied those translations to the really long string to come up with this (I indented to make it easier to read). You can definitely tell that there are a lot of parameters getting passed.
     
{
  "company":"F100 Examination",
  "parameters":
  {
    "BranchID":
    {
      "view_name":"Filter",
      "display_name":"Branch:",
      "is_default":true,
      "value":"XYZ"
    },
    "LedgerID":
    {
      "view_name":"Filter",
      "display_name":"Ledger:",
      "is_default":true,
      "value":"ACTUAL"
    },
    "FinPeriodID":
    {
      "view_name":"Filter",
      "display_name":"Financial Period:",
      "is_default":false,
      "value":"122013"
    },
    "AccountClassID":
    {
      "view_name":"Filter",
      "display_name":"Account Class:",
      "is_default":true,
      "value":null
    },
    "SubCD":
    {
      "view_name":"Filter",
      "display_name":"Subaccount:",
      "is_default":true,
      "value":null
    },
    "ShowCuryDetail":
    {
      "view_name":"Filter",
      "display_name":"Show Currency Details",
      "is_default":true,
      "value":"False"
    }
  },
  "filter_name":"Saved Filter",
  "filters":{},
  "data":
  {
    "screen_id":"GL.40.10.00",
    "view_name":"EnqResult",
    "parameters":
    [
    {
      "view_name":"Filter",
      "items":
      [
      {
        "name":"BranchID",
        "is_key":false,
        "value":"XYZ"
      },
      {
        "name":"LedgerID",
        "is_key":false,
        "value":"ACTUAL"
      },
      {
        "name":"FinPeriodID",
        "is_key":false,
        "value":"122013"
      },
      {
        "name":"AccountClassID",
        "is_key":false,
        "value":null
      },
      {
        "name":"SubCD",
        "is_key":false,
        "value":null
      },
      {
        "name":"ShowCuryDetail",
        "is_key":false,
        "value":"False"
      }
      ]
    }
    ],
    "filters":
    [
    ],
    "fields":"AccountID,Type,Description,LastActivityPeriod,SignBegBalance,PtdDebitTotal,PtdCreditTotal,SignEndBalance,ConsolAccountCD,AccountClassID"
  }
}