Boosting the All-UP VPC Performance - Set max SQL memory to something reasonable instead of unlimited.
Modify IIS to disable logging for PerformancePoint Planning site.
Turn off performance counters.
Configure Analysis Services memory limit.
Setting MAX SQL Server Memory value - SQL Server Performance
Sean's tips on VPC performance:
Now, in order to set this post aside from some of the other "Virtual PC 2007 has been released!" postings, I thought I would share some best practices relating to the usage of Virtual PC that I have picked up from doing demos over the last year or so (as well as from an internal demo optimization doc that made its way into my inbox a few months ago):
- I'll start with the obvious… the more RAM the better. You need to have enough RAM to run your host operating system, your guest operating system(s), plus an additional 32MB of overhead for each guest OS that you will be running. From my personal experience, 2GB of RAM is a good starting point.
- A CLOSE second to RAM is your hard drive configuration. If you are running your Host OS and Virtual machines from the same hard drive, you are in for a painful experience. For good performance you will want to move the .vhd files off to the fastest secondary hard drive possible. For a secondary internal hard drive, SATA or SCSI is ideal, and for an external HD, pick up an eSATA drive. This advice is even more important if your computer is a laptop, as they often have slow 4200 RPM hard drives that will struggle with the concurrent I/O load.
- If your Antivirus allows you to exclude files or directories from realtime scans, exclude the directory that holds your images (and/or exclude the following extentions: .vhd .vud .vsv .vfd .vmc)
- Keep the host computer hard drives defragmented.
The smaller (or less fragmented) the VHD files the faster. Steps to shrink/defragment:
- In the VM run defrag (a couple of times). It might take a while (up to hours) but it is worth waiting for
Run the VM precompactor – this is an ISO image. Just attach it to the VM . The precompactor will zero all the unneeded space in the VHD
- Load the 'Virtual Disk Pre-Compactor.iso' from the Virtual Machine Additions Directory
- Answer 'Yes' to start the zeroing
- Shutdown the VPC when done
- Use Virtual PC Disk Wizard to edit and compact the pre-compacted .vhd
- Use a processor with Virtualization Technology (Intel Page, AMD Page). This is any reasonably recent processor. This will greatly speed up the OS boot time, OS installation time, and performance in non-Windows operating systems (as well as Windows installations that to not have the Virtual PC Additions loaded). On guest Windows installations that have the Virtual PC Additions loaded, performance is significantly enhanced), which brings us to:
- Load the Virtual PC Additions. These are included with Virtual PC, and will need to be loaded in the Guest operating system. This will load up drivers for the virtualized hardware, allow for folder sharing (with the host OS), drag-and-drop functionality (from the host OS), synchronize time with the host OS, and generally turn on "Teh Snappy"
Start up Virtual PC with the following option (Best way is to create a shortcut on your desktop): -usehostdiskcache From the command line reference:
Turns on host-side disk caching, which can improve performance of virtual machines running operating systems other than Windows. This parameter can be useful for resolving poor performance problems with disk intensive tasks. This parameter can be used only when starting Virtual PC.
- Do NOT use the /3GB switch on your Host machine. That switch increases the user memory space at the expense of Kernel space. Virtual machines use more kernel memory than user memory – so enabling this switch on the host will decrease the amount of virtual machines that you can launch
- I'll leave you with a tip that would seem to defy logic (and which I have not tried myself), but the word on the street is that you can increase performance by enabling NTFS compression on the directory that holds your Virtual PC image files (.vmc, .vud and .vhd). It seems that writing a big chunk to disk is slower than first compressing the chunk and then write it to disk.
Turn off what-if analysis in Excel forms and reports for improved performance.
What-is what-if?
When you set the Enable what-if analysis option to True for a matrix, PerformancePoint Add-in for Excel automatically performs what-if calculations that show the effect that the change will have on an assignment if it is submitted.
If this option is set to False, PerformancePoint Add-in for Excel does not apply any changes to the matrix. This can help improve query performance.
Some informal (but real world) feedback from internal deployments and some preview customers tell us that common refresh/what-if operations in SP1 are 50% of the time in RTM or better. In one extreme case which could take advantage of the option to turn of what-if the time was even 5%, but I don't think that will be typical. Opening assignments sounds like SP1 is 50% to 33% of the RTM time. So far I haven't seen any negative performance feedback, knock wood, cross fingers, and any other anti-jinx superstitions.
some AddIn for Excel SP1 performance improvements details beyond the readme... - TechNet Forums
This may cause issues with PerformancePoint as we don't have control over the processing job, but let's set it to false and see.
Lazy processing allows fast updates to the dimensions. After an update, SSAS processing of aggregations and indexes is initiated in the background. As a result of lazy processing, you might see Analysis Server consuming CPU and disk I/O at times when no user activity is performed. To avoid lazy processing, you can set the ProcessAffectedObjects property as part of a dimension update processing command, causing all aggregations and indexes to be processed as part of the same transaction.
Let's bump this one up from 64 to 128. Let's not... :) Either this or the other setting maxed out the memory in the VPC...
MaxThreads
MaxThreads is a server property that controls how many threads will be spawned, at maximum for either query or process operations.
Bumping up (or tuning down?) performance.
Set this to -5 or -6 perhaps? I'm running a dual-core laptop, so this might improve things. Probably not a good idea if you're using a VPC which appears to use only 1 CPU...
CoordinatorExecutionMode
CoordinatorExecutionMode is a complex SSAS server property that controls the number of parallel jobs that Analysis Server will start. When operations (such as querying and processing) occur in parallel, performance can be significantly enhanced. However, too many parallel jobs on a server can decrease responsiveness to other operations. The CoordinatorExecutionMode property’s value has special meaning as it controls the SSAS coordinator component, as follows:
•
Positive number. Sets the absolute maximum number of parallel jobs per server.
•
Zero. Enables the server to automatically determine the maximum number of parallel operations, based on workload and system resources.
•
Negative number. Sets the absolute maximum number of parallel jobs that can occur per processor (as opposed to a positive number, which is per server).
Under the Advanced Properties of Analysis Services, set OLAP\Process\CheckDistinctRecordSortOrder to 0. (At your own risk!!! :)
CheckDistinctRecordSortOrder
CheckDistinctRecordSortOrder is a Boolean server property that dictates how record sort orders will be handled. When processing distinct counts coming from a relational data source, Analysis Services needs to have records in a specific order to ensure accurate counting.
If collations are different between Analysis Services and the relational source, an error might occur if this property is set to True. On the other hand, if collations are known to be the same, performance can be enhanced by setting the property to False, but no checking will occur.
Tool for PerformancePoint Migration
Microsoft has released the tool BI Report Automation Publisher to a wider audience. This is a command line tool with .Net library which makes life easier in moving PPS/ProClarity reports between servers.
Microsoft has built this tool mainly for PPS which can also be used for ProClarity and Excel Services reports to move to different environments. One can configure the server details as well. The tool is being enhanced by the Microsoft team add more features like
- PerformancePoint Target Location Redirection
- PPS Sql Server Reporting Services (SSRS) Report Support
- PerformancePoint Input from BWSX Files
- Deploying PAS Reports
- Deploying PPS Dashboard
- Updating Post-Deployed PPS Webparts
More details on the tool and source code is available in CodePlex . Its very interesting to see the number of hits on this site and the count on people who downloads this tool.
There's a lot of stuff on the MS BI VPC. Server operating system. SQL Servers & Analysis Services & Reporting Services. Sharepoint. IIS. PerformancePoint.
It's a wonder it even starts up at all.
I'm going to focus on a few ways to speed up the All Up VPC, besides adding more RAM, CPU, and fast disks.
First, get the latest All up BI VPC from MS downloads.
All-up BI VPC
For folks who want to play with our Business Intelligence technologies and don't want to bother with installing everything, you can download a virtual machine image that has just about everything installed you could want, including demo content.
The "All-up BI VPC" has two parts, available at:
Part One (3.4 GB)
Part Two (1.0 GB)
Yes - *giga*bytes. These are compressed, and will unzip to a vhd file for Virtual PC that's over 20GB in size. To run the virtual PC, I highly recommend at least 1GB free memory (so your PC needs at least 2GB). If you can free up more, that's better. Also try to work on a PC with two hard drives, and put this on the non-system drive.
Philo's WebLog : All-up BI VPC
Then do some reverse-engineering of queries to see what is going on.
Step 1 - SQL Profiler - Analysis Services
I noticed that the AdventureWorks_Mfg Analysis Services database uses Proactive Caching. Disabling this will help a tad, if you're not using it. You can do this by browsing to the cube in Analysis Services manager and right-clicking on the measure group partition.
Step 2 - SQL Profiler - SQL Server
I noticed that the bcp_BizSystemGetAllApps script is called all the time, and having it return the b.OriginalBMO xml column is a big drag on performance. I commented the column out of the proc's query with no ill effects (just yet... but beware.)
Step 3 - Add indexes
Add an XML index to the BizSystem table OriginalBMO column in the application database and watch the modeler fly. Actually, add XML indexes to all XML columns. Either it's in my head or this improved things dramatically. No more remote scans...
select * from sys.objects o inner join sys.columns c on c.object_id = o.object_id where c.user_type_id = 241 will get you a list. There are some in the application database and some in the PPSPlanningSystem database.
Note that any of these changes could break PerformancePoint. Try at your own risk!
Step 4 - SQL Analysis Services - Under the properties of the bi-vpc, turn off the flight recorder.
Step 5 - SQL Server - Take offline any databases you are not using. So long ProjectReal, you fake project!
Step 6 - Services - turn off Automatic Updates. Disk Defragmenter. Office Sharepoint Server Search. Office Document Conversions services, Windows Sharepoint Services Search.
A common request by form authors is to create a "start here" worksheet page where contributors can change some global filters. Then they advance to other worksheets in the workbook to do their actual data entry. Other times authors just want to link multiple matrices so that changing the filter on matrix A also changes matrix B at the same time. The way RDL is structured makes it natural for PPS to also define all filters as global filters: the add-in stores them naturally as ReportParameters in RDL which are global. Then each matrix is subscribed to the filter.
If you're not seeing any applications when connecting from the modeler, and perhaps you ran some DB upgrade scripts, chances are your applications are offline.
This can be changed by going to http://localhost:46788/Applications.aspx , connecting, clicking on applications and selecting each app to Take Online.
An interface would be good for stuff like this.
Two approaches exist for converting to label-based tables in preparation for populating PerformancePoint Server measure group tables.
- Use PPS stored procs - more of a production solution
- Use a join with source and staging and pull id's.
Here are the steps from the data integration help.
To prepare and load measure group data that have labels
Create a label-based table
For a specified measure group, use the bsp_DI_CreateLabelTableForMeasureGroup stored procedure.
For all measure group tables in a specified model, use the bsp_DI_CreateLabelTableForModel stored procedure.
Populate label-based measure group tables with data from the source database or system. This is a manual step.
Validate label-based measure group tables and correct any errors by using the bsp_DI_ValidateLabelTable stored procedure.
Repeat step 3 as required until all label-based data is valid.
Convert a label-based measure group table to a MemberID-based table
For a specified label-based measure group table, use the bsp_DI_ConvertLabelColumnToMemberIDForMeasureGroup stored procedure.
For all label-based measure group tables in a specified model, use the bsp_DI_ConvertLabelColumnToMemberIDForModel stored procedure.
About performing measure group validation in a model
You can use one of the following methods to validate measure groups before you load data from the staging database to the application database.
Validate data in a specified measure group by executing the Transact-SQL stored procedure, bsp_DI_ValidateMeasureGroup.
Validate all measure groups for a specified model by executing the Transact-SQL stored procedure, bsp_DI_ValidateMeasureGroupForModel.
Note
If you have questions about the process, don't forget to use the PerformancePoint Server forum. You can also send me a message if you are concerned about if a question is phrased correctly -I'm a writer.
PerformancePoint Server 2007 Planning Data Migration Tool
There are two primary steps to a Migration, with many tedious “sub-steps”.
The First step in the process is to Migrate the Application, this part of the process migrates:
- Security roles
- Structural metadata: all model site, model, dimension, and member set definitions
- Data Sources and Data Destinations
- Calculation rules and any associated job templates
- Form templates
- Calendar
The Second, (more tedious), step is to Migrate the Data, which accomplishes migrating the following:
- Dimension and hierarchy data
- Fact data and annotations
- Associations
The “PerformancePoint Server 2007 Planning Data Migration Tool” automates the Second set of steps, the “Data Migration” portion. The full manual process, (which requires the user to have intricate knowledge of the application), is documented in the TechNet Documentation located here:
- Preparing the Staging DB on the Source Server: http://technet.microsoft.com/en-us/library/bb838747.aspx
- Loading the Migrated Application on the Target Server: http://technet.microsoft.com/en-us/library/bb838692.aspx
Norm's PerformancePoint Server Blog : New Planning Data Migration Tool !
White paper: Performance tuning for PerformancePoint Planning Server
This white paper is for analysts and IT professionals who want guidance about PerformancePoint Planning Server scalability capabilities and the number of users that can be served. Considerations for hardware planning and software tuning are also covered.
Download this white paper as a Microsoft Office Word document (.doc).
Performance tuning for PerformancePoint Planning Server (http://go.microsoft.com/fwlink/?LinkID=100209&clcid=0x409)
White paper: Performance tuning for PerformancePoint Planning Server
Downloadable implementation plans
This guide contains detailed information about how to plan a deployment of Microsoft Office PerformancePoint Server 2007.
The PerformancePoint Server Implementation Guide consists of 12 Microsoft Office documents (templates, projects, and worksheets) that can help you plan your implementation of PerformancePoint Server. Start with the Project Plan Framework presentation for a detailed introduction to the tasks involved in implementing PerformancePoint and for guidance on how to most effectively use the templates. Included in the guide are templates for configuration planning, training, requirements gathering, and a plan for a personalized operations and maintenance plan.
Here is a useful utility to pull the tough-to-get-at PerformancePoint M&A repository data out.
Reuse & Recycle.
The PPS Monitor & Analyze UDF Project (MAUDF)
I've often wanted to be able access metadata within a monitoring server with greater ease. Querying the PPSMonitoring database is difficult because the more interesting pieces of information pertaining to a particular element are locked away within the SerializedXML column of the FCObjects table. Sure we can access the data in the SerializedXML column with XPath / XQuery but I really wanted to get stuck into some CLR stuff and figured UDFs would provide the greatest reuse, flexibility and query-ability.
I've created a series of CLR table-valued UDFs and created a home for it on Codeplex, a project that I have named MAUDF (taking a bit of inspiration from the ASSP project). Using the CLR the MAUDFs connect to the PPS Monitoring web service and extract pertinent metadata and return the results in a tabular format.
Nick Barclay: BI-Lingual: The PPS Monitor & Analyze UDF Project (MAUDF)
Radowslaw posted some good links to help with upgrading to SP1 for PerformancePoint – with one from a co-worker of mine.
After launching SP1 for PerformancePoint Server 2007, on many internet forums about PPS, I’ve found many posts describing errors while installing SP1.
For example, one of them described problems with planning applications, which disappeared in Business Modeler after upgrading Planning Server with SP1.
Installing SP1, specially for Planning Server, it is not only running SP1 installation files, but many other important steps.
So, if you omit some of them, you can receive different errors like this above.
If you are going to upgrade your PerformancePoint Server, I strongly recommend to review the following articles, before you start this procedure:
Detailed instructions on Microsoft Technet:
Updating PerformancePoint 2007 Planning Server with Service Pack 1
Updating PerformancePoint 2007 Monitoring Server with Service Pack 1
And if you need another step by step instruction with many screenshots included, read the following article:
http://performancepoint07.blogspot.com/2008/06/installing-performancepoint-planning.html
I've written a paper on Totaling in ProClarity. It's certainly not comprehensive but I do hope it answers some common questions regarding totaling. Please contact us with any feedback or questions.
Excellent resource for Planning Module training notes.
Enabling NativeSQL/NativeMDX Rules
In order to enable Native SQL and Native MDX rules you must perform the following two steps:
- Enable Native SQL/MDX Rules in the Administration Console
- Have a DBA (Database Administrator) activate the rule in the back end
The above steps cannot be performed by a user who only has Business Modeler rights. In order to perform these steps the user will require Global Administrator (GA) rights.
PerformancePoint Server 2007: Enabling NativeSQL/NativeMDX Rules
Thanks to Barry Tousley - the PerformancePoint Server 2007 Planning Data Migration Tool is now available on TechNet and the MS Download Center.
Norm Warren has provided some great additional guidance to support this here.
Documentation: http://technet.microsoft.com/en-us/library/cc512833.aspx
Downloadable implementation plans for PerformancePoint server 2007
A wealth of new content (templates, project plans and worksheets) has been made available on TechNet to assist partners and customers with implementing PerformancePoint Server 2007.
There are links to this content below:
Planning Design Configuration (template) (.docx) [Forthcoming]
Preliminary Requirements Gathering and Scoping Questionnaire (.docx)
Some useful tips for deploying Proclarity.
ProClarity Madness
This past few weeks were practically devoted to working on ProClarity BI solutions. The near heart-stopping moments came while implementing structural changes to the Time dimension and during the solution migration to the production server.
Implementing structural changes for OLAP cubes is straight-forward. The difficulties arise when we already have a lot of BI content (reports and analytics) created and almost all of them uses the Time dimension. With ProClarity, it can potentially disable all reports and analytics. Even with running the Missing Data Wizard will not be enough, as ProClarity Shared Items such as Dynamic Sets and KPI uses the Time dimension as well. The strategy that we used to resolve such change was:
ProClarity Dashobard & Analytics Server - Hierarchy Override
Got another undocumented feature for you, Hierarchy Override in PAS. I heard that there is a whitepaper out in the community somewhere but I was not able to find it.
I am working on a ProClarity dashboard and wanted to pass the dashboard parameter values down to the analyze links in each view on the dashboard. There is an advanced feature in the dashboard product that lets you do hierarchy overide through the Dashboard Studio GUI but that only applies to the actual view and not the Analyze URL. So I needed a way to do hierarchy override in the URL and pass down the current parm value. The answer is the undocumented S node. Let's say that we want to return the children of the selected parameter. The syntax would look something like this:
&expr=[[ProductParm:xml({0})]].CHILDREN
The "ProductParm" before ":xml" is the name of the dashboard parameter. The key to this syntax is the H attribute. You must specify which attribute hierarchy the MDX Set logic applies to. The whole purpose of the "parameter:xml" syntax is to make sure the selected value is properly encoded for both XML and HTML (primarily deals with the ampersand characters in the member names).Thanks to Jason Picker from Microsoft (ProClarity) for the help on this one.
Introduction:
"Microsoft® Office PerformancePoint™ Server 2007 was designed as an infrastructure that includes everything between the server client tier and the Microsoft SQL Server 2005 databases and SQL Server 2005 Analysis Services cubes. It includes a run-time environment, APIs, scripts, configuration files, administrative functions, and a management console interface.
"This document describes the results of tests to measure scalability of Planning Server. The results of the tests confirm the following:
· Planning Server supports large numbers of business users, even when deployed on a conventional configuration consisting of four physical servers.
· Planning server performs well when additional server resources are added.
· Planning Server asynchronous implementation enhances performance and scalability.
"The test scenario described a typical corporate planning budgeting cycle with one data entry model. It had a large data set and was designed to provide unique security for a large number of users to ensure each component’s caching engine was working effectively."
For the current PerformancePoint project I am on, the client runs a fiscal year that ends June 30th. This is the first project where the client has not been on a calendar year. I learned a valuable lesson today that will end up costing me about two-thirds of a days worth of work due to the fact that once the calendar is created, there are no changes allowed (whose stupid idea was that anyway?!?!).
Beware When Creating the Application Calendar Using a Fiscal Year « Alan Whitehouse’s Ramblings
Nice walkthrough of creating a simple Scorecard in PerformancePoint.
Problem
We are just getting started with PerformancePoint Server 2007 and trying to help our business users to develop their first scorecard. For those of you who are new to Office PerformancePoint Server 2007, PPS is a recent addition to the Microsoft Business Intelligence (BI) offering. PPS enables users to develop a variety of reports and scorecards, then compose them into a dashboard which is deployed to SharePoint. Business users can then simply navigate to a URL in a SharePoint site to access the dashboard.. Can you help with some background on scorecards and also provide the detailed steps involved to develop a scorecard?
I don't like it when functionality gets removed from an application. Especially something as obvious as linking to external systems using a URL. Why can't there just be a globe with a link icon in PerformancePoint Dashboard Designer, like Windows Live Writer?
Debugging Filter Links with Web Page Reports
Often when hooking up and testing dashboard filter links you may find the need to see precisely what value is being passed by that filter link. This becomes particularly urgent if you just can't figure out why things in your dashboard are not functioning the way you expect them to.
A few months ago the PPS team put out this post about using web page reports to mimic the absent OverridingURL functionality that used to exist in BSM. It is this technique that I have adapted to create a simple debug page to display the values a filter link passes across to other dashboard items.
Nick Barclay [MVP] BI-Lingual: Debugging Filter Links with Web Page Reports
MDM is the concept of managing those "reference data" lookups that seem to plague all organizations. Things like department structures, organizational and accounting hierarchies, product identifiers, campaign codes, application identifiers... anything which gets stored in a database, and a spreadsheet, and an email, and another spreadsheet, and then back into a database, then out to a reporting tool, then out to a spreadsheet, then out to Powerpoint, then out to an exec who says the data is all wrong and to start over.
You get the idea?
MDM is supposed to solve this problem. However, unlike all of these redundant "backups" of master data on various employee desktops, MDM could have the problem of Central Point of Failure, and this One Version of The Truth can quickly become worthless bytes on a disk if trust is lost in the accuracy of data.
Ownership, or Stewardship in this case makes a lot of sense. Good article around MDM follows.
Data Stewardship is a key success factor – MDM systems make data quality and accuracy more important than ever because a mistake in master data can cause issues in all the systems that consume the data. While automated match-merge, standardization and data quality tools are becoming more capable all the time, at some point real human beings who are passionate about the data are required to make decisions that tools can make and monitor the processes to ensure that business rules and data standards are being enforced correctly. This is just one more example of the people aspects of MDM being as important as the technology. While processes, policies, governance, and standards are the real success factors, a good MDM hub can provide tools and capabilities that make a data steward’s job easier. Some of the more useful capabilities are business rule enforcement, workflow, versioning, searching, auditing, and eventing. The combination of the Microsoft MDM system and SharePoint supplies all of these capabilities and more.
There's a working example of PerformancePoint 2007 with a SQL Health Dashboard at the link below.
SQL Server Database Activity and Health
and another with Excel services
http://richardlees.com.au/sites/Demonstrations/Pages/ExcelServices.aspx
and an Internet Analytics dashboard
Lots of useful demos on this site.
One of the key features missing from a relational data tool such as SQL Server is built-in Time Intelligence. SQL Analysis Services and PerformancePoint Server are 2 tools where time intelligence and analysis of historical data are simplified and available out-of-the-box.
Time Intelligence filters within PerformancePoint provide users with a way of specifying dates using everyday terms like Today, Yesterday, Last 6 months etc rather than having to select from a calendar control or pick different measures/metrics.
At its simplest, the PerformancePoint implementation of time intelligence filters are really just MDX generators, simplifying MDX for the end-user.
David's blog recaps this nicely.
FRxBuzz debates Crystal vs. FRx for Financial Reporting.
Why are there two reportwriters (usually Crystal and FRx in the midmarket) when you're implementing a new ERP? Why not just use Crystal to create the financial reports? Crystal is a database reportwriter. The database that contains the accounting transactions can have a couple hundred tables (or more). To create financial reports in Crystal, you would have to know lots of techie things like which tables contain the data you need (and tables can have very strange names!), which field to use to link those tables, how to structure the join that links the tables, the field names for the data you need, and how the application flags items in the database like whether a transaction is posted, how to separate budget from actual, how to exclude void transactions, and a whole host of other things absolutely critical to accuracy.
How do you do that when you’re an accountant without extensive training on this one database? Answer: you don’t. You use FRx instead.
FRxBUZZ :: The Techie Version of Life's Too Short To Do Balance Sheets in Crystal
I still can't track down the elusive Management Reporter download.
If you’re entertaining the idea of migrating from FRx to Management Reporter, here’s a laundry list of functionality, both what’s new and what’s missing.
Dig deeper into the PerformancePoint cubes with this set of utilities.
ToolBox
SQL Server Analysis Services Performance Monitoring and Visualization Methodology
This methodology collects and processes SQL Server Analysis Services trace files by using SQL Server Integration Services and SQL Server stored procedures. It then utilizes SQL Server Reporting Services reports to visualize performance issues related to MDX queries and processing jobs. This methodology includes components to work with both SQL Server 2005 and SQL Server 2008 CTP6.
SQL Server Analysis Services Performance Monitoring and Visualization Methodology - ToolBox
Podcast around PerformancePoint and Business Intelligence with National Money Mart, T4G, and Microsoft.
Business is getting more and more competitive and many organizations are looking to be able to get an edge by collecting data and analyzing it to make better business decisions. One company may want look at weather patterns to better stock their stores while another may need to analyze information to figure out how the introduction of a new product may cannibalize the sales of other products.
The use of business intelligence is not just for large conglomerates. The technology is available so that smaller and mid-sized companies can also make use of data to grow their businesses intelligently. But the implementation of a business intelligence system is not without its challenges.
Join me as I chat with Ed Raffin from National Money Mart, Paul Barter from T4G and Ryan Dochuk from Microsoft Canada and we discuss common pitfalls of using business intelligence for performance management and how National Money Mart is avoiding them and using business intelligence for its planning department.
13 MB of PerformancePoint goodness for Management Reporter demos...
PerformancePoint 2007 Data Integration Toolkit
Brief Description
The Data Integration Toolkit is a set of software, documentation, and samples designed to enable partners and customers to expose data from financial source systems in a standardized, contextual format for use in reporting, planning, monitoring and analytics scenarios.
Download details: PerformancePoint 2007 Data Integration Toolkit
The Dashboards by Example blog has some good, well, examples...
Related Executive Dashboard Posts:
- Red Green Yellow Indicator Icons Used at Google...
- Automotive Maintenance Dashboard - a real-world, physical dashboard used by auto mechanics to inspect your car...
- Executive dashboard with big indicator icons for at-a-glance status of projects...
- Xcelsius Dashboard Status Indicators...
- Banking Performance Dashboard - Scorecard on financial performance, customer value, internal processes, growth...
- Manufacturing Shop Floor Dashboard Screenshot - status, metrics of a bottling line...
- Information Technology Management Scorecard - measuring effectiveness in six areas...
- Red Light - Green Light - 123 - Dashboard Indicator Icons, a graphic resource for enterprise dashboards from The Dashboard Spy...
Web integration is the obvious next step for analyzing customers, competitors, and providing up-to-the-minute information on BI data.
Nick has a solution... just make sure you're not searching on credit card numbers...
Creating Dynamic Web Page Reports
As a follow up to my post on debugging filter links with web page reports I thought it would be worthwhile to put together a quick example to illustrate just what an unsung hero the web page report type is. Just think of the web page report as the catch-all report type. Basically, if you can't do it with one of the many pre-baked M&A report types then the web page report is what you want.
Mauro talks about a new book worth looking at on the various Office Server products.
There is a new techie book on the market that I had the pleasure of contributing on. It is the "Administrator's Guide to Microsoft Office 2007 Servers". I wrote some of the PerformancePoint content (I love that product!). I liked the concept behind this book; an administrative guide to installing and configuring all the major Microsoft server products. It's great bookshelf material for anyone responsible for managing a Microsoft-centric environment.
Jeff's taken this one
stepLEAP further - This is your PerformancePoint Bible:Help, How To, Guides, Manuals, & Forums
Planning Help: How do I ...
- Roadmap: How do I write a business rule?
- How do I export data with an outbound rule?
- How do I use an association to move data between models?
- PeformancePoint Planning Server capabilities?
- See more Planning "How do I" topics
Monitoring Help: How do I ...
- Link scorecard KPI's to analytic reports
- Keyboard shortcuts in Dashboard Designer
- Create a strategy map
- Create a Web page report
- Filters in Dashboard Designer
- See more Monitoring "How do I" topics
Guides and Manuals
- PerformancePoint Server 2007 Deployment Guide
- PerformancePoint Server 2007 Operations Guide
- PerformancePoint Server 2007 Planning and Architecture Guide
- PerformancePoint Planning Business Rules Development Guide
- PerformancePoint Monitoring SDK Reference Manual
User Forums on TechNet
This is a GREAT resource for both customers and partners.
Lots of templates and iconsets for your dashboarding solutions from Dashboard Spy.
Dashboard Templates
The most valuable thing that The Dashboard Spy website can give you (other than the guided tour through hundreds of dashboards, that is!) is the head start on your dashboard project via these templates. With these dashboard templates, you can literally save weeks or months on your project. Don't believe me? Download one today and see why.
“Where are PerformancePoint ScoreCard comments stored and how can we access them?”
Last Tuesday evening I delivered a presentation, Summing up BI features in MOSS. There was a lot of discussion and questions around Reporting Services integration as well as PerformancePoint Server 2007 integration. One question asked was where comments (or annotations) added to scorecards via the browser and/or Dashboard Designer are stored, from the perspective of accessing and doing comment roll-ups (and no, it’s not a SharePoint list as someone suggested :-)). This is something I’ve also been pondering so jumped in earlier today to take a look. This post details my initial findings.
MOSS (SharePoint) – PerformancePoint Scorecard Comments (Annotations)
PerformancePoint sits smack dab in the middle of this map. Coincidence or subliminal message?
Microsoft Office Interactive Developer Map
The Microsoft Office Interactive Developer Map is a Windows Presentation Foundation (WPF) application that helps developers visualize the different programs, servers, services, and tools that will help them build solutions. It allows them to drill down to each product and technology and learn about new features, objects, Web services, namespaces, and schemas required to extend Microsoft Office and build custom Office Business Applications (OBAs).
An excellent overview of Sharepoint & reporting/workflow tools and architecture.
After working with Excel Services, it appears to be great for hosting static and formula-driven Excel spreadsheets on the web, however programming it is a different story. Don't expect it to web-ify your Excel LOB application. Look for VSTO + VIsual Studio 2008.
I would focus on using Reporting Services + Proclarity + PerformancePoint as a BI solution with a lot of flexibility and room to grow. Throw in some Analysis Services for some high-performance slicing and dicing.
3.2.1) Excel Services
This is quick overview of excel services however it Excel Services is not a robust tool. After reading the list of Unsupported Features in Excel Services you will find out it may not provide what is needed for Excel power users (macros are not supported). It would not be recommended to use Excel Services as a replacement for enterprise reporting platform while tools such as Performance Point Server are built for this. Understand its limitations before using it.
K2.net and MOSS Collaboration Distillery: MOSS 2007 Architecture and Performance Considerations
More from Dan's blog.
Online PerformancePoint Server (PPS) 2007 Training
If you haven't had a chance to check it out yet there is some free training available online (videos and downloadable slides). In order to download the workbooks you might be prompted to install the latest version of the .NET Framework (3.5). If I recall correctly, this install required a reboot on my laptop. Here is what is available in regards to training and I add the duration of the videos so you can plan accordingly:
Building a planning budgeting or forecasting application
Module 1: Business integration (29:20) video | workbook
Module 2: Architecting a PPS 2007 planning application (39:20) video | workbook
Module 3: Fundamentals of a planning application (19:22) video | workbook
Module 4: Building business models and data dimensions (44:04) video | workbook
Module 5: Data Loading (22:15) video | workbook
Module 6: Managing roles and security in a planning application (16:51) video | workbook
Module 7: Fundamentals of the Excel add-in for PPS 2007 (26:14) video | workbook
Module 8: Embedding rules and calculations into a planning application (25:53) video | workbook
Module 9: Planning process management and workflows (19:06) video | workbook
Building a scorecard, dashboard, or analysis application
Module 10: Fundamentals of monitoring and analyzing data with PPS 2007 (19:40) video | workbook
Module 11: Fundamentals of dashboarding, scorecarding and analytics (33:05) video | workbook
Module 12: PPS 2007 elements for monitoring and analysis (1:13:55) video | workbook
Module 13: Building dashboards with PPS 2007 (21:27) video | workbook
Module 14: Managing security in dashboards, scorecards and analytic (15:44) video | workbook
8:04 PM | Add a comment | Send a message | Permalink | View trackbacks (0) | Blog it | PerformancePoint
Good training links from Dan's site.
Here is what is available (looks like the second one on the list is not uploaded yet and the third one got uploaded twice):
#00: Platform Overview (25 minutes, 25 seconds)
#01a: Introduction to Microsoft BI (20 minutes, 33 seconds) ??? (not available yet)
#01b: Introduction to SQL Server 2005 Tools and Sample Databases (13 minutes, 16 seconds)
#02a: Developing a Query-Driven E-mail Delivery System (17 minutes, 37 seconds)
#03a: Loading and Integrating Data from Multiple Sources (17 minutes, 13 seconds)
#04a: Identifying and Importing New Customers by Using Fuzzy Lookup (12 minutes, 5 seconds)
#04b: Implementing Slowly Changing Dimensions in the Data Flow (16 minutes, 11 seconds)
#04c: Advanced Package Design (13 minutes, 26 seconds)
#05a: Designing a Basic Report (18 minutes, 10 seconds)
#05b: Adding Interactivity to a Report (13 minutes, 59 seconds)
#06a: Developing a Report Model and Designing a Report Builder Report (15 minutes, 46 seconds)
#07a: Deploying and Accessing Reports (16 minutes, 23 seconds)
#09a: Designing Dimensions (16 minutes, 55 seconds)
#09b: Advanced Dimension Design (12 minutes, 25 seconds)
#09c: Using the Cube Wizard (13 minutes, 20 seconds)
#09d: Introducing Cube Calculations (14 minutes, 55 seconds)
#10a: Designing Reports on the UDM (15 minutes, 28 seconds)
#10b: Exploring Analysis Features of Microsoft Excel 2007 (14 minutes, 57 seconds)
#11a: More Analysis with Microsoft Excel 2007 (12 minutes, 45 seconds)
#12a: Introducing Data Mining (14 minutes, 54 seconds)
#12b: Introducing the Table Analysis Tool for Excel 2007 (15 minutes, 28 seconds)
#14a: Building a Performance Point Scorecard (15 minutes, 55 seconds)
Dan English's BI Blog: Microsoft Business Intelligence Screencasts
I was asked last Monday to fly last minute down to Redmond to perform a 1-day review of the PerformancePoint Beta Exam for Friday. I still don't know if I passed yet, but it looks promising.
Anyway, I madly arranged flights & hotels, and was just about to confirm my flight when I found out they had filled the spot I was filling for someone else.
Needless to say I was a bit peeved. But I think things work out for the best, or, like Seinfeld, they even out in the end. Sounds like it would have been a wet weekend, and I probably would have been stuck in the airport anyway. Next time I'll try for the summer instead.
We got dumped with snow on Sunday up here too, which closed the airport for awhile.
Because it always snows in Canada.
I often defend Seattle when people say it rains all the time. I say, "Actually, it doesn't rain that much, it is just gray a lot." But this just in, my basement is flooded. And it is still coming down sick in Seatown! I've been carting 5-gallon buckets of murky business up my stairs to dump in the backyard all morning, to the tune of:
- no apparent result, and
- one blister.
You're on your own, Seattle.
Don't come crying to me.
One of the capabilities of Business Scorecard Manager 2005 (BSM) that was changed in PerformancePoint was the ability to specify a URL to link to upon clicking a KPI. This was the Overriding URL property in BSM. It is still possible to get this functionality in PerformancePoint, but it requires adding an aspx page (attached to this blog post) and implementing the following procedures.
The Microsoft PerformancePoint Team Blog : Implementing a Hyperlink from a KPI in a Scorecard
A package of cubes that integrate with PerformancePoint Dashboards, Charts, KPIs, and Scorecards? Nothing to do but map data? Sounds like the Scorecard Accelerator model taken to another level.
Microsoft is working on a project called Microsoft Enterprise Cube.
Summary: While you're developing software that integrates with PerformancePoint Server 2007 Dashboards, you may find it helpful to turn on CriTrace. CriTrace traces all the calls to the CreateRenderingInstructions web method in RenderingService.asmx and dumps the parameters passed to that web method out to the server's file system as xml files.
Master Data Management (MDM) has been a problem since the first customer management systems were designed. I remember maintaining a Broadbase/Kana system that utilized a 'deduping' process to unify multiple customer attributes into a single unique customer identifer. Back then the process was cumbersome, unreliable, and generally messy. Though Analysis Services provides a Unified Dimension Model (UDM) that supposedly provides a single view of your data, it still requires a clean relational data source. Garbage in... Garbage out.
If we get accepted to the Technology Preview for MDM, I will see if MS has solved this problem with Microsoft MDM, or if this is just another BI/Data Management tool to add to the MS stack that seems to be getting more crowded every day.
Within OLAP data, hierarchies are a key feature, and keeping them clean and up to date can be tough. One of the core problems with our last project was trying to understand how to best map and consolidate this chart of accounts into a single model from multiple sources.
Perhaps this does the trick?
For example, one of the things that most impressed us about the Stratature product is that they do a better job than just about anybody we have seen at managing hierarchies. When we talked to our internal IT people they said they were buying a copy of Stratature +EDM primarily for its hierarchy management capabilities because they found many people were spending a significant amount of time managing hierarchies in spreadsheets on their desktops and this not only lead to lots of duplication of effort but in some case could be error-prone if the wrong spreadsheet was used.
This information lead to quite a few statements that Stratature was only a hierarchy management system. Stratature is a very fully-featured MDM hub and hierarchy management – while it’s cool – it only a small part of what it does. Going back to the whispering analogy, this is like starting with a statement that I bought a pair of shoes because they had really cool laces and ending up with I bought a pair of shoe laces.
Source: Have Data Will Travel
Speaking of shoes, my wife got me a pair of North Face Endurus XCR BOAs last January on our LA vacation in Beverly Hills. The coolest thing about them? No laces to do up.
I read about them in a Wired article about Dean Karnazes on the plane to LA, and I thought they sounded amazing. Dean's story is truly inspirational too. It got me motivated to start running... or at least to get the gear for when I decide to.
(I did walk over 200 blocks last weekend in New York, and I followed Dean's secrets to success (sleeping less, eating junk food and pushing your body to the point of death). Not sure that I'll go running any 50 state marathons any time soon though.)
To tighten the shoes, you turn a knob on the back similar to a ski boot. To loosen, you pull the knob. People tend to turn their heads to try and figure out what the heck you are doing with your clicking shoes, until I tell them the laces are stainless steel. Yes they're geeky, but at least they're sports-geeky.
It still appears to be a V1 product. There are some glaring defects that I was a bit bothered about, though I haven't bothered to call the company about them. After about 1 month, the rubber washer on the back dial on the back broke, making turning the knob a bit harder on the fingers. These things are no good with pants, as they catch annoyingly on them.
A few days ago, I noticed that the tensioner on the back started to skip as if it was stripped. For the most expensive shoes I have ever owned, I am getting the same experience I would expect with a high-performance British race car - low reliability.
My fault for believing I would become an Ultra Marathon man like Dean Karnazes, just by stepping in his shoes. They are really comfortable though, they're a conversation piece, and they never come undone.
And since he's #27 on the list of Time's World's 100 Most Influential People, I shouldn't feel too bad. Is it the shoes, Dean?
So yes, I did end up buying a pair of really cool laces wrapped in a shoe.
Free Training for PerformancePoint Server 2007 Now Available
Good news! There are now 9 training modules for the Planning side of PerformancePoint, and 5 modules for the Monitoring & Analytics side.
Here are a sampling of the titles available:
- Module 3: Fundamentals of a planning application
- Module 7: Fundamentals of the Excel add-in for PerformancePoint Server 2007
- Module 8: Embedding rules and calculations into a planning application
- Module 10: Fundamentals of monitoring and analyzing data with PerformancePoint Server 2007
- Module 13: Building dashboards with PerformancePoint Server 2007
- Module 14: Managing security in dashboards, scorecards and analytic
All modules come with a video and a workbook, and are available for download here.
The Data Puzzle: Free Training for PerformancePoint Server 2007 Now Available
3000 Consultants Trained on PerformancePoint?
So says Eddie Short, a VP at CapGemini, attending the MSFT PerformancePoint Server launch in London a few weeks ago, when asked about his firm's commitment to the Microsoft product. In fact, that's not 3000 consultants in the next 5 years, that's 3000 consultants by the end of THIS year.
The Performance Guys: 3000 Consultants Trained on PerformancePoint?
Jason Morales' Microsoft BI Update v.14 - November
Another great Microsoft BI update from Jason Morales:
Hot Topics
PerformancePoint and BI resources have been arriving fast and furiously. Scroll down to the Resources section to check out the latest Operations and Deployment Guides, new sample data sets, online trainings, on-demand webcasts and whitepapers.
And on top of it all, this month marks the release of The Rational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007… (Nick Barclay, Adrian Downes)
Upcoming Events
Extending CRM Analytics via Microsoft BI Products
When: Nov 13 (Tue) @ 9-11am
Where: Denver, CO
Click here to register
St Louis BI/PerformancePoint Roundtable
When: Nov 28 (Wed) @ 8-10am
Where: St. Louis, MO
Click here to register
Omaha BI & Wine Tasting Event
When: Dec 6 (Thurs) @ 430-8pm
Where: Omaha, NE
Click here to register
Upcoming Webcasts
PerformancePoint Server Demo
When: Nov 13 (Tue), 11am PDT
Click here to register
When: Oct 30 (Tue), 11am PDT
Click here to register
Monthly Reporting with PerformancePoint Server 2007
When: Nov 16 (Fri), 1pm PDT
Click here to register:
SQL Server 2008: Expanding Business Intelligence Capabilities
When: Dec 6 (Thurs), 1pm PDT
Click here to register:
Training
PerformancePoint Server 2007 Training Sites
PPS Planning, Monitoring and Analytics, online training includes videos and workbooks
BSM & ProClarity self-paced online training
Business Intelligence Education Services
BI Courses, including SQL 2005, PPS and ProClarity
Microsoft Office PerformancePoint Server 2007 Monitoring & Analytics
ProClarity Training
Online & Instructor led
Partner Program – webcasts
Business Intelligence Using PerformancePoint Server 2007 Business Modeling (Level 200)
ISV Web Seminar Series Part 1: PPS – Leveraging PerformancePoint in your BI Solution
ISV Web Seminar Series Part 2: PPS - Planning
ISV Web Seminar Series Part 3: PPS - Monitoring
Online Training Webcasts - (keyword = performancepoint)
Resources
Microsoft BI
PerformancePoint Server 2007
Book: The Rational Guide to Monitoring and Analyzing with… (pre-order form: Available 11/15)
Book: The Rational Guide to Planning with… ((pre-order form: Available 12/15)
Flash Demo: Virtual Experience
Datasheets: Overview, Monitor, Analyze, Planning
Case Studies: Energizer, Oticon, Skanska, Gemplus, Capella, CompUSA
Webcast: Performance Management 101 with PerformancePoint Server 2007,
Webcast: Business Intelligence with Office PerformancePoint Server 2007
Webcast: Revenue Forecasting with PerformancePoint Server 2007
Download PerformancePoint Server 2007 Evaluation Version (x86)
Download PerformancePoint Server 2007 Evaluation Version (x64)
Sample: Monitoring Data here
Sample: Planning Data here
Data Import Wizard for Microsoft Dynamics AX Tool here
Scorecard Migration Tool here
PPS Planning & Architecture Guide
ProClarity
Search the Support Knowledge Base
Microsoft ProClarity Software Developer’s Kit 6.3
SQL Server 2005
SQL Server 2005 Business Intelligence
SQL Server 2005 – Learning Portal
Archived Webcasts, Virtual Labs and Podcasts
Microsoft SQL Server 2005: Scales to your growing business needs
Case Studies: Kelley Blue Book, National Aquarium, Barnes & Noble
SQL Server 2008
What’s New in AS2008? (blog)
Testers give high marks to new features in SQL Server upgrade
SQL Server 2008 sticks to schedule, Microsoft exec says
A Look at Microsoft SQL Server 2008
Analysis Services
Microsoft SQL Server 2005 Analysis Services Step by Step
Microsoft SQL Server 2005 Analysis Services (Paperback)
Applied Microsoft Analysis Services 2005… (Paperback)
Delivering Business Intelligence with… (Paperback)
Whitepaper: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
SharePoint Server 2007
SharePoint Business Intelligence
Data Mining
Webcast: Building Adaptive Applications with SQL Server Data Mining
Dynamics
Microsoft Dynamics-Everyone Gets It
Evaluate Microsoft Dynamics—What is Microsoft Dynamics?
Whitepaper: Evaluating Financial Management Software…Is it time for a change?
Microsoft & BI Blogs
The Microsoft PerformancePoint Team Blog
Patrick Baumgartner’s Unofficial PPS Blog
Intelligent Insight on PerformancePoint – PerformancePoint Server
Adrian Downe’s Blog – Great 7 Part Series on PPS Planning Features!
Ian Tien’s Unofficial PerformancePoint Server and Business Scorecard Manager Blog
Microsoft BI Blog – by Patrick Husting
Nick Barclay’s BI Blog –PerformancePoint Server resources
Chris Webb’s BI Blog – MDXtreme Programming
Vidas Matelis BI Blog – SQL 2005 resources
Microsoft OLAP by Mosha Pasumansky – Deep Insights into SSAS & MDX
Prologika (Teo Lachev’s Weblog) – Reporting Services insights
Direct Reports (Brian Welcker’s Weblog) - Reporting Services
The Data Puzzle – Solving the Enterprise Data Puzzle with Microsoft BI
Microsoft BI Partners
http://www.microsoft.com/bi/partners/partners.aspx
OLAP Report - summaries
Preview of PerformancePoint Server 2007
SQL Server Analysis Services 2005 (Microsoft)
Commentary: Business Intelligence Competency Centers
Dimensional Relational vs. OLAP: The Final Deployment Conundrum
Analyst Reports
Forrester Report Detail BPM Growth and Market Leadership
Gartner Research: Microsoft Office PerformancePoint Server 2007 Coming Soon to a Desktop Near You
Business Intelligence Virtual Labs
Try out a business intelligence virtual lab to test out the technology stack, including:
- Analysis Services
- Excel 2007
- Business Scorecard Manager 2005 (BSM)
- ProClarity
- Report Builder
- Integration Services
- Data Mining
More News Links & Press Releases
Microsoft ‘ecosystem’ is biggest, survey says
Microsoft Crafts Single Code Base for ERP Apps
Gartner data warehouse DBMS Magic Quadrant 2007: New tools, old mantras
Business intelligence set for a boom
Technology or Business – Chicken or the Egg, Part 3
Data Warehouses and Scalability
Business intelligence and corporate performance management software: What’s the difference?
Operational Business Intelligence – A Prescription for Operational Success
The Road to Business-IT Alignment, Part 1
Analytics and Experiments for Business: An Interview with Super Crunchers Author Ian Ayres
The Secret to Successful Business Intelligence: A Top-Notch Data Warehouse
Data Mining the Financial Markets, Part 1
