Performance Issues with PPS Management Reporter - Update

After months of testing PerformancePoint Server Management Reporter in it's current state, I have concluded that it will not handle the volumes that our company requires. It is way to slow and will just frustrate the business. The problem lies with the whole design of the system. I did a full analysis of the MDX code and how the system operates and found that the client (on your machine) sends an MDX code string to the Analysis Server for every combination of lines in your report to the entities specified in your reporting tree. So if you have a 50 line report and you are running it for 10 entities you will send out 500 MDX queries, one at a time. As you can imagine this makes my ZX81 (with the 64Kb Ram Pack) look like Usain Bolt pumped up on steroids.

Do not despair, all is not lost. I had a very positive conversation last night with some members of the team working on this issue. I suspect that we could see two positives in the near future. The first being a MASSIVE improvement on SQL Server 2008. PPS should be certified to work on SQL Server 2008 by SP2 which is planned for December 2008. Another idea kicked around is changing the architecture to a service type environment on the server and use multi-threading and the power available. I would guess that this type of change would only be done for V2, so let's wait and see. As soon as I get my grubby paws on a copy of SP2 to test I will post an item on performance improvements.

- Paul Steynberg

The Financial System Manager's Toolbox (Part II)

A while back I promised to elaborate on Microsoft Analysis Services as part of a Financial Systems Manager's Toolbox, so here it is. If there was an Accounting God this would be the tool that he would bestow upon his most loved people. SSAS is shipped with certain SQL Server versions. To see if yours is please follow this link:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

I cannot even begin to tell you how incredible this software is for accountants. (Well actually for most people but you get the idea). It allows one to take huge amounts of data and store it in such a way that you can retrieve aggregated data on any combination of dimensions FAST. Two words in that sentence are of importance. The first is dimension and the second is fast.

A dimension in SSAS terms is one of a possible number of ways to describe a number. Here is an example. I give you a number, say 100. That means nothing to you. Now I start to give you some dimensions and dimension members and suddenly the 100 makes sense. Here they are:

Dimensions and Member of the Dimension
Entity=Contoso Head Office
Department=Accounting
Account=Bank Charges
Scenario=Actual
Period=Y2009P03
Currency=USD

Now that you have the dimensions and the dimension member the 100 suddenly makes sense. It is the actual bank charges (as apposed to the budget bank charges) for the accounting department at Contoso Head Office for the accounting period 3 of Year 2009 in dollars. Now imagine having millions of rows of data each having these dimensions describing the number in each row. To try and make sense of this would be madness. Along come SSAS.

SSAS takes the data and builds OLAP cubes which pre-aggregate the data based on internal algorithms in order to allow the user to bring back information at every possible intersection of data with incredible speed.

Here at my organisation I have built a few financial cubes off our underlying ERP system. These cubes allow the rapid slice and dicing of information from a number of delivery mechanisms. I prefer delivering the data in pivot tables in Excel linked directly to the cube. One can also use tools such as ProClarity and PPS Monitoring and Analytics. The uses are endless. PerformancePoint Server uses SSAS as the data point for all its components including Management Reporter, a product that we are rolling out in parallel with our new ERP System, Dynamics AX 2009.

Once you start using something like this you will know that the Accounting Gods are smiling upon you.

- Paul Steynberg

The Rational Guide to PerformancePoint Server Planning - Not a Book Review

I back-ordered The Rational Guide to Planning with Microsoft Office PerformancePoint Server 2007 with Amazon and was probably one of the first to get my hands on it. I hang my head in shame as it has taken me months to finish it. Not due to any short comings in the book, but rather that my 13 month old daughter firmly believes that my wife and I are put on this planet to serve and play.

The book is written by Adrian Downes and Nick Barclay and assured to contain no bull by Microsoft's own Peter Bull, Principal Group Program Manager for PerformancePoint Server.

I would like to call this a book review but cannot as just reading the book would be like reading a murder mystery with the last chapters torn out. The authors casually point out that you can download bonus chapters from the website, but what they don't tell you is that this is where the gold is to be found. If you buy this book you MUST download the additional 4 chapters.

The book is well laid out and easy to read. There are highlighted notes, tech tips and cautionary announcements throughout and enough screen shots for illustration. Worth mentioning is Chapter 9, Integrating Business Data. This details the ETL process between LOB systems and PPS and from many posts on the forum this seems to be an area of concern for a lot of people.

Both authors are extremely active in the PPS community and this book shows their authority on the subject.

- Paul Steynberg

Leverage Your Existing Investment in Microsoft (Part I)

Under utilisation (Correctly spelled, see footnote 1) of one's current investment is probably one of the most common issues surrounding IT projects. This is especially true with Microsoft. Just as an example when I speak to other Managers about Analysis Services I am most often greeted with a look typically sported by the lesser spotted stunned mullet. It is quite unbelievable how many companies install an ERP system with SQL Server as the underlying database and have no idea that it is shipped with some great additional features such as Analysis Services and Reporting Services. To check what your version is shipped with go to: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Just as a quick FYI, Analysis Services is a framework that allows one to produce cubes for typically “slicing and dicing” ones data, often referred to as OLAP. Reporting Services is a tool that allows one to author and deliver reports from within a web environment. I am not going to go into much detail on these 2 gems (They will feature in a future blog), as the biggest culprit is the Office Environment, Excel being the ring leader.

Excel, Excel wherefore art though, a product that seems as old and familiar as Bill Shakespeare himself. Yip, I’m back here again, lurching on about Accountants and Excel, but you must understand, you just cannot separate them. So if this is true then why do so many Financial Systems not ship with Excel tools as standard. I had this happen to me some years back with our current system. The original decision makers who attended the demonstrations on the product were amazed by how easy the information could be pulled into Excel. However when it came time to actually implement this great functionality we were told that it costs “extra” and we were not licensed. Not just a little, but a lot extra.

Act II – In walks my stubborn alter ego, has a look at the product and says “We can write something like that for free”.

Evil Vendor – Ah, but our product is fully integrated with securities etc.

Stubborn Alter Ego – Big deal.

And so our Excel integration was born a few weeks later with a bit of help from our Database guru. We only worked on this from time to time when we needed a break from our daily routine and if we had to work out the actual man hours we would estimate that it was no more than 40 hours in total.

We now run 7 Excel based extracts from our Financial Systems, 1 Fixed Assets, 2 Accounts Payable and 4 General Ledger. We benchmarked them against the products supplied by our vendor and found that on average they ran 20 times faster. Not kidding, 20 times faster. How was this achieved? There are only 2 questions to be answered. Firstly is the person making the query allowed access to the data and secondly where is the data.

The first question can be answered by finding the security related tables within the database. One normally starts with finding the user table which holds the user id, name and other details related to the user. From here you then link to tables that associate the user with different entities and then profiles/menus/accounts etc etc. You need to understand how the security is set up in order to make the connections. A little bit of digging and you will work it out.

Once you understand the security model you then need to find out where the data is that is required. In my example of one of our Excel extracts we extract the balances of certain accounts chased down from a higher level account into several columns based on some criteria. Below is screenshot of the criteria that the users would enter in order to get the information. This particular extract will look to the account master table in order to return the account description and also the account balance table in order to retrieve the balances for the selected periods.

Most of the sheet is protected which gives the user limited input. Everything in grey cannot be changed, only the white blocks can be. Each of the input fields has a comment attached to it for on-line help and most of them are validated so that users cannot capture invalid information. In reality the users will complete the input fields and save the spreadsheet for repeated usage.

Once all the fields are updated the user then clicks the “Run Extract” button. This button has some Visual Basic code behind it which is also password protected. Once clicked the code traverses through the input fields and passes them as parameters to a Stored Procedure in SQL Server which in turn returns a data set and populates the data sheets.

Here is a step by step explanation of how this particular extract works.

The amount columns from 1 through 6 represent 6 different columns of data for each account that can be extracted and can change for each column based on the fiscal year, the scenario (Actual/Budget/Forecast) the currency (drop down validated to the currency table), Currency type (Also drop down and validated), from and to periods. I have not had anyone ask for more than the 6 comparative columns yet so one can assume that it is a safe number.

The accounts are then entered. If the account is a parent account you can specify the chase level which is just how many accounts down from this account do you want to display. The show levels switch if set to “N” will only show the accounts at the bottom of the chase and if set to “Y” will show the original account chased and all its subordinates down to the chase level. The New Sheet switch if set to “N” will return the data set to the DataDefault Sheet and if set to “Y” will create a new sheet for each account line’s data set that is fetched. Each sheet will be named with the account number.

There is setup information higher up for the Server Name, Database Name and version number of this particular extract. I have not shown this for obvious security reasons.

Now once the user hits the “Run Extract” button the VB Code collects the input data and builds a string that eventually gets sent to the server and database in the set up information as a stored procedure.

The stored procedure accepts the parameters and now goes through a set of validation procedures starting with the user access. When the user calls the stored procedure their domain and user name are available for the procedure to use in order to compare to the security settings within the product. If the user is allowed to see this org and the account then one allows the procedure to continue. If not we return an error message indicating lack of security. As in our case you may have to create a security link table that holds the Windows full domain name of the user logged in and then the user name of the user within the Financial System. If your system is not Windows Security compatible you may have a spare field in the user table to store the windows login.

Once the data sets are returned the VB script does a whole lot of quick steps such as:

Format the numbers columns correctly
Correct the column sizes to fit the data
Put in some totals

Some procedures done before the data is returned are:

Clear the data area to be populated
Remove and formatting including subtotals etc.
Delete any sheets that are not in the original sheet. (We have made provision for specially named sheets that are never deleted so the user can link them up for other purposes etc.)

Some of my other extracts are even more elaborate. For example the AP Voucher extract allows one to return the entire vendor master into Excel for an org, by double clicking the vendor line you return all vouchers from the system in another sheet. By double clicking a voucher one can return the lines that make up the entire voucher and even further down to the distribution etc. Our users just LOVE it, no need to navigate the application if you can just double click in an Excel sheet.

The point of this blog was merely to demonstrate that we must seek out ways to maximise our Microsoft investment and to challenge the hold that some vendors appear to have over us. In my next session on this I will highlight Analysis Services and how this product can make a full grown Accountant weak at the knees.

- Paul Steynberg

Footnote 1

An excerpt from a letter supposedly penned by John Cleese to the citizens of the Unites States of America: You should look up "revocation" in the Oxford English Dictionary. Then look up "aluminium". Check the pronunciation guide. You will be amazed at just how wrongly you have been pronouncing it. The letter 'U' will be reinstated in words such as 'favour' and 'neighbour', skipping the letter 'U' is nothing more than laziness on your part. Likewise, you will learn to spell 'doughnut' without skipping half the letters. You will end your love affair with the letter 'Z' (pronounced 'zed' not 'zee') and the suffix "ize" will be replaced by the suffix "ise". You will learn that the suffix 'burgh is pronounced 'burra' e.g. Edinburgh. You are welcome to respell Pittsburgh as 'Pittsberg' if you can't cope with correct pronunciation. Generally, you should raise your vocabulary to acceptable levels. Look up "vocabulary". Using the same twenty seven words interspersed with filler noises such as "like" and "you know" is an unacceptable and inefficient form of communication. Look up "interspersed". There will be no more 'bleeps' in the Jerry Springer show. If you're not old enough to cope with bad language then you shouldn't have chat shows. When you learn to develop your vocabulary then you won't have to use bad language as often.

A rebuttal to this can be found at http://www.authorsden.com/categories/article_top.asp?catid=19&id=26498