Tag: SharePoint 2013

Whither Power Pivot for SharePoint? A Comparison With Power BI

Published December 2, 2013

Power BI is a hot topic within the Microsoft Business Intelligence community. Since it was announced last July at the Worldwide Partner Conference in Houston, it’s pretty well been the only thing that has gotten discussed with regard to Microsoft BI. There are good reasons for this, the addition of a mobile BI story, its ease of use, and it’s compelling new features (Power Q&A!) make it the shiny new toy. I’ve certainly been paying a great deal of attention to it, but what about the more traditional products, in particular Power Pivot for SharePoint?

One of the big questions around Power BI is whether or not there will be an on premises version of Power BI. Whenever asked, Microsoft responds with “Power BI will initially be available through Office 365”. This answer causes concern to those with requirements that can not or may not be met by a cloud based solution. Many worry that Microsoft’s move into devices and services are leaving on premises installations behind. I’ve been of the opinion that this is evidence of a “cloud first” release strategy, as opposed to a “cloud only” release strategy. Recent statements by Microsoft officials would tend to confirm this, but the question should be asked, does it even make sense to bring Power BI on premises?

A little explanation is in order. The collection of tools that is Power BI is centred around the x-Velocity data model that is part of Excel 2013, and available through Power Pivot in Excel 2010. All of the client based design tools can be used with Excel without the need for a Power BI license. With Power Pivot for SharePoint, it has been possible to interact with these data models through a browser for several years already, and with the 2013 updates to Office 365, it is even possible to interact with these models in the cloud. What has been missing from the Office 365 BI story has been an automatic way to keep on premises data refreshed, and the ability to work with large models.

While these two capabilities have been available on-prem for years through Power Pivot for SharePoint, they are only coming available to Office 365 now with Power BI. It doesn’t really make sense to bring these capabilities on-prem when they already exist. However, complicating this picture is the host of new capabilities that are being introduced by Power BI. In many ways, it’s a “leapfrog” product, filling in gaps in some areas, while moving forward in others. A comparison of the two products can be seen below.

Let’s walk through these features. Obviously both products work with the embedded x-Velocity data models. Power Pivot for SharePoint from SQL Server 2012 SP1 can render Power View in Excel, as can Power BI. Power View has some interesting variations however. Through the Power View that is available via Power Pivot gallery, live Power View reports can be exported to PowerPoint decks. This feature is not available through Excel Power View, or through Power BI. On the flip side, on-prem Power View reports (both types) use Silverlight for rendering, whereas Power BI will allow both Silverlight and HTML 5 renderings (confused yet?).

Both Power Pivot for SharePoint and Power BI are powered by an Analysis Services engine. The Power Pivot gallery available on-prem provides for the ability to connect to that engine through Excel with an Analysis Services connection. This makes the embedded model created in one workbook available to Excel clients as what appears to be a data cube. This is not available through Power BI, although the OData publishing features fills that gap somewhat.

The ability to refresh the data in the embedded model is critical and is to my mind, the most important feature in Power BI. However, at best, this brings it to parity with Power Pivot for SharePoint. For the moment it supports only SQL Server on-prem data sources where Power Pivot for SharePoint supports all Power Pivot data sources for refresh. As of this writing (December 2013) neither product supports the refresh of Power Query data sources, but this has been promised for Power BI “soon”. No announcement has yet been made as to the refresh of Power Query data sources on-prem.

The default maximum file size for SharePoint 2013 is 200 MB, and the default maximum workbook size for Excel Services is 10 MB. These values can be changed on prem, making the maximum possible size for a data model equal to the maximum possible file size in SharePoint – 2 GB. This equates to the maximum file size in Office 365 as well, but that 10 MB Excel Services limit can’t be changed in Office 365. Power BI supports model sizes up to 250 MB by removing the model portion from the workbook, and housing it in an Analysis Services instance, allowing the workbook to remain within the 10 MB limit. It’s wonderful to be able to move beyond the 10 MB limit that we’ve had, but it’s not without its limits.

Both products have a thumbnail gallery, but the one available through Power BI sites is arguably more sophisticated, and it doesn’t rely on Silverlight for rendering. The rest of the feature set outlined above is all in Power BI’s favour. Power Pivot SharePoint will optimize workbooks (move the data model into Analysis Services) on first interaction, but Power BI can do that ahead of time, minimizing user inconvenience. The rest of the feature set, OData feeds for on-prem data, Power Q&A, and mobile clients are only available with Power BI.

The fact that Power BI for SharePoint on premises has not been announced isn’t as disappointing as it may seem. Parts of it, the Data Management Gateway in particular, aren’t even necessary in an on-prem scenario. This is pure speculation on my part, but if I had to bet, I would expect to see the relevant features from Power BI (Power Q&A, OData publishing) put into Power Pivot for SharePoint in a future release of that product. It also wouldn’t surprise me to see it renamed to Power BI for SharePoint. As to when this could happen I have absolutely no idea, but we should keep in mind that this is a product that ships with SQL Server, not with SharePoint, and I haven’t heard of anything like this in the previews of SQL Server 2014.

3 Comments

How to display Specific Content to Anonymous or Authenticated Users in SharePoint

Published July 6, 2013

A long time ago I wrote about the usefulness of the SPSecurityTrimmedControl in selectively displaying content based on a user’s permission level. It supports a myriad of different permission options, and my friend Marc Anderson has an excellent post in which he outlines all of the possible permission levels that can be used with this control by manipulating the PermissionsString attribute.

What is less well known about this control is that it can also be used in an application that supports anonymous access to selectively display content based on the user’s logged in state. This might be to display a custom log in button or link that should only be displayed when a user has not logged in. The way that this is accomplished is through the AuthenticationRestrictons attribute. There are three possible values:

AllUsers
AnonymousUsersOnly
AuthenticatedUsersOnly

I have no idea why the AllUsers value exists. It’s not much of a restriction. The other two values are pretty well named, so I won’t bother explaining them.

A pretty typical usage scenario might be to build a page layout that will display a content field for all users, another field exclusively for anonymous users, and another for logged in users. The exclusive fields would need to be created and added to a content type prior to the creation of the layout. A simple example of this might appear as follows:

<PublishingWebControls:RichHtmlField id="PageContent" FieldName="PublishingPageContent" DisableInputFieldLabel="true" runat="server"/>
<SharePoint:SPSecurityTrimmedControl runat="server" ID="spAuthenticated" AuthenticationRestrictions="">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAuthenticated" runat="server" />
</SharePoint:SPSecurityTrimmedControl>
<SharePoint:SPSecurityTrimmedControl runat="server" ID="spUnAuthenticated" AuthenticationRestrictions="AnonymousUsersOnly">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAnonymous" runat="server" />
</SharePoint:SPSecurityTrimmedControl>

In this example, the PublishingPageContent field is displayed on the page first, and depending on the logged in state, one of the remaining fields will be displayed. Of course, in order to use the control, the WebControls directive must first be added to the page:

<%@ Register Tagprefix="PublishingWebControls" Namespace="Microsoft.SharePoint.Publishing.WebControls" Assembly="Microsoft.SharePoint.Publishing, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

The above example is for SharePoint 2010, but the namespace also exists in 2013.

This is simple enough, but there is a practical problem with this approach. How do we edit the content for anonymous users on the page? Although the control is there, editors are going to be authenticated users, and therefore the content will be completely hidden from them.

To do this, we can treat the Anonymous filed like other page metadata, and include it (also) in an edit mode panel, without the spSecurityTrimmedControl.

<PublishingWebControls:EditModePanel class="ewiki-margin" runat="server">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAnonymous" runat="server" />
</PublishingWebControls:EditModePanel>

The contents of the edit mode panel are only displayed when the page is in edit mode, so authors will be able to edit anonymous content and authenticated content in one step.

This control isn’t limited to publishing scenarios, but does require the Publishing namespace, and therefore requires at least SharePoint Standard license.

1 Comment

How to display Specific Content to Anonymous or Authenticated Users in SharePoint

Published March 26, 2013

AllUsers
AnonymousUsersOnly
AuthenticatedUsersOnly

I have no idea why the AllUsers value exists. It’s not much of a restriction. The other two values are pretty well named, so I won’t bother explaining them.

<PublishingWebControls:RichHtmlField
id=”PageContent”
FieldName=”PublishingPageContent”
DisableInputFieldLabel=”true”
runat=”server”/>

<SharePoint:SPSecurityTrimmedControl
runat=”server”
ID=”spAuthenticated”
AuthenticationRestrictions=””>

<PublishingWebControls:RichHtmlField
FieldName=”PageContentAuthenticated”
runat=”server”
/>

</SharePoint:SPSecurityTrimmedControl>

<SharePoint:SPSecurityTrimmedControl
runat=”server”
ID=”spUnAuthenticated”
AuthenticationRestrictions=”AnonymousUsersOnly”>

<PublishingWebControls:RichHtmlField
FieldName=”PageContentAnonymous”
runat=”server”
/>

</SharePoint:SPSecurityTrimmedControl>

<%@ Register Tagprefix=”PublishingWebControls” Namespace=”Microsoft.SharePoint.Publishing.WebControls” Assembly=”Microsoft.SharePoint.Publishing, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>

The above example is for SharePoint 2010, but the namespace also exists in 2013.

To do this, we can treat the Anonymous filed like other page metadata, and include it (also) in an edit mode panel, without the spSecurityTrimmedControl.

<PublishingWebControls:EditModePanel
class=”ewiki-margin”
runat=”server”>

<PublishingWebControls:RichHtmlField
FieldName=”PageContentAnonymous”
runat=”server”
/>

</PublishingWebControls:EditModePanel>

The contents of the edit mode panel are only displayed when the page is in edit mode, so authors will be able to edit anonymous content and authenticated content in one step.

This control isn’t limited to publishing scenarios, but does require the Publishing namespace, and therefore requires at least SharePoint Standard license.

1 Comment

Using Per-User Identity with Reporting Services and SharePoint

Published September 3, 2012

Almost anyone that has dealt with business intelligence in the Microsoft space is aware of the double hop problem. Simply put, when a user wants a server to execute a process on their behalf, that process will have a different identity, if any at all from the originating user, and therefore user focused operations such as per-user security trimming can’t be performed. The traditional solution to this problem in the Windows world has been to use Kerberos authentication, which allows server to pass on secure user tokens to other servers on behalf of originating users.

Unfortunately, Kerberos requires additional infrastructure, and is not adopted in all organizations. Smaller to mid sized organizations tend to stay away from adopting it, primarily due to its perceived complexity, which leaves them with the double hop problem. In the long term, claims based authentication promises to solve this problem, but it is still in its infancy, and is not adopted at all yet in the SQL Server suite of products, which is where double hop pain is felt most keenly.

The new BISM tabular model with Power View works around this problem by taking advantage of the EFFECTIVEUSERNAME property available in Analysis Services (I’ll be writing more about that later), as does Excel Services and PerformancePoint Services in SharePoint 2013. With EFFECTIVEUSERNAME, the server makes its connection with the data source using its service credentials, but then every query identifies the originating user, and those queries are executed in the context of that user. It’s a clever workaround, and takes care of much of the BI stack, but what about Reporting Services and relational data?

As it turns out, Reporting Services has been able to do this sort of thing for a long time. It’s just been rather cleverly concealed or misunderstood. SQL Server itself has a function, SetUser() that can be used by someone with sufficient privileges to impersonate another user. Originally implemented for testing purposes, Reporting Services takes advantage of it in much the same way that EFFECTIVEUSERNAME works in Analysis Services. The place to turn on this function is within a data source editor.

Using SharePoint Integrated mode, there are 3 main tools that can be used to define a data source. A data source can be created from any library that is configured to use the Report Data Source content type. In addition, SQL Server Report Builder can be used to edit a data source, or to create an embedded data source in a Report. Finally, Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (depending on your version of SQL) can be used to create shared data sources. And the best news is, the interfaces are all different!

Setting Connection credentials in SQL Server Data Tools

Setting Connection credentials in SharePoint 2013

Setting Connection credentials in Report Builder

The option that we’re interested in, in all three cases is “Use this user name and password” (stored credentials). In the case of Data Tools or BIDS, the impersonation option isn’t available. Normally what would happen is that the connection would be deployed to the server, and then edited afterwards. We can therefore ignore this interface. However, examine the description of the impersonation option in both SharePoint and Report Builder. Although worded completely differently, they mean exactly the same thing. And in my opinion, Report Builder has it right. In fact it’s a stretch to say that the SharePoint screen isn’t dead wrong.

The SharePoint form can only be considered correct if the word “this” applies to the currently logged in user. To my mind, when I read that option, I am led to believe that it will be set to the account that is being used to make the connection (the stored credentials), when it is in fact the opposite. The setting in Report Builder is far clearer and more accurate.

When the option is checked, the SetUser() SQL function will be used in advance of any queries performed by the report, and the value will be set to the currently logged in user, NOT to the credentials specified under “Use this name and password”. The credentials specified here are used to make the connection to the SQL server, and because of the security restrictions of SetUser(), MUST have at least the dbOwner role on the queried database, or the SYSADMIN fixed role on the server. My recommendation is the latter if possible. This requirement also makes it very important to restrict the editing of the Data Source.

I imagine that most of the confusion around this feature comes from the fact that in most cases, when you are using impersonation, you are saying “I want to execute this function as this account”. However, in this case, with stored credentials, it’s always the stored credential executing the function, and what we’re asking it to do is to impersonate the currently logged in user. It’s sort of backwards to the way we normally think about impersonation. It’s not immediately intuitive as to why a logged in user would need to impersonate themselves, but that’s basically what’s happening.

Using this option therefore allows you to utilize per user permissions through Reporting Services on SharePoint without having to use Kerberos. This is a good thing, but you should be aware that in many cases, using a proxy account (by not selecting the impersonate option) may be a better option. There are several drawbacks to using per-user authentication. Impersonation should only be used when it is absolutely required.

If you open up the report processing screen for a report that uses a data source defined for impersonation, and select the “used cached data” option, you will receive the warning “This report can not be cached because one or more of the data sources credentials are not stored”.

The reality is that although the credentials are in fact stored, the report could be different for different users, and caching depends on a consistent result. Therefore, to prevent confusion, it’s not allowed. If the report is particularly large, this removes an important performance optimization technique.

From the same screen, it’s also made very clear that snapshotting is not an option when impersonation is being used, from the error “This report can not run from a snapshot because one or more of the data sources credentials are not stored”.

The reasons are the same as for caching, and the implication is that snapshots cannot be created, preventing us from storing a version history for the reports.

Finally, subscriptions are also rendered useless through the use of impersonation. An attempt to create a subscription to a report using an impersonated data connection results in the following error:

Per-user identities also require management on the SQL server side. Because we are impersonating the currently logged in user, that user must be managed from within SQL itself, whereas a proxy account only requires that account to have access to the data in question.

To summarize, Kerberos is not required to do per user queries in Reporting Services for relational data. The SetUser() function in SQL Server combined with the Execution Context option in data connections allow this to be performed quite effectively. However, just because it can be done, doesn’t mean that it should be. In many cases a simple proxy account can do the job, and should likely be allowed to, unless the requirements absolutely demand otherwise.

8 Comments

How To Configure the Nintex Workflow Preview on Office 365

Published August 7, 2012

Anyone that has worked with my company, UnlimitedViz, knows that we’re unabashed fans of Nintex Workflow. As far as I’m concerned, it’s one of the most cost effective pieces of software I’ve ever come across. Projects that would require days or even weeks of consulting using the out of the box features in SharePoint can be accomplished in mere hours with Nintex Workflow, and what’s more, they can be accomplished by power users, not developers.

Another exceptional aspect of Nintex workflow is that is doesn’t replace the out of the box SharePoint workflow engine, it simply enhances it, through a browser based graphical designer/viewer, and multiple custom actions. This design approach really showed its value on several SharePoint/Nintex 2007 to SharePoint/Nintex 2010 upgrades that we’ve performed. We were able to upgrade entire farms and maintain in-flight workflows.

Unfortunately, as I wrote about previously, once we moved our organizational assets to Office 365, we were no longer able to use Nintex Workflow ourselves, because NWF is an on-premise solution. However, with a little redevelopment, changes in the SharePoint development model (for a reference, see Jeremy Thake’s excellent summary) open the doors for third party code to be used with Office 365 sites in addition to on-premise sites.

Happily, these changes have not gone unnoticed by the folks at Nintex, and they are one of the first vendors out of the gate with a solution in the SharePoint store. It’s just a preview of what will be coming very shortly after the release of SharePoint 2013, which is important, because Office 365 sites will be upgraded very shortly after release.

Walkthrough

It should go without saying, but in order to try the Workflow Preview, you’ll need to be using an on premises SharePoint farm that has been enabled for SharePoint 2013 workflows, or using the Office 365 preview. In our case, we’re using the Office 365 preview.

Firstly, you’ll need to add the app to the Office 365 App Catalogue. To do this, go to the Office 365 Admin screen and click on apps.

Next, click on Purchase Apps. You’ll see a yellow alert bar indicating that you don’t have permission to add apps, but as far as I can tell, it’s a bug. Click on the “Nintex Workflow Platform Preview” icon.

Once that’s done, you’ll be presented with a summary screen. When ready, click the “Add It” button.

You’ll then be presented with a summary screen indicating that you now have a site license.

At this point, you’ll need to navigate back to the site that will be using the workflows. Now that the app is available, you won’t be turning on features for a site anymore, you’ll be adding the app to any site that you want to use the workflow preview with. In this case, we’ll be adding the preview app to a basic team site. Assuming that you have the appropriate permissions, you’ll start by adding an app by clicking on Site Contents – Add an App.

The Workflow Preview should appear in the “Apps you can add” section.

Click it, and you’ll be asked if you trust it – of course you do! Click the Trust It button and in a moment the app will be added.

What’s actually happening behind the scenes is that an app subsite is being provisioned that will contain all of the assets needed for the app to work. This is done to keep the app itself isolated from the content of the site. It may take a minute, so have patience.

Once it’s ready, you’ll see “Nintex Workflow Platform Preview” comingled in with the other site lists and apps. Clicking on it takes you into the workflow designer where you can design a site workflow.

Drag and drop actions to your heart’s content, and when ready, click Publish from the ribbon, give it a name, and save it. You can now run your Nintex site workflow in Office 365. You should note that this is a small subset of actions intended for preview only, but the Nintex Live actions are available, so you can translate items, use current exchange rates, etc.

If you want to design a list workflow, simply navigate to a library or list within the site, and click on the appropriate list or library tab. Over on the right near the standard workflow button, you’ll find a “Nintex Workflow” button.

Clicking on it will once again open the designer. If you examine the URL, you’ll notice that what it’s really doing is opening the isolated app subsite and linking back to the list where the workflow will be saved.

In this case, we’ll build a quick translation workflow that will take content from the English field, use the Bing Translation action available through Nintex Live, and then write the value to the French field.

The workflow can be set to run on item creation, and then published. So that adding a new item:

Then saving it, after a few moments results in a translated value appearing:

This is early days, and we’re looking at previews on previews, but I’m quite encouraged with this. Not only will I be able to use Nintex workflows for our own Office 365 site shortly after launch, but in my opinion, this preview validates the new application model. Yes there are currently bumps, understandable, given that this is a preview on a preview, but this is functional and responsive. The folks at Nintex have been doing their homework, and appear to be hitting the ground running into the emerging Office 365 market.

1 Comment