This post is really a collection of field notes and some lessons learned from recent project experience. I’ve done plenty of SSAS Tabular projects over the past few years – usually starting with a Visual Studio project rather than Power Pivot. I’ve also done a bit of Power Pivot work for clients. These projects were either delivered to an analyst using Excel on their desktop or some business users through SharePoint. But, authoring in Excel Power Pivot and deploying to a server-hosted Tabular model has been mainly theoretical up to this point so I thought I’d share my experience.
Requirements
Executives and account managers in a large firm who are off to a conference where they will need to need to quickly lookup and analyze client and partner financial account information. The main fact table currently has a few million rows with about five years of history and the model compresses really well (to just over 200 MB). Ideally, all users would like to connect to their corporate servers but they’ll be in a conference setting with no control of their network environment.
Solution Options
I thought this was an ideal problem for Power Pivot to solve with offline workbooks that could be refreshed and synched-up after hours or on-demand. The catch is that everyone would need to upgrade to Office 2013 ProPlus with well-equipped laptops to support enough RAM and the 64 bit version of Office. We don’t have control over the hardware so this is a challenge – or at least a perceived challenge – for some users. The other option is to deploy the Power Pivot model to an SSAS Tabular server and give the users access to the model through a role. They could connect from Excel 2010 without taxing their local hardware but would need to have a rock-solid connectivity and VPN access to the SSAS server. A large portion of big companies continue to use Office 2010 while Microsoft has made add-ins and apps for Office 2013 (and soon, Office 2016) and there’s a lot of resistance to upgrade. As I explained these two options to users, it was kind of a “damned if you do” scenario. Installing Office 2013 ProPlus x64 raises concerns about corporate conformance and compatibility with specialized software that has Office extensions. On the other side is the need for a fast and reliable network connection; so the proposed solution is to maintain BOTH Power Pivot workbooks users can download and deploy a fresh copy of the model to SSAS every time a design change is made. This solution has worked pretty well and the following are some observations and lessons learned along the way:
Model Stability
Both Power Pivot in Excel and the SSDT Tabular model designer have some quirks but the tool in SSDT is generally more stable when working with larger data sets. On occasion, Excel will just shut down or not allow the xlsx file to be saved. I’ve had to reboot the development machine a couple of times for reasons I still can’t explain that resolved behavioral problems in Power Pivot. Because we need to continue to deliver part of the solution in Power Pivot, we can’t migrate the project to SSAS Tabular. Lesson (more like a reminder): make frequent backups of your workbook and save often.
Deployment
You can’t deploy a workbook model over the top of an existing SSAS database so you must delete the database. This removes roles and any other design additions we might make after deployment.
Security & Role Complications
To give users access to the deployed model, you must create at least one role with Read permission, and then add the users. Since the role gets deleted when we delete/re-deploy the model, the trick is to script the role in SSMS after adding the users. Every time you add a new user, you’ll need to re-script the role and save the xmla file for safe-keeping. When you run the script after re-deploying the model, you will need to grant Read rights to the role again because this property is stored in the database object and not in the role. Tiago Rente made a great suggestion in the comments below to restore Power Pivot to a different database name and then backup and restore, overwriting the main database from the new backup. This negates the need to script and reapply the roles using my earlier method. Nice suggestion, Thiago.
Maintaining Workbooks & Reports
Giving a group of users copies of a decentralized Power Pivot model can be a bit of a dichotomy because these are multiple copies of a single version of the truth. If users consistently refresh their models and resist the natural Excel users’ tendency to copy, paste and make offline copies (which promotes spreadsheet sprawl) they can benefit from updates and corrections to master data in the data warehouse or data mart. This is the problem that SSAS and Power Pivot were supposed to solve and can when appropriate discipline is exercised. It really comes down to training and demonstrating business value obtained when users can easily refresh their model with new data from a reliable source rather than making copies and promoting data sprawl. The notion of maintaining both connected and disconnected data models has been successful with other consulting clients but the degree of success depends on factors such as the business culture for change and adopting new methods, the opportunity for training and fostering internal user support communities, and sponsorship from an authoritive stakeholder.