Atlanta BI SIG September Meeting

Atlanta BI fans, join our next Atlanta BI SIG meeting! Mark Tabladillo (Ph.D., Industrial Engineering, MCAD.NET, MCT) will show us how to do data mining with PowerPivot. And Dundas will demonstrate their latest BI offering – the Dundas dashboard. Here are the details:

Please RSVP to help us plan food as follows:

  1. Go to the Atlanta BI home page (atlantabi.sqlpass.org).
  2. Choose Yes and submit the RSVP survey found at the right top corner of the page.

 

Main Topic:         Data Mining with PowerPivot 2010
  Level: Intermediate
Date: Monday, September 27, 2010
Time: 6:30PM
Location Matrix Resources

115 Perimeter Center Place

Suite 250 (South Terraces Building)

Atlanta, GA 30346

Speaker:

 

 

 

 

Mark Tabladillo (Ph.D., Industrial Engineering, MCAD.NET, MCT)
Mark Tabladillo provides consulting and training for data mining with Solid Quality Mentors. He has taught statistics at Georgia Tech and for the graduate business school of the University of Phoenix. Mark has years of deep experience with the SAS System, and has presented at many local, regional, and national technical conferences. Mark produces a data mining resource and blog at http://www.marktab.net. the current president of the Kansas City SQL Server Users Group.
Overview:

 

Excel provides a compelling and ubiquitous interface for Microsoft Data Mining. With new features available through PowerPivot, business users can apply the technology through a well-designed infrastructure of Microsoft technologies. This presentation will welcome any newcomers to data mining, and provide interactive demos which highlight data mining through these technologies.

  

Location: Matrix Resources Dunwoody Office
Sponsor
Presentation:
Dundas

Dundas will present their latest BI offering: Dundas Dashboard. Dundas Dashboard is a flexible, turnkey solution for the rapid development of business dashboards. Whether you are leveraging an existing BI infrastructure/application or starting a standalone project from scratch, Dundas offers the industry’s most cost-effective platform for creating/deploying sophisticated digital dashboards and empowering users quickly and easily.

Analysis Services Processing Performance

Analysis Services has a very efficient processing architecture and server is capable of processing rows as fast as the data source can provide. You should see processing rate in the ballpark 40-50K rows/sec or even better. One of my customers just bought a new shiny HP ProLiant BL680c server only to find out that processing time went three times higher than the old server. I did a simple test where I asked to execute the processing on both the old server and the new server. The query on the old server would return all rows within 2 minutes, while the same query would execute for 20 minutes which averages to about 4K rows processed/sec. This test ruled out Analysis Services right off the bat. It was clear that the network is the bottleneck. Luckily, the server had a lot of processing power, so processing wasn’t ten times slower.

As it turned out, the company has a policy to cap the network traffic at the switch for all non-production subnets or security and performance reasons. Since the new server was still considered a non-production server, it was on plugged in to a restricted network segment. The moral of this story is that often basic steps could help you isolate and troubleshoot “huge” issues.

DynamicHeight Bug

The chart region in Reporting Services 2008 introduced the ability to dynamically size charts by setting the DynamicHeight and DynamicWidth properties, as Robert Bruckner explained in his blog. This feature is really useful and I hope one day it makes to the other regions as well. A customer recently reported an issue with their reports where regions would overlap when the report is previewed in Print Layout mode or exported to a hard page renderer, such as PDF. For example, in the report below the radar chart is positioned after the bar chart in RDL. However, in Print Layout preview the radar chart overlaps the bar chart. The customer tried every possible combination to enclose one or more regions in rectangles which helped avoiding the overlapping issue to some degree but introduced other issues.


After some digging, I discovered that the issue is caused by the fact that the bar chart is configured for dynamic height and managed to confirm that this is a bug. I will post an update when I learn more. Meanwhile, one possible workaround is to re-arrange the report so the region with dynamic height (hopefully, it’s only one) appears last on the report.

UPDATE (9/18/2010)

Robert Bruckner provided the following workaround which fixed the DynamicHeight issue for me:091610_1142_DynamicHeig1

1. Add a table with a single static cell (one row and one column).

2. Delete the table Details group.

3. Nest the chart in the table.

Importing SSAS KPIs in PerformancePoint

Forging ahead through the unchartered land of PerformancePoint 2010, I ran into a snag today. Attempting to import Analysis Services KPIs resulting in the following error:

An unexpected error occurred.  Error 47205.

Exception details:

System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

File name: ‘Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’

   at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(DataSource asDataSource)

   at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)

Since in our case, PerformancePoint was running on a SharePoint web front end server (WFE) which didn’t have any of the SQL Server 2008 components installed, it was clear that PerformancePoint was missing a connectivity component. Among many other things, I tried installing the Analysis Services Management Objects (AMO) from the SQL Server 2008 Feature Pack but the error won’t go away. I fixed it by running the SQL Server 2008 setup program and installing the Client Tools Connectivity option only. Then, the KPIs magically appear in the Scorecard Wizard.

090710_1533_ImportingSS1

PerformancePoint 2010

PerformancePoint? Is it still around? It is (in SharePoint 2010), and it should peak your interest if you are serious about dashboarding. The planning component is of course gone and I have to admit I never had too much faith in it. When comes to dashboards, Microsoft gives you two implementation options:

  1. Reporting services reports in SharePoint web parts – Pros include low cost because Reporting Services is available with SharePoint Foundation, and no need to learn new skills. On the downside, you need to implement your own global filter web parts assuming that you don’t use SharePoint Server.
  2. PerformancePoint – This is tool specifically designed for dashboards and it just got better in SharePoint 2010. However, it requires SharePoint Server 2010 which you need for PowerPivot as well. Unfortunately, this puts you in the $5,000+ upfront investment bucket (Vidas has more to say about SharePoint pricing).

Personally, I was pleasantly surprised when I re-discovered PerformancePoint in SharePoint 2010. Here is a cool little dashboard I put together in a couple of hours after importing the Adventure Works KPIs.

090310_0251_Performance1

I’ve been complaining for a while that Microsoft doesn’t have a web-based OLAP browser. PerformancePoint reporting capabilities (chart and grid) come pretty close. Below is a grid report bound to the Adventure Works reseller data. It would be really cool if PerformancePoint continues the trend to fill in the gap and adds more Excel-like features, such as filters, slicers, etc.

090310_0251_Performance2

Yes, we now have the ProClarity remnants in the form of a Silverlight-based decomposition tree (requires Silverlight 3.0 on the client). To get it, I right-clicked a cell on the report and clicked Decomposition Tree. This lets me analyze sales by any dimension.

090310_0251_Performance3

So, what’s the catch except the cost? The ridiculously difficult Kerberos configuration of course if you have a multi-server environment. In our case, just when we thought we conquered the Kerberos beast with SSRS, we’ve found the PerformancePoint doesn’t work. As it turned out, unlike Reporting Services, PerformancePoint requires a constrained delegation and uses the Claims for Token service. So, follow the steps in the Configuring Kerberos Authentication for SSRS 2008 R2 with SharePoint 2010 whitepaper closely.

If you have SharePoint 2010 Server already, PerformancePoint definitely warrants your interest.