Use Cases for Azure Analysis Services (Part 2)

This is part 2 of a discussion about a new service in Microsoft Azure: Azure Analysis Services. Azure AS is a Platform-as-a-Service (PaaS) offering which is in public preview mode (as of November 2016).

Part 1: Why a Semantic Layer Like Azure Analysis Services is Relevant

Part 2: Use Cases for Azure Analysis Services {you are here}

Part 3: Where Azure Analysis Services Fits Into BI & Analytics Architecture

Image Credit: https://azure.microsoft.com/en-us/blog/introducing-azure-analysis-services-preview/

Because Azure AS is a cloud service, it has several meaningful benefits we've come to associated with this type of PaaS service:

• Ability to scale up or down to increase performance when the demand is there (yet not pay for that level of performance when it's not required)
• Ability to pause to save money if no queries are issued during a particular period of time (such as overnight on Dev/Test databases)
• No need to provision hardware, handle upgrades nor patching
• Inherent redundancy of the data

Note: at this early time of the public preview, not every feature mentioned in this post is available just yet, but they are coming.

Azure SSAS May Be Beneficial For...

Upsizing Data Size from Power BI

If you import the data into a Power BI data model (vs. issuing queries via DirectQuery mode), the data imported is currently limited to 1GB of data if you want to upload the file to the Power BI Service. This 1GB is after it's been compressed into its in-memory columnar format. The columnar compression is very efficient, so it can contain quite a bit of data, but 1GB certainly doesn't represent enterprise level data models (again, I'm referring to imported data not DirectQuery scenarios). Upsizing to Analysis Services changes all that since it can handle larger data volumes imported to the in-memory data model.

Faster Data Refresh Schedules than Power BI

Currently you can set a Power BI dataset (which has been imported) to refresh up to 4 times per day. If your data latency requirements dictate fresher data than that, then Analysis Services can be scheduled more frequently. (Just like the previous item, this refers to imported data and not DirectQuery models.)

Varying Levels of Peak Workloads

Let's say during month-end close the reporting activity spikes much higher than the rest of a typical month. In this situation, it's a shame to provision hardware that is underutilized a large percentage of the rest of the month. This type of scenario makes a scalable PaaS service more attractive than dedicated hardware. Do note that currently Azure SSAS scales compute, known as the QPU or Query Processing Unit level, along with max data size (which is different than some other Azure services which decouple those two).

User Activity Occurs During Fixed Hours Only

We will be able to pause the Azure AS service in order to save charges. If you're a regional company with users who don't need to query the system from, say 10pm to 7am, you'll be able to pause the service programmatically if you choose.

Cloud or Hybrid Fits Your Strategic IT Direction

If you're purposely attempting to reduce the overhead of running a data center, then more and more service offerings like this one may be a fit. To the extent you're using multiple services such as Azure SQL Data Warehouse and/or Azure SQL Database, I'm hoping we're going to see some performance benefits (assuming you've selected Azure locations in close proximity to each other for the related services that pass data around).

You Are Short on Performance Tuning Expertise

I tend to get a little preachy that running workloads in the cloud does *not* mean your DBA can retire. However, it is certainly the case that there's less administrative oversight with cloud services. With this Azure AS PaaS service, rather than tuning your server specs, instead you would change the scale level for your instance in Azure - certainly a lot easier because there's a way fewer "knobs" to adjust. Having said that, there's still *lots* of things to pay attention to: performance of queries from the original source (if your data refresh window is small, or if you are using DirectQuery mode), and also good design patterns are always *incredibly* important in order to achieve optimal performance from an Analysis Services model.

Getting New Features Fastest

We're going to be seeing new features hit Azure Analysis Services faster than SQL Server Analysis Services. Using the AAS cloud service, versus the SSAS service which is part of the SQL Server box product, offers that if getting the latest and greatest quickly appeals to you.

Next up is Part 3: Where Azure Analysis Services Fits Into BI & Analytics Architecture

Finding More Information

Kasper de Jonge's blog: Analysis Services in Azure, When and Why (I had 95% of this post written before Kasper published his, so I decided to publish mine anyway even though they're fairly similar.)

Azure Documentation - What is Azure Analysis Services?

Feedback to the Product Team - Feedback on Azure AS

Channel 9 Videos: Azure AS Videos

You Might Also Like...

Building Blocks of Cortana Intelligence Suite in Azure

Power BI Features End-to-End

Power BI Admin Portal Settings

Today I noticed there are a LOT more settings available in the Power BI Admin Portal than the last time I checked. Here are all of the choices currently available:

Keep in mind that the above selections apply to all users across the entire tenant. At this time we can't control them by groups or anything of that nature.

In addition to the above settings for controlling user experience, the Admin Portal is also the place for viewing usage metrics which are helpful for determining who runs what how often (it's not everything we could possibly want to know, but it's a start).

The other two options, Manage users and Audit logs, redirect you over to the Office 365 Admin Center.

Getting to the Power BI Admin Portal is done from the gear menu at the top right of the Power BI Service:

If you don't see the Admin Portal or Manage Gateways menu items, that's a permissions issue. Until last month (Oct 2016), you needed to be an Office 365 co-administrator to be able to view the Power BI Admin Portal. That's pretty high level permissions, so thankfully Microsoft has released a new "Power BI Service Administrator" role for delegating the Power BI administrative portion. Info about the new role is here: https://powerbi.microsoft.com/en-us/blog/making-it-easier-to-administer-power-bi/.

You Might Also Like...

Overview of Power BI Features End-to-End

Why a Semantic Layer Like Azure Analysis Services is Relevant (Part 1)

This is part 1 of 3 about Azure Analysis Services (Azure AS) which was announced a few days ago. Azure AS is a Platform-as-a-Service (PaaS) offering which is currently in public preview mode at the time of this writing (late October 2016).

Part 1: Why a Semantic Layer Like Azure Analysis Services is Relevant {you are here}

Part 2: Use Cases for Azure Analysis Services

Part 3: Where Azure Analysis Services Fits Into BI & Analytics Architecture

Fundamentally, Analysis Services serves as a semantic layer (see below for further discussion of a semantic layer). Because the business intelligence industry now embraces an array of technology choices, sometimes it seems like a semantic layer is no longer valued like it once was. Well, my opinion is that for many businesses, a semantic layer is tremendously important to support the majority of business users who do *not* want to do their own data wrangling, data prep, and data modeling activities.

Let's first do a super quick recap what Analysis Services is.

Analysis Services Primer

Analysis Services can be categorized with competitor tools such as Business Objects Universe, Cognos Framework Manager, or AtScale.

Prior to the introduction of Azure AS, Analysis Services was available as part of the SQL Server stack. Therefore, we now have SSAS which is a component of SQL Server, and Azure AS which is now a separate cloud-based offering. The main difference is in where the model is deployed; the development experience is exactly the same. Prior to the release of Azure AS (PaaS), the only way to run Analysis Services in Azure was within a virtual machine (IaaS). If you're searching for information online, be a little careful & watch the dates-you will easily run into older articles which are talking about running SSAS in a VM rather than this PaaS service. The good news is that nearly all of the information will still be relevant though since the development experience remains in SQL Server Data Tools; the primary difference is in deployment.

There are two modes to an Analysis Services instance: Tabular and Multidimensional.

• Tabular is the newer in-memory data structure, based on DAX (Data Analysis eXpressions). Generally speaking, Tabular has an easier entry point though there is *definitely* a learning curve to DAX if you go beyond the simple stuff. 
• Multidimensional is traditional OLAP so it's been around a lot longer, has a few more advanced features, and is based on MDX (MultiDimensional eXpressions). Multidimensional models are less 'forgiving' than Tabular models with respect to certain design characteristics (such as classifying facts and dimensions).

The rule of thumb most BI folks follow these days is to look at using a Tabular model unless you find you need one of the features that Multidimensional provides than isn't in Tabular yet. For this public preview of Azure AS, Tabular is the only mode supported initially. The product team has stated that Multidimensional will be considered for a future release, based on customer demand.

Regardless if you use Tabular or Multidimensional, your data is handled one of two ways: 

• It can be imported into the Analysis Services in-memory model, which is also sometimes called cached mode. In this mode, Analysis Services holds a copy of the data and serves up the data from user queries. An in-memory model provides an excellent user experience assuming that enough memory is provided, and the design is sound. The data contained in an in-memory model needs to be refreshed on a schedule, so there's always some data latency.
• The other option is to utilize DirectQuery mode. In this mode, the queries are passed onto the underlying data source so Analysis Services really is metadata only.  To get optimal performance with DirectQuery mode, the data source needs to be tuned for performance of user queries which is sometimes a challenge. The appeal of DirectQuery mode is access to real-time data, and the omission of needing to populate another dataset in Analysis Services.

Analysis Services development is usually handled by BI/Analytics/IT professionals within SQL Server Data Tools in Visual Studio, and it is typically under source control such as TFS or GitHub. No changes to the development experience occur as part of the introduction of Azure AS. This is a good thing-but it's important to be aware that development of a semantic model does take some time. It's fast and easy to create an Azure AS instance in Azure, and it's fast and easy to deploy the model, but development of the model itself does take time and know-how. The good news is there's tons of resources available to learn development techniques, such as dimensional modeling which is optimal for reporting.

Analysis Services is considered an enterprise-level data modeling tool. Ideally it exposes a large amount of your corporate data to business users. The pricing of Azure AS reflects that it is an enterprise-level tool (you can see pricing in the Azure portal when you provision the service).

Benefits of a Semantic Layer

According to b-eye-network: "A semantic layer is a business representation of corporate data that helps end users access data using common business terms."

A semantic layer sits between the original database and a reporting tool in order to assist business users with ease of reporting. It becomes the main entry point for data access for most business users when they are creating reports, dashboards, or running ad hoc queries.

Traditionally, we've built a semantic layer on top of a traditional data warehouse. That's certainly not a requirement, though certainly still a very valid and common design pattern. In Part 2 of this post we'll talk about some variations on that theme to accommodate a modern data warehouse platform.

Though exposing reporting to users via a semantic layer is not an absolute "must" in today's world of options, it facilitates reporting for things such as:

• Data pre-integrated for users (ex: Salesforce data, invoice data, A/R data, and inventory data are all integrated for the end user to consume)
• No joins or relationships for users to worry (because they've all been handled in the data model)
• Columns have all been renamed into business user-friendly names (ex: Invoice Amount instead of INVAMT)
• Business business logic and calculations have been centralized in the data model (which reduces risk of recalculations being done incorrectly)
• Time-oriented calculations are included which are really powerful (ex: Sales Increase Year Over Year; Sales Year-To-Date; % of Change Since Prior Quarter)
• Aggregation behavior has been set so reporting tools respond correctly (ex: sales $ sum up, but something like a customer number or invoice does not try to aggregate)
• Formatting has been specified so reporting tools handle it by default (ex: sales displays with the $ sign whereas units display with commas and no pennies)
• Data security is incorporated (ex: standard row-level security, and/or exposing certain sensitive measurements to only authorized users)

In summary, a semantic layer is all about the convenience of business users so they can focus on getting the answer they need. It facilitates self-service reporting by providing a cleansed, understandable, trustworthy environment. A semantic layer can often support self-service analysis for 80% of a user base. After all, most functional users want to get the information quickly and then get on with their day. A semantic layer can also help the other 20% of users that have additional needs as well--though in all fairness, those hard core data analysts and data scientist types often prefer to go after the original source data, though there's certainly still value in them acquiring some of their data from the semantic layer as well. After all, why reinvent the wheel if the investment has been made?

So, even though we have tons of options these days for information delivery, and a single semantic layer for an entire organization isn't a silver bullet, I firmly believe that a semantic layer is certainly still a great tool in the toolbox. 

Next up is Part 2: Use Cases for Azure Analysis Services

Finding More Info

Azure Documentation - What is Azure Analysis Services?

Feedback to the Product Team - Feedback on Azure AS

Channel 9 Videos: Azure AS Videos

You Might Also Like...

Data Lake Use Cases and Planning Considerations

Building Blocks of Cortana Intelligence Suite in Azure

Why You Want to Use the Latest SQL Server Management Studio Release

We have two main built-in tools for interacting with a SQL Server database platorm: SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT). This post is about SSMS and why you may want to install the latest release...even if you're not ready to upgrade to SQL Server 2016.

SSMS is now a standalone tool which is being updated on a monthly basis for bug fixes and new features. This is big because it allows the tooling team to be speedier with releases. The latest download file is easily located on the web.

Frequently you'll see it referred to it as SSMS 2016, though technically speaking it is "version-less" now. It's sometimes referred to now as SSMS 16.x which correlates with the release numbering at this time. If you are checking your Help>About menu in SSMS to check what's installed, you'll need to translate the build number shown in Help>About to the release number which can be found in the changelog:

SSMS is now based on the Visual Studio 2015 shell. This gives it some performance improvements and removes the dependency on installing .NET Framework 3.5 as a prerequisite. **For this reason, you will want to make sure Visual Studio 2015 is installed first followed by SSMS and SSDT.**

SSMS is supported for managing SQL Server 2008 through 2016 (except for SSIS instances which sadly still require a version-specific SSMS at the time of this writing). If you manage numerous servers on different versions, this unification is fantastic. There is partial support for managing pre-2008 instances. And, of course as you'd expect, the newest SSMS release supports various new features in SQL Server 2016 such as Query Statistics, Live Query Plans, Compare Showplan, Security Policies for row-level security, and so on with all the new 2016 goodies we have. 

SSMS also supports managing Azure components such as Azure SQL Database and Azure SQL Data Warehouse, as well as hybrid cloud features such as StretchDB and Backup to URL. This additional unification is really, really handy.

SQL PowerShell updates are also shipped with SSMS. In order to take advantage of this monthly release cadence, the latest and greatest SQL PowerShell module is now called "SqlServer" whereas the the legacy module, which does *not* have the newest cmdlets released since mid-2016, is "SQLPS". More info here: https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/.

If you'd like to follow and vote for new feature requests, the is a SQL Server Community Trello board here: https://trello.com/b/M9NmFPfv/sql-server-management-studio-2016-enhancements.

Finding More Information

• Latest download: https://msdn.microsoft.com/en-us/library/mt238290.aspx (there's an option in the installer media as well)
• Changelog: https://msdn.microsoft.com/en-us/library/mt588477.aspx (categorized by each release)
• Release notes: https://msdn.microsoft.com/en-us/library/mt238486.aspx (there's a few important FYIs here)

You Might Also Like...

Why You Should Use a SSDT Database Project For Your Data Warehouse

Overview of Azure Automation