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

As Cloud Architectures Grow Up, Migration and Portability Will Be Key Issues

We have been working on a major upgrade project for an application that was written more than ten years ago.  Written in the era of the “latest” technologies from Microsoft such as InfoPath 2003, JScript, SharePoint 2003, ASP.NET, Windows Workflow Foundation, etc. we have been working to upgrade this entire application because all of these components are now end of life from a support perspective.

Image result for azure application architecture

We are now in the 2003 era for cloud – Microsoft, Amazon, Google, etc. are introducing new services and technologies at a blistering pace with the promise of making line of business applications easier to develop, manage and maintain.  As an industry, we have been sold on the same messages for years:

  • Why build something custom when you can use “out of the box” and configure?
  • Why have developers when you can have power-users who can self-service their own maintenance?
  • Why design your own architectures for scalability, security, etc. when you can rent our already industrial strength solution?
  • Why own your own code, platforms, etc. when we’ll allow you to rent ours at a fractional cost?
  • Why have a simple, monolithic application when you can try dozens of new services optimized to be best of breed?

As we fast forward five to ten years from now, there is a lesson from our experiences maintaining, upgrading and re-platforming applications from years ago. 

Migration matters.  Upgradeability matters.  Vendor lock-in matters. 

When upgrading this particular application, the custom code we developed upgraded without any problem.  Take the 10 year old code, put it into Visual Studio 2015 and build it and it builds.  Hosting the code in a basic web container like IIS also works without a problem.  However, when we start upgrading higher level platform components like InfoPath, SharePoint or Windows Workflow Foundation, Microsoft has “evolved” (e.g. broken backward compatibility) their platforms over the past 10 years and the “configuration” based application components break.  We’re now in the process of fixing these components and re-writing them in some cases to adopt the latest Microsoft standards.

Microsoft is no better or worse than any other vendor – we see similar lessons with Oracle, SAP, IBM, etc.  We will see the same issue with cloud vendors like Amazon, Google,  It is in the vendor’s interest to ensure that your application is tied to their architecture components.

Migrating to the cloud is easy.  Upgrading to a new cloud, migrating off the cloud or picking a new cloud vendor could be very hard indeed.

Lets imagine our organization has a line of business application that processes orders coming from the web site to our CRM system and then into our financial system.  We also want analytics so we also invest in some big data services and business intelligence tools.  In the Microsoft world, we might use ASP.NET, Azure Search, Flow, Power Apps, Dynamics CRM, HD Insight, Power BI, etc.  In the Amazon world, we might use Amazon Web Services, RedShift, Hadoop, etc. and maybe we use SalesForce as our CRM system and DOMO as our business intelligence solution.

Regardless of the platforms you pick, each introduction of a new component creates a dependency on the application that increases your migration risk, the complexity to make changes and locks you into the vendor providing you the service.  As the cloud architects advocate for micro-services architectures that split your applications into potentially dozens of services the supply chain of code for your application will become increasing complex.  While there are benefits to such architectures, tracking the dependencies and ensuring all these services work together now and into the future will become a significant challenge in the cloud era.

Avoiding future nightmares by learning from the past.

In reviewing upgrades to legacy applications, we can learn lessons that apply equally well to cloud.  Our team does many of these projects where we review, analyze and re-platform applications that were built decades ago.   Here are is what we have found:

  • Vendor lock-in is a huge challenge and creates a massive migration risk.  If you decide you don’t want to do business with Microsoft anymore because ABC is a cheaper alternative, do you have a plan to move all your data, your customizations, all the configurations, etc. to a new platform?  Microsoft, SalesForce, Amazon, etc. are all betting that in five years from now it will be more difficult to migrate than to pay the increased fees that they will inevitably charge.
  • Pure code solutions tend to upgrade easier than “configuration” based solutions.  My C++, Java or C# application can be ten years old but I can still compile it, run it and in most cases upgrade it to the latest frameworks.  These programming languages are super mature and backward compatibility is very high.  Be careful with high level “power-user” solutions that promise an easier but technically less robust solution.
  • Transparency and portability of code and data is an important principle.  For example, if I adopt Azure SQL as my database, I can still access my data reasonably easily.  I can also export it, move it around, take it back on premise, and see it as a set of well understood tables.  Can I say the same thing if my data is in SalesForce?   
  • As the number of platform components increase, the challenges with dependency management increase as well.  While your architecture could leverage dozens of cloud services to optimize the operation and hosting costs of your application, what is the cost of the increased complexity when you try to upgrade?
  • Understanding the maturity and commitment to maintain backward compatibility and support your customization model is a key requirement.  With the cloud accelerating the product development life cycle dramatically from 3 years to every 15-30 days, cloud vendors are going to threaten your architecture every 15- 30 days if they don’t maintain backward compatibility or provide an upgrade path.  What happens in 3 years if Microsoft decides that they don’t want to support Flow or Power Apps anymore? 
  • While there are some performances and scalability advantages to introducing micro-services, leveraging distributed architectures, harnessing specialized cloud services, etc. their introduction also increases complexity and dependencies.  Most organizations are not Facebook and could build a reasonably focused and robust application using a simple web server and a database only.  While this may sound old-fashioned and harder to maintain than a more sophisticated architecture and operational model, it means upgrading and maintaining the application will be easier.

Past technology revolutions show the way forward – those who invested in new fangled technology solutions a decade ago made some bad bets and bought into technologies that over-promised and under-delivered.  Applications that are simple, leverage non-proprietary and mature technologies and that are easy to migrate over time provide longevity.  Cloud services will follow the same path, except perhaps faster and with more complexity. 

Do you have an architecture that be easily maintained, upgraded and re-platformed five to ten years from now?  As you migrate to the cloud, is their a plan to avoid the same lock-in, migration and portability challenges we experienced on premise?

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

SharePoint Framework Developer Preview Just Released

In May 2016, Microsoft unveiled a new SharePoint Framework for creating responsive, modular presentation components as a replacement for web parts, app parts, etc.  The framework will be initially released to a new portal experience in Office 365 – I would expect it to be released for SharePoint 2016 on premise as well at some point in the future.

The SharePoint framework-an open and connected platform 3

The new framework is preview only and is available on GitHub here.

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

When Migrating InfoPath 2003 or 2007 Forms, Watch out For Legacy JScript

JScript is a JavaScript variant used both by Microsoft browsers (e.g. Internet Explorer) and active scripting engines such as the Macro development engine within Office.  It was originally developed back in 1996 as part of Internet Explorer 3.0 to compete and co-opt JavaScript which was originally part of Netscape, the dominant web browser at the time.  Microsoft also has VB Script which is the default scripting language found in Office products for developing macros.

When migrating InfoPath 2003 or 2007 forms from previous versions, the original developer could have included some JScript code in the form.  This code responds to various events such as the form loading, clicking on buttons, changes to the form, etc.  If you try to load this form in the “latest” (InfoPath stopped being latest in 2013 when Microsoft stopped development on it), you will see this message if you look at the code:


Any JScript that was embedded into the form won’t run and isn’t compatible with InfoPath 2013 – Microsoft dropped support for JScript in 2010.  You cannot even open the code to view it – the only option you have is to remove it.

If you want to keep this code, you’ll have to port it to C# by opening the form in a previous version of InfoPath (e.g. 2003 or 2007), scraping out the original JScript code and re-writing it as C# code. 

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