With Azure SQL Data Warehouse, You Pay Only for Active Compute Use

One of the key principles of cloud economics is the ability to only for active use of a service.  However, many cloud services don’t actually work that way in reality – for example, Office 365 and Active Directory Premium both are priced per user and not per use.  If your end users only upload one document to SharePoint in a year, you pay the same price as the end user who uses it every five minutes.

Azure SQL is priced on a constant use basis – there is no mechanism for “pausing” an Azure SQL Database.  At a minimum, your Basic database will cost ~$5 US / month.  If you scale up your database to S1 (for example, if you have a database larger than 2 GB) the S1 database will cost ~$15 US / month.  Storage is bundled into the price at its fixed at 250 GB for Standard and 500 GB for Premium.

Azure Data Warehouse is priced differently – it is priced per hour based on how many performance units you use at $0.70 US / hr.  On a monthly basis, this is about ~$521 per month.  In addition, the storage price is independent and not bundled into this price.  You can pause your Azure Data Warehouse and only use it for when you need it.  In addition, you can scale up your warehouse to massive processing power for an hour and then scale it back down or pause the service.

Azure Data Warehouse uses RA-GRS storage which is $0.061 / GB for the first terabyte and then cheaper as your storage increases.  If you have a 10 TB warehouse, the cost of storage is ~$599 US / month. 

Read More

Admin Security Reviews Come to Azure AD Premium in Preview

Admin accounts have a significant risk exposure to enterprise organizations because they have access to potentially hundreds of services, accounts and settings that if compromised could wreak havoc on the organization’s overall security.  As part of an overall enterprise security program, validating these accounts to ensure they are still active and being used by the right people ensures that the threat to these accounts is minimized.

Microsoft has now introduced a new “Security review” process as part of Azure AD Premium (which is itself part of the Microsoft Enterprise Mobility Suite bundle) that allows the security administrator to validate administrator accounts through the following process:

  • Security administrator picks a privileged role, such as Global Administrator, where they believe administrators might still be holding that role who no longer need it.
  • Azure AD sends each user in that role a notification, and they respond in the Azure portal whether or not they need still need that role.
  • The security administrator reviews the results to decide who to remove from the role.

The security review process is in addition to the existing features of the Privileged Identity Management service which provides global administrators the ability to:

  • Discover and monitor privileged roles. The Azure AD PIM Dashboard gives you visibility into and tracking of users with privileged roles.
  • Automatically restrict the time that users have these privileged permissions through on-demand “just in time (JIT)” activation of permissions for pre-configured time windows.
  • Monitor and track privileged operations for audit purposes or security incident forensics.

Read More

Connecting to SharePoint Online Document Libraries from Power BI

The new Power BI Desktop has a data source specifically for SharePoint Lists. 


This works quite well but doesn’t currently support document libraries (even though they are also SharePoint lists).   If you connect to your SharePoint Online site you’ll find that the document libraries are not available through the SharePoint List data source, only lists.

If you want to connect to your document libraries (for example if you wanted to report on the number of documents being uploaded) you can do this through the OData feed instead.

The URL for the OData feed is: https://yoursharepointsite/_vti_bin/listdata.svc to bring up all the lists and document libraries in Power BI Desktop or if you want to connect to a specific list the URL will be: https://yoursharepointsite/_vti_bin/listdata.svc/ListName

Once you have created a connection to your list or document library, you can use the table of items as a data source and create dashboards and reports based on this data.

Read More

Azure Web Sites Shared has a New Quota Limit

Azure Web Sites Shared appear to have implemented a new usage quota based on your CPU usage over a 12 hour period.  This is new – previously there was a maximum CPU usage per hour but not one over a 12 hour period.

Once it has been exceeded, your web site becomes unavailable.  It also doesn’t seem to scale based on the number of instances you’re using (unlike memory which does) so this means that scaling out instances doesn’t help.  This WordPress blog running with 15K visits per month has exceeded this quota a couple times this month.

I’ve upgraded this blog to Basic Small which is about $60 / month instead of $10 / month per instance.

Read More

Power BI REST API is now v1.0

The Power BI Preview service introduced a new REST API for pushing data into Power BI.  I previously blogged about the preview REST APIs and these seem to be similar with a few minor updates.  It has now been published as version 1.0 to coincide with the Power BI general availability release.

The value of the Power BI REST API is that you can push your own custom datasets, tables and rows into Power BI from any source using either JavaScript for client side scenarios or C# for server side scenarios.  You can authenticate via web or as a console application.


As with the preview version, version 1.0 seems to be missing a few basic API methods such as you cannot actually delete a dataset or change a dataset and you cannot delete a table once it is created. 

Read More

New Organization Content Packs Enable Sharing in Power BI

One of the cool new features in Power BI are content packs.  Content packs have been available in preview from partners such as Microsoft, Google, SalesForce, etc.  but with the release version you can create your own content packs for your organization.

A content pack is essentially a collection of datasets, reports, and dashboards that you package together and share.  To create a content pack, click on the gear icon on the top right and select create content pack.

imageThis will take you to a screen that allows you to select who you want to share the content pack within your organization and what dashboards, reports, and datasets you want to share.


Once you have published your content pack it is now available to the rest of the organization or the specific users and groups you have selected.  You can view the existing content packs by clicking on the gear icon and selecting view content packs.

Read More

Connecting to Dynamics CRM Online with Power BI

Power BI provides the ability to  connect to a number of online services and one of these is Dynamics CRM Online.  This is how you connect to Dynamics Online once you have logged into Power BI.

Connect to the Service

At the bottom left, you’ll see a button for “Get Data”.  and clicking that reveals a number of different methods for connecting to datasets.  Dynamics CRM Online is under services.



Click on the Microsofy Dynamics CRM and then click on Connect and you’ll see a dialog box that looks like this:


The URL for your service will be your tenant URL + “/XRMServices/2011/OrganizationData.svc”.

Logging In

The next dialog box will prompt for authentication.  Assuming you have the normal authentication set up, the best select here is oAuth2 which will redirect you to Microsoft’s single sign on experience to log you into Dynamics CRM Online.

Once you have logged in successfully, Power BI will synchronize with your Dynamics CRM Online repository and provide a dataset representing your CRM data.

The Default CRM Dashboard

One of the cool things is that Power BI doesn’t just load up a raw dataset – each service has a set of default dashboards that are in context with the service.

For CRM, you get dashboards for:

  • Revenue (last 12 months)
  • Revenue (last 3 months)
  • Open Revenue (closing next 90 days)
  • New Opportunities Trend (by month, year, rating)
  • New Leads (last 90 days)
  • Win Rate (last 12 months)
  • Open Opportunities (closing next 90 days)
  • Sales Pipeline (by sales stage)
  • Leads (by lead source)
  • Lead Conversion Rate (last 90 days)
  • Average Deal Size (last 12 months)
  • Revenue (by owner)
  • Open Revenue (by owner)
  • Average Time to Close (in days)
  • Revenue Trend
  • Deal Size, Deal Age, Revenue
  • Top 10 Accounts (by revenue)
  • Win Rate, Win Count Trend
  • Activity Trend
  • Revenue by Region/Country
  • Top Open Opportunities
  • Deal Size, Deal Age Trend




Accessing it from your Phone

Microsoft has an IOS, Windows Phone and Android app for viewing your dashboards.  This works well with Dynamics CRM data for getting access to your vital data at a glance while on the road!


Manipulating your Dashboard

There are a number of easy things you can do to configure your Dynamics CRM dashboard.

  • You can drag and drop the individual dashboard widgets to change their order.
  • If you click on the widgets, they will drill down into more detailed reports.
  • Create your own new sales reports

Here is a new dashboard I created show accounts sorted by revenue.



Read More

Power BI Will Launch With Office 365 Groups, Content Packs and More!

When Power BI v2 launches on July 24, it will be delivered with some new features previously unavailable in the preview version.

Support for Office 365 Groups

Power BI will support Office 365 groups for team collaboration.  If you are already using Office 365 groups you will be able to repurpose them in Power BI and/or you can create new ones for collaborating with colleagues around your datasets, dashboards, etc.

Organizational Content Packs

Content packs are deployable and sharable collections of datasets, dashboards and connections to data sources that you can prepare and then share with the rest of the organization.  Similar to an app store model, content packs will be both available from commercial vendors as well as deployable from private organizations through their own private distribution point.

Extended Support for Excel

Power BI will bring in the features of Excel Online into its native experience and support visualization of a number of Excel features such as:

  • Pivot Tables
  • Charts
  • Slicers
  • Data in tables and worksheets
  • Power view sheets

This functionality has been available in Power View and the original Power BI for a while – now it appears in the new Power BI feature set.

Replace Excel, Power BI Desktop and CSV Files in Power BI

if you create dashboards based on excel files, CSV files or a Power BI Desktop definition you won’t have to re-create them if you need to replace the original file.

Row Level Security for On Premises SSAS Tabular Models

Power BI now respects row level security rules and permissions defined in your on premise SSAS tabular models.  Dashboards connected to your on premise SSAS tabular models will only show data that the authenticated user is allowed to access.

Read More

Office Online Integration with Yammer Coming Soon

Microsoft has announced that document previewing and editing in Yammer will be provided through Office Online.

First, the document preview and edit experience for files uploaded into Yammer are now powered by Office Online. When you click the Edit File link in Yammer, a new browser tab opens up with a full-screen view of the document. Make changes and comments directly in the document using Office Online. When you’re done, simply close the tab. Your changes are automatically saved in Yammer, where you have the ability to review the version history of each file and mark documents “Official.” In the near future, you’ll also be able to co-author documents in real-time and translate documents in Yammer into 30+ languages using the Bing Translator.

Preview files in Yammer using Office Online.

Document collaboration in Yammer just got better with Office Online 3

Read More

Visual Studio 2015 and .NET 4.6 Just Released

Visual Studio 2015 and .NET 4.6 have just gone general availability and are now available for download.

Key feature improvements include:

  • Unified debugging and profiling
  • Proactive refactoring and code optimization tools
  • New XAML editor
  • Support for the latest language frameworks including updated versions of C#, VB, C++, TypeScript, F#, ASP.NET, etc.
  • Improved JSON editor
  • Cross-platform mobile development for both HTML and native clients

Read More