The Beauty of Palo

There are many ways to explain how Palo is enhancing spreadsheets like Microsoft Excel or OpenOffice.org Calc. Today I will try a very generic approach. Let’s have a look at the following spreadsheet that only uses 6 cells, whereby cell C6 displays the actual number of units sold in 2007 for Germany. It is 919.665 units.

Now lets change the row title in cell B6 from “Units” to “Turnover”.

You see that cell C6 now displays the value 6.196.660 which is the actual turnover for Germany 2007. How is it done? Obviously there is a formula in cell C6 but I reassure you that the spreadsheet does not contain lookup tables or links to other spreadsheets.

To make it more mysterious, lets add two more row titles in cell B7 and B8, labeled “Cost of Sales” and “Gross Profit”. Using the copy fill command, we copy the cell formula in cell C6 to cells C7 and C8. As a result, cells C7 and C8 now display reasonable values for Cost of Sales and Gross Profit.

The same miracle works if we add France and Belgium as additional column titles. After copying the formulas in cells C6:C8 to D6:E8, the values for France and Belgium appear automatically.

How does the magic work? Very simple: Cells C6:E6 contain a Palo function, called the PALO.DATA function. The PALO.DATA function uses a special syntax to identify cell coordinates of data, which is very different from the well known A1-Style that you know from Excel.

For Example: let’s take a look at the Palo data function in cell C6. This function retrieves the actual year total turnover for all products in the region Germany in 2007. Expressed in the Palo syntax this is PALO.DATA(…,…,”All Products”,”Germany”,”Year”,”2007″,”Actual”,”Turnover”).

But where does the Palo function retrieve this value from? It retrieves it from Palo, more precisely from a Palo cube in a Palo database on some Palo server. The “…,…,” expression in the previously cited Palo function specifies the name of the Palo server and the Palo database (“localhost/Demo”) and the name of the Palo cube (“Sales”). So the complete syntax of this Palo data function in cell C6 is PALO.DATA(“localhost/Demo, “Sales”, “All Products”, “Germany”, “Year”, “2007″, “Actual”, “Turnover”).

With the Palo Function it is possible to work with numbers in Excel which are actual not stored in the spreadsheet, but in an external database (i.e. Palo). So if two or more people on different workplaces looking on the actual year total turnover for all products in the region Germany in 2007, even on different spreadsheets, they can be sure they all see exactly the same value. This is the end of the Excel-Chaos where people have multiple versions of spreadsheets with outdated data leading to multiple versions of the truth.

It is also important to know that you can enter values directly on PALO.DATA functions. The values do no overwrite the function, instead the values are “beamed” to the corresponding cell in the Palo cube and the function stays intact, displaying the newly entered value. To the end user it looks like he entered a value in Excel, but in the background Palo makes sure that the value is actually stored in the Palo database making it centrally available for all Excel users that are connected to the specified Palo cube.

I should mention that you can assign read and write access rights to every user that is connected to the Palo server. The access rights go as far a the element level, so for example one user could edit values for France, but would only have read access for Germany and would not have any access for Belgium.

So what is the beauty of Palo? It is the easy way how Palo adds consistency, multidimensionality and centrality to Excel. By the way, Palo is not limited to Excel, but that is another story and will be covered in a future post. Another beauty of Excel Palo? It is free and Open-Source. Both client and server. You can download it from the Jedox website at www.jedox.com .