Search This Blog

Wednesday, August 12, 2015

Why I still like Excel PowerPivot models in SharePoint however Tabular solutions in SSAS are better

[2015-Aug-12] It’s quite obvious that in present time there is a growing audience for all the latest changes and updates within the Microsoft Power BI sphere. I think someone has already built a data model that showed number of tweets about Power BI which prevailed other more usual and heavily discussed software development topics on one day. However I would still like to share some thoughts that came as result of building PowerPivot data model in Excel/SharePoint vs. Tabular solutions.

Also this post was inspired by (Tabular Models vs PowerPivot Models) and (When to choose tabular models over PowerPivot models).

So here are a few risks or disadvantages that we may face with PowerPivot Excel data models SharePoint:

Performance/Availability to users

  • Excel PowerPivot in SharePoint is just a physical file that internally holds a backup of tabular model that automatically gets restored into a memory and is utilized whenever users try to interact either with linked PowerView report or manage Excel files itself. Once this “in-memory” file tabular database is not used for while it gets deleted by internal garbage collector. That means that after a short while, it will take another waiting time to bring that “hibernated” data model into memory before it will be available for users.
  • Server Tabular model is a different type in memory model, that still may be “cleansed” by internal garbage collector; and for sure certain performance tests that will compare Excel PowerPivot model vs. Tabular model needs to be built. However preliminary tests showed that Tabular model was more responsive during user interaction.


Stability

  • SharePoint Excel PowerPivot books live under control of Excel Services, PowerPivot Services and Analysis Services within SharePoint environment and may be affected by user activity with other SharePoint objects; SharePoint disk and memory space might bring some other dependency on life of Excel PowerPivot models.
  • So having a dedicated, set apart from SharePoint, SQL Server Tabular environment may bring a peace of mind to all users who will rely on their reporting data availability.


Scalability 

  • Even with a standalone Excel file 2 GB limit, we are faced with different file size limitations that SharePoint will handle (2 GB is still an option in SharePoint, but this has to be specifically defined).
  • With a tabular database we only have to deal with how its hardware infrastructure is organized (Hardware Sizing a Tabular Solution (SQL Server Analysis Services))


Development/Deployment

  • I like both Excel development environment as well as Visual Studio for tabular solution. In both places I can define all the metrics, build relationships between tables and define necessary calculations.  
  • Deployment could be tough with Excel PowerPivot work books: I’ve built a PowerShell scripts that prepares and updates metadata within each Excel PowerPivot data models prior their uploading to different environments in SharePoint (http://datanrg.blogspot.ca/2015/06/updating-powerpivot-model-and-powerview.html). However it’s still depended on how an Excel XML document is structured in Excel 2013 and this may get changed in later versions.


Manageability

  • You’re on your own with managing an Excel PowerPivot model: it’s you and Excel. Yes, SharePoint will let you to schedule data model refresh once a day; however this schedules may be jeopardized by frequent updates and interactions with Excel files themselves (https://support.microsoft.com/en-us/kb/2874957)
  • Tabular solutions can be backed up, restored, detached, and attached; PowerShell cmdlets can be applied. And most importantly we can trigger a partial or complete data refresh of our server data models multiple times a day (XMLA scripting could only be a great benefit for this).


Security

  • When an Excel PowerPivot model file is deployed to SharePoint, users will or will not have a complete access to the data model (depending on their access permission scope defined in SharePoint).
  • With tabular solution we can utilize all the benefits or role base security model and manage access to our server data models on a more granular lever.


Will all this being said, I still see that an initial PowerPivot data model could be designed and developed in Excel; however once it’s defined and tested then it’d be wiser to export this model into a tabular solution for a better manageability and data availability for end users.