PerformancePoint 2013 and Tabular data sources #fail

I spin up my spiffy new SharePoint 2013 environment, migrate my PerformancePoint databases, and then try to hit the dashboards. I’m greeted by all kinds of errors. Take note: we’re using more and more tabular SSAS data sources at Trek.

Fast forward a few days. I open a ticket with Microsoft and begin troubleshooting. Engineer was a helpful chap. He had an idea from the get-go what the problem was, but wanted to check some environment variables before we go and start installing stuff.

Long story, short: you need to install the ADMOMD.NET SQL 2008 R2 feature pack if you want to hit tabular data sources (LINK – you’ll find the correct pack towards the bottom of the Install Instructions section). #lamesville #sql2012hasbeenoutforalmostayear

I asked the engineer to send me the Technet article stating this to which he replied “Wish I could.” Nowhere in any of Microsoft’s documentation does it state you need to install this feature pack in order to use tabular data sources in SP 2013. He did however send me this blog post so kudos to him for that:

Pre-req installer may not progress past IIS configration.

I’m pissed. Like, in disbelief pissed. Kinda like my buddy Tracy:

Anyone seen this KB? The Products Preparation Tool in SharePoint Server 2013 may not progress past “Configuring Application Server Role, Web Server (IIS) Role”

The KB lists 2 possible workarounds: 1) Install a hotfix; or , 2) Run a whole bunch of PowerShell that requires the OS .iso readily available. I’ve tried both with little to no success. I especially see issues when it comes to the AppFabric and Distributed Cache.

So you can imagine my disbelief when the true workaround is to Install the IIS role first before running the Pre-Req installer. Do that and the pre-req installer runs just fine (at least on Server 2012). Son of a…

SharePoint on Azure: several lessons learned

We got back from Convergence on Friday. I had a good time overall; good food, good times, crazy things to see. Never did make it to Acme Oyster House (sorry dad!).

Meanwhile, we’ve migrated a public SharePoint site to Azure. I should say we’ve migrated a SharePoint 2010 internet site to SharePoint 2013 running in the 14-hive. Many of my colleagues throughout the SharePoint community I’ve spoke to have this to say about that:

In the last few weeks we’ve learned some valuable lessons. Here they are (in no particular order):

  • Search scopes – I chose to not migrate the Search databases because migrating the content db was hard enough. Plus, when we complete the upgrade to 2013 I would just have 1 DB to focus on. In doing so, I don’t have scopes anymore since they’ve been deprecated in SharePoint 2013. You can’t even use PowerShell to add them. The fix was to revert back to the All Sites scope. It isn’t the end of the world though because the Search Service App is smart enough to see the variation you’re searching from and serve up that site’s content. For example, if you search for bikes on the German site, you’ll get German site content back to you, rather than UK or US content.
  • SharePoint Designer – Someone wanted a quick change to a page layout. Good news here is that only 1 page in the overall site uses that page layout. SharePoint Designer 2010 works but I was unable to add another web part zone the page layout. As a workaround I added the HTML directly to the page layout. Again, not the end of the world here, but it definitely isn’t what I would want to do. Adding a web part zone would have allowed me to drop additional content in the future or replace it altogether from the Edit Page rather than editing the Page layout.
  • compat.Browser config – We never bothered with mobile sites in SharePoint 2010 with this site. All we did was turn off the mobile browsers in the compat.browser config. I did the same for SharePoint 2013 (set all ismobiledevice to false) then reset IIS. However, this did not result in success. Got hit with a vague SharePoint error. I started looking on the interwebs for help and ran across this: LINK. Followed Option 2 and hit pay dirt. It does seem out there to have to drop a statement in to the overall web.config but I had to get things working. Once we migrate to SharePoint 2013 we’ll remove the statement and make use of device channels but since we’re still running in the 14-hive I don’t get that functionality quite yet.
  • Azure IaaS growing pains – IaaS is still in preview, and thus you’re subject to wonkiness and issues beyond your control. A few days ago we experienced an outage on the site. SharePoint couldn’t talk to SQL for some reason. Logged on the SQL box and couldn’t even connect to the SQL instance. The service was running but still no dice. Well, time for a restart and Yahtzee! everything was better. I went through the logs as best I could but had no idea what I was looking for. Come to find out Azure pushed down restarts. In doing so, SQL came back before the AD instance did so nothing was authenticating properly. HUGE lesson learned there. Best way to overcome this is to start using Availability Sets: link to documentation.
  • Calculated Column issues – had a user come to me with this one. The user noticed that a calculated column was throwing a string of characters into the column. I went and checked the column settings and miraculously the issue was gone. I edited another item and got the exact same string again. So it would appear that it wasn’t the formula but rather something was going on in the DB. Luckily I was at Convergence and there were a handful of SharePoint support folks in the Expo hall. Ran by their booth and showed them the issue. Thankfully this is a known issue and installing the March PU will fix everything: link to PU. One other note, according to the KB, you MUST install this PU if you ever hope to install a future CU.

That’s about it for now.

The mother of all Test environments (series)

So a few weeks ago I set out to create the mother of all Test environments at work running the latest Microsoft tech. We’re talking SharePoint 2013, Lync 2013, and Exchange 2013 all running on Windows Server 2012. In addition, my SQL backend is SQL 2012 SP1, and my workstations are running Windows 8 with Office 2013. All brand new tech that I won’t be able to put into production for months (if not 201 4).

It’s all set up now and let me tell you…it is a thing of beauty.

I’m going to putting together a series of articles outlining all the lessons learned and the pitfalls I ran into. The series WILL NOT be regurgitating MSDN and Technet articles; 1) because Microsoft pays their writers handsomely to write that stuff; and, 2) I’m a lazy blog writer.

My first post will be around setting up my VMs and the Domain (complete with AD Certificate Services). Who’s excited!? I know this kid is:

Search adventures with SSRS in Integrated Mode

At Trek we’re all about the BI. Just so happens I sit within the BI team so it was no surprise when Steve wanted to go with all the BI tools. PowerPivot and PerformancePoint were already setup before I got there. With SQL 2012 we get an overhauled instance of SSRS in Integrated mode and the newly introduced Power View. SSRS is now a Service App rather than a separate application so it makes deployment a lot easier. I won’t bore you with the details of installing as you can find all sorts of other bloggers walking you through the steps. What most bloggers don’t cover are the details of searching for .rdl’s.

Installation is pretty easy and getting your report libraries up and running are relatively straightforward. We even managed to create a few shared data sources and reports the day we went live. Real “Ready, Fire, Aim” type stuff. We happened to do the install on a Friday so we didn’t really put everything through its paces until Monday. When we began emailing links to rdl’s around we started getting complaints that users couldn’t see these reports. In addition, rdl’s weren’t showing up in search results.

Now what?

Posted this to the technet forums: LINK. Nauzad was pretty helpful in his reply in pointing me in the right direction. Come to find out, rdl’s are not extensions SharePoint search crawls by default. That’s an easy fix. Navigate to Central Admin > Manage Service Apps > Search Service App > File Types > add “rdl” and you’re golden. Kick off an incremental crawl and you should start to see rdl’s showing up in your results.

But I also noticed that only Full Owners, Site Collection Admins, and Farm admins were seeing reports in search results and in the libraries themselves.

Based on my research for the search issue I found that Report Libraries rely heavily on Publishing. Because of this, only those with Full Control rights and Site Collection Admins or Farm admins will see the rdl’s until they’re published, but it doesn’t stop there. You also have to publish the data source file as well. After some trial and error we figured this all out.

It’s relatively easy to get around the publishing requirement. Navigate to the Library Settings > Advanced Settings > check the box for “all users can see draft items.” Doing so will make all reports and data sources viewable as well as surface all reports in search regardless of published status.

For now, we’ve made the intentional decision to leave publishing on as it will allow use to security trim who has access to publish (accurate) reports and provide layer of oversight.

Issues adding add’l SharePoint server when using SP1 media

Few months back we added an app server to our farm, and – as always – it turned out to be an adventure.

Got a fresh Windows Server 2008 R2 install, joined to the domain, and applied all pertinent hotfixes and patches. While things were installing I downloaded the latest .iso’s from Volume Licensing. I went with the SP1 .iso’s as I figured this would be fine since the farm was already well past SP1. Save some time right?

Popped the dvd’s in the server and away we went. Install went fine with SharePoint so I moved on to the Office Web Apps. No incident there either.

I started up the Products Configuration Wizard and tried to join the box to the farm when I ran across an (in)convenient message telling me the Office Web App language server proofs we’re missing. But didn’t they install when I laid down Office Web Apps? What gives!?!

I commenced to Googling and I ran across this:

Looks like the slipstream installers are broken. I would take a guess and say Microsoft won’t be fixing them any time soon either.

Needless to say, I ended up reformatting the hard drive and starting over. I downloaded the pre-SP1 media and installed in the following order: SharePoint 2010 pre-SP1 > Office Web Apps pre-SP1 > SharePoint 2010 SP1 > Office Web Apps SP1 > SharePoint June 2011 CU > Current SharePoint CU. Took considerably more time, but it joined the farm no problem.

Moral of the story here is if you stood up your farm prior to SP1 with Office Web Apps, and you want to add another server to the farm you’ll need to do so following the procedures I went through.

Anyone else run into this?

Remove ;# when using a SharePoint List as a data source in SSRS

I find myself creating more and more SSRS reports with a SharePoint List as the data source. If you’re like me you’ll run into this issue sooner or later; the dreaded ;#.

Generally speaking, you’ll run across this when there is a choice column setup to allow multiple values. Gina and Zach helped me put this fix together:

Function ReplaceChar(ByVal value As String) As String 

	Dim fixedString As String 

	if(String.IsNullOrEmpty(value)) then

		fixedString = String.Empty


		fixedString = value.Replace(";#",", ") 
		fixedString = fixedString.substring(2, fixedString.length-2) 
		fixedString = fixedString.substring(0, fixedString.length-2) 

	end if

    Return fixedString
End Function

You’ll take this code and add it to the Code section of the Report properties. MSDN explains it pretty well for me.

How’s it work? Pretty sweet if you ask me. Starts by declaring “fixedString.” Then we go with the all important if statement. If it’s Null or blank then leave it blank. For everything else, replace ;# with a comma. But we don’t want commas at the start and end of our string so we gotta take care of those. The next two fixedString.substring statements take care of that.

When you’re ready to use the code on a particular column, right-click said column then choose Expression. From there you should see your column setup like this: =Fields!DogNames.Value. Add the following red text and you’re golden: =Code.ReplaceChar(Fields!DogNames.Value)


Auto post to Yammer from SharePoint

I haven’t talked much about Yammer here, but we’re using it pretty extensively at Trek. Definitely a game changer if used correctly and often. The best part is that Yammer integrates with SharePoint, but one feature missing is that of auto posting to Yammer. While manual posting to Yammer is there, and it works pretty slick, the absence of any programmatic posting causes a barrier to adoption with some of our groups.

Originally we attempted to accomplish this via a custom SharePoint Designer Action. Seemed like the most reasonable approach to try first. We scoped it as a sandbox solution, which forced us to do a full-trust proxy since we were using email as our delivery vehicle. Needless to say, the attempt failed. Partly because of Yammer’s issues with email feature.  Things got so bad for Yammer they had to re-architect their whole approach to e-mailing. So we shelved the idea.

Fast forward a few months. Email to Yammer is working and a few groups were pushing for the functionality we had tried a few months back. Sat down with Tim and tried to logically architect this. What to do? Object model, SSIS package, Event Receiver, OData? Lots of options but which one is the best? I kept on my old path of trying to use SharePoint tools to solve this so I traveled down the client object model path. Why? Because it sounds cool.

Long story short: Epic. Fail.

Client Object Model – in this case – just isn’t really good at grabbing the Created By’s email address and plugging it into an email. But the bigger issue though was that I was focused on the solution and not the problem. Lesson learned here: when developing code, the goal should be to remove as many external dependencies as possible. The object model puts a big, fat dependency on SharePoint’s code and could possibly come back to haunt me in the future. Enter OData. Developed by Microsoft, it’s a standard data access method that isn’t going anywhere for the foreseeable future. Sweet.

Next I had to figure out how to flag items for processing and then – once processed – how do I flag it so that the app knows never to process it again. Enter custom columns and content types. Enable control of content types in your list and add 2 choice columns: SendToYammer and Processed. The choices are Yes and No. I originally tried a Yes/No column but it appears the data types are different between the two types of columns. I set the defaults on both columns to No. I then hid the Processed column in the content type so that only the app would have access to it.

Now that the SharePoint List is where I want it. Time to open up Visual Studio.  I referenced Eric White’s blog to get me started:

  1. Create a new project.  Click File -> New -> Project.  Select a directory for the project.  Set the name of the project to      PostToYammerOData.
  1. Right click on the References      node in the Solution Explorer window, and click Add Service      Reference.  Enter http://site/_vti_bin/listdata.svc for the address.  Change the namespace to      PostToYammer.

Now for the code – apologies in advance for not posting the “cleanest” code, but hey, it works:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Mail;
using PostToYammerOData.PostToYammer;
using System.Configuration;

namespace PostToYammerOData
    class Program
        static void Main(string[] args)
		//You can get the DataContext when you setup the Service Reference
			[Title of your site]DataContext dc = new [Title of your site]DataContext(new Uri("http://site/_vti_bin/listdata.svc"));
			dc.Credentials = CredentialCache.DefaultNetworkCredentials;

			var result = from d in dc.[List Title]
				where d.SendToYammerValue == "Yes" && d.ProcessedValue == "No"
					select new
						//Define your columns
							Id = d.Id,
							Subject = d.Title,
							Body = d.Body,
							//may be .Email or .WorkEmail depending on your AD attribute mappings
							CreatedByEmail = d.CreatedBy.EMail

                foreach (var d in result)
                    //For Troubleshooting purposes

                    // email to yammer

					string to = "[Yammer group address]";
					string from = d.CreatedByEmail;
					MailMessage message = new MailMessage(from, to);
					message.Subject = d.Subject;
					message.Body = d.Body;
					SmtpClient smtp = new SmtpClient();
					smtp.Host = "[Exchange IP address]";
					smtp.Port = [port];

                    // update by id, set processed to yes
                    var item = dc.[List Title]
                    .Where(i => i.Id == d.Id)
                    item.ProcessedValue = "Yes";



Basically the code opens the list, iterates through the list’s items and – for each item where SendToYammer is equal to “Yes” – e-mail Yammer then set the Processed column to “Yes.”

I can take my newly built and tested code and I can give it to someone to run on their desktop or I can deploy it to a Windows Server in my environment and use Task Scheduler to run the app on a scheduled basis. We chose the latter. Just make sure whoever runs the app (or if you use a Service Account like we did) has Contribute rights to the site.

All the lists I’ve attempted this with have had less than a few hundred items. I haven’t tried it with 1000 or more and we know that in some instances OData will only return 1000. Check out Tim’s post on how he overcame that issue (Link).

One last thing, why didn’t I use an Event Receiver? Well, besides the dependencies mentioned above, I can never get Event Receivers to work. Then again I try to do sandbox whenever possible which could be part of my problem and if you want to e-mail with a sandbox solution you’ll need a Full Trust Proxy, thereby defeating the purpose of a sandbox solution.

Thanks again to Tim and especially Steve for giving me a hand on this. Much appreciated.

June 2012 CU build number discrepancy

So tonight I just finished applying the June 2012 CU. I took a look in Central Admin’s “Servers in Farm” page and noticed that the build number was 14.0.6123.5000. According to a variety of SharePoint heavyweights’ blogs and MSDN, it should be 14.0.6123.5002.

I ran and reran PSConfig 3 times. Still no change in the build numbers. WTF!

Say What!?!

Event Viewer was fine. ULS was fine. The sites were fine. But I was starting to feel a twinge of panic.

Come to find out the last number in the sequence is the revision number. I ran across this TechNet post that put my mind to ease:

The poster with the answer mentions the syntax for a build number is Major (14), Minor (0), Build (6123), Revision (5000/5002). From now on I’ll pay attention to the third number in the sequence. … Microsoft Shenanigans win again.

Create a standalone report from the SharePoint 2010 Web Analytics Service App

I did it – albeit with a lot of help from a variety of folks. I successfully created an SSRS report that queries the SharePoint Web Analytics database. If you don’t have SSRS, then there’s always PowerPivot. The queries will work with both. Here’s how I did it.

First, you’re going to need the Site Aggregation ID. Why Microsoft uses this is beyond me. I ran across a blog post where the writer wrote a console app to extract this (blog post HERE). Tried the app with no luck. I then posted THIS on TechNet. Thanks to Guru Karnik for getting me on the right path.

The only catch is Guru’s query uses CURSORs. According to Tim Laqua this is very bad, and I’m inclined to listen to him (you’ll notice our blogs look eerily similar – yes I used the same layout). So what to do? Well we first need the Aggregation ID. I grabbed this portion of Guru’s query and fired away:

SELECT DISTINCT DimensionName as SiteCollectionID,AggregationId,SM.[Path]

FROM WASiteInventorySnapshot WASIS WITH (NOLOCK)

INNER JOIN [SPConfigDB].[dbo].[SiteMap] SM

ON WASIS.DimensionName = SM.Id

WHERE WASIS.DimensionType=0

Order by Path

Take a look at your results and you now have the Site Collection GUID, the AggregationID and the URL path. Filter on the site collection you’re after and grab the Aggregation ID. If you’re not sure which site collection you want to grab (as was the case for me), you can grab the site collection id and use PowerShell to show you the way like this:



OK, AggregationID in hand, we place it into the following query:

USE [WebAnalytics_ReportingDB]

   --Declare everything

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Date

DECLARE @EndDate Date

DECLARE @DateDiff Int

  --Set Variables; @datediff is the amount of days you're after

SET @DateDiff = 30

SET @StartDate = DATEADD(d,-1*@DateDiff,GETDATE())

SET @EndDate = GETDATE()

SET @AggregationID = '[paste aggregationid here]'

SET @SiteID = '[paste siteid here]'

SET @SitePath = '[paste URL here]'

  --Make Magic

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM [WebAnalytics_ReportingDB].[dbo].[fn_WA_GetSummary] (Cast(CONVERT(varchar(8),@EndDate,112)as int),Cast(CONVERT(varchar(8),@StartDate,112)as int),@DateDiff,@AggregationId,1)

You’ll notice I took Guru’s query and further tweaked it for my use. Couple of notes here:

  • The GetSummary function the query is calling is a little jacked. You need to set a Start Date, End Date, and a Date Difference (i.e. the difference between the 2 dates). Why? Because that’s the variables the function wants. Like I said…jacked. Get’s worse below.
  • For me, I wanted the SSRS report to always display the last 30-days starting with Today’s date. Later I can always go back and add some sort of parameter/expression magic to make the dates customizable by the user, but for now this works.
  • Notice in the Set Variables section of the query that I’ve included the SiteID and SitePath. I did this for posterity only. It has no other bearing on the query other than making it available for me when I create the report.
  • I first declare the date fields as Date so that I can do some magic with them in terms of calculating the dates programmatically, but later on I have to convert them to Int so the function will work properly. Very jacked, but hey, it works.

But what about Top users? Got that too:

USE [WebAnalytics_ReportingDB]

   --Declare everything

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Date

DECLARE @EndDate Date

DECLARE @DateDiff Int

  --Set Variables; @datediff is the amount of days you're after

SET @DateDiff = -30

SET @StartDate = DATEADD(d,@DateDiff,GETDATE())

SET @EndDate = GETDATE()

SET @AggregationID = '[paste aggregationid here]'

SET @SiteID = '[paste siteid here]'

SET @SitePath = '[paste URL here]'

  --Make Magic

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM [WebAnalytics_ReportingDB].[dbo].[fn_WA_GetTopVisitors] (Cast(CONVERT(varchar(8),@StartDate,112)as int),Cast(CONVERT(varchar(8),@EndDate,112)as int),@AggregationId,1)

Similar to the query above, only this time I call a different function: GetTopVisitors. Again, had to do some magic with the Start and End Dates to calculate programmatically.

This is only the beginning. Lot’s of possibilities here. The downfall to this approach is that it’s site specific. There is a function that will show top pages, but you can’t get down to the specific web (i.e. sub-site).

Next iteration will allow users to input custom dates and we’ll get creative with the graphs and charts in SSRS (think: dancing kittens and rainbows style). Thanks to everyone that gave me a hand in this.