Many people are comfortable with spreadsheets. We can arrange data in rows and columns. Cells are identifying through row id and column id (eg: A1 refers to column A and row 1 in Excel). We perform basic calculations using formulas across a group of cells. If needed, we can freely insert rows and columns in the middle and update the formulas.
SQL
In SQL, the data is also in rows and columns like a spreadsheet. However, we do not access the data using cell references. We get the data using conditional queries:
Select all where "condition" is true
Select all where "condition" is not true
Select all where "condition 1" is true OR "condition 2" is not true
These search queries give us all the rows of data that passes the given conditions. Then we can parse the results automatically or manually. If needed, we collect data in two tables and join them.
While the SQL table looks like a spreadsheet, there are no cell references. In SQL, we can only extract data through conditional and/or join statements. Then we can move to another data structure for further narrowing or analytics.
Cubes
Cubes are like tables with many dimensions. There are rows, columns, dimension 3, dimension 4 and etc. We access the data like a spreadsheet. Row-1, column-1, dimension3-1, dimension 4-1 all points to the first cell in each dimension. We can do calculations such as sum across rows and columns or across other dimensions like a spreadsheet.
The advantage of Cubes is that the data is structured across multiple dimensions, not just rows, and columns. The disadvantage of Cubes is that the table rows, columns and other dimensions have to be decided up front. Then we can enter data into the appropriate dimension members.
Use Cases and Users
The spreadsheet provides maximum flexibility but is limited in layout. We use SQL for querying but it gets complicated for business users. Cubes are harder to understand but just as easy to use as a spreadsheet.
There is no single best tool. Business users can use spreadsheets for quick data gathering and ad-hoc analysis. SQL is for querying applications but it gets complicated very quickly. SQL’s sweet spot is with software developers building advanced applications. We can use Cubes as advanced spreadsheets but lose some flexibility.
PivotXL
PivotXL utilizes the simplicity and flexibility of a spreadsheet and adds a layer of data organization on top of spreadsheets. The software utilizes the modern Microsoft Excel add-in technology to connect 2D Excel cells to a Cube cell in a simple graphical user interface. There are other advanced features for maintaining data integrity as the data in the dimensions change.
