Dan English's BI Blog

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

Archive for November, 2010

Migrating PerformancePoint 2010 Content to New Server

Posted by denglishbi on November 20, 2010

So you created some cool dashboards with PerformancePoint Services 2010 with SharePoint 2010 and now you want to deploy them to a new environment (server).  How do you do this?  Well with PerformancePoint 2007 there were a couple of options, you could save your work to a workspace file, connect to new environment, and then publish everything.  The other option was to use the BI Report Automation Publisher tool available on CodePlex.  With the tool you were able to reference a workspace file as well, reference certain items, and make appropriate adjustments for server references.  Both were easy options and straight forward for the most part.

Now with the 2010 version this has changed.  I will have to admit that at first I got thrown for a loop and was confused myself since I wasn’t able to simply use the new workspace file, connect to new server, and save the content.  I even tried modifying the file and making environment URL reference changes to try and get the 2007 deployment option to work.  Needless to say I initially started to recreate items in the new environments from scratch and I guess I was not the only one.  I ended up posting an item in the forums – Deploy PPS 2010 dashboard content to new server – to seek some advice and Umair Khan pointed me in the right direction and refreshed my memory.

In PerformancePoint Services 2010 we need to start out by opening up Dashboard Designer and connecting to the new environment.


Click on the Import Items button in the Home Ribbon of Dashboard Designer and select your workspace file (this will be the file from your other environment that contains the content that you want to migrate and move to the new server).


Now you will walk through a wizard to select the content that you want to import to the new server (environment).


In this case I am going to do both items and I only have one location for each on my new server that I am moving the content to.




The items will now be imported and saved to the specified locations and you will receive a results page.


You can then add the items to the workspace and start to make the necessary changes. 


This is similar to the 2007 way where you have to make all of the necessary changes after the fact when you simply connect and publish the items from an existing workspace file.  It would be nice if there were some options to make some changes to the connections or report file references, but it does move the content.

For some more information on this check out the online content here – Import PerformancePoint Dashboard 2010 content from a SharePoint Server 2010 server, site, or list.

Posted in PerformancePoint | Tagged: , | 10 Comments »

New PowerPivot & Azure DataMarket Experience

Posted by denglishbi on November 19, 2010

I finally got around to installing the latest version of the PowerPivot application.  The new build number is 10.50.1747.0 (previous RTM build was 10.50.1600.1).  Here is my install experience while having the RTM x64 version already installed on my laptop (go from left to right and then work your way down):

image image image
image image image
image image image

Pretty neat, no need to uninstall and reinstall – thanks Microsoft!

Ok, so now that we have rebooted lets take a look at this new functionality that was added…Azure DataMarket data connection.  To the clouds we go!

image image image
New icon in the external data connection portion of the PowerPivot Ribbon – lets load up that cart with some data… The option that is available in the ‘From Other Sources’ option The new Import Wizard for getting data from the clouds
image image image
What you see when you click on the ‘View available Azure DataMarket datasets’ link in previous screenshot Need to register for the service to get an Account Key setup – this is a FREE registration Registration agreement screen
image image image
Going to subscribe to get access the to the crime data feed Sweet, we are all set to go and the list of options is available for us for exploring the data Account information provides Account Key needed in PowerPivot to connect.  This screen will be displayed if you click on the Find button in the wizard above
image image image
If you go into your account, datasets, you will get the URL for accessing it in the details section of the dataset in the browser. Place this in the wizard Next screen, let’s try the preview now Oh no, didn’t work, what now?
image image image
Never fear, we have Marco Russo to save us!  Just add an extra slash onto the end of the URL string Now we can preview the data.  The Test Connection option would work either way, but need extra slash to get data. Let the import begin.
image image image
Download complete. New table in the PowerPivot Windows. Time to analyze data for violent crimes, MN is looking okay (#34).

How cool is this?  Really neat that we can tap into some external data and get this incorporated into our existing internal data for analysis.  Lots of data is available out on the Azure DataMarket to take a look at including weather information which is always useful.  This analysis I did was extremely raw and has tons of gaps since I find it hard to believe MN had zero violent crimes in 2006 and you have to factor in population sizes into this analysis (that information was included, just not part of my Pivot or metrics).

So what are you waiting for, go ahead and download the latest version of PowerPivot and tap into some new data feeds –> Download PowerPivot.

Head to the clouds for your data – http://datamarket.azure.com/

Please support the connect bug to get this escalated – PowerPivot fails using URL published on Azure DataMarket website

Posted in Azure, PowerPivot | Tagged: , | 2 Comments »

SharePoint 2010 Business Intelligence Virtual Lab

Posted by denglishbi on November 18, 2010

Today I was browsing through the SharePoint 2010 general forums to see if there were any unanswered PerformancePoint questions and found one about PerformancePoint 2010 tutorial.  I replied linking back to Free Online PerformancePoint Services 2010 Training Videos and then someone later posted a reference to a new Microsoft Virtual Lab that I had not seen before MSDN Virtual Lab: Developing Business Intelligence Applications (filed under Microsoft Office SharePoint Server).  The description of this new lab (and it must have been posted this month based on the lab manual screen shots because the MonthToDate TI forumla returns November 2003 in the PerformancePoint lab) is the following:

MSDN Virtual Lab: Developing Business Intelligence Applications

After completing this lab, you will be better able to use the Chart Web Part to create graphical representations of data within SharePoint lists, use Microsoft Excel 2010 to examine and data from SQL Server Analysis Services, and publish an Excel workbook with Excel Services to make it accessible to users using a browser. You will also be able to work with a PerformancePoint Services site and the new Dashboard Designer.

I went ahead and printed out the manual and went through the lab with about 15 minutes to spare on the end (server was a bit unresponsive at times – and you get 90 minutes to complete the lab).  I have not used the Chart Web Part before, so that was something new (I stick with PerformancePoint, Reporting Services, Excel Services, etc.) and the Excel Services portion wasn’t too bad (got to add a sparkline).  The Chart Web Part does provide 3D and Tooltips, something that Excel Services doesn’t provide though…interesting.  The PerformancePoint KPI setup was a good exercise using the Time Intelligence portion of PerformancePoint as well.  All-in-all I would give the lab maybe a 7 out of 10.  There were a couple of minor typo issues in the manual on pages 28 and 41 from what I saw (one stating to map to 2009 and then tells you to type 2010 and the other saying to ‘mote’ to a URL), but overall it was very straight forward.

Definitely nice to have these available and provide you the ability to test drive the technology and get exposure to these items.  And of course it is FREE training opportunity that you can do on your own time.  So what are you waiting for, get over there and check it out.

Posted in Business Intelligence, PerformancePoint, Training | Tagged: , , | 1 Comment »

Free SQL Server Community November Training

Posted by denglishbi on November 17, 2010

Head over to Pragmatic Works site and take a look at some training that is available this month.  Yesterday Jamie Thomson did an SSIS performance webinar and Chris Webb and Brian Knight are doing ones this month as well (tomorrow is the one being done by Chris).  Can’t beat the price either, FREE, plus you are getting the information from the pros (MVPs!), and they are broadcasted, so you can connect from work, home, or any place you have an Internet connection.image

  • November 16, Jamie Thomson – SSIS Dataflow – Designing for performance
  • November 18, Chris Webb – Introduction to Performance Tuning Analysis Services 2008
  • November 23, Brian Knight – Building calculations in SSAS 2008

If you miss a session they record them and you can check them out later on-demand in the webinar resource area.


Posted in Training | Tagged: | Leave a Comment »

SQL Server ‘Denali’ SQL11 CTP1 Install Experience

Posted by denglishbi on November 17, 2010

Nothing really new with install compared to the SQL Server 2008 R2 install, but I wanted to share my experience installing the product.  I installed this on a Windows 7 32 bit OS to test out and take a look at some of the new features that are available in the CTP1 release.

image image image
The please wait message you will see periodically throughout the process during some validation checks of your system. Installation menu page The ‘Denali’ CTP1 logo
image image image
Setup Support Rules check – passed with flying colors Installing the Enterprise Evaluation edition of the product License agreement terms
image image image
Ready to install support files Support Support Rules check again – just a warning that the firewall is enabled Setup Role – going to perform the feature installation
image image image
Going to install everything that is available Installation Rules check – passed with flying colors again Instance Configuration screen – just going with a default instance
image image image
Disk space required for the full install – 6076 MB compared to the 6102 MB required for the R2 install Server configuration – time to setup the accounts for running the services Database Engine configuration screen and account provisioning setup
image image image
Don’t forget to setup filestream if you want to install the latest Adventure Works sample database Analysis Service configuration screen and account provisioning setup Reporting Services configuration screen – just doing a native install
image image image
Error Reporting option to help out Microsoft Install configuration rules check – all systems clear Ready to go ahead with the install
image image image
Installation progress – here we go Error message referencing a file during install – not everything will be installed during Round 1 (at least for me) Install issue prevented the Management Tools from being installed
image image  image
Time to send the install issues to Microsoft Install Round 2 – add features to existing install Need to install Management Tools
image image image
Okay, all set for Round 2 Success! Shortcut menus available after installation
image image image
SQL Management Studio splash screen – powered by Visual Studio… Rounded corners BIDS is still using Visual Studio 2008 for now
image image imageimage
CTP1 only has changes for SSIS (at least for the BI side of the world which is my focus) Some new icons to get at parameters (new item) and variables Window along with SSIS Toolbox Control Flow toolbox items
image imageimageimage image
New Zoom item available on design surface and rounded corners for the items Data Flow Task toolbox items – new assistants (wizards) for source and destinations Initial source assistant Window

Well that is it for now.  If you are interested in the new SSIS enhancements and want to give the CTP1 release a test drive there are some cool new features.  I plan on doing a couple of follow up postings going over some of these.  If you can’t wait and have an hour to check out a video you can take a look at a recent presentation done at TechEd Europe What’s Coming Next In SSIS.

If you want you can check out some of the BOL items for ‘Denali’ here to get some more details on what is available in the new release:

The build number for the CTP1 release is 11.0.1103.9

That is it for now.  Time to start diving into the SSIS changes.

Posted in SQL Server | Tagged: , | 1 Comment »

Project ‘Crescent’ first impression – simply amazing!

Posted by denglishbi on November 10, 2010

All I can say is ‘wow’.  During yesterday’s keynote that was streamed live from the SQL Pass Summit Amir Netz did a demonstration of the new reporting tool codenamed ‘Crescent’ that will be available in the next release of SQL11, codenamed ‘Denali’.  I was completely amazed at the visualizations and the functionality that was provided.  Being able to visualize the report instantly as you are creating it and the interactivity that is provided out-of-the-box was incredible.  The storyboard feature with PowerPoint was very nice as well.  Can’t wait until it is available to test drive which hopefully will be in the CTP2 release.

If you haven’t heard about this product yet then take a look at Robert Bruckner’s posting here Glimpse at Project Crescent.  He has FAQ information along with links and additional information that you will find very helpful.  There is a brief video you can check out on BizIntelligence.tv – Data Visualization Done Right: Project Crescent and if you didn’t get to see the keynote yesterday you can head over to the SQL PASS site and watch the on-demand video recording (fast forward about 3/4 of the way through to get to Amir Netz’s presentation) – PASS Summit 2010 Day One Live Streaming Keynote.

Kudos to the product team and can’t wait to see how this evolves over the next year.  Very exciting news as always.

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

PASSMN November 16, 2010 Meeting

Posted by denglishbi on November 8, 2010

The next Minnesota SQL Server User Group meeting is coming up next week.  Lara Rubbelke (blog | twitter) will be presenting on achieving separation of duties with SQL Server.

In order to RSVP for the event you will need to login to the national PASS site and click on the RSVP link.  The sponsor for next month’s event is PASS.


PASSMN November 2010 Meeting – Si Se Puede! Achieving Separation of Duties with SQL Server


3:00 – 3:15 : Announcements
3:15 – 3:25 : Sponsor
3:25 – 4:45 : Si Se Puede! Achieving Separation of Duties with SQL Server (Lara)
4:45 – 5:00 : Survey Collection and Giveaways


Si Se Puede! Achieving Separation of Duties with SQL Server (presented by Lara Rubbelke) – Given a SQL Server querying problem there’s much that you can do to enable a good performing solution. Tuning involves arranging an optimal physical environment, e.g., by creating supporting indexes, as well as writing the query in a way that it would get an optimal execution plan. Many factors can affect the efficiency of the solution including the availability of indexes, data distribution and density, and others. In different scenarios, a different solution could be the most efficient for the same querying problem. Query tuning could be considered an art. This session will provide various tips to do efficient query tuning and demonstrate those through specific tuning examples.


Other News:

  • PASSMN 2011 Executive Board – Congratulations to the executive board for 2011 – Jason Strate, Gordon D’Souza, Tim Plas, Andy Lohn, Steve Hughes
  • PASS Summit 2010 Live Keynote Streaming – Are you unable to make it to the PASS Summit 2010 event this year?  Well you are in luck, this year they are going to be streaming the keynote talks along with the Women in Technology luncheon. Head over to PASS Summit 2010 Live Streaming Events and get registered today.
  • SQL Saturday 58 Feedback Survey – We had an amazing turnout with right around 250 people in attendance along with our sponsors of the event. Next year we are looking at doing it again, but before we start in on the planning for that we would like to get everyone’s feedback and comments. If you could take a couple of minutes and answer a few questions in regards to the event we would be extremely grateful – SQL Saturday 58 Minnesota Feedback Survey.  Thanks again for making last week such a wonderful event!
  • Minnesota Microsoft BI User Group Q4 2010 Meeting – The next BI User Group meeting will be on December 1 with an Ask the Experts panel to discuss BI challenges and a Master Data Management presentation – Please Register Here.

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

Minnesota Microsoft BI User Group 2010 Q4 Meeting – Dec 1

Posted by denglishbi on November 6, 2010

The next MN Microsoft Business Intelligence User Group has been scheduled and the agenda is set.  There will be an Ask the Experts panel to discuss BI challenges and a presentation on Master Data Management.

MN Microsoft BI User Group 2010 Q4 Meeting

Date: Wednesday, December 1, 2010

Check In: 2:30 PM Event Time: 3:00 PM – 6:00 PM

Location: Microsoft Event Center 8300 Norman Center Drive Suite 950 Bloomington, MN 55437

Please Register Now


  • 2:30-3:00 : Registration
  • 3:00-3:15 : Microsoft BI User Group Updates & Announcements
  • 3:15-4:15 : Ask The Experts: Your Biggest BI Challenge Panel Discussion
  • 4:15-4:30 : Break
  • 4:30-5:30 : Master Data Management
  • 5:30-5:45 : Q&A
  • 5:45-??? : Social and Networking


Ask the Experts: Your Biggest BI Challenge Panel Discussion – Here is a chance to send us your TOUGHEST BI challenges and seek answers from a distinguished panel of experts. The CHALLENGES can include everything from Organizational, Methodology, Approach, Adoption, Data Governance and other solution oriented BI issues.

Your questions will be pre-selected for a moderated live panel discussion at the Microsoft BI User Group Event on 1st December. The panel of experts is comprised of BI managers from local organizations in Minnesota who have successfully implemented Business Intelligence solutions and have addressed tough challenges on their path to BI Success.

If you have any questions for the panelists, please submit them in advance here – BI Panel Question Submission.

Master Data Management – This session will introduce Master Data Management, it’s importance in managing critical company assets and how SQL Server 2008 R2  Master Data Services provides an MDM solution. The session will include a demo going about the process of creating a Master Data Model, applying  business rules and security using the stewardship portal.


Hope to see you there!

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

PASS Summit 2010 Keynote Live Streaming

Posted by denglishbi on November 6, 2010

Are you unable to make it to the PASS Summit 2010 event this year?  Well you are in luck, this year they are going to be streaming the keynote talks along with the Women in Technology luncheon.  I will not be able to make it this year, so I have already registered to watch the events.  I know there are going to be some big announcements coming out in regards to the Denali (SQL11) release and I don’t want to miss out getting a sneak peek at the new features.


So head on over to PASS Summit 2010 Live Streaming Events and get registered today.

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

PASSMN 2011 Executive Board

Posted by denglishbi on November 3, 2010

I am pleased to announce the new Minnesota SQL Server User Group (PASSMN) 2011 Executive Board:

  • Jason Strate, Digineer*
  • Tim Plas, Virteva
  • Andy Lohn, Medtronic
  • Steve Hughes, Magenic

* Incumbent

Yep, that is right, my name is not on the list this year.  After two years I have decided to open up the spot to someone new and I will be shifting my focus over to the local Microsoft BI User Group.  We do not need to vote this year since we had exactly five nominees and five positions that need to be filled and assigned.  Congratulations to everyone that will be part of next year’s board.  Thanks so much for being involved and helping make this such a great SQL community.

In December we will get together and distribute the role assignments for the new board members.  A special shout and thanks goes out for the existing PASSMN 2010 Board that consisted of myself, Jason Strate, Mark Rocco, Gordon D’Souza, and Eric Strom.  Great job and thanks for all of the hard work you have done to make this such a great user group.

Posted in News and politics | Tagged: | Leave a Comment »