Google
 

Saturday, February 16, 2019

PerformancePoint Services a side note in latest Sharepoint 2019

The Machine Translation and PerformancePoint Services will remain supported, but deprecated, for the SharePoint Server 2019 release. Microsoft recommends PowerBI as an alternative to PerformancePoint Services.

Oh, and the Decomposition Tree has decomposed, since Silverlight is no more.



Microsoft Access Services seems like another dead-man walking.  Microsoft PowerApps and Flows along with the Common Data Model are the alternatives being proposed for Access Services.  I think that Dynamics could fill a gap here if the licensing costs could come down for custom outside-of-the-box implementations.

The Power Platform is the theme in the April 2019 release notes of Dynamics and Business Applications.  Some exciting new changes coming based on those notes.

How about an Enterprise App Store?

Thursday, October 29, 2015

Sharepoint 2016 and PerformancePoint

The Sharepoint 2016 IT Preview doesn't seem to contain much in the way of PerformancePoint.  PerformancePoint Services 2013 are being migrated to Sharepoint 2016 though.  Unsure about the code changes to the platform, if any.  Microsoft's primary focus will likely be PowerBI stack & SQL 2016 for the future, though Sharepoint will also be improved into the foreseeable future so expect convergence starting in the cloud...

Excel Services will be removed from Sharepoint 2016 and available in Office Online Server 2016.  PowerView and PowerPivot will stick around.

Looking for the Reporting Services add-in for Sharepoint for SQL 2016 & Sharepoint 2013/2016?

Sharepoint 2016 will run on SQL 2014.  Some more features from Bill Baer coming in the beta later this year.

Friday, May 16, 2014

PerformancePoint 2015 - A Roadmap or ?

PerformancePoint started out at Microsoft as a tool called Business Scorecard Manager 2005, and before that as an internal tool used for planning.  Companies at the time (or at least Steve Ballmer) were crying out for a decent way to gather Key Performance Indicators from various sources and present them in a well-formulated dashboard.  Third-party vendors or custom code were usually the only options.  Microsoft released this version 1 product to some very positive feedback at the partner conference I attended in Bellevue.  It took numbers from Excel, SQL Server, and Analysis Services (flat, relational, multidimensional) and combined them with a tool that any executive in finance could understand.

I produced some early prototypes based on this software with mixed results.  Since it wasn't quite ready for prime-time, the glitches and awkwardness of using the product were readily apparent.  It really took a developer with an understanding of financial systems to create these dashboards, when Microsoft was trying to offload this task on non-IT staff.

Next comes 2006, and after a couple of acquisitions Office PerformancePoint 2007 is launched.  Microsoft was becoming a serious thorn in the side of companies like Cognos and BusinessObjects, with its combination of Planning, Monitoring, and shortly afterwards Proclarity.  It was around this time I became a certified PerformancePoint trainer, prototyped, trained staff, and implemented the software at various Canadian companies.  I was looking forward to seeing how Microsoft could integrate tools like Data Explorer (anyone remember that one?) with Proclarity and PerformancePoint, to create the ultimate multidimensional client.

PerformancePoint 2007 was a standalone product, in that it stood alone with a forest of other Microsoft products required for a successful implementation.  Windows Enterprise Server, SQL Server Enterprise, Analysis Services, SSIS (there was very limited built-in data integration), Sharepoint, Office, Reporting Services, and a few add-ins and smart clients.  Just about the only thing it didn't require was Flight Simulator, which was unfortunate because I was working right next to an airport and could have kicked off my aviation career.

In 2009, Microsoft discontinued PerformancePoint.  And Proclarity.  And a few other products.  For me it was an upsetting year, as Flight Simulator was also discontinued.  Not to mention a client whom we had great success implementing the software with launched the same day the announcement was made.  Luckily for me the client still saw the power of the tool and continued to build on it, integrating a recent acquisition's 500 locations worth of data for enterprise financial reporting.  They are still using it to this day, though its days are numbered.

In 2010, PerformancePoint Planning was long gone.  PerformancePoint Monitoring (or PerformancePoint Services as it is called in Sharepoint 2013) became a pillar in the Sharepoint stack of services.  Microsoft's planning product was to be rolled into Dynamics sometime in the future, with Excel cube writeback being the stopgap replacement.  Switching my focus between PerformancePoint and other tools around the Analysis Services stack, the planning product slowly faded from my memory.

That's not to say BI at Microsoft is dead.  Excel Services was trumped as the solution to serving up spreadsheets on the web.   This was truly distributed computing for Excel.  My favourite Excel Services visualization was the data refresh flowchart.

With the departure of a few key people from the Analysis Services team, and the emergence of another, multidimensional mode Analysis Services became sidelined by PowerPivot.  With its in-memory, compressed, high performing columnar storage engine, it blew away many of the performance metrics where the standard multidimensional cubes lagged.  And it brought Excel into the world of the million-row spreadsheet.


End of life (mainstream support) for PerformancePoint 2010 within Sharepoint is October 13, 2015.

http://blogs.perficient.com/microsoft/2014/03/spc-2014-roadmap-updates-sharepoint-2015-announced/

Today, Steve Ballmer is looking for a better tool for a different kind of scorecard and I am conquering the elephant.

Wednesday, September 28, 2011

Angry Geeks and User DSNs

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?

clip_image001

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.

Friday, September 23, 2011

RDA Blogs - RDA BI/SQL Server Practice Group Blog

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:

  1. Open your browser as an admin user
  2. Go to this URL http://<server-name>/sites/<site-name>/pages/ppssample.aspx
  3. 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

Wednesday, September 14, 2011

Bidoma Alert | Bidoma.com

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

Saturday, August 6, 2011

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

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.


Wednesday, July 13, 2011

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

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!
  • 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.

Wednesday, April 20, 2011

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

 

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

Wednesday, December 29, 2010

How Microsoft Excel Changed The World

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

Tuesday, December 28, 2010

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

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

Monday, November 22, 2010

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

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

Thursday, October 28, 2010

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

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

Wednesday, October 13, 2010

Forced Parameterization: A Turbo Button?

 

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?

Wednesday, October 6, 2010

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

 
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

Tuesday, October 5, 2010

Data Mining, Predictive Modeling, Techniques

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

Thursday, September 23, 2010

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

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 ::::

Friday, June 4, 2010

XML/A Thin Miner


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

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

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

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/

Thursday, June 3, 2010

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

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?

Wednesday, January 27, 2010

Download details: 2010 IW Demo VM (Beta)

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)

Monday, November 16, 2009

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

 
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

Wednesday, October 7, 2009

Microsoft Excel – Asymmetrical Reports

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

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, clip_image001 and selecting “Create set based on column items”.

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

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

Monday, July 13, 2009

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

 
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

Thursday, June 25, 2009

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

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.

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

Tuesday, June 23, 2009

Microsoft BI: Partners

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

Monday, June 8, 2009

Simple Studies - Best Free Online Accounting Lessons

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

Tuesday, June 2, 2009

Example Gallery – Tableau

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

Monday, June 1, 2009

Friday, May 22, 2009

Microsoft Excel : Common Questions Around Excel 2007 OLAP PivotTables

 

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:

  1. Filtering individual calculated members
  2. 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:

  1. Adding calculated members/measures
  2. 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

 

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

 

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

Tuesday, May 5, 2009

Download details: Microsoft Windows Dynamic Cache Service

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

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

Wednesday, April 29, 2009

Log Parser Lizard GUI (free edition) - Lizard Labs

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

Log Parser Lizard GUI (free edition) - Lizard Labs

 

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

Wednesday, April 22, 2009

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

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

Thursday, February 12, 2009

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

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&#133; -- ( [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

Tuesday, February 3, 2009

Cubegeek: Microsoft PPS: Dead.

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

image

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. 

image
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

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.Context Menu

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

Monday, December 29, 2008

Download details: 2007 Office System Driver: Data Connectivity Components

 

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

Sunday, December 21, 2008

Regular Expressions Cheat Sheet (V2)

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.

Regular Expressions Cheat Sheet 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

Friday, December 19, 2008

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

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?

Tuesday, December 16, 2008

PPS SP2 and SQL 2008 revisited - Windows Live

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

Monday, December 15, 2008

Yellow Input Forms for the Assigner Check

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

Friday, December 12, 2008

BET: Microsoft Excel Formulas - 3D Formulas

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

Thursday, December 11, 2008

Download details: PPS 2007 SP2: Data Import Wizard

 

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

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

Tuesday, December 9, 2008

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

The 640+ certified PerformancePoint Specialists will soon have company.

Microsoft Top 13 Microsoft Certification Exams List:
  1. .html">70-285:MCSE Designing a Microsoft Exchange Server 2003 Organization
  2. .html">70-442:Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 is in development
  3. .html">MB7-517:NAV 5.0 Installation & Configuration
  4. .html">70-556:TS: Ms Office PerformancePoint Server 2007, Applications
  5. .html">70-089:Planning, Deploying, and Managing Microsoft Systems Management Server 2003

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

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

 

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

 

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!

    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

    Thursday, December 4, 2008

    Tips from PerformancePoint Planning Series I Course


    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

    1. The current date is within the Assignment start/end date/time
    2. The cells you are looking at are within the start and end periods for the assignment (that you selected from the time dimension)
    3. The scenario you are trying to input to is the scenario you selected for the cycle that the assignment is using
    4. 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
    5. The user you are logged on as has access to the model set to “On” in the summary view of the user’s role.
    6. That read/write access is enabled for all of the dimensions selected in the filters/rows/columns you selected above.
    7. The status of the assignment is listed as “started” or “partial”, not “Not Started” or “Submitted”, or something else.
    8. That the assignment is opened using the assignment list in the action pane or from the assignment search window.
    9. That the report property “Allow data entry” is set to true
    10. 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