Latest downloads, training, and informational materials

The reason my blog postings have been reduced a bit lately is because of Twitter.  When I come across new content and materials I post the information through my twitter account (http://twitter.com/denglishbi); It is more convenient and easy for me and I plan on using this method more and more over time.

I decided to go ahead do another catch-up for people that might be interested in some of the content that I have been checking out (and plus for when the tinyurl links stop working).  So here is a list of some of the items over the past couple of months (these are in no particular order):

• Gartner’s Magic Quadrant for Busines Intelligence 2010 (2009 version)
• Free eBook – First Look at Microsoft Office 2010 – 14 Chapters (XPS or PDF format available)
• PerformancePoint Services 2010 Unleashed "Rough Cuts" available (pre-order here)
• Microsoft release dates possibly leaked for future product releases beyond 2010
• Windows Azure Platform TCO and ROI calculator
• Hyper-V Information Worker 2010 (Beta2) – Office 2010, SharePoint 2010, SQL Server 2008, Project Server 2010, etc. (sorry, no SQL Server 2008 R2 or PowerPivot)
• Business Intelligence Executive Briefing – presentation introduces Microsoft’s vision for BI in the enterprise
• Silverlight 4 Training Course includes hands-on-labs, a video and a whitepaper
• VS 2010 and .NET Framework 4 Training Kit – January Release: includes presentations, hands-on labs, and demos
• Add the power of Bing search to your website or blog
• Windows Azure Platform Training Kit – December Update
• Sharepoint 2010 Beta Developer Training Kit – includes presentations, HOLs, videos, and code!
• APO for Microsoft Data Warehousing Quickstart – more great content and tons more available for migration material
• Good overview and training of SSRS 2008 and Report Builder 2.0
• Application Platform Optimization SQL Server Reporting and Analysis QuickStart

Hope you find some of these informational and helpful.

Cheers!

Using Excel Services Reports with PerformancePoint Server (PPS)

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (10/1/2010): I corrected the image of the screenshot for configuring the Products parameter in the Excel file.  I originally had one displayed with DateFiscal for the setup instead of the one referencing Products.  I apologize for that.  If you wanted to do one for DateFiscal you would need to setup the named range for Sales!$B$4.

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.

One of the nice features about creating dashboards with PerformancePoint Server (PPS) is the ability to incorporate different types of reporting types like Analytical Grids/Charts, SQL Reporting Services Reports (SSRS), ProClarity Analytical Views, and Strategy Maps.  Another one is being able to leverage Excel Services.  As we all know Excel is one of the most widely used tools, so it only makes sense that at some point we will probably want to be able to integrate an existing Excel file within the organization.

Some of the reasons that you might want to use Excel as a report in a PPS dashboard could be:

• use an Excel report that has already been developed by end-user
• leverage some of the nice Excel 2007 conditional formatting options – data bars, color scales, and icon sets
• want more control over the style and format of the chart or table
• need to incorporate a pie, bar (not column), area, scatter, radar, or bubble chart into your dashboard

The first thing that you will need to do ahead of time before you are able to do this is enable Excel Services on SharePoint 2007 and configure it.  If you are unfamiliar with this process you can check out this reference – Demo: Enable Excel Services and data connections for a SharePoint team site and check the video and download it for reference.  NOTE: Excel Services is an Enterprise feature within SharePoint Server.

Once you have Excel Services configured you will then need to create the Excel file with the items that you would like to include in your dashboard.  In this example I will be using Analysis Services as the data source (the ever famous Adventure Works DW) and will create an Excel PivotTable and PivotChart.  These two Excel items will then be made available in SharePoint (Excel Services) for reference by the PPS dashboard that will then be connected to dashboard filters.  NOTE: this demonstration is using a virtual environment that is setup in a standalone configuration (meaning everything is installed on the server – SQL Server, PPS, SharePoint, etc.).

(You can click on the images below to view a larger image – the flow goes from left to right)

Open up a new Excel workbook and create the data connection to the Analysis Services database	Connecting to a local instance of Analysis Services
Select the AdventureWorksDW database and the Adventure Works cube	Enter a name for the Office Data Connection (odc) file
Going to create a PivotChart and Pivot table	Select ‘Reseller Sales Amount’, ‘Date.Fiscal’, and ‘Geography.Geography’
Only select FY 2004 in the column labels and change the chart type from Column to Pie	Remove Chart Title and add Data Bars conditional formatting to the PivotTable
Click in the PivotTable and in the Options enter a meaningful name and deselect the grand total options since we are only going to show one time period in this example	Now select the pie and go into the Format Data Labels options and choose the options you would like to include
Select the PivotChart and in the Properties in the Layout portion of the Ribbon give the Chart a meaningful name	Changed the worksheet name and add an additional hierarchy from the field list in the Report Filter section – Product.Product Categories
Go into the data connection properties and export the data connection to the data connection library in SharePoint (just like in the demo link provided above)	Publish the Excel workbook to Excel Services
Specify the path to the document library, provide a meaningful file name, and specify the Excel Services options (I just selected items in the workbook and then added the parameter in to be used with PPS)	Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotTable
You can click on the View button to see a preview of the item you are referencing	Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotChart (you can copy and paste the previous report, change the name in properties, and change the item name being referenced)
Create the dashboard page and link the filters to the reports	Deploy the dashboard page
Test the dashboard page filter and drill into the data within the table for further analysis

NOTE: If you don’t see the Excel Services parameter in Dashboard Designer it is most likely because in the Excel filter you have multiple values selected.  Go back into the file and make sure only one item is selected so that it will be recognized and pulled into the parameters section in Dashboard Designer.

For some additional references in regards to Excel Services checkout some of the links below:

• Publish a workbook to Excel Services
• Differences between using a workbook in Excel and Excel Services
• Business Intelligence with SharePoint and Excel
• Ten Tips for Using SharePoint Server 2007 with Excel Services

Download:

Installing Dashboard Designer for PerformancePoint Server (PPS)

On a few occasions I have been asked where the install is for the Dashboard Designer application that is used to create and deploy the PerformancePoint Server (PPS) 2007 elements.  This is a fair question because it would be a little confusing for someone that is new to the product.  This isn’t a typical installation that is found on the PPS installation disk like you would install ProClarity Desktop Professional, Excel, SQL Server Management Studio, Business Intelligence Development Studio, etc.

So where is the installation located and how do you install Dashboard Designer?

Dashboard Designer is a ClickOnce application and it is available to install after you have installed PPS Monitoring Server.  ClickOnce provides a way to install the application from a web page (in this case Monitoring Central) which then gets downloaded to the user’s workstation.  When you launch the application it is actually setup to check for updates on the server and provide you an option to install the latest version.

Once the server product has been installed for PPS you will be able to access the Dashboard Designer installation by going to the Monitoring Central web page which by default is http://servername:40000/Central

Once you have accessed the Monitoring Central web page you can either click on the Run button or the Download Dashboard Designer hyperlink.  This will launch the Dashboard Designer installation.

This is the point where a check will be performed to see if the application is already installed and if you have the latest version that is available on the server.  If the application is installed and is the latest version the application will be launched.  If not you will receive the message box above to run the installation.

Once the product has completed the installation it will then be launched.

At this point you will be able to verify which PPS web service you are connected to in the lower left-hand corner and also the version (build) number that you are working with in the lower right-hand corner.  In the picture above you will see that I am currently running SP3 of the product (PerformancePoint Server (PPS) 2007 SP3 now available).

Once this is installed you will also get a shortcut placed in your start menu located in the following location: Start—>All Programs—>Microsoft Office PerformancePoint Server 2007—>Dashboard Designer

And that is how you install Dashboard Designer for PPS which is not your typical installation process.

For reference, ClickOnce applications get installed in the following location by default — for Windows Vista and Windows Server 2008 (or new version of the Windows OS) C:Users\<USERNAME>\AppData\Local\Apps\2.0 and in Windows XP and Windows Server 2003 it would be C:Documents and Settings\<USERNAME>\Local Settings\Apps\2.0.

MSDN Event – Cloud Computing and SQL Azure

Next month on Tuesday, February 9 there will be an MSDN Event in Eden Prairie, MN (from 1 to 5 p.m.).  These events typically occur on a quarterly basis and you can see if there is one happening in your area by going to http://msdnevents.com.

Here is the agenda for the event next month:

Event Overview

MSDN Events presents:  Take Your Applications Sky High with Cloud Computing and the Windows Azure Platform

Join your local MSDN Events team as we take a deep dive into cloud computing and the Windows Azure Platform. We’ll start with a developer-focused overview of this new platform and the cloud computing services that can be used either together or independently to build highly scalable applications. As the day unfolds, we’ll explore data storage, SQL Azure, and the basics of deployment with Windows Azure. Register today for these free, live sessions in your local area.

SESSION 1: Overview of Cloud Computing and Windows Azure

The Windows Azure platform is a set of high-performance cloud computing services that can be used together or independently and enable developers to leverage existing skills and familiar tools to develop cloud applications. In this session, we’ll provide a developer-focused overview of this new online service computing platform. We’ll explore the components, key features and real day-to-day benefits of Windows Azure.

SESSION 2: Survey of Windows Azure Platform Storage Options

Durable data storage is a key component of any cloud computing offering. The Windows Azure Platform offers many options, which can be used alone or in combination. Windows Azure itself offers ready-to-use and lightweight storage in the form of tables, blobs, and queues. Another choice for storage is SQL Azure, a true relational database in the cloud. In this session, we’ll explore the highlights of these implementations and how to both create and use storage in each form. We’ll give you guidance on choosing the right forms of storage for your application scenarios.

SESSION 3: Going Live with your Azure Solution

Windows Azure features a powerful, yet simple deployment model. By focusing on your application and abstracting away the infrastructure details, you can deploy almost any app with minimal fuss. In this session, we’ll walk you through the basics of Windows Azure deployment, including site monitoring, diagnostics and performance issues.

To register for this event in Eden Prairie and for additional agenda information check out the registration link here –> Event Registration

You can find additional Microsoft events through http://technetevents.com and http://msevents.microsoft.com.

        

PASSMN January 19, 2010 Monthly Meeting

The next Minnesota SQL Server User Group meeting is coming up in a couple of weeks on Tuesday, January 19 from 3 to 5:30 at the local Microsoft office in Bloomington.  This month we will be looking at some new technologies – SQL Azure, SQL Server 2008 R2, and PowerPivot.  

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 Microsoft.

This month we are also going to be starting a new option to attend and watch the presentations by using Live Meeting, so if you are not available to attend the event in person you will also be able to connect remotely.  This will be our first time using this to kickoff the new year, so please be kind.  We have added some additional time in the agenda below just to accommodate for Live Meeting and any additional time for demos and questions with the new technologies.

PASSMN January 2010 Meeting – SQL Azure & SQL Server 2008 R2

• Meeting sponsor: Microsoft
• Location: 8300 Norman Center Drive, 9th Floor, Bloomington, MN  55437
• Please RSVP here for the event to assist in making sure there is enough snacks and beverages ordered.
• Live Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=ZK954S&role=attend&pw=D%60%5D6%3EdQ%40w Code: D`]6>dQ@w

Agenda:

• 2:30-3:00 : Networking
• 3:00-3:15 : Announcements
• 3:15-3:25 : Sponsor introduction
• 3:25-4:25 : SQL Azure (Sarita)
• 4:25-4:30 : Break
• 4:30-6:00 : SQL Server 2008 R2 (Jin & Lara)
• 6:00 :  Swag Drawing

Presentations:

SQL Azure This session will answer some of the following questions about new state of the art technology SQL Azure from Microsoft

• What is SQL Azure?
• Why to use it?
• How does it work?
• How the pricing and licensing will work?
• Role of DBAs, Developers with SQL Azure
• Future of SQL Azure

About Sarita Garg – Currently working at United Health Group as Senior Database Analyst Project DBA.  Responsible to provide Solution, Design, & Delivery of infrastructure build to integrate legacy systems and Merger & Acquisition systems into managed services.  Many years of experience in data and business processes related to super computing, finance, marketing, non-profit organization, student services, trucking, customer relation management (CRM), and travel at Royal Credit Union, Cray Research, Chippewa Valley Technical College, Carlson Wagonlit Travel of Carlson Companies.  Education Master’s in Industrial Psychology from University of Bombay, India, Bachelor’s in Business Administration from UW – Eau Claire, WI,  Associate Degree in Computer Information Systems from Chippewa Valley Technical College, WI, and CPPM (Certified Professional Project Manager) from University of St. Thomas.

SQL Server 2008 R2 Some of the details are still being flushed out on this presentations, but this will be covering the items below with some demos of these features.

• Overview/Highlight of SQL Server 2008 R2 (new features)
• PowerPivot – Excel Add-in, deployment to SharePoint, IT Management

About Jin Cho – Jin evangelizes on Business Intelligence capabilities for suite of Microsoft products.  Jin has proven ability to deliver Business intelligence solutions utilizing leading market BI technologies.  Prior to becoming a Microsoft employee, Jin spent 11 years at Allina Health System.  He managed the Business Intelligence/Data Warehouse team to lead an effort to provide clinical, financial, and operational BI solutions.  He was also responsible for implementing reporting and analytic environment for the Epic electronic medical record system.  Jin has bachelor degree in Statistics from the University of Minnesota.

About Lara Rubbelke – Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).