4 ms·

I'm not disagreeing with what you've included above, but I'd like to add a fourth, new use case. Excel is increasingly being used as Microsoft's gateway drug t

by greggyb 11y ago

I'm not disagreeing with what you've included above, but I'd like to add a fourth, new use case. Excel is increasingly being used as Microsoft's gateway drug to an excellent self-service BI platform, Power BI. There is incredible power in providing a smooth (and approaching seamless) upgrade path from workbook with embedded queries, to a proper dimensional modelling tool (Power Pivot, AKA SSAS Tabular columnstore engine), to a proper dashboard visualization tool (Power View), to a hosted, centralized team solution (Power BI portal - mobile app batteries included), to an on prem enterprise-grade analysis engine (SSAS Tabular). Again, this is not a refutation, but a new use case and powerful new functionality to address the concerns you raised in Fallacy 2.

chris_wot 11y ago
That's quite cool - didn't really know about that! The only thing is that the reason VBA is so widely used is because it's built in to every single version of Excel (except Excel 2008 on OS X), which is why it's ubiquitous. Are Power BI and PowerView things that can be used within Excel without using a server? Are there additional licensing costs?
- greggyb 11y ago
  Power BI is a series of free add-ins for Excel starting in 2010. Depending on your year and version they are either included or are downloadable. They are available in the standalone products and Pro Plus. Additionally there is a standalone, free program Power BI Desktop Pro which includes all the pieces without a dependency on Excel at all. The desktop and mobile apps are completely free and the desktop pieces can be used without a server. The web portal is cloud-hosted and freemium to anyone with a corporate email address and the mobile apps require at least the free web portal. Feel free to email me if you're curious about more details. Gregory.Baldini at gmail dot com.
  - chris_wot 11y ago
    Oh! I think I will tomorrow - I just finished a stint developing some Qlikview reports so I'm interested in other BI solutions.
frik 11y ago
And yet this BI platform shares the same limitation as Excel. It uses Excel WebApp hosted as SharePoint WebPart/"App". The Excel row and column limits are also true for BI. If you want to show millions of data entries, better choose a more mature BI platform. On the one side companies want to ged rid of their Excel data silos with many unverified formulas, on the other side some choose a BI platform based on Excel (data is loaded from MSSQL DB).
- greggyb 11y ago
  This is not at all true. The Power Pivot add-in provides a mature in-memory columnstore database engine with no hard row limits - this can easily manage 10s of millions of rows, and with a decent model architecture can grow to 100s of millions. When upgrading to SSAS Tabular on a server with enough RAM, billions of rows are not unreasonable. Additionally, the hosted solution allows Excel Services to be the presentation layer, but the real push is toward Power View. I would suggest you do more research before spreading this disinformation. There has been significant development on this front and what you are saying is very much out of date.
  - frik 11y ago
    What I wrote is true for MS BI on Sharepoint 2013 with MS SQL Cluster 2012 in real world. It's probably the co consultants fault to use SQL2012 instead of SQL2014. MS mentions the new 2016 release on the upcoming MS SQL version will do away some of the current limitations, as you mentioned.
    - greggyb 11y ago
      SharePoint 2010 and SQL Server 2008R2 introduced support for hosted Power Pivot workbooks.[0] Power Pivot is the Excel add-in (available for Excel 2010+) which enables dimensional modelling and >1M row analysis in Excel, and which can be transitioned to a hosted environment via SharePoint and SQL Server. Starting with SQL Server 2012, SSAS includes Tabular mode, which allows a seamless upgrade path from an Excel Power Pivot model to an SSAS instance. This is seamless because the same DB engine is used in both products. Power Pivot runs a private, anonymous instance of SSAS Tabular behind the Excel process, with a few features disabled (e.g. row-level security). Power View is a high level visualization and dashboarding tool introduced for SharePoint 2010 and 2013 as a part of the SQL Server 2012 SSRS module.[1] This is the visualization tool designed to pair with Power Pivot and the Tabular model. It is also available as an add-in for Excel 2013+[2], and as part of the standalone Power BI Desktop[3] which has been available since Power BI portal's general availability in July of 2015. I do not know what you are referring to by saying that there is a 1M row limit in practice with SP 2013 and SQL Server 2012, because this is simply not the case. If your organization is not using the features and these are pain points for you, I'd love to have further discussion with you offline - my email is gregory.baldini at gmail dot com. [0]https://technet.microsoft.com/en-us/library/ee210682(v=sql.105).aspx?f=255&MSPPError=-2147217396 https://technet.microsoft.com/en-us/library/ee210682(v=sql.1... [1]https://support.office.com/en-us/article/Power-View-in-SharePoint-Server-Create-save-and-print-reports-f616575e-02bf-46cd-b94a-b744245ef9d1 https://support.office.com/en-us/article/Power-View-in-Share... [2]https://support.office.com/en-us/article/Power-View-Explore-visualize-and-present-your-data-98268d31-97e2-42aa-a52b-a68cf460472e https://support.office.com/en-us/article/Power-View-Explore-... [3]https://powerbi.microsoft.com/en-us/desktop https://powerbi.microsoft.com/en-us/desktop

by greggyb 11y ago

chris_wot 11y ago

That's quite cool - didn't really know about that! The only thing is that the reason VBA is so widely used is because it's built in to every single version of Excel (except Excel 2008 on OS X), which is why it's ubiquitous. Are Power BI and PowerView things that can be used within Excel without using a server? Are there additional licensing costs?

greggyb 11y ago
Power BI is a series of free add-ins for Excel starting in 2010. Depending on your year and version they are either included or are downloadable. They are available in the standalone products and Pro Plus. Additionally there is a standalone, free program Power BI Desktop Pro which includes all the pieces without a dependency on Excel at all. The desktop and mobile apps are completely free and the desktop pieces can be used without a server. The web portal is cloud-hosted and freemium to anyone with a corporate email address and the mobile apps require at least the free web portal. Feel free to email me if you're curious about more details. Gregory.Baldini at gmail dot com.
- chris_wot 11y ago
  Oh! I think I will tomorrow - I just finished a stint developing some Qlikview reports so I'm interested in other BI solutions.

frik 11y ago

And yet this BI platform shares the same limitation as Excel. It uses Excel WebApp hosted as SharePoint WebPart/"App". The Excel row and column limits are also true for BI. If you want to show millions of data entries, better choose a more mature BI platform. On the one side companies want to ged rid of their Excel data silos with many unverified formulas, on the other side some choose a BI platform based on Excel (data is loaded from MSSQL DB).

greggyb 11y ago
This is not at all true. The Power Pivot add-in provides a mature in-memory columnstore database engine with no hard row limits - this can easily manage 10s of millions of rows, and with a decent model architecture can grow to 100s of millions. When upgrading to SSAS Tabular on a server with enough RAM, billions of rows are not unreasonable. Additionally, the hosted solution allows Excel Services to be the presentation layer, but the real push is toward Power View. I would suggest you do more research before spreading this disinformation. There has been significant development on this front and what you are saying is very much out of date.
- frik 11y ago
  What I wrote is true for MS BI on Sharepoint 2013 with MS SQL Cluster 2012 in real world. It's probably the co consultants fault to use SQL2012 instead of SQL2014. MS mentions the new 2016 release on the upcoming MS SQL version will do away some of the current limitations, as you mentioned.
  - greggyb 11y ago
    SharePoint 2010 and SQL Server 2008R2 introduced support for hosted Power Pivot workbooks.[0] Power Pivot is the Excel add-in (available for Excel 2010+) which enables dimensional modelling and >1M row analysis in Excel, and which can be transitioned to a hosted environment via SharePoint and SQL Server. Starting with SQL Server 2012, SSAS includes Tabular mode, which allows a seamless upgrade path from an Excel Power Pivot model to an SSAS instance. This is seamless because the same DB engine is used in both products. Power Pivot runs a private, anonymous instance of SSAS Tabular behind the Excel process, with a few features disabled (e.g. row-level security). Power View is a high level visualization and dashboarding tool introduced for SharePoint 2010 and 2013 as a part of the SQL Server 2012 SSRS module.[1] This is the visualization tool designed to pair with Power Pivot and the Tabular model. It is also available as an add-in for Excel 2013+[2], and as part of the standalone Power BI Desktop[3] which has been available since Power BI portal's general availability in July of 2015. I do not know what you are referring to by saying that there is a 1M row limit in practice with SP 2013 and SQL Server 2012, because this is simply not the case. If your organization is not using the features and these are pain points for you, I'd love to have further discussion with you offline - my email is gregory.baldini at gmail dot com. [0]https://technet.microsoft.com/en-us/library/ee210682(v=sql.105).aspx?f=255&MSPPError=-2147217396 https://technet.microsoft.com/en-us/library/ee210682(v=sql.1... [1]https://support.office.com/en-us/article/Power-View-in-SharePoint-Server-Create-save-and-print-reports-f616575e-02bf-46cd-b94a-b744245ef9d1 https://support.office.com/en-us/article/Power-View-in-Share... [2]https://support.office.com/en-us/article/Power-View-Explore-visualize-and-present-your-data-98268d31-97e2-42aa-a52b-a68cf460472e https://support.office.com/en-us/article/Power-View-Explore-... [3]https://powerbi.microsoft.com/en-us/desktop https://powerbi.microsoft.com/en-us/desktop