PowerPoint–My Dashboard and Report Design Tool

At some point I think that I am becoming a Microsoft OfficeMALL13_Badge_See125x125 specialist as opposed to a BI Architect.  All of this work in Excel and now PowerPoint.  Okay, done with the ramblings.  As I have noted in a couple previous posts, I am working with a team on the Modern Apps Live! conference which is in Vegas next week.  Well, this is another “lesson learned” that I wanted to pass along as a result of doing that work.  (Hope to see you there.)

Using PowerPoint 2013

Microsoft Powerpoint 2013 IconSo I had to create two types of data visualizations for this conference.  Usually, I would use paper or white board to sketch it out and then proceed to make it a reality.  Somewhere along the way, I heard that Microsoft uses PowerPoint to lay out UIs.  Not sure if it is true or not, but it seemed easier and less expensive than Blend or Visio, so I thought I would give it a try.

So, I first needed to create a summary report for a poll within the app that was created.  I used the standard tools with in PowerPoint such as tables, charts, text boxes, and images to mock up my report.  What I liked was I was able to add notations to the mockup for future reference.

image

I had some frustration creating the charts as I wanted them to be representative.  But overall not a bad experience.  The next task I was taking on was working with the dashboards I was going to create in Excel 2013.  I still wanted to lay it out so I knew what I would be trying to design.  This was when I stumbled onto the Storyboarding menu.

image

I actually like using the shapes in this toolset better.  Turns out this is available when you install Visual Studio Ultimate, Visual Studio Premium (my version), or Visual Studio Test Professional.  More on that can be found on MSDN – Storyboard Using PowerPoint.  This can be integrated into TFS and directly associated to work items.  I am not a UX expert, but I like the ability to add tabs like I will have in Excel and there is even a SharePoint page background.

image

However, as you can see, even if you don’t have Storyboarding you can still effectively build up a PowerPoint slide to look like the report, dashboard, or even SharePoint page.  I was not sure if I would be able to embrace this, but in the end I really like the simplicity and using PowerPoint allows for comments, versioning in SharePoint, and other mechanisms to support dashboard design.

I also wanted to pass along another blog post I found from Jason Zander on the Windows Azure team on the same subject:  My Favorite Features: Creating Storyboards with PowerPoint.  Hopefully this gives you another simple way to mock up reports and dashboards when you can’t find that User Experience Pro.

SQL PASS Summit–Day 3 (Keynote)

From the PASS president, Rushabh Mehta.

PASS has facilitated 430,000 hours of training and expansion into the global community.  These three days contain massive amounts of training and opportunities to network with other SQL peers, including over 800 first timers.  SQL Server MVP Deep Dives Volume 2 has been officially released here with the opportunity to get a bunch of autographs from the 55 MVP authors in the house.

Ted Kummert’s Keynote highlights:
  • “Some database vendors just decided to get into the cloud last week”
  • Growth of PASS is amazing! Over 240 chapters, 300 MVPs, 79,000+ members, and over 4000 attendees at the Summit.
  • SSAS in the cloud, coming soon?
  • Crescent is now PowerView.
  • SQL Server 2012 (aka Denali) will release in the first half of next year.
  • MS SQL Server Vision: “any data, any size, anywhere”
  • Big Data characteristics:  Large data volumes, tranditional and non-traditional data sources, new technologies and new economics and new insights.
  • They will be supporting Hadoop on Windows. By providing connectors to SQL Server and SQL Server Azure, by investing in a JavaScript Framework for Hadoop, drivers for Excel, by partnering with Hortworks,
  • From Denny Lee: HiveQL – the query language for working with Hadoop data. Hive ODBC Driver in PowerPivot – this driver will be available in the next month.
  • Announcing code name Data Explorer for data enrichment, which will be in SQL Azure Labs by end of year.
  • Data Explorer will be in SQL Azure Labs by end of year.  It allows you to do data classifications.  Looks very much like a variation of Excel.  It allows you to add data from external data in the Azure Marketplace which will recommend data based on the data you are analyzing.  Kind of looks like PowerPivot in the cloud, interesting.
  • Amir Netz was recently promoted to Technical Fellow at Microsoft – early developer of OLAP Services, SSAS and beyond.
  • Amir demos PowerView.  During the demo we discovered that Meet the Fockers was the top comedy.  Oh, the power of BI.  Great demo about this product.  He also announced that export to PowerPoint will be supported in this release.

Quite the whirlwind this morning, but some more cool BI stuff coming soon.  Looking forward to the rest of the day.

Recovering SharePoint 2010 with BI on Windows 7

While working with Microsoft BI projects, I am often required to deliver the visualizations through SharePoint.  On my current project, I have a SharePoint 2010 development environment on Windows 7 that has SharePoint and all the SQL Server services running.  Last week I was preparing to demo our progress when the site became unresponsive.  The demo site had the wonderfully descriptive message:  “An unexpected error has occurred.”  So, I attempted to open the management console but that page could not be found (404 error).  I turned on the expanded error messaging by modifying the web.config file and checked the log files.  The errors pointed to the inability to connect to a database.  Upon further investigation I discovered that three of our databases went suspect including the admin and content databases.

At this point, I proceeded to try to restore my full SharePoint backup.  Which works in all situations except for recovering the admin database. Based on the online resources, this is to be expected.  After conferring with the coworkers, I was able to determine that the admin database was unrecoverable and the best bet was a reinstall of SharePoint.  After working through some options I was able to get the site back up and running without recreating the entire solution.  The rest of the blog walks through the recovery scenario and calls out the nuances of having BI components as part of the content.

Prerequisites

First, the SharePoint Farm must be Farm backed up.  I did this through the Central Administration console. 
(http://technet.microsoft.com/en-us/library/ee663490.aspx)

image

Reinstalling SharePoint

The next step involves uninstalling SharePoint.  I did this by running Setup and choosing the Remove Option.  (The Repair option did not work.) Once I had SharePoint uninstalled, I proceeded to reinstall it.  After SharePoint was installed, I ran the Configuration wizard. This step recreated the Admin database.  Once this was complete, I verified that the starter site and the Central Administration site were operational. 

Recovering the Farm

You can recover the farm from either the Central Administration site or by using PowerShell.  In this case, I used Central Administration as it is easier to select and understand the options. 

I first browsed to the Backup and Restore menu page. From this page, I selected the “Restore from a backup” option under the Farm Backup and Restore header.

NOTE:  In order to run the backup or restore farm functions, the Administration service must be running.  If it is not running, start it in the Windows Services console.

There are three pages or steps to recovering the backup. 

Step 1.  Select the backup to restore.

image

Step 2.  Select the components to restore.

image

For my purposes, I selected the Farm option which restores all of the options to the farm from the backup.

Step 3.  Select Restore Options.

imageimageimage

The first two selections are the most important.  I selected the “Restore content and configuration” and “Same configuration”.  This uses the same application and site names for the full restore of your site.  The “New configuration” option would have required me to rename all of the applications and services I needed to restore. There were a lot of applications and services to rename which I chose not to do.

When I restored at both the client and on my personal development environments I needed to remove the following services before running the restore:

  • Application Registry Service
  • Web Analytics Service Application
  • User Profile Service Application
  • Search Service Application.

When I did not remove these, my restore failed due to duplicate names.  You may run into a similar issue, so removing them before the restore is easiest.  These applications and services should be removed from the Central Administration site.  Once these services are removed, run the restore.

Bringing BI Back Online

At this point, my site would have been operational except I had a Reporting Services webpart on the homepage of my site.  Because of that I saw an error below.

image

As the error message notes, the issue is with the Reporting Services webpart. The issue is that the full restore did not restore the Reporting Services integration.  The integration needed to be redone manually. 

Once Reporting Services was reintegrated I was able to open the site.  However, neither the Reporting Services or PerformancePoint webparts were working. 

image

These issues are primarily security related.  To fix the Reporting Services issue, I openend the data source and reset the credentials.  Once reestablished, the report webpart came back online. 

To resolve the PerformancePoint issue, I first went to Central Administration to refresh the Secure Store Service key.  I was able to use my passphrase refresh the key.  (NOTE: if necessary you can create a new key.) Next, I went in to the PerformancePoint Service Application settings and reset the unattended service account. 

Once these steps are complete, the site was fully restored.  While the full backup is extremely useful for site failures it will not recover all of the BI components and as such feels very incomplete. I hope this saves someone some time later.

Installing PowerPivot on an Existing Farm

At my current customer, we are putting together a sandbox MSBI environment with SharePoint as well.  We installed and configured SharePoint, SQL Server, Analysis Services, and integrated Reporting Services.  I was preparing to put together a PowerPivot demo in SharePoint and started to install it.  So here is where the fun began.

First, you need to use the SQL Server 2008 R2 install to

Next, you need to LOGIN using your Farm admin account and from what I could piece together, that user MUST have the following privileges.

  • SysAdmin on SQL Server
  • Local and Domain Administrator
  • SharePoint Farm Administrator

Of course, all of this violates any concept of minimum privileges for users.  While this level of access may not have been absolutely necessary, it is definitely where I ended up to get this working.  (The moral of this story is to install PowerPivot on new farms only?)

Here are some of the links I used to get me pointed in the right direction.  I would be interested in hearing if anyone else has had this issue and resolved it differently.

Now that it is running I can put the rest of my demo together.  This sure seemed more painful than it had to be.