When a Cube is Still Better Than PowerPivot

PowerPivot is an amazing tool for performing ad hoc querying of data sources, creating dynamic models and empower business users to manage their own data feeds.  For all those scenarios where your Excel power users are saying, “Just give me the data”, PowerPivot provides a very robust solution.  Combined with SharePoint, collaborating, sharing and publishing of PowerPivot models is really easy as well – just upload it to SharePoint and distribute to teams through your intranet.

Having now worked with both PowerPivot and SSAS cubes, here are some suggestions as to when using cube is still a better option.  (Another option is to use both – use an underlying cube as a pre-aggregated, high performance data repository and then use PowerPivot to just grab a piece of the data set for analytics purposes).


Your Desktop Clients are SLOW, Old or 32 Bit

PowerPivot works amazingly well on my machine – but I have a 64 bit Windows laptop with 32 GB of RAM.  The available RAM is what makes PowerPivot fast and able to handle millions of rows.

If you’re running on an older machine, still running 32 bit windows or have limited RAM available then PowerPivot performance will likely suffer.

One alternative solution is to use SharePoint and render Excel files using Office Web Apps.  This is a good work around for read only access to dashboards, reports, etc. but this doesn’t help with editing workbooks, creating new models or performing ad hoc queries.

Your Data is Incrementally Growing

One of the key weaknesses in PowerPivot is there is no support for incremental refresh. 

When you refresh data, the data in the PowerPivot window is replaced with new data. You cannot import just new rows of data or just changed data. PowerPivot for Excel does not track which rows were added previously.

We have many clients who have a large volume of historical records and they are adding a couple hundred thousand rows every month.  In PowerPivot, I cannot just grab the new rows in my refresh – it grabs the entire data set from the beginning.  If you have millions of rows, this can be very slow. 

You Need to Analyze TENS of Millions of Rows

I have PowerPivot models that are in the millions of rows (see my blog post here) and they work quite nicely.   The data refresh is quite slow (e.g. 15-20 minutes) but I can create PowerView dashboards and they are quite responsive even with millions of rows.

If your dataset is tens of millions of rows, then having a cube to aggregate and crunch the data is going to make a massive difference in performance. 

You have more than 250 MB of data and You want to Share via Office 365

With 64 Bit Excel 2013, file size limitations have been removed (they used to be 2 GB on disk and 4 GB in memory).  However, there are still size limitations with SharePoint – SharePoint Server has a 2 GB file size limitation for uploading any file to a document library. 

In addition, Power BI for Office 365 has a 250 MB limitation for Excel Online and Office 365 alone has a limitation of 30 MB for a maximum workbook size. 

You Need Granular Security Over Your Data

SQL Server Analysis Services provides a role based security model that allows you to restrict access to particular dimensions and cells within the cube.  Imagine you have a cube that you have rows organized by geography, sales person and product dimensions.  In SSAS you can create rules so that users can only access cells that match particular slices of the cube based on MDX expressions.  For example, you could create a rule that users in the “Michigan” group can only see data from their state.

PowerPivot is an all or nothing security model – queries and data refreshes are governed by a single user account that has access to all the data.  In addition, once the data has been refreshed it sits locally in the excel file and as long as I can open the file I have access to the data.

Read More

New Microsoft Azure Service for the Internet of Things

The Internet of Things is about linking devices, sensors, wearable technology and other embedded systems to cloud based applications that will collect, analyze and present data from this network of connected data collectors.

Microsoft has announced today the limited preview of the Microsoft Azure Intelligent Systems Service, a specific service targeted for the Internet of Things.

Enterprises using the Intelligent Systems Service to extend the Azure cloud across connected devices and sensors can capture vital data, analyze it with familiar Microsoft tools such as HDInsight and Power BI for Office 365, and then quickly take the appropriate action that drives impact. Underlying all of these capabilities is enterprise-grade security developed and supported by Microsoft.

Bringing the Internet of Things to the London Underground…

Read More

Microsoft Azure is now the ONLY Cloud Service to Comply with European Union Privacy Laws


Microsoft has just announced that the European Union data protection authorities have recognized that Azure’s enterprise cloud contracts meet the EU’s privacy laws.  The EU data protection authorities have issued a joint letter stating this approval.

Microsoft is the first and so far the only company to receive this approval.

The statement doesn’t just cover Microsoft’s European data centers – it also covers data stored in American data centers as well.

By acknowledging that Microsoft’s contractual commitments meet the requirements of the EU’s “model clauses,” Europe’s privacy regulators have said, in effect, that personal data stored in Microsoft’s enterprise cloud is subject to Europe’s rigorous privacy standards no matter where that data is located. This is especially significant given that Europe’s Data Protection Directive sets such a high bar for privacy protection.

The work that Microsoft is doing in terms of compliance with various laws, regulations and standards including ISO standards, HIPPA standards, and now EU privacy laws is a clear differentiator as enterprise cloud customers demand strict compliance with privacy regulations and enterprise class security.  image

Microsoft has also recognized the concern of government snooping into enterprise data.  Customers will be able to encrypt just about everything stored in the cloud:

This effort will include our major communications, productivity and developer services such as Outlook.com, Office 365, SkyDrive and Windows Azure, and will provide protection across the full lifecycle of customer-created content. More specifically:

· Customer content moving between our customers and Microsoft will be encrypted by default.
· All of our key platform, productivity and communications services will encrypt customer content as it moves between our data centers.
· We will use best-in-class industry cryptography to protect these channels, including Perfect Forward Secrecy and 2048-bit key lengths.
· All of this will be in place by the end of 2014, and much of it is effective immediately.
· We also will encrypt customer content that we store. In some cases, such as third-party services developed to run on Windows Azure, we’ll leave the choice to developers, but will offer the tools to allow them to easily protect data.
· We’re working with other companies across the industry to ensure that data traveling between services – from one email provider to another, for instance – is protected.

This type of enterprise class protection of data is a key differentiator and one that is worth reviewing when comparing enterprise cloud services.

Read More

The Basics of Working on a Shared Spreadsheet Using Office Online

The new web office is easy to use, integrated into OneDrive and supports sharing with anyone.  It’s also free! 

Why would you use Google Docs when you can have real Excel, Word or PowerPoint?

Here are the basics to get you started…

Go To Office Online

Office online is at office.live.com.   Click on Word, Excel, OneNote, Outlook or PowerPoint to go to the web based versions of these office tools.


Sign In With a Microsoft Account

In order to use the tools, you need to have a Microsoft account.  In my case, I have a work account and my personal hotmail account and both work.


Create Your File

Create a new file either as a blank document or from the various templates available.  You can also load files from your OneDrive as well.


I can created a Profit and Loss Excel file from one of the templates.

Save Your File

You can Save As and name your file.  This will save it to your OneDrive for safe keeping.


Note: Office Online automatically Saves as you go so there is no need to save once you have created your file. 

Share Your File

Office online allows for collaboration by sharing of your file.  To share you just go to File->Share and invite by email address the people with whom you wish to share.  If you click on the “Recipients can Edit” then the people you select will be able to edit your file. 


All edits go back to your OneDrive automatically and the permissions are also set automatically.  Your recipients will receive an email with a link to your file that looks something like this:



Co-authoring is built into the Office experience.  If multiple contributors access the file at the same time, both of their changes are incorporated into the document in real time and they can see any edits from other authors.

Read More

Microsoft Azure and IIS are likely NOT Vulnerable to Heartbleed But You Could Have Introduced Your Own Vulnerabilities

Heartbleed logo

As many are hearing through the news media, Heartbleed is a vulnerability in OpenSSL that allows for a hacker to steal information by reading the memory of systems protected by specific versions of the OpenSSL software.

The reason why the vulnerability is getting so much attention is that OpenSSL is the default SSL implementation for Apache and nginx.  These web servers run the majority of the worlds web servers:

Netcraft server stats

Are Microsoft Web Servers Affected?

Microsoft uses a different implementation of SSL called Secure Channel so if you are running IIS your web server isn’t impacted.  Microsoft Azure itself is not impacted either for the same reason.

Microsoft issued a statement yesterday saying as much and you can find it here.

Could You Still Be Vulnerable?

The answer to the question is absolutely YES, depending on what you have introduced into your infrastructure that uses OpenSSL.  There are a couple scenarios that are clear risks:

  • If you are running a Linux image even if its running within an Azure Virtual Machine, you could still be vulnerable. 
  • If you are running Apache Web Server or nginx even if its on Windows, you could still be vulnerable.
  • If you are running a Java or open source program (either acquired or custom built) that uses the OpenSSL libraries you could still be vulnerable.
  • If you are managing your SSL through your firewall or SSL gateway and its Linux based or uses OpenSSL as its implementation, you could still be vulnerable.

For more details, see Troy Hunt’s excellent article on Heartbleed….

Read More

Don’t Install SharePoint 2013 SP1 If You Are Running Nintex Workflow

Over the past few days, SharePoint 2013 SP1 has been recalled by Microsoft.  For those of you who have not installed SP1, we recommend waiting until this problem is fixed by Microsoft and a new cumulative update or service pack is available.

Here is another reason to delay installing SharePoint 2013 SP1 – there is an issue with Nintex Workflow.

Nintex Workflow is an enterprise workflow designer that installs on top of SharePoint to provide a business user driven workflow design surface.  It’s quite popular and we have seen it used by many of our clients.


Here is the content from the original post…there is a fix coming in a few weeks.

**UPDATE 3rd April 2014 **

We are pleased to announce support for SharePoint 2013 SP1 will be introduced in the 3.0.7 build of Nintex Workflow 2013 which will be available in the coming weeks.

If you have already applied SP1 and are experiencing issues, please contact support@nintex.com for further assistance.

Thank you for your patience.

**Original Post**

We have identified an issue with Nintex Workflow 2013 in SharePoint 2013 Service Pack 1 environments that may cause a script error during workflow design.

At this time, our development team are investigating the issue with a view to releasing an update to address the problem.

We will update this post with further information once it is available.

Read More

AutoScale Adds More Advantages to a PAAS Strategy

While Infrastructure as a Service (IAAS) continues to dominate the cloud world, there are some compelling reasons to look at Platform as a Service (PAAS) as an option.  For those applications architected specifically for the cloud, PAAS offers potentially lower costs, less management and better scaling than IAAS. 

(Troy Hunt wrote a really good article on the advantages of using Azure Web Sites instead of managing your own virtual machines.)

With the new AutoScale service, Azure provides additional management services for automatically scaling up and down your VMs or service instances.  With PAAS, the autoscale feature is really easy to use because you don’t have to create or maintain your own virtual machines.

AutoScaling IAAS

One of the key challenges in any application architecture is scalability.  If you are managing your own VMs through IAAS, you need to set up your own high availability strategy and Microsoft Azure provides tools for creating “availability sets” for clusters of virtual machines. 

Update domains

For every new virtual machine you add to your cluster, you have to manage patches, updates, licenses, etc.

NOTE: AutoScale only works for stateless virtual machines (e.g. IIS) because each virtual machine is assumed to be able to turned on or off without needing to be persistent.

With the new AutoScale service, you can automatically activate your virtual machines in an availability set. 

AutoScaling PAAS

Platform as a Service in Azure includes Azure Web Sites, Cloud Services (web roles and worker roles), Queues, and Azure Storage (sql, table, blob). 

In each of these services, you don’t have to manage each virtual machine.  Azure manages the entire service for you, allowing you to focus on the application.

AutoScaling these PAAS services becomes really simple – you set rules to specify when and how to scale up or down and Azure takes care of the rest.  The key criteria available today are CPU usage, Queue and Schedule.

With CPU usage, you can autoscale when your instances exceed your target CPU usage threshold.

Target cpu

With Queue, you can autoscale based on the number of messages waiting to be processed in your Queue – if the messages cannot be processed fast enough, the service will add more instances to improve performance.

Scale queue

With Schedule, you can autoscale based on peak periods.

Schedule scaling

With PAAS and IAAS, I can autoscale, but with PAAS the instances are a lot easier to manage and I don’t have to configure the availability set.   As the need to scale up and down your instances becomes key to your performance, having someone else manage all these autoscaling virtual machines becomes an attractive option in comparison to having to manage your own VMs using IAAS.

Read More

New Azure Web Sites “Basic” Option Now Available

Microsoft has now introduced a new “Basic” Option for Azure Web Sites.  Before we had “Free”, “Shared”, and “Standard” – now we have “Free”, “Shared”, “Basic” and Standard.

Basic is $59 / month in comparison to Standard at $79 / month for a small VM.  In both cases, you can run as many web sites as you like on a single VM and/or scale out your web farm to multiple VMs as you need additional performance.  With Basic, you can scale out to 3 dedicated instances where with Standard you can scale out to 10 dedicated instances.

One of the key benefits to Standard is the ability to AutoScale.  In Shared and Basic mode, you have to manually monitor and scale up or down your instance count through the Azure portal.  In Standard mode, you can set rules on when to trigger increases in your instances such as by schedule (for peak periods) or by metric (e.g. when your CPU becomes overloaded.


Scale By Metric

Through the Azure portal, you can easily upgrade or downgrade your web site from one plan to another.  You only get charged for the periods that your site is actively in the plan.  So for example, you could run Shared or Basic 80% of the time and then move up to Standard for those peak periods where you need AutoScale and more than 3 instances for your web site.


Read More