Charley
Member
- Joined
- Jul 29, 2017
- Messages
- 64
- Reaction score
- 0
- Points
- 6
- Excel Version(s)
- 365
I've never understood the business strategy for using Power Pivot. Perhaps someone could explain it to me.
As I understand it, Power Pivot allows us to store tables with millions of records in a workbook, manipulate the data with DAX, and then report the results only from within that one massive workbook. Because there's little practical way for other workbooks to reference that data, one company could have thousands of massive workbooks containing similar, but not necessarily the exact data. And even workbooks that contain identical queries could be updated at different times to generate different results.
That is, Power Pivot appears to give companies multiple versions of the truth, but on a larger scale than ordinary Excel.
And speaking of scaling. I've seen questions in this forum from people who have outgrown Power Pivot's capacity, and there appears to be no solution for them. Similarly, the larger the Data Model, the slower all that relational processing becomes.
In contrast, I know of at least two products on the market that give Excel worksheet formulas live links to massive amounts of OLAP data. (With OLAP, Excel formulas can return data from multidimensional cells in cubes that reside on an OLAP server.)
At least one of these products gives Excel real-time access to petabytes of data. The data can flow continuously from or to just about anywhere: Excel, Access, SAP Business Objects, SAP HANA, SQL Server, Oracle, SharePoint, Dynamics, Tableau, QlikView, ArcPlan, CrystalReports, and so on.
Response time is VERY fast, because the data resides in RAM, which is backed up in real time on SQL Server.
At the other end of the scaling spectrum, at least one of the companies offers single-user versions of their product. (The other company used to do so, but I don't think they offer single-user versions anymore.)
Both products offer cube formulas, which allow Excel-like formulas to return results across many dimensions, and can reference data in many cubes. So, for example, one cube can contain exchange rates while formulas in all other cubes can support a Currency dimension that allows users to choose which currency they wish to see. In Excel, they can make the choice using an interface that resembles a data validation list.
And unlike the "Power" world, both of these "Excel-friendly OLAP" products offer two ways for users to WRITE data to the server from their worksheets. Users can write the data manually into cells that update the server in real time, or allow Excel formulas to write the data to the server in real time. (There's full security, of course.) Years ago, I used the formula-write-back version to update a multidimensional rolling forecast, which I updated monthly by opening, recalculating, and then closing a workbook. But these days, a small number of cube formulas could maintain that rolling forecast automatically...for all organizational units in the company.
I know of a retailer with about 2000 stores worldwide, whose executives get currency-translated sales results at any level of consolidation on their mobile devices...in real time. (That is, not only can they see how today's worldwide sales are trending by hour in US Dollars, they could see how a specific SKU's sales--denominated, say, in Euros--have been going in Mexico for the past few minutes.) The data flows continuously from cash registers to SAP and then to their OLAP cubes. Excel users with the proper credentials can read the same data, of course. And the company relies heavily on cube formulas to generate rolling forecasts and to generate analytics.
So, would somebody please explain the strategic thinking behind the enthusiasm for Power Pivot? When you outgrow Excel Tables, why don't you immediately switch to small versions of Excel-friendly OLAP products, which...
...offer virtually infinite scaling,
...have a faster response time than relational databases,
...don't require domain experts throughout the company—like people from Finance, Marketing, HR, and so on—to learn obscure relational programming languages,
...support Excel-like cube formulas,
...offer write-back from worksheets, and,
...give real-time updating, so that everyone in any company of any size sees just one version of the truth?
I don't understand it. I hope someone can explain it to me.
Charley
As I understand it, Power Pivot allows us to store tables with millions of records in a workbook, manipulate the data with DAX, and then report the results only from within that one massive workbook. Because there's little practical way for other workbooks to reference that data, one company could have thousands of massive workbooks containing similar, but not necessarily the exact data. And even workbooks that contain identical queries could be updated at different times to generate different results.
That is, Power Pivot appears to give companies multiple versions of the truth, but on a larger scale than ordinary Excel.
And speaking of scaling. I've seen questions in this forum from people who have outgrown Power Pivot's capacity, and there appears to be no solution for them. Similarly, the larger the Data Model, the slower all that relational processing becomes.
In contrast, I know of at least two products on the market that give Excel worksheet formulas live links to massive amounts of OLAP data. (With OLAP, Excel formulas can return data from multidimensional cells in cubes that reside on an OLAP server.)
At least one of these products gives Excel real-time access to petabytes of data. The data can flow continuously from or to just about anywhere: Excel, Access, SAP Business Objects, SAP HANA, SQL Server, Oracle, SharePoint, Dynamics, Tableau, QlikView, ArcPlan, CrystalReports, and so on.
Response time is VERY fast, because the data resides in RAM, which is backed up in real time on SQL Server.
At the other end of the scaling spectrum, at least one of the companies offers single-user versions of their product. (The other company used to do so, but I don't think they offer single-user versions anymore.)
Both products offer cube formulas, which allow Excel-like formulas to return results across many dimensions, and can reference data in many cubes. So, for example, one cube can contain exchange rates while formulas in all other cubes can support a Currency dimension that allows users to choose which currency they wish to see. In Excel, they can make the choice using an interface that resembles a data validation list.
And unlike the "Power" world, both of these "Excel-friendly OLAP" products offer two ways for users to WRITE data to the server from their worksheets. Users can write the data manually into cells that update the server in real time, or allow Excel formulas to write the data to the server in real time. (There's full security, of course.) Years ago, I used the formula-write-back version to update a multidimensional rolling forecast, which I updated monthly by opening, recalculating, and then closing a workbook. But these days, a small number of cube formulas could maintain that rolling forecast automatically...for all organizational units in the company.
I know of a retailer with about 2000 stores worldwide, whose executives get currency-translated sales results at any level of consolidation on their mobile devices...in real time. (That is, not only can they see how today's worldwide sales are trending by hour in US Dollars, they could see how a specific SKU's sales--denominated, say, in Euros--have been going in Mexico for the past few minutes.) The data flows continuously from cash registers to SAP and then to their OLAP cubes. Excel users with the proper credentials can read the same data, of course. And the company relies heavily on cube formulas to generate rolling forecasts and to generate analytics.
So, would somebody please explain the strategic thinking behind the enthusiasm for Power Pivot? When you outgrow Excel Tables, why don't you immediately switch to small versions of Excel-friendly OLAP products, which...
...offer virtually infinite scaling,
...have a faster response time than relational databases,
...don't require domain experts throughout the company—like people from Finance, Marketing, HR, and so on—to learn obscure relational programming languages,
...support Excel-like cube formulas,
...offer write-back from worksheets, and,
...give real-time updating, so that everyone in any company of any size sees just one version of the truth?
I don't understand it. I hope someone can explain it to me.
Charley