Infographic – The Modern Data Warehouse Framework

Check our latest infographic, The Modern Data Warehouse Framework!

As organizations are collecting and processing increasing amounts of data from a growing number data sources, data systems must evolve to keep up. In order to make the best data-driven decision possible, you need to reimagine the way you look at data warehousing.

We took a look at how to transition your data warehouse to the cloud and put together our top 8 recommendations for building a modern data warehouse on Azure.


Download the PDF here


The-Modern-Data-Warehouse-Framework Infographic - The Modern Data Warehouse Framework

Infographic – The Modern Data Warehouse Framework




Three Trends Influencing the Rise of Application Development on Microsoft Azure

In today’s always-on, data driven world, organizations will either disrupt, or be disrupted. For those that choose the former, a fast application development cycle is critical for success.

This is why companies are turning to Azure for application development. Azure can help cut costs and reduce development wait and integration time. Additionally, the flexibility of the Azure Cloud architecture enables quick access to resources and a streamlined development process that gets projects off the ground quicker.

Below are three trends that are influencing the rise of application development in Azure.

1. Managed Infrastructure Means More Developer Cycles

The rise of Infrastructure-as-a-Service (IaaS) and Platform-as-a-Service (PaaS) have been one of the dominant trends helping to facilitate  application development in the Azure Cloud.

IaaS is where Azure manages virtualization, servers, storage, networking and data centers. It leaves users with the most flexibility, coupled with the most responsibility.

PaaS takes things a step further than IaaS. In a PaaS model, Azure manages everything in the IaaS level in addition to security, databases and operating systems. While less flexible than IaaS, it provides developers with the ability to customize applications without the headache of infrastructure management. Without having to worry about infrastructure maintenance, IT teams are free to focus on innovation and development. As a general rule, we’re seeing that PaaS is the preferred model for application development

2. Faster Development with Azure Services and Open-Source Capabilities

Cloud infrastructure is enabling developers to spend more time on innovation, making their apps unique, and less time on infrastructure and plumbing.

One of the Azure services we like is Azure App Service. It’s a PaaS solution that enables developers to create applications at a significantly faster rate than would have been possible with traditional legacy solutions. Aside from the benefits of a managed PaaS model, the team at Optimus finds there are a number of advantages with Azure App Service:

  • Optimized for DevOps: Apps can be managed using Azure PowerShell, or the cross-platform command-line interface (CLI). This enables continuous integration and deployment with GitHub, Visual Studio Team Services or BitBucket.
  • Highly scalable: You can spin up or shut down VMs instantly. Apps can be hosted anywhere in the Microsoft global datacenter infrastructure. For one of our clients – they were able to expand from North America to APAC in only a few days. This instant scalability helped them grow revenues rapidly without having to worry about cost or risk.
  • Visual Studio integration: At Optimus, we’re seeing more and more developers taking advantage of Visual Studio. The ease with which it lets users create applications for any operating system, coupled with the ability to manage and deploy those applications on your preferred platform and device, significantly simplifies application development in Azure.
  • Support for multiple languages and frameworks: ASP.NET, Node.js, Java, Python and PHP are all supported by Azure App Service. This lets developers work with the tools they’re most comfortable with. It also means they don’t have to spend time rewriting legacy applications that they want to integrate into their cloud environment.

What we’ve found is that application development teams who have moved to Azure have actually decreased their development times because of Azure’s support  of third-party services. For example, Azure supports stacks such as MongoDB, Cassandra and Hadoop, alongside Microsoft MySQL. Azure App Services also offers an extensive array of templates that let devs use a wizard to install open-source software such as Drupal, Joomla and WordPress.

However, most of our clients are working in hybrid environements. This means a need to integrate with legacy, on-premises infrastructure. This is a strength of Azure and means that developers aren’t wasting time trying to get things to work together.

3. Pay for Usage, not Hardware

As most know, traditional on-premises application development involves a significant hardware capital expenditure (CapEx). In contrast, the Azure Cloud does not require any up-front investment, instead offering a pay-as-you-go model. This converts CapEx into an operational expense (OpEx). The result is twofold:

  1. Devs can spin up a VM and begin testing new applications within minutes, as opposed to the days, weeks or months it would take to acquire new hardware.
  2. Applications that previously would have been too risky to justify the purchase of expensive hardware can now be developed.

The ability to transform application development from a CapEx to an OpEx not only allows developers to work faster; it enables them to innovate in ways that were previously unfeasible.

For more information on application development in the cloud, check out our e-Book, The Do’s & Don’ts of Application Development on Azure.



Cloud Data Warehousing with Azure SQL

If you are running an on-premise data analytics stack on Microsoft’s SQL Server, but running into maintenance, cost and scaling issues, you can consider moving your data system to a cloud-based database service such as Azure SQL Database. Especially for your first data analytics stack, Azure SQL Database provides low startup costs with the ability to easily expand as business grows.

Advantages of Azure SQL Database

There are several benefits to moving on-premise SQL Server infrastructure to Azure:

  • Physical acquisition, provisioning and maintenance of SQL Server deployments are a thing of the past. Furthermore, decreasing or increasing data infrastructure is instantaneous with SQL Database elastic pools.
  • Azure assists existing database migration to Azure SQL Database with wizard-based tools.
  • All stored and transmitted data are encrypted via client-side keys.
  • Microsoft accommodates third-party and open-source technologies, such as Python, Java, node.js, PHP and Python.
  • SQL developers feel right at home using SQLCMD or SQL Server Management Studio for development.

SQL Database Limitations

Although all SQL Server components, SSIS, SSAS and SSRS are available on Azure, there are still areas where the Azure version is not completely fleshed out. For instance, only a growing subset of T-SQL features are yet available such as cursors, transactions, triggers, all data types, all operators plus logical, arithmetic and string functions.

Additionally, many T-QSL statements in SQL Database do not support every option available in SQL Server 2016, such as CREATE/ALTER for databases, logins, tables, users and views. Collation of system objects, cross-database queries with three- or four-part names, database collector, diagrams and mail, some events and certain database characteristics that were managed manually in SQL Server but are automatic in SQL Database are also missing.

For a full list of deficiencies, see Azure SQL Database Transact-SQL differences

Additional Azure Capabilities

SSRS is actually replaced with a separate service, SQL Reporting, which incurs a separate charge for reports. It is not a general reporting service since it only works with SQL databases. It does offer a nearly identical development interface to traditional SSRS.

Azure Tables is a storage service targeted at non-relational database storage, which is a type preferred for data analysis processes. It stores up to 100TB of data via an Azure Storage account and supplies data in row form. Additional advantages include less cost than straight Azure storage and easy scaling.

Built on top of Hadoop, HDInsight offers unstructured data storage plus a number of tools, such as Sqoop, Pig and Hive for query processing. Your in-house SQL Server, Excel or SQL Database are all able to connect to this service.

Data Factory is Microsoft’s SaaS analogue to SSIS. It visually coordinates other services to transform raw, unstructured data via data flow pipelines into clean, transformed data ready for analysis engines such as HDInsight or Azure Machine Learning for predictive analytics.

In lieu of SQL Reporting, you can utilize Microsoft’s SaaS Power BI for report, dashboard and visualization creation. You can use this tool in conjunction with your on-premise SQL Server installation or stored spreadsheets too.

Steps to Migrating from SQL Server to SQL Database

SQL Database is, in theory, backward-compatible all the way to SQL Server 2005. In spite of this, the first step in migration is to test and fix any compatibility issues that may exist with SQL Database V12.

There are several methods to determine compatibility including the use of SQL Server Data Tools, the SqlPackage utility, SQL Server Management Studio’s Export Data Tier wizard and the Azure SQL Migration Wizard. SSDT, SSMS and SAMW can be used to fix any migration issues with your database also.

The next step is to create an Azure SQL Database logical server and migrate your existing data to it. Although other methods exist, the use of SQL Server transaction replication is the recommended solution since it minimizes live database downtime. Other solutions are to export/import BACPAC files when connection bandwidth is low or unreliable or use the SSMS database deploy wizard for smaller databases.

Running SQL Server in the Cloud Directly

There is nothing stopping you to begin or continue your SQL Server-based data analytics development and deployment without Azure SQL Database. Amazon AWS provides any level of SQL Server instantiation online with the advantages of computational, networking and storage elasticity on a pay-as-you-go basis. With a bit more lifting, you could do the same thing on Azure or AWS by utilizing their Virtual Machine services directly for your own SQL Server deployment.


Running data analytics in the public cloud brings all the usual benefits of cloud-based operation, the most important of which are elastic storage for big data crunching systems and high availability in-house or mobile across the enterprise.

Whether or not your business should consider a cloud-based data analytics deployment depends on several factors including TCO, data volume, bandwidth requirements, security and the need to scale operations up or down quickly.

A wise approach is to work with an experienced Optimus data analytics consultant to collate all factors and develop a full data architectural solution. Our experts can guide you towards the best solution for your needs.




How to Setup iOS Push Notifications on Windows Azure

In this post I’ll give a quick overview of how to add Push Notifications to the iOS application we created in our previous blog post entitled “How to Setup Windows Azure for iOS in under 5 minutes.” It will be helpful to use that sample app as a starting point for this tutorial.

On the Azure website, they have a detailed tutorials on how to get Push Notifications working for iOS; however, if you’re pretty comfortable in the Apple Developer center you can follow the simple steps outlined below.

There are three main areas that need to be configured: the Apple Developer Center, Azure Management Portal, and your App in Xcode.

Apple Developer Center Configuration

  1. Setup Push Notifications for your iOS app: in order to enable push notifications, you will need to go to the Apple Developer center and create an App ID and Provisioning profile with Push Notifications turned on. To do so, you’ll need to create/upload a certificate – save this certificate because in the next step you upload it to Azure.
    Push+Notifications+on+iOS-300x149 How to Setup iOS Push Notifications on Windows Azure

Azure Management Portal Configuration

  1. Login and go to your app
  2. Upload your Push Notification Certificate
    Azure+Mobile+Services-300x127 How to Setup iOS Push Notifications on Windows Azure
  3. Update the Insert script for the app
    Azure+Table+Push+Insert-300x149 How to Setup iOS Push Notifications on Windows Azure

    function insert(item, user, request) {
    // Set timeout to delay the notification, to provide time for the
    // app to be closed on the device to demonstrate toast notifications
    setTimeout(function() {
    push.apns.send(item.deviceToken, {
    alert: "Toast: " + item.text,
    payload: {
    inAppMessage: "Hey, a new item arrived: '" + item.text + "'"
    }, 2500);

App Configuration

  1. Open the app that we built in the previous post.
  2. Update the Bundle ID to match the new provisioning profile you created above
  3. Open QSAppDelegate.h and add the following property:
    <a href="!/@property">@property</a>  (strong, nonatomic) NSString *deviceToken;
  4. Open QSAppDelegate.m and replace the following method
    - (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:
    (NSDictionary *)launchOptions
        // Register for remote notifications
        [[UIApplication sharedApplication] registerForRemoteNotificationTypes:
        UIRemoteNotificationTypeAlert | UIRemoteNotificationTypeBadge | UIRemoteNotificationTypeSound];
        return YES;
  5. Then add the following methods to QAAppDelegate.m
    // We are registered, so now store the device token (as a string) on the AppDelegate instance
    // taking care to remove the angle brackets first.
    - (void)application:(UIApplication *)application didRegisterForRemoteNotificationsWithDeviceToken:
    (NSData *)deviceToken {
        NSCharacterSet *angleBrackets = [NSCharacterSet characterSetWithCharactersInString:@"&lt;&gt;"];
        self.deviceToken = [[deviceToken description] stringByTrimmingCharactersInSet:angleBrackets];
    // Handle any failure to register. In this case we set the deviceToken to an empty
    // string to prevent the insert from failing.
    - (void)application:(UIApplication *)application didFailToRegisterForRemoteNotificationsWithError:
    (NSError *)error {
        NSLog(@"Failed to register for remote notifications: %@", error);
        self.deviceToken = @"";
    // Because toast alerts don't work when the app is running, the app handles them.
    // This uses the userInfo in the payload to display a UIAlertView.
    - (void)application:(UIApplication *)application didReceiveRemoteNotification:
    (NSDictionary *)userInfo {
        NSLog(@"%@", userInfo);
        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Notification" message:
        [userInfo objectForKey:@"inAppMessage"] delegate:nil cancelButtonTitle:
        @"OK" otherButtonTitles:nil, nil];
        [alert show];
  6. Open “QSTodoListViewController.m” and import the QAAppsDelegate header.
    <a href="">#import</a> "QSAppDelegate.h"
  7. Update the NSDictionary in the IBAction in “QSTodoListViewController.m” :
    // Get a reference to the AppDelegate to easily retrieve the deviceToken
    QSAppDelegate *delegate = [[UIApplication sharedApplication] delegate];
    NSDictionary *item = @{
        @"text" : itemText.text,
        @"complete" : @(NO),
        // add the device token property to our todo item payload
        @"deviceToken" : delegate.deviceToken

How to Test iOS Push Notifications in Azure

  1. Create a new To Do item and you’ll receive a push notification almost immediately.
  2. To see the toast message, create a To Do item then immediately close the app and you’ll see the toast message.

The post How to Setup iOS Push Notifications on Windows Azure appeared first on OptimusMobility.

How to Setup Windows Azure for iOS in under 5 Minutes

Below is a super quick-start guide to getting up and running on Windows Azure for iOS.

Azure is a powerful backend that can be used for powerful multi-platform mobile, web, and desktop applications. Today we’ll focus on iOS development with the below tutorial illustrating how easy it is to get a demo app up and running.

  1. Go to the Windows Azure website and setup a trial account. It’s free for 90-days as long as you don’t go over the usage limits.
  2. Once your account is created, head to the Azure Management portal and create a Mobile Service.
    Screen-Shot-2013-03-18-at-9.32.57-PM-300x147 How to Setup Windows Azure for iOS in under 5 Minutes
  3. Once the new service is running (takes a couple minutes), click into it and create a new iOS app (or connect to an existing app).
    Screen-Shot-2013-03-18-at-9.34.52-PM-300x191 How to Setup Windows Azure for iOS in under 5 Minutes
  4. Create the data table and download the starter project.
    Screen-Shot-2013-03-18-at-9.36.25-PM-300x255 How to Setup Windows Azure for iOS in under 5 Minutes
  5. Run the starter project in the simulator and it’s ready to go!
    Screen-Shot-2013-03-18-at-9.37.25-PM-300x170 How to Setup Windows Azure for iOS in under 5 Minutes
  6. Simply type in a task and press “+” to add it to the list. Pro tip: to delete a task, swipe from either left-to-right or right-to-left.
    Screen-Shot-2013-03-18-at-9.42.09-PM-300x279 How to Setup Windows Azure for iOS in under 5 Minutes
  7. Add a todo item, then loop back to the Azure portal and see your entry:
    1. Click Mobile Services
    2. Click on the database you’ve started
    3. Click on ‘Data’ to see the data you’ve entered
      Screen-Shot-2013-03-18-at-9.43.50-PM-300x118 How to Setup Windows Azure for iOS in under 5 Minutes
    4. Click on the table and you can see your entry.
      Screen-Shot-2013-03-18-at-9.44.41-PM-300x106 How to Setup Windows Azure for iOS in under 5 Minutes
    5. Bonus – Go back to the Dashboard to see usage stats.
      Screen-Shot-2013-03-18-at-9.46.05-PM-300x98 How to Setup Windows Azure for iOS in under 5 Minutes

That’s how easy is to get a simple POC up and running on Azure. Stay tuned to this blog as we’ll dive deeper into Azure and how it can be used to setup Push Notification, Authentication, and more!

The post How to Setup Windows Azure for iOS in under 5 Minutes appeared first on OptimusMobility.

Cloud Data Warehousing Advantages

cloud_storage-300x200 Cloud Data Warehousing Advantages

Data warehousing in the cloud

“Why create a data warehouse in the cloud?” I have been answering this question many a times when I go to networking events.

There are a number of cloud data warehousing advantages:

  • Cost savings
  • Total cost of ownership
  • Mergers and acquisitions
  • Standardization
  • Better understanding of competitive landscape
  • Improving product and service quality.

Many enterprises are finding that data warehouse appliances can significantly reduce the total cost of ownership. All resources, including expensive networking equipment, servers, IT personnel, etc are shared, resulting in reduced costs, especially for small to mid-sized applications and prototypes.

In the case of mergers and acquisitions, duplicate financial data warehouses need to be rationalized. An enterprise or government agency may want to rationalize various warehouses based on disparate warehouse technology platforms by moving to a standard platform. Cloud computing enables companies to shift money from capital expenses to operating expenses, enabling the customer to focus on adding value in their areas of core competence, such as business and process insight, instead of building and maintaining IT infrastructure.

In short, cloud computing allows you to focus your money and resources on innovating. “Regardless of the reason for the migration, in every case the reporting and analysis supported by the migrating data warehouse must continue to run seamlessly.” This is a common need expressed by my professional network. Cloud computing platforms such as  Microsoft Azure, provide many of the core services that, under traditional development models, would normally be built in house. These services, plus templates and other tools can significantly accelerate the development cycle.

“What should be the solution?” is the subsequent question that arises during conversation. “Use data visualization to insulate reporting users during data warehouse migrations” is my to-the-point answer. Additionally, data virtualization to the cloud provides a more agile integration approach that overcomes data complexity and disparate silos to provide business with the timely data it needs to meet today’s ever-changing business requirements. Provisioning-on-demand enables faster set-up and tear-down of resources on an as-needed basis. When a project is funded, you initiate service, then if the project is killed, you simply terminate the cloud contract.

Data Virtualization using cloud computing is the opposite of traditional data warehousing. The cloud is more vibrant and valuable in that it’s something elastic that customers can scale up or down with dynamic resource allocation ensuring efficient use of shared processing and storage resources. It wants data to be location independent, transparent and function shippable; whereas, the traditional data warehouse is a centralized, persistent data store. There will be a need for a run-time metadata in order to register and access data sources as a service. Growing data volumes are winning. Still, with cloud computing (as with web services), the service, not the database, is the primary data integration method.

The ability of the cloud to load data quickly allows the vendor to work with larger data sets during smaller time windows as dictated by the customer, as well as service more customers at any one time. While smaller companies may only refresh the data in their data mart weekly, larger customers typically refresh data daily. Moreover, the transactional database of such larger customers can only stay offline for short time periods during which data must be transferred to the data marts.

Data loaded in the data warehouse has to be organized in the best possible way to enable the optimal execution of queries. The managers of on-premise data warehouses and marts constantly look at the queries executed against their databases to determine how to best organize the data to achieve best query execution times. Some of these optimizations can be performed automatically by the database management system but most require manual intervention. Having full control of the database management system enables the SaaS BI vendor to better optimize the organization of the stored data.

Finally, the emotion of the language used to query the data mart in the cloud determines the range of data analyses that can be performed and reports that can be created by the business intelligence tools (like Microsoft SSRS). Cloud infrastructure also contributes to the speed with which queries are executed that generates quick analytics reports.

As a business intelligence vendor in Vancouver, OptimusAnalytics would be happy to share our experiences with you. To learn more about cloud based data warehouse solutions, contact me directly at

(image credit: Goma on Flickr)

Windows Azure: Move your applications to the cloud

Windows-Azure-300x190 Windows Azure: Move your applications to the cloud

Set your apps up in the cloud using Windows Azure.

Many clients have been asking us about the Windows Azure platform so we’d like to take this opportunity to give a brief overview and let you know what we’ve been working on.

The Windows Azure platform is a platform-as-a-service offering from Microsoft that competes with Rackspace, Amazon Web Services, and Salesforce. It is everything you need to move your applications to the cloud. This enables developers to focus on developing great applications, instead of worrying about infrastructure.

It supports a variety of programming languages out of the box including Visual Studio, .Net, Java, PHP, and Ruby.

As illustrated above, using the Azure platform you can deploy your application, database, services, and access control service to the cloud. Then you can control who has access remotely while deploying your application through Microsoft’s globally distributed datacenters. The Azure platform will then automatically scale infrastructure up and down depending on load and charge only what you use. There are no upfront costs and organizations even have a free trial to get started.

A few other key components worth mentioning are:

  • The Azure Platform Appliance: If you need to keep your applications and data in your own data center, you can setup a private cloud using the Azure Platform Appliance. It is essentially a suite of software and hardware to setup on your own infrastructure. It is designed to be extremely efficient while providing massive scale.
  • The Azure Market Place: This is a market place for components, applications, and even services that run on Azure.
  • The Azure Data Market: This market is used for buying and selling raw data. From real estate to demographics, these data sets are rich with information that can be utilized in your applications.

At Optimus we focus on Software Development, Software Testing, and Business Analytics. We have been working with our clients to move all three services onto cloud based infrastructure. It has proven to be reliable, scalable, and increasingly cost efficient.

To learn more about how you can move your applications to cloud, contact us.

(image courtesy of Microsoft)