SQL Saturday #130 Jacksonville Schedule Posted

sqlsat130_printSQL Saturday Jacksonville is just under a month away (April 28) and I’m happy to announce the we recently released the schedule for the event.  We have a great line up of speakers with a variety of topics for both DBAs and Business Intelligence developers.  You can register to attend the event here.

While the speakers may be all set for the event we’re still looking for sponsors.  If you’re interested in sponsoring our event please visit our sponsorship page.

Don’t forget we’re also offering a fantastic pre-conference seminar with Kevin Kline on Troubleshooting & Performance Tuning SQL Server that you can sign up for now.  http://sqlperftuning.eventbrite.com/

Choosing the Right Microsoft Reporting Technology Part 4: PerformancePoint

If you’ve followed this blog series from the beginning then you may have started thinking about which tools would be best for your environment.  If so that’s great and I’m glad I got you thinking, but I encourage you to keep an open mind as we go through the last couple tools because both PerformancePoint and Power View provide some of the most impressive visualizations that the Microsoft BI reporting tools have to offer.

If you’re new to reading this blog series I encourage you to start from the beginning even if you think you have a strong understanding of the tools detailed because you may reconsidered using tools that previous you dismissed as an option.  The tools discussed so far have been:

We have two more tools to go (listed below) and then a final wrap up post where I’ll show you how to use a decision matrix to make quick decisions based on your reporting needs.  The final two presentation layer tools are:

  • PerformancePoint
  • Power View

This week I’m excited to talk about one of my favorite Business Intelligence reporting tools called PerformancePoint.


What it is

PerformancePoint is one of those tools that when used right can create some of the most impressive and interactive reports within the Microsoft reporting tools.  While the tool can create reports, scorecards, filters, KPIs and indicators the main goal with PerformancePoint is to create dashboards.  Each of the items mentioned previously are component within a completed dashboard design.  For example, the screenshot below is a dashboard using NFL data which has brought together a filter, KPI, scorecard and two charts.  Each of the sub components of a dashboard are developed first and then brought together the make a completed dashboard.  The tool used for building theses dashboards is called Dashboard Designer and is primarily used by Developers and not end users.


I mentioned that the dashboards created by PerformancePoint are highly interactive, which is often dependent on the data source type used.  For example, if an Analysis Services cube is used any dimension hierarchies that are part of that design make it easy for users to drill up and down through that hierarchies.  A user could even completely change the attributes that are being displayed on a report as shown below.  While many data source can be used in PerformancePoint Analysis Services is preferred because it gives you the most bang for your buck when it comes to the interactive features.


If a user browsing the dashboard wants to change the report type within two clicks it can be changed to reflect their preference.  So if User A prefers bar charts and User B prefers grids that flexibility is built into the tool.


Another neat feature of PerformancePoint that users love is the Decomposition Tree.  Simply by right clicking on a value in a report you can launch a Decomposition Tree.  This part of the tool allows users to dig in deeper into the data so they understand how they arrived at the number the report displays.

In the Decomposition Tree shown below I started with all plays in the 4th Quarter(still looking at football data) and then I decided to look at those play by Down.  When I saw first down had the most plays (as you might expect) I decided to navigate through my team hierarchy that was in my cube, which stored the Conference, Division and Team.  Now at that level I wanted to see how many plays in this tree were runs vs. passes.  You see very quickly I was able to break down this information in an impressive visualization that helps me understand my data better (if you look closely it also explains why I’m a disappointed Jaguars fan).


Now that you’ve seen the end result of PerformancePoint development let’s talk about the tool at a higher level.  PerformancePoint is a service within SharePoint 2010.  So yes that means you must have SharePoint to use it.  The tool did exist in 2007 but was not as nicely integrated into SharePoint as it is now.  PerformancePoint originated as part of Proclarity but as you can see now fits nicely into Microsoft Business Intelligence.  Mark Stacey gave a brief history of PerformancePoint in this post.

The quickest way to get started with the tool is to use the SharePoint 2010 template site called Business Intelligence Center which has all the components needed to begin development.  Any objects created in PerformancePoint are saved into a SharePoint library called PerformancePoint Content List.  That means by default nothing you developed is saved locally although you can optionally save a workspace file on your machine with the content.

What it isn’t

While the final result of PerformancePoint is highly interactive for end users this is not a tool that the end users will actually using to develop their own reports.  So unlike Excel if a user wants a new dashboard created they will likely have to involve IT.  A power user could potentially take part in development, but I describe in the limitations section further down why I don’t recommend that.

I would not consider PerformancePoint an ad-hoc reporting tool although it does have some features described earlier that allow users to change a reports type and the content it displays.  The reason I don’t consider it ad-hoc is because the user is still limited to what the developer placed on the dashboard as far as filters, KPIs, scorecards, etc…

Also, the dashboards you develop in PerformancePoint are meant for high level reporting so it’s not a great idea to place detail level reports on a dashboard.  You may see performance issues if you do and because it is a detail report it will likely take up a lot of restate on the dashboard.  So an example of this would be you may use a scorecard that analyzes orders by year and product category, but you wouldn’t put line item detail information about an order on a dashboard.  Something like that may be better suited for Reporting Services.

Last, if you reports require a lot of customizations like column name changes or special colors on chart then you’re out of luck.  Basically whatever metadata comes from the source is what you get in the report.  So be sure to name measure and dimension attributes in your cube properly before bringing them into PerformancePoint.  Same for chart colors, there is no way to change the default colors that are provided to you in a bar chart for example.  If you need that kind of flexibility then again Reporting Services or Excel may be the tool you’re looking for.

Who Uses it

From the perspective of the report consumer the user can vary drastically.  Often PerformancePoint is thought of as an executive level reporting tool but I’ve seen users range from executive team members, department heads and even lower level managers.  In fact, I have done work for a major retailer that exposed PerformancePoint dashboards to the highest level of management and to individual store managers so they could make decisions that would impact the sales floor immediately.

From the perspective of the report author this is a developer tool.  There are a couple major reasons why end users typically do not develop PerformancePoint dashboards:

  • The end result is often a highly visible executive level report that should be under the care and maintenance of IT
  • Dashboard Designer, the tool used for creating PPS objects, uses a lot of terminology that is MDX related (Not end user friendly)

Having said that I have seen some corporate environments where a highly technical power user uses Dashboard Designer to create PerformancePoint dashboards after a little training.  They would of course need to have a strong understanding of how the data source is structured.

How is it consumed

Anything developed in PerformancePoint (at least in the current incarnation) can only be consumed through SharePoint.  In SharePoint 2010 the tool is already part of the installation process so after configuration you are ready to start.  In SharePoint 2007 you had to installed PerformancePoint 2007 separately which was a painful and tedious process.  Without SharePoint unfortunately you are out of luck.


While the product that results from PerformancePoint can be impressive the limitations you will find while developing can form a pretty hefty list:

  • Filtering dashboards can only be done by the report objects of Filters and Scorecards.  Meaning you cannot have a bar chart filter a scorecard when you select something.
  • Analysis Services drillthroughs have a lot of special conditions if you plan to use them.  I detailed that here.
  • You’re very limited on visual customization.  Things like bar chart colors cannot be changed.  What you see is what you get.
  • Showing two types of charts on the same report item is not ideal.  Meaning if I want a bar chart with a trend line through it then the trend line has to be a percentage value.  Otherwise it shows up as another bar.
  • Export options are limited.  You can only export items to either PowerPoint or Excel.

And there are a few more I haven’t listed but I think you get the idea.  Despite these I do really like the tool and most people find a way to work within the limitations when creating dashboards because it is generally the best tool for that purpose.


As we go through this series remember these high level characteristics about PerformancePoint

  • Used for creating dashboards
  • Only consumable through SharePoint
  • Analysis Services data source is preferred
  • Ideally not developed by end users
  • Highly interactive visualizations
  • Limited customization

I hope you’ve found this helpful and stay tuned for the Part 4 in this series on Power View. To read any of the other parts to this series follow the links below.

Choosing the Right Microsoft Reporting Technology Part 3: PowerPivot

Today I’ll be continuing in a blog series designed to help you decide which presentation tool is best for your reporting needs.  So far I’ve discussed in previous posts:

You learned how these two tools are almost opposites of each other.  Reporting Services is generally thought of as a static reporting tool, while Excel is usually used for ad-hoc reporting.  The products still left to cover are:

  • PowerPivot
  • PerformancePoint
  • Power View

Part three of the series will focus on PowerPivot.


What it is

PowerPivot is much more powerful than any of the other reporting tools that will be discuss in this series because it does much more than just product reports.  While it does have the ability to create some impressive reports it really cannot create any addition visualization that regular Excel PivotTables can’t already do.


The real impressive part about PowerPivot is the modeling part of it.  With PowerPivot you actually design a modeling layer that brings in the different objects that you choose.  I purposely use the word objects generically here because you can import any data source that you can think of into PowerPivot.  Whether you need to bring in a table from SQL Server, a flat file or even something from DB2 it can all be done very easily with PowerPivot.  In fact, even if I needed to bring in all three of those objects into the same PowerPivot document and relate them to each other that is possible too.  Within the tool you have the ability to create logical relationships that may not exist on the source systems.  The view below shows how the new PowerPivot 2012 has improved the way relationships are designed.  Previously designing these relationships was done through a single dialog box instead of the new graphic diagram view shown here.


PowerPivot uses a fairly new in-memory technology called xVelocity (formally called Vertipaq) to handle all of the report processing requests.  With xVelocity Excel is able to process hundreds of millions of rows with amazing response times from a desktop machine. The xVelocity engine uses in-memory column-oriented storage and a highly compression data storage to produced the results you see today.

Another key thing to understand about PowerPivot is that the data pulled into PowerPivot is actually stored in the document an import is performed.  That allows it to use the store engine described earlier.  The one problem with this is that the data is static until manual update is kicked off.  When the update is run all of the data in the PowerPivot document is reloaded backed back in file.  Unfortunately, there is not incremental update yet so if you have a significantly large data source it may take a while update.  Later when I discuss how PowerPivot can be consumed I’ll talk about how SharePoint can assist in automating this data refresh process.  SharePoint is really the true way to scale out PowerPivot so other users can utilize the reports you build.

What it isn’t

One worry that many IT staffers feel about PowerPivot is that power users will begin creating these documents and making decisions off of them while10 other users have created simpler but different documents and get different results.  If you see something like this happening in your environment, which is completely possible, you could create a full Analysis Services solution (either tabular or multidimensional) to replace the PowerPivot documents to ensure all these users a consuming a single source for their reports.

It is also important to note that PowerPivot is not a replacement for any ETL that performs data cleansing or applies business rules.  Most data warehouses have a set of ETL processes that perform these tasks and that way all users are looking at the same data set.  PowerPivot does not eliminate the need for this.

Who Uses it

The goal is to have power users the driving force behind the creation of PowerPivot documents.  This would be a person that understands the source database(s), business needs, understands the concept database relationships and understands Excel all fairly well.

While that may be the goal for who should be using PowerPivot that is not what I actually see happening in the field.  I am still seeing most PowerPivot implementations being completely controlled by IT.

Over the last year I have seen this start to change so it does become more user driven but I think the problem that is preventing more users from getting their hands on the tool is education.  A lot of clients that I visit either don’t know what PowerPivot is or if they do they’ve never be taught (even in a simple demo) how it works.  So until this changes we may see a lot of PowerPivot solutions started in IT.

How is it consumed

The two ways PowerPivot can be consumed is the either directly through Excel or through SharePoint.  PowerPivot documents that are used directly through Excel rely completely on the machines resources they are viewed from.  So for example if I create and use a PowerPivot document on my laptop then it use all the resources of my laptop for importing data and processing results.  If I wanted others to see this report using this method I would have to either place the file on a shared drive or email it to those I want to view it.  That sounds terrible!

The best way to scale PowerPivot so that it is usable by a larger number of users is to setup SharePoint integration.  When PowerPivot for SharePoint is installed any reports viewed from a PowerPivot Gallery (SharePoint library for PowerPivot) run using a special Analysis Services instance to do all report processing rather than your laptops resources as previously described.  You may have noticed going through the SQL Server 2008 R2 install that there is an option to install SQL Server PowerPivot for SharePoint.  Using this SharePoint integration not only allows you to using Analysis Services for report processing but also allows you to schedule data refreshes, which is a huge help because normally data refreshes are a manual process without SharePoint.



The major known limitations with PowerPivot are experienced when SharePoint is not part of the solution.  All of these have been detailed previously but as a reminder:

  • Refreshing report data in PowerPivot is a manual process
  • PowerPivot uses the resources of the machine the file is on
  • Sharing a PowerPivot reports can be difficult for large documents (shared drive or email)

PowerPivot for SharePoint of course addresses each of these limitations.  One other limitation that I have not detailed yet is working on 64 bit vs 32 bit PowerPivot.  I highly recommend if you use PowerPivot that you only do it with the 64 bit version.  You will find out very quickly when you begin importing data sources into you document that without 64 bit the process can be slow and painful.  You might even run into some limitations with the amount of data it will import on a 32 bit instance.  With 64 bit PowerPivot the sky is the limits though!


As we go through this series remember these high level characteristics about PowerPivot:

  • For power users that can understand a little modeling and relationships
  • Can bring in multiple unrelated data source together for reporting
  • SharePoint is the right way to make PowerPivot scalable

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PerformancePoint. To read any of the other parts to this series follow the links below.

SQL Server 2012 Data Mining add-in for Excel 2010

Another release that went without much trumpeting was the SQL Server 2012 Data Mining add-in for Excel 2010.  This has been a much awaited release for those vested in Data Mining. 

The current release was only available for Excel 2007 and if you tried to use it in Excel 2010 it only worked for 32 bit machines.  You’ll find now a 32 bit and 64 bit version available now.

Go download and start playing with the latest release here.


PowerPivot 2012 RTM Release

Somewhat overshadowed, for obvious reasons, by the big RTM release of SQL Server 2012 was the RTM release of PowerPivot 2012.  You can go download the much improved version of PowerPivot for Excel here.


To give you a fair warning there are some prerequisites to installing that were not required in the previous version of PowerPivot that you can find on the download site:

Also, here’s a few post I wrote several months ago that will help you learn the latest release:

Choosing the Right Microsoft Reporting Technology Part 2: Excel

This post is a continuation of a series started to help you understand each of the Microsoft presentation layer tools purpose and when you should choose each for your reporting solution.  The decision to pick the appropriate tool is difficult but so very important as I detailed in part one of this series.  As I continue the discussion here again are the different tools I will detail:

    • Reporting Services
    • Excel (From the perspective of building PivotTables without PowerPivot)
    • PowerPivot
    • PerformancePoint
    • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

In part one of the series you learned about Reporting Services and the role it plays mainly as a static reporting tool.  Here in part two we will discuss the polar opposite, which is Excel.


What it is

As you can see in my list I’ve made sure to differentiate Excel and PowerPivot as two separate tools even though PowerPivot is part of (free add in) Excel.  So to be clear when I say Excel I’m referring to Excel PivotTables off of an Analysis Services data source.  Of course any business user will tell you that PivotTables are possible without an OLAP data source, but to keep this relevant to Business Intelligence I will focus on Analysis Services as a data source.  With that focus in mind let’s discuss the tool.

Excel is customarily thought of as an ad hoc reporting tool that is great for getting a quick answer from your data source.  This is where quick analysis can be done without having to wait days for a report developer to define specs, write a source query and then finally build the report.  The report below for example was literally built in less than a minute.


Usually when I teach Analysis Services to a group my first question is, “How many of you have dozens of reports waiting for you or someone else to build when you get back to work?”  Predictably about half the room raises their hand and I go on to explain that part of the goal when building an OLAP database is to take much of the reporting burden off of you and move that to your more savvy end users that are comfortable in Excel.  This is possible because after you have built the Analysis Services layer Excel can consume it in a way that give users the ability to drag and drop fields into a PivotTable with very little training required.  This was one of the earliest forms of Self services BI.  So now many of those reports that are waiting in your queue can likely easily be created by the users themselves.  Of course this doesn’t solve all reporting needs for users but it can definitely relieve some stress.

Because Excel is most end users comfort zone you will find they need little training.  In fact admittedly I have learned a thing or two from end users about reporting in Excel.  Your diving into the end user’s bread and butter and that is why this is such a popular option for reporting.  Be wary though because end users love it so much they may start going beyond Excel’s intended purpose as a reporting platform.

What it isn’t

While Excel is great for cranking out reports quickly many think it lacks some customization features.  Now I happen to think this is partially a myth because we’re IT folks and don’t play in Excel all day.  Like I said earlier I’ve had too many experience where an end user has taught me something I didn’t think was possible in Excel.  Even though I identify it as partial myth I would still argue that tools like Reporting Services are far more customizable with with reports they produce.

Excel also is not necessarily the right tool for static reports.  As mentioned earlier it’s purpose is for ad hoc reporting, but if you use it for more permanent reports it’s not the end of the world.  You may be just miss out on the benefit of other reporting tools.  For example, let’s say your sales team looks at a weekly report of their previous weeks sales compared to last year.  If this report was in excel they could either open the file each week or view it in SharePoint with Excel Services, but the better solution may be to have it emailed directly to the sales team members using a Reporting Services report and subscriptions.  Having it in Reporting Services also opens the doors for features like report caching if performance becomes a problem.

Who Uses it

No long explanation needed here because this tool is for end users as previously detailed.

A couple caveats I’ll mention here are:

  • Excel is a far superior tool for testing KPIs than the built in Analysis Services KPI browser.
  • In SQL Server 2012 Excel becomes the primary way that developers test tabular BISM designs.

How is it consumed

The two typical ways of consuming Excel PivotTable reports are opening the .xls or .xlsx file itself or SharePoint with Excel Services.  Ideally your company would go the Excel Services route otherwise an unorganized chaos of workbooks being emailed or placed on shared drives can become a mess.

Excel Services allows you to place the workbook in a web facing version of Excel.  It does not require that Excel is installed on the users machine because they can view the Excel workbook directly through their web browser.  Any content deployed to Excel Services is managed through SharePoint content databases.


One major limitation some find is depending on the version of Excel you are running that row limits exist.  I would argue that you are not using the Excel PivotTables for their intended purpose if you get anywhere near those limits.

I have also found that occasionally Excel can write some rather poor MDX against a cube with the drag and drop interface and unfortunately you do not have much control over it.  There is a nice free tool available called OLAP PivotTable Extensions which allow you to right-click on a cell and it provides the MDX that led to the results.  This can be pretty helpful when you’re first learning MDX (the query language for Multidimensional Analysis Services).


As we go through this series remember these high level characteristics about Excel:

  • Most end user preference
  • For ad hoc report development
  • Can be used for static reports but may not be best fit
  • Excel Services with SharePoint best way to store and share reports

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PowerPivot.  To read any of the other parts to this series follow the links below.

Choosing the Right Microsoft Reporting Technology Part 1: Report Services

As Business Intelligence has evolved over the years the number of tools we have to choose from for presenting data has advanced drastically.  With so many tools to choose from it can be rather confusing (especially when your company is just getting their feet wet in BI) to determine which tool is the right one for an organizations reporting needs.  Just within the Microsoft suite of tools (not including third-party tools) you have Reporting Services, Excel, PowerPivot, PerformancePoint and Power View.

Many companies try to marry themselves to one or two reporting tools and fit their needs into the restrictions of the tool(s) they have chosen.  The truth is not a single one of these tools can solve all reporting needs.  While each one of these tools by themselves may be able to present your data, you will find that using a combination approach will conclude in a much more well rounded and impressive reporting solution.

That’s why in this blog series I hope to demystify the decision process and educate you a little bit on each of the tools so that you can easily determine which tool would be best in different scenarios.  Like I mentioned earlier the Microsoft reporting tools we will cover will be:

  • Reporting Services
  • Excel (From the perspective of building PivotTables without PowerPivot)
  • PowerPivot
  • PerformancePoint
  • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

After discussing each of the tools each individually I’ve bring it all together by showing you how to use a decision matrix to determine which tool makes sense for your work examples.

Reporting Services

What it is

Reporting Services is traditionally used for developing static reports.  The word static here is referring to the report layout rather than the report data (obviously the data will change).  For example, I want my sales team to be able to review a line item detail of each of their sales from the prior day.  With Reporting Services I could have the sales team either log onto a web front end to view a live version of the report or I could have the report emailed to them daily.


The reports developed with the tool are highly customizable.  In fact just about any object of the report can be manually changed or made dynamic with the expression language it uses.

If you’re looking for a tool for creating dashboards then Reporting Services also has the capability of filling that need.  With charts, gauges, indicators, sparklines and maps you have many of the necessary tools for creating an impressive dashboard.  There are other tools that like PerformancePoint that can create more impressive interactive dashboards but Reporting Services can get the job done.

Reporting Services is also highly scalable.  Without much effort the workload for report processing can be shared across multiple instances of reporting services.  After installing multiple instances the native wizard guides you through creating a farm of report servers.

Another thing worth noting because it will not be true of all the other tools I discuss in this series is that it can connect to virtually any data source you have.

What it isn’t

While there are some parts of Reporting Services that can provide ad hoc reporting capabilities the core of the tool is for static reporting.  If you really have a need for ad hoc reporting and could care less about report layout than you would likely pick another tool like Excel.

Who Uses it

While Reporting Services does have functionality for a power user to create their own reports it is typically thought of as a developers tool.  So report developers would use the tool Business Intelligence Development Studio (BIDS), which is part of the SQL Server install) to create incredibly detailed reports with almost endless flexibility when it comes the visualizations.

The tool that power users can use for creating reports is called Report Builder.  It has almost all the same functionality as BIDS but has been simplified a little for end users with more wizards and easier ways to bring in datasets.  Because it has a lot of the same functionality of the developers too you will find that this tool is for highly technical end user.

How is it consumed

Like I mentioned earlier Reporting Services reports can be consumed through a web front end of the native Report Manager, SharePoint, or even embedded in a custom application you’ve written with the Report Viewer control.

In addition to viewing the reports from a web browser they can also be delivered directly to the users via email or in a shared folder that they can access.  This is done using an incredibly useful part of the tool call Subscriptions.


Again, Reporting Services is a static reporting tool therefore if your users find the need to change report layout frequently than that may eliminate this tool as an option for that particular report.

Another limitation you may run into is some of the functionality with Subscriptions, the report delivery tool mentioned earlier, are enterprise only features.  For example, if you wanted to make the reports delivered dynamic based off a list of emails you have in the database that must be done using Data Driven Subscriptions, which are Enterprise only.


As we go through this series remember these high level characteristics about Reporting Services:

  • For static report development
  • Extremely customizable
  • Dashboard capable
  • Highly scalable
  • Traditionally thought of as a developers tool
  • Has a native web front end but can also view reports through SharePoint and direct delivery with subscriptions

I hope you’ve found this helpful and stay tuned for the Part 2 in this series on Excel.  To read any of the other parts to this series follow the links below.