Data Analytics with Office 365

Things to come-space-scope

As the saying goes, ‘I’m not a doctor, but I play one on TV’.

Only in this case, we can replace ‘doctor’ with “data scientist” or “business intelligence analyst” and ‘TV’ with “this blog post”.

Because this time, we’ll be taking an introductory look at Power BI for Office 365, the data analytics and business intelligence component of Microsoft’s service platform.

Let’s start with a definition, borrowed from the Power BI product page:

[Power BI is] an online service where you can quickly create dashboards, share reports, and directly connect to (and incorporate) all the data that’s important to you. [It] also introduces the Power BI Desktop, a dedicated report authoring tool that enables you to transform data, create powerful reports and visualizations, and easily publish to Power BI. [This] extends to all your mobile devices, too.

Full at the Power BI page.

For the un – or recently initiated, what this means is that using Power BI, you can import data from a variety of sources and manipulate that data to gain insights which may be hidden in plain sight within a forest of information.

Which is exciting, in a data geek sort of way.

As an example of what’s possible and how you can start to use this very potent tool, we’re going to walk through the process of importing and creating a report using Power BI from my test tenant.

When you log into your Office 365 tenant at you’ll notice the Power BI app tile:


By selecting the Power BI tile, you’ll be presented with the app login page:


Of course, you’ve noticed  “Already have an account?”  which, if you have an Office 365 Power BI license assigned from your tenant you do, although it may need to be provisioned (licensing example shown below from Office 365 portal admin view).

PowerBI licenses


By scrolling down the login page, you’ll see sign-up options:

Power BI Sign-up

For the sake of our overview, let’s assume you already have a licensed account and are ready to go, by clicking “sign-in“, you’ll be taken to a logon splash page:


And then into the Power BI dashboard.


Notice the “Get Data” button in the lower left-hand portion of the screen, when you click this, you’re presented with options for acquiring data:


Data sources include:

  • Content Packs created within your organization
  • External services that have created content packs for Power BI
  • Files (either stored on your computer or within your Office 365 tenant within SharePoint Online or OneDrive for Business)
  • Databases hosting ‘live’ data found within Azure SQL database, Azure data warehouse, SQL Server Analysis Services and Spark on Azure HDInsight

Here are samples of the individual interfaces:

Content Packs:


External Services (just a sample):






For the purposes of our example, we’ll be getting sample data from a file stored within OneDrive for Business:


Browsing to my OneDrive for Business site:

Earlier, I downloaded sample files from Microsoft’s Power BI support page and uploaded it into the “PowerBI Data Files” folder you see pictured above.

Next I selected the file and chose to connect to it:


I’m presented with the Excel import page:


One thing I neglected to mention is that data imports are linked to dashboards, as shown here:


In our example, I’ve imported the “Sales and Marketing Sample” to ‘Dashboard Two’.  Now let’s get back to what happens to this imported file. As you can see, the default view isn’t very interesting:


But by clicking on the file name displayed on the dashboard, a much richer visual is revealed:


Very nice!

But just a moment, before you pop a cork on that bottle of Clique Veuve to celebrate the end of your business intelligence problems (instant insights!) I should mention that this apparently painless creation of a beautiful dashboard is possible because the sample spreadsheet has been massaged using Power View for Excel.

More typically, after your spreadsheet has been properly formatted for Power BI you’ll interact with it more like this:

importing data –


Followed by –


In this more realistic example (which hasn’t had the benefit of being prepped by Power View for Excel) you must choose the fields that will be included and be used to form the elements of your interactive report.

That’s all the time I have for today.  Next time, we’ll take a closer look at how to manipulate your data using Power BI.



OneDrive for Business: Command and Control Your Files (with SharePoint)

Dr No Control Room-smaller


In previous posts I’ve described some of the challenges you might face deploying OneDrive for Business (ODB).

This time, let’s focus on a few of the attractive features made available by the fact the application’s storage backend is SharePoint Online (which, somewhat ironically, is also the reason you might face the deployment challenges mentioned before).

In addition to the data loss prevention and auditing capacities important to systems engineers and architects (not to mention security and legal teams) the OneDrive for business web interface provides end-users with a fairly rich subset of SharePoint controls.

Synchronized files (from your desktop, if you have the Windows SharePoint sync client) are duplicated at the user’s Office 365 tenant hosted OneDrive for Business site collection which takes the form of:

Consider, for example, the default view of my test ODB site:


This is the standard interface which shows files and folders.

If however, you click the gear icon and choose “Show Ribbon



An entirely richer interface becomes available:



Notice the appearance in the upper-right-hand corner of three new tabs, Browse, Files and Library:

OneDrive new options after ribbon is turned on


These options unlock the SharePoint control and workflow elements of the ODB site collection to the user.

For example, from the Files tab context, look at what’s possible when I select a folder or file:




Now I can configure files to be part of workflows, alerts, RSS feeds (reporting on changes to a file) and other dynamic things in addition to sharing.

This only scratches the surface of what’s possible.  The key thing to keep in mind is that OneDrive for Business, because of its mating with SharePoint Online, provides you with the ability to manipulate your files in a variety of powerful ways.




Delve: Find Everything (as long as “everything” is stored within O365)


One of the interesting features of Office 365 is the sudden appearance of new capabilities.

I wrote “sudden” but of course, if you follow the Office 365 blogs and take a peek every now and again at the Office 365 roadmap, you’ll be aware of upcoming changes.

But we’re all busy and so, are often surprised when a new tile shows up in the Office 365 Portal.

Which is what happened to me when Delve made its debut.

What is Office 365 Delve?

According to Microsoft, Delve…

…helps you discover the information that’s likely to be most interesting to you right now – across Office 365. You don’t have to remember the title of a document or where it’s stored. Delve shows you documents no matter where they’re stored in OneDrive for Business or in Sites in Office 365.

Full at “What is Office Delve“.


All of which sounds great but how does it look and behave when actually used?

To test this I logged into the Office 365 portal and clicked the “Delve” tile:


When you choose Delve for the first time you’re presented with some (actually not bad) marketing material:



Delve Search message

After reviewing this information, I chose the “Go to my page” option to see what would be waiting for me there:


Very intriguing; files that I’ve uploaded to a SharePoint site hosted on my test tenant are displayed on the main page.  And I can search for other files using a wide variety of criteria (file name, words within a file, etc.)

I noticed that the types of files displayed can be opened by applications included with Office 365 – Word, Excel, PowerPoint, etc – which are hosted within one of Office 365’s storage repositories (i.e., OneDrive for Business site [], SharePoint team sites and Exchange Online email messages which contain compatible attachments).

As a test of how quickly an uploaded file appears within Delve, I created a spreadsheet named “Excel-Test-for-Delve” and uploaded it to my OneDrive for Business site.  After about five minutes or so, the file appeared on my Delve main page:


Not bad.  From the Delve interface I can open the file, download it and perform all of the other standard Office 365 actions.

But what about security?  Microsoft addresses those concerns here at “Are my documents safe in Delve?

And if you’re wondering how to make sure Delve can find the things you want to be able to find, read, “Store your documents where Office Delve can get to them.”

But wait, there’s more…

In addition to using Delve via a web browser, there’s also a mobile interface for iOS and Android.

Here’s a quick look at how Delve is presented on iOS.

The main login page:


By clicking, “sign in now” you’re taken a page where you submit your Office 365 portal login:


And then the portal login:


Next you’re taken to the main splash page (note the options at the bottom of the interface):


By choosing the “My work” option at the interface’s bottom, you can view the most recent or most popular

delve-mobile-6Office 365 Delve is an interesting and potentially very powerful addition to the service.  The more information we store within a tenant, the more useful it will be giving users the ability to upload files without concerning themselves with each item’s precise location.

It’s the application of enterprise search technology to the task of locating data anywhere it’s stored provided “anywhere” is within the Office 365 galaxy of data objects.

One final thing…

Delve is built on Office Graph technology which you can read about in depth here.







SharePoint Online: PowerShell as Headache Reducer

Busby Berkley

Collaboration is beautiful.

When we collaborate, we accomplish miraculous things such as building pyramidssending robots to distant planets, staging intricately choreographed musical numbers or simply working together a little more efficiently.

Perhaps this is why SharePoint Online – a key part of the Office 365 suite – is popular.

By now, we all should be familiar with the sorts of things you can do with SharePoint such as:

  • Create sites for team collaboration
  • Build workflows to automate business processes
  • Create business intelligence portals

and much more…

There are two ways to manage SharePoint Online (SPO hereafter)which is SharePoint 2013:

  • Using the Office 365 Portal Administration Interface
  • Using PowerShell

If you’ve spent even a little time at this site, you’ve probably guessed that my preference is for PowerShell.

The reason is simple: fewer moving parts.

Consider the web portal:



Voila, a typical SPO admin portal (uh oh, looks like I may need to add more storage to my test environment but that’s a story for another time).

From here you can create new site collections, edit sites, assign administrative roles, modify the look and behavior of sites and many other things.

It’s a good interface; relatively straightforward – although not entirely intuitive (although to be fair, I’m not sure how intuitive it can be considering the application’s complex feature-set).

This is certainly a good management tool for making and controlling SPO sites.

The trouble is, sometimes the interface – or rather – the idiosyncrasies of the browser you use to control it – can create headaches that take you away from your actual goal of performing SharePoint administrative tasks.

To remove the browser from the process, I use PowerShell.


How to Login to SharePoint Online via Remote PowerShell

The cmdlet index for SharePoint Online isn’t as extensive as what’s available for Exchange or Skype for Business. Even so, it’s possible to perform all the basic functions of creating, modifying and deleting site collections, making it a very useful tool.

To run the SharePoint Online cmdlets, you’ll first need to download the SharePoint Online Management Shell which, as of this writing, is available at this link.

Once you’ve installed the management shell you can login to SharePoint online from your PowerShell session (launched as administrator) by running:

Connect-SPOService -Url -credential

Note that your login account must possess global administrator privileges or at least SharePoint admin privs to use these commands.

Also note that the URL for your SPO admin site isn’t (a common mistake) but

You’ll be prompted to provide your tenant credentials:


Once you’ve logged in, you’ll be able to issue cmdlets such as Get-SPOSite (which lists all currently running site collections):


You can see a full list of SharePoint Online cmdlets by going here.

Of course, your opinion may vary; that’s why we live in such an exciting world full of clashing swords (both real and rhetorical).

In my experience, managing SharePoint Online with PowerShell simplifies the SPO administrative part of my life considerably.