PerformancePointing

Angry Geeks and User DSNs

2011-09-28T07:18:00.000-07:00

Microsoft has added another item to their deprecation chopping block. It seems like they are focusing more on pairing down their software offerings and developer tools to a single trunk rather than adding branches to them. This time it is replacing OLE-DB with its older, less agile parent, ODBC. A roadmap which may not be the most current flavour is here.

ODBC (Open Database Connectivity) is a standard, platform-independent interface to databases. For years, ODBC has been shunned by SQL application developers in favor of OLE-DB, a faster, more object-oriented replacement. Many business users have been faced with the need to make sure their User DSNs or System DSNs are setup correctly when using Excel spreadsheets, Windows apps accessing databases, or Access linked databases. This headache was solved by utilizing the OLE-DB interface, which stored the connection string outside the ODBC Adminstrator box in Windows Control Panel and uses a more dynamic approach. This also removed some headaches for standardizing desktop configurations and making things more portable.

However, OLE-DB doesn't work in the cloud and MS has its head in the cloud right now.

There are pros and cons to using OLE-DB vs. ODBC, and apparently Microsoft has weighed in and decided the cons outweigh the pros, or at least there is no business justification for further investment in the technology, or they figure it's best to shove everyone screaming into the cloud.

What does this all mean? To me, it's sort of like getting rid of the Ford Mustang and replacing it with a Model T. Yes everyone has one, yes the parts are interchangeable and easy to fix, and yes it only comes in black, but is that a good thing if it only does 72km/h and gets flat tires every 3000km?

Pasted from <http://www.topspeed.com/cars/ford/1908-ford-model-t-ar32509.html>

Perhaps, if the Mustang had a faulty "Made in China" transmission...

If you're using OLE-DB for SQL Server to access a SQL database in your application, in 2018 this is no longer supported by Microsoft. This means many legacy apps will require retuning to operate with the ODBC drivers coming out of post SQL 2012 releases.

Other than Angry Geeks who need to change connection strings and rewrite code, who does this affect?

Customers who have long-term IT roadmaps will have to rethink their strategy in a couple of years. Who this may impact the most is those who invested in Analysis Services cubes and Integration Services ETL packages. These BI solutions don't play well with ODBC, and it's funny that MS is killing off something that really affects the SQL suite of products so dramatically.

Some comments from the developer community here and here.

So it's now ODBC in favour of OLE-DB, HTML5 in favour of Flash and Silverlight, Excel in favour of PerformancePoint Planning, Office Accounting, and Microsoft Money, Freecell in favour of Flight Simulator, Windows Metro on a tablet, phone or TV instead of Windows on a PC, and NASA balloons instead of manned Space Shuttles.

It seems Everything pre-2011 will be legacy in 2012 and MS will start with a clean slate, frustrating customers and developers in the process.

I have a box of discontinued Sidewinder joysticks if anyone is interested. Unfortunately you need a game port to USB adapter, which is probably more expensive than a USB joystick.

The personal computer in 2019.

RDA Blogs - RDA BI/SQL Server Practice Group Blog

2011-09-23T11:46:00.000-07:00

For the life of me, I can’t figure out why these things are so hard to find.

Finding SharePoint performance point Dashboard designer tool was a little tricky. It’s a click once download that is not available anywhere in MSDN downloads or any other place. However you can download it from your Business Intelligence Center page in SharePoint.

To make your life easier, if you ever need it and you already have PerformancePoint installed on your SharePoint sever then here is how you can get it:

Open your browser as an admin user

Go to this URL http://<server-name>/sites/<site-name>/pages/ppssample.aspx

Click the “Run Dashboard Designer” and let it download the tool for you.

In highly customized SharePoint 2010 sites this page can be hard to find.

RDA Blogs - RDA BI/SQL Server Practice Group Blog

Bidoma Alert | Bidoma.com

2011-09-14T11:44:00.001-07:00

Push-notification of Sharepoint PerformancePoint Services Alerts on KPIs.

Bidoma Alert is a productivity add-on for Microsoft® PerformancePoint®.

Bidoma Alert supports Microsoft PerformancePoint Server Monitoring & Analytics 2007 and PerformancePoint Services for Microsoft Office SharePoint 2010.

Customers are able to add extensive e-mail alerting capabilities to existing or new Microsoft PerformancePoint installations.

Bidoma Alert | Bidoma.com

Getting rid of GUIDs in SharePoint 2010 database names - The SharePoint Farmer's Almanac

2011-08-06T18:52:00.000-07:00

Getting rid of GUIDs in SharePoint 2010 database names - The SharePoint Farmer's Almanac: "'But dynamic duo who cares if the world runs out of GUIDs?' Well, to be honest the people it scares the most are the poor developers. And while we generally consider developers second class citizens that doesn't mean we wish them harm. And let's face it, if they didn't have GUIDs they would have nothing to do. And we all know bored developers will wander the halls of your office building aimlessly. Or worse they will congregate in places like the break room and will want to make small talk with you when you go to get an afternoon Mt. Dew. I don't know about you, but nothing ruins my day worse than idle chit-chat with a developer. Yucky.

Get rid of those GUIDs. Whoever decided to put GUIDs in Sharepoint database names and didn't allow the users to change them at install time should be made to type in GUIDs from memory for 30 days. Failure to type in a correct GUID would result in an electric shock.

SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE! - SQL Server Team Blog - Site Home - TechNet Blogs

2011-07-13T19:43:00.001-07:00

The latest SQL 2011 (2012?) bits are ready for public consumption, including the long-awaited Project Crescent.

You’ll need a Sharepoint 2010 SP1 instance to get a taste of it though.

Some of the key features:

Column-store indexes blow away performance ratings and flip SQL’s architecture on its side.
Great for read-only snapshots of data but can’t update the column-store indexed tables
Always-on – primary with up to 4 standby servers with read-only capabilities
BISM, BISM, BISM
Analysis Services multi-dimensional or tabular (Vertipaq in-memory) modes. Run setup twice for 2 instances.
T-SQL Windowing functions like Lag and Lead.
SSDT “Data Dude”
Relationship diagrams in PowerPivot
Ragged columns, Shared Connections in SSIS
Data taps in SSIS
Master Data in Excel
Data Quality Services

SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE! - SQL Server Team Blog - Site Home - TechNet Blogs

Some of the quirks and gotchas:

In January 2012 there is a good possibility you will be scrambling for a version that doesn’t expire in 180 days. Otherwise your 180 day install will just stop working. The YSQL2.012K feature.
Running the download file doesn’t actually install the software. It just uncompresses it automatically. Then goes away.
Analysis Services multi-dimensional or tabular (Vertipaq in-memory) modes. Run setup twice for 2 instances. Which one do I choose? The one without the features like parent-child relationships and many-to-many dimensions but supported by PowerPivot? Or “legacy” mode? Both will likely eat server resources.
Reporting Services wouldn’t let me log in until I added my account as admin, which required me to login somehow. Run IE as Admin to get around this.
No backwards compatibility for PowerPivot < CTP3.
MS appears to be killing off those developers who still want to run SQL on Windows XP. Denali won’t work on XP.
You need to install Powershell 2.0 first.
You need admin rights on the box to work with Analysis Services tabular models.
Installing Office 2010 after may break Analysis Services connectivity for some Reporting Services features.
Installing some components like DQS and Integration Services in a non-default location won’t work.
Clicking on multiple SQL files opens multiple instances of SSMS. Don’t click on multiple files… OR ELSE!

IInstalling Books Online was strangely confusing for me… pick the location where you downloaded Help libraries, huh?

Reinstall Visual Studio SP1 after installing CTP3. It will break VS 2010.
VS 2008 BIDS stops working? Haven’t experienced this yet and hope I don’t.

Lots of stuff to take in.

Performance Point Services 2010 Limitations - Microsoft Business Intelligence by Nishant - Site Home - MSDN Blogs

2011-04-20T07:10:00.001-07:00

Some limitations of PPS according to Nishant. My biggest limitation with the product is it no longer includes Proclarity, and they’re still trying to catch up with the features of that tool.

Customers I worked with loved Proclarity and I found it easy to use and navigate, and features like custom sets, decomp trees and heat maps were very powerful. MS really needs a desktop tool for their BI stack, outside of Excel. Perhaps Project Crescent might deliver on that, within a Silverlight smart client.

Performance Point Services 2010 Limitations - Microsoft Business Intelligence by Nishant - Site Home - MSDN Blogs

How Microsoft Excel Changed The World

2010-12-29T11:11:00.001-08:00

Microsoft discontinued PerformancePoint Planning, and the roadmap looks a bit blurry for Analysis Services. Data Mining is going to the cloud, and Reporting Services will most likely be integrated permanently into Sharepoint. Sharepoint could go to the cloud, leaving Excel on the desktop (or in the cloud) to take over the full suite of BI responsibilities at Microsoft.

An interesting article about the humble beginnings of Excel, the world’s most ubiquitous BI tool, on its 25th Anniversary.

As for Excel, Klunder still considers it a powerful tool. But he harbors no illusions about how it can be abused. “It’s the same thing with PowerPoint—Excel lets things look professional, and people assume there’s substance behind it.”

How Microsoft Excel Changed The World

And my favourite uses of Excel:

http://www.eeggs.com/items/8240.html

http://www.calculatedriskblog.com/2008/11/happy-thanksgiving.html

SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect

2010-12-28T11:43:00.001-08:00

For those of you using float expecting precise decimal values from SQL Server when performing aggregate functions or multiplication/division, don’t.

http://en.wikipedia.org/wiki/Fixed-point_arithmetic

http://en.wikipedia.org/wiki/Computer_numbering_formats

One recommendation for storing monetary values is to use the Money data type.

http://sqlcat.com/technicalnotes/archive/2008/09/25/the-many-benefits-of-money-data-type.aspx

Apparently this helps with performance during Analysis Services data loads, if currency type is selected in Analysis Services to match the data types. (getting rid of implicit conversion to double)

However, if you need to do any kind of basic math or rounding, money is not a good fit.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a544f732b84257dd/8926f7fa31fa4832?hl=en&lnk=st&q&pli=1

Use decimal with the precision you need to get a correct answer, or use float and always round your results.

http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx

Note that PerformancePoint Planning and cube writeback uses float data type, which can cause rounding and comparison issues.

SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect

Configuring extranet access for PerformancePoint Services 2010 - To the SharePoint - Site Home - TechNet Blogs

2010-11-22T13:08:00.001-08:00

Configuring Internet access (and mobile/PDA) to PerformancePoint whitepaper is available.

A new white paper, Configuring extranet access for PerformancePoint Services 2010, describes architectural considerations for deploying PerformancePoint Services in an extranet environment. The paper provides a detailed walkthrough of the recommended architecture that uses Forefront Threat Management Gateway (TMG) server as a reverse-proxy server, the Kerberos protocol for authentication of service and user accounts, and Microsoft SQL Server and SQL Server Analysis Services as back-end data systems.

Configuring extranet access for PerformancePoint Services 2010 - To the SharePoint - Site Home - TechNet Blogs

IBM Press room - 2010-10-21 IBM Acquires Clarity Systems - United States

2010-10-28T06:38:00.001-07:00

Coming close to a single source of total BI tools, IBM takes over a mid-sized financial planning and reporting technology firm in Toronto.

Clarity Systems delivers financial governance software that enables organizations to automate the process of collecting, preparing, certifying and controlling financial statements for electronic filing, in support of mandates by the SEC and other financial regulatory agencies. To significantly reduce the risks of potential error and the lengthy times required to create and file financial documents, Clarity Systems software allows finance professionals to seamlessly integrate information for more efficient planning, consolidation and financial reporting.

IBM Press room - 2010-10-21 IBM Acquires Clarity Systems - United States

Forced Parameterization: A Turbo Button?

2010-10-13T18:58:00.001-07:00

Forced Parameterization: A Turbo Button?

I never had the need to turn on the PARAMETERIZATION FORCED database option until this week. We pretty much use only stored procedures for our internal applications so the execution plans are almost always in cache and reused. This practice of using parameterized stored procedure calls, together with attention to detail in query and index tuning, allows us to comfortably handle several thousand requests per second on commodity hardware without taking special measures.

Forced Parameterization: A Turbo Button?

AMO Analysis Services Create KPI

2010-10-07T11:08:00.001-07:00

How to create a KPI from command-line/Powershell.

AMO Analysis Services Create KPI

First PerformancePoint Services 2010 Project Recap « Alan Whitehouse’s Ramblings

2010-10-06T08:35:00.001-07:00

Great article on some of the trials and tribulations of installing and designing PPS 2010 dashboard solutions.

Project Background
We recently completed Phase 1 of a PerformancePoint 2010 deployment for one of the largest insurance companies in Canada. In 2009, TGO Consulting engaged with them to deploy Dynamics GP as their new ERP system. Towards the middle of November, we heard through the grapevine that the client was also looking for a business intelligence solution and had already narrowed the list down to two well-known players in the industry – neither of which was Microsoft.

First PerformancePoint Services 2010 Project Recap « Alan Whitehouse’s Ramblings

Data Mining, Predictive Modeling, Techniques

2010-10-05T09:14:00.001-07:00

Though not specific to PerformancePoint or even SQL 2008 data mining, Statistica has an excellent and comprehensive site about Data Mining.

Data Mining is an analytic process designed to explore data (usually large amounts of data - typically business or market related) in search of consistent patterns and/or systematic relationships between variables, and then to validate the findings by applying the detected patterns to new subsets of data. The ultimate goal of data mining is prediction - and predictive data mining is the most common type of data mining and one that has the most direct business applications. The process of data mining consists of three stages: (1) the initial exploration, (2) model building or pattern identification with validation/verification, and (3) deployment (i.e., the application of the model to new data in order to generate predictions).

Data Mining, Predictive Modeling, Techniques

Dinesh's Blog :::: Being Compiled ::::

2010-09-23T08:36:00.001-07:00

Great how-to for PPS 2010 Sharepoint dashboards

We have been creating reports or dashboards with different types of technologies including PerformancePoint 2007 for seeing the insight of the business. Those who have used PerformancePoint 2007 know how it supports creating dashboards with various components like scorecards and reports. Now it has been further enhanced and fully integrated with SharePoint 2010. The PerformancePoint 2010 allows us to create rich, context-driven dashboards that show the business in every angle.

This post discusses the way of configuring the PeformancePoint 2010. Let’s try to discuss the way in step by step with relevant images but limiting, as it makes the post lengthy. This post does not discuss about configuring SharePoint 2010. If need, have a look on below posts;
http://dinesql.blogspot.com/2010/03/configuring-windows-2008-r2-for.html
http://dinesql.blogspot.com/2010/04/complete-farm-sharepoint-2010.html

Dinesh's Blog :::: Being Compiled ::::

XML/A Thin Miner

2010-06-04T08:41:00.001-07:00

XML/A Thin Miner is a tool that generates and executes DMX and XML/A for data mining queries.

XML/A Thin Miner

Excel 2007 – CubeValue Reporting and Excel Services

2010-06-04T08:36:00.001-07:00

Within PerformancePoint, and for cubes in general, Excel is usually the reporting tool of choice, or the tool being replaced by Monitoring scorecards. The pivot table in Excel can only get you so far in terms of set-based reporting. Sometimes free-form reports are required.

These functions provide this ability in Excel.

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

Cube functions

CUBEKPIMEMBER function

CUBEMEMBER function

CUBEMEMBERPROPERTY function

CUBERANKEDMEMBER function

CUBESET function

CUBESETCOUNT function

CUBEVALUE function

Plan Excel reports against OLAP cubes

Updated: 2009-02-26

In this article:

http://technet.microsoft.com/en-us/library/cc263194(office.12).aspx

Once the reports are built, they need to be consumed. Other than email distribution or publishing to a document library, Excel Services would be the preferred display method for companies using Sharepoint 2007 Portal Server Enterprise. (MOSS)

There are some whitepapers for Configuring Excel Services to support this functionality.

http://technet.microsoft.com/en-us/library/cc263430(office.12).aspx

White paper: Excel Services step-by-step guides

Updated: 2007-03-08

These guides help you learn about Excel Services in Microsoft Office SharePoint Server 2007 functionality and understand the relationships among various components of Microsoft Office SharePoint Server 2007. The procedures in these guides take you step by step through the process of configuring and using Excel Services to build fully functional PivotTable reports, charts, and a variety of other reports. You can also learn how to restrict access to spreadsheet data and connect to an external data source.

Download the following guides as Microsoft Word documents (.doc).

Setting up the Server - Step 1: Creating a Trusted Location with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84833&clcid=0x409)
Setting up the Server - Step 2: Configuring Single Sign-On for Excel Services (http://go.microsoft.com/fwlink/?LinkId=84834&clcid=0x409)
Setting up the Server - Step 3: Creating a Trusted Data Connection Library with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84836&clcid=0x409)
Building a Report Based on Cube Functions for Excel Web Access (http://go.microsoft.com/fwlink/?LinkId=84828&clcid=0x409)
Protecting Workbook Intellectual Property (IP) with View Item Rights (http://go.microsoft.com/fwlink/?LinkId=84832&clcid=0x409)
Using OLAP Formulas, Conditional Formatting, and Charts (http://go.microsoft.com/fwlink/?LinkId=84838&clcid=0x409)
Building an OLAP PivotTable Report with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84830&clcid=0x409)
Building a Relational PivotTable Report with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84827&clcid=0x409)
Building a Report with a Chart for Excel Web Access Based on Cube Functions (http://go.microsoft.com/fwlink/?LinkId=84829&clcid=0x409)
Creating a Multi-Report Summary Page of Reseller Sales Data (http://go.microsoft.com/fwlink/?LinkId=84831&clcid=0x409)

Excel 2007 - Cube

These Excel Services reports can be consumed as PerformancePoint Monitoring data sources.

http://blogs.msdn.com/b/performancepoint/archive/2007/07/12/how-to-use-excel-services-as-a-data-source.aspx

They can be referenced directly within PerformancePoint Dashboard Designer.

http://office.microsoft.com/en-gb/performancepoint/HA102524991033.aspx

SSIS Package Manager - PacMan

2010-06-04T08:19:00.001-07:00

A series of tools for ETL projects, including one that performs mass updates called PacMan.

http://ssisctc.codeplex.com/

http://bixpress.codeplex.com/

http://sqlservermddestudio.codeplex.com/

http://pacman.codeplex.com/

http://powershellscripttask.codeplex.com/

http://ssisunit.codeplex.com/

And the best of them all, for ETL and adds the missing components for BIDS.

http://bidshelper.codeplex.com/

Todd's World: Why Isn’t Predictive Analytics a Big Thing?

2010-06-03T06:47:00.001-07:00

Answer? Because it only works until it doesn’t.

Why Isn’t Predictive Analytics a Big Thing?

I’m baffled as to why Predictive Analytics still has the status of a fringe, niche, and advanced realm of the Microsoft Business Intelligence world. Business Intelligence without Predictive Analytics is like a bus taking you most of the way home from work, but dropping you off on the main drag leaving you with a two mile walk. Those two miles are doable and it is great exercise, but what if you’re short on time, ill or injured, it’s pouring rain, or it’s full of dangerous drivers? The bus may have taken you 90% of the way home in terms of miles, but that last 10% of miles is really half the journey in terms of time and effort.

Todd's World: Why Isn’t Predictive Analytics a Big Thing?

InformIT: Safari Books Online: Microsoft Office PerformancePoint Services 2010 Unleashed

2010-04-26T11:43:00.001-07:00

Have not posted here in awhile. Here is an upcoming book on PerformancePoint Services 2010 along with a “rough cuts” version that’s available now, for those early adopters.

The full version.

http://www.amazon.com/Microsoft-SharePoint-PerformancePoint-Services-Unleashed/dp/0672330946/ref=sr_1_1?ie=UTF8&s=books&qid=1272306230&sr=1-1

There is a rough cuts version of the 2010 book that is available, partially online.

http://www.informit.com/store/product.aspx?isbn=0789740753

http://my.safaribooksonline.com/9780789740755?portal=informit

InformIT: Safari Books Online: Microsoft Office PerformancePoint Services 2010 Unleashed

Download details: 2010 IW Demo VM (Beta)

2010-01-27T07:52:00.001-08:00

Considering an upgrade? If you’re planning on using PerformancePoint 2010 or Sharepoint 2010 in a local development environment, you may want to consider beefing up your system and getting into the Solid State Drive world.

2 or more internal SSD drives.
8+ GB of RAM

Sounds like an HP Envy to me…

System Requirements

Supported Operating Systems: Windows Server 2008 R2; Windows Server 2008 R2 Enterprise; Windows Server 2008 R2 Standard

In order to run this demo you will need the following: - Windows Server 2008 R2 with the Hyper-V role enabled. - Drive Formatting: NTFS - Processor: Intel VT or AMD-V capable - RAM: 8 GB or more recommended - Hard disk space required for install: 50 GB

Download details: 2010 IW Demo VM (Beta)

PerformancePoint Services : Upgrading PerformancePoint Server 2007 to PerformancePoint 2010 - Straightforward and easy?

2009-11-16T16:52:00.001-08:00

Doesn't seem that straightforward to me. Most customers I know that would run Sharepoint would have a hard time giving their BI groups access to their corporate Sharepoint portals.

The "Import PerformancePoint Server 2007 Content" option seems the most straightforward requiring the least amount of database access, however the limitation of requiring Sharepoint admin rights to create security groups might be an issue for some clients.

At least there are quite a few different options to migrate content.

Most of the customers who have been using PerformancePoint Server 2007 have accumulated several months, if not years, worth of dashboards and data. Their KPIs, grids, charts, scorecards, and custom objects have gone to good use, providing a great deal of corporate discussion about how to handle business decisions and to help plan for the future. And while the 2007 version of PerformancePoint helped to do this very well, the SharePoint BI 2010 version does it even better. Understandably, most companies want to build on top of their old dashboards in 2010. And the idea of starting from scratch is unthinkable. Fortunately, Microsoft has a nice migration path so that you can migrate all of your existing objects to the new version. The migration process is straightforward, but to help ensure that things go smoothly, we’ve created a set of steps to follow.

PerformancePoint Services : Upgrading PerformancePoint Server 2007 to PerformancePoint 2010

Microsoft Excel – Asymmetrical Reports

2009-10-07T13:21:00.001-07:00

No more counting rows of data in PerformancePoint and matching them up with the previously selected data to create asymmetrical reports. Excel 2010 has this functionality built-in… provided you have a cube or data source behind the scenes created.

I wonder how dynamic it really is though… seems like next year (or next month) I’m going to be changing the columns in my report manually. There had better be some dynamic time intelligence in there – without resorting to custom MDX.

Creating an Asymmetric Report with the New Set UI

A very common report layout needed by our customers is to show actual sales for the year 2008 while showing forecasted sales for the year 2009. Here is a PivotTable showing actual, budgeted and forecasted sales quotas for the last two fiscal years (click to see larger image).

As you can observe there is a lot of irrelevant information in our report such as Forecast for 2008 (since we have actual data for 2008) and Actual for 2009 (since this only includes 9 months of data). If I tried to hide Actual it would actually get hidden for both 2008 and 2009, but since I have Excel 2010 I can go ahead and create a set out my information on columns by simply going to PivotTable Tools -> Options in the ribbon, and selecting “Create set based on column items”.

Now the Named Set creation UI will pop up (click to see larger image):

The UI contains all the tuples that currently define the column labels of my Pivot report. Now I can easily remove the tuples I no longer want. In this case I am going to go ahead and remove:

Microsoft Excel

Fast GPUs Accelerate Performance of In-Memory OLAP Server | SYS-CON AUSTRALIA

2009-07-13T08:27:00.001-07:00

Looks like NVidia’s Tesla cards are getting some traction.

The development is based on the open-source Jedox's Palo OLAP Server. By utilizing fast GPUs originally designed for computer games, the team of researchers and developers was able to make the breakthrough and accelerate the performance of OLAP analyses with Palo by up to 40 times of its current performance.

A prototype of this GPU version of Palo will be introduced by Jedox at this year's CeBIT at their stand in hall 4, D 53. Additionally, Jedox will also present the new release of its multiprocessor version of Palo 3.0.

Fast GPUs Accelerate Performance of In-Memory OLAP Server | SYS-CON AUSTRALIA

PerformancePoint Server Planning Gets a New Lease on Life -- Enterprise Systems

2009-06-25T07:50:00.001-07:00

Not so much a new lease on life, as a transplant of an appendage that may or may not get rejected, the source code for PerformancePoint is released.

PerformancePoint Server Planning Gets a New Lease on Life

Microsoft last week announced a reprieve, of sorts, for beleaguered users of its PerformancePoint Planning Server.

By Stephen Swoyer

06/24/2009

PerformancePoint Server Planning Gets a New Lease on Life -- Enterprise Systems

Microsoft BI: Partners

2009-06-23T09:27:00.001-07:00

Microsoft releases the source code for PerformancePoint Planning to partners and customers.

Financial Planning Accelerator

Microsoft is pleased to make available the Financial Planning Accelerator (FPA). The FPA is source code and project files derived from the PerformancePoint Server 2007 Planning module. Based on requests from customers and partners, we are making this code available on a no-cost, individual license.
This is unsupported source code that customers and partners can use to support or change PerformancePoint Server Planning functionality. Derived object code files can be distributed to end users with Microsoft SharePoint Server Enterprise Client Access Licenses. To obtain access to the FPA a license agreement between Microsoft and the customer or partner is required. After that agreement is in place, download instructions will be made available.
Please e-mail fpasupp@microsoft.com to request the agreement.

Microsoft BI: Partners

Simple Studies - Best Free Online Accounting Lessons

2009-06-08T06:19:00.001-07:00

We provide free online accounting lessons that teach introduction to accounting. These accounting tutorials explain financial accounting principles in simple accounting terms. The website also has accounting exercises with solutions and accounting dictionary.

Simple Studies - Best Free Online Accounting Lessons

Reed Me : Bing: there it is! My PPS setup problem(s) solved.

2009-06-04T07:23:00.001-07:00

Some tricks to setting up PPS with SQL 2008.

Obligatory bing plug. The name is growing on me... Link #1 in the search results was Nick Barclay’s great post on sorting out PPS setup issues with SQL Server 2008:

"System.Web.Extensions" performancepoint "configuration error" - Bing

Useful reference linkages:

Installing and Troubleshooting M&A on Win / SQL 2008

Install PerformancePoint Monitoring Server with SQL Server 2008 (TechNet)

ASP.NET AJAX 1.0 (the missing piece what was causing the problem)

Reed Me : Bing: there it is! My PPS setup problem(s) solved.

Example Gallery – Tableau

2009-06-02T09:33:00.001-07:00

Tableau has some interesting data visualizations and can connect to both relational and OLAP data sources, including PerformancePoint Planning. Plus it’s really easy to use if you are familiar with pivot tables and drag-drop functionality.

Check out the blog for more details and other interesting examples

http://www.tableausoftware.com/community/blog

Example Gallery

Magic Quadrant for Corporate Performance Management Suites

2009-06-01T05:57:00.001-07:00

MS has been dropped from Gartner’s Magic Quadrant for CPM.

Magic Quadrant for Corporate Performance
Management Suites

Magic Quadrant for Corporate Performance Management Suites

Microsoft Excel : Common Questions Around Excel 2007 OLAP PivotTables

2009-05-22T07:03:00.001-07:00

Common Questions Around Excel 2007 OLAP PivotTables

Today's author: Allan Folting, a program manager who works on the Excel team.

I have gotten lots of questions from customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:

Filtering individual calculated members

Showing non-visual totals when filtering

I have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:

Adding calculated members/measures

Adding named sets

Today I'll discuss these topics and outline current ways and workarounds to address this.

Microsoft Excel : Common Questions Around Excel 2007 OLAP PivotTables

Ten Tips for Using SharePoint Server 2007 with Excel Services

2009-05-22T06:44:00.001-07:00

Ten Tips for Using SharePoint Server 2007 with Excel Services

Summary: Learn ten tips to improve your use of SharePoint Server 2007 with Excel Services from the SharePoint - Excel Services forum. (15 printed pages)

Joel Krist, Akona Consulting

May 2008

Ten Tips for Using SharePoint Server 2007 with Excel Services

Nick Barclay: BI-Lingual: PerformancePoint Monitoring Data Source Connection Problems

2009-05-22T05:40:00.001-07:00

A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.

That last sentence is important so it's worth repeating...

A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.

Nick Barclay: BI-Lingual: PerformancePoint Monitoring Data Source Connection Problems

Download details: Microsoft Windows Dynamic Cache Service

2009-05-05T10:46:00.001-07:00

Investigating bottlenecks with 64-bit servers? Check the memory and ensure it is available.

Overview

The Microsoft Windows Dynamic Cache Service will manage the working set size of the Windows System File Cache. For 64 bit systems this service helps to address the problem of excessive cached read I/O that could eventually consume all of physical memory. Sample source code and compiled files are included in the compressed file.

Download details: Microsoft Windows Dynamic Cache Service

Request for comment: Global filters linked to different models

2009-05-05T06:40:00.001-07:00

Modifying the RDL of a PPS Matrix to activate functionality within the product… tedious but useful.

We've found a way of implementing global filters (we use dimension property filters) for different models... and we'd like to know pitfalls of our approach (for the moment I can guess 2 :-))

Task: Apply global "Entity - Office" filter to Operations and Salary models

Approach:

1. Create all matrices and filters needed and apply corresponding filters to matrices.

Matrix 1: Model - Operations. Columns - Entity:Business unit Leaves. Rows - Account. Dimension property filter: Entity - Office.

Matrix 2: Model - Salary. Columns - Account. Rows - Entity:Employees. Dimension property filter: Entity - Office

2. Edit RDL

2a. Find filter definitions

<ReportParameter Name="PerformancePoint_ebb03fb2_b6f8_4298_91cf_ceb94ee473ff"> ' Filter from Operations Model to be applied to Salary model

…

</ReportParameter>

<ReportParameter Name="PerformancePoint_ef581824_8178_4b6b_a743_59a26f740964"> ' Filter from Salary Model to be "skipped"

…

</ReportParameter>

2b. Replace Name and Value for QueryParameters using appropriate name of one of the filters (ReportParameter) you want to use for different models:

<QueryParameters>

<QueryParameter Name="PerformancePoint_ef581824_8178_4b6b_a743_59a26f740964"> ' to be replaced with see bellow

<QueryParameter Name="PerformancePoint_ebb03fb2_b6f8_4298_91cf_ceb94ee473ff">

<Value>=Parameters!PerformancePoint_ef581824_8178_4b6b_a743_59a26f740964.Value</Value> ' to be replaced with see bellow

<Value>=Parameters!PerformancePoint_ebb03fb2_b6f8_4298_91cf_ceb94ee473ff.Value</Value>

</QueryParameter>

</QueryParameters>

3. That's it.

If I select Office for Operations matrix, it's also applied to Salary Matrix :-)

Guessed pitfalls:

1. Forms and reports created using this approach can't be migrated to other application - this is hardcode! :-(

2. Any change to report definition will destroy our filters override - this is hardcode! :-(

Any thoughts re other difficulties we'll meet?

Regards, Vasily

Request for comment: Global filters linked to different models

Log Parser Lizard GUI (free edition) - Lizard Labs

2009-04-29T10:16:00.001-07:00

Using Lizard to parse PPS Planning logs.

1. Create a header file.

DateTime Event PPS Location User Details

Note that spaces delimit the file.

2. Create a new Log Parser Lizard Group & Query for PPS.

3. Select Tab-separated and space-separated values

4. Enter Query

select top 100 *
from '\\Path_To_PPS_Server_Logs\trace log\server.log'
--where details like '%error%'
order by rownumber desc

5. In Query Properties:

iHeaderFile = <step 2 filename>
nFields = 6
iSeparator = ‘space’
FixedSep = true

Save and “Generate” (run) the query.

Log Parser Lizard GUI (free edition) - Lizard Labs

2009-04-29T08:57:00.001-07:00

Click here to download Log Parser Lizard

Log Parser is a very powerful, free and versatile tool that provides universal query access to text-based data, such as log files, XML files, and CSV files, as well as key data sources on the Microsoft Windows operating system, such as the event log, IIS log, the registry, the file system, and the Active Directory services.

Because the command-line interface is not very intuitive, I have created Log Parser Lizard, a GUI tool for managing queries, exporting results to Excel, charts, etc… In addition I have added input filters for RegEx and log4net input log formats (with support for regular expressions) and SQL server T-SQL queries. There are some helpful examples included in installation package to help you start using Log Parser Lizard (and it’s SQL like syntax) for a web log analyzer and system log analyzer.

Log Parser Lizard GUI (free edition) - Lizard Labs

Jon Gallant's Blog : Most Popular Microsoft Blogs – January 09

2009-04-22T10:36:00.001-07:00

Microsoft PerformancePoint Team Blog was #81. I wonder how it’s looking this month…

The Microsoft blogs solution has a lot of great features, but an important feature that is missing is popularity metrics. I want to know what the community is most interested in and keep up on all things Microsoft. The “all posts” feed that comes OOB doesn’t do the trick. My team inherited the Microsoft blogs apps (http://blogs.msdn.com/, http://blogs.technet.com/) recently and we are in the process of looking at adding new features to support many views over the data. Until we have all the details fleshed out I will continue post the 100 most popular MSDN and TechNet blogs on a monthly basis…complete with Opml so you can subscribe to them all at once.

Jon Gallant's Blog : Most Popular Microsoft Blogs – January 09

SQL Server 2008 Training Resources - Canada

2009-03-18T10:18:00.001-07:00

Large collection of training resources.

SQL Server 2008 Training Resources - Canada

The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX

2009-02-12T15:49:00.001-08:00

Roll your own Assumption Model with LookupCube.

with member [PurchaseAmt] AS LOOKUPCUBE("Project Materials Cube", "([Measures].[Purchase Amount],[Fy Qtr].[" + [FY Qtr].CurrentMember.Name + "])" ) -- We need to construct a string that looks -- exactly like this -- ( [Measures].[Purchase Amount] , [Fy Qtr].[2005 Q1]) select { [Measures].[HoursWorked], [Measures].[Total Labor], [PurchaseAmt]} on columns, [Fy Qtr].children on rows from [Project Labor Cube]

The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX

Cubegeek: Microsoft PPS: Dead.

2009-02-03T17:44:00.001-08:00

This site will soon be called www.performancepointed.com I think.

Per-for-mance-Point-ed - definition: To develop a planning & forecasting tool for the masses and bring the role of IT back into to Finance, and then communicate that you are discontinuing the tool after planning & forecasting cuts, and realizing many of the masses don't have multiple SQL Server Enterprise Edition and Sharepoint servers just laying around.

Disappointment indeed. It's now official. I can say with finality that I wasted a year in my career.

Cubegeek: Microsoft PPS: Dead.

Demand for PerformancePoint jobs in the UK experienced almost a 25% increase year over year.

If that was a stock I would probably be selling it. Nice return and unsustainable growth?

Speaking of stock...

The trendline on this doesn't look very good, though I would suspect that we have some bottoms around here. MS hasn't seen 18 bucks since the Asian crisis of 1998.

From ITJobsWatch

I guess that intangible benefits like CFO visibility and the "potential" of Windows/Office 2007/SQL Server 2008/Sharepoint license sales and adoption don't matter in an economic downturn. Or perhaps the "what-if" analysis MS built for themselves was too good for their own good? Maybe they forecasted something they didn't want to see?

I have been working with Planning since CTP2 and am a certified PPS Planning Trainer. When they demo'ed Performance Point (Business Scorecard Manager) way back in 2005 at a partner convention I went to in Redmond, there was some serious excitement there. Finally, MS would have the proper client tools for Analysis Services to compete with other OLAP vendors on, instead of some Office Web Components and a pivot table.

Planning was just a bonus. Bonuses get cut back in economic downturns... unless you're a bank, or a certain bank CEO. (Isn't MS a bank too?)

The good news is that PPS Monitoring is now free with Sharepoint (or is it Sharepoint is now free with PPS?). Now if they would only release the source code for Planning & Management Reporter (and why not toss in Proclarity Desktop too?) so we can go back to building custom-tailored planning solutions without the constraints of a packaged planning tool... and have the ability to fix some of the troublesome bugs and performance issues inherent in a V1 product.

At least give us the source code for that Excel add-in reporting tool. Lots of good possibilities there.

Nothing wrong with a little .NET code to solve some business issues. So much more fun debugging rules in Visual Studio than trying to debug and maintain rules in a client tool like the PPS-P GUI. Planning should have been a web application to begin with. Keep it in the cloud guys...

Well, I'm off to develop the "yellow cell input" feature checkbox on the Excel 2007 ribbon. The checklist for getting mine to work should be a bit shorter than the current "yellow cell input" checklist for Planning.

1. Click the 'yellow cell input' checkbox in Excel 2007 (or just format the cell as INPUT).
2. Input your values.
3. Add your commentary next to the values.
4. Publish to your database and refresh the cube.

This was probably how Biz#/Planning got started too...

I think we're on step #6 of the definition for performance at Microsoft.

per·form·ance [ pər fáwrmənss ] (plural per·form·ances)

noun

Definition:

1. artistic presentation: a presentation of an artistic work such as a play or piece of music to an audience

2. manner of functioning: the manner in which something or somebody functions, operates, or behaves
a high-performance car

3. working effectiveness: the way in which somebody does a job, judged by its effectiveness ( often used before a noun )
performance-related pay

4. thing accomplished: something that is carried out or accomplished

5. accomplishment of something: the act of carrying out or accomplishing something such as a task or action

6. display of behavior: a public display of behavior that others find distasteful, e.g. an angry outburst that causes embarrassment ( informal )

7. linguistics language produced: the language that a speaker or writer actually produces, as distinct from his or her understanding of the language.
See also competence (sense 3) parolen (sense 5)

http://encarta.msn.com/dictionary_1861724999/performance.html

Sacha has more info.

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2009/01/29/rip-performancepoint-planning.aspx

So does Ajay.

http://ppsinfo.blogspot.com/2009/01/pps-planning-is-being-discontinued-what.html

Convert PivotTable to PerformancePoint Planning Matrix - Sacha Tomey's blog

2009-02-03T17:20:00.001-08:00

Sacha finds an interesting "upsizing" feature for Pivot tables.

Now if only it was so easy to convert from Report Wizard to Matrix to Custom MDX to Management Reporter Reporting Services... and back again.

There should be a market for PerformancePoint Planning RDL converters pretty soon...

Convert PivotTable to PerformancePoint Planning Matrix

I spotted a 'Convert to PerformancePoint Matrix' menu option on the context menu
of an Excel PivotTable recently.

I'd not noticed it before - it is documented within the PPS Excel Add-In help but only once, and subtly at that. I'm glad to report, on first impressions, it appears to do a fairly decent job.

It's a one-way process that cannot be undone and it creates a 'User defined' MDX style matrix. You are therefore limited to making subsequent edits using the Report Properties window but suffice to say it's a handly little feature that I'll no doubt use more and more now I've found it.

Convert PivotTable to PerformancePoint Planning Matrix - Sacha Tomey's blog

Download details: 2007 Office System Driver: Data Connectivity Components

2008-12-29T14:19:00.001-08:00

This download will install a set of components that can be used by non-Microsoft Office applications to read data from and write data to 2007 Office system files such as Microsoft Office Access 2007 (mdb and accdb) files and Microsoft Office Excel 2007 (xls, xlsx, and xlsb) files. Connectivity to text files is also supported.

ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.

Download details: 2007 Office System Driver: Data Connectivity Components

Regular Expressions Cheat Sheet (V2)

2008-12-21T07:27:00.001-08:00

This post doesn't have a lot to do with PerformancePoint specifically, however there are lots of possibilities to use Regular Expressions to solve some of your business requirements.

With the previously blogged-about plugin for Excel (morefunc) you have the ability to use Regular Expressions as formulas in Excel. You can also use Regular expressions in Excel with some VBA code.

What are Regular Expressions? They're anything but regular. They allow you to design expressions to apply to tasks such as searching for patterns of data, data validation, etc. Rather than just searching for the @ sign to find a "possible" email address, you could use regular expressions to find an exact interpretation of an email address with this regular expression.

\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b

You could also use this as a data validation rule to ensure that you are cleanly entering email addresses into a form for later import into the application using CSV.

These expressions look very cryptic until you get the gist of the syntax.

These types of tools could be handy for cleansing data before they enter the PerformancePoint staging database or within an input form. They could also be useful for mapping and profiling data.

For example, labels are required to be no more than 40 characters in length, member properties are restricted to 100 characters, and there are a series of restricted characters and reserved words. You could design a regular expression that checks for these values within your data before it is loaded into the system. You could build something to do this in Excel or use SSIS, or even in a stored procedure before moving the data into a label table.

Another application would be to "tokenize" or split a pattern into a comma-delimited string. (eg. Account-Dept-Location), Lexical Analysis in geekspeak terms.

http://effbot.org/zone/xml-scanner.htm

http://en.csharp-online.net/CSharp_Regular_Expression_Recipes%E2%80%94A_Better_Tokenizer

It is easy to tokenize a value with 3 hyphens without learning regular expressions, but what if you needed to parse something like this to get the numbers out?

123ab2c
1234abc

Regular expressions might be useful...

How about a search/replace, but with intelligence?

http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.replace.aspx

Within SSIS, you can use this to perform data profiling and to classify data into buckets.

SSIS Regex Component
SSIS Regular Expression Transformation

You can use it in SQL

SQL and Regular Expressions
Regular Expressions with PATINDEX in SQL
Regular Expressions with OACREATE in SQL
Working with crummy data in SQL

It's about as handy as knowing XSLT and MDX

Regex is as forgettable as XSLT and MDX

The most useful tools for learning Regular Expressions:

Regulator and Regulazy

Some 3rd-party implementations of Regex:

Regular Expressions in Matlab
Regular Expressions in SAP Routines (Converting dates, thousands separator,

A deep dive into Regular Expressions.

Regular Expressions in Wikipedia.
All you ever need to know from 4guysfromrolla

I can see this one being useful to translate the XML format that PEL rules are stored in back to a readable text interpretation... without using an HTML parser.

Removing All HTML Tags from a String Using Regular Expressions

http://www.4guysfromrolla.com/webtech/073000-1.shtml

These type of statements are great for enforcing some of your business rules/validation logic using a data-driven approach.

UPDATE updateTable
SET ut.field1 = dbo.regexReplace( ut.field1, rt.regex, rt.replace, 1, 1 )
,ut.field2 = dbo.regexReplace( ut.field2, rt.regex, rt.replace, 1, 1 )
FROM updateTable AS ut
CROSS JOIN regexTable AS rt

DECLARE @strTest varchar(8000) SELECT @strTest = '\\servername\foldername99999\filename.ext' SELECT [Position]=PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest), [Before]=SUBSTRING(@strTest, 1, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest)-1), [After]=SUBSTRING(@strTest, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest), LEN(@strTest))

Regular Expressions to decompose MDX query

Regular expressions can be useful to decompose a MDX query (with elements explicitly defined, i.e. not using MDX functions) and retrieve involved objects (i.e. dimensions, levels, members, etc.). I often used them in performance improvement processes or for MDX pre-validation purpose.

How about being able to parse your calculated measures out of the MDX script used by the cube?

http://geekswithblogs.net/darrengosbell/archive/2006/08/10/87561.aspx

Or building some pattern filtering functionality using MDX stored procedures?

http://blogs.conchango.com/christianwade/archive/2006/04/30/3896.aspx

There's nothing stopping you from using the System.Text.RegularExpressions libary in Reporting Services functions either.

http://msdn.microsoft.com/en-us/library/ms157328.aspx

Here is a good resource to better understand Regular Expressions. There are plenty of possibilities - just make sure when you get this hammer, everything doesn't look like a nail.

The Regular Expressions cheat sheet is a one-page reference sheet. It is a guide to patterns in regular expressions, and is not specific to any single language.

Regular Expressions Cheat Sheet (V2) - Cheat Sheets - Added Bytes

Peter Eb. : When will data appear in the cube after a submit from the PPS Planning Business Client?

2008-12-19T11:00:00.001-08:00

Once you’ve used the handy dandy Yellow Cell Checklist found here, how do you know when the data appeared in the database?

When will data appear in the cube after a submit from the PPS Planning Business Client?

Peter Eb. : When will data appear in the cube after a submit from the PPS Planning Business Client?

PPS SP2 and SQL 2008 revisited - Windows Live

2008-12-16T09:33:00.001-08:00

How to install SQL 2008 with PerformancePoint.

After 2 days I finally figured out why the pps olap charts and grids would not work in my pure 2008 environment. Also proclarity. You must install the SQL 2005 CU9 redists BEFORE installing SQL Server 2008. Here is the order of operations

Install Windows Server 2008

add IIS and AppServer roles (incl. CGI, ASP, IIS6 mgmt)

Install the 4 SQL 2005 CU9 redists

Sqlncli

SQLServer2005_ADOMD

SQLServer2005_ASOLEDB9

SQLServer2005_XMO

Install SQL Server 2008

DB (incl. full text search for AdWorks sample), AS, RS

Install MOSS 2007 SP1

Create SSP, site collection

Install PPS 2007 RTM

do NOT run config mgr

install PPS SP2

run Config mgr

Install PAS/Desktop

install SP2

Install Visio, Office, etc

PPS SP2 and SQL 2008 revisited - Windows Live

Yellow Input Forms for the Assigner Check

2008-12-15T18:49:00.001-08:00

Courtesy of Sundar in the Planning forums:

Follow the steps for the reviewer to display the editable cells:

Connect to the Server and Select the Assignments
Select "Select Review or approver submission of this form link"
Highlight the User Row with Status Submitted to enable the 3rd Icon with pencil
Select the Pencil Icon to open the assignments
New Assignment window with Yellow color writeable region is displayed
Refer to PerformancePoint Add-In for Excel Online Help on Assignments

BET: Microsoft Excel Formulas - 3D Formulas

2008-12-12T09:36:00.001-08:00

Creating a Summary worksheet (Cube) directly in Excel.

As you move to PerformancePoint for reporting, this scenario will hopefully be less relevant.

But what if you have 25 worksheets and you need to sum up the same fields in all of them on a consolidation worksheet.

Named ranges and colons are your friend.

What is a 3D Formula ?

These are also known as cubed formulas.

A reference that refers to the same cell or range on multiple worksheets is called a 3D reference.

Using 3D formulas allows you to calculate data thorughout a workbook using multiple worksheets.

All 3D formulas are based on the syntax: Sheet1:Sheet4!A2:B5.

A 3D formula is a formula that refers to the same cell (or range of cells) on multiple worksheets.

The 3D formula "=SUM(Sheet1:Sheet4!A2)" can be used to add up the numbers in cell "A2" on 4 different worksheets.

BET: Microsoft Excel Formulas - 3D Formulas

Download details: PPS 2007 SP2: Data Import Wizard

2008-12-11T08:00:00.001-08:00

PerformancePoint Server 2007 SP2: Data Import Wizard

Brief Description

The Data Import Wizard enables users to configure and move data from source systems into Microsoft Office PerformancePoint Server 2007.

Download details: PPS 2007 SP2: Data Import Wizard

Nugget: Stop a rule in PerformancePoint while running

2008-12-11T07:14:00.001-08:00

When you run rules, there is no option to cancel in the Business Modeller. There are a few solutions for killing rules while running.

1. Kill the SQL process (sp_who2, kill ##) of the rule. (SQL implementation)

2. Kill the Analysis Services process of the rule. http://www.codeplex.com/MSFTASProdSamples.

3. IISReset.

4. SQL / SQL Analysis Services Reset.

5. (Possibly) kill w3wp.exe process.

All are brute-force methods highlighting the fact that you are probably not meant to stop rules while they are running. :)

Welcome to the PerformancePoint Planning Business Rules Development Guide

The Most Popular Microsoft Certification Exams in October 2008 | Jack’s Blog

2008-12-09T13:29:00.001-08:00

The 640+ certified PerformancePoint Specialists will soon have company.

Microsoft Top 13 Microsoft Certification Exams List:

.html">70-285:MCSE Designing a Microsoft Exchange Server 2003 Organization

.html">70-442:Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 is in development

.html">MB7-517:NAV 5.0 Installation & Configuration

.html">70-556:TS: Ms Office PerformancePoint Server 2007, Applications

.html">70-089:Planning, Deploying, and Managing Microsoft Systems Management Server 2003

Financial Systems and Business Intelligence: PerformancePoint Server - Balancing Your Actuals

2008-12-09T13:27:00.001-08:00

Be sure to budget time to create variance reports for proper data validation when planning any data project.

PerformancePoint Server - Balancing Your Actuals

If like me, you are loading your actuals from your ERP system in PerformancePoint Server Planning, it helps to check your figures to make sure that they always balance. Now every good accountant will tell you that your trial balance must always balance to zero. This goes for each period and off course year to date. I have written some reports that self balance our system but in general here is the manual leg work behind it.

Financial Systems and Business Intelligence: PerformancePoint Server - Balancing Your Actuals

TechNet Virtual Labs: Office

2008-12-09T13:23:00.001-08:00

A couple helpful ones here…

TechNet Virtual Lab: PerformancePoint Server 2007- Designing a Budgeting Model

TechNet Virtual Lab: PerformancePoint Server 2007- Monthly Reporting

TechNet Virtual Labs: 2007 Microsoft Office System

Get hands-on with the key features and components of the 2007 Microsoft Office system and learn the benefits of deploying it within your organization. Walk through installation, configuration and the new components around enterprise features administration, collaboration, business intelligence, and experience common deployment and integration scenarios that can help you make better choices when planning your roll-out.

TechNet Virtual Labs: Office

PerformancePoint SP2 is out!

2008-12-09T13:13:00.001-08:00

Be sure to apply the “Pre-Service Pack 2 Update”

PPS 2007 SP2: Application Database Pre-Service Pack 2 Update

The Application Database Pre-Service Pack 2 Update resolves an issue that prevents PerformancePoint Server 2007 SP2 from updating the Application database.

Microsoft Download Center Search Results

Tips from PerformancePoint Planning Series I Course

2008-12-04T19:03:00.001-08:00

I'm teaching a PerformancePoint Planning course this week with a colleague from my company. Here are some tips that may help implementations, some added from previous postings, others discussed in forums, others discussed in the class:

Calendar

Note that the calendar cannot be changed for an application once it is created. (Time Dimension) Plan carefully!

Model Type

As with Calendar, Model Type is not something you can change, though you could add or remove rules from other model types to make them similar...

Schedule a Job

Is really just "create a job" which could be scheduled or run manually.

Associations

Are really just hidden model rules... sometimes it might be better to roll your own rather than using the associations section.

Input Forms

http://performancepointing.blogspot.com/2008/09/why-aren-my-input-form-cells-turning.html

Why aren't my input form cells turning yellow?

A colleague of mine offers this troubleshooting checklist when you just can't get the PerformancePoint Planning Excel Add-in to recognize input boxes in an assignment.

PerformancePoint Planning - Input Forms Checklist

The current date is within the Assignment start/end date/time
The cells you are looking at are within the start and end periods for the assignment (that you selected from the time dimension)
The scenario you are trying to input to is the scenario you selected for the cycle that the assignment is using
You have a value selected for every dimension in the model (both filters, rows and columns) and that the value selected is at the leaf level
The user you are logged on as has access to the model set to “On” in the summary view of the user’s role.
That read/write access is enabled for all of the dimensions selected in the filters/rows/columns you selected above.
The status of the assignment is listed as “started” or “partial”, not “Not Started” or “Submitted”, or something else.
That the assignment is opened using the assignment list in the action pane or from the assignment search window.
That the report property “Allow data entry” is set to true
That you haven’t mistakenly changed the format of a data entry to a white background (this has happened). Check this using the “Home” ribbon and going to cell styles. Right click on the “PerformancePoint data entry cell” to modify if necessary. This can be testing by trying to write to the cell –if you don’t get a “read-only” error you’re fine.

And a few new ones.

11. The cycle you have created uses the same model as the assignment's forms.

12. The cycle you have created starts before the current date and ends 1 day after the current date (depending on your cycle configuration.)

13. The roles that the user you are logged on as do not have read/write access turned off for the dimension members selected to input, as any role with this will override a permitting role. (similar to 6)

14. That you have refreshed the offline assignment under Work Offline - Manage offline Assignments.

Dynamic Dates in Reports

Use the Current Period Reference in the time dimension of reports to dynamically change report date ranges. Static examples from the course are not as maintainable, especially for monthly cycles.

Cycles

Only one role/user can be assigned to a cycle template. Multiple users/roles can be assigned to an instance.

Might want to name cycles like YYYY-MODEL-SCENARIO-DESCRIPTION

Security And Roles

For Business Unit security - Create a master role with read/write access to all business units. Add all users to this role. Treat it as a 'mailing/distribution list' used specifically for creating the cycle instance and assigning it to the role.

Create separate roles for each business unit contributor making an assignment. Deny access to units that you do not want them to have access to.

Create another role "Lockout" or "Disabled Users" with no access to anything. Add users to this role to disable access to models. Deny overrides any other access they may have.

Do not specify User Customized Permissions. (Performance Hit). Use increased number of roles instead.

Excel Add-In

Cut-pasting the matrix from Report-Properties may throw an error if dimension filters exist. Edit the dimension filters under report properties to include name, label syntax same as original matrix. Ensure that all properties match original matrix.

Rules

Use Active column to disable rules when performance problems exist. Test model performance without any rules. SQL Rules should be scoped very tightly, MDX rules should be scoped very broadly.

Use variables to store start and end dates for rules instead of current period reference. Additional flexibility.

PPSCMD.EXE

Creating a job of jobs. (Courtesy Jeffrey Wang, Technet forums)

Well, its not impossible, its just not documented - but i guess that also means use it with care =)

If you use .Net Reflector, you can browse into the PPSCmd itself and see that it actually has some hidden commands inside. One such magical command is the ImportXml that takes an xml file descibing the jobs you want to run.

Create a XML file with the following content in your context:

<?xml version="1.0" encoding="utf-8"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/performancepoint/2007/03/MigrationDefinition">
<Applications>
    <Application Id="GUID" Label="AppLabel" Name="AppName">
      <ImportCommands>
        <Command xsi:type="LaunchJob" Name="JobLabel">
        </Command>
      </ImportCommands>
    </Application>
</Applications>
</Root>

Next, call it:

PPSCmd importXml /server http://server:46767 something.xml

Make sure you do not have have rules that are NativeMdx or NativeSQL implementation as this will disable the EnableNativeMdxSQLRule on the application.

Once you have it running, you can schedule this to run in SQL Agent - full scheduling to your heart's desire.

PerformancePoint Planning - Analysis Services

Use SQL Profiler to connect to Analysis Services to identify query problems and issues with caching, or just to see what it's doing in the background while it's not "rendering the matrix". You may be able to tweak aggregations using Aggregation Manager, though you will probably need to do this on every deploy. Analysis Services should be on at least one separate server in the architecture for memory and performance reasons.

PerformancePoint Exam 70-556 Outline

http://www.microsoft.com/learning/en/us/exams/70-556.mspx

The Microsoft ProClarity Team Blog : ProClarity 6.3 SP2 (build 2217) Documenation

2008-12-04T19:00:00.001-08:00

Proclarity 6.3 SP2 is out, and still doesn't play very well with Sharepoint 2007 on Windows 2008.. but hey, you should be scaling out right?

Attached is the Requirements.doc for the ProClarity 6.3 SP2 (build 2217) release. This document is being posted here because it will not be available via KB article for another couple of weeks. The SP2 bits are available at http://download.microsoft.com. Still to come later are the "Installing ProClarity 6.3 SP2 on Microsoft Windows Server 2008" and "ProClarity 6.3 SP2 readme.docx" files. We'll post these as soon as possible.

The Microsoft ProClarity Team Blog : ProClarity 6.3 SP2 (build 2217) Documenation

Filtering Unneeded Dimension Members in PerformancePoint Filters - SQL Server Central

2008-12-04T18:59:00.001-08:00

Sometimes we need to utilise dynamic dimension security in an Analysis Services solution and we also need to display dynamically the allowed members in a PerformancePoint filter.

Filtering Unneeded Dimension Members in PerformancePoint Filters - SQL Server Central

Aurelien Koppel: Blog technique sur PerformancePoint Server (PPS)

2008-12-04T18:34:00.001-08:00

Advantages of Proclarity vs. Excel... in French.

Proclarity 6.3 et Excel 2007 permettent à leurs utilisateurs de réaliser des rapports ad-hocs fondés sur des cubes Analysis Services (SSAS).

Aurelien Koppel: Blog technique sur PerformancePoint Server (PPS)

PerformancePoint Planning: Deleting a Custom Member Property - A Solution - Sacha Tomey's blog

2008-12-04T18:23:00.001-08:00

Some sample code to get started building your own PerformancePoint Planning add-in in C#... or at least one feature of it.

Below is some really rough and ready C# code that actually does delete a dimension member property. I will improve the code and probably add it in to my PPSCMD GUI interface as a 'feature pack' bonus at some stage, however, if you are in desperate need for the code to delete a member property, and you can't wait for PPSCMD GUI v0.2 or PerformancePoint Version 2 (I'm not sure which will come first !) the code is below (Use at your own risk !!)

PerformancePoint Planning: Deleting a Custom Member Property - A Solution - Sacha Tomey's blog

So.... you want to use the PerformancePoint web service so you can extend PerformancePoint, and integrate it into bespoke applications and SharePoint.

The PerformancePoint Planning Web Service

The Microsoft PerformancePoint Team Blog : Creating Excel and PowerPoint Documents Using PerformancePoint

2008-12-04T18:18:00.001-08:00

Still very relevant.

Creating Excel and PowerPoint Documents Using PerformancePoint

Eric Friedrichsen (ericfr@microsoft.com)

Microsoft Corporation

August 2007

Applies to:
Microsoft Office PerformancePoint Server 2007
Microsoft Office Excel 2007
Microsoft Office PowerPoint 2007

The Microsoft PerformancePoint Team Blog : Creating Excel and PowerPoint Documents Using PerformancePoint

Microsoft Dynamics CEE Blog : Microsoft Office PerformancePoint Server 2007 and Microsoft Dynamics AX 2009 Monitoring and Analytics Sample

2008-11-28T13:44:00.001-08:00

Microsoft Office PerformancePoint Server is a performance management application that helps users monitor the performance of their organizations, analyze the results, and make effective business decisions.

The Microsoft Dynamics AX 2009 sample contains a sample Microsoft Dynamics AX data cube and two sample dashboards that are based on the information. One is an executive overview dashboard and the other a drill down into the General Ledger information. You can reuse these dashboards on your own Microsoft Dynamics AX cubes if you desire by following the instructions included with the sample.

Microsoft Dynamics CEE Blog : Microsoft Office PerformancePoint Server 2007 and Microsoft Dynamics AX 2009 Monitoring and Analytics Sample

The Other James Brown : Single View Platform

2008-11-28T13:03:00.001-08:00

It’s not the Unified Dimension Model that’s the focus at Microsoft any more… it’s the Single View Platform. It’s not just Analysis Services on top of SQL Server to centralize your data, it’s Virtual Earth and PerformancePoint, tied into Sharepoint, to offer a comprehensive “Single View” of the world.

The Single View Platform is not a new product in itself, but rather a group of existing products brought together under a banner for a specific problem domain (like CSP). For SVP it brings in Virtual Earth, SQL, SharePoint, PerformancePoint and a whole host of other products. The advantages of this approach were rather nicely summed up on another blog:

For those of us who have chosen the Microsoft platform, the good news is that we have many products from which to choose. Microsoft has an array of server and client products, as well as online services, that cover everything from collaboration to commerce, business intelligence to geographical information systems, personal productivity to accounting.
Sometimes the sheer number of choices can be daunting, so Microsoft is initiating new offerings that help us weave the various products into cohesive solutions. The goal is to bridge the gaps between product groups and provide additional documentation and code to integrate multiple Microsoft products.

Very nicely put! I really hope that we see more of these frameworks from Microsoft. They allow us tackle a problem by grouping a set of our products together, and then producing documentation, guidance, demos, samples and so forth directed at solving the specific problem space.

The Other James Brown : Single View Platform

PerformancePointy : PerformancePoint Monitoring + Silverlight: KPIs and Scorecards

2008-11-28T12:40:00.001-08:00

PerformancePoint Monitoring + Silverlight: KPIs and Scorecards

PerformancePoint Monitoring doesn't have as robust a prescription as Silverlight Blueprint for SharePoint, but yes, PerformancePoint Monitoring & Silverlight can be used together.

PerformancePoint dashboards are SharePoint web part pages, so web parts or master pages using Silverlight for menus, visualization, charting, or any other thing, are right at home in (or surrounding) a PerformancePoint dashboard.

Another integration point is the web service PmService.asmx. You can learn more about this service through PerformancePoint SDK documentation of its client proxy. In this article we’ll call the GenerateView method of this service to retrieve and work with a scorecard/KPI data set, and visualize that data in Silverlight 2.

PerformancePointy : PerformancePoint Monitoring + Silverlight: KPIs and Scorecards

PerformancePoint Server Information by Ajay Singh

2008-11-27T08:24:00.001-08:00

Details on Consolidation from the BI VPC 6.0.

How Consolidation works in Performance Point Server:

Financial model with shares consolidation: This uses financial model with shares since it requires ownership information which derives from the shares calculation. There are five rules templates for statutory consolidation in FI. They are Inter-company rule for Profit/Loss, Inter-company rule for investment, Inter-company rule for Equity and Inter-company rule for Balance Sheet. One can map a leaf account to the model property that used by the rule. The leaf account will then be used for calculating minority interest, investment and elimination related transactions. Users can also write their own rules to accommodate more statutory requirements. The financial model with shares consolidation only supports one level entity hierarchy, and shares calculation will generate all the ownership information for each entity relative to the parent entity which in term generates a flat hierarchy.

If company A owns 90% of company B, there will be two leaf entities (company B and company A) and one consolidated entity Consol. After shares calculation company A has holding consolidation method and company B has Full consolidation method. Once the method is determined for each entity within the hierarchy, we need to load fact table data for each leaf entity. Consolidation process involves the following three steps:

Shares Calculation: To calculate the ownership and control for each sub entity.

Reconciliation (optional): To record any difference occurred between inter companies.

Consolidation: To record elimination for inter-company transactions based on the type of the accounts, consolidation methods and flow type (for BS accounts only).

Currency Conversion (optional): Consolidation can also do currency conversion but it’s optional.

Financial model without shares consolidation: This uses financial model without shares since we assume 100% ownership between parent and child entities. The model property Consolidation Balance Account is used to record the elimination for each account. Non statutory consolidation supports staged hierarchy so that parent entities can have multiple levels. The 100% elimination will take place for all the intercompany transactions.

PerformancePoint Server Information by Ajay Singh

PerformancePoint Server Information by Ajay Singh: InterCompany Reconciliation

2008-11-27T08:09:00.001-08:00

I don’t usually ask myself questions, but if I did it would probably be “Self, what is an Offset Account” if I had to work with the Financials with Shares model in PerformancePoint Planning.

You may be asking yourself, “Self, what is an Offset Account? What is a Balancing Account for that matter?” Well, when an IC reconciliation job runs it looks at the difference between the values recorded by the buyer and the seller. In this case, the seller recorded the transaction at $50.00 higher than the buyer. IC reconciliation always assumes the buyer’s value is correct and so we must reduce the seller’s value by 50 bucks. The offset account (that we set in the model properties) is considered a “seller’s account” so adding -$50.00 will bring the whole shebang into balance. But, our ancient Greek accounting rules tell us that we need to record a double entry for this transaction. Enter: the balancing account. The balancing account will always get a double-entry for the same amount and opposite sign as the offset account.

PerformancePoint Server Information by Ajay Singh: InterCompany Reconciliation

What is PerformancePoint?

2008-11-22T13:12:00.001-08:00

Do you know what Microsoft PerformancePoint is?

I think of Microsoft PerformancePoint as a combination "all-in-one", "do-it-yourself with a bit of help" planning, analyzing, reporting, scorecard and dashboard creating suite from Microsoft with Excel, Sharepoint, and Web functionality. It helps you to capture and analyze your data. It integrates multiple Microsoft products into a single common theme of _Business Intelligence_.

It's kind of like a swiss-army knife for BI, but with power tools instead of a fork or toenail clippers.

There are many components of PerformancePoint drawn together from creations of Microsoft's development teams, acquisitions, and licenses of 3rd-party tools. Proclarity, Business Scorecard Manager, Biz #, FRx, to name a few. Planning, Monitoring, and Analyzing.

There's a lot to cover if you want to know all there is to know about PerformancePoint.

PerformancePoint is not:

A server performance optimization tool (though it does allow for data analysis to optimize performance)
A load testing tool.
A tool to speed up your computer (in fact, it requires some serious resources and server-class machines if you want the entire suite installed along with SQL Server, Sharepoint, and lots of enterprise data)
An add on for Microsoft Surface.
It is not a Master Data Management Tool, though you could probably turn it into one. There was some speculation that the Stratature acquisition would provide PerformancePoint with an MDM tool. Instead, this falls into the Sharepoint camp.
It does not support SQL Server 2008 (until you apply the service pack that is coming soon!), though parts will still function. The Planning component of PerformancePoint uses MDX and Analysis Services extensively, which apparently doesn't translate very well in the SQL 2008 world of MDX.

What is PerformancePoint?

It falls under the Office Server suite of products, though it could just as easily have fallen under SQL Server's umbrella due to its close ties with the database and tight integration with Analysis Services.
It is the client tool that Microsoft was missing when browsing OLAP cubes. (Pivot tables in Excel 2000 were not much fun, though they are much better in Excel 2007.)
It is the data entry tool for Excel that Microsoft was missing for planning submission and workflow. (Spreadmarts are not much fun.)

I enjoy word analysis more than number-crunching, as words can be beautiful and numbers are just... well... numbers. Wordle is one of my favourite "tag cloud" tools for creating these word visualizations.

PerformancePoint, as defined by the search terms accessing this blog.


	There seems to be a common theme here. Let's filter out PerformancePoint, Sharepoint, Performance Point, and Server to see what the true story is.

As this is a blog, you're going to see terms like 'problem', 'failed', and msmdpump.dll' picked up from the search engines. I had a few "head scratching" moments myself when trying to build my first model using PerformancePoint Planning, trying to create my first Dashboard using PerformancePoint Monitoring, and trying to get Proclarity Web Professional installed.

Not to mention data integration. "createlabeltableformeasuregroup" anyone?

The purpose of this blog and my postings here, other than to capture every tidbit of internet knowledge around PerformancePoint, is to solve problems, turn failures into successes, and assist with configuring XML/A. :)

So that's how people got here. But where is Microsoft going with this?

A solution to "An unexpected error has occurred" in WSS v3

2008-11-18T10:33:00.001-08:00

Debugging SharePoint can be problematic at times, it does like to hide debugging information from you. The bain of my life recently has been “An unexpected error has occurred” with nothing written to log files, trace or the event log.

Normally I can debug the problem with a little commenting & narrowing down of the problem, but today I have managed to get rid of that error screen completely.

The solution is to change a single entry in web.config, by modifying the line…

<SafeMode MaxControls=“200“ CallStack=“false“…

to…

<SafeMode MaxControls=“200“ CallStack=“true“…

You will also need to set custom errors to 'Off' .

<customErrors mode=“Off“/>

You will no longer see the “An unexpected error has occurred” error page and instead you get a lovely ’standard ASP.Net error page’ with the stack trace and everything…development has got that little bit easier!!

A solution to "An unexpected error has occurred" in WSS v3

Justin T. Ho - Event Log Shows {3D42CCB1-4665-4620-92A3-478F47389230} and {61738644-F196-11D0-9953-00C04FD919C1} Permission Errors with Sharepoint 2007 Search Engine Installed

2008-11-18T09:02:00.001-08:00

If you notice this filling up the Windows Event Log, you may need to modify permissions within Component Services.

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
User: NT AUTHORITY\NETWORK SERVICE
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{3D42CCB1-4665-4620-92A3-478F47389230}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

Justin T. Ho - Event Log Shows {3D42CCB1-4665-4620-92A3-478F47389230} and {61738644-F196-11D0-9953-00C04FD919C1} Permission Errors with Sharepoint 2007 Search Engine Installed

BeI - Microsoft Business Intelligence – Reporting Services 2008 & PerformancePoint

2008-11-05T12:42:00.001-08:00

Here is another quick tip, Reporting Services 2008 would be fully supported using SP2 (see my previous post).
To those of you who need to use SSRS2008 today, using PPS SP1, here is a workaround:

Scenario: Integrating SSRS2008
1. Set Server Mode to "Share Point Integrated"
2. Type the SSRS2008 Report Server URL
3. Set the Report name using Full Path description

BeI - Microsoft Business Intelligence

All About Annotations « Alan Whitehouse’s Ramblings

2008-11-05T12:26:00.001-08:00

It would be great to have a way to enter text into a cell as something that could be captured and referenced from either the cube or a relational table. This concept would fit well with the standard of variance explanations placed beside numbers.

After all, a number is only as useful as the story surrounding it.

Today I have decided to talk about the concept of Annotations. Annotations are a nice feature of PerformancePoint, but there are some limitations to keep in mind.

All About Annotations « Alan Whitehouse’s Ramblings

BeI - Microsoft Business Intelligence

2008-11-05T12:13:00.001-08:00

Lots of useful links here. Many I already link to on the right hand links.

Since there are a lot of you out there looking for away in, I've decided to gather all my known links and recommendations to help you take your first steps. I've decided to call it the "Junkies List":

Taking the first steps: Adrian's Impressive Intro Tutorials (1-7):
http://blogs.technet.com/datapuzzle/archive/2008/03/21/help-how-to-guides-manuals-forums-for-microsoft-office-performancepoint-server-2007.aspx

Level 100 – 200 : PPS – Planning On-Line raining
http://www.microsoft.com/business/performancepoint/resources/training.aspx

Basics Concepts:
http://blogs.adatis.co.uk/blogs/sachatomey/archive/2008/04/28/new-microsoft-insider-pps-planning-blog.aspx

XL-Add-ON
http://blogs.msdn.com/petereb/default.aspx

Centeral Blog:
http://adriandownes.blogspot.com/

Planning Server Forum:
http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1871&SiteID=17

Paul Steynberg
http://paulsteynberg.blogspot.com

Nick Barclay
http://nickbarclay.blogspot.com/

Jason Morales
http://blogs.msdn.com/jasonmorales/default.aspx

Peter Eb
http://blogs.msdn.com/petereb/about.aspx

Sacha Tomey
http://blogs.adatis.co.uk/blogs/sachatomey/archive/2008/04/28/new-microsoft-insider-pps-planning-blog.aspx
During the coming weeks, I'll publish posts about in-depth Planning Development Issues. Have Fun and Be I,

BeI - Microsoft Business Intelligence

PerformancePoint, Azure and SQL Reporting Downloads from Microsoft | SharePoint BUZZ - Your SharePoint Community Resource

2008-11-05T12:04:00.001-08:00

Downloads available from Microsoft:

PerformancePoint Server 2007 Contoso Demo Data - contains data for the Contoso application of PerformancePoint Planning and workspace of Monitoring and Analytics. This demo application was designed to illustrate real-world business scenarios on performance management areas like statutory consolidation, Planning/Budgeting/Forecasting, KPI, scorecard, dashboard etc.

Windows Azure Tools for Microsoft Visual Studio October 2008 CTP - for Microsoft Visual Studio extend Visual Studio to enable the creation, building, debugging, running and packaging of scalable services on Windows Azure

Windows® Azure Software Development Kit (October 2008 CTP) - provides developers with the APIs, tools, documentation, and samples needed to develop Internet-scale applications that run on Windows Azure

Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies - a Web download that provides features for running a report server within a larger deployment of Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007.

PerformancePoint, Azure and SQL Reporting Downloads from Microsoft | SharePoint BUZZ - Your SharePoint Community Resource

Performance Point Server SP2 pre-release nuggets - BeI - Microsoft Business Intelligence

2008-11-05T12:03:00.001-08:00

Best news I have heard in awhile – the long-awaited SP2 for Performance Point Server 2007 is coming out… before the New Year?

Performance point server SP2 is planned to be release on December 2008.
We are currently waiting for the beta version, due in a couple of days.

The dev team has relased some nuggets regarding the upcoming release:
1. The support for SQL Server 2008 has been established:
    a. SSRS report view now supports the SSRS2008 connection.
    b. Setup up over SQL Server 2008
2. Support for virtualization environments:
    As previously mentioned by Norm's blog post:
http://blogs.msdn.com/normbi/archive/2008/10/14/performancepoint-server-sp2-hyper-v-is-supported.aspx
   Performance Point Server would support virtualization (using Hyper-V),
   you can read further about it at:
http://support.microsoft.com/?kbid=957006
    Since Planning Server requires wide topology, this issue could change the future architecture of planning implementation.

Performance Point Server SP2 pre-release nuggets - BeI - Microsoft Business Intelligence

B.I. the way...

2008-11-05T12:01:00.001-08:00

Some more documentation coming shortly.

The Planning Business Modeler topics are now "out of the box". These topics are organized by category rather than table of contents. The content is very good. In coming weeks, we will cross-reference between TechNet and Office Online so that customers have a better experience (less fragmented) reading our documentation.

Overview

Getting started

Accessibility

User interface

Concepts

How do I

Assignments

Jobs

Associations

Assumptions

Business rules

Business rules tasks | Business rules overview | Business rules templates | More...

Cycles

Dimensions

Predefined dimensions

Models and model sites

Forms and reports

Planning an application

Process scheduling

Security and Roles

B.I. the way...

PerformancePoint Install - Failed Windows Server 2003 SP1 - Dan Lewis

2008-11-05T11:52:00.001-08:00

Work Around:

You can run the following commands to install PerformancePoint making it ignore the prerequisites check, and ultimately enabling the 'Next' button.

To skip check for Planning Server:
MSIEXEC /i PPLSrv.msi SKIPREQCHECK=1
To skip check for Monitoring Server:
MSIEXEC /i PSCSrv.msi SKIPREQCHECK=1

PerformancePoint Install - Failed Windows Server 2003 SP1 - Dan Lewis

MSDN Blog Postings » Blog Archive » PerformancePoint Server SP2: Hyper-V is supported

2008-10-22T12:59:00.001-07:00

It is good to know that PerformancePoint Server (PPS) Service Pack 2 will support Hyper-V. The news is located on a Microsoft server software and supported virtualization environment list.

This article discusses the support policy for running Microsoft server software in the following supported virtualization environments:

Windows Server 2008 with Hyper-V

Microsoft Hyper-V Server 2008

Server Virtualization Validation Program (SVVP)

MSDN Blog Postings » Blog Archive » PerformancePoint Server SP2: Hyper-V is supported

Availability of the Changelist field of Submissions table. - TechNet Forums

2008-10-20T08:14:00.001-07:00

Tip from the forums:

From Planning Business Modeler | Process Management | Cycle Instance | Form | Select the Assignment on the Tasks you can Export the Change list as CSV files. The exported file can be accessed outside the application.

Availability of the Changelist field of Submissions table. - TechNet Forums

The NAV/SQL Performance Field Guide - 3rd Edition - Dynamics NAV Performance on SQL - My Two Cents

2008-10-07T08:58:00.001-07:00

While struggling with NAV/SQL performance issues for many years I scribbled down countless pages about various issues, learned by own experience or from colleagues, taken from numerous knowledgebase articles, whitepapers, etc. or heard in several webcasts.

In 2006 I started to put all this into a structured document, one I could use more straight in my optimization workshops - so "The NAV/SQL Performance Field Guide" was born. It's not a replacement of any official NAV/SQL documentation and requires some pre-knowledge about certain issues, but this "braindump" should give feasible advice and practical hints for the daily work with NAV and SQL.

The NAV/SQL Performance Field Guide - 3rd Edition - Dynamics NAV Performance on SQL - My Two Cents

Preprocessor commands for native code rules

2008-10-06T10:27:00.001-07:00

System constants (preprocessor commands) within PerformancePoint rules are available here.

Preprocessor commands for native code rules

Kevin White's PerformancePoint Blog : A brief overview of PerformancePoint Planning business rules

2008-10-02T07:32:00.001-07:00

Kevin talks about the different types of business rules in PerformancePoint.

I have described the behavior of PPS rule sets below. Any rules that are added in these rule sets follows the described behavior.

Kevin White's PerformancePoint Blog : A brief overview of PerformancePoint Planning business rules

Developing for Dynamics GP : Microsoft Dynamics GP Blogs

2008-10-01T11:02:00.001-07:00

This page contains links to blogs relating to Microsoft Dynamics GP

Developing for Dynamics GP : Microsoft Dynamics GP Blogs

The Microsoft PerformancePoint Team Blog : Working with the Monitoring CSS Files to Adjust Settings in Deployed Dashboards

2008-10-01T05:01:00.001-07:00

PerformancePoint Monitoring server has a few style sheets that can be adjusted in order to modify settings in your deployed dashboards.

The Microsoft PerformancePoint Team Blog : Working with the Monitoring CSS Files to Adjust Settings in Deployed Dashboards

Snatched Moments - Create a NativeSQL Business Rule

2008-10-01T04:44:00.001-07:00

It is usually recommended that all rules be written in PerformancePoint Expression Language (PEL) in to ensure upgrade compatibility.

However, some rules (either throwaway or complex) need to be driven by either Native SQL or MDX code.

Here is a blog posting outlining how to write a Native SQL rule.

In some difficult situations you might want to use NativeSQL to implement a business rule. It gives you the full power of SQL Server.

First you need to create the rule in the Business Modeler to host the NativeSQL business rule. You can use parametres and even use members of dimensions as parameters. When it is time to execute the business rule the user gets a nice interface where the user is able to select the parameter in the dimension.

In the SQL it is the memberId of the dimension that is passed into the function and they are of the type int. A start of an implementation can be seen here:

create proc Calc.[sp$procedure] @entity int, @scenario int, @startTimePeriod int, @endTimePeriod int as

It looks almost as a normal stored procedure in SQL. The only difference is that the name is a preprocessor commands and the Performancepoint translate this to a real stored procedure name, when it is created by the Performancepoint in the SQL Server. The rest of the parametres is just like a normal stored procedure.

Snatched Moments - Create a NativeSQL Business Rule

Why aren't my input form cells turning yellow?

2008-09-29T08:19:00.001-07:00

A colleague of mine offers this troubleshooting checklist when you just can't get the PerformancePoint Planning Excel Add-in to recognize input boxes in an assignment.

PerformancePoint Planning - Input Forms Checklist

The current date is within the assignment start/end date/time
The cells you are looking at are within the start and end periods for the assignment (that you selected from the time dimension)
The scenario you are trying to input to is the scenario you selected for the cycle that the assignment is using
You have a value selected for every dimension in the model (both filters, rows and columns) and that the value selected is at the leaf level
The user you are logged on as has access to the model set to “On” in the summary view of the user’s role.
That read/write access is enabled for all of the dimensions selected in the filters/rows/columns you selected above.
The status of the assignment is listed as “started” or “partial”, not “Not Started” or “Submitted”, or something else.
That the assignment is opened using the assignment list in the action pane or from the assignment search window.
That the report property “Allow data entry” is set to true
That you haven’t mistakenly changed the format of a data entry to a white background (this has happened). Check this using the “Home” ribbon and going to cell styles. Right click on the “PerformancePoint data entry cell” to modify if necessary. This can be testing by trying to write to the cell –if you don’t get a “read-only” error you’re fine.

MSDN Blog Postings » Blog Archive » A brief overview of PerformancePoint Planning business rules

2008-09-25T06:25:00.001-07:00

Over the course of the past year I’ve had a number of conversations on what type of business rule to choose for a PerformancePoint model. The answer is always - ‘it depends’. Choosing the right type of business rule depends on the the context of your business requirements. For example, technical accuracy of a rule (i.e. Sales Volume * Product Price) can be done through any rule type - but choosing the right rule type requires you to consider how the resulting data is processed and stored in the system and how that impacts or aligns with your business requirements.

I have described the behavior of PPS rule sets below. Any rules that are added in these rule sets follows the described behavior.

MSDN Blog Postings » Blog Archive » A brief overview of PerformancePoint Planning business rules

Peter Eb. : HOWTO: Dynamic rows or columns with a dimension property filter

2008-09-24T10:32:00.001-07:00

Currency may not change that much, but how about employee or department?

Peter has a way to hack PerformancePoint Planning into using dynamic members.

Then click Validate RDL to make sure there are no syntax errors. Then click ok and the report will be refreshed. Now using the filter should give the same values as the static selection, but when a new currency member is added with a new value for the property MemberName it will automatically appear in this list. Voila! Not so simple, but not so hard.

Peter Eb. : HOWTO: Dynamic rows or columns with a dimension property filter

What would be nice is to see an example of a web-based or Excel-based data entry solution for adding members to the model.

Something like this:

http://www.oraxcel.com/projects/sqlxl/

or this

http://msdn.microsoft.com/en-us/library/aa203746(office.11).aspx

or this

http://www.perlielab.com/CallistoPages/datamanager.html

or how about creating a SQL job on the server that polls published spreadsheets or Sharepoint lists for data and loads them into the staging & app databases and processes the model?

This would allow for things like headcount planning and adding new products or customers to a model without modeler permissions.

Sounds like a lot of work for something that should really be in the add-in as a feature (member management).

Perhaps Microsoft MDM will incorporate this feature into PerformancePoint and/or Excel?

At least that's what the roadmap tells us...

How to speed up the All up BI VPC 6

2008-09-24T07:18:00.001-07:00

Take out the cubes. Any cubes that you do not want to demo or try out,

1. From a command line (or with services console) NET STOP MSSQLSERVEROLAPSERVICE

2. Create a new directory called data2 and copy files/directories below into the directory from data directory.

3. Rename the data directory to data_backup.

4. Rename the data2 directory to data.

5. Start up Analysis Services.

Because it is not precaching anything, I noticed a huge improvement in loading the service and connecting to AS. No more all-consuming memory bloat.

This is good if you would like to use the All Up BI VPC with your own demo data and have no interest in the samples provided.

If you need any of the sample database, copy the directories and xml associated to them back in with above steps.

If you're building PerformancePoint Planning models for training purposes it should speed up the deployment immensely.

Learn, Build, Grow...the future of BI: BI VPC 6 now available

André Vala : PPS 2007 Tip #1: Creating Models with Multiple Measures

2008-09-22T07:08:00.001-07:00

In the current release (CTP4), the only way to create a model with more than one measure is to export the application metada to a XML file, manually edit it and import the updated metada. That can be achieved following the steps listed below.

André Vala : PPS 2007 Tip #1: Creating Models with Multiple Measures

Re: Output from ppscmd - TechNet Forums

2008-09-19T10:30:00.001-07:00

Some useful info for tracking the results of PPSCMD.exe into a log file.

Your post did point me in the right direction. However, there are two parts to a workable solution:

1) There is the *undocumented feature* for ppscmd which is using custom handle 3 for output (instead of stdout which is handle 1) so you have to use 3>&1 in you command argument. I was lucky, I guessed...

2) To implement a redirect in SSIS Execute Process task you have to use a workaround using cmd /C as your executable.

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

Put together, it willl look something like cmd /c ppscmd [arguments] 3>&1 >> mylog.txt.

Re: Output from ppscmd - TechNet Forums

Jason Morales' Microsoft BI Update : The Business Intelligence Update - September 2008

2008-09-15T12:06:00.001-07:00

The Business Intelligence Update - September 2008

HOT TOPICS

Register NOW for the 2nd Annual Microsoft BI Conference - click here!

25% Discount Promotion - Microsoft Office SharePoint ECAL / PerformancePoint Server CAL

*** extended through September 30, 2008

Assessment Tool for Business Intelligence

- compare your infrastructure against industry best practices and the latest IT research

Online Demo: Geospatial Business Intelligence

- by integrating Virtual Earth and PerformancePoint

- Blog: More on Virtual Earth & PPS

Business intelligence basics: case studies and job advice

Five keys to hiring (good) business intelligence professionals

Leveraging business and marketing skills for a business intelligence (BI) career

UPCOMING EVENTS

How does it all stack up?

6 part Seminar Series, sponsored by Inetium & Microsoft

Sept 18

Omaha, NE

Click here to register

Dashboard in a Day - sponsored by Quilogy

Sept 16 - Omaha, NE

Sept 18 - Chicago, IL

Click here to learn more and register

The Next Generation of Business Intelligence

Executive Roundtable Breakfast Series, sponsored by Neudesic & Microsoft

Sept 18 - Denver, CO

Sept 30 - Las Vegas, NV

Microsoft BI Conference

Oct 6-8 - Seattle, WA

www.microsoftbiconference.com

Managing Performance with PerformancePoint

Planning, Financial Reporting, Monitoring and Analytics for Dynamics GP 10

Oct 8 - Mountain View, CA

Click here to register

PASS Community Summit 2008

Nov 18-21 - Seattle, WA

Click here to learn more

Find Events & Webcasts

UPCOMING WEBCASTS

* all times listed below are for Pacific time zone

Microsoft Business Intelligence for Retail - sponsored by Solver

Sept 11, 11am

Click here to register

Using Your SharePoint Portal to Communicate Business Intelligence presented by Quilogy

Sept 11, 1 pm

Click here to register

Achieving Business Intelligence Across the Enterprise presented by Thorogood

Sept 18, 9am - Realize Fast & Effective Planning, Budgeting & Forecasting

Click here to register

NEWS LINKS & PRESS RELEASES

SQL Server 2008: First Impressions

BIScorecard Rates Eight Leading Products

An Effective Business Intelligence Strategy is the Key to Business Success

Next-generation BI at Hand, Forrester Reports

Gartner lists Microsoft India as leading vendor of BI

Information Agility

Who doesn't want to be agile?

The Business Intelligence Model of Conflicting Interests

Is Excel a Complete BI Solution?

Pervasive business intelligence: Are organizations really ready?

Seven Steps to Successful BI Competency Centers

Business Intelligence: Distilling Raw Data into Useful Information

The Business Intelligence Education Problem

Full Circle: Decision Intelligence (DSS 2.0)

Contact Me if you'd like to receive monthly emails of Jason Morales' Microsoft BI Update

Jason Morales' Microsoft BI Update : The Business Intelligence Update - September 2008

How To Get A Token For A User

2008-09-12T14:46:00.001-07:00

If you're going to be coding in Excel Services, you may need to work with impersonation for the web service credentials.

This article could help.

If you're trying to do the same thing with a local account, or a domain account on a Windows 2000 or Windows XP box, you'll need the user's password to get a token for it. Where are you going to get the user's password, though? If you need to do this sort of thing, prompt the user for a password (HowToPromptForAPassword). If the user won't be present, then you'll need to store the password someplace where the machine can read it. This is really bad news because, even if you do this as carefully as possible (HowToStoreSecretsOnAMachine), root compromise of the machine eventually leads to compromise of these secrets, and you can't prevent this. This should be a major consideration in your threat model (WhatIsThreatModeling)!

How To Get A Token For A User

Cum Grano Salis : AJAX Library for Excel Services

2008-09-12T13:05:00.001-07:00

Access Excel Services from any web page using Ajax and this wrapper.

I have had this on my table for a while already and have been meaning to make it available for everyone to use/modify. The idea behind this example is to wrap the Excel Web Services API so that it is available for call inside Internet Explorer. The idea is that people will be able to leverage models inside workbooks just like C# code.

The attached file (ExcelServicesAjax.js) needs to be placed inside the _layouts directory of your SharePoint server. In yout HTML files, you then need to reference the javascript file. That will give you access to the functionality.

Cum Grano Salis : AJAX Library for Excel Services

Differences between using a workbook in Excel and Excel Services - SharePoint Server - Microsoft Office Online

2008-09-11T13:20:00.001-07:00

As part of your dashboard or planning project, or separately, you may be investigating Excel Services. It does not have all of the features of Excel in the browser. Data entry isn’t supported. Charts are different. Macros aren’t supported, though UDFs are and you can call Excel Services from VBA. It’s usually hosted in Sharepoint, though Excel Services is just a web service that’s accessible via custom apps.

Supported and unsupported features when loading a workbook

Differences between using a workbook in Excel and Excel Services – SharePoint Server - Microsoft Office Online

It is, however, a good way of keeping one version of the truth around in a central, protected format and is easily understandable by the business. And there are some other possibilities like using the Excel Web Companion, and saving data using Excel Web Companion. The Save As feature may provide an interesting way of doing some adhoc forecasting & brainstorming online.

Spreadsheet hell comes to Sharepoint!

What is "one version of the truth"?

A common requirement when publishing a Microsoft Office Excel 2007 workbook that is connected to an external data source on Excel Services is to ensure that users always view a consistent set of values and formula results, and that the workbook uses secure and up-to-date connection information. This is sometimes called obtaining "one version of the truth".

For example, if you are reconciling budget data each quarter between different departments to provide a company-wide budget summary, you need to ensure that everyone is working with reliable and consistent data to ensure that the process goes smoothly with minimal disruption and disagreement over differing values between workbooks and the timing of formula calculations and results.

Roadmap for publishing an Excel workbook as "one version of the truth"

Using charts and PivotChart reports in Excel Services

Using named views to show/hide charts.

View a named item in Excel Services

Tying back to external systems

Using hyperlinks in Excel Services

Drilling to details in a cube/pivot table

Expand or collapse data details in Excel Services

Drilling to details with outline

Outline data in Excel Services

Custom Properties of the Web Part

Create a menu to show/hide named items.

Connect a List View Web Part to Excel Web Access

Sacha Tomey's blog

2008-09-09T13:07:00.001-07:00

Recently Sacha published links to some of the nuggets that were posted here from the training course I went on a few weeks ago. I got a lot of traffic from those links so thanks!

Some of the nuggets were straight out of my shorthand notes, which, looking at them now, may not make a lot of sense.

Will try to clarify some of the ones he pointed out to me. His comments in bold, mine in italics.

• Remove blank columns and remove blank rows in the Report Properties - Matrix properties

Apparently this improves performance. It is a setting in the Excel Add-in under Report Properties.

• Create a separate section with "Suggested changes" with read/write access or use Sharepoint Library. Create Groups for Detail & Summary reports.

This was a suggestion to add a spreadsheet with “Suggested changes” for users to add their feedback. Another suggestion was to use the Grouping features in Excel to create Summary/Detail sections with expand/collapse capabilities. Users like these simulated drilldown capabilities.

• Select False to validation will ignore unknown member data.

This was in reference to a parameter used with the Label stored procedures for staging and validatating dimension members. The parameters are mutually exclusive, that is you need to run the procedure twice with the different parameters to validate annotations & validate members.

• Always set cycle dates to at least 1 day after the current date, then manually instantiate the cycle afterwards. This solves issue with waiting for cycle to appear automatically.

You can manually instantiate a cycle even if you leave it to start on the current date. Not sure of the benefit of adding a day. You can either see if the cycle starts straight away or manually instantiate it – during the build phase I tend to reduce the polling time to around 10 seconds so you hardly ever have to wait. Just need to remember to reset it once live..

Setting the cycle date to 1 day after the current date mitigates the problem where sometimes cycles do not get instantiated. Was a suggestion from one of the attendees – reducing polling time is another good method.

• Create referential integrity within dimension properties by assigning references to other dimensions. Adding new properties will check data within the other dimension.

This was in reference to setting up dimension properties. Dimension properties can be pulled from other dimensions (or at least it is my understanding that a reference can be made to those.) In the dimension modeler, there is an automatic referential integrity check if this link is in place.

• All users have to have read access to all Account members. You have to design report without salary line or use separate models when dealing with separate information like salary. You can also create an alternate member set without Salary, and then Create a member set with Salary.

All users have to have read access to all account members - is that a statement? I'm not sure I agree.

I am not sure about this one either.

• Use fake data for IFRS - SOX requirements - when dealing with Salary information. Modeler should not have access to data.

• Create a property on Account dimension - Restricted. Assign restricted accounts. In Report Properties for Filters, Setup Filter to only show unrestricted accounts in the filter. Set True Hide & True Lock on filter.

There were some comments during the sessions about protecting restricted account data from developers. There is still not a good way of doing this in my opinion as data has to be seeded at the leaf level and available for rollups.

Sacha Tomey's blog

Learn Microsoft BI > New Video

2008-09-08T08:25:00.001-07:00

I've just posted a new video, and the first about PerformancePoint Server 2007. In it, I discuss just what PerformancePoint Server is and describe the three modules that make up the product. Those modules are:

Monitoring - What is happening now?

Analytics - Why is it happening?

Planning - What do I want to happen?

The video describes how these pieces fit together to form a complete solution for business performance. This video runs 21 minutes.

This video, along with all others, can be found on the Videos page of the site.

Learn Microsoft BI > Home

Radoslaw Lebkowski's Blog: The PPS Monitor & Analyze UDF Project (MAUDF)

2008-09-08T08:23:00.001-07:00

For planning folks, this could probably be modified to read the metadata xml used in PerformancePoint Planning.

Microsoft Office PerformancePoint Server 2007 M&A stores all objects in it's metadata database (named PPSMonitoring in default configuration). Many of these objects are stored in SerializedXML column of the FCObjects table. So, it is very inconvenient to retrieve and search objects with criteria based od particular attributes (XPath / XQuery expressions would be necessary to perform the search).

But fortunately Nick Barclay has created great solution, which makes these operations much easier and straightforward.

This solution is called The PPS Monitor & Analyze UDF Project (MAUDF) and it is the project which delivers a set of useful views and UDF functions, which allow to access and retrieve metadata from PPSMonitoring database with using simple T-SQL queries.

Radoslaw Lebkowski's Blog: The PPS Monitor & Analyze UDF Project (MAUDF)

Forecast Input Form Design - TechNet Forums

2008-09-05T06:29:00.001-07:00

From Jon Crowell in the MSDN forums. How to create a forecast input form with rolling actuals.

Try this:

1. You need scenario and months on the columns.

2. Select Actuals and Forecast in scenario.

3. In Time.YQM, select the first month of the year (or whatever range you want to display) and select "Add Range" from the Time drop-down.

4. Select "from member to current month"

Your selected member should look like this: [Time].[YQM].[All].[2008].[Q1 2008].[January 2008] : Current Month + 0

5. Select the last month of the year (or whatever range you want to display) and select "Add Range" from the Time drop-down.

6. Select "from current month to member".

7. Increase the month offset to 1. (You may decrease the previous range by 1 and leave this at 0 depending on how your current period is set up.)

Your selected member should look like this: Current Month + 1 : [Time].[YQM].[All].[2008].[Q1 2008].[December 2008]

8. Click OK.

9. Select Define member intersections for columns.

10. Select "column by column".

You should see Actual lined up with your first time range and Forecast lined up with your second range.

11. Refresh your report.

12. In report options, set "merge and center headers" to false.

This approach will require you to edit the ranges at the end of each year, but will dynamically update for all other times. You can make this completely dynamic using custom MDX, but it's a little more complicated.

Sacha Tomey's blog – PPSCMD GUI

2008-08-29T06:53:00.001-07:00

Sacha was kind enough to link to some of my posts from the Train the Trainer sessions in Denver.

He has published a simple GUI for the PPSCMD command.

Looking for some automation of jobs from here too and the undocumented commands.

PerformancePoint Server 2007 PPSCMD GUI

I've built a really simple GUI for a couple of commands of the PPSCMD utility. I always take far too long to work out the syntax and navigate to the appropriate directory (Yes, I ought to update the PATH environment variable) that I felt I could justify building a simple front end to help speed up the usage.

So far I've only implemented the MIGRATE and REPROCESS commands - I use these quite a lot outside of any automated SSIS package so they seemed the most sensible to implement in the GUI first. I do intend on extending it to encompass some of the other commands and I would welcome any feedback towards prioritisation, usage, features and the inevitable bugs. It's currently version 0.1 and more or less ready for 'Community Preview' - there are some omissions such as full error handling and validation that I do intend on implementing over time along with the other commands.

It's a .NET 3.5 application so you will need to deploy it to a client where you are happy to install .NET 3.5 if it's not already present.

You can download version 0.1 from here - (You will need to create a login first)

Sacha Tomey's blog

Andrew Fryer's Blog – Memory settings for SQL & Analysis Services

2008-08-26T10:56:00.001-07:00

His memory settings seem very conservative but worth a shot implementing within PerformancePoint VPC.

You can decide on different values based on your workload. Keep in mind that less memory will mean less pages in memory (more IO) on SQL and potentially less space for Analysis Services to cache queries. Processing will also take a hit depending on size since it copies the cube into memory for processing.

SQL Server Database - Set SQL to use max 40% physical memory

Windows - Set <LimitSystemFileCacheSizeMB> to 20% of total, 65022 MB * .20 = 13004 MB. This limits Windows file system cache usage of memory.

Analysis Services- Set SSAS to use max 35% memory, edit the msmdsrv.ini file (make backup first) and set <TotalMemoryLimit>35

Set <PreAllocate>35 (this means total percent of physical), to preallocate memory at server startup. This helps for NUMA enabled servers especially I found. More on SSAS properties can be found here and more tuning analysis Services can be found in this white paper.

If there are problems with runaway queries using too much memory, you can run ASGovernor to cancel queries if memory usage is greater than 40%. (5% past target for SSAS) It might be that this is too large and it needs to have a smaller threshold.

Andrew Fryer's Blog

Tips from Train the Trainer Sessions – Day 4

2008-08-26T08:00:00.001-07:00

Rules

Sometimes you need to remove carriage returns & redo return and put in extra spaces to fix validation issues.
For SQL rules & the time dimension, ensure you use Add Lowest instead of Add Descendants.
Create PEL Business Rules instead of Excel formulas wherever possible. Reusability and centralization of calculations.
Design Time Matrix - Set filter with All checked in order to use formulas with multiple filters.
There are no CAST or CONVERT statements in PEL. If you want to perform math on member properties, use NativeMDX or NativeSQL or Assumption Models.
Create a Scenario called Budget Variance, can use Budget - Actual. Create definition rules. Create 5 variances, % of revenue, ForEx variance, % of sales, etc.
Definition rules are always in order of solve order. The last one orders the calculations. Make sure definition rule TimeDataView Calculation is at the end in order to perform YTD calcs against calculated members.
Keep rules small. Use only single This & Scope for simplicity. Nest RuleSets... but don't ever go more than 1-2 down. Performance would be degraded.
Rule Set - 17 Rules in 3 folders - put them in 1 RuleSet, Create 1 Job that runs the 17 rules.
Comparison to last year rule
Useful function this = ($CurrentPeriod$.Lag(0)) - ($CurrentPeriod$.Lag(12));

Data Input

Use a numeric signoff code column to ensure that users have reviewed the budget and are comfortable signing off.
Include workbook checkbox - Sometimes &ref! is stored in the boxes when corrupted. Ask users to save copy of workbook. Performance issues with 'include workbook'. Avoid.
PerformancePoint includes annotations, which are different than Excel comments. Annotations get saved in the cube. Use View - Annotations in PerformancePoint menu option. Orange cells have annotations. Right-click on each orange cell to check for annotations. Users can only see their own annotations.
You can use data validations inside a matrix. For signoff, you have a list for 1 - I agree as is, 2 - made changes, Excel will stop change.
If 15 assignments are submitted to 1 contributor, separate assigments are available for rejection. The aggregated view is also available.
Use stored procedures to create member set out of member properties as parent/child hierarchy instead of member property. There are limitations to member views vs. member sets.
Use member properties within Filters of the reports to perform cascading filters. Eg. Create a department member property in the employee dimension. Assign employees to departments. Assign filter to property.
Ensure Reports directory is read-only for everyone but modelers.
Create a separate section with "Suggested changes" with read/write access or use Sharepoint Library. Create Groups for Detail & Summary reports.
Remove blank columns and remove blank rows in the Report Properties - Matrix properties -
Intersections - Create asymmetric reports using Row/Column interesections with multple different rows/columns.
When copying Matrices during authoring, you will need to re-add the properties within the matrix as they get lost during a copy.

Planning - TechNet Forums

PerformancePointing

Angry Geeks and User DSNs

RDA Blogs - RDA BI/SQL Server Practice Group Blog

Bidoma Alert | Bidoma.com

Getting rid of GUIDs in SharePoint 2010 database names - The SharePoint Farmer's Almanac

SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE! - SQL Server Team Blog - Site Home - TechNet Blogs

Performance Point Services 2010 Limitations - Microsoft Business Intelligence by Nishant - Site Home - MSDN Blogs

How Microsoft Excel Changed The World

SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect

Configuring extranet access for PerformancePoint Services 2010 - To the SharePoint - Site Home - TechNet Blogs

IBM Press room - 2010-10-21 IBM Acquires Clarity Systems - United States

Forced Parameterization: A Turbo Button?

Forced Parameterization: A Turbo Button?

AMO Analysis Services Create KPI

First PerformancePoint Services 2010 Project Recap « Alan Whitehouse’s Ramblings

Data Mining, Predictive Modeling, Techniques

Dinesh's Blog :::: Being Compiled ::::

XML/A Thin Miner

Excel 2007 – CubeValue Reporting and Excel Services

SSIS Package Manager - PacMan

Todd's World: Why Isn’t Predictive Analytics a Big Thing?

Why Isn’t Predictive Analytics a Big Thing?

InformIT: Safari Books Online: Microsoft Office PerformancePoint Services 2010 Unleashed

Download details: 2010 IW Demo VM (Beta)

System Requirements

PerformancePoint Services : Upgrading PerformancePoint Server 2007 to PerformancePoint 2010 - Straightforward and easy?

Microsoft Excel – Asymmetrical Reports

Creating an Asymmetric Report with the New Set UI

Fast GPUs Accelerate Performance of In-Memory OLAP Server | SYS-CON AUSTRALIA

PerformancePoint Server Planning Gets a New Lease on Life -- Enterprise Systems

PerformancePoint Server Planning Gets a New Lease on Life

Microsoft BI: Partners

Financial Planning Accelerator

Simple Studies - Best Free Online Accounting Lessons

Reed Me : Bing: there it is! My PPS setup problem(s) solved.

Example Gallery – Tableau

Magic Quadrant for Corporate Performance Management Suites

Microsoft Excel : Common Questions Around Excel 2007 OLAP PivotTables

Common Questions Around Excel 2007 OLAP PivotTables

Ten Tips for Using SharePoint Server 2007 with Excel Services

Nick Barclay: BI-Lingual: PerformancePoint Monitoring Data Source Connection Problems

Download details: Microsoft Windows Dynamic Cache Service

Overview

Request for comment: Global filters linked to different models

Log Parser Lizard GUI (free edition) - Lizard Labs

Log Parser Lizard GUI (free edition) - Lizard Labs

Click here to download Log Parser Lizard

Jon Gallant's Blog : Most Popular Microsoft Blogs – January 09

SQL Server 2008 Training Resources - Canada

The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX

Cubegeek: Microsoft PPS: Dead.

Convert PivotTable to PerformancePoint Planning Matrix - Sacha Tomey's blog

Convert PivotTable to PerformancePoint Planning Matrix

Download details: 2007 Office System Driver: Data Connectivity Components

Regular Expressions Cheat Sheet (V2)

Peter Eb. : When will data appear in the cube after a submit from the PPS Planning Business Client?

PPS SP2 and SQL 2008 revisited - Windows Live

Yellow Input Forms for the Assigner Check

BET: Microsoft Excel Formulas - 3D Formulas

What is a 3D Formula ?

These are also known as cubed formulas.

A reference that refers to the same cell or range on multiple worksheets is called a 3D reference.

Using 3D formulas allows you to calculate data thorughout a workbook using multiple worksheets.

All 3D formulas are based on the syntax: Sheet1:Sheet4!A2:B5.

A 3D formula is a formula that refers to the same cell (or range of cells) on multiple worksheets.

The 3D formula "=SUM(Sheet1:Sheet4!A2)" can be used to add up the numbers in cell "A2" on 4 different worksheets.

Download details: PPS 2007 SP2: Data Import Wizard

PerformancePoint Server 2007 SP2: Data Import Wizard

Brief Description

Nugget: Stop a rule in PerformancePoint while running

The Most Popular Microsoft Certification Exams in October 2008 | Jack’s Blog

Microsoft Top 13 Microsoft Certification Exams List:

Financial Systems and Business Intelligence: PerformancePoint Server - Balancing Your Actuals

PerformancePoint Server - Balancing Your Actuals

TechNet Virtual Labs: Office

TechNet Virtual Labs: 2007 Microsoft Office System

PerformancePoint SP2 is out!

Tips from PerformancePoint Planning Series I Course

The Microsoft ProClarity Team Blog : ProClarity 6.3 SP2 (build 2217) Documenation

Filtering Unneeded Dimension Members in PerformancePoint Filters - SQL Server Central