Understanding how Power Pivot and Power Query contribute to a successful Business Intelligence project built with Power BI

For readers familiar with Microsoft’s Power BI suite of tools for Excel, but not clear as to how Power Pivot, and Power Query interact within a Power BI project, a webcast from TechEd Europe 2014, titled Overview of Business Intelligence in Office and Office 265 may be worth watching. The webcast is led by Peter Myers, a subject matter expert on the topic, and runs about 1.25 hours.

Myers identifies the target user for the Power BI suite: a business analyst from a Line of Business (LoB) unit within an organization. He explains how research has shown analysts are the biggest users of Excel for data analysis. He also explains how a central IT support organization usually interacts with this target user, including a mention of why it makes sense for LoBs to sponsor an effort to produce a data governance plan, which a central IT organization can use to sign off on a business intelligence (BI) project, which an analyst, as the target user, will be likely to produce.

But what I found to be really useful within the first 10 minutes of the webcast, was how Myers presented the first of his three components of a successful BI project:

  1. “Explore”
  2. “Visualize”
  3. “Control”

The “Explore” phase, as Myers presents it in the webcast, is characterized by a need to collect data directly related to business process and to provide it with a form suitable for analytics manipulation. The steps in this phase amount to “[c]ombin[ing] and analyz[ing] large datasets; [s]ummarize data, and discover trends” along with a need to “[I]nstantly preview charts and pivot tables” (quoted from one of the slides included in this webcast).

As he describes the “large datasets” typically required for the type of analytics he is about to demonstrate, he segues into a presentation of Power Pivot as a tool developed to overcome Excel’s row limitation (he explains this limit amounts to 2 to the power 20 rows). He defines Power Pivot as “the ability to load data into the workbook, but not into the work sheet. It is loaded into a data model”. So Power Pivot is used to store large amounts of data for the overall Power BI process.

When he turns to discuss the process required “to access data”, which he explains typically includes a need to “access, filter, cleanse, and to transform before it is added to the workbook”, he introduces the Power Query add-in. He goes onto explain how Power Query can be looked at as the Data Warehousing tool for the business analyst to implement an ETL (Extract, Transform and Load) process in Excel.

To reiterate: stakeholders in a Power BI implementation for SharePoint 2013 on-premises, or for SharePoint Online, Office 365 should free up sometime to watch this webcast.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

Data Visualization is a popular requirement for Office 365 computing built on Business Intelligence components

Microsoft’s TechEd Europe 2014 event, which was held in October, 2014, included a presentation on the Business Intelligence (BI) opportunities presented by Microsoft Office and Office 365 products. The title of this presentation, which is now available for public viewing as a webcast on Microsoft’s MSDN Channel 9 web site, is Overview of Business Intelligence in Office and Office 365. The presentation is led by Peter Myers, an expert on Microsoft databases who is based in Australia. Myers is affiliated with Bitwise, a Microsoft partner.

Microsoft is not the only ISV looking to capitalize on the increasing appetite of enterprise business consumers for data and the visualizations (dashboards, charts, color coded heat maps, etc) produced from it. But Microsoft may be favorably positioned in the competitive landscape for this market. The core segment of enterprise consumers driving ISVs to produce ever more powerful data visualization solutions are Lines of Business (LoBs). LoBs usually exist within so-called silos. As the result of an important parallel enterprise computing trend, Bring Your Own Device/Bring Your Own App (BYOD/BYOA) LoBs now find themselves needing to “self manage”, in other words, to provide themselves with the development and administrative resources they require to successfully build out the computing infrastructure they require in order to drive business. Enterprise IT is neither positioned any longer to lead, nor even to support the kinds of development requirements LoBs surface.

What makes Microsoft’s position in this market attractive is the ubiquity of Excel as the spreadsheet platform of choice for most LoBs. Developing BI add-ons to Excel, Power BI, Power Pivot, Power Query, etc, made a lot of sense since the procedures required to install these tools, administer them, and customize views to deliver targeted data visualizations were all kept within the scope of the skill set of what Gartner calls “Citizen Developers”, meaning power users who play other roles in the organization, but, nevertheless, have the skills required to configure applications to deliver a lot of the result they require.

Microsoft competitors (for example, IBM and Apple, or even Tableau Software) are attempting to work with the same consumer segment with similar solutions for Excel. Nevertheless as the IP owner for Excel, as mentioned at the top of this post, Microsoft looks to be the leader, at least at the time of this post.

The next post will include comments on the opening section of Myers’ presentation.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

What are the mandatory requirements organizations must meet to support hybrid computing scenarios built with SharePoint 2013, on-premises, and Office 365?

So what are the mission-critical components of an acceptable foundation for a hybrid scenario for SharePoint 2013 on-premises computing, and Office 365? Paolo Pialorsi presents 6 of these in a webcast recorded at Microsoft’s TechEd Europe 2014 event, which was held in October, 2014 (the title of Pialorsi’s presentation is Overview of Microsoft SharePoint 2013 and Office 365 Hybrid Scenarios):

  1. “Directory Federation via ADFS and Azure AD Sync
  2. “Internet routable AD domains”
  3. “SSL certificates and secured communication channels”
  4. “Good bandwidth and Internet connectivity”
  5. “Office 365 Enterprise Subscriptions”
  6. “SharePoint Server 2013 Enterprise on-premises

The fifth and sixth components are obvious. The fourth requires further definition as “good” means different things to different people. For graphics intensive computing (working with dashboards, Power BI, Power Query, etc), high speed broadband data connectivity (at speeds faster than 3M per second download) should be in place. But for normal web page viewing, speeds below 1M per second should suffice.

The third component must be supplemented. Anyone following the 2014 tech industry news will likely be familiar with the security problems detected in the OpenSSL data communications standard. Neither Office 365, nor SharePoint Server 2013 (or Windows Server, SQL Server, etc) made use of the standard, but third party applications may have made use of this standard, and, therefore, should be closely reviewed to ensure any security issues have been corrected.

More broadly speaking, a plan must be created for users to implement as to precisely how data (corporate, personal, third party, etc) is to be handled in a hybrid computing scenario to buttress the third component of Pialorsi’s list with secure procedures. So this third component should be recognized as, perhaps, even more “mission-critical” than any of the others.

Of course, it will not be possible to enforce a set of security procedures without the first and second components of the list. So care must be exercised to ensure ADFS and Azure AD Sync have not only been correctly implemented, but are also, themselves, secure.

Pialorsi includes a few limitations on the scope of what can be done with the hybrid scenario he presents. These include the following:

  1. “Office 365 + DirSync does not support multi-forest environments”
  2. “Some Service Applications cannot be shared” – “User Profile Service” “Managed Metadata Service” “Word Automation Services” “Workflow Services”

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

The objective for a hybrid cloud computing solution should be to bring on-premises and cloud computing together

Early into a one hour presentation on hybrid computing scenarios for SharePoint 2013 on-premises, and Office 365, Paolo Pialorsi states an objective most organizations will likely implement when they embark on a hybrid cloud computing project — to build an integrated computing solution from cloud and on-premises components.

The title of Pialorsi’s presentation is Overview of Microsoft SharePoint 2013 and Office 365 Hybrid Scenarios. He points to two prime areas where hybrid computing scenarios will likely make sense: enterprise search, and any services built with Business Connectivity Services (BCS). The latter usually pop up for organizations needing to pull external data into a SharePoint computing environment.

When the broad benefit of a seamless computing experience is applied to enterprise content search, Pialorsi explains, users will be able to “[s]earch for content in both SharePoint Server 2013 and SharePoint Online at once”.

These users should also be able to consume “on-premises business data” from SharePoint Online (and, presumably, through the use of mobile computing devices like smartphones and tablets).

Finally, the computing experience for users, regardless of whether or not they are working on-premises, or remotely, must be uniform.

The principal drivers pushing organizations to implement these scenarios include polices designed to support Bring Your Own Device (BYOD) initiatives. Once BYOD policies are approved, most organizations experience a substantial change in the manner under which data is processed. Some content is reposed on line, in addition to the usual content with is stored on-premises.

Pialorsi cites Active Directory Federated Services (ADFS) as one control organizations should implement to simplify the task of supporting BYOD computing by providing communities with a “single sign-on for users via ADFS”.

Two other drivers for hybrid scenarios include trusted solutions and performance concerns with regard to “[s]ome business data (ERP, DBMS, DWH) [which] are on-premises”.

We’ve written elsewhere in this blog on the topic of how organization can, inadvertently, restrict their own ability to capitalize on perceived benefits to be realized from a decision to implement cloud computing, by implementing full trusted solutions for SharePoint. One can argue the Office 2013 app model provides a route for these organizations out of this restriction, but, as Pialorsi notes, the pace at which organizations have implemented the Office 2013 app model has been very slow. Bottom line: as long as organizations require trusted solutions, SharePoint 2013 computing, on-premises, will have to stay as is.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

Hybrid scenarios for SharePoint 2013 and SharePoint Online Office 365 were presented at Microsoft’s TechEd Europe 2014

Hybrid computing scenarios for SharePoint 2013 and SharePoint Online, Office 365 were presented at Microsoft’s TechEd Europe 2014 event last month. The webcast of this presentation, which is led by Paolo Pialorsi, and titled Overview of Microsoft SharePoint 2013 and Office 365 Hybrid Scenarios is of interest to us. We recently an online course on the same topic, which was led by Fabian Williams, a Microsoft MVP. We will also host a one hour panel discussion next Wednesday, November 19, 2014 on the topic, as well. Anyone interested in this topic who would like to attend our webinar next week can register online.

A lot of analysts have written about hybrid computing since Satya Nadella, CEO of Microsoft spoke to the concept during Microsoft’s most recent earnings conference call. These analysts have, generally, focused on the implications of a hybrid computing scenario on data storage. But as Pialorsi illustrates via his presentation, and Fabian Williams also extensively examined during our online class, when the computing platform underpinning the discussion is SharePoint, a successful effort to build a hybrid computing solution between SharePoint 2013 and Office 365 promises a lot of other benefits for organizations, as well.

The principal computing themes promising a lot of value from a hybrid scenario are enterprise search and external data and business connectivity services (BCS). The catalyst for engaging in an effort to mine this value is, as Pialorsi points out, the very slow rate at which enterprise organizations are moving to a cloud, only, computing solution. These organizations are not only likely to implement a comprehensive computing solution like SharePoint 2013, they are also very likely to decide to simultaneously support separate instances of SharePoint computing on-premises, and in the cloud via Office 365.

Any opportunity to safely interconnect both computing platforms is likely to stimulate implementation interest by these organizations and, therefore, is worth an exploration. Microsoft, and its consumers, are uniquely positioned to truly capitalize on this opportunity.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

Some limitations on building dynamic diagrams with Visio Services for SharePoint 2013 on-premises and SharePoint Online, Office 365 should be kept in mind

This a second and final post on a webcast published on Microsoft’s Channel 9 web site titled Interactive Visual Dashboard Solutions with Visio Services. There are some important caveats about using Visio Services to produce data visualizations like the “interactive dashboards” Ian White presents in this presentation.

Data sources are limited. Visio Service currently provides no support as of yet for OData, so linking to external data from sources like MySQL, or other non SQL Server databases otherwise accessible via the OData protocol unfortunately won’t work. As well, Data linking is only available with Visio Professional, (available online as Visio Pro for Office 365). The graphics produced with Visio Standard will publish correctly with SharePoint, Enterprise Edition.

The Visio diagrams, as published for browsers, are presently read-only. So it is not possible, presently to draw on the diagrams “in the browser”. On a positive note, some of the process of creating shapes is automatic once data is linked to a Visio diagram. As well, an extensive set of symbols and images is provided with either Visio Standard, or Visio Professional, out-of-the-box. These lend themselves to the kind of implementation in dashboard displays usually created for data visualizations built on business performance metrics.

There are differences between what users can do with dynamic diagrams built with Visio Services on-premises vs. in the cloud: With SharePoint 2013 on-premises, “Native External Lists support via BCS – WCF, .NET, OData” is possible; however, in an Office 365 implementation, “Native External Lists support via BCS” is the only option. As well, the security features of Visio Services with SharePoint 2013 on premises “Supports Kerberos, SSS & Unattended Authentication”, vs. the “multi-tenant environment” of Office 365, as a public cloud option.

On the subject of available data sources, Visio Services for SharePoint 2013, on-premises can be used to link to SQL data via ODC, Excel Services, SP Lists via BCS, and any other data sources conforming to OLEDB or ODBC. Custom linkage is also possible. But in an Office 365 implementation, Excel Services and/or SP Lists via BCS are the only option.

For anyone curious as to what topics are covered in the remainder of webcast, Ian White does provide a working demonstration of how to build interactive diagrams within this presentation, complete with demonstrations of how to use the Visio feature set.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

The agenda at TechEd Europe 2014 included a presentation on dynamic diagrams and SharePoint on-premises or online

Visio is usually seen as the successor to SharePoint Designer. The common understanding is Visio represents the “next generation” of workflow design tools. But it’s very important not to forget Visio is, if nothing else, a diagram tool.

During Microsoft’s TechEd Europe 2014 event, last month, Ian White, EPM and Visio Technical Lead at Microsoft in the UK, led an hour presentation titled Interactive Visual Dashboard Solutions with Visio Services. It’s worth noting the venue for displaying these “visual dashboard solutions”, and providing dynamic data for them, is SharePoint on-premises, or SharePoint Online, Office 365.

White points out how some of SharePoint’s out-of-the-box features add substantial value to any effort to implement Visio Services for dynamic (meaning interactive) dashboards:

  • “Central Location”
  • “Version Control”
  • “Backup”

White also cited the ubiquity of “modern browsers”, which require “no configuration” and are readily “device accessible”. These clients provide a “consistent UX” across a range of hardware form factors.

Using SharePoint as the publishing method for these diagrams also allows persistent data communications with data sources like SQL Server to provide a periodic, automated refresh to ensure users are consuming a current view of these diagrams.

White mentions web services, Javascript, and web parts as vehicles for the data communications required between Visio Services and data sources across SharePoint (he also mentions OneDrive as a method of publishing the diagrams).

Data sources can include “multiple external data sources”, which are “configured using the ‘data linking wizard’ of the Visio Services client”.

SharePoint-Videos has recently published a video tutorial set on working with External Data in SharePoint 2013. The set is led by Fabian Williams, a SharePoint Server MVP and is titled SharePoint 2013: Working with External Data​ in SharePoint 2013 using BCS and Workflows. This set of video training looks to be a complement to White’s technical presentation. The set can be purchased for individual, or unlimited viewing across an organization. Please contact us for further information on our set.

White also reports on a couple of changes in Visio 2013. These include publishing all diagrams as .png images (Silverlight is no longer used).

Dynamic, truly interactive diagrams are “accessible only via SharePoint” (either on-premises via Enterprise, or in the cloud, Office 365 via an E3 or E4 subscription).

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

Use Office 365 Groups to consolidate collaboration efforts and simplify the Office 365 user experience

On September 25, 2014, Jared Spataro, General Manager of Enterprise Social published a post titled Delivering the first chapter of Groups in Office 365. With this post, and the short video embedded within it, Microsoft presented a new method for Office 365 consumers to use to collect users into groups.

As Jared Spataro points out in his post, it’s not as if this type of method was at all lacking across the various components of the Office 365 tools. Lync, Outlook, and Yammer all offer methods of collecting users into teams and then pooling files, calendars, etc. The opposite is the case. There are too many ways to group users. So it is safe to say Microsoft built Office 365 groups to reduce the confusion and complexity of online collaboration.

Office 365 Groups inherit the group calendar and inbox features of Team Sites. The Office 365 Groups Landing Page presents detail over and above the information in Jared Spataro’s post. Group scheduling and the option to share files are defined and emphasized.

For anyone using Google +, the membership process for Office 365 public groups will be very familiar. No need to apply for membership, anyone can join a public group. Group participants can collaborate when they are out of office, as long as they can access Outlook Web App from mobile devices. The Office 365 Groups landing page includes set up instructions for iOS and Android mobile devices, as well as instructions as to how group participants can connect to their groups with only a web browser from a PC.

SharePoint administrators can use the Admin Center in Office 365 to manage Groups. The documentation cites four types of groups:

  • Group
  • Security Group
  • Mail-enabled Security Group
  • Distribution List

It’s important to note “With the exception of security groups, the global admin can’t create groups in the Groups section of the Office 365 admin center at this time.” So groups must be created by users from either “Outlook, People, Calendar, or OneDrive”. Nevertheless, Administrators can certainly manage groups via the Admin Center.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

SharePoint-Videos will host a webinar on hybrid, online, or on-premises SharePoint server options on November 19, 2014

As Satya Nadella made clear during Microsoft’s most recent quarterly earnings report, a strong driver of the success of Microsoft’s Azure and Office 365 products is the unique position of servers for Microsoft’s ubiquitous enterprise applications (not the least of which is SharePoint) at the so-called “edge” of the cloud, meaning on-premises. But how can business and organizations in the public and not-for-profit sectors get the most value out of the opportunities presented by the choices they have as regards serving SharePoint?

On November 19, 2014, SharePoint-Videos will host a one hour webinar on this topic. The webinar is titled Webinar – Hybrid, Online or On-Premises?. Attendees will have an opportunity to present their questions on this topic directly to three highly qualified panelists, including:

  • Mark Kashman, Senior Product Marketing Manager, SharePoint, at Microsoft
  • Rob LaMear IV, Founder and CEO of FPWeb.net
  • and Fabian Williams, a Microsoft MVP and leading author of SharePoint-Videos training content on SharePoint server

If your organization is considering how best to provision SharePoint computing resources based on the needs of your community of users, then you could not ask for a much better opportunity than this one to present your questions to three subject matter experts on this topic.

But if you have not yet decided whether or not it makes sense to consider an option other than on-premises servers for SharePoint, you will likely benefit from attending this short webinar, as well. BYOD and a trend towards “consumerized IT”, whereby the familiar path by which innovation used to enter the organization (meaning via the notions recommended by a central IT function) has taken a detour and lines of business (LoBs) have become the pioneers for new technology, almost certainly are coloring the computing experience for your personnel. Therefore, it makes sense to attend this webinar which will consumer absolutely nothing more than one hour from your schedule (there is no charge to attend).

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved

Comments on the Microsoft Roadmap for Enterprise Social Webcast from TechEd Europe 2014

This writer attended SharePoint Conference 2014, including the Keynote presentation where Jared Spataro, General Manager of Enterprise Social at Microsoft first presented the “Work Like a Network” theme.

Some 7 months later, in late October, 2014, in a presentation titled The Microsoft Roadmap for Enterprise Social, Christophe Fiessinger, Group Product Marketing Manager for Enterprise Social at Microsoft picked up on the theme. This time the marketing communications content amounted to a 2 minute advertisement depicting the theme in a video presentation targeted to a business audience, along with Fiessinger’s monologue about how the theme has played out in his own experience.

Fiessinger presented three benefits most organizations should realize from an implementation of enterprise social computing with a product like Yammer in conjunction with either Office 365 or SharePoint. The three benefits are:

  • “Listen to conversations that matter”
  • “Adapt and make smarter decisions”
  • “Grow your business”

Through groups, newsfeeds, etc, organizations implementing enterprise social computing solutions have an opportunity to engage with customers to learn more about emerging needs for new products, presumably extending benefits realized from past product purchases.

The information collected through listening to these important conversations can be applied to identify where it makes sense to change things. The process of making these changes amounts to adapting, “mak[ing] smarter decisions”.

The end result of this process, if successful, should be to “grow your business”.

Leaving aside the considerable number of abstractions included in these three points, there is certainly a case to be made for implementing an enterprise social effort with objectives consistent with the three Fiessinger presents. Naomi Moneypenny, CTO of Synxi made very similar points in an audio recording exclusively available from SharePoint-Videos, titled SharePoint 2013: Making Enterprise Social Real in your Organization with Yammer and SharePoint. Naomi’s presentation is granular, complete with a set of recommendations of how best to plan for Yammer Groups and to manage them. But Fiessinger’s points are entirely consistent with the key takeaways from Moneypenny’s set.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved