Storing data in SQL Server either locally or on Azure is expensive, especially for large datasets. Microsoft provides a few noSQL alternatives – Table Storage, Blob Storage and HDFS based (e.g. Hadoop) storage.
The economics for Table Storage are incredibly attractive in comparison to SQL Storage, especially for large data sets. For example, let’s say you had a 10 gigabyte database (not that large) – to host this on Azure SQL it will cost $48.46 / month. In comparison, you can host the same data in Azure Table Storage for $0.75 / month! While not appropriate for complex relational databases, it is ideal for large volumes of records that need to be stored.
In the original Power Pivot addon for Excel, you could access oData based data sources. Technically, Azure Table Storage is oData but it requires a shared key authentication mechanism that the Power Pivot addon didn’t support. There have been work around solutions published but they required proxy solutions to supply the appropriate key.
Power Query now solves this problem by supporting Windows Azure Table Storage and Blob Storage natively!
