Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for May, 2010

PerformancePoint Server 2007 Build List and Hotfix Update

Posted by denglishbi on May 22, 2010

Occasionally as I run into something odd with PerformancePoint Server or Dashboard Designer I will take a peek out on the Microsoft Support site to see if there are any new hotfixes available.  I had to admit that this is not a fun task by any means because of how the KB article titles are named and the results that are displayed in the search, plus there is no sorting capabilities (at least none that I am aware of).  What I end up doing is running a PerformancePoint Server 2007 Hotfix search on the support site.  Then once I have the results I review the KB article numbers to look for any results that are higher than the previous hotfix or SP that I am aware of.  Not a fun task, but at least it is something.  Once I find these I then review the fixes to determine if there is anything important that might be worth implementing or if it possibly resolves an issue that I am currently experiencing.PerformancePoint 2007

What I have found out is that since SP3 there have been four hotfixes released, the first one was for Planning and the last three were for Monitoring & Analytics.  The only reason that I found out about the last one (which is currently not publically available yet, but should be very soon) was because I contacted some people on the PPS team directly.  I am trying to get this content to be more publically facing, easier to access and get updates on, and also to possibly look into creating a setup similar to what SQL Server has with their Connect site (so that we can submit bugs, suggestions, and review the status) like this example – OLAP Browser.

Here is the complete list of build numbers for PPS 2007 since RTM along with the links to the KB articles.  Just a reminder, the hotfixes are cumulative, so if you install the latest one it will include all prior fixes and service packs as well.

Build Number KB Article Title
3.0.4512.00 KB 2401959 PerformancePoint Server 2007 hotfix package: September 14, 2010
3.0.4510.00 KB 983228 PerformancePoint Server 2007 hotfix package: June 14, 2010
3.0.4506.00 KB 982016 PerformancePoint Server 2007 hotfix package: April 21, 2010
3.0.4504.00 KB 979684 PerformancePoint Server 2007 hotfix package: February 11, 2010
3.0.4502.00 KB 978617 PerformancePoint Server 2007 hotfix package: January 8, 2010
3.0.4501.00 KB 977619 PerformancePoint Server 2007 hotfix package: November 30, 2009
3.0.4417.00 KB 975250 PerformancePoint Server 2007 Service Pack 3
3.0.4318.00 KB 971928 PerformancePoint Server 2007 hotfix package: June 17, 2009
3.0.4317.00 kb 970524 PerformancePoint Server 2007 hotfix package: May 26, 2009
3.0.4315.00 KB 969842 PerformancePoint Server 2007 hotfix package: April 28, 2009
3.0.4314.00 KB 968471 PerformancePoint Server 2007 cumulative update package: March 31, 2009
3.0.4313.00 KB 968131 PerformancePoint Server 2007 hotfix package: February 26, 2009
3.0.4312.00 KB 967900 PerformancePoint Server 2007 hotfix package: February 24, 2009
3.0.4311.00 KB 967343 PerformancePoint Server 2007 hotfix package: February 9, 2009
3.0.4305.00 KB 961878 PerformancePoint Server 2007 hotfix package: February 2, 2009
3.0.4300.00 KB 961041 PerformancePoint Server 2007 hotfix package: December 22, 2008
3.0.4209.00 KB 958291 PerformancePoint Server 2007 Service Pack 2
3.0.4108.00 KB 957166 PerformancePoint Server 2007 hotfix package: August 29, 2008
3.0.4107.00 KB 956553 PerformancePoint Server 2007 hotfix package: August 21, 2008
3.0.4105.00 KB 955751 PerformancePoint Server 2007 hotfix package: July 28, 2008
3.0.4104.00 KB 955432 PerformancePoint Server 2007 hotfix package: July 14, 2008
3.0.4103.00 KB 954710 PerformancePoint Server 2007 hotfix package: July 1, 2008
3.0.4102.00 KB 953766 PerformancePoint Server 2007 hotfix package: June 13, 2008
3.0.3917.00 (Planning) KB 948060 PerformancePoint Server 2007 Service Pack 1
3.0.3916.00 (Monitoring) KB 948060 PerformancePoint Server 2007 Service Pack 1
3.0.3715.00 KB 951326 PerformancePoint Server 2007 hotfix package: April 11, 2008
3.0.3714.00 KB 949690 PerformancePoint Server 2007 hotfix package: March 3, 2008
3.0.3712.00 KB 948718 PerformancePoint Server 2007 hotfix package: February 7, 2008
3.0.3711.00 KB 947262 Cumulative Update 1 for PerformancePoint Server 2007
3.0.3709.00 KB 946222 PerformancePoint Server 2007 hotfix package: December 19, 2007
3.0.3705.00 KB 945663 PerformancePoint Server 2007 hotfix package: November 29, 2007
3.0.3703.00 KB 944842 PerformancePoint Server 2007 hotfix package: November 15, 2007
3.0.3702.00 KB 944452 PerformancePoint Server 2007 hotfix package: November 8, 2007
3.0.3701.00 KB 944387 PerformancePoint Server 2007 hotfix package: November 1, 2007
3.0.3523.00 – 3.0.3524.00   RTM – International
3.0.3520.00   RTM – English


You can also check out the build information on the TechNet site, http://technet.microsoft.com/en-us/library/cc298347(office.12).aspx, but this is not a complete list.  If you are using PerformancePoint I would highly recommend installing the current latest public available hotfix to get to build 3.0.4504 because that resolves an issue of unnecessary MDX being issued, so it will reduce overhead and improve load performance.  The latest hotfix (for 3.0.4506) that will be coming out shortly (and I guess another one will be coming out next month in June – not sure what that one will fix) resolves the following issues:

  • Character limit for MDX queries is too low
  • SP3 regression causes multiple font sizes to display in scorecard

I am hoping that this list will possibly be made available on the PPS Team Blog and hopefully at some point some sort of mechanism will be made available so that we can post bugs and suggestions so that we can have a more direct link to the product team to work on not only improving the product but also getting more direct input as to the direction that it is heading.

A couple of items that I have on my list, and these are just a couple, would be

  • cascading parameters (filters) like Reporting Services – I believe we will actually see this for PPS 2010 later this year
  • improvement in Tree filter support that have more than 500 members – way to slow and the list view is not pretty when dealing with hierarchies (apparently improvements have been made in PPS 2010, but that does not help for PPS 2007 users)
  • please get the PerformanceMap incorporated in PPS 2007 (and 2010).  This is an extremely powerful visualization that is in ProClarity and in my opinion more important than the eye candy Decomposition Tree.  The ability to analyze two metrics simultaneously and interact with them provides a ton of insight and analytical power to the end-users.  I don’t think this will happen for 2007 just like Decomposition Tree was not and displaying the ProClarity views is just not the same.

Well that is all I have for now.  Apparently there should be a posting in the near future on the PPS Team Blog to provide the steps needed to display a Decomposition Tree report as a default report view in a PPS 2010 dashboard, so keep monitoring their blog.  If you have any other comments or suggestions make sure you leave comments on their blog postings.  The more we make our voices heard the sooner we should see results.  We need to do our part to help shape the product as well.

Updated (7/7/2010): Link for the most recent hotfix has been officially published now for KB 982016.  Included the link in the list.  A new searching mechanism is now available, at least I believe this is new – TechNet Support.  Just in, new June 2010 hotfix has been released that includes a couple of new fixes – KB 983228.

Updated (12/24/2010): A new hotfix was released for Planning when opening a form in Business Modeler – KB 2401959.

Posted in PerformancePoint | Tagged: | 1 Comment »

SQL Server 2008 R2 MN Launch Follow-up

Posted by denglishbi on May 20, 2010

Last week PASS and Microsoft had one of its five major launch events in Minneapolis.  I recall last time we had the major launch here was back in 2008 on April 1, so I felt a little better about the date for this one.  The reason that I was so excited for this one was because this time I got to be involved since I help run and organize theSQL Server 2008 R2 Launch local PASSMN Chapter; plus Jason Strate who is a SQL MVP and also on our local Chapter board was able to help out and present as well.  Not only did I get to do the welcome and keynote introduction I also got to do a presentation on a subject that is near and dear to me – Lower Costs through BI Consolidation.

I got a lot of positive feedback from the attendees saying

  • that they they were wowed by PowerPivot (who isn’t and Jin did an outstanding job),
  • loved the new SSRS 2008 R2 enhancements (Lara rocked the house as always),
  • were very interested in Master Data Services (wanted to see an actual demo though),
  • and are pondering the possibilities of using SQL Azure.

We had around 320 people in attendance which was outstanding and everyone that was involved did an outstanding job of organizing the event and making sure everything was ready including the staff at the Bloomington Sheraton hotel.  I know from personal experience that putting together an event like this not an easy task at all and I am extremely grateful that we were able to participate in this event.

The part that I truly enjoyed was being able to talk and network with everyone at the event.  I was able to meet new people in the community, talk with former co-workers, talk to customers, evangelize about SQL Server, listen to what people are doing with SQL Server, talk with the PASS and Microsoft employees (and Sponsors), and let everyone know that ‘yes’ we do have a local SQL Server user group!

If you haven’t had a chance to check out the new version of SQL Server 2008 R2 yet here are a few links to get you started:SQL Server 2008 R2

Thanks to everyone that attended the event and made this such a big success.  The community here in the Minneapolis area and surrounding cities and states is just fantastic and I am just glad that I can be a part of it!

Posted in SQL Server | Tagged: , | Leave a Comment »

Hierarchies in PowerPivot

Posted by denglishbi on May 19, 2010

I saw one of Denny Lee’s tweets this morning titled “Hierarchies, Oh Hierarchies…where are thou? (in PowerPivot)”, so I was intrigued since I just talked about this not being available yesterday in my PASSMN presentation.  In his example that he provided was two tables, one for the list of states and another with the city names along with the corresponding state id value reference, and he showed how even though you can relate the two tables that the results could be misleading.  If you tried to select the state name and then city name the city name would actually be replicated for each state.  Not what you would expect.


As a work around for this solution he suggested to add a fact table and once that was created then you would see the expected results and the cities would be mapped to the respective states.  My thought was to simply add a calculated column in the city table that had the state id mapping.  So the tables would look like such:

image image image

So initially the StateName column did not exist in the city state table I had defined, but once the relationship was in place I simply added a new calculated column in the table using the RELATED DAX function to pull in the state name associated with the cities (see the DAX function in the formula bar in the middle screenshot).  Now you can provide the single table for the end-users to reference and they will get the following results:


The good thing about this is that you don’t necessarily need to display the state table anymore since the information is included in the city state table and the end-users will know that the columns are truly related to each other since they are in a single table.  Still doesn’t exactly make sense why it didn’t work out-of-the-box originally without the fact table solution that Denny provided, but the calculated column solution takes care of the issue, plus provides the single denormalized table that we would typically see for geography information in this instance.

Now the one thing that you don’t get is the user-defined hierarchy like you have in Analysis Services (SSAS) where you would have the navigation path clearly defined for the users to be able to drag-and-drop and drill down (or up).  So when I state that hierarchies are not available this is what I am referring to.

Posted in PowerPivot | Tagged: | 2 Comments »

Training, Training, Training, and oh my all Free!

Posted by denglishbi on May 16, 2010

You just got to love a launch year and all of the new products that have come out – Visual Studio 2010, Silverlight 4, Office 2010, SharePoint 2010, SQL Server 2008 R2, Master Data Services, Azure, PowerPivot, and more to come like Parallel Data Warehouse, Windows 7 Mobile, etc.  Now this can be very overwhelming, but with new products brings new opportunities along with some outstanding productivity gains.  So how can you get up-to-speed or exposed to all of these new offerings?  Well there is just a ton of new content and resources readily available to download and use online to get you started and trained on these new products.  This is a great imageway to expand your portfolio of products and prepare you for these new releases to see how you can benefit from them and incorporate them into your organizations.

Here is just a few possibilities for you to explore, to get your learning on, and extend your portfolio:

  • Visual Studio 2010 Resource Kit – provides a deep offering of online and offline content. You have direct access to the most up-to-date training kits for Visual Studio 2010 and the .NET Framework 4, SharePoint 2010, Microsoft Office 2010 and many more. Inside you’ll also find resources including the Visual Studio 2010 Reviewer’s Guide and other valuable content. Explore the Visual Studio 2010 environment through the Hands on Labs and get started to unleash your creativity.
  • Moving to Visual Studio 2010 e-Book – The book is not a language primer, a language reference, or a single technology book. It’s a book that will help  professional developers move from previous versions of Visual Studio (starting with 2003 and on up). It will cover the features of Visual Studio 2010 through an application. It will go through a lot of the exciting new language features and new versions of the most popular technologies without putting the emphasis on the technologies themselves. It will instead put the emphasis on how you would get to those new tools and features from Visual Studio 2010.
  • Silverlight 4 Training – Course includes a whitepaper explaining all of the new Silverlight 4 features, several hands-on-labs that explain the features, and a 8 unit course for building business applications with Silverlight 4. The business applications course includes 8 modules with extensive hands on labs as well as 25 accompanying videos that walk you through key aspects of building a business application with Silverlight. Key aspects in this course are working with numerous sandboxed and elevated out of browser features, the new RichTextBox control, implicit styling, webcam, drag and drop, multi touch, validation, authentication, MEF, WCF RIA Services, right mouse click, and much more!
  • Office 2010 Training Course – includes videos, presentations, hands-on labs and source code. This content is designed to help you get started developing solutions, from Add-ins to full featured Office Business Applications (OBAs), using Visual Studio 2010 with Office 2010 and SharePoint 2010 as the core platform.
  • SharePoint 2010 Advanced Developer Training – offers technical training as self-paced modules and hosted labs for SharePoint 2007 professionals who want to upgrade their skills to SharePoint 2010.
  • SQL Server 2008 R2 Labs – Try out one, two, or all seven of the virtual labs available in the hosted trial environment and don’t forget the free eLearning that is available – Free Microsoft eLearning SQL 2008 R2 and Azure Training along with SQL Server 2008 R2 Update for Developers Training Kit (new one should be available for RTM soon).
  • PowerPivot – Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction and DAX for PowerPivot Whitepaper

This is just a small sample of what is available out there for you to consume.  As new content is available I continue to tweet about it, so if you are looking for more immediate content and information get your twitter going and expand your resource pool.  I also post tons of content in regards to free online training, events, and other download content on my blog, so explore around and check out the training and downloads categories.  If any of these new technologies interest you at all or you are trying to increase your value, the sky is the limit and the price is definitely right…free, so you can’t complain, just need more time in the day.


Posted in Training | Tagged: | Leave a Comment »

Free Microsoft eLearning SQL 2008 R2 and Azure Training

Posted by denglishbi on May 9, 2010

Just wanted to point out that there are some free eLearning courses to provide you some insight into the new offerings in the R2 release of SQL Server 2008 and SQL Azure.  There is plenty more content out on the SQL Server Launch Resources site including FAQs, whitepapers, presentations, datasheets, videos, and more.

Another one should be available over the next couple of months for the Parallel Data Warehouse, so be sure to check out the Microsoft eLearning site.

Posted in Training | Tagged: | Leave a Comment »

TechFuse 2010 Follow-up

Posted by denglishbi on May 8, 2010

This past week I spoke at TechFuse 2010 in Bloomington, MN.  There were approximately 400 people in attendance and for my session I had 50 to 60 people which was really good since there were seven different tracks to attend.  I did have a little scare with my virtual environment performance the night before and the morning of the event, but I was able to resolve it just before I headed out to the event. 

My talk was to discuss all of the Microsoft Business Intelligence components and try and shed some light on how they all fit together and can be used to create a unified dashboard.  Hopefully I was able to accomplish some of my goal, it was rather aggressive to try and do in 75 minutes and I could have definitely used more time since I had demos.  You have to have demos for a tech focused event and I did two samples with Report Builder 2.0 (one relational and the other multi-dimensional), one with Excel Services (PivotTable), and the final one was using Dashboard Designer to create the SharePoint PerformancePoint Dashboard.  Here was my final result that combined all of the pieces together:


Granted it is a little busy, but with my time constraint I just wanted to show how it could be done and not necessarily how pretty you could make it, just that you can use all of the different pieces for different effects and the end-user doesn’t need to know what technology was even used. 

  • In the scorecard I added an MDX formula for the Order Quantity target which states that they wanted a 15% growth over same time previous period,
  • linked the measures in the scorecard to the analytical chart below the scorecard,
  • created a matrix report with an embedded line chart to create a sparkline trend for sales,
  • used the SSRS 2008 gauge to show gross profit margin KPI from the SSAS cube (added ranges to create the red/yellow/green and associated with goal value),
  • used an Excel PivotTable with filters and conditional formatting,
  • and then the two filters on the dashboard page that were linked to the different components.

For my data sources I used the Adventure Works 2008 databases.  I have included my demo files along with my presentation in the download link below.  The demo files include a ReadMe.txt file that provides the links to the database downloads along with some additional notes, the SQL script for the view I used for my SSRS matrix report, the two RDL files, the Excel file, the dashboard.master page I used, and the bswx Dashboard file.  If you have any questions in regards to the demo or follow-up in regards to my presentation feel free to leave a comment on the posting or send me a direct message.

Thanks to everyone that attended my session and now I am getting ready for my next one, ‘Lower Costs Through BI Consolidation’ at the SQL Server 2008 R2 Launch in Bloomington, MN next week.  I also get to start out the event doing the welcome and introduction of the keynote speakers.  Once that is done it is on to the next presentation at the PASSMN May 18, 2010 Monthly Meeting where I will be talking about ‘SSAS Design Best Practices & Incremental Processing’.



Posted in Business Intelligence | Tagged: , | Leave a Comment »

PASSMN May 18, 2010 Monthly Meeting

Posted by denglishbi on May 4, 2010

The next Minnesota SQL Server User Group meeting is coming up in a couple of weeks on Tuesday, May 18 from 3 to 5:30 at the local Microsoft office in Bloomington.  This month we will be looking at Analysis Services (SSAS) Design Best Practices and Incremental Processing.  We are going to try a new format over the next few months to see how things go and have one presenter.  We are going to allow for more time for the speaker and for attendees to ask questions.


In order to RSVP for the event you will need to login to the national PASS site and click on the RSVP link.  This month our sponsor for the event is Magenic.


PASSMN May 2010 Meeting – SSAS Design Best Practices & Incremental Processing

Agenda: image

3:00 –3:15 : Announcements
3:15 –3:25 : Sponsor
3:25 –4:45 : SSAS (Dan) 
4:45 –5:00 : Survey Collection and Giveaways
5:00 –5:30 : Networking


SSAS Design Best Practices & Incremental Processing (presented by Dan English) – So you built a SSAS cube or are planning on it…now what? Just deploy, process, and let the users go at it? Maybe and maybe not. During this presentation we will talk about and explore Analysis Services design best practices and talk about a method for incremental processing.

Don’t forget there are a couple of other events coming up.  This week there is TechFuse 2010 – May 6 Bloomington, MN, next week we have the SQL Server 2008 R2 Launch – May 11 Minneapolis, MN, and then the day after our monthly meeting there is 24 Hours of PASS 2010 – Free Training.

Hope to see you at one (or all) of the events and as always we will have some giveaways at the PASSMN meeting!

Posted in Training | Tagged: | Leave a Comment »