threewill-webinars-2017.jpg

Free ThreeWill Webinars for 2017

We’re excited to announce our Webinar Schedule for 2017 (all times in EST)…

  1. Moving from SharePoint Online Dedicated to Multi-Tenant – 1/26/17 @ 1:00pm – Listen Now
  2. Migrating from Jive to Office 365 – 2/23/17 @ 1:00pm – Listen Now
  3. Complex SharePoint Online/2016 Migrations – 3/30/17 @ 1:00pm – Listen Now
  4. Creating Award-Winning SharePoint Intranets – 4/27/17 @ 1:00pm – Watch Now
  5. Find Anything in SharePoint with Amazon-Like Faceted Search – 6/29/17 @ 1:00pm – Watch Now
  6. Budgeting for 2018 SharePoint Initiatives – 10/26/17 @ 1:00pm – Register
  7. Successful SharePoint Farm Assessments – 11/30/17 @ 1:00pm – Register

The schedule is subject to change (especially if presenters get overloaded on projects). Let us know in the comments if you have other topics that you would like us to cover.

Sign up below to get notified about upcoming events or follow us on twitter.


SharePoint is a web application platform in the Microsoft Office server suite. Launched in 2001, SharePoint combines various functions which are traditionally separate applications: intranet, extranet, content management, document management, personal cloud, enterprise social networking, enterprise search, business intelligence, workflow management, web content management, and an enterprise application store. SharePoint servers have traditionally been deployed for internal use in mid-size businesses and large departments alongside Microsoft Exchange, Skype for Business, and Office Web Apps; but Microsoft’s ‘Office 365’ software as a service offering (which includes a version of SharePoint) has led to increased usage of SharePoint in smaller organizations.

While Office 365 provides SharePoint as a service, installing SharePoint on premises typically requires multiple virtual machines, at least two separate physical servers, and is a somewhat significant installation and configuration effort. The software is based on an n-tier service oriented architecture. Enterprise application software (for example, email servers, ERP, BI and CRM products) often either requires or integrates with elements of SharePoint. As an application platform, SharePoint provides central management, governance, and security controls. The SharePoint platform manages Internet Information Services (IIS) via form-based management tooling.

Since the release of SharePoint 2013, Microsoft’s primary channel for distribution of SharePoint has been Office 365, where the product is continuously being upgraded. New versions are released every few years, and represent a supported snapshot of the cloud software. Microsoft currently has three tiers of pricing for SharePoint 2013, including a free version (whose future is currently uncertain). SharePoint 2013 is also resold through a cloud model by many third-party vendors. The next on-premises release is SharePoint 2016, expected to have increased hybrid cloud integration.

Office 365 is the brand name used by Microsoft for a group of software plus services subscriptions that provides productivity software and related services to its subscribers. For consumers, the service allows the use of Microsoft Office apps on Windows and OS X, provides storage space on Microsoft’s cloud storage service OneDrive, and grants 60 Skype minutes per month. For business and enterprise users, Office 365 offers plans including e-mail and social networking services through hosted versions of Exchange Server, Skype for Business Server, SharePoint and Office Online, integration with Yammer, as well as access to the Office software.

After a beta test that began in October 2010, Office 365 was launched on June 28, 2011, as a successor to Microsoft Business Productivity Online Suite (MSBPOS), originally aimed at corporate users. With the release of Microsoft Office 2013, Office 365 was expanded to include new plans aimed at different types of businesses, along with new plans aimed at general consumers wanting to use the Office desktop software on a subscription basis—with an emphasis on the rolling release model.

read more
Danny RyanFree ThreeWill Webinars for 2017
letterman.jpg

Top Ten Reasons You Need Power BI

Introduction

In honor of David Letterman’s departure from Late Night with David Letterman, I’m doing a top ten on the reasons you need Power BI.  Unfortunately, you’ll have to imagine the drumroll for each.  What I like about Dave’s Top Ten is that they are timely, relevant and usually pretty funny.  So I thought that while Power BI is a typically a serious topic, it can probably be a little fun, at least I hope so.  While my top 10 list are obvious exaggerations I hope you can find some of them a little relatable and maybe some of the details for each relevant to you and your needs.  You may also notice below that I’ve broadened Power BI to include features that you’ll find in both Excel, Power BI for Office 365 and in the Power BI Preview because all of these technologies work together. Drum roll please….

#10. Corporate Upgrade from Office 95 is Behind Schedule

We’ve all been in the situation before where the latest and greatest is out there but you just can’t have it yet.  You might be looking at all the new shiny things Power BI has to offer, but for one reason or another, you’re stuck back on an old version of Excel.  Microsoft has recognized this and within the Microsoft Power BI Preview has created the Microsoft Power BI Designer client tool.  It’s specifically designed to create reports for the new Power BI experience.  Even if you are on the latest version of Excel, this is a tool worth checking out since it has built from the ground up to work with the new Power BI experience.

#9. Haven’t taken Vacation in 27 Years because no one else can Create the Sales Report

It’s likely today someone still has the role of gathering up data on a daily, weekly or monthly basis compiling it into some sort of report-able form and then disseminating that information out.  Often times these roles are called analysts but they aren’t getting to analyze data, instead they act more as data curators.  The client tools available in Excel along with the features in Power BI for Office 365 and Power BI Preview should help users focus less on repeated data curation and more on data analysis. By using Power Query, an analyst can to connect to many different sources of data, merge that data together and load it into PowerPivot all in a manner than is recorded and repeatable as a Power Query. Then with PowerPivot they can compress huge data sources, create relationships between data sources and even model hierarchies or create calculations.  Once all the data is modeled and in a friendly format for end users, then it can be easily put into Power View by dragging and dropping fields to create visualizations.  Finally, with Power BI for Office 365 or Power BI Preview these workbooks can be uploaded, shared and set up with scheduled data refreshes so that they are always up to date.

#8. Management’s New Chant is “Hadoop, There It Is”

Honestly, I don’t know much about Hadoop, but I’m sure there are a lot of you out there who do and Microsoft has realized that, too.  When I opened up Power Query in Excel, I counted 32 different types of data sources I could use, seriously 32!  You’ve got all the standard fair in Microsoft’s wheel house from Excel, Access, SQL Server and Azure all the way to databases like Sybase, MySQL, DB2 and Teradata.  If that’s not enough there’s basically any OData feed, anything that’s like a table on the web (more on that in a second) and stuff like Salesforce and Hadoop. Beyond the sheer number of available data sources, Power Query also supports a Data Catalog where queries are available for use by others.  In this catalog there are public and private catalogs available. You can think of public queries as those that Microsoft makes available for everyone.  Public examples include things like population data pulled from a table in Wikipedia or census data from the US Census Bureau.  The private catalog would be your corporate queries that you or others in your organization have published to your own tenant for use just by others within your organization. While on the topic of corporate queries, another thing to think about is that underlying your corporate queries is likely corporate data that is in all likelihood stored on-premises.  Since Power BI is essentially Microsoft’s cloud BI offering, you’ll need to make data available by using the Data Management Gateway service.  A Power BI administrator can manage the settings for your tenant, and it does require the installation of gateway software to make your on-premises data available within Office 365.  If you are interested in more you can follow the instructions in Create a Data Management Gateway.

#7. Email Inbox is now referred to as My Reports Repository

By now everyone knows that emailing attachments is generally a bad idea.  There are the obvious issues of multiple copies, lost emails, storage space and so on.  Within Office 365, the share button has really become the way to ensure everyone is on the same page for any type of content and ensure we don’t end up clogging our inboxes with attachments.  Technologies like Delve and One Drive for Business are really leveraging this sharing behavior to create a powerful experience for users and a single place to go for content.  Power BI is no different when it comes to leveraging sharing.  Whether it’s the current Power BI for Office 365 sites experience or the new Power BI Preview experience, sharing is the way to point everyone to a single source of a report or dashboard. Obviously sharing is a great way to pull users into a report, but sometimes you likely also want to allow for a better self-discovery experience and this is where Power BI for Office 365 sites comes in.  When you install the app in a site, you get a special landing page that rolls up all your Excel documents from your document libraries.  You can then build a more engaging and cohesive experience for your users by choosing to enable specific Excel reports which creates a thumbnail of the contents for a visual representation.  You can also chose to feature key reports and even Power BI Q&A results (Natural Language Queries) on the page.  Through featuring items, you can have them shown more prominently to users without having to navigate down through document libraries and folders.

#6. Excel Spreadsheet is so big people just call it “Big Mama”

We’ve all probably had to deal with a large Excel workbook in the past at some point.  Maybe it’s one that has just grown overtime or maybe it’s one that is an extract of an internal system that allows people to report on it.  In either case Power BI supports larger workbooks than just a regular SharePoint Site, thus allowing you to work with more Excel data in Office 365.  There are some important things to consider about your work book size and what happens when you try to view the Excel file online. In addition to supporting larger workbooks, if you utilized PowerPivot you can create a memory-efficient Data Model so you don’t run into the 10 MB size limit for data outside your model.  How good is the compression you ask?  Well Matt Allington has shown an example where the compression ratio was 92.2%.  In tests of my own using the Power BI – Getting Started Guide I saw significant compression of the 2 sample Excel inputs (each 45 MB) as well as other data sources and a final Excel workbook of around 5 MB.  While working through the getting started guide, I did also learn a valuable lesson, you have a choice between using the 32 bit and 64 bit versions of Excel, go with the 64 bit version, otherwise, you can run up against the 2 GB limit just based on how much memory Excel and PowerPivot need to work.

#5. Everyone Keeps Telling You How Much Cooler The Charts on The Fitbit Site Are Then Yours

We all love visualizing data and it really confirms the old saying “a picture is worth a thousand words”.  I guess in BI it might more aptly be “a chart is worth a thousand data points”.  In Excel, we’ve always had lots of charts available as Pivot Charts but these days Power View is where it’s at.  While Pivot Tables and Charts are typically okay solutions for dealing with relatively small sets of data you really want to leverage PowerPivot and Power View for enterprise data or if you are planning on uploading your Excel files to Power BI Preview where only Power Views are available to be shown as reports currently. Power View is actually a special type of worksheet within Excel and there are a large number of visualizations available.  Since Power View is a special type of worksheet in Excel when you upload it into Power BI it can be surfaced as an interactive report where users can do things like click on bar charts to affect other charts or use slicers to filter data and even use a timeline control to see how data changes over time which is one of my favorites.  If you haven’t thought about the value in how seeing something like a bubble chart when viewed over time you should really check out Let my dataset change your mindset from Hans Rosling.  His Ted talk isn’t on Power BI but it’s an eye opener on seeing a bubble chart as it changes over time. Additionally, you have Power Map which allows for showing data on geographically. If your data source has city, state, zip code or even latitude and longitude it can be mapped and combined with different calculations to visually show your data on a map in many different ways like heat maps, bubble charts, and column charts.  There is actually another great demonstration from that’s part of the Microsoft Virtual Academy Power BI Jump Start where Michael Tejedor shows a Power Map over time to determine energy usage for Microsoft buildings in Seattle.

#4. Support Center is really into NASCAR

The Internet of Things (IoT) is a huge buzzword these days, and there is no escaping it.  Not only are people creating more data, but now so are “things.”  Regardless of the source sometimes this data is really just transient data that you just want a real time view of how things are doing and not necessarily a more static historical view.  It is times like these that things like gauges in your car such as your speedometer come to mind.  The great thing is that the new Power BI dashboard can support just such real-time scenarios as shown by Amir Netz in his Power BI and Azure Stream Analytics demo.  While a typical data source in Power BI has a daily or weekly refresh frequency through the powerful integration of Azure and Power BI, you can use Azure Stream Analytics to push data into Power BI rather than have Power BI attempt to pull data from a source.  If you are interested in this you can find an article here.

#3. Marketing Team just got iPads and Immediately Threw their Desktops out the Window

Let’s face it, gone are the days where in corporate IT we can really force everyone to be on a specific version of Internet Explorer.  There are more devices than ever with executives on tablets, marketing on MacBooks and many companies embracing a Bring Your Own Device (BYOD) philosophy.  What’s great about Power BI for Office 365, Excel Services and certainly the Power BI Technical Preview is that they leverage HTML 5 so that users on tablets and mobile devices can have a great experience.  When building your reports in client applications like Power View, you can be sure that when they are published and shared your users will have a great experience with powerful visualizations and interactivity.  Additionally, within the Power BI Preview it even has native apps for iOS and Windows for an even better experience than the browser.

#2. Focus Groups have determined everyone likes “Dashboard Thingies”

In the land of BI, dashboard is a pretty broad term and most people use it all the time to mean many of things.  I’d like to define it as a “one stop shop” for one or more visualizations that matters to me.  While that sounds pretty simple I really want to focus on that last part “that matters to me.”  Depending on a person’s department or role within an organization, what matters to them can vary. I really like the simplified approach inside of Power BI Preview where Dashboards are front and center and can be easily manipulated.  Users can start with nothing but a dataset created from an ever growing number of data sources and create reports within the browser, in Excel or in Power BI Designer.  Dashboards can be assembled for each user’s preference by pinning different reports or pinning the results of simply Natural Language Queries against the available datasets.  The ability to pin and easily manipulate the size and position of each visualization on a dashboard means everyone can see just the things they care about from an underlying data source.  Also if an analyst is creating dashboards for others, they can of course also share their dashboard with others so there is a common dashboard as well.

And the #1 Reason You Need Power BI is… Reporting Strategy, code-named Whack-A-Mole, is really Killing your Arm

If you’ve had to create charts, reports and dashboards for very long a theme that often emerges is that as soon as your consumers look at the reports they often have more questions about the data.  First, asking questions of the data is a good thing; it helps deepen an understanding of it.  However, it can feel a bit like a game of Whack-a-Mole where you are constantly responding to variations of reports that are based on the same data because you don’t have a tool or strategy in place that lets user’s do this for themselves.  In Power BI, the mantra is self-service BI and it applies to many different roles or experience levels that must interact with data. In Excel an analyst can utilize self-service BI though things like Power Query to find and gather data from many data sources.  They can publish these queries to a catalog for others to use.  They can utilize PowerPivot to allow Excel to create a Data Model for them and, of course, they can create Power View reports for users.  Finally, they can upload the Excel to a SharePoint site as a Report or Power BI Preview to make it available as a dataset to create reports and dashboards on. In Power BI (both Power BI for Office 365 and Power BI Preview) report consumers can use the Natural Language Query to ask questions of the data which will render charts and reports real time that were not even created by an analyst but simply as a result of the questions asked.  In Power BI Preview, users can even open datasets and just drag and drop available fields in the browser to create reports which they can pin to a dashboard.  This sort of power really empowers the users and speeds up the cycles, no longer requiring an analyst to create yet another report variant.

Summary

Hopefully, you enjoyed a little humor around some of the really great self-service features that Power BI offers.  Whether you’re an analyst, a power user or just a consumer of reports and dashboards, there is bound to be something you can benefit from with the client tools offered in Excel like Power Query, PowerPivot, Power View and Power Map and the online offerings of Power BI for Office 365 sites and the new Power BI Preview.

Next Step

We have a webinar coming up in June on getting up to date on Microsoft’s BI Offering. Join us then for more information on this important subject.

read more
Bo GeorgeTop Ten Reasons You Need Power BI
ocean-toe-dip.jpg

Power BI Primer

Introduction

Recently, I have been doing a lot of research on the evolution of business intelligence within Office 365 and wanted to do a quick primer for others.  The goal of this primer is not to teach you about creating dashboards or reports or any of the awesome stuff; What I do want to share with you in this primer is some information on the constantly evolving landscape within Office 365 that I myself had.  Admittedly, I’m putting the cart before the horse a little here since it all starts with your data sources and client tools like Excel, but I’m saving that stuff for a later post.

What is Power BI?

I couldn’t start a primer without a really big question first, right?  The term Power BI is used so much that it is easy to feel like it means that all business intelligence technologies are now under that umbrella.  However, I really think Microsoft intends the term Power BI to just mean the cloud based Power BI service.  Simple enough right?  Well, not quite because even when we boil it down to this service, there are currently two experiences available that Microsoft is working to unify.

Dueling Experiences

Power BI for Office 365

This is also referred to as the “current” experience, and you can find out more here.  It is available as an app that you add to any SharePoint Site that integrates with Excel and Excel Services.  More technically, it is what is referred to a Provider-Hosted App (PHA).  Essentially, from a SharePoint site you can launch the app and you are redirected to the provider hosted app with a URL similar to https://sites.powerbi.com/sharepointpages/SPIntegration/PowerBI/pages/PowerBI.html?SPHostUrl=https://domainname.sharepoint.com/sites/bicenter. Which might look something like below.

Power BI For SharePoint App

Power BI For Office 365 App

The first thing the app is doing is rolling up the Excel files from the SharePoint document libraries in your site into a more engaging end user experience.  From here, you can do many things including:

  • Enable a workbook to get a nice preview thumbnail generated from the first sheet in the workbook.
  • Mark a workbook as a featured reports to make them more visible to visitors of the app
  • Allow users to favorite the workbooks
  • Add a workbook to Q&A to allow Natural Language Queries against its data

Of those bullets above, the last one to me is the most exciting because anyone that has done much work with business intelligence knows that by its very nature people are going to want to ask new and different questions of the data.  Take the screen shot below, this was not a report I created but simply a question I asked of data that was in one of the workbooks.

Power BI For SharePoint Natural Language Query

Power BI For SharePoint Natural Language Query

The last few things I wanted to mention about Power BI for Office 365 before moving on, is that from what I’ve seen in a desktop browser the typical experience for viewing reports uses Silverlight.  This applies to both asking questions and when launching a report.  Note that launching a report is still rendering using Excel Services just as if you opened it directly from a document library.  When on mobile, launching a report does switch to HTML5 rendering, but asking questions of the data still appears dependent upon Silverlight at this time.  These are relevant when considering mobile and also lead into the “new” experience.”

Power BI Preview

This new experience is more of a standalone service in that it is not dependent upon a SharePoint site and Excel files being placed in document libraries.  The app resides on the URL https://app.powerbi.com.  Also you can launch it outside of Office 365 and login with your Office 365 account and can get to it from a tickler when you are in Power BI for Office 365.  For more imformation here.  This new experience is HTML5 based so it renders well on many form factors and there are even apps available for iOS and Windows Tablets.

Power BI Preview Dashboard

Power BI Preview Dashboard

My first question with the new preview service was, “Can I take an Excel workbook that I’d worked so hard on and just drop it in?”  The answer is a definite “yes”; you can use the Get Data function to bring in all sorts of data sources including Excel files.  In fact, Excel Files are just one of many possible data sources, a list that is constantly growing.  The list of data sources includes things that you’d expect like Excel, Azure SQL and SQL Server Analysis Services, but it also includes things like GitHub, Google Analytics, Salesforce and many others.  Interestingly, there is also support for a Power BI Designer File which comes from a new client tool that is available called Power BI Designer.  I’ll cover it more in another post about the client tools, but it exists for users who may not have Excel 2013 and still want to create reports in Power BI Preview.

The goal with all the data sources is that Power BI Preview becomes your single view for dashboards across all sources of cloud and on-premises data.  With all the available data sources, you don’t necessarily have to first go through Excel like you do with Power BI for Office 365.  Don’t get me wrong, that experience is still great, but who doesn’t love having more options?

In Power BI Preview, you’ll notice your data sources are shown as Datasets and then you have Reports.  These can currently come from Power View in Excel but not PivotTables or PivotCharts.  I suspect this will change overtime though.  You can also create reports by clicking on a dataset and using the Fields and Filters to compose a report or even by starting with a Natural Language Query and building upon it.

Finally, you have Dashboards where things all really come together.  You can compose these by pinning visualizations from Reports such as Power Views from Excel.  You can even pin the results of a Natural Language Query so that it’s easily viewed by others.

What is Not Power BI?

Okay, now that I’ve provided a little about what Power BI is, I also wanted to share a little about what it isn’t just to make sure there isn’t any confusion.  If you’ve been around earlier versions of SharePoint and done much with business intelligence, you’ve probably heard of a BI Center.  The good news is that this still exists and can be used with or without Power BI for Office 365.  You can find a little comparison information in Compare a BI Center site to Power BI for Office 365 sites.  Ultimately, a BI Center was and still is a special type of site in SharePoint.  You can still store and manage BI content such as data sources and Excel workbooks.   Depending on the user license, there are some behavioral differences, particularly as it relates to work book size when viewed online. See File size limits for workbooks in SharePoint Online for more.  There is also a table in the capabilities section of Power BI for Office 365 FAQ that compares feature differences.

Licensing Questions

Let me go on record stating that I don’t sell Office 365 quote licensing or get into that particular area.  I am a technology guy, after all.  Having said that, licensing has still confused me so I thought I would share some of my thoughts and findings to support them.

First, if you are just going “old school” with your business intelligence offering, I think you can take a purely BI Center approach as mentioned in Compare a BI Center site to Power BI for Office 365 sites you just need to have SharePoint Online (Plan 2).  You won’t get some of the cool stuff that comes with Power BI, and you’ll have some constraints, (see “What is Not Power BI above?”) but you also can save some on costs potentially.

Now if you want Power BI it is an add-on to Plan 2 part of a Power BI for Office 365 offering.  You can find those details here Power BI for Office 365 Service Description.  Within Power BI, just as there are two experiences there are two pricing models (Power BI for Office 365 and Power BI Preview) models but according to Power BI Transition these are being consolidated into one model.

Summary

I’m not sure why I always have to end on a summary, it just feels like the right thing to do I guess.  Hopefully I have answered some questions you might have and provided some helpful links to dig into things a little further in terms of what matters to you.  I hope to get into more “fun” topics in upcoming posts including building reports, charts and dashboards using all those Power [Whatever] tools like Power Query, PowerPivot, Power View and Power Map.

read more
Bo GeorgePower BI Primer
get-analytical.jpg

Basic Analytics with SharePoint Designer Workflow and Access Reports

Introduction

Analytics are a valuable resource to evaluate business processes to diagnose problems and implement efficiencies. There are many professional offerings that will provide analytics, however these tools can have high costs, both for licensing and developmental effort. As an alternative to this, you can create basic analytics with SharePoint data using only SharePoint Designer and Microsoft Office.

This is a basic example of simple analytics requiring only out of the box (OOB) SharePoint workflows and Microsoft Access. In this example, I will be interested in capturing and analyzing the timing of items in a SharePoint list as they move through certain statuses; however, this process could be adopted to any number of uses.

This guide is divided into two steps. In the first step, I’ll show how to create a SharePoint Designer workflow to capture and document status change events in a SharePoint list. In the second step, I’ll show how to use Microsoft Access to create some basic reporting on the status events captured by the workflow we created in step one.

Step One: Create a SharePoint workflow to log events

Firstly, I’ll assume that I have a SharePoint list that has a status field. In this example, the status field will have the following states: Cancelled, Implemented, In Progress, On Hold, Pending Update, Received, Rejected, Sent to Approver A, and Sent to Approver B.

Next, we will need a place to store the status change events that we will be capturing. A SharePoint list is a logical place to store these events. Create an Events list to store the following fields:

  • ChangeDate – the date and time when the change occurred
  • ChangedBy – who the change was performed by
  • StatusBefore –the status was before the change
  • StatusAfter –the status following the change
  • SourceID – the list item ID for the item that changed

BASD-1

Because this basic example will be using SharePoint workflows instead of List Event Receivers, we will not have access to the previous state of the status field. Therefore, we need to store the previous status value in the source list. To do this, I added a Previous Status field in the source list to store the previous value of the status field.

Note: Because this field should be used only by a SharePoint workflow, it would be a good idea to hide this from view of the user. My preferred method to accomplish this is to set the ShowInNewForm, ShowInEditForm, and ShowInViewForm properties of the SPField object (http://msdn.microsoft.com/en-us/library/office/microsoft.sharepoint.spfield_members(v=office.15).aspx)to False. These properties are not exposed in the UI so you will need to use PowerShell or SharePoint Manager 2013 (http://spm.codeplex.com/).

The next step is to create the workflow to log the status change events in the Events list. Open SharePoint Designer and create a new List Workflow for the list with the Status information.

For the first step of the workflow, we want to add a condition so that an event is only added to the Events list if the status has changed. Add an “If any value equals any value” condition.

BASD-2

Set the first value equal to the Status field, set the comparison to “not equals”, and set the second value to the Previous Status field.

BASD-3

Next, we need to add an item to the Events list to capture the status change event. Insert a “Create List Item” action beneath the condition.

BASD-4

For “this list,”select the Events list and add the fields that you want to capture. For this example, we will capture the following fields:

  • Modified -> ChangeDate
  • Modified By -> ChangedBy
  • ID -> SourceID
  • Previous Status -> StatusBefore
  • Status -> Current Status

BASD-5

Note: You could also create a link to the item that created the event by storing the Workflow Context: Current Item URL field. This may be useful to have a way to quickly get back to the item in question to get additional information.

The Last Step of the workflow is to store the current value of the Status field in the Previous Status field to signify that we have captured this status change. Add an “Update List Item” action.

BASD-6

Click “this list” and add the Previous Status field and set value to Current Item:Status.

BASD-7

 

The end result of the workflow should look like the below.

BASD-8

Step Two: Create Reports in Microsoft Access

The next step is to use Microsoft Access to connect to your Events list in SharePoint. In Access, select the External Data tab, select the More drop-down, and click SharePoint List.

BASD-9

Enter the URL of the site that houses the Events list and follow the steps of the wizard to connect to the list. If you would like the data to stay current, make sure you select “Link to the data source by creating a linked table” option.

BASD-10

Note: Alternatively, you can use the “Open with Access” option in the SharePoint Ribbon to create a linked table in Access.

BASD-11

Next, we need to create some queries in Access. The first finds the starting and ending time during which a particular item was in a certain status. I named this query StatusStartEndTime.

SQL:

SELECT Events.SourceID, Events.StatusBefore, Events.StatusAfter, Events.ChangeDate,

(

SELECT max(e.changedate)

FROM Events e

WHERE e.StatusAfter = Events.StatusBefore and e.SourceList = Events.SourceList and e.SourceID = Events.SourceID and e.ChangeDate < Events.ChangeDate

) AS PreviousChangeDate

FROM Events

WHERE Events.StatusBefore <> null;

The next query that I create uses the results of the above query to calculate the elapsed time (in minutes) that each item was in a certain status. I call this query StatusMinutes.

SQL:

SELECT StatusStartEndTime.SourceList, StatusStartEndTime.SourceID, StatusStartEndTime.StatusBefore AS Status, sum(DateDiff(&amp;quot;n&amp;quot;, StatusStartEndTime.PreviousChangeDate, StatusStartEndTime.ChangeDate)) AS ElapsedMinutes

FROM StatusStartEndTime

GROUP BY StatusStartEndTime.SourceList, StatusStartEndTime.SourceID, StatusStartEndTime.StatusBefore;

This last query is the building block that will allow us to drill down into some interesting information.

For example, let’s say we would like to know the average, minimum, and maximum time spent in each status. We could create the following SQL query:

SELECT DISTINCT (status), round(avg(ElapsedMinutes)) AS AverageTime, round(max(ElapsedMinutes)) AS MaxTime, round(min(ElapsedMinutes)) AS MinTime

FROM StatusMinutes

GROUP BY status;

BASD-12

We could of course use Access reports to display the data in a more aesthetically appealing manner.

BASD-13

From this information, we can extract information like which states take the longest to complete or which states have the most variability.

This is only the beginning of the analysis that you can perform. There are numerous other cases that could be analyzed with this data. Another useful case would be to find items that stayed in a certain state longer than a defined amount of time. With that information the cases could be investigated individually to determine why those particular items took longer than normal and whether any changes can be made to speed up the process.

Conclusion

While business intelligence and other analytics tools have their place, this example demonstrates that you can do some very basic analysis and reporting using OOB SharePoint and Microsoft Office tools.

read more
Grant LewisBasic Analytics with SharePoint Designer Workflow and Access Reports
lets-do-this.jpg

Free Office 365 / Azure / Salesforce / SharePoint Webinars for 2015

We’re excited to announce our Webinar Schedule for 2015 (all times in EST)…

  1. OneDrive for Business – Tommy Ryan – 1/23/15 @ 1:00pm – Registration – https://attendee.gotowebinar.com/register/6546469505055148801
  2. Migrating to Office 365 – Chris Edwards – 4/17/15 @ 1:00pm – https://attendee.gotowebinar.com/register/8454863250773402114
  3. Moving from Full Trust Code to the New Cloud App Model – Pete Skelly – 5/22/15 @ 1:00pm – https://attendee.gotowebinar.com/register/6134409931049990657
  4. Get Up To Date on Microsoft’s BI Offering – Bo George – 6/26/15 @ 1:00pm – https://attendee.gotowebinar.com/register/8891692623419306753
  5. Integrating Office 365 and Salesforce – Eric Bowden – 7/17/15 @ 1:00pm – https://attendee.gotowebinar.com/register/2558996029615612417
  6. Getting Started with Salesforce Development – Tim Coalson – 8/21/15 @ 1:00pm – https://attendee.gotowebinar.com/register/4631765663484917249
  7. Moving from Office 365 Dedicated to Multi-Tenant – Kirk Liemohn – 9/25/15 @ 1:00pm – https://attendee.gotowebinar.com/register/3796349032119339521
  8. Integrating Visual Studio Online and Office 365 – Lane Goolsby – 12/11/15 @ 1:00pm – https://attendee.gotowebinar.com/register/5900541608798828801

The schedule is subject to change (especially if presenters get overloaded on projects). Let us know in the comments if you have other topics that you would like us to cover.

Sign up below to get notified about upcoming events or follow us on twitter.


SharePoint is a web application platform in the Microsoft Office server suite. Launched in 2001, SharePoint combines various functions which are traditionally separate applications: intranet, extranet, content management, document management, personal cloud, enterprise social networking, enterprise search, business intelligence, workflow management, web content management, and an enterprise application store. SharePoint servers have traditionally been deployed for internal use in mid-size businesses and large departments alongside Microsoft Exchange, Skype for Business, and Office Web Apps; but Microsoft’s ‘Office 365’ software as a service offering (which includes a version of SharePoint) has led to increased usage of SharePoint in smaller organizations.

While Office 365 provides SharePoint as a service, installing SharePoint on premises typically requires multiple virtual machines, at least two separate physical servers, and is a somewhat significant installation and configuration effort. The software is based on an n-tier service oriented architecture. Enterprise application software (for example, email servers, ERP, BI and CRM products) often either requires or integrates with elements of SharePoint. As an application platform, SharePoint provides central management, governance, and security controls. The SharePoint platform manages Internet Information Services (IIS) via form-based management tooling.

Since the release of SharePoint 2013, Microsoft’s primary channel for distribution of SharePoint has been Office 365, where the product is continuously being upgraded. New versions are released every few years, and represent a supported snapshot of the cloud software. Microsoft currently has three tiers of pricing for SharePoint 2013, including a free version (whose future is currently uncertain). SharePoint 2013 is also resold through a cloud model by many third-party vendors. The next on-premises release is SharePoint 2016, expected to have increased hybrid cloud integration.

Office 365 is the brand name used by Microsoft for a group of software plus services subscriptions that provides productivity software and related services to its subscribers. For consumers, the service allows the use of Microsoft Office apps on Windows and OS X, provides storage space on Microsoft’s cloud storage service OneDrive, and grants 60 Skype minutes per month. For business and enterprise users, Office 365 offers plans including e-mail and social networking services through hosted versions of Exchange Server, Skype for Business Server, SharePoint and Office Online, integration with Yammer, as well as access to the Office software.

After a beta test that began in October 2010, Office 365 was launched on June 28, 2011, as a successor to Microsoft Business Productivity Online Suite (MSBPOS), originally aimed at corporate users. With the release of Microsoft Office 2013, Office 365 was expanded to include new plans aimed at different types of businesses, along with new plans aimed at general consumers wanting to use the Office desktop software on a subscription basis—with an emphasis on the rolling release model.

read more
Danny RyanFree Office 365 / Azure / Salesforce / SharePoint Webinars for 2015
steps-e1425509264318.jpg

PowerPivot Primer

PowerPivot for Excel and SharePoint 2010

PowerPivot is an exciting new addition to the Microsoft Business Intelligence stack that provides a lot of power to end-users.  For office savvy users who have Excel 2010 and the PowerPivot add-in, analysis against backend datasources is quick and powerful.  For users who may have less knowledge of the data or who aren’t as fluent with Excel, Workbooks with one or many Worksheets can be exposed through a browser to broaden the availability to any user who may have a need to analyze the data.

One of the latest additions to the Microsoft Business Intelligence stack is PowerPivot.  PowerPivot leverages the comfort and familiarity of Excel with the power of Analysis Services and other data sources to allow business users to analyze mission critical data without the need for IT involvement.

As a business user with Excel 2010 and the free PowerPivot add-in, a connection to a secured datasource can quickly be configured using AD or SQL Server permissions and slicing and dicing of data can be achieved in minutes.  However, there are times when personal manipulation and viewing of this data is not enough; the data needs to be shared with other users who may not be able to easily access it for any number of reasons (don’t have Excel 2010, don’t know the data, etc).  This is where PowerPivot for SharePoint 2010 comes in.  PowerPivot workbooks can be published to a PowerPivot Gallery in SharePoint 2010 where a user-friendly view of the data can be surfaced through the web browser along with the capabilities of slicing and dicing the data to get greater understanding of what is happening in the business.

With PowerPivot, tens of millions of rows can now be imported into Excel and manipulated in real time. This ability to handle massive amounts of data natively from the ubiquitous Excel application empowers business users with the ability to perform true real time analytics that previously required massive servers and tended to be painstakingly tedious.

While a blog doesn’t serve well to share all of the capabilities of this powerful set of tools, I would like to share a quick example of how to retrieve SQL Server Analysis Server (SSAS) data into an Excel 2010 PowerPivot workbook, format the names to be user friendly, and make the workbook available to end-users through SharePoint 2010.  Since many of you are probably familiar with the AdventureWorks 2008 database examples, I’ll start out with a very simple example that merely displays Sales totals that can be sliced and diced by Calendar Year, Product Category, Product Sub Category and Product.  I’ll then follow-up with something that is more visually appealing by adding a Chart as a second worksheet in the work book.

This walk-through begins with the assumption that you have Excel 2010 and that you have downloaded and installed the free PowerPivot add-in. The plugin can be found here.

The Walk-Through

Open Excel 2010 and note the new PowerPivot tab that is now available on the top row.

New PowerPivot tab available

Next, click on the PowerPivot Window button at the top left.

PowerPivot button

Note that you are presented with several data source options:  database, report, Azure DataMarket, etc.

In my example, I want to retrieve data from an Analysis Services database, so I specify the server and database as shown below.

After pointing to the correct database, a wizard will guide you through the steps of choosing the data that will be available in the Excel Workbook.

You can specify an MDX query or alternately select the Design button so that you are presented with a graphical interface to select your data.  In my example, I am selecting the Design button.

Now I can use the graphical interface to select the measures and dimensions that I want to see.

For this example, I want to look at Internet Sales Amounts and I want to see them based on Calendar Year, Product Category, Product Sub Category and Product so I drag the corresponding fields onto the design surface as shown in the following screen shots.

Now that I have selected the appropriate data elements, I click OK to continue through the wizard.

The corresponding MDX query is displayed based upon the facts and dimensions that have been selected using the designer.  Click Finish to continue.

A “Success” dialog is displayed to indicate the number of rows that met the criteria for the MDX query and the rows that were subsequently imported into the Excel Workbook.

So, now you can see the individual rows that were retrieved.  Note that the columns above have all been imported as Text data types.  Before you can leverage the numeric data in meaningful ways, you must update the data type to numeric which is shown below.

Select the column that you want to update and specify the data type and the display format.

Now you can close the PowerPivot window and return to the Excel Worksheet where you can begin to build meaningful displays that will help visualize the data.

In this simple example, I’m going to select a PivotTable, but as you can see I have several choices of how to present my data.

I’m going to put the PivotTable on the existing sheet which is Sheet1 in my example.

Now you can begin to build out your PivotTable by specifying the values to display as well as the “Slicers”.  Slicers are dimensional data that allow you to “slice” the fact data.  In our case, we will be able to slice the sales data by Calendar Year, Product Category, Product Sub Category and Product. As a general rule, each Dimension imported from the SSAS cube will be a slicer. For those who are familiar with SSAS cubes, this makes for a good analogy for what a slicer really does.

Select the Sales Amount field and drag it over to the Values column.

Specify a Custom Name for this amount if you like.  Also specify that we will be summing the Sales Amount.

Next, select the Calendar Year field and drag it into the Slicers Horizontal column.  Then, select the Product Category, Product SubCategory and Product fields and drag them to the Slicers Vertical column to achieve the display above.

Next, let’s give user-friendly names to the slicers by right-clicking on them and choosing the Slicer Settings.

Now, you can update the Display Caption to “Calendar Year”.  You can also change other items such as Sorting or Filtering if desired.

Lastly, we’ll update the Sales Amount field with a new caption and currency display as shown below.

(Note my total Sales Amount of $29,358,677.22.)

When I click the CY 2001 button in the Calendar Year slicer, note the sales amount changes to a much smaller number, $3,266,373.66.  This is because we are now only looking at sales for Calendar Year 2001.  Note also that the other slicers are updated to reflect what data applies to CY 2001.  So, we can see that only the Bikes Product Category was available in 2001.

Next, clear the Calendar Year filter by clicking on the red filter icon next to Calendar Year.

With the Calendar Year filter now cleared, click on the Clothing button in the Product Category slicer to see sales for Clothing.  Note that clothing was only available in Calendar Years 2003 and 2004.

Now that we have our workbook functioning, let’s navigate to the SharePoint PowerPivot gallery and upload the Excel Workbook.

First, access the PowerPivot Gallery on your site.

Within the PowerPivot Gallery, click the documents tab and select the upload document option.

Browse to the Excel Workbook and click Open.

Note that Workbook is now uploaded to SharePoint and the hour glass icon indicates that the thumbnail preview is being created. The thumbnail generation process can take several minutes.

When I refresh the page, I can now see that a thumbnail image has been created so that a User can easily see the visual display of the workbook along with a title and other information without opening the workbook.  Click on either image to navigate to the first sheet of the workbook in the browser.

Note that the look of the display is very similar to what was experienced in Excel.  Now users who access this SharePoint site can slice the data similar to what we did earlier in Excel.

In this scenario, I have picked the Bike Stands Product SubCategory.

While this display is very informative, it is not very visually compelling so let’s go back to Excel and add another Worksheet to this Workbook to make it more visually appealing.  Instead of updating the existing Excel Workbook, I have decided to make a copy and update the copy.  This will allow me to demonstrate the PowerPivot Gallery display of a Workbook with a single Worksheet and a Workbook with multiple Worksheets.  So, make a copy of the first Workbook and open the copy.

Navigate to the PowerPivot tab and select the PivotChart option.

Specify to add the chart to a new Worksheet.

Note that a new chart has been added along with the same field options as I had before.  Similar to before, I can now begin to select the fact and dimension values to create an appropriate display.

In this example, I chose to display Sales by Product Category with the ability to filter or slice the data based on Calendar Year.  Note that I also renamed the Worksheets to have more meaningful names.

Now, I want to upload my updated Workbook to the PowerPivot Gallery on the SharePoint site

Having navigated back to the PowerPivot Gallery, I navigate to the documents tab and select the Upload document option.

I select the “copy” to upload.

Note that I now have two PowerPivot Workbooks in the gallery.  As I hover over each of the smaller thumbnails, the larger thumbnail updates to provide a larger image.

Select the new worksheet to see the PivotChart.

Select the Calendar Year 2001button in the Calendar Year slicer to narrow down the data to only 2001.  Note that only bikes were sold during the year 2001.

read more
Tim CoalsonPowerPivot Primer
toe-dip1.jpg

PerformancePoint Primer

Introduction

If you have stood up SharePoint Server 2010 Enterprise, you have no doubt noticed that PerformancePoint is now a first class citizen. In MOSS 2007 there was integration, but it wasn’t the sort of integration that stares you in the face and begs to be taken for a spin. The improved integration and bundling will no doubt encourage more and more businesses to dip their toes into the business intelligence waters. But the water can be deep and the opposite shore can look dauntingly far away. So where do you start? My recommendation is to stick close to the shore at first, get your bearings and then work out your plan for crossing the ocean.

Staying Close To Shore

Starting with a proof of concept that leverages SharePoint lists is great way to get your feet wet. This is especially true if your company is not yet mature with full blown business intelligence solutions but does understand the power of custom lists in SharePoint. Even if you do have a robust helping of Analysis Service cubes to draw on for data, starting with SharePoint lists can still be a good way to get an idea of how you can organize your UI and establish how users will interact with it.

Some advantages of prototyping with SharePoint lists include:

  • Allows for rapid creation and modification of your underlying data
  • PerformancePoint manages pivoting the data for you and extracting your facts and dimensions
  • You can easily plug your data into KPIs, build scorecards and create dashboards
  • Your users can see a working solution without the time and expense of building a SSAS cube

Starting With What You Know

Whether you are a SharePoint administrator or developer, there may come a time when you are asked to dive into the business intelligence waters. Below is a brief overview of the approach I took as a SharePoint developer exploring these new found capabilities. If you are in need of some help with the deployment of PerformancePoint, there is a great PerformancePoint Services blog post that walks you all the way through to a Business Intelligence Center, which is where I begin.

First, you will need some data to work with. I started by creating two SharePoint lists that act as highly de-normalized data sources. My goal was to quickly create some data that could be displayed in PerformancePoint. The lists were very simple with essentially the same list schema to hold sales data and sales goals.

  • Product (Single line of text)
  • Product Category (Single line of text)
  • Purchase Date (Date and Time)
  • Product Cost (Number)
  • Purchase Quantity (Number)
  • Sales Amount Calculated (calculation based on other columns)

Naturally, you can create as many columns to describe your data as you need (I have used Product and Product Category). Make sure to leverage the SharePoint field types including single line text, numeric and date as these help PerformancePoint figure out the purpose of the data in a given column.

Going Into Deeper Waters

Once I created my SharePoint lists, it was time to launch Dashboard Designer. Dashboard Designer is the tool for managing your PerformancePoint content within SharePoint. I created a new data source using the SharePoint list template. On the Editor tab, I provided the SharePoint site and list information and chose to use an unattended service account. I could have selected the per-user identity, but I would have also needed to configure Kerberos.

On the Time tab, I chose my time dimension. The time dimension is important since you will most often want to see data broken out by month, quarter, year, etc. This is one place where PerformancePoint is trying to make sense of the field types from my list. Since Purchase Date was a date and time field, I selected it as my default time dimension and specified my time period levels.

Lastly, on the View tab, I adjusted the columns to suit my KPI and scorecard needs. PerformancePoint does a pretty good job at this already, but the option is there to change the column type. If you want to use it for slicing and dicing then select dimension and if you plan to aggregate data in it then select fact.

Time To Explore Further

With a useable data source created, it was time to really start tip-toeing out into PerformancePoint and set up my KPIs. The KPIs will be the building blocks for scorecards and dashboards.

I began by creating a new “Blank KPI” and naming it “Sales of Natural Things” since I planned to filter on the “Natural” product category. By default, a KPI has one actual and one target (goal), but it can have multiple actuals and targets. Clicking on the data mappings column allowed me to select my SharePoint list data source and choose my Sales Amount measure. Measures are created from my fact columns. I selected a dimension filter for product category and set my calculation to be sum of children. Sum of children allows for child items, such as products, to be summed for my KPI when I build my scorecard.

I recommend you do some exploring on the many other options available on a KPI including specifying numbers formats, deciding which visual indicators to use and providing thresholds for the icons of that indicator. This process can be tedious and time consuming. If you multi-select rows, you can actually bulk edit many of these settings. You can even copy and paste entire KPIs when you need to create similar KPIs.

To populate my new scorecard, I brought over my KPIs and dimensions from the tool pane on the left, making sure to stack them in the appropriate order. For some KPIs and dimensions I bumped them under others as children. I also changed the metric settings by right-clicking on my target cells and choosing the metric settings option. These settings allow me to modify how the indicator, variance, scoring and values are shown in this column. Finally, I spent a little time selecting rows and formatting the fonts and colors to break up the scorecard visually.

Admittedly, I haven’t done this phase of PerformancePoint justice, in part because it has so many options that it really requires a solid exploration to establish your bearings. I encourage you to spend a good amount of experimentation time here seeing the effects of different approaches to your scorecard. The exploration in this phase is why starting with simple SharePoint lists is so helpful. It allows you time to spend discovering your options with KPIs and scorecards before going full steam ahead.

Inviting Others Into The Water

Dashboards are the containers for your scorecards and other PerformancePoint content. They are how you get your hard work into your user’s hands so they can begin using what you have produced. Dashboards can be created using standard web part pages and there are sometimes good reasons to do this (covered in a future post).

For my prototype I used the standard dashboard available in Dashboard Designer. By dragging content (scorecards, reports or filters) onto a web part zone, I created a dashboard. I then gave my page a name and deployed it to the SharePoint dashboards library where end users could access it. The image below shows the scorecard after I expanded it to the details.

Conclusion

SharePoint 2010 coupled with the more tightly integrated PerformancePoint Server provides a lot of Business Intelligence features that your company or your clients will want to leverage. The SharePoint list data source provides a quick way to begin learning about the PeformancePoint KPIs, scorecards and dashboards and allows you to prototype solutions to help educate your business users about these new and powerful capabilities. And while KPIs and scorecards are not all that can make up this solution, they are a good start at dipping your toes in before going headlong into things like analytic grids and the decomposition tree which I will cover in an upcoming post.

read more
Bo GeorgePerformancePoint Primer
start-here1-e1425574385661.jpg

SharePoint 2010 BI Primer

Simple PerformancePoint Dashboard

SharePoint 2010 BI Primer Introduction

In any organization there are business users who need information to understand current and historical trends so they can make appropriate business decisions.  In a Financial role, this might be tracking Actual expenses to Forecasted expenses.  In a Sales role, this could be tracking Forecasted sales to Actual sales with a need to see this information broken down by Year, Region, State and Sales Person.  In a Services Management role, this might include tracking Actual and Forecasted performance metrics to ensure that Service Level Agreements (SLAs) with external customers are being met (or not) so that appropriate adjustments can be made to avoid financial penalties or an unsatisfied customer. This last scenario, tracking of key performance metrics, is one that ThreeWill was recently engaged to develop leveraging features of both PerformancePoint (PP) and SQL Server Reporting Services (SSRS).  The initial engagement consisted of creating a prototype that was demonstrated within a multi-million dollar Request For Proposal (RFP) meeting.  After winning the RFP, the client initiated a second engagement with ThreeWill to create a Production version of the earlier prototype that expanded the functionality to include a greater number of Service offerings and was more scalable and reliable.

In this blog and in subsequent blogs, we would like to share the Business Intelligence functionality that we leveraged in the development of the prototype and Production instance as well as “lessons learned” as we moved from a prototype to a fully functional Production system.  In this first blog, we would like to provide a brief overview of the functionality available in PerformancePoint and SQL Server Reporting Services that we leveraged.  In the subsequent blogs, we will drill-down into more of the nitty-gritty technical details and discuss how we “beefed-up” some of the native PP and SSRS presentation by leveraging JQuery and Ajax.  Also, we will discuss how we secured the application and data leveraging Active Directory, SharePoint Groups, Kerberos and SQL Server Analysis Server  Roles (SSAS).

What is PerformancePoint?

Dashboard Designer

PerformancePoint is a set of related objects that can be leveraged individually or together to provide meaningful views of data.  These views of data can take the form of Key Performance Indicators (KPIs) where actual values are compared against a target or goal value with a corresponding visual indicator to give a quick indication of the performance.  Or, these views of data can be more complex where drilling up and down in the data through dates (Year, Quarter, Month, Week) and other dimension hierarchies (Country, State, County, City) is possible using the Analytic controls in PerformancePoint.  KPIs and analytic charts and grids are a few of the objects included in PerformancePoint.  Other objects include dashboards, filters, scorecards as well as a very cool Silverlight control called the Decomposition Tree that integrates with the analytic controls to provide a “left to right” drill-down capability.  To work with and organize these objects in a meaningful way, a client application called the PerformancePoint Dashboard Designer is used.  PerformancePoint Designer reads and writes PerformancePoint objects to/from a document library that is hosted in a SharePoint site.  Filters, scorecards, KPIs, analytic grids and charts are all available to be hosted as web parts on SharePoint web part pages so they can be mixed and matched as needed or on the special PerformancePoint dashboard pages that come with tab-like navigation.  PerformancePoint objects can display data from a variety of data sources including Analysis Services, Excel Services, Excel workbooks, SharePoint list data and SQL Server tables and views.  During the initial prototype development, we primarily leveraged a SharePoint list data source so we could quickly create a custom SharePoint list and add data using the datasheet view.  However, we did eventually create an Analysis Services data source so that we could demonstrate some of the more visually compelling functionality available in the analytic chart and associated Decomposition Tree.

What is SQL Server Reporting Services?

Report Builder Content Types

SQL Server Reporting Services (SSRS) has been around for a while but the SSRS add-on to SharePoint surfaced for Windows SharePoint Services v3 and MOSS 2007.  In a nutshell, the SSRS add-on provides, in SharePoint vernacular, custom content types that can be applied to SharePoint document libraries.  These custom content types allow SSRS data sources, data models, and reports to be created and managed in SharePoint leveraging the security infrastructure that is included out of box.  If a user has access to update the document library, they can add new or update existing SSRS reports from within SharePoint using the click-once ReportBuilder application that comes bundled with the SSRS add-on for SharePoint.  By leveraging SSRS in SharePoint, reports can be easily previewed, printed and even exported to excel and other formats using the included Report Viewer.

While there is a lot of additional capabilities provided in SSRS, such as scheduling reports, the primary use of SSRS in our scenario was to provide access to detail data, allow exporting of the data, and to provide “ad hoc” report development by end-users using the ReportBuilder application.

PerformancePoint and SQL Server Reporting Services

As previously mentioned, we needed to build a solution that would provide Performance Metrics for the variety of services that our client provided to it’s customers.  The target audience for our client solution included two sets of users.  One set of users that we’ll call the Committee Users could see summary data for each of our services as well as the service data broken out by regions.  However, the detail data that rolled up to create regional and overall totals was not accessible by the committee users.  Our second set of users that we’ll call Regional Users could only see data for their particular region.  They could see this data summed up for each service as well as all of the details.

Define SSAS Role

To achieve this level of security, we were able to leverage SSAS roles.  While I won’t go into detail about the creation, configuration and assignment of SSAS Roles in this blog, it is significant to point out that securing the data within SSAS allowed us to share KPIs and other objects among the two user groups knowing that the data security would only display the data appropriate for the currently logged-in SharePoint user.  So users from all of the regions could log into the same region dashboard and would only see data for their region.

To provide a quick view of all of the Key Performance Indicators defined for each Service offering,  PerformancePoint KPIs and Scorecards were the primary focus of our effort.  KPIs were created for each individual performance metric and actual and target values were mapped for both current month and year to date.  The KPIs were grouped under the appropriate Service category and displayed in a PerformancePoint scorecard.  For each of the performance metrics, a green, yellow or red icon was displayed to indicate how well the actual performance values mapped to the targeted performance values that were agreed upon in the SLA.

Drill-Down using Decomposition Tree

In addition to seeing Summary data, the Regional users needed the ability to view and analyze the detail transactional data.  To provide visual views of the data that could be used for analysis, we leveraged the Analytic Chart.  Analytic charts provide the real “coolness” factor with the ability to drill-down into the data based upon the attributes or dimensions defined in the data. In our case (as in most cases), a date dimension was defined so we were able to drill-down into the details by Year, Quarter, Month and Day.  Analytic charts are flexible in that they can be presented as a bar chart, stacked bar chart, 100% stacked bar chart, pie chart or line chart.  Analytic charts are built/configured within the PerformancePoint Designer application where the measures, dimensions or named sets can be configured using drag and drop.  By right-clicking on an analytic chart, a number of options are made available including but not limited to drill-down, drill-up, sort, filter, pivot and the Decomposition Tree.  The decomposition tree always seems to be a crowd favorite as it provides a nice visual way to drill up and down the hierarchy.  The decomposition tree is built using Silverlight so Silverlight must be installed on the client for this option to be available.  Otherwise, it will not be displayed.

Besides being able to drill-down into the data and do visual analysis, there was also a requirement to see the raw detail data and be able to export this data into Excel for further analysis. SQL Server Reporting Services (SSRS) was a good fit for this requirement as we were able to define parameterized reports to show the detail data in a viewer that was searchable, printable and exportable into Excel and other formats.

Conclusion

Export Report Details to ExcelConclusion

Many organizations own SharePoint and take advantage of the document management features that have been available since the first version of the product.  However, SharePoint has continued to mature as both a product and a development platform, adding capabilities such as PerformancePoint and the SSRS integration that can be leveraged to solve business problems for both internal and external customers.  My goal for this post was to provide a high-level view of a real-world problem and how we leveraged PerformancePoint and SSRS to solve that problem.  In subsequent posts, we will share more detail about these features and some of the lessons learned as we integrated these features to provide a client solution.

read more
Tim CoalsonSharePoint 2010 BI Primer