Young, female and paving the way for technology in South Australia

We recently joined forces with St. Peter’s Girls’ Collegiate School, facilitating an 8-week data and analytics project for Year 11 students.  This exercise provided the girls with real-world skills development but most importantly, opened their eyes to what a career in IT can look like, dispelling some of the misconceptions in the process.

View the article here: St Peters Girls DA Project

Exposé team members working with the St Peter’s Girls were Andrew Exley, Etienne Oosthuysen, Kelly Drewett, Trevene Leonard

Common Data Service (CDS) – A Common Data Model. Accelerate your ability to provide insights into your business data

If you’ve been following Microsoft’s recent press releases, chances are you’ll have been exposed to the term “Common Data Service” (CDS). Read on as we shed light on the exact nature of CDS and what it can mean to your business.

Back in November 2016, Microsoft released their Common Data Service to general availability. In a nutshell, CDS is Microsoft’s attempt at providing a solution to counter the time and effort customers are spending to bring together disparate apps, services and solutions. At its most basic level, it provides a way to connect disparate systems around a focal point of your data. The intention is that Microsoft will provide the “heavy lifting” required to ensure the data flows back and forth as required.

To achieve this, Microsoft has defined a set of core business entities and then built them into what is known as the Common Data Model (CDM). For example, they have exposed entities for managing data around Accounts, Employees, Products, Opportunities and Sales Orders (for a full list see: Where there isn’t an existing entity to suit a business requirement, Microsoft has made the CDM extensible, which allows you to add to your organisation’s instance of the CDM to meet your needs. As your organisation adapts and changes your CDS instance, Microsoft will then monitor this and look for common patterns amongst the business community that it will use to modify and extend the standard CDS.

Microsoft is committed to making their applications CDS aware and is working with their partners to get third party applications to interact effectively with the CDS.

When establishing CDS integration from an organisational use perspective, it should ideally be a simple configuration of a connector from a source application to the CDS, aligning its data entities with the reciprocal entities within the CDS.  This will ensure that as products are changed to meet business needs over time, the impact should be almost negligible to other systems. This negates the need for an organisation to spend an excessive amount of time ensuring the correct architecting of a solution in bringing together disparate apps and siloed information. This can now be handled through the CDS.

Since its release in 2016, CDS has evolved with Microsoft recently announcing the release of two new services; Common Data Service for Apps (CDS for Apps) and Common Data Service for Analytics (CDS for Analytics).

CDS for Apps was released in January 2018 with CDS for Analytics expected for release in second quarter 2018. As a snapshot of how the various “pieces” fit together, Figure 1 provides a logical view of how the services will interact.

Figure 1 – Common Data Service Logical Model

Common Data Service for Apps

CDS for Apps was initially designed for businesses to engage with their data on a low-code/no-code basis through Microsoft’s PowerApps product. This allows a business to rapidly develop scalable, secure and feature-rich applications.

For organisations needing further enhancement, Microsoft offers developer extensions to engage with CDS for Apps.

Common Data Service for Analytics

CDS for Analytics was designed to function with Power BI as the visual reporting visual product. Similarly to the way CDS for Apps is extensible by developers, CDS for Analytics will also provide extensibility options.

Figure 2 below provides the current logic model for how CDS for Analytics will integrate.

Figure 2 – CDS for Analytics Logic Model

Business Benefits

Implementing the CDS for Apps and CDS for Analytics will enable you to be able to easily capture data and then accelerate your ability to provide insights into your business data.

To assist in this acceleration, Microsoft and expose data, as their partners, will be building industry specific apps that immediately surface deep insights to an organisation’s data. An initial example is currently being developed by Microsoft; Power BI for Sales Insights will address the maximisation of sales productivity by providing insights into which opportunities are at risk and where salespeople could be spending their time more efficiently.

The ease of development and portability of solutions aren’t possible, however, without having a standardised data model. By leveraging Microsoft’s new common data services and with the suite of Microsoft’s platform of products being CDS aware, utilisation of tools such as Azure Machine Learning and Azure Databricks for deeper analysis of your organisation’s data becomes transformational.

If you’d like to understand more about how to take advantage of the Common Data Service or for further discussion around how it can assist your business, please get in touch.

A Power BI Cheat Sheet – demystifying its concepts, variants and licencing

Power BI has truly evolved over the past few years.  From an add-on in Excel to a true organisation wide BI platform, capable of scaling to meet the demands of large organisations; both in terms of data volumes and the number of users. Power BI now has multiple flavors and a much more complicated licencing model. So, in this article, we demystify this complexity by describing each flavor of Power BI and their associated pricing. We summaries it all at the end with some scenarios and in a single cheat sheet for you to use.

Desktop, Cloud, On-premise, Pro, Premium, Embedded – what does all of this mean?

I thought it best to separate the “why” (i.e. why do you use Power BI – Development or Consumption), the “what” (i.e. what can you do given your licence variant), and the “how much” (i.e. how much is it going to cost you) as combining these concepts often leads to confusion as there isn’t necessarily an easy map of why what and how much.

Let’s first look at the “why”

“Why” deals with the workload performed with Power BI based on its deployment – I.e. why do you use Power BI? Is it for Development or for Consumption. This is very much related to the deployment platform (i.e. Desktop, Cloud, On-Premise or Embedded).

The term “consumption” for the purpose of this article could range from a narrow meaning (I.e. the consumption of Power BI content only) to a broad meaning (i.e. consumption of-, collaboration over-, and management of Power BI content – I refer to this as “self-serve creators”).

Why – workload/ deployment matrix

Now let’s overlay the “why” with “what”

In the table above, I not only dealt with the “why”, but I also introduced the variants of Power BI; namely Desktop, Free, Pro, On-Premise and Embedded. Variants are related to the licence under which the user operates and it determines what a user can do.

Confused? Stay with me…all will become clearer.

What – deployment/ licence variant matrix

Lastly let’s look at the “how much”

The Power BI journey (mostly) starts with development in Desktop, then proceeds to a deployed environment where it is consumed (with or without self-serve). Let’s close the loop on understanding the flavours of Power BI by looking at what this means from a licencing cost perspective.

Disclaimer: The pricing supplied in the following table is based on US-, Australian-, New Zealand- and Hong Kong Dollars. These $ values are by no means quotes but merely taken from the various calculators and pricing references supplied by Microsoft as at the date of first publication of this article.

How much – licence variant/ cost matrix

**Other ways to embed Power BI content are via Rest API’s (authenticated), SharePoint online (via Pro licencing) and Publish to Web (unauthenticated), but that is a level of detail for another day. For the purpose of this article, we focus on Power BI Embedded as the only embedded option.

Pro is pervasive

Even if you deploy to the Cloud and intend to make content available to pure consumers of the content only (non-self-serve users), whether it be in or as embedded visuals, you will still need at least one Pro licence to manage your content. The more visual content creators (self-server creators) you have, the more Pro licences you will need. But, it is worth considering the mix between Pro and Premium licences, as both Pro and Premium users can consume shared content, but only Pro users can create shared content (via self-service), so the mix must be determined by a cost vs capacity ratio (as discussed below).

A little bit more about Premium

Premium allows users to consume shared content only. It does not allow for any self-service capabilities. Premium licences are not per user, but instead, based according to planned capacity, so you pay for a dedicated node to serve your users. Consider Premium licencing for organisations with large numbers of consumers (non-self-serve) that also require the dedicated computer to handle capacity. The organisation would still require one or more Pro licences for content management and any self-serve workload.

Premium licencing is scaled as Premium 1, 2 or 3 dependant on the number of users and required capacity. You can scale up your capacity by adding more nodes as P1, P2 or P3, or scale up from P1 to P2, and from P2 to P3.

Premium capacity levels

The mix between Pro and Premium

Given that Pro users can do more than Premium users, and given that you will need to buy one or more Pro licences anyway, why would you not only use Pro rather than Premium? There are two reasons:

  • There is a tipping point where Pro becomes more expensive compared to Premium, and
  • With Pro licences you use a shared pool of Azure resources, so is not as performant as Premium which uses dedicated resources, so there is a second tipping point where your capacity requirements won’t be sufficiently served by Pro.

The diagram below shows the user and capacity tipping points (discussed further in scenario 1 below):

Capacity planning Premium 1 vs Pro: Users/ Cost/ Capacity

Put this all together

Right, you now understand the “why”, “what” and “how much” – let’s put it all together through examples (I will use Australian $ only for illustrative purposes). Please note that there are various ways to achieve the scenarios below and this is not a comprehensive discussion of all the options.

Scenario 1

A large organisation has 10 Power BI Developers; their Power BI rollout planning suggest that they will grow to 50 self-service creators and 1450 additional high activity consumers in 12 months. And that they will grow to 125 self-serve creators and 5000 high activity consumers in 48 months:

Initially, they will require

10 x Power BI Desktop licences = $0 x 10 = $0

500 x Power BI Pro licences to cover both self-serve users and consumers = $12.70 x 500 = $6,350

Total – A$6,350.00pm

Once they exceed 500 they can revert to

50 x Power BI Pro licences to cover self-serve users = $12.70 x 50 = $635

1 x P1 node to cover the next tranche of high activity consumers = $6,350

Total – A$6,985.00pm


Add Power BI Pro licences as required up to their planned 125 = $12.70 x 125 = $1,588

Add 1 additional P1 node at 1,450 users, and again at 2,900 users, and again at 4,250 users = $25,400 for 4 x P1 nodes

Total after 4 years at 5000 high activity consumers and 125 self-serve creators – A$26,988.00pm

Scenario 2

A small organisation with 1 Power BI developer, 5 additional self-service creators and 10 additional consumers of visual content, with no custom applications/ websites.

1 x Free version of Power BI Desktop: 1 x $0

15 x Pro licences as both visual creators and mere consumers will take part in shared content: 15 x $12.70

Total – A$190.50pm

Scenario 3

A small ISV organisation with 3 Power BI developers want to embed Power BI content in an application that they sell. The application must be up 24 x 7 and do not require a very high volume of concurrent users, but licencing cannot be on a per-user basis.

3 x Free version of Power BI Desktop: 3 x $0

1 x Pro licences acting as the mater of the Shared content: 1 x $12.70

A1 Node pricing: 1 x $937

Total – A$950.00pm

Scenario 4

A medium sized organisation with 5 Power BI developers want to embed Power BI content in an internal portal such as SharePoint which is used by potentially 250 users. They also have 10 self-service creators and 25 consumers of Power BI content through the Power BI portal.

5 x Free version of Power BI Desktop: 3 x $0

26 x Pro licences acting as 1 mater of the Shared content and 25 consumers: 26 x $330.20

A1 Node pricing: 1 x $937

Total – A$1,267.20pm

Power BI – licence variant, workload, deployment & cost cheat sheet

Any process is shown in Australian $

Disclaimer: The pricing supplied in the following table are by no means quotes, but merely taken from the various calculators and pricing references supplied by Microsoft as at the date of first publication of this article.

Licence variant, workload, deployment & cost cheat sheet

Networks Asset Data Mart – our Energy Infrastructure Provider case study

networks asset

Exposé designed and developed a solution that saw an increasingly temperamental Networks Asset Analytical solution move to the Exposé developed Enterprise Analytics Platform.

The solution now:

• Allows staff to focus on business-critical tasks by utilising the data created by the system.
• Reduces support costs due to the improved system stability.
• Utilises the IT resources for other projects that improve business productivity.

exposé case study – Energy Infrastructure Provider – Networks Asset Data Mart

See another case study here

ETL as a Service – Lifting & Shifting your existing SSIS ETL to Azure

Why a trend towards PaaS over IaaS ETL and how this relates to SSIS

Businesses are moving more towards environments where infrastructure is invisible to the business; where Platforms as a Service (PaaS) are developed, deployed and managed in the cloud to reduce operational infrastructure overhead costs as well as increase the scalability and availability of services.

But why is PaaS more attractive compared to on-premise or Infrastructure as a Service (IaaS) solutions?

  • Savings – PaaS services often use a Pay as You Go (PAYG) billing model, meaning you only pay for what you use and when you use it (for example, there are no costs due to idle server times).
  • Scalability – PaaS provides huge scalability and availability on demand.
  • Less overhead – PaaS requires no patching or maintenance of application infrastructure – it’s all taken care of. Therefore, there is less staff overhead from a maintenance perspective which allows staff to take part in activities that deliver a definite and measurable business benefit. This may include new solution developments, improving existing solutions, or data analytics.

3 images

This did not apply to SSIS

Microsoft’s incumbent data integration technology that handles the extract-, transform and load (ETL) of large volumes of data, SQL Server Integration Services (SSIS), has played a key role in many organisations’ data platforms, but unfortunately has not been available as a Service (PaaS).

So for businesses with existing investments in SSIS, wishing to move their data analytics solutions into the cloud and leverage the advantages of PaaS over IaaS, would not have been able to do so with their existing SSIS workloads. They would have had to embark on a full ETL redevelopment using an alternative data integration technology such as Azure Data Factory, or they would have had to use Azure VMs running SSIS to host their SSIS packages.

The good news

The good news is that Microsoft (MS) recently announced Azure-SSIS Integration Runtime (SSIS-IR) public preview, as well as the public announcement Azure SQL Database Managed Instance (SQLDBMI). Together these two new PaaS Services allow businesses to leverage the advantages of PaaS for their ETL workloads, without a major redevelopment.

In this article

  • We discuss what this means for businesses.
  • We discuss some capabilities still missing in public preview but in the pipeline of work for General Availability of the product.
  • We discuss how the recent announcement of SQLDBMI goes hand in hand with shifting your SSIS solutions into PaaS.
  • We touch on some of the other benefits of SQLDBMI.
  • We walk the reader through creating an SSIS IR instance as well as deploying, running and monitoring solution execution.

What are the business benefits

Savings, Scalability and Less overhead

SSIS workloads can now leverage the attractive cost advantages through a PaaS PAYG model – this includes instantaneous scale up and down to meet varying workloads, paying only for what you use, and no hidden costs through servers being idle.

By moving your SSIS workloads to PaaS, you minimise overhead costs, time and skillsets required to manage its supporting infrastructure. Infrastructure often involves costly hardware- (including depreciation, physical space and upgrades), plus backup-, network-, redundancy- and failover costs. Most of these costs simply do not apply to PaaS solutions.

Your staff can also focus on where there is a maximum business benefit – i.e. the solutions themselves rather than infrastructure or the setup, configuration, maintenance and licensing.

In addition, use what you previously developed

SSIS-IR allows businesses to easily lift and shift existing ETL workloads into the cloud so there is no need for redevelopment in ADF.  Developers familiar with the extensive functionality and components available in SSIS can easily continue to develop using proven technology such as Visual Studio and then deploy to their SSIS-IR instance in the same fashion they would for on-premise solutions.

What does this mean in $ terms


A cost analysis between the 3 options (running SSIS in an on-premise server environment, running it on a cloud-based VM or as a PaaS service) based on ETL processes that is required to run twice a day and takes around 1 hour per run (~60 hours a month) shows a clear cost advantage when using PaaS.

Please note that the table below is NOT an official quote and any cost estimates must be obtained from your licenced software provider. Costs taken from the following sources:



This option also leaves the business with the flexibility to easily scale up or down they compute power as their needs change. Doubling the computing power would take the annual cost from $550 to $870.


Regarding FTE overhead and maintenance – even if a conservative 30% of DBA time is saved by adopting PaaS, this will have a major cost saving and productivity increase to the Business.

What is currently missing? And what is coming?

SSIS-IR is still in public preview.  Therefore, there are some features not yet available as at the time of authoring this article – January 2018. These features are all expected to be available upon General Availability of the product:

  • 3rd party assemblies and API’s are not supported in SSIS-IR in the preview, only the stock SSIS components are supported.
  • Only East US, East US2, West Europe & North Europe host SSIS-IR in the preview.
  • There is no portal GUI available for provisioning your SSIS-IR instance; PowerShell scripts must be used to provision, stop/start and scale up/down.
  • You cannot schedule the execution of the SSIS packages from within SSIS-IR in preview and packages must be run
    • This is where another upcoming release by Microsoft, Azure SQL Database Managed Instance (SQLDBMI) comes to play.


The PaaS database service, Azure SQL Database Managed Instance (SQLDBMI) was announced at the recent MS Ignite conference and is set to add some key features into a more powerful version of Azure SQL Database.  This will be similar to what you would normally expect to see in the on-premise version of SQL Server and makes a very compelling case for migrating existing databases to the cloud. One of the key features in SQLDBMI is the SQL Agent, this will enable the scheduling, automation and execution of SSIS-IR packages.


Additional to SQL Agent in SQLDBMI there are many other great features which are on their way.  The public preview of SQLDBMI is set to be available around Q1 2018. So, stay tuned for the following:

  • DB Mail
    • An enterprise solution for sending email messages through the SQL Server database engine
  • R / Machine Learning Services
    • Develop and deploy data science solutions that uncover new insights as an in-database service
  • Service Broker
    • Native support for messaging and queuing applications.
  • CDC
    • Change Data Capture records data manipulation operations that are applied to SQL server tables in an easily consumed relational format
  • Replication
    • Enables the copying and distributing of data and database objects from one database to another and the synchronising between them to maintain consistency.
  • Resource Governor
    • Used to manage the SQL Server workload and system resource consumption by specifying limits that incoming application requests can use


Together SSIS-IR and SQLDBMI will make it much easier for businesses to lift and shift existing Business Intelligence architectures (notably data storage in a fully functional Database as a Service environment, and batch based Extract, Transform and Load solutions) straight into the cloud without the need for additional costs of redevelopment. The benefits of reduced operational overhead and running costs, and increased scalability and availability are hard to overlook when comparing on-premise or IaaS based solutions with PaaS based architectures.

For those interested in more of the technical aspects of SSIS-IR, the remainder of this article will walk through a set-up of SSIS-IR. Knowledge of SQL Databases and SQL Server Integration Services are required for the remainder of the article.

Walkthrough of setting up Azure-SSIS Integration Runtime

An existing Azure SQL Server is required – this is where SSISDB will reside.  We preloaded the server with an Azure SQLDB – Adventure works (not required) which were used for some SSIS tests.

SQLDB Provisioning Screen
Resource group with Azure SQL Server and SQLDB

Missing functionality in the preview – Next, because there is no GUI in private or public preview, SSIS instance must be provisioned using PowerShell scripts.  The parameters, variables and example scripts can be found here:

After provisioning, the following objects should appear in your resource group.

SQLDB Provisioning Screen 2
Resource group after provisioning showing ADFv2 and SSISDB

After connecting to your Azure SQL Server through SSMS, you can see that provisioning has created a SSISDB database. To see the Integration Services Catalog, you must connect to your Azure SQL Server and SSISDB database directly through the advanced options.

Integration Services Catalog
Integration Services Catalog menu

You can now go ahead and create folders and deploy packages to the Azure-SSIS Integration Runtime environment just as you would with a regular SSIS server.  We created a few simple test cases in visual studio and deployed the project to the newly created Azure-SSIS.

SSIS Packages
SSIS Project packages

These packages can be configured in the same way as standard integration services, including parameters, environment variables and connection managers.

SSIS Package Configuration
SSIS Package configuration

Running these packages and checking the integration services catalogues reports allow for all the logging associated with standard SSIS.

SSIS Package Execution
SSIS Package execution

SSIS Package Run Log
SSIS Package run log