SQL Server Analysis Services Finally Coming to the Cloud!

Microsoft has announced a new cloud service in preview – SQL Server Analysis Services.  This has been a glaring gap in the cloud stack for years and while you could deploy databases through Azure SQL and/or Azure Data Warehouse, you could not deploy SSAS cubes.  If you wanted to deploy cubes to the cloud, the only approach supported was to spin up a SQL Server as IAAS and pay the full on premise licenses to run it.

The new SSAS service supports data sources from both cloud and on premise including SQL Server, Oracle, SharePoint and HD Insight.

Data sources

The new SQL Server Analysis Services only supports tabular models – multi-dimensional cubes are not supported currently.  Pricing also has not been provided yet.

Read More

Auditing Arrives in Preview to Power BI

Microsoft today announced the addition of new auditing capabilities within Power BI.  The new features are only available in preview to United States customers for now.

For regulated organizations, being able to audit and analyze who has viewed dashboards or reports is an important requirement.  The new auditing features record every user view, export or change to data within Power BI.  The auditing data is available to export or can be viewed within the Office 365 Security and Compliance Portal.

Image result for office 365 security and compliance portal

Read More

Azure SQL Now Supports JSON Storage and Query

Microsoft has just released into General Availability support for storage and retrieval of JSON data.  JSON is a convenient data format used by web sites, JavaScript, REST APIs, etc.  Using the new JSON functions, you can run a query against an Azure SQL database and return a JSON object or you can store JSON entities as values within Azure SQL tables.

The ability to store and retrieve JSON also means you can combine traditional relational data with no-SQL JSON data and use SQL to query both at the same time and to link relational data records and semi-structured JSON data together through queries.

Read More

Power BI Embedded Pricing Simplified as Service Becomes Generally Available

Power BI Embedded is a new service available to developers or independent software vendors to allow them to embed Power BI dashboards into their applications.  Unlike the traditional Power BI per user subscription licensing, Power BI Embedded is based on consumption of reports by any user accessing the application.

Power BI Embedded

Microsoft has changed the pricing model as the service becomes generally available.  In the original pricing model, the price was per render of a dashboard or report.  1,000 “renders” was priced in preview at US $2.50.  In the new pricing model, pricing is now per “session”, e.g. each unique user connecting to a unique report.   Each of these sessions is now US $0.05 for a maximum 60 minute session.  If your application has 10 reports and a user accesses all 10 reports, that’s $0.50 charged.

The advantage of this new pricing model is easier methods for calculating the consumption costs – it was difficult to figure out how many renders of a dashboard were needed for an application and on average how many might be consumed by an end user.  With this model, it becomes easier to predict based on the number of users sessions you have in your application.

In addition, Microsoft has made development of reports free with an Azure subscription.  Developers can access their own reports for dev/test at no cost – only end user consumption is charged.

Read More

Microsoft Adds Excel Support to the Office 365 REST API

Microsoft has added support for interacting with Excel documents, formulas and reporting through the Office 365 REST API.

Breakdown of tasks in a pie chart

Key scenarios that this could enable include:

  • Reading and writing data to an Excel document stored within Office 365
  • Calling an Excel formula for calculations
  • Having Excel online render a chart or graph based on Excel data

Code samples and documentation are available here.

Read More

Azure Data Warehouse is Released to General Availability

Microsoft has just announced the release of Azure Data Warehouse to general availability.  Azure DW is a fully managed data warehouse that leverages elastic and hyper scale database cloud technologies to provide massively scalable SQL capabilities. 

Azure SQL Data Warehouse uses an elastic massively parallel processing (MPP) architecture built on top of the industry-leading SQL Server 2016 database engine. It allows you to interactively query and analyze data using the broad set of existing SQL-based tools and business intelligence applications that you use today. It uses column stores for high performance analytics and storage compression, a rich collection of aggregation capabilities of SQL Server, and state of the art query optimization capabilities. With built-in capabilities such as Polybase, it allows you to query Hadoop systems directly, enabling a single SQL-based query surface for all your data.

Azure DW comes with a number of features including polybase for combining HD Insight and SQL queries, very fast scaling,  transparent encryption, auditing, threat detection and Active Directory integration. 

Read More

Azure SQL Always Encrypted Now Generally Available

Microsoft has just announced that their “Always Encrypted” feature for Azure SQL has now been released to General Availability.

Always Encrypted allows you to consistently store columns of data within SQL tables as encrypted data.  The encryption/decryption happens at the .NET calling layer so that the underlying data at rest is always encrypted.  In order to encrypt/decrypt the calling application has to be registered, have sufficient permissions and access to the encryption keys.

By leveraging Always Encrypted, any DBA level access has no ability to decrypt the data without going through the application tier.  Any direct SQL call will result in encrypted data being returned.

Another advantage to this approach is selective column encryption – only columns specified by the schema are encrypted such as personal information, credit card numbers, etc.

Always in Encrypted in Azure SQL Database

Read More

Power BI Increases File Size Limit from 250 MB to 1 GB

Microsoft has announced that they are increasing the file size limit for files to 1 GB from 250 MB.  This limit impacts any data source which is not direct query enabled.  It also impacts any Power BI Desktop file that is published to the Power BI service.


We know many of you are doing analysis that involves large amounts of data and have large data models that you want to put into Power BI to share with others. After hearing your requests and seeing what you want to accomplish, we increased the file size limit for both Excel workbooks and Power BI Desktop files to 1 GB.

When you publish a PBIX file from Desktop, publish an Excel file to Power BI service, or upload an Excel or PBIX file through Get Data in the service, you won’t get a file size limit message unless the file is greater than 1 GB. Additionally, the data will continue to refresh as long as the data model size does not grow beyond 1 GB. After that, refreshing the dataset will give an error message letting you know your dataset is too large, and it will fail to refresh until you filter the data model back down under 1 GB.

Read More

Power BI Q&A Support for On Premise Data Sources Coming Soon

One of the most powerful features in Power BI is the natural language based Q&A feature.  It allows end users to pose questions and receive reports, dashboards and answers using natural language queries.  The Q&A query makes suggestions to the end user on what to ask as well as automatically displays the appropriate chart or graph based on the type of data.  End users can also pin any question to their dashboard.


The Q&A support is going to be extended to on premise data sources through the Enterprise Gateway.  Starting with SQL Server 2016 Analysis Services Tabular Models, now in preview, end users will be able to use the Q&A feature to access data on premise in the same as currently supported with cloud data sources.  Additional support for on premise data sources such as SQL Server, Oracle, SSAS, etc. is coming soon.

Read More

DataZen is Officially Dead, Customers Need to Transition to SQL 2016

Microsoft has officially killed the independent DataZen product stack with most of its features now incorporated into SQL Server 2016, SQL Server Reporting Services and Power BI.

Customers will be given 1 year of support following the release of SQL Server 2016 and then the product will be considered end of life. 

To offer Datazen customers more time to plan on when to transition to SQL Server 2016 Reporting Services Microsoft will provide support for existing deployments of Datazen-branded products for 1 year following the SQL Server 2016 GA release.

Our team is actively working on the SQL Server Migration Assistant for Datazen which will help customers in migrating content from Datazen Server 3.0 to SSRS 2016. We plan to release the Migration Assistant shortly after general availability of SQL Server 2016.

Read More