Skip to content

Year: 2015

Ignite 2015 Impressions

I don’t normally do conference summaries, but Ignite was just so big, and there was so much information that I felt the need to record my thoughts around it, and decided to share. Ignite was very much cross product, which is in line with where Microsoft seems to be headed – a focus on the function, not the tooling. With around 24,000 people in attendance, the conference, and the logistical issues that it imposed was too big for my taste, but the amount of information was excellent, and I imagine that I’ll be digesting it for some time to come. For now, here’s how I interpreted it all.

Azure and Office 365

Cloud services are killing it.

Between Azure’s Platform as a Service (PaaS) and Infrastructiure as a Service (IaaS) and Office 365’s Software as a Service (SaaS), Azure Active Directory is already sporting over 450 million active users. Azure Active Directory is what is used by Office 365, and the accounts within are otherwise known as Organizational Accounts. It’s an important metric because I believe that the Microsoft strategy is to own identity online. It makes sense when you look at what they seem to be doing.

For years, they absolutely dominated operating systems. Nothing to this day has ever really touched them on the desktop, but Apple changed the base with mobile, and developers flocked there. Google tried to do the same thing to Apple, and has been quite successful, but not fully so. While Android is in the majority in the mobile space, iOS is still quite strong, and shows no signs of diminishing. Windows isn’t really a factor in mobile, but still dominates the desktop which remains significant (about 300 million units/year), and is a factor on tablets. Microsoft got flanked by Apple and Android, and is holding the fort, but not conquering any new territory.

Microsoft now seems to be focusing on cloud services, and they don’t care what platform is being used to consume them. I think that at the core of this strategy is cloud identity – whether it is consumer (Microsoft Account) or enterprise (Azure Active Directory). With this identity strategy, Microsoft is attempting to again change the base – to outflank both Apple and Google and make the operating system almost irrelevant. Every app they’re putting out now is usually for iOS first, then Android, then Windows Phone. The new Universal app platform likely means that they will come out for Windows (desktop, phone, whatever) at initial launch with iOS, but the bottom line is that an awful lot of effort is going into supporting all platforms all the time. If the apps work well across platforms, then the choice of operating system simply becomes one of personal preference, not of features. It gets marginalized, and Microsoft owns the back end service. That’s why I think that so much effort has gone into this strategy.

Another thing that I sensed at the show was that in the past, all of the talk around identity and federation (ADFS) was about bringing your on-premises identities into the cloud to support a few new services. Now, there seems to have been a real shift, and the reason for adopting ADFS is to bring the Azure Active Directory identities back down on-premises to where legacy applications can use them. It’s a subtle shift, but discernable.

One of the more interesting product introduced into Azure recently is Logic Apps. As far as I can tell, Logic Apps are the cloud manifestation of BizTalk, which is an excellent product with a steep learning curve. Logic apps remove the learning curve and allow you to quickly connect and flow data through multiple systems. The session on logic apps can be seen here:

SharePoint 2016

In the past, SharePoint announcements would warrant their own post, but now SharePoint is probably best seen as part of a greater whole. Details on SharePoint 2016 details were first announced at Ignite, and I feel that the most informative session was Bill Baer’s on Wednesday morning where he outlined the major architectural changes:

Not surprisingly, this release will be very much about hybrid Sharepoint/Office 365 scenarios. Some of the notable items from the talk are:

  • SharePoint Server 2016 Will require 64 bit Windows Server 2012 or Widows Server 10, and SQL Server 2014 SP1 as a minimum
  • Standalone installations are no longer supported. It will be possible to install SharePoint and SQL Server on the same machine, but full SQL Server will be required, and SQL Express will no longer be supported. This obviously raises questions about whether or not there will be a free SharePoint Foundation SKU with the next release.
  • PerformancePoint will in fact be included with SharePoint 2016. I doubt very much that there will be any investments in it at all, but it will at least be there. I’d view this as legacy support.
  • SharePoint 2016 will support SAML claims as a first class citizen. That means that it will be possible to login with Azure Active Directory credentials, and is an example of bringing cloud identities on prem. However, don’t trash that domain controller just yet, I’m sure that service accounts will still need to be NTLM – SQL Server needs it.
  • There will be a new Roles Based installation. It will be much simpler to install and maintain servers with specific roles such as web front end, search, etc. BI will be one of the roles.
  • There will be new boundaries. Content databases up to Terabyte sizes, 10 GB file size limit, list thresholds of much greater than 5000 items (although how much greater was not specified)
  • No more FIM. The user profile engine that we’ve all grown to….. deal with from SharePoint 2010 and 2013 is no longer embedded. The full Forefront Information Manager can be used, but the default profile import mechanism will be the good ol’ User import from SharePoint 2007.
  • Durable resource based links. Every object in SharePoint will receive its own resource based URL. That means that it can be moved around in the farm, and reference URLs will still work. This is like permalinks in WordPress.
  • While not final, a preview was shown of some operational reporting. This is primarily “speeds and feeds” type information that would interest a farm administrator, although simple usage reporting could be seen.
  • Integration with the Office Graph – see below section on Delve.

SQL Server 2016

The next release of SQL Server was announces at Ignite. Its chock full of new things, focused primarily at hybrid operation and analytics. One of the more interesting concepts in this version is the ability to “stretch” a database into the cloud. With this, you can take an on-premises database, and extend it into Azure SQL, specifying rules to determine which data goes where. Given that online storage is significantly cheaper than on-premises, this makes total sense, and they’ve figured out a way to make it work reliably. The overall SQL Server keynote can be found here:

I’m very interested in the analytics capabilities, and the session outlining the improvements to SQL Server BI is found here:

I found the following items particularly notable:

  • A comment was made during the BI session that Microsoft is “Super Committed” to SQL Server Reporting Services (SSRS). Hopefully this helps quell the naysayers. SSRS is receiving a major facelift in this version, bringing a modern design experience. In addition, the parameters pane has received a great deal of attention, adding, among other things, support for cascaded dropdowns.
  • Datazen is a visualization company based in Toronto that was recently acquired by Microsoft. There is a good demo of Datazen in the session, and I highly recommend watching it. It will be included with SQL Server 2016.
  • Datazen has KPIs. It also has “sub-KPIs”. I’m not sure about you, but that sounds a lot like a scorecard to me. This may sound the eventual (see the SharePoint section) death knell for PerformancePoint, given that that’s about all that it uniquely provides to the BI stack.
  • Tabular models in SSAS (and presumably PowerPivot) will support many-many relationships and a host of other new features.
  • Tabular models in SSAS and PowerPivot will have time intelligence built in. No longer will separate time intelligence tables be required. It’s an open question however as to how extensible they will be and when.
  • SharePoint will allow browser editing on PowerPivot embedded workbooks. Currently, you need to launch Excel to edit a PowerPivot embedded workbook.

Office 365 Groups

I attended the roadmap on Office 365 Groups:

(video unavailable as of posting – should be shortly)

During this session, the light really went on for me. Groups was (were? Not sure about the grammar on this…it’s a name) introduced last year and appeared to be a glorified distribution list with Sharepoint artifacts. However, its about to become the center of the Office 365 collaborative experience. It ties together Azure Active Directory objects, a SharePoint site collection, One Note, Skype, and OneDrive into a single cohesive, non-customizable experience. It currently uses Exchange exclusively for social conversations, but full Yammer integration is promised. No date was given for the integration, but my guess is that the target is early 2016.

The current User interface is limited – too limited for my own use at the moment, but during the demonstration, a rather useful interface was shown that is coming soon. You can access groups presently through the Outlook web client in Office 365. I’m running Office 2016 preview on my laptop, and there is a very nice interface contained there. There was chatter, particularly in the Yammer community about confusion as to what tool should be used when, but I think that the coming deep integration of Yammer into Groups will render this point moot’

The next UI, demonstrated in the above session looks really good, and offers a lot of benefits. There is also a mobile app coming very shortly for, you guessed it, iOS and Windows Universal, then Android.

One unanswered question from the show is whether Groups would be available on-premises.

Power BI

Power BI content was sort of sprinkled throughout the conference, without specific focus. There was a session on the new DAX features available in Power BI Designer that is worth a watch from a modeling perspective:

One talk that really impressed me was by Lukasz Pawlowski and Josh Caplan entitled Power BI for Developers:

They cover content packs are mentioned, real time analytics, and an in depth analysis of the “how old” app that went viral during Build.

It was also announced in the SQL BI session that SSRS will in fact be included in Power BI shortly, although little detail was provided. Finally, for development, the best place to get started is http://dev.powerbi.com.

It should also be noted that Power BI was at the center of almost any analytics discussion during the conference. This is by no means a little side project.

Delve/Office Graph

Delve is a newer product in Office 365 that provides insights around what content is relevant in an organization, and how people interact with it. It’s available directly from the app launcher in Office 365, and recently, user profiles have moved to the Delve application. It’s powered by the Office Graph, which in essence an advanced index that contains content from Exchange and SharePoint, and will very shortly, be extensible for multiple content types. The roadmap session for Delve/Graph is available here:

During the session, it was stated that “Delve is the evolution of Enterprise Search”. Given that all of the work on Delve and the Graph is coming from Oslo and the former team from FAST search, this just makes sense. One of the major announcements around SharePoint 2016 was that SharePoint 2016 content can be crawled by the Office Graph to provide both search results and Delve results in Office Graph. The reverse will also be true in that the on-premises crawler will be able to index Office 365 content for search results, but Delve and the Graph will remain in Office 365. The surprise here was that later this year, it will be possible to do the same thing with Sharepoint 2013 through a coming enhancement.

Much of this Graph goodness can also now be accessed through the new Office 365 Universal API:

tyGraph

tyGraph is our product that provides advanced analytics for Yammer. It had something of a coming out party at Ignite, and while we didn’t have a booth or any launch sessions, we were fortunate enough to have several folks, customers and thought leaders present talks that at least in part featured tyGraph. If you’re interested in analytics for your Yammer network, I recommend that you watch some or all of these sessions:

Enterprise Social, from “Ooh, Shiny” to Business Success – Melanie Hohertz, Cargill

The Microsoft Enterprise Social Journey: How We Did It – Chris Slemp, Microsoft

Gain Organizational Insights with Yammer Data Mining and Analytics – Steve Nguyen, Microsoft and Tammy Young Heck, EY

Yammer Mining: Dig in and “Listen” to What Your Big *Social* Data Is Saying – Richard diZerega, Microsoft

4 Comments

PerformancePoint Lives!

During the Microsoft Ignite conference in Chicago, Bill Baer delivered the first public details on the inner workings of SharePoint 2016. He was discussing how non-Office 365 services were implemented in SharePoint 2016, and made the following statement:

For capabilities such as PerformancePoint Services, we have brought those services forward into SharePoint 2016 by back porting them into the product itself.”

This is quite significant as it represents the first time that any statement has been made about PerformancePoint by someone at Microsoft in quite some time. There has been a fair bit of speculation as to whether the product would even be included in SharePoint 2016, for example here, here, and here. This statement should clear up any confusion for those heavily invested in PerformancePoint as to whether they will be supported into the new release. You can see it for yourself below, and if you can, I recommend watching the entire session – Bill did a great job. The PerformancePoint statement occurs at about 9:50.

However, just because it will be there, doesn’t mean that it has a bright shiny future. I suspect that it is being included for compatibility reasons only so that those with PerformancePoint can move forward to SharePoint 2016. In terms of new features, I believe that PerformancePoint, much like InfoPath is a dead end. Time will of course tell.

4 Comments

SQL Server Data Tools, BIDS, Visual Studio – What Do You Need?

The Business Intelligence design components of SQL Server have an identity crisis.

What I’m talking about are the tools that are used by designers to create BI objects in the SQL Server Business Intelligence stack, specifically Analysis Services (SSIS) OLAP cubes and tabular models, Integration Services (SSIS) ETL packages, and Reporting Services (SSRS) reports. These tools have always been bundled into a single product as part of the SQL Server distribution. The original incarnation of the tools was called Business Intelligence Development Studio, or just BIDS. It was originally introduced with SQL Server 2005, and was included on the SQL distribution media as an optional install component.

BIDS was a distribution of the Visual Studio shell, and a set of bundled project types for creating cubes, SSIS packages and SSRS reports. The original version was based on Visual Studio 2005, and subsequent releases of SQL Server stayed in step with more recent versions of Visual Studio. The projects were tied tightly to the Visual studio versions. If, for example you had Visual Studio 2010 already set up, installing BIDS would still install a different Visual Studio to support the projects. BIDS maintained its own identity. Installing BIDS is straightforward, you simply run setup from the SQL Server media, and select “Business Intelligence Development Studio”.

When SQL Server 2012 was released, a change was introduced. In addition to the projects required for tabular models in SSAS being added to the tools, they also received a new name. Henceforth they were to be known as SQL Server Data Tools (SSDT). As expected, the requisite level of Visual Studio was incremented and SSDT was based on Visual Studio 2010. The installation experience didn’t change fundamentally, the installation option just took on the new name.

So far so good. Products get renamed all of the time. Simply substitute SSDT for BIDS, and everything is pretty much as it once was.

SQL Server 2014 is the latest SQL Server version, and it introduced another major change. Data tools is no longer available from the SQL Server installation media.

This is where it gets very confusing. It was always possible to download BIDS or SSDT directly from Microsoft. These products don’t require a license to deploy and use, so they were freely available. This is also true with SQL 2014, but now, a download is the only way to get the tools. That’s simple enough. However, if you search for SQL Server Data Tools and download the version for either Visual Studio 2012 or Visual Studio 2013, you won’t find the projects that you were looking for. You’ll instead find projects for deploying databases and DACPACs. So what’s going on here?

As it turns out, a separate Microsoft team put out a separate set of VS project templates in the SQL Server 2012 timeframe that were also called SQL Server Data tools. Apparently there are a finite quantity of names.

The product that we originally knew as BIDS, and then SSDT was renamed one again for the SQL Server 2014 wave of products. Well, only sort of renamed. It’s now called SQL Server Data Tools – BI. Apparently this was intended to avoid confusion…. SSDT-BI is available for either Visual Studio 2012 or Visual Studio 2013. If you don’t already have a Visual Studio installed, it will install a VS shell for you.

The summary of all this is to say that if you want to build BI projects for SQL Server, you’ll need to have the right tooling for your target server, and that tooling is as follows:

Server Toolset Name Location
SQL Server 2008 R2 and below Business Intelligence Development Studio On SQL Media
SQL Server 2012 and 2012 SP1 SQL Server Data Tools On SQL Media
SQL Server 2014 SQL Server Data Tools – BI Download for Visual Studio 2012
Download for Visual Studio 2013

The complete current Data Tools product set is laid out and can be downloaded from here. The Data Tools team blog is here.

I hope that this helps to clear up some confusion. I can’t wait to see what they have in store for us in V.Next….

25 Comments

License Requirements for SQL Server Reporting Services in SharePoint Integrated Mode

As I am keen to point out every opportunity I get, when installing SQL Server Reporting Services in SharePoint Integrated mode, it is important to ensure that it is installed on a SharePoint server, and not on a SQL Server. It’s a bit counter-intuitive because the installation files are on the SQL Server media, and not the Sharepoint media. This causes confusion in a number of areas, but one question that I hear a lot is in the realm of licensing.

Technically, using any SQL Server component on any server requires a full SQL Server license for that component for each server. Running SQL Server Analysis Services and SQL Server Database on two different machines requires two different licenses. Seen that way, putting the SSRS Service on a SharePoint server would require a second license of SQL Server, which can be an expensive proposition. It also doesn’t make much sense, because it promotes bad design. Luckily, the SSRS service application is specifically exempted from additional licensing requirements. The following is taken directly from the Microsoft SQL Server 2012 SP1 license document. Section 2.5 states:

2.5    Running Instances of the Additional Software.

You may run or otherwise use any number of instances of additional software listed below in physical or virtual operating system environments on any number of devices. You may use additional software only with the server software directly, or indirectly through other additional software.

  • Business Intelligence Development Studio
  • Client Tools Backward Compatibility
  • Client Tools Connectivity
  • Client Tools SDK
  • Data Quality Client
  • Data Quality Services
  • Distributed Replay Client
  • Distributed Replay Controller
  • Management Tools – Basic
  • Management Tools – Complete
  • Reporting Services – SharePoint
  • Reporting Services Add-in for SharePoint Products
  • Master Data Services
  • Sync Framework
  • SQL Client Connectivity SDK
  • SQL Server 2012 Books Online

 

Notice that little bullet point “Reporting Services – SharePoint”? That’s the service application. Put simply, this means that in order to use SSRS in SharePoint Integrated mode, you’ll need to have at least one licensed SQL server, but you don’t need to run it on the SQL machine. In fact, according to the line “any number of instances” you can run it on as many SharePoint servers as you wish to take advantage of load balancing without incurring any additional licensing cost for SQL Server.

6 Comments

Schedule Data Refresh for SSAS Connected Excel Workbooks with PowerPivot for SharePoint

Using Excel Services, SharePoint users have been able to share workbooks that are connected to back end data since SharePoint 2007. Typically, the connection is made to SQL Server, or to Analysis services although a wide variety of sources are available. It’s also possible to publish individual components from these workbooks anywhere within the site collection through the Excel Web Access web part. Users can navigate to a dashboard page that contains all sorts of elements including an Excel chart that is connected to back end data. Well, to be precise, it was connected to back end data, the last time the workbook was saved. The workbook itself can be refreshed, but only manually.

When you open an Excel workbook in a browser through Excel services, by default, you’ll see the visualizations and any stored data in precisely the way that the workbook was when it was last saved. If you need to see more up to date data, you can select “Refresh Connections”. If (and sometimes that’s a big if) the server and connections are set up properly, the server will fetch updated data and update the workbook.

 This works well enough, but the problem is that when you, or anyone else opens the workbook again, they’ll still see the old version of the workbook, and will need to manually refresh the date again. In addition, any visualizations published elsewhere on a dashboard will also continue to show old data unless manually refreshed. If the amount of data is significant, this poses a serious performance issue to the server(s). There’s also a significant usability impact in that it’s a pretty big ask of an end user to have them constantly hitting a refresh button.

To get around this issue, one option is to set the refresh options in the data connections of the workbook. Excel Services respects these options. There are two settings that we need to be aware of, periodic refresh, and refresh on open. Connection properties can be accessed within the Excel client by selecting the Data tab, choosing Connections, then highlighting the connection in question and selecting Properties.

Periodic refresh will allow the workbook to be automatically refreshed in the background while it is opened in the browser. This can be useful when the source data is changing frequently. Refresh on opening will have the greatest impact in our scenario, as it will automatically refresh the data in the workbook whenever the file is opened. This will also work with published objects (Excel Web Access web parts) – every time that the web part is opened, the data will be automatically refreshed. This solves the usability problem above because the user no longer needs to manually update the data. However, it does not affect the server load problem.

Due to the fact that the data and visualizations retain the state that they had when the workbook was last saved, it also affects search. When the search indexer runs, it will only index the data that is saved in the workbook. It has no means of refreshing the data. Finally, in addition to the load imposed on the servers by constant refreshes, if the quantity of data being refreshed is large, users can experience significant lags when loading the file. This obviously introduces another usability option. While the refresh options in Excel are helpful, they don’t fully solve the problem. What is needed is a way to automatically open the file for editing, refresh the data, and resave it to SharePoint.

If you have ever used Power Pivot for SharePoint, you know that it can do exactly that. Power Pivot for SharePoint contains two primary elements – a specialized instance of SQL Server Analysis Services that allows users to interact with workbooks that contain embedded PowerPivot models, and a SharePoint service application that among other things, keeps those embedded models refreshed. Using the PowerPivot Gallery (enabled when PowerPivot for SharePoint is installed), you can configure a workbook’s refresh options by clicking on the icon in the Gallery view, or by selecting “Manage PowerPivot Data Refresh” in the simple All Documents view.

 Data Refresh options in PowerPivot Gallery View

 Data Refresh options in All Documents View

Once configured, the PowerPivot for SharePoint Service will refresh the data model in the workbook on a periodic basis (no more than once per day). The service essentially opens the workbook in edit mode, refreshes all of the data connections, and saves the workbook back to the library. If versioning is enabled, it will be saved as a new version. Unfortunately, if you’re not using a PowerPivot data model, the options are unavailable. In Gallery view, the icons are simply unavailable, and while the option is available in the All Documents view, selecting it results in an error.

On the surface, it would seem that using workbooks with PowerPivot is the only option for keeping large volumes of back-end data up to date in Excel visualizations. However, there is a small loophole that you can take advantage of.

The refresh function in PowerPivot for SharePoint refreshes all of the connections in a workbook. While this option is unavailable if the workbook has no embedded PowerPivot model, when it does, it refreshes ALL of the data connections in the workbook, whether they connect to a model, a back end SSAS server, SQL server or whatever. So therefore, if you want to keep your connected data refreshed, the solution is to add a dummy PowerPivot model to your workbook.

Simply open up the PowerPivot window, import some small amount of data from an external source, and save it. Once saved, the PowerPivot refresh options will appear, and you’ll be able to schedule data refresh for your workbook. You can even deselect the refresh of the source data for your dummy model, and the other connections will work just fine.

Once your workbooks are being updated automatically, your users will be presented with up-to date data on load with no delays, all dashboard visualizations will be up to date and quick to render, and the visible data will be picked up by your search crawler. All will be well with the world.

3 Comments