I know I’ve been slacking on this whole blog challenge thing. CRM has been taking up more and more of my time along with SharePoint going gang busters. Not to mention it takes real effort to put blog posts (even 300 word posts) together. I have several fellow tech bloggers that I’m amazed at who can put together several stellar posts a week when I have trouble just getting one together.
Rob Collie – www.powerpivotpro.com – is one of those bloggers in particular. He puts out 2-3 blogs A WEEK and they each are all excellent as far as I’m concerned. I had the opportunity to meet Rob at #SPC13 and he is as engaging and smart in person as he is on his blog. I even got the opportunity to have drinks with him and a whole host of Microsoft Program Managers. So the story goes like this . . .
On Monday morning of the SharePoint conference I get an e-mail from Steve: “Go have dinner with this guy,” along with a link to a blog post (LINK). I’d heard Rob’s name mentioned but never really followed his blog much or considered myself a PowerPivot expert by any stretch. I had spoken on PowerPivot in SharePoint and how to set it up, but as far as using it I felt I was still a relative n00b. But Steve is a smart guy and has never steered me wrong when it came to tech or drinking so I figured what the hey. Shot Rob an e-mail and heard back within an hour or two. Instructions for Wednesday night was to stroll on down to the Experts Exchange and then go with a group of Microsoft BI folks to dinner. Sounded pretty cool.
Monday and Tuesday flew by. On Wednesday I was starting to get pretty excited and anxious all at the same time. I may work on a BI team, but I was far from a BI expert. My advice to myself was “stick to what you know lest you sound like an idiot in front of people smarter than you.”
After the conference wrapped for the day I headed down to the Experts Exchange. This is a pretty cool event at the conference where Microsoft Program Managers, MVPs, and MCMs meet with conference attendees and answer their biggest questions. It was pretty sad that Microsoft BI only had 2 tables while other Products/Topics had far more. At the BI table was Rob and several other program managers: Diego and Kay (rhymes with Hi).
We started talking about PowerPivot and SharePoint and service architecture. It was immediately apparent to me that all 3 of the guys were beyond experts at this material: THEY DESIGNED IT. Diego was a Program Manager for the Excel team and Kai was a Program Manager for the SSAS team. Very smart, very cool dudes. They broke down the architecture of PowerPivot and SharePoint to its most basic level and even took time to answer my pitiful questions.
After Experts Exchange it was time for dinner. I figured the place would be PACKED. Went over to Red Square in Mandalay Bay and sat down with a small group of about 10 or so. Not what I was expecting. I figured the place would be full of people clamoring over each other to talk with Microsoft’s BI brain trust, but sadly it looked like I was the only Microsoft customer that took Rob up on his offer. I felt like Wayne and Garth at the Aerosmith concert. Rob sat next to me, across from me was Jen Underwood, and to my right was a Senior Program Manager on the Excel team. Holy sh^t! I was a simpleton among geniuses. I – sadly - did not catch everyone’s names and nor could I keep up with all the genius talk. It was just nice to sit there and be a fly on the wall while BI experts solved the world problems. Only here these folks did have the ability to solve the world’s problems. Pretty surreal if you ask me.
After a few hours the party’s attendees started to trickle out one by one. I was determined to be the last guy there. The folks at the other end of the table meandered down to me. Come to find out they figured it was only Microsoft employees and Rob at this thing. Astonishing how they didn’t think anyone would be interested in attending. Boy were they dead wrong.
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.
I made a promise to myself to start writing a little everyday. Just 20 minutes every day to get something down on “paper.” I’m long out of writing practice and doing this will help a lot of things. But enough about that, let’s talk about SharePoint Saturday St. Louis!
My wife and I had originally planned to drive down Friday, but we came up with this wild idea to drive down Thursday night instead. So with the car packed and 2 little kids in tow, we left at 5:30 PM. Word to the wise… I don’t recommend starting a 7-hour car ride with two children under 2 that late. Everything went fine until hour 5, then all hell broke loose. I’ll spare you the gory details, but let’s just say my 11-month old had had enough of riding in the car. Family drama aside we made it fine to my father-in-laws just fine.
Friday night was the speaker’s dinner at the Moonrise Hotel. VERY nice. The food was fantastic, lots of new faces for me, and they even had an open bar. The more I go to these things the more I begin to notice that SharePoint Saturdays are very much a local thing. You’ll have the occasional speaker come from far away, but for the most part, the folks speaking and attending are local. Most of the folks I spoke to on both days thought it was crazy that I would come all the way from Wisconsin to attend #SPSSTL, but when I tell them my wife’s father-in-law lives close by they didn’t think I was so crazy.
On Saturday morning I noticed that my CloudShare environment PowerPivot wasn’t refreshing correctly. I spent 95% of Danny Jessee’s presentation on Facebook, Cloud, and SharePoint in the back of the room trying to fix things. By the 1-hour mark I decided to go with my backup environment. Wasn’t difficult to get up and running, but the whole ordeal was a HUGE pain. From what I did see of Danny’s presentation it was pretty slick.
Next presentation was Virgil Carroll’s on Information Architecture in SharePoint. Honestly, this guy could enthrall me just by reading the phone book. I saw him last year at the Twin Cities SharePoint Saturday and he was awesome. If you get a chance to see him I highly recommend it if for nothing else to see his presentation style.
Lunch. I was too busy talking to vendors that the catering vendor (St. Louis Bread Company; Panera to those folks who live outside the area) ran out of box lunches. So I was stuck with a bagel for lunch. Oh well.
Next up was Enrique Lima’s presentation on SQL best practices. SQL is still very much black magic to me in some ways and Enrique removed some of that mysticism for me. You can check out his presentation HERE.
The last session I saw before mine was Todd Kitta’s BI presentation. Now I’d be lying to you if I told you I wasn’t nervous that he would cover every point in my presentation. Thankfully he did not (whew!). Probably the best part of the weekend for me was when he covered Power View from a 100K ft level. I haven’t had a chance to test it yet in my own dev environments so it was nice to get a view of it. I didn’t know that Power View and PowerPivot work pretty closely with one another (i.e. you can take a working PowerPivot and turn it in to a Power View report). I was also curious to find out how you build Power View reports. I was somewhat disappointed to learn that there is yet another app needed to build Power Views; however, it’s all browser based and it’s pretty simple to use. So I’d call that a wash. Good job Todd!
Finally it was time for my presentation. I learned a valuable lesson right from the start of my presentation. When presenting to SQL Saturday’s, the title “Demystifying PowerPivot from the SharePoint Admin’s perspective” means something entirely different than it does to a SharePoint Saturday audience. Had about 12 or so people come. Not bad, but definitely not standing room only like it was at SQL Saturday back in April. My presentation caters to just about anyone, but the folks who did come appeared to get a fair amount of information out of it. At the #SharePint afterwards I talked to a couple of folks who – once I gave them the background on my presentation – were bummed they missed it. Definitely violated the first rule of anticipating your audience. I guarantee I won’t make that mistake again. Any ideas on a new title?
Overall, I had a good time in St. Louis. #SharePint was held at Moonrise again up on their rooftop bar. Certainly a hidden gem in St. Louis that I would recommend to anyone visiting STL this summer. You can find my slides to my presentation at the following: PowerPivotSPSSTL.pptx. Thanks!
So I admit that I've been slacking on my blog and Twitter posts, but I swear it was for a good reason. I was preparing for my presentation at SQL Saturday #118 here in Madison this past Saturday.
My presentation was entitled "Demystifying PowerPivot from the SharePoint Admin's Perspective." Feedback was overwhelmingly positive. Had a great crowd and they asked some fantastic questions.
I've had several inquiries about my slidedeck. Ask and ye shall receive! PowerPivotSQLSAT118.pptx
I also be posting the slides to the PASS site as well.
I've been asked to give this (or similar) presentation at Tech on Tap & MADPASS. There are a whole handful of other possibilities for this presentation too (i.e. SharePoint Saturday Chicago and/or St. Louis, SPUG's, and maybe even another SQL Saturday). I'm all about getting out there and spreading the PowerPivot love.
Thanks again to everyone that came out.
Make sure - before you read any more of this post - you have ADO.Net Data Service Update for .NET Framework 3.5 SP1 installed in your environment. Linkage: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=2343
Now with that out of the way, let me say again, the SharePoint REST API and PowerPivot will change your life.
Slap this into the URL Address bar:
From there you’ll be presented with a list of all the lists and libraries that are compatible with the REST API. Grab the list you’re interested in and tack it on to the end of the above URL like so (case sensitive so be as exact as possible):
You should then be presented with an ATOM feed (looks something like an RSS feed). You may or may not see much, which is fine. The background XML has all the metadata of the list/library you’ll need.
Next, fire up Excel with the PowerPivot add-in installed. Open the PowerPivot window and select “From Data Feeds.”
Paste in the URL above (e.g. http://site.net/sites/sitename/_vti_bin/listdata.svc/[List Name]) and then click Next. It’ll chew on that for a second. Then click Finish. The list will now be imported into memory (i.e. PowerPivot does its thing).
Keep in mind here that the REST API only imports 1000 items in at a time. However, for a lot of lists and use cases this is more than sufficient. [See update below.]
Close the PowerPivot window. Go back to Excel and click Insert > Pivot Table/Chart. Then click the “external data source” radio button and then click “Choose Connection”
Find the PowerPivot Data connection you just created above and click Open. Then click OK.
From here you can add all the Values, Slicers, Filters, etc. you want.
Now how will this change your life? The applications of this are endless. One example could be a project list sliced and diced to your users’ liking and displayed via Excel Services web part. Users get the familiar Excel look and feel while you can rest comfortably knowing that they’re not manipulating your list data. You get far more control over the look and feel too as opposed to PerformancePoint. In addition, you get the ability to integrate outside data as well (try integrating TFS or Oracle data in PerformancePoint).
But why not just use SSRS? Good question. Not everyone has access to SSRS. That takes a considerable amount of time, energy, resources, and cash to get that going. But again, with the ability to use slicers and allow your users to see what they want, when they want, that alone makes this a pretty powerful feature in your bag of tricks.
Anyone else tried this? Any other use cases come to mind?
So only 1,000 items will show up in the ATOM view; however, PowerPivot can pull ALL the items down. VERY COOL! I tested it on a list with +32K items and PowerPivot had no problem.