Administrators Data Analytics Data Science Exchange Online Microsoft Exchange Migrations Office 365 Power BI PowerShell

Power BI for Exchange Migrations

Are you planning or in the beginning stages of an Exchange migration?

Exchange to Exchange Online migrations are very complex. There are many factors that need to be considered, users, technical details, VIPs and project champions., etc..

My goal is to help in some areas of migrations that are constant across migrations to Exchange Online using Power BI.

How can you utilize Power BI with Exchange Migrations? First we need to start with Power BI.

Power BI is not just an application but a cloud analytics service. It allows users to visualize and analyze data quickly. It has a easy and user friendly interface that lowers the bar for entry for new users. Where things get a bit complex is when trying to sort out licensing. I have a blog post that explains at length licensing and the different versions of Power BI.

Power BI Key Features

The ability to import data from a plethora of sources, especially other non-Microsoft services, including but not limited to the following.

    • Azure Data Lake
    • Azure Cosmos
    • Azure Blob
    • Salesforce
    • Facebook
    • Dynamics
    • QuickBooks Online
    • Zendesk
    • Google Analytics
    • Excel
    • Access Databases
    • SQL/MySQL
    • PostgreSQL
    • GitHub
    • JSON
    • Folders
    • SharePoint
    • XML
    • Teradata
    • Oracle
    • IBM DB2 Database
    • IBM Informix (Beta)
    • IBM Netezza
    • Amazon Redshift
    • Google Big Query
    • OData Feed
    • R Script






Now to the fun stuff!

The list below contains some of the primary areas that are difficult to extrapolate but are crucial to migrations. Gaining visibility adds a level of clarity certain to ensure your migration runs smoothly. The following list is are the areas that I will be focusing on.

    • Mailbox Delegations
    • Mailbox Sizes
    • Mailbox Item Counts
    • Migration Waves

We can create basic reports that can accomplish the following.

First, we will need to export data.

Export all of the mailbox permissions with the following Exchange PowerShell code.

Get-Mailbox -ResultSize unlimited | Get-MailboxPermission | Select Identity,User,@{Name='Access Rights';Expression={[string]::join(', ', $_.AccessRights)}} | Export-Csv MailboxPermissions.csv –NoTypeInformation

Next, we export all of the mailbox sizes.

Get-Mailbox -ResultSize unlimited | Get-MailboxStatistics | Select-object DisplayName, ItemCount, @{expression={$_.TotalItemSize.Value.ToMB()};Label="SizeInMB"} | export-csv MailboxSizes.csv -Notypeinformation

Now, that we have the data we need to import it in to Power BI Desktop. There is a lot of data wrangling required. The data wrangling will vary depending on naming schemes, etc., but this Power BI template can serve as a guide. Simply download the file and change the source location of the data to where you stored the files that were created.

Prior to creating reports, you will need to download the visualizations from the Microsoft Marketplace.  I used the Bubble Chart by Akvelon and the Network Navigator Chart which is from Microsoft.

The video below will show the basic reports that you can create.

This methodology can be applied to shared mailboxes, which is what let me to developing these reports. I have an old post here. Hopefully the creativity juices are flowing and you will apply Power BI visualizations to many other areas.

Administrators Data Analytics Data Science Power BI

Where to start with Power BI and Licensing?

It is very easy to become overwhelmed with Power BI as a beginner. There are so many Power BI designations going around. There are seven Power BI names sailing the internet. While Microsoft states that there are only two licensing models for Power BI, Pro and Premium. The nuances of it all is were some potential adopters get lost. I hear it all the time. Which Power BI do I need? Do I need a server in Azure for this? Is it a service in Azure or in Office 365?

It can be very confusing as an example here is a list of names associated with Power BI.

    • Power BI Free
    • Power BI Pro
    • Power BI Desktop
    • Power BI Desktop “optimized” for Report Server
    • Power BI on Premises Data Gateway
    • Power BI on Premises Personal Data Gateway
    • Power BI Premium
      • Shared Capacity
      • Premium Capacity
      • Power BI Report Server

To provide some insight as to why are there so many names floating the web and help guide the decision process. We need to understand some history.

Microsoft has adopted an “evergreen” strategy. This allows them to introduce new features remove old ones, change direction on how they monetize, etc. It’s brilliant, no longer are do they need to slowly become stale before adding new features to either stay ahead or play catchup with the competition. Microsoft now leverages the “User Voice” to listen to its customers, make the necessary changes, add popular requested features, alter existing features, etc. While we have grown custom to the “evergreen” strategy over the last couple of years with the explosion of Office 365, Azure Infrastructure as a Service (IaaS), Azure Software as a Services (SaaS) and Microsoft Cognitive Services to name a few. One caveat inherent of this strategy for us as users, administrators, engineers, is the constant changes to our products. It is very difficult to stay current in this environment but once you adjust to the rapid pace of things its manageable.

A prime example of rapid changes is made very evident in the Power BI Gateway. Once upon a time it was one product, now there are two variations. The Power BI Team added a plethora of data source options and features that elevated the product. If a user did not need these features and simply wanted to use it as initially intended. Why force them to adopt the new requirements? Naturally the on premises data gateway forked. Now we have Power BI on Premises Personal Gateway for single users and the “normal” on premises data gateway that can connect to Microsoft Flow, Power Apps and Logic Apps and be shared with many users.

June of 2017, Power BI Premium was released as part of the General Availability (GA). This was a great attempt in removing the license limitations in the previous model thus removing a barrier of adoption. Until that point, both content generators and report consumers needed to have Power BI Pro licenses. This was making adoption for some organizations very costly. With the new Power BI Premium model, changes were introduced to accelerate adoption. Users who were report consumers and not content creators could consume these reports without the need for an individual license. The Power BI Premium model provides shared capacity and dedicated capacity. Power BI Premium also introduced the Power BI Report Server which allowed an on premises deployment of the service, also lowering the cost to consume reports. In general, I like to view Power BI Pro licenses for content creators and for smaller companies. Power BI Premium for enterprise companies or companies that will share reports with many consumers but have fewer Pro users. Microsoft provides a nifty calculator to help in the Premium model decision making.

Changes to Power BI happened very quickly and while I admit it is a challenge to keep current. I feel it is our responsibility to help guide beginners and potential Power BI users in to the Microsoft Data Science ecosystem. Power BI when used properly becomes a gateway in to the realm of possibilities that reside in the data that already exist for many customers. It provides the ability to tell a story visually that would otherwise have been lost in an email, spread sheet or simply not understood. If you combine Power BI with Microsoft Cognitive services and Azure Machine learning the possibilities are infinite.

Which Power BI is right for you?

    • Power BI Free – Exploration phase or for report consumers.
    • Power BI Pro – Need to share what I have learned with my immediate circle or small company.
    • Power BI Desktop – Everyone should use this tool. **There are two versions one of which is optimized for the on premises report server.
    • Power BI Data Gateway – Perhaps as proof of concept or personal use.
    • Power BI Premium Shared Capacity – Small to midsize companies because reports refresh no more than 8 times a day.
    • Power BI Premium/Premium Capacity – Large enterprises were reports need to be refreshed as close to live as possible.
    • Power BI Report Server (On Premises) – Need to keep everything on premises or already own the necessary hardware. Facilitate a least expensive Premium deployment.

Are you ready to learn some Power BI? Below are some useful links.