BISM Connection is missing from Document Types in SharePoint

If you want to connect Power View to your Tabular model, then you need to create a BISM Connection file. What happens, however, if you try to create a new connection in your SharePoint Data Connections library, and cannot find the BI Semantic Model Connection document type under ‘New Document’? It is missing! Why?

This usually means that you haven’t enabled the Content Type for the Data Connections library, or wherever you want to store your Data Connections.

To do this:

  • In SharePoint, go to ‘Library Tools’ for the Data Connections library
  • Go to Library Settings
  • About half way down, go to ‘Add from Existing Site content types’
  • Look for ‘BI Semantic Model Connection’ and add it to the list of available Content Types
  • When you return back to the ‘Settings’ page, you should now see the option available for BI Semantic Model Connection.

Here are some handy Books Online links, in case they help you to create the Connection file:

Create a Shared Data Source for a Data Model

Create a BI Semantic Model to a Tabular Database

Use a BI Semantic Model in Excel or Reporting Services

I hope that helps to save someone some time!

Jen x

Mobile Business Intelligence Series: Part 1 – Mobilising Native SSRS

This is the first part in a series about implementing Mobile Business Intelligence. Interested readers may like to know that I’m presenting on this topic at a number of events, including SQLSaturday 162 in Cambridge, UK, and at SQLSaturday 170 in Munich, Germany. I also presented on this topic at SQLMidlands in July 2012, and also in SQLSaturday South Florida straight after TechEd North America 2012.

In this series, I will suggest different ways in which people can mobilise reports using a variety of technologies. If I am missing some from the list, please let me know! I should add that Copper Blue or myself are not affiliated or partnered with any of the organisations that  appear here. These are simply solutions to business problems that I’ve come across, and thought it might be useful to share here. 
One question is this: how can we mobilise standalone SSRS reports without SharePoint?  Here are some suggestions, which are dependent on your mobile device:

At the time of writing, Reporting Services isn’t viewable on an iPad in SharePoint 2010.  Issues that you might find include the following:

Native Mode Report Manager not fully functional
Some reported drop-down issues, for example, if you have more than 6 parameters. In this case, you might want to consider PowerPivot. I will look at this later on in the current series.
Vertical text does not render properly. In any case, are you sure that you need to use it?
Calendar control issues

How is it possible to view Reporting Services on an iPad? One option is to use MobiWeave, which displays SQL Server Reporting Services reports.  It’s possible to download an evaluation copy from the MobiWeave site. Here are the main features: 
  • Download and View Reporting Services Reports from multiple reporting servers
  • Supports SSRS in SQL Server 2005, 2008, 2008 R2, 2012
  • It also supports Azure SSRS in Native and Sharepoint modes with SSL
  • Parameters support – remember that this requires specific ways of interacting on the iPad
  • Interactive mode which supports Drill down and Drill through reports
  • Bookmarks and History will also work
As a Windows fan ( I can’t wait until I get my hands on a Surface!) I like Blue Granite Nitro. Blue Granite uses XML-formatted data to produce dashboards and reports. The XML data is generated using an application server, such as SQL Server Reporting Services. This tool is available from the Zune MarketPlace (how I love saying that!), and you can take a look here.

These solutions might be a good option for people who don’t have SharePoint but still want mobile business intelligence. 
Next up, we will look at the various options for mobile Business Intelligence in SharePoint 2010 and SharePoint 2013 Preview. I’m a SharePoint fan so it’s next up! I look forward to your comments.

PowerShell for Business Intelligence – Day 1

Can PowerShell help Business Intelligence specialists?

I was very fortunate to present recently at TechEd North America on my favourite topics, Business Intelligence and Data Visualisation. Whilst I was there, I learned that the most-attended session involved PowerShell for beginners. I thought that Aaron Nelson ( blog ¦ twitter ) did a fantastic job of his presentation, and I had the good luck to car-share with Aaron on our way down to SQL Saturday South Florida, where he shared his boundless enthusiasm for all things PowerShell.

That got me thinking. I can see that DBAs and sysadmins use PowerShell to automate things, and make their lives easier. I couldn’t see anything similar for Business Intelligence developers, however, and I wondered how PowerShell could make our lives easier by automating certain aspects of our work. For example, it’s possible to use PowerShell to install Sharepoint-integrated Reporting Services, and I also use it to administer the Office365 account. 

Therefore, I’m starting a blog series which starts right at the beginning, and it’s aimed at helping Business Intelligence people to use and adopt PowerShell to make their day-to-day work easier. It’s aimed at people who might not have discovered PowerShell yet, but are happy with the way that they do things already. I like to challenge myself to ‘work smarter rather than harder’
Since this is the first part in this series, we are going to look very simply at the very basics of PowerShell. Let’s start by describing what PowerShell actually is.

Well, PowerShell is a scripting language, that is part of the standard installation of Windows 7 and Windows Server 2008 R2.  You an find it if you go to your ‘Accessories’ folder from the Start Prompt.  PowerShell allows you to write little scripts, which are simply text files which have the extension ‘.ps1’ Since the commands are stored in text files, they are accessible using your preferred text editor.

We are going to practice PowerShell by:

  • Starting an SSRS service
  • Stopping an SSRS service
  • Doing some Fact Finding about SSRS using PowerShell 
  • Installing SSRS in Integrated Mode for Sharepoint using PowerShell

Launch the Sharepoint 2010 Management Shell by going to the Start button, go to Microsoft Sharepoint 2010 Products group. You need to run the Shell as Administrator. To do this, right click on it, and select ‘Run as Administrator.

Starting the SSRS service:

get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance

Stopping the SSRS service:

get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Stop-SPServiceInstance

Here is a sample of how that might look.  Here, we have an attempt to start a service that is already started. We can see this in Yellow.

The command is then executed to stop the service. The default ‘Y’ was chosen. The service was stopped since the next command line does not show any information.

PowerShell Start Stop Service 

Doing some Fact Finding about SSRS using PowerShell 

If the service is installed and configured, here is the command to get the URLs for accessing SSRS as a service.


The following command will tell you any other servers in the Sharepoint farm where there is an install of the SSRS shared service.


Installing SSRS in Integrated Mode for Sharepoint using PowerShell

It is possible to install SSRS using PowerShell, if it is not installed already.

I’ve particularly found PowerShell an easy – and quick – way to install Reporting Services as part of an integrated Sharepoint installation. Instead of navigating the Sharepoint Central Administration and installing and configuring the SSRS service, it is straightforward to run and execute the PowerShell cmdlets. The instructions to do this activity can be found here.

Install the SSRS integrated service for Sharepoint 

Copy and paste the following command, and then press return.


If the command was successful, then there will be no message returned to the command prompt.

Install the SSRS Service Proxy


That’s all for now! In the next episode, we will look at doing more with PowerShell in Business Intelligence, along with some common error troubleshooting.

I hope that helps!

PowerView and PowerPoint Interaction is not available offline

During my session at TechEd Europe, I was asked if Power View would still be interactive in PowerPoint, if the PowerPoint couldn’t connect to the Sharepoint source.

Unfortunately, this is not possible. We can look at the code inside the PowerPoint file by right-clicking on the Power View image, and selecting ‘View Code’.  We can see that there is a link to the Sharepoint PowerPivot Directory and also the Report Server. Here is my example below, and if you click on it, you will get my flickr larger version:
PowerView Silverlight

Alternative Hosting is being used in order to serve up Silverlight, which means that it needs to be able to point to the Sharepoint source in order to serve up the Power View report for interactivity. Since it is using Silverlight, it won’t be accessible on an iPad device.

I believe that this constraint has been put in place for security reasons; it is important to secure data and data visualisations, and it would make sense that Power View interactivity was restricted to where it could be best secured.

Power View outside of Sharepoint Enterprise

I often hear requests for Power View to be made available outside of Sharepoint.  Currently, Power View does require Sharepoint Enterprise 2010 and SQL Server 2012. I see that people are starting to love Power View. I love Powr View since I think it is facilitating businesses to ask the different questions of their data. 

If you want Power View outside of Sharepoint Enterprise, then go and vote for my Connect case hereYou can have input to the roadmap by going to the Connect case and leaving your thoughts there. I do know that the Microsoft team do listen carefully to Connect cases, but I don’t see Connect being adopted widely by folks in the community to feedback their suggestions.  

The system requirements for Power View can be found here

Personally, I love Sharepoint and I’ve implemented Microsoft Sharepoint Business Intelligence solutions that really deliver value to enterprises. Yes, I am a Sharepoint fan! However, I do sympathise with small customers who do not have Sharepoint Enteprise, but would love to have some of the features. 

Power View is still ‘early days’ and I accept that other packages such as Tableau are much more mature. The debate of Sharepoint in Business Intelligence rages on elsewhere, and it isn’t the point of this blog here; instead, I’m focusing on this particular request for Power View outside of Sharepoint, which I hear often at User Groups and via my Twitter conversations.

Technically, Power View is a Reporting Services Add-on.  If you want more information on Power View, you can look at the Microsoft material here. I am not copying and pasting the information – I hate it when people do that to me! – so you can head over and take a look at the official Power View documentation

I’d like to add that the Power View training materials are superb, so well done to the team who’ve carefully produced this documentation. You can look at some of my Power View posts on my blog for my presentations, articles and so on.

Sharepoint and PowerPivot: Connections fail to refresh in SQL Server 2008 Denali CTP3

Sharepoint requires a range of troubleshooting skills because it can involve the integration of various applications, which are necessarily resting on the application stack. I have the deepest respect for SharePoint specialists since i know that the troubleshooting skills can cut across applications and an understanding of the ‘glue’ that welds SharePoint together. Take, for example, the issue which I found recently whilst importing a PowerPivot into SharePoint. Everything looked absolutely fine: the PowerPivot loaded nicely into the PowerPivot gallery, and I could see my Excel spreadsheets too. The black and lime-green SharePoint theme was carried on throughout the Excel workbooks, and the whole thing was looking great. The Excel slicers were highly customised to match the SharePoint theming and customer branding, and the whole pieces, put together, looked fresh.

The PowerPivot was created on a Windows 2008 R2 Server 64 bit machine, which directly accessed the underlying SQL Server 2008 R2 database source. It is roughly 1.8Gb in size, which is just under the 2Gb limit for SharePoint files. I uploaded the PowerPivot from the original machine to the PowerPivot gallery as normal, on the target machine.

The issue came, however, when the user tried to filter the Excel spreadsheets or use the slicers in the Excel Services workbook, based on the PowerPivot. Even though the Excel workbook was part of the file uploaded to Sharepoint, the filters would not refresh. The file was uploaded with ‘All’ so it was not the case that any data had been filtered.  When a filter item was selected, the Excel spreadsheet froze, and then produced the following error message: 

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

This issue is covered by MSDN here. However, none of their resolutions worked for me. The way I resolved this issue was as follows:

My resolution: I recreated a new Excel workbook, which used the PowerPivot as the source. The issue was due to the fact that I had developed the PowerPivot on one machine, and then moved it to another. Otherwise, the initial PowerPivot worked fine, and a new test PowerPivot on the subsequent machine worked fine too. The second machine did not have access to the source data since the PowerPivot was generated from a static data source, so it did not actually need to be refreshed. 

The MSDN suggested resolutions were as follows:

Scenario 1: The cause might be that a domain controller is not available to validate the user identity – this was a test VM that was a full administrator, and this wouldn’t explain why it worked for a PowerPivot that was ‘home grown’ on the server, and not a PowerPivot that had been moved.

Scenario 2: Differing versions of PowerPivot. I was very careful to ensure that the versions of PowerPivot were identical across machines

Scenario 3: IIS Reset in Sharepoint might work; no, I did try.

In the end, it was just easier and quicker to recreate the Excel workbook. The PowerPivot was otherwise unharmed during its movement from one server to another. I could see that the ‘PowerPivot data’ connection, when it was cracked open, pointed to a file that didn’t exist on the target machine. To make sure everything ran smoothly, it was easier just to recreate the Excel workbook in a completely new Excel workbook, which then worked perfectly.

These scenarios show the multi-disciplinary elements of troubleshooting Sharepoint and PowerPivot, since it involved aspects of Sharepoint such as IIS, Windows Claims authentication, and so on. Despite this, the end result of using Sharepoint and PowerPivot together was an great solution and if you haven’t tried it out yet, I suggest that you think about installing a Sharepoint and MS Business Intelligence Virtual Machine and try it out for yourself! Here is a link which might help you to build a VM for yourself.

Hope that helps someone else! 

Office365 Getting rid of Custom Theme Images in External Sites

If you are using Office365, you probably want ‘quick and easy’ wins in order to make your external site look better. At Copper Blue Consulting, we use Office365 for our email, web hosting and our Sharepoint activity. The current blog series will look at some of our findings which we’ve used in order to improve the appearance of our Office365 site.
I know that the Copper Blue site needs some love and attention, and now that SQLBits, SQLRelay and SQLPass is out of the way, I hope that I can start to make our corporate website look, well, corporate.
First thing on the list was to get rid of the theme header logo on the top right hand side. These are automatically added when you select a theme in Sharepoint.  If you’ve set up an external website in Sharepoint, then the configuration items are quite clearly laid out in the ribbons. Here is an example here:

Themes in Office365 1

We can see that none of the themes are blank, but each has a little picture somewhere. On the Copper Blue site, I just want a plain header with a logo. Nothing more, nothing less. How do we achieve this?

When we open up the Site in Sharepoint Designer, we notice that there are custom images in the Office365 structure. I wasn’t able to delete these. However, I was able to amend the file. Therefore, I navigated to the offending image and clicked on ‘Edit File’. You can see the structure in the following image:

Themes in Office365 3

Since I couldn’t delete the image, I decided instead to simply blank it out. Here is the editor for changing the image:

Themes in Office365 4

On the right hand side, you can change the brightness. I simply made it pure white, so that it matched the header of the site. I know that this is a ‘fudge’ but it gave me a quick easy win in terms of getting rid of the annoying logo, that didn’t sit with the rest of the branding.
If anyone has other ideas on how to do this, I’d be glad to hear them. In the meantime, more adventures of Office365 will appear as I move the Copper Blue site from being very Sharepoint looking, to something that’s much more customisable. If you want to see the end result, then please do look up the Copper Blue website to have a look!