Tag: Excel Services

Power BI – What Is It, And Why Does It Matter?

Published July 18, 2013

Power BI for Office365 was first shown to the public last week at the Microsoft Worldwide Partner Conference. Power BI is in my opinion, the most significance advance that Microsoft has made in the area of Business Intelligence in the cloud, and it significantly advances their offering in personal Business Intelligence. I’ve been evangelizing Microsoft’s recent personal and team BI features for some time now, and this announcement builds on that momentum. The demonstration raised a lot of eyebrows, and showed off some of the great strides that Microsoft has made in this space. One little gem in there that was easy to miss was the fact that we got our first glimpse at the HTML 5 version of PowerView – at least the renderer. It also raised a number of questions as to how it works and what will be necessary to get it working optimally. I had the opportunity to speak with a project manager and managed to answer a few of these questions. This post is attempt to explain what this product is all about.

Overview

Firstly, if you haven’t already seen it, you need to see the demonstration that was done by Amir Netz during the Day 1 keynote. It’s really one of the most compelling demos that I’ve ever seen. I include it below.

Amir Netz Announces Power BI at WPC 2013

Microsoft has provided a web page to describe the product and let you sign up for a preview. It also contains links to download some of the constituent parts, but it doesn’t necessarily do a great job of explaining exactly what you’re signing up for, or what these downloads are for.

To start with, Power BI requires Excel 2013. This is because all of the constituent components rely on the xVelocity (PowerPivot) engine that is included with Excel 2013. Make no mistake, as far as Microsoft is concerned, the future of multi-dimensional analysis is with the xVelocity in-memory engine. This engine isn’t new – it’s what powers Power Pivot (note the space between the words Power and Pivot… that’s also new).

The “Power” branding I think is encouraging as well. The business intelligence offerings from marketing have been confusing to say the least. Check out this handy “quick reference guide” if you don’t quite understand what I mean. There’s a very large assortment of tools that come from two different product groups (even after the reorg) and it can be difficult and confusing to navigate. Marketing hasn’t made this any easier in the past, but the consistent “Power” prefix I think goes some way to remedying this.

Power BI itself is the culmination of several different projects – Data Explorer, GeoFlow, BI Sites, and work in the mobility space. The first two of these have been in the public for a little while now, and were given their release names this week – Power Query, and Power Maps respectively. In addition, Power Query reached its General Availability milestone, making it acceptable for production use. The BI Sites project has been a very well-kept secret, and last week was the first time that it has been seen in public. Finally apps were shown for both Windows 8 and iOS that should go a long way to address the deficiencies in the mobile space. Unfortunately, nothing was said about Android or Windows Phone, but I have to think that they’re not far off.

Given that there are several components to Power BI, I think that it’s worth outlining each one and how it will be used. To start with, Power Query can be used to discover, acquire and transform source data and load it into an xVelocity based data model in Excel. It can also register the data connections through a “data steward” service that will reside in Office 365 – this will allow for tenant wide discoverability of the data. Power Pivot will then be used as necessary to modify the model, create calculated measures, format columns, etc. Data analysis can then be performed in Excel, using pivot tables, charts and/or Power View. In addition Power Map can be used to visualize geospatial data on a rotatable, zoomable 3D map. Once ready, the workbook will be published to an Office 365 document library, where a BI site will find it. BI sites can then be used to perform natural (English) language queries on the data, add to the mobile applications, and to schedule data refreshes whether the data is public or private. Finally, the mobile apps can be used to consume the reports from the BI Site.

Power Query

Power Query went into general release last week along with the Power BI announcement, and you can download it here. It has been available in preview form as project “Data Explorer”. It’s an add in to Excel 2010 or 2013 that gives it Extract, Transform and Load (ETL) capabilities. I like to call it the personal version of SQL Server Integration Services, in the same way that Power Pivot is the personal version of Analysis Services, and Power View (to a lesser extent) is the personal version of Reporting Services.

As you might expect, it can pull data from a wide variety of sources. Web pages can be parsed, and tables scraped to provide tabular data. The usual suspects are all supported (SQL Server, Oracle DB2, etc.), as well as any OData feed, Azure Data Market, SharePoint lists, Hadoop and HDInsight, Active Directory, and Facebook. One of the more interesting data sources is a folder (not a file). If you point Power Query at a folder, it will merge the data from all of the files in that folder into a single result. Refreshes will pick up any new files as new data. I can see some real uses here for log files. Yes, the files do all need to be the same schema.

The killer feature for discovery here is the online search. In preview, online search gave you access to all of the Wikipedia data that has been expressed in tables. Now however, you not only have access to more public data sources, but you can publish your own data sources (on premises or otherwise) to a catalogue that resides in Office 365. Below is a screen shot from Amir’s demo.

Clicking on Online Search in the ribbon brings up the window on the right, where you can enter a search term, in this case “Microsoft Azure Datacenters”. You are then presented with a list of candidates, both public and private. Hovering over one of them will give you a preview of the data to be returned, and when satisfied, you can select Add to Worksheet, or if you would like to transform the data click Filter and shape, where you can modify data types, flatten relational data, merge or split fields, etc. Despite the “Add to Worksheet” button name, the data can be loaded into the worksheet itself, the back end data model, or both. This distinction is very important when working with very large data sets ( i.e. > 1million rows).

You can also see from the result set that the data is coming not only from Wikipedia sources, but from the Azure Data Market, and from On premises data sources. What the demo didn’t make clear was how those data sources get into the catalogue. Power Query is the answer here as well. When BI Sites are implemented in Office 365, Power Query will expose some additional UI elements. that will let you publish the query and transform that you have built into the enterprise catalogue. In fact, when you look closely at Amir’s demo screen, you will see these elements.

These don’t show up on a standard Power Query install

The transformation capabilities of Power Query are really quite impressive, but a detailed description is beyond the scope of this post. They can be seen in the version available for download. As an aside, models built with Power Pivot can be moved directly into Analysis Services. Given the relationship that I mentioned above between Power Query and Integration Services, I wonder if it’s on the roadmap to allow Power Queries to be brought in to SSIS. On the surface, it would seem to make sense, at least from where I’m standing.

Power Map

Power Map is the other component that’s available today, although as of this writing, it’s still in preview form. You can download it from here. Unlike Power Query, Power Map requires Excel 2013 – it won’t run on 2010 at all. Power Map has been available since last fall in the form of a preview as code name “Geo Flow”. It didn’t make it to the main stage in the announcement, but it’s a pretty impressive piece of technology.

What Power Map does is allow you to take a data model built with Excel and/or Power Pivot, and plot the data on the surface of a 3D map. The map can then be rotated and zoomed, and animations created around different views or time dimensions of the data. It wasn’t shown at the announcement last week, but below is a Power Map visualization of Hurricane Sandy data.

This was put together simply by plotting the location of the measurements, the pressure on one layer as a heat map, and the wind speed on another layer as columns. Storm category was used as the category to color the columns appropriately.

There are a number of limitations to Power Map currently, the big one is that it doesn’t yet work at all in SharePoint – it’s Excel only (and not Excel Web App). Given that fact, it’s really just a sort of one off visualization/presentation tool, no matter how cool it is. However, we did just see our first glimpse of an HTML 5 Power View viewer… could an HTML 5 Power Map viewer be far behind?

BI Sites

To my mind, BI Sites is the most exciting part of Power BI. Here is where we find the most significant advances yet in both Microsoft’s mobile and cloud BI strategy. Until now, the cloud strategy was hampered completely by the inability to keep data refreshed automatically, and mobile devices could settle only for mobile web approaches. With BI sites, we see not only a great first step into these areas, but a solid infrastructure to expand upon. Unfortunately, at the point of this writing, it’s not available. You can however sign up for the preview program here.

BI Sites is a SharePoint App, pure and simple. This fact was not even mentioned during the announcement, but I feel that it’s vitally important to understanding how it works, and what its limitations are. Once created, the app reads Excel content from your Office 365 tenant, and provides a great number of features. Let’s quickly run through a few of these features.

Data Refresh

The Achilles heel of using Office 365, at least for me, has been the inability to schedule data refreshes. The latest 2013 wave brought support for interacting with PowerPivot enabled workbooks, which was great, but data refreshes had to be done manually, and the results re-uploaded. On premises, PowerPivot for SharePoint can be used to perform this function, but PowerPivot for SharePoint is not available in Office 365. Since the data can exist both on premises, or in the public, how does a cloud based service refresh data from inside your firewall?

The answer is that you will install a service, a sort of “data gateway” somewhere in your environment. This gateway will manage the refresh of all the on premises data sources and push updates out to Office 365, NOT the other way around. This will not require any firewall configuration, and will not depend on App Fabric, or any other relatively complex infrastructure.

Once configured, the automatic refresh each individual workbook can be configured separately, as can be seen below.

Report Gallery

One of the things that you notice when you enable PowerPivot for SharePoint is a new library template, the “PowerPivot Gallery”. When you use this template, you will notice that you get a completely different UI for the library that is Silverlight based, and gives you previews, and access to data refresh capabilities. This is again absent in Office 365 due to the lack of PowerPivot for SharePoint. However BI Sites, gives you the same functionality, in a (presumably) HTML 5 based experience.

HTML 5

Power View is a self-service analytical reporting tool that was first introduced in SQL Server 2012 Reporting Services, and subsequently added to Excel 2013. It allows users to quickly analyze data from an embedded model, and is supported within SharePoint. It’s a great tool to get answers very quickly, but one of the concerns with it is the fact that is built with Silverlight. This limits is use, particularly in the mobile space, as Silverlight is not supported on iOS, Anroid, or (surprisingly) Windows Phone.

BI Sites show us the first version of a PowerView renderer based on HTML 5, which should work on all devices. When the workbooks are opened through the report gallery, you will see the Power View reports, but they are rendered in HTML 5. I suspect that we’ll continue to see Silverlight being used in the designer in Excel, but the rendering through BI Sites is done with HTML5, which opens up the world of mobile.

Data Catalogue

I hesitate to include this in this section, as it could just as easily be included in the Power Query section, but since it required the BI Sites capability in Office 365, I include it here. The Data Catalogue is that service that allows you to register data sources from Power Query. The catalogue lives in Office 365, and is managed from there. Data sources in the catalogue can be discovered by Power Query users through the “Online Search” button.

The catalogue also supports features that help support the natural language features of BI Sites. These features are unclear to me at this point, but I will be sure to expand upon them once I get my hands on it.

Finally, to be sure, “Data Catalogue” is what I’m calling it in the absence of any official nomenclature from Microsoft.

Natural (English) Language Query

Natural language query was a very well kept secret. I had no idea that it was being developed until I saw it on stage. Natural language query allows a user to interrogate a data source almost as if they were performing a full text search. Based on their query, Power BI selects an appropriate visualization to display the results.

As you can see above, 1 shows that we’re interrogating a data model contained in the “Sales Reporting and …” workbook. 2 shows the query entered by the user in relatively plain English (no Select statements!), and 3 shows how Power BI has interpreted the natural language query. Finally, it determined that a tabular display was the best visualization to use for this result. However, many are available, and it uses the visualizations available to HTML 5 PowerView to display them. Number 4 shows other visualizations that may be relevant to the result set, and all you need to do is to select them to see them.

A few more results can be seen below.

New Visualizations

Any user of Power View will be familiar with the visualizations shown above. However, the one that brought the biggest applause at the announcement was the “king of the hill” visualization. This shows you a “winner” and “contenders” over a period of time, based upon a defined criteria. When applied to pop music over time, it looks something like this.

To get the full effect of this, I highly recommend watching the video starting at about 9 minutes in. Of course, this is a highly specific case, and I have to think that more interesting visualizations are on the way.

Limitations

As with most products, Microsoft giveth and Microsoft taketh away. Without having my hands on the full product, I have no hope of coming up with anything like a comprehensive list of limitations, but there are two that jump to mind immediately.

Power BI is limited to Office 365 only. That’s right, there will be no on premises version of this, at least not at launch. From a technical standpoint, I can’t see any reason why this must be, apart from either resourcing limitations within Microsoft, or strategic direction (or a combination of both). It remains to be seen whether Power BI is a “cloud first” product, or a “cloud only” product. Time will tell. In the meantime, those organizations that are still leery of cloud computing may miss out, but there is still a lot of goodness in the on premises offerings.

BI Sites is a SharePoint App, not a container. The workbooks themselves are still stored within document libraries in SharePoint. the default file size limit for Excel Services is 10 MB, and for SharePoint itself is 50 MB. It’s unclear to me if BI Sites relies on Excel Services (I assume that it does), and if so, your data models have a hard constraint of 10 MB. Even if Excel Services is not the bottleneck, they won’t be any larger than 50 MB. That is simply too small for some models, and will leave them out in the cold. I was told at WPC by a Product Manager that this is changing, but specifics are currently unavailable.

Mobile Apps

The final component (that is still unavailable) of Power BI is a collection of Mobile BI apps. Reports are selected in various BI sites and flagged for mobile use. Once flagged, they appear in the mobile apps and can be interacted with directly.

As an aside, I borrowed the above image from the Office 365 technology blog. Anyone that has attended my BI presentations in the past year will be familiar with the first data set in the app, a topic near and dear to my heart.

Apps were initially announced for Windows 8 and for iOS. No mention was made of Android or Windows Phone (?!?!), but I have to think that that isn’t far behind. Blackberry? I have no idea, but I wouldn’t hold my breath.

To finalize, I’m very excited about several of the new capabilities that Power BI brings, and the doors that it now opens. It’s another big step in moving Business Intelligence from the realm of the data gurus, and into the hands of those that can actually benefit from it. Once it is available to play with it, I intend to focus on it more thoroughly.

8 Comments

Using the new Excel 2013 Data Model with SharePoint

Published July 17, 2012

One of the biggest new features in Excel 2013 is the ability to import and analyze massive amounts of multi tabular data. However, it’s not really all that new – if you’ve used PowerPivot in the past, you’ll already be familiar with the underlying technology. What’s really new here is that Excel can now work with these large data models without the need of an add-in. The even better news is that Excel Services in SharePoint supports this new data engine. What may be a little unclear is what is necessary to make it all work, and this post is an attempt to clarify this a little bit.

To really understand what’s new, we need to understand what is (or was) available via the 2010 family of products. It’s a bit of a bumpy ride that covers 3 main product groupings, SQL Server, SharePoint, and Office, so hang on, and I’ll do my best to clarify.

Background

When SharePoint Server 2007 was initially released, it contained Excel Services. Among other capabilities, the original Excel Services was a means of surfacing an Excel workbook, or part of a workbook to a user through a browser interface without the requirement to have Excel installed client side. It was relatively limited, and the version of Excel Services that shipped with SharePoint 2010 removed many of those limitations. The Office Web Applications that install alongside of SharePoint bring the ability to edit workbooks directly in the browser.

At approximately the same time that SharePoint 2010 was released, the SQL server team released PowerPivot for Excel. PowerPivot is a free, downloadable add in to Excel that allows Excel users to work with massive amounts of data, and to analyze it in ways previously available only to advanced OLAP systems. The reason that it can accommodate the required data volumes is because it adds a new highly compressible data engine (previously named Vertipaq, now called x-Velocity) directly into the Excel file. These PowerPivot workbooks work perfectly when you have Excel 2010 and the PowerPivot add in installed, but what happens when you want to use Excel Services to share it with your team?

To solve this problem, the SQL Server team also released PowerPivot for SharePoint and included it with SQL Server 2008 R2 (read – it’s not available out of the box with SharePoint 2010). Installing PowerPivot for SharePoint in your SharePoint farm adds a new SharePoint shared service that works with Excel Services to allow those PowerPivot enabled workbooks to be rendered up by the server. These workbooks are “live” so that users can very effectively interact with them, using slicers, etc.

The way that it does this is that it creates a local “runtime” version of Analysis Services on the server that is used to perform all of the necessary analytical calculations. Also, to keep the data fresh, PP for SP also contains a series of timer jobs and configurations that connect to the back end data sources on a periodic basis. If you’re up for it, there’s a very detailed description of the architecture here.

Pre 2013 Usage

Without getting into much detail (like I will below with 2013 ), setting up PowerPivot for SharePoint involves starting with a SharePoint farm that is enabled for Excel Services, adding the PowerPivot for Excel add-on to one or more workstations that will create at least one PowerPivot enabled workbook, and then installing PowerPivot for SharePoint onto a SharePoint server FROM the SQL Server 2008 R2 or 2012 media. After performing some (relative onerous in some cases) configuration steps, The PowerPivot enabled workbooks can be uploaded into a SharePoint library or PowerPivot gallery, and then used by anyone accessing the SharePoint environment. There are quite a few moving parts here including

Excel 2010
SharePoint Server
Excel Services
PowerPivot for SharePoint
SQL Server
Analysis Services

As with any system, the more moving parts that there are, the more opportunity there is for failure. In addition, there are a fairly broad set of skillsets required to make it all work, so any simplification is welcome.

What’s New in 2013

The big news is that the x-Velocity engine that is the underlying power behind PowerPivot is included in Excel 15 without the use of an add-in, which will undoubtedly increase its adoption. This does lead to some practical questions around how this works with SharePoint 2013 and Excel Services. Couple this with the fact that PowerPivot still exists, and the landscape can get pretty confusing.

Excel Services itself can render Excel workbooks with embedded data models, but it’s not possible to interact with them. Clicking on slicers, filters etc. simply won’t work. The workbook can be seen, but it is completely static. Any attempt to interact with it will result in an error like the following.

As with PowerPivot in SharePoint 2010, what is needed is an Analysis Services engine to perform the necessary work. The big difference with 2013 is that the engine no longer needs to be installed on the SharePoint server – you can connect to an Analysis Services server elsewhere on your network, and that server does not need to be part of your SharePoint farm.

That server does, however need to be operating in “SharePoint mode”, and it can be a little unclear as to what that means. In the end what it means is that PowerPivot for SharePoint (available on SQL 2012 media) is installed on a non-SharePoint server. However, if you try to do this using the release versions of SQL Server 2012 (or 2008 R2 for that matter) you will get an error because the SharePoint bits are not present. What is needed is a minimum of SQL Server 2012 SP1, which as of this writing is available as a Community Technology Preview V3, and can be found here. To set this up, run the SQL install on the AS machine, and at the Setup Role prompt choose SQL Server PowerPivot for SharePoint.

Unless you need it, you don’t need to install the relational engine. No, you’re not really using PowerPivot at this point, but it’s the option you need to pick. I suspect that the name may change by the time SQL Server 2012 SP1 is released, but for now, that’s what it is.

Follow the remaining prompts to complete the installation. After the installation completed, resist the urge to run either of the PowerPivot configuration tools. These are used if we actually ARE using full PowerPivot (see below), and that will require a SharePoint server. If you run it on a non SP farm machine, things can get very messed up.

Once that is set up, you need to tell Excel services to use it. Navigate to your Excel Services service application in Central Administration, and select “Data Model Settings”.

The options here are pretty limited…what’s your Analysis Services server? Click on Add Server, and add your Analysis Services (PowerPivot for SharePoint) server. Unless you’ve changed it, PowerPivot for SharePoint will have installed in a non default instance named POWERPIVOT, so the server name will take the form SERVERNAMEPOWERPIVOT, as below

You may need to run an iisreset on your SharePoint front end servers, but once this is complete, you’re ready to test. To do that, you’ll need to start up Excel 2013, and import multiple tables from a data source. This triggers creation of the model.

Once imported (it may take a bit), create a pivot table and add an interactive element (a slicer is perfect). Test it out, and if it’s working, save it to a SharePoint document library.

Close Excel, navigate to the library, and click on the workbook. It should open in the browser. Once open, trigger the interactivity element (click the slicer) and if it works, you have everything configured properly.

If you want to have a look under the covers, start SQL Server Management studio, and create an Analysis Services connection to your instance (defined above). You should see a new model created for your workbook.

If it’s not there, make sure that you’ve interacted with the workbook – it doesn’t get created until the first interaction.

Whither PowerPivot?

Now that the engine is baked into Excel, is there any need for PowerPivot any longer? As we’ve seen, for simple analysis, no. However, if we want to do anything advanced, like filtering import data, modifying the model, creating complex relationships, or using DAX (the tabular answer to MDX), the answer is emphatically yes.

On the client side, PowerPivot is actually included with Excel – no download required anymore. It is disabled by default, and is enabled through the Excel Com Add in interface. You can view this as your individual or team model designer. With it you can create complex models and then share them out via SharePoint. Optionally these very same models can be imported into Analysis Services projects if/when they become mission critical, or too large. Another nice thing is that models created with PowerPivot can be shared on a SharePoint farm that is not using the full PowerPivot for SharePoint (but does need “Analysis Services in SharePoint mode – confused yet?).

On the Server side, we can install PowerPivot for SharePoint, just as we did with SharePoint 2010. The reason that we would want to do this is to gain access to PP for SP features like the Pivot Gallery, but primarily to access refresh capabilities. In the scenarios that I’ve described above, using Excel Services, the data that we’re using is relatively static. Data is imported when creating the model, and is used for analysis, but there is no mechanism to refresh the data in the model in the way that Analysis Services can. PowerPivot for SharePoint offers this capability in SharePoint 2010, and this carries forward with SharePoint 2013.

Configuring PP for SharePoint 2013 is beyond the scope of this post, but I will likely address it in a future post.

Compatibility

With Office 2013 we have the Third major release of PowerPivot, and the data model on the second platform for SharePoint. It’s not likely that anyone will upgrade all of their applications immediately, so the question is, what works where? The good news is that the core engine is relatively unchanged, and models created with any versions of PowerPivot should work well with Excel Services 2013 (and Analysis Services SharePoint mode). Conversely, models created with Excel 2013 whether with or without the PowerPivot 2013 add-in should work with previous versions of SharePoint. I haven’t yet had the chance to try out all of the various permutations and combinations, and would like to hear what your experience is.

Hopefully, this helps clear up some of the confusion around the Excel BI features in SharePoint 2013. Hopefully the naming in the SQL media installation gets cleaned up by release.

Update – July 26 2012 – If you’re interested in trying out any of the tools that I mention below, you can use a pre-built environment that’s been set up on CloudShare. Click here to sign up and access the environment.

3 Comments

Fixing Access Errors When Creating a BISM Connection for PowerView

Published April 17, 2012

PowerView is one of the shiny new features that are available to SharePoint users with the implementation od SQL Server 2012. To be sure, you don’t need to convert your database engine over to SQL Server 2012, but you do need to install PowerPivot for SharePoint, and/or the new Reporting Services service application for SharePoint, both only available in SQL Server 2012.

PowerView requires a connection to an xVelocity Analysis Services engine (formerly known as tabular mode or VertiPaq), or to an Excel spreadsheet with an embedded PowerPivot model in order to function. When connecting to the Analysis Services engine, there are a couple of permissions that need to be set on that engine that you should be aware of.

If you wish to connect to the Analysis Services engine, you have two choices. Firstly, you can use a Reporting Services connection (.rds) and select “Microsoft BI Semantic Model for PowerView” as the Data Source type.

Once this is done, it can be used to create PowerView reports. This approach works without any additional configuration. The reason that this is so is that Reporting Services takes advantage of the EFFECTIVEUSERNAME connection property for user impersonation. This is something that BISM does as well. When using an RS connection, the stored credential (in this case) is used to establish a connection with the data source, and then all queries are executed with the identity of the requesting end user (provided that the “Set execution context…” option is not selected.

For further reading on how Reporting Services uses the EFFECTIVEUSERNAME property, check this article. Since BISM uses the same property, it’s highly relevant to PowerView.

However, the problem arises if we choose the second option, and want to work with a BISM file directly. If we want to use a basic BISM file, we first add it by creating a document with the the BI Semantic Model content type.

However, the connection form is far simpler than the one used to configure the Reporting Services connection. You’ll notice immediately that there is no option for storing credentials. For a server connection, all that is necessary is a name for the connection, the name of the server, and the name of the database.

Unfortunately, if you haven’t gotten everything configured just so and you click ok, you’ll receive the error “Cannot connect to the server or database”.

This is due to the fact that when the server performs the validation check against the Analysis Services server, it uses a system account to first connect to the AS server. This is documented in this Connect forum entry. The corrective action is to add the account to the Analysis Services Server Administrator role:

What account needs to be added? The forum article I referenced above indicates the PowerPivot service account, which is now listed in SharePoint as the “SQL Server Analysis Services” account. However, my testing has indicated that the farm account is the one that is used to verify connections with Analysis Services, so that’s what I’m recommending here. This is only used for validation purposes, and to get around this problem without adding the account to the administrators role, you can simply select the “Save the link file without connection validation” option. This may however be unsettling to users.

Once you’ve created your BISM connection, you should then be able to use it to launch Excel, or create a PowerView report. When you launch Excel, the BISM is referenced by an Excel connection, which then connects directly to the data source with the credentials of the end user. No problem. However, if you attempt to create a PowerView report, you may find yourself faced with “An error occurred while loading the model for the item or data source”.

In the body, it misleadingly leads you to believe that it’s the end user (btidwell in this case) that doesn’t have access. What should happen is that, as explained above, the Reporting Services service makes a connection with the AS server, and then uses the EffectiveUserName property to impersonate the user. For more detail on how BISM authenticates to back end data sources, see this MSDN article.

If you’re seeing this error, the chances are that the service account that is being used for Reporting Services doesn’t have access to the Analysis Services database.

As in the verification step above – granting the account access to the specific database doesn’t do it, you need to add the service account that is used by Reporting services to the Server Administrators role. Once you’ve done that, PowerView should behave smoothly.

4 Comments

Connecting to Cubes and External Data with Excel in SharePoint

Published December 6, 2010

While many people are still unaware of it, Excel 2010 (and even previous versions) is a very powerful business intelligence client. That’s right, I said Excel. And I don’t mean the classic grab some data, do some charts and email it around sort of Excel, I mean connecting it to Analysis Services cubes and performing fast, useful data analytics on known sets of data. It can also go off and do some pretty amazing things with the Data Mining add-in, or PowerPivot, but for now I’m going to restrict myself to using core capabilities, and getting them published to SharePoint.

To start with, it’s important to understand how SharePoint interacts with Excel. Of course, at it’s core, you can store Excel files in a SharePoint document library,and open them in Excel. However,starting with SharePoint 2007,SharePoint included Excel Services, which allowed you to open a spreadsheet directly in the browser (without having Excel installed or using any ActiveX trickery). It also provided an Excel calculation engine, that was programmatically callable. Why would that matter? A power user could develop a complex model with Excel, store it in SharePoint, which could then be used as a calculation “black box” for other things. The model could also be tweaked by the power user as necessary with them needing to worry about coding. Finally, you could expose part of the spreadsheet (and named range or object) through the Excel Services web part, which would truly allow you to incorporate Excel content into a dashboard.

SharePoint 2010 brings more to the table, including not only the browser consumption of spreadsheet content, but editing through the Excel Web Application.

However, all of this power does not come without its risks, and when interacting with external data, risks abound. Microsoft has done an excellent job of providing us with a highly secure infrastructure that allows us to do what we want here, but the only trouble is that it’s not always clear which approach to take. My aim in this article is to navigate through the process of connecting to an external data source (an analysis services cube, but the principle applies to others) in a particular scenario in a simple fashion. Our scenario is a fairly common one:

One Front End SharePoint Server
One SQL back end Server hosting the SharePoint databases
One SQL server hosting the Data Warehouse and the Analysis Services cubes
Kerberos not installed in the environment

In this environment, we have an authentication problem, the famous double hop problem. If I as a user ask the server to process a spreadsheet that connects to external data, it can’t just take my authentication token and pass it along to the external data source. What we need to do is to set up a proxy account (this has its own set of implications that I won’t get into here) and we’ll do that via the secure store service. If you’re using Kerberos, then you don’t have the double hop problem and the Secure Store part of this won’t apply, but then you’re probably busy enough dealing with Kerberos issues anyway….

If you’ve ever connected to external data, and then sent the file to someone else, you’ll know that they get prompted to verify that they want to connect to the data source and that they trust the connection. In this case, the author has created an embedded data connection. Embedded data connections are not allowed (or at least are strongly discouraged) on the server, because it has no way of verifying the authenticity of the connection.

The way that we deal with this in SharePoint is by creating a connection file, and then publishing it to a trusted location in SharePoint. Administrators need to take heed, in that by default, Excel Services trusts all SharePoint locations, which makes life easy, but not necessarily secure. The library that the connections are stored in should also utilize approval features, but this is not required. This walkthrough isn’t concerning itself with the security aspects, but they need to be considered in any real world situation.

Most of the steps below only need to be performed once, and connection documents can be reused, etc. However, this walkthrough is aimed at getting everything all set up from scratch.

We also don’t want to store our credentials in our connection string, so we will utilize the Secure Store service in SharePoint which will allow us to use, without necessarily knowing, a set of credentials. This will allows us to work around the double hop problem in a secure way. We will start with the setup of the Secure Store Service.

1. Set up the Secure Store Service ID

From Central Administration, navigate to Manage Service Applications, and click on your Secure Store application. If you don’t have one already, you’ll need to create one. You should know that SharePoint Foundation does NOT come with the Secure Store service. However, Search Server Express 2010 does come with it (and a few other things). Given that it’s free, it provides a nice option.

The Secure Store Service Application relies on both the Secure Store Service (duh) and the Claims to Windows Token service. You’ll need to make sure that they’re both started in the “Services on Server” Section in Central Administration System Settings.

The secure Store application requires an encryption key. If one has not already been created, you’ll see a message indicating that you need to do so. The process is simple, just click the “Generate New Key” button in the ribbon.

Once a key has been created, we need to create a new target application, which is essentially a set of credentials. This is the application that our connection strings will reference when they need to connect to a back end data source. You create a new application by clicking the “New” button in the ribbon. The New application screen then appears.

There are a couple of things to note on this screen. Firstly, the Target Application ID is the ID that you will be using when you set up your connection below. You can name it what you like, but you’ll need to remember what it is. The Display Name and the Contact E-Mail need to be filled in, but the important thing to note is the Target Application Type field. If this is to be used by more than one person, you need to make sure that it is set to Group. By default, it is set to Individual, which doesn’t work so well in a shared environment. Take it from me – I found out the hard way. When this is filled in, click Next, and you’re presented with the following screen.

The Administrators are just that – the people who will set the properties of this set of credentials. The Members are the people that will be allowed to use this credential set in connections, External Lists etc. In the example above it’s set to anyone authenticated, which again, I wouldn’t recommend in production…..

When done, click OK, and we’re done right? Not so fast. We never actually set the credentials for this application, just everything around it. To do that, we select the application, and click the “Set (credentials)” button in the ribbon, or hover over the ID in the list and select “Set Credentials” from the dropdown.

In the subsequent screen you enter the account and the password (twice) of the credentials that are to be used as a proxy. Click OK and you’re done. A couple of things to note. If using a Windows account the name should be in DOMAINACCOUNT format. The proxy account is NOT a managed account, and if the password changes, you’ll need to come back here to update it. The proxy account should be a least privileges account, with access only to the things that are absolutely necessary to retrieve the appropriate data, or whatever its purpose is. Don’t add it to the Domain Administrators group.

OK, now step 1 is done. Whew.

2. Set up a Data Connection Library

The next thing we need to do is to set up a library to store our data connections. If you’ve set up a BI Site already (to use PerformancePoint), you can use the libraries there. Depending on your requirements, I find that it’s often a good idea to centralize this library and use it from different locations, but your requirements may vary. What we want to create is a Data Connection Library. This library will be used to store connections for both the Office Applications (ODC) and InfoPath UDC).

From the Site Actions menu on the site where it is to be located, select More Options, the, in the subsequent dialog box, Filter by Library, and select Data Connection Library. Give it a name (Don’t use spaces here, you can always come back and add them later, but we don’t want spaces in the internal name), and click Create

What makes this library special is that it uses the “Office Data Connection File” and the “Universal Data Connection File” content types. You could always add these content types to any old document library and achieve the same effect. You could also include Reporting Services connections if you’re using Reporting Services in integrated mode.

This library also needs to be registered as trusted in the Excel Services Application. This is done through the Manage Service Applications section of Central Administration. Simply click on your Excel Services application, click Trusted Data Connection Libraries, and add the URL of your library, if not already there.

3. Set up a Library to house the Excel Reports

You can store the Excel Report that we’ll be creating below into any document library in the Site Collection. If you have set up a BI Center, then you already have a “Reports” library, whose purpose is to do just that. Oddly, by default, this library isn’t set up to contain Reporting Services reports (.rdl files), but that isn’t relevant to my task here.

You simply need to create a new document library, and for the purposes of this example, I’ll call mine Excel Reports (original, huh?)

You’re now ready to create our connection. We’ll use Excel to do that.

4. Create And Store The Connection File

Open a new Spreadsheet in Excel. Click on the Data tab, then click the “From Other Sources” button and choose “From Analysis Services” (obviously, if you’re using something else, choose that).

Enter in the name of your server, and click Next, Select the Database and Cube that you want to connect to, and then click Next again. We’ll need to do a couple of things in the last screen that appears.

First, select “Always attempt to use this file to refresh data”. Then, click the “Authentication Settings” button. This is where it gets interesting.

Window
s Authentication is what you would use if the data resides on the same machine as the SharePoint front end, or your organization is using Kerberos. With this setting, the credentials of the user are used to connect to the data source.If you select None, then the credentials identified as the “Unattended Service Account” (if configured) in the Excel Services configuration will be used. You can only use one of these accounts, and when configuring it, it too will use the Secure Storage Service. We want to select our credential set and we do so by selecting SSS, and entering the ID of the credential set that we created in step 1 above.

Next, we need to save this connection into the SharePoint data connection library that we created in step 2. Click the Browse button, enter the URL of the library in the Address bar, choose a name and click save.

Finally, When you’re done, click finish, and fill out the metadata form that pops up with any comments or keywords that you might want to use later to fine the connection document. Now we have a connection document in the connection library.

5. Create The Excel Content

When you’re done creating the connection document, Excel will prompt you to insert a pivot table or chart. Choose the location, and Excel will insert it for you, and put you in pivot edit mode. You can select your dimensions and measures, and build your chart accordingly. When you’re ready, your worksheet should look something like this.

Later, we’ll want to show just the chart on a dashboard page, and to do that, we need to make sure that our chart has a logical name, so we need to do that, as highlighted above. Any named range can be published out to an Excel Services web part. When ready, it’s time to publish to SharePoint, but we also want to set some file properties first. These properties are very well hidden….

Click on the “File” tab to go to the backstage. Once in the backstage, click on the “Save and Send” tab on the left hand side. Then click on “Save to SharePoint”. Your screen will appear as follows:

Finally, we need to click on the Publish Options button. This allows you to control what gets used by Excel Services, and what appears when the spreadsheet is opened in the browser. It also lets us define parameters which can be used by the Excel Services web part, but I won’t be using parameters here. I will however choose to publish only my chart and my pivot table to Excel Services, so I click on the dropdown option in the Show tab, change it to “items in the workbook”, and check my two items.

Finally I can click OK, then click on “Save to SharePoint” and save the spreadsheet in the library created in step 3 above.

6. Test the File and Publish the Chart in a Dashboard

Navigate to the library that you stored the report and click on it. The file should open in a browser, and you should see the first item (alphabetically) that you set to publish above.

You can switch between published items, using the view dropdown highlighted above. Once you’ve gotten this far, everything is working, and we’re ready to put our chart into a dashboard page.

All that we’ll need to know is the name of the item, and the address of the spreadsheet. In our case, our item is “Chart 2” and the URL of the spreadsheet is http://uvspdev2010/ExcelReports/MySpreadsheet.xlsx. Navigate to anywhere in the site collection that you’d like to show the chart. I’m going to use a team site. From the destination, select Site Actions – Edit Page (or select Edit from the ribbon). Place your cursor where you want the chart to appear, click the Insert tab on the ribbon and click the Web Part button. Select the Business Data category, Choose the “Excel Web Access” part and click insert.

Once added, click on the “Click here to open the tool pane” link, and enter all desired parameters. The two crucial parameters are Workbook and Named Item. When adding content to a dashboard, I find that it is better to remove all of the buttons and web part chrome, but that’s totally dependent on the use case. When ready, my web part parameters look something like this:

At this point, you can click OK, and you should see your item on the page in context.

t=”773″>

Congratulations – you’re done!!

This was meant to be a “Hello World” walkthrough, and thus, I’ve left out quite a bit. You can add slicers and all kind of cool BI functions to your sheet, and they’re work in the browser. This is showing data in a cube, and therefore the chart will be updated when the cube is. You can also use parameters in Excel and drive them through filter web parts and connections on the display pages.

There ARE a lot of moving parts here, but once the infrastructure is set up, power users can do some pretty spectacular things using just Excel and SharePoint, and they can do so in a fully managed environment. Oddly enough, chances are that your power users probably already know both of these tools.

21 Comments