Find All Those SpreadMarts and Overgrown Excel Files Now in Office 365

Microsoft released a tool with Office 2013 called the Discovery and Risk Assessment tool that helps your IT organization find spreadsheets that are overly complex and risky.  It uses a risk matrix that ranks your spreadsheets based on complexity of formulas, the number of sheets, the number of external connections and cross linking of workbooks.

Microsoft has just announced that this feature will be moving into the eDiscovery Center in Office 365.

Using a set of configurable search criteria, you can now add finding spreadsheets to your eDiscovery rules and track down overgrown spreadsheets based on a number of items and complexity of workbooks.   As part of the eDiscovery Center, you can then also set rules on what do with found items, e.g. notify people, place a legal hold on the files, export the results, etc.

Read More

Cloud Service Level Agreement Standardization Guidelines

 

The European Commission has published a document called the Cloud Service Level Agreement Standardization Guidelines that provides a technology neutral, business model neutral, and global applicable set of standards for measuring service level agreements from the growing number of cloud service providers.  The document also includes a very good list of definitions for cloud computing.

The following are Service Level Objectives defined as part of the standard.  If you are looking to purchase cloud services from any vendor, this is a good list to start with when evaluating their Service Level Agreements.

Performance Service Level Objectives

  • Availability
  • Response Time
  • Capacity
  • Capability Indicators
  • Support
  • Reversibility and the Termination Process

Security Service Level Objectives

  • Service Reliability
  • Authentication and Authorization
  • Cryptography
  • Security Incident Management and Reporting
  • Logging and Monitoring
  • Auditing and Security Verification
  • Vulnerability Management
  • Governance

Data Management Service Level Objectives

  • Data Classification
  • Cloud Service Customer Data Mirroring, Backup and Restore
  • Data Lifecycle
  • Data Portability

Personal Data Protection Service Level Objectives

  • Codes of Conduct, Standards and Certification Mechanisms
  • Purpose Specification
  • Data Minimization
  • Use, Retention and Disclosure Limitation
  • Openness, Transparency and Notice
  • Accountability
  • Geographical Location of Cloud Service Customer Data
  • Intervenability

Read More

Microsoft Band Released – New Fitness Tracker

image

Microsoft has released a new fitness band product called Microsoft Band.  Like other fitness bands on the market, it tracks your activity including heart rate tracking and sleep tracking.  It also tracks GPS so that you can track where you have run, walked or biked.  It also has a UV sensor to track sun exposure and a galvanic skin response measurement to track stress. 

Microsoft Health

Microsoft Band will work with Android, iOS and Windows phones.  Microsoft Band also provides email and calendar preview and built in Cortana based voice activation.  Cortana will only be available though if you have a Windows phone.

Microsoft Health

Data is stored in Microsoft’s Health platform (what used to be called Health Vault) and Microsoft is promising machine learning algorithms that will provide you with recommendations on work outs, anticipate your needs and keep you motivated throughout the day.

Read More

Process your Big Data Using New Azure Data Factory

Microsoft has released a new ETL type service for processing big data called Azure Data Factory.  Like a traditional ETL engine, this data processing factory is designed for orchestrating incoming data sources and processing them into new data models. 

The Azure Data Factory though is designed for hyper scale, is cloud based, and can orchestrate multiple types of data including on premises SQL Server, Azure SQL, Azure Blob and Azure Table.  You can use multiple processing languages such as Hive, Pig or C#.

Todays Diverse Processing Landscape

Azure Data Factory also supports data lineage tracking for pinpointing issues with your transformations and data dependencies.

Read More

New Office 365 APIs Provide REST Access to Files, Sites, Mail, Contacts, Calendars and Users

Microsoft has just announced the preview release of the new Office 365 APIs. 

Imagine building an iOS or Android mobile app that processes a reservation and then creates a calendar appointment in the user’s outlook calendar.  The iOS SDK and Android SDKs have been just been revamped as well.

Your app can also be pinned to the new App Launcher which was announced earlier this month.

Development stack for creating solutions that use Office 365 APIs. Select your developer environment and language. Then use Azure single sign-on authentication to connect to the Office 365 APIs.

Read More

Hyper Scale Parallel Processing with Azure Batch

Microsoft has released in preview another new service – Azure Batch.  Batch is a job scheduling framework that allows you to distribute jobs to dozens or hundreds or thousands of computers on demand.

Here is a great scenario where Batch would be awesome – we have an insurance client who has a proprietary 500 core computing grid, all running on premise, to calculate actuarial tables for its various insurance plans.  It refreshes its plans once a month and needs the intense computing power in order to take incoming actuals and turn them into policy rates. 

Imagine instead of building your own 500 core cluster and paying for proprietary grid frameworks you can just rent one by the hour?  This is essentially what Microsoft Azure is providing with Batch, coupled with an easy to use .NET or REST API.

Parallel tasks

The key value proposition with Batch is the same as any cloud service – you pay by the minute and you can dynamically spike up and down the cores you need.  Each instance in your resource pool of VMs costs as little as $0.008 / hr – that means you could be running batch jobs across 1,000 virtual machines at $8 / hr.  If you need a high performance compute environment, you can have rent a cluster of A8 instances each with 8 cores and 56 GB of RAM for $0.0317 / hr – go rent 1000 for just $31.70!

Batch is designed for running large volumes of tasks programmatically where there is an opportunity to go massively parallel with data processing.  Good examples of such scenarios include:

  • Image or video processing
  • 3D rendering
  • Software testing
  • Indexing of files
  • Actuarial analysis
  • Risk modeling
  • Weather analysis

Creating a new Batch account is easy – of course, it’s just done through the Azure portal.

Create a Batch account

Read More

New Office 365 Security and Compliance Features Announced

Microsoft has just announced new security and compliance features in Office 365. 

Mobile Device Management for Office 365

Microsoft is making a big play at the moment for comprehensive enterprise class mobile device management.  Office 365 will include its own set of MDM features rolled out in the first quarter of 2015 including:

  • Apply security policies to only allow for synchronization of Office 365 documents on corporate devices
  • Block Office 365 access if a device is stolen or jailbreaked
  • Perform a selective wipe of just your Office 365 data

 

Expanding Data Loss Prevention (DLP) Beyond Email and SharePoint

As reported earlier, Data Loss Prevention is a method in Office 365 to look for documents with sensitive information such as credit card numbers, SIN numbers, etc. and to flag them in an e-discovery type interface.  Originally built for Exchange Online, DLP has been expanded into SharePoint and OneDrive for Business.

Microsoft is now promising to integrate DLP into native Office applications such as Excel, PowerPoint or Word so that if your users put a list of credit card numbers for example in Excel it would warn them at the point of document creation.

Advanced Encryption with Per File Keys

Microsoft is also rolling out more advanced encryption of files at rest that includes per file encryption.  Each file get its own unique encryption key and every update refreshes the key.  This significantly increases the difficulty in decrypting your files because each file essentially has to be hacked to find its key and gaining access to one file doesn’t mean access to any other file because each one has its own key.

Read More

Integrating Excel and Google Maps to Geo-code Your Addresses

My sister was looking for an easy way to calculate distances so I wrote her a couple macros that that she could use in Excel.  The code is below for anyone who wants to borrow it and extend it further.

Using Google MAP APIs

Google provides a very well documented and easy to use API that is accessible through basic HTTP requests.  The response it returns can be either JSON or XML format – in our case we’re using the XML format because the MSXML parser can easily parse it within an Excel Macro.

In order to use the XML parser, you need to have a reference to the Microsoft XML v6.0 COM object.  In the macro editor, go under Tools –> References and make sure this component is checked.  If the component isn’t in the list, you can browse to it – it should be located in the C:\Windows\System32 directory and the DLL is msxml6.dll.

image

Looking Up Longitude and Latitude

The objective in this scenario is pretty simple – take a list of addresses and geo-code them with longitude and latitude values.image

Google Maps API provides the ability to look up longitude and latitude:

https://maps.googleapis.com/maps/api/geocode/xml?address=New+York

Looking Up Distance

The objective in this scenario is to take a list of source and destination addresses and calculate the distance based on the shortest route that Google Maps calculates.  The advantage of this approach is this factors in the specific routing through streets instead of calculating it “as the crow flies” (e.g. directly from one point to another).

image

Google Maps API provides the ability to look up directions and routes and provides a total distance in meters for each route.

https://maps.googleapis.com/maps/api/directions/xml?origin=Chicago&destination=Toronto

VBA Macro Code

Option Explicit

' Subroutine to plug in Longitude and Latitude for a range of locations.
Public Sub LookupLongitudeAndLatitude()
    
    ' Declare variables
    Dim CurrentSheet As Worksheet
    Dim CurrentRange As Range
    Dim row As Range
    Dim Address As String
    Dim Longitude As Double
    Dim Latitude As Double
    Dim Success As Boolean
    Dim BlankValues As Boolean
    Dim Status As String
    
    ' Get the current selected cells
    Set CurrentSheet = ActiveSheet
    Set CurrentRange = Selection
    
    
    ' check for current range size to see if three columns are available.
    If CurrentRange.Columns.Count <> 3 Then
        MsgBox ("Please select 3 columns, one with addresses in it and two blank to put the long and lat values")
    Else
            
        For Each row In CurrentRange.Rows
          BlankValues = True
          Address = row.Cells(1, 1)
          
          ' Check for existing values.  We're expecting blank for columns 2 and 3 so if they have any content,
          ' we'll error out and send a warning isntead of writing over existing cells.
          If IsEmpty(row.Cells(1, 2)) <> True Then
            MsgBox ("Expected longitude column to be blank for cell " & row.Cells(1, 2).Address(False, False))
            BlankValues = False
          End If
          If IsEmpty(row.Cells(1, 3)) <> True Then
            MsgBox ("Expected latitude column to be blank for cell " & row.Cells(1, 3).Address(False, False))
            BlankValues = False
          End If
          
          If BlankValues = True Then
            Success = GetLongitudeAndLatitude(Address, Longitude, Latitude, Status)
            If Success = True Then
                row.Cells(1, 2) = Longitude
                row.Cells(1, 3) = Latitude
            Else
                row.Cells(1, 2) = Status
                row.Cells(1, 3) = Status
            End If
            
          End If
        Next row
    End If
    
    ' reset selection to original range
    CurrentSheet.Select
    CurrentRange.Select
    
End Sub

Private Function GetLongitudeAndLatitude(Address As String, Longitude As Double, Latitude As Double, Status As String) As Boolean

    ' Declare variables and set return value to false by default
    GetLongitudeAndLatitude = False
    Dim response As DOMDocument60
    Dim http As XMLHTTP60
    Dim node As IXMLDOMNode
    Dim nodes As IXMLDOMNodeList
    Set http = New XMLHTTP60
        
    ' Read the data from the website
    On Error Resume Next
    ' Open an XML request from Google using their GeoCode API
    http.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?address=" & URLEncode(Address), False
    http.send
    Set response = http.responseXML
        
    ' get the status node.  This node tells you whether your search succeeded - OK means success.  Any other status means some kind of error or address not found.
    Set node = response.SelectSingleNode("/GeocodeResponse/status")
    If node.nodeTypedValue <> "OK" Then
        Status = node.nodeTypeString
    Else
        Set nodes = response.SelectNodes("/GeocodeResponse/result")
        ' check for multiple addresses if we found more than 1 result then error out.
        If nodes.Length > 1 Then
            MsgBox ("Found Multiple Matches for Address: " & Address)
        Else
            ' grab the latitude and longitude from the XML response
            Set node = response.SelectSingleNode("/GeocodeResponse/result/geometry/location/lat")
            Latitude = node.nodeTypedValue
            Set node = response.SelectSingleNode("/GeocodeResponse/result/geometry/location/lng")
            Longitude = node.nodeTypedValue
            GetLongitudeAndLatitude = True
        End If
        
    End If
    
    Set http = Nothing
    Set response = Nothing
    
End Function

' URL Encoding function courtesy of http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba
Private Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

' Subroutine to plug in Distance between two addresses.
Public Sub LookupDistance()
    
    ' Declare variables
    Dim CurrentSheet As Worksheet
    Dim CurrentRange As Range
    Dim row As Range
    Dim SourceAddress As String
    Dim DestinationAddress As String
    Dim Distance As Double
    
    Dim Success As Boolean
    Dim BlankValues As Boolean
    Dim Status As String
    
    ' select the currently selected range
    Set CurrentSheet = ActiveSheet
    Set CurrentRange = Selection
    
    ' check for current range size to see if three columns are available.
    If CurrentRange.Columns.Count <> 3 Then
        MsgBox ("Please select 3 columns, one with source address, one with destination address and one for distance.")
    Else
        
        For Each row In CurrentRange.Rows
          BlankValues = True
          SourceAddress = row.Cells(1, 1)
          DestinationAddress = row.Cells(1, 2)
          ' Check for existing values.  We're expecting blank for column 3 so if it contains any content,
          ' we'll error out and send a warning isntead of writing over existing cells.
          If IsEmpty(row.Cells(1, 3)) <> True Then
            MsgBox ("Expected distance column to be blank for cell " & row.Cells(1, 3).Address(False, False))
            BlankValues = False
          End If
         
          If BlankValues = True Then
            Success = GetDistance(SourceAddress, DestinationAddress, Distance, Status)
            If Success = True Then
                row.Cells(1, 3) = Distance
            Else
                row.Cells(1, 3) = Status
            End If
            
          End If
        Next row
    End If
    
    ' reset selection to original range
    CurrentSheet.Select
    CurrentRange.Select
    
End Sub

Private Function GetDistance(SourceAddress As String, DestinationAddress As String, Distance As Double, Status As String) As Boolean
    
    ' Declare variables and set return value to false by default
    GetDistance = False
    Dim response As DOMDocument60
    Dim http As XMLHTTP60
    Dim node As IXMLDOMNode
    Dim nodes As IXMLDOMNodeList
    Set http = New XMLHTTP60
        
    ' Read the data from the website
    On Error Resume Next
    
    ' Open an XML request from Google using their Directions API
    http.Open "GET", "https://maps.googleapis.com/maps/api/directions/xml?origin=" & URLEncode(SourceAddress) & "&destination=" & URLEncode(DestinationAddress), False
    http.send
    Set response = http.responseXML
        
    ' get the status node.  If it isn't OK then we have either an error or no address found.
    Set node = response.SelectSingleNode("/DirectionsResponse/status")
    If node.nodeTypedValue <> "OK" Then
        Distance = 0
        Status = node.nodeTypedValue
    Else
        Set nodes = response.SelectNodes("/DirectionsResponse/route")
        If nodes.Length > 1 Then
            ' this should never happen unless alternatives=true is added on to the URL above.
            MsgBox ("Found Multiple Routes for Source Address " & SourceAddress & " and Destination Address " & DestinationAddress & ".")
        Else
            ' Grab the distance value from the XML - it's in meters so we'll divide by 1000 to convert to KM
            Set node = response.SelectSingleNode("/DirectionsResponse/route/leg/distance/value")
            If node Is Not Null Then
                Distance = node.nodeTypedValue
                Distance = Distance / 1000
                GetDistance = True
            End If
            
        End If
        
    End If
    
    Set http = Nothing
    Set response = Nothing
    
End Function

Read More

Faster Azure VM and Storage Options Just Announced

Microsoft has announced a new series of VM sizes called G-Series which provides higher performance, faster I/O and more RAM than current VM sizes.

VM Size Cores RAM (in GB) Local SSD Storage (in GB)
Standard_G1 2 28 406
Standard_G2 4 56 812
Standard_G3 8 112 1630
Standard_G4 16 224 3250
Standard_G5 32 448 6500

 

These VM sizes will be available later this year.

In addition, Microsoft is providing a new high performance storage called Azure Premium Storage which offers SSD based storage designed for intensive I/O workloads.  For those looking at shifting their SQL Server workloads to the cloud, this will be welcome news.

Pricing has not yet been announced.

Read More