A Novel Way of Dealing with Excel Models

Excel is both heaven and hell in the business world. It's a great calculator, modeler, reporter, you name it. With this incredible flexibility comes a trade off, your data is stored in multiple locations throughout the company in an unstructured and often unsecured manner. This data is not visible to the Enterprise and in most cases is not integrated into your reporting stack. Loads of capturing and data copying results in huge inefficiencies and in many cases errors.

My current employer embarked on a project 18 months ago to "black box" a number of these high profile models. Without going into the gory details let's just say that it did not work. I joined them in April of this year when my family and I made the move to London and was tasked with firstly evaluating the models to give an opinion on whether they were fit for purpose and could they be supported into the future. Sadly they could not and it was my unfortunate responsibility to stop the bleeding and bring the project to a conclusion. That was the easy part, the hard part is what do you do now as the original issues around the Excel Models were still relevant.

The obvious choice would be to take a look at the short listed products again that were selected at great expense and find the next suitable candidate. I was not comfortable with this approach and we did not want to try and rebuild them in PerformancePoint as all development for the product has been discontinued by Microsoft. (Don't even get me started on this subject, I have bitten my tongue since they shook the market with this announcement in January 2009).

I have come up with a rather "out of the box" approach which my team is in the process of designing at the moment. We needed to achieve a few objectives from the project being:

• The models should be flexible
• They should have access control
• Changes should follow appropriate change control
• They should be auditable
• The data should be stored centrally and available to other business systems

Package these requirements up with the new Solvency II requirements and SOX etc, and you have quite a tall order.

Off course the most flexible model is still Excel. So what if you could keep Excel and also apply security, access control, change control, auditing etc. This led me into a journey of trying to find software that would give us this functionality. And guess what, it does exist, albeit a software concept in its infancy. Gartner have even released a paper on it (March 11, 2008) under the heading "MarketScope for Spreadsheet Control Products, 2008". It was quite comforting to find out that we had made contact with all the major players prior to this report and that our findings were in line with Gartners. Here are the major players in this space:

• Cimcon Software
• ClusterSeven
• Finsbury Solutions
• Prodiance

They all have a presence in London. Cimcon and Prodiance are US based suppliers whereas ClusterSeven and Finsbury are UK based.

Now they all do pretty much the same thing but in slightly different ways. Essentially each product has 3 major functionality groups:

• Discovery and Risk Assessment - Basically trolling through all your locations looking for spreadsheets, categorising them and creating a baseline.
• Monitoring - Keeping track of your spreadsheets and keeping a full audit trail of all changes and versions.
• Development Tools - Some are add-ins, others applications that assist in developing your spreadsheets to minimise risk, errors etc.

So in a nutshell you can see who did what to which spreadsheet over time and set up alerts and workflows etc. You will be able to see broken links between spreadsheets and a whole host of interesting things that are out of the scope of this paper. We are in the process of getting RFP's from the suppliers and although I have some opinions will reserve them until a later date.

This piece of software should take care of most of the issues surrounding the Excel Models. Now for centrally storing the data and making it available to the rest of the business. My team is in the process of requirements gathering for a framework that will allow us to upload many different data sets from these models into a SQL Server database and thus make it available to our reporting layer. This may sound quite simple but to build a framework that is scalable. supportable and flexible is no easy task.

Watch this space!

- Paul Steynberg

PerformancePoint Server Management Reporter and SQL 2008

Does Management Reporter (MR) work with SQL Server 2008? Yes, if you apply SP2 which has been recently released by Microsoft. I have tested this with huge improvements in performance. But that is not the reason for this blog. Something a lot more sinister is afoot.

I started the MR rollout to the business after upgrading all our pre-sp2 installs. We migrated the ManagementReporter database from the SQL Server 2005 environment to the SQL 2008 environment with no problems.

Here is the BUT. When you install MR on a clean machine you MUST, during the install process give it a valid ManagementReporter database to connect to. Now in order to install SP2 you must firstly install RTM. SP2 is designed to work with 2008 but RTM does not and lets you know in no uncertain terms. So you sit with a chicken/egg story. You want to install SP2 to make it work with 2008 but because the DB is 2008 you cannot firstly install RTM.

Work around. DO NOT delete a copy of MR DB which you have anywhere on the network that is already on SQL 2000/2005. Or just install a DB from the RTM version on to any 2005 SQL box. During the client install you must point to this DB in order to get RTM complete. Apply SP2 and then create a new connection to the 2008 DB and delete the old connection. Simple but unfortunately necessary.

This has been raised as a bug and is in production.

- Paul Steynberg

PPS Planning - Current Period

Over the past 2 years one of the biggest mysteries to a lot of PPS Planning users is the storage of the current period which is set in the Business Planning Modeler. This has been discussed and a number of posts on the PPS forum have clearly demonstrated how one can get to it. However I have yet to see a full set of code to bring this setting into your environment for use. I use the current period for my hourly PPS updates from our ERP system so as to make sure that we do not reload old data and speed up the process. So part of my ETL process interrogates the XML blob in the table BizAppNodes and returns the current period ID and Label for each model. These records are inserted into a table that I keep up to date and then use this in my ETL process. Here is the code:

DECLARE @xmlblob xml

SELECT @xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX))
as xml)

FROM BizAppNodes ba

WHERE VersionEndDateTime = '9999-12-31
00:00:00.000'

AND BizAppNodeLabel = 'FinanceModel'

SELECT distinct ModelLabel

,CurrentPeriodId

,T.[MonthLabel]

FROM

(

SELECT DISTINCT

tab.col.value('../../@Label', 'varchar(30)') as ModelLabel,

tab.col.value('@CurrentPeriodId', 'varchar(30)') as
CurrentPeriodId,

tab.col.value('@EffectiveDate', 'varchar(30)') as EffectiveDate

FROM @xmlblob.nodes
('/BizModelSite/Models/ArrayOfBizModel/BizModel/EffectiveDatedCurrentPeriods/EffectiveDatedCurrentPeriod')
as tab(col)

) AS TABLE1

LEFT OUTER JOIN [dbo].[D_Time] T ON T.[Month] =
TABLE1.[CurrentPeriodId]

The output is the model name, period id and period label for each model in the application.

- Paul Steynberg

PPS Up Your Street

I have added a new link to another PPS blog by David Street. It makes for some good reading.

- Paul Steynberg

PerformancePoint Server SP2 - Feedback

Nick Barclay, Tim Kent and Sacha Tomey have all posted some detailed blogs on what you can expect from PerformancePoint Server SP2 so I will not labour the point. However here are some real live bits and bobs as I installed SP2(Beta) over a month ago under the following configuration:

• PPS Server, Windows 2008 Server and SQL Server 2008. X64, 4x6 core processors, 64 Gb Memory.
• SQL Server, Windows 2003 Server and SQL Server 2008. X64, 2x4 core processors, 64 Gb Memory.

We put a new server in for PPS but used an existing one for SQL. This will be upgraded within the next 3 months.

The install did not give any surprises. You must firstly install PPS RTM and then apply SP2 as was expected. Something that did bite us was the fact that you could use the PBM on the server but NOT from your desktop. My guys in networks are convinced that it is something to do with the way credentials are double hopped from the desktop to the PPS Server to the SQL Server. (They mumbled a whole bunch of stuff about Kerberos and AD and it would take days to figure out the problem.) To fix this we just changed the PPSPlanningWebService “ASP.NET impersonation” from Enabled to Disabled. Here is the error message you will find in your event viewer:

Date 14/11/2008 15:38

Log SQL Server (Current - 14/11/2008 15:38)

Source Logon

Message

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: [Ip address here]]

Date 14/11/2008 15:38

Log SQL Server (Current - 14/11/2008 15:38)

Source Logon

Message

Error: 18456, Severity: 14, State: 11.

Considerations when Installing on Windows 2008.

You need to make sure that Web Server (IIS) is installed. If not open Server Manager, click on “Add Roles” and Install Web Server(IIS).

Once installed open up Server Manager and click on Roles.

Click on “Add Role Services”.

Install the following services:

Application Development, all except “Server Side Includes”.

Under Management Tools, “IIS Management Console” and “IIS 6.0 Management Capability”.

Under Security “Basic Authentication” and “Windows Authentication”.

Now install the PPS RTM 64 bits. DO NOT run the Configuration Manager.

Install the PPS SP2 64 bits.

Now you can run the Configuration Manager for the PPS Planning. You must have SQL Server Cumulative Update 7 installed. We needed to install these files to ensure that we met the requirements:

• SQLServer2005-KB949095-x64-ENU.exe
• sqlncli_x64.exe
• SQLServer2005_ADOMD_x64.exe
• SQLServer2005_XMO_x64.exe

To register ASP.NET 2.0 Web Service Extensions in IIS:

• Open cmd : Start -> Run -> cmd
• Navigate to the correct folder and run:
• C:\Windows\Microsoft.NET\Framework64\V2.0.50727\aspnet_regiis –ir

You can then start the Configuration Manager again and from here on it is stock standard as per normal.

We needed to update to SP2 so that we could get the benefits on the Management Reporter in terms of performance. The improvements in performance are massive HOWEVER they are just not good enough. It still takes way too long to produce our reports. So back to the drawing board for Microsoft. They are now performing a full evaluation of the product and are working out how to improve it.

- Paul Steynberg

PerformancePoint Server - An ETL Tip

Anyone running a Financial Model within PPS Planning in all likelihood updates the actuals from an ERP System within the business. Under normal circumstances this will entail (amongst a myriad of other things) the following:

• Synchronize MG Tables to the Stage Area.
• By way of some ETL tool (Normally SSIS) bring in your actuals.
• All Deletes, Inserts and Updates are then written into the MG Table with the appropriate BizSystemFlag. 200 for Inserts, 300 for updates and 400 for deletes.
• The data is then loaded from staging to the RefDB.
• The model is processed.

Now one of the biggest problems in this entire process is the time it takes to Synchronize the MG tables to the Staging database and then if you have an enormous number of records, the inserting of these to the RefDB. (We have 22 million records in our MG table).

So we have decided to shortcut the process. We created our own ETL SSIS packages to move the data into the MG tables in the Stage database. This method completely negates the effort of synchronizing the MG table to the staging database as we join across the 2 databases in order to detect any updates or inserts. As the data always comes from a LOB system we never do any deletes. For new records we just insert them into the Stage database with the BizSystemFlag of 200. For updates we fetch the existing record from the RefDB into the StageDB and insert an additional record with the BizSystemFlag of 300. You cannot insert a record of type 300 or 400 without the accompanying 100 record. If you do the load will fail and you will get errors on those records.

By adopting this methodology we have reduced our update of PPS to under 5 minutes and it is run every half hour so that our reporting will be up to date. Another advantage of not synchronizing is that your indexes on the MG table in the staging DB are not dropped. Saves a lot of time.

- Paul Steynberg

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.

Remember that when loading from your ERP system all Balance Sheet Items are loaded as “CLO” for closing balance and all your Income Statement items are loaded as “NONE”. Your rules will calculate the Opening Balance “OPE” records and the Movement “MVT” records prior to you processing the model. In order to balance you then just bring the data into a pivot table (Standard connection to analysis services) and then check that your totals come to zero. But it can get a bit confusing if you do not get your combinations of Flow and TimeDataView correct. So to check you Year to Date figure you select TimeDataView as “YTD” and multi-select “NONE” and “CLO” for the flows. To check the period movement only change the TimeDataView to “Periodic” but then set flow to “NONE” and “MVT”. Voila, it should all balance as per the example below. In this example the 1 and 2 series are Balance Sheet and 3-9 series Income Statement. Also note that if you just add up the numbers you will not get to zero but the Pivot Table understands that some of them are signed and that they should be subtracted. Take a look here and here for a quick overview on this subject.

- Paul Steynberg

PPS Management Reporter - Some Key Tables

While authoring some reports using the Management Reporter Designer from PerformancePoint Server I had reason to start digging through the tables in order to make sense of the row definitions. During this little excavation I identified some tables that thought might just be useful for somebody else if they knew this information upfront.

Firstly it would appear as though the report meta data is stored in a set of tables with the prefix “Control”. So for example the row definitions are stored in a number of tables but the one that resembles the layout in the designer is “ControlRowDetail”. Listed below are a list of tables that I needed to look into.

ControlCompany

When one has connected to a Management Reporter database (known as a connection) you are then required to connect to an entity. One creates an Entity and when doing so you have 2 “out of the box” sources being the Financial Data Mart 7.0 or PerformancePoint Server. This data is stored in the ControlCompany table. The connection data is stored in XML format and looks something like this:

EntitySetting Name="Connection"; Value xsi:type="xsd:string" /; /EntitySetting; EntitySetting Name="Model"; Value xsi:type="xsd:string";Financial Reporting/Value; /EntitySetting; EntitySetting Name="FunctionalCurrency"; Value xsi:type="xsd:string";ZAR/Value; /EntitySetting; EntitySetting Name="Calendar"; Value xsi:type="xsd:string" /; /EntitySetting; EntitySetting Name="Address"; Value xsi:type="xsd:string";http://servername:46787/Value; /EntitySetting; EntitySetting Name="Application"; Value xsi:type="xsd:string";TheGroup/Value; /EntitySetting; EntitySetting Name="ModelSite"; Value xsi:type="xsd:string";FinanceModel/Value; /EntitySetting; EntitySetting Name="OLAPServer"; Value xsi:type="xsd:string";OlapServerName/Value; /EntitySetting; EntitySetting Name="OLAPDatabaseName"; Value xsi:type="xsd:string";TheGroup_FinanceModel/Value; /EntitySetting; EntitySetting Name="Cube"; Value xsi:type="xsd:string";Financial Reporting/Value; /EntitySetting; EntitySetting Name="CalendarHierarchy"; Value xsi:type="xsd:string";Financial Calendar/Value; /EntitySetting; /ArrayOfEntitySetting;

Associated with the Entity is also a Building Block Group. This is stored in the field SpecificationSetID.

ControlSpecificationSet

The ControlSpecificationSet table stores the Id, Name and Description of the Building Block Groups referred to in the ControlCompany table.

ControlRowMaster

By using the SpecificationSetId from the ControlSpecificationSet table one can then return the Id’s and descriptions of the Row Definitions for the Building Block Group selected.

Now in order to get to the data that I needed (ie what accounts make up the lines in the Row Definition) one has to look at several tables being the ControlRowLinkMaster which gives you the ID for the ControlRowCriteria field called RowLinkId. You obtain the RowDetailId from the ControlRowDetail. This is used to build a query on the ControlRowCriteria, see example below:

select * from dbo.ControlRowCriteria where
rowlinkid='76DCEA67-9069-46E1-9704-2F42A3E0BC72' (Obtained from
ControlRowLinkMaster) and rowdetailid='E430DB82-081B-475A-B7E4-81AF5ECC3725'
(Obtained from ControlRowDetail)

You now have the dimensions and exactly what the criteria for each of those dimensions are for the row being looked at.

I needed to do this in order to generate a list of lowest level accounts from a report to make sure that EVERY account in my hierarchy was represented in the report at some point.

I am sure that the same logic above can be applied to Column Definitions etc etc.

Other tables worth mentioning are those surrounding security which all start with the word “Security”. So to find your users you look at SecurityUser etc.

- Paul Steynberg

PerformancePoint Server Planning and Management Reporter - Further Considerations

When I originally listed a few things that one should bear in mind when designing a PPS Planning Model which will ultimately be used for reporting via the Management Reporter I forgot to mention this little tit-bit of information.

One should be very careful in naming your Entity Dimensions especially if you require Management Reporter Designer to self generate your reporting tree definitions. As in our situation it is quite possible to have a number of entities that have unique labels but duplicate names or descriptions. So as an example you may have the entity called “Finance” in a number of your entity hierarchies pertaining to various companies or divisions within your structure. Because these are identified separately in PPS Planning due to the label being different the problem is not immediate. The problem becomes apparent when you try and import all your entities into a Management Reporter Reporting Tree. The system identifies that you have duplicate Unit Names and will not allow you to save the Reporting Tree. (Ours lit up like a red Xmas Tree)

How to fix this. Luckily it is quite easy to create your descriptions in SQL or Excel and then just copy and paste them into the Reporting Tree Grid in the designer. All we did was concatenate the label and the name with a hyphen in between from SQL. This way you are assured of name and description uniqueness in your reporting structure. Or make sure that your Entity dimension names are unique.

- Paul Steynberg

Dynamics AX 2009 - ETL for PPS

Unlike our old system Dynamics AX is more suited to the PerformancePoint
Server Planning data requirements. Our old system stored the account movements in buckets by period in a single line for a year. In order to make this PPS friendly I had to use the SQL Server 2005 function "unpivot". We also had to hard-code certain things into the extract scripts in order to make sense of it in PPS. As an example here is the code I wrote. (It was beautifully structured and formated until a pasted it into the blog).

select case when code_fscs_dsg='A' then 'ACTUAL' when
code_fscs_dsg='B' then 'BUDGET'end as ScenarioLabel,case when
Period='Amt_Perd_01' then 'P1'+' FY'+cnt_yr_fscs when Period='Amt_Perd_02'
then
'P2'+' FY'+cnt_yr_fscs when Period='Amt_Perd_03' then 'P3'+'
FY'+cnt_yr_fscs
when Period='Amt_Perd_04' then 'P4'+' FY'+cnt_yr_fscs when
Period='Amt_Perd_05'
then 'P5'+' FY'+cnt_yr_fscs when Period='Amt_Perd_06'
then 'P6'+'
FY'+cnt_yr_fscs when Period='Amt_Perd_07' then 'P7'+'
FY'+cnt_yr_fscs when
Period='Amt_Perd_08' then 'P8'+' FY'+cnt_yr_fscs when
Period='Amt_Perd_09' then
'P9'+' FY'+cnt_yr_fscs when Period='Amt_Perd_10'
then 'P10'+' FY'+cnt_yr_fscs
when Period='Amt_Perd_11' then 'P11'+'
FY'+cnt_yr_fscs when Period='Amt_Perd_12'
then 'P12'+' FY'+cnt_yr_fscs when
Period='Amt_Perd_14' then 'P12'+'
FY'+cnt_yr_fscsend as Time_MonthLabel,
right(rtrim(left(id_acct,21)),8) AS
ACCOUNTLABEL,case when
Period='Amt_Perd_14' then 'MANADJ' else 'INPUT' end as
BusinessProcessLabel,left(ID_ACCT,13) as EntityLabel,'PERIODIC' as
TimeDateView,CODE_CURN_FSCS as CurrencyLabel,case when
substring(id_acct,14,1)
>= '1' and substring(id_acct,14,1)<='2' then 'CLO' else 'NONE' end as FlowLabel,substring(id_acct,6,2) as Country,Value,null as RowId, '' as RuleID, '' as ContextID, '' as AssignmentID, GETDATE() as CreateDateTime, GETDATE() as ChangeDateTime, 0 as LoadingControlID, 200 AS [BizSystemFlag],'' as BizValidationStatus, '' as BizSystemErrorDetailsfrom (Select Id_Acct,code_fscs_dsg,cnt_yr_fscs,CODE_CURN_FSCS,fscs_curn_type, Amt_Perd_01,Amt_Perd_02,Amt_Perd_03,Amt_Perd_04,Amt_Perd_05,Amt_Perd_06,Amt_Perd_07,Amt_Perd_08,Amt_Perd_09,Amt_Perd_10,Amt_Perd_11,Amt_Perd_12,Amt_Perd_14 from iMp30_afs)punpivot (Value for Period in (Amt_Perd_01,Amt_Perd_02,Amt_Perd_03,Amt_Perd_04,Amt_Perd_05,Amt_Perd_06,Amt_Perd_07,Amt_Perd_08,Amt_Perd_09,Amt_Perd_10,Amt_Perd_11,Amt_Perd_12,Amt_Perd_14)) as Unpvt

As mentioned Dynamics is better suited as it stores the balances by company, dimensions and accounts per day. So having to unpivot the data is now unnecessary. The information is now stored in a table called LedgerBalancesDimTrans. I have had a cursory look at the AX to PPS Wizard and have it on good authority that it will take across your transactions by day. Now I hate duplicating data so have written our extract to summarize the data by Ledger Period before loading it into PPS. In order for all this to make sense one has to make sure that the descriptions captured in the Ledger Period table match those that you are going to use when setting up the PPS Calendar series. For example I have used the format P1 FY2009 in PPS and must type this into the Period description on the table LedgerPeriod in Dynamics. Now my extract will bring across movement by period in the format that I require for PPS. There is subsequent conversions to id's etc but I will not get into that now. Below is the much shortened and simpler extract.

SELECT A.DATAAREAID AS DATAAREAID, A.DIMENSION AS DIMENSION, A.ACCOUNTNUM AS ACCOUNTNUM, P.COMMENTARIES AS PERIOD, SUM(A.DEBITMST+A.CREDITMST) AS VALUEFROM LEDGERBALANCESDIMTRANS A JOIN DBO.LEDGERPERIOD PON A.DATAAREAID=P.DATAAREAID AND A.PERIODCODE=P.PERIODCODE AND A.TRANSDATE BETWEEN P.PERIODSTART AND P.PERIODEND WHERE P.PERIODSTART>='2008-03-30'GROUP BY A.DATAAREAID, A.DIMENSION, A.ACCOUNTNUM, P.COMMENTARIESORDER BY A.DATAAREAID, A.DIMENSION, A.ACCOUNTNUM, P.COMMENTARIES

This was also laid out in an orderly fashion but it appears that the blog editor does not respect my line feeds and tabs. I think you will agree that Dynamics AX 2009 lends itself to easy integration with PPS Planning.

- Paul Steynberg

Dynamics AX 2009 - Half Way Review

I have spent the past 14 weeks with Dynamics AX 2009. In this time we have installed and configured it with a view to going live in 2 weeks time. I am reasonably happy with the product so far but, like all enterprise software solutions, one only really gets to grips with it once you go live. In these few weeks we have configured General Ledger (GL), Accounts Payable (AP), Accounts Receivable (AR) and Fixed Assets (FA). We have also written interfaces to and from our Stock & POS, Payroll, Bank Reconciliation and PO System. We are also in the process of finalising bespoke integration with Excel for uploading of journals and invoices.

Database Design

Believe me, I have asked the question, but no answer as yet. The design of the database flies in the face of database design principles. Very little attempt has been made to normalise the tables. The biggest sin of all is that the company code (DataAreaId, Char(4)) is stored against EVERY record in the entire database. This would have been the prime candidate for a surrogate integer key. Follow that closely with AccountNum and Dimensions and one can see the HUGE space savings and potential performance improvements. This design, in my experience, has 2 potential sources. One, the database was designed by front end coders. Two, the database was designed by someone who has little experience with large record sets. What is going to happen when my LedgerTrans table gets to 100 million rows? On that note based on all the answers that I have received back from Microsoft it would appear as though Dynamics does not have a stock standard archiving solution shipped with it. So this is what I'm thinking - "I have a database which is not normalised, check. My business processes approximately 40 Million Journal Lines per annum, check. Dynamics does not ship with an archiving solution, check. Question - What will happen to performance and database size over the next 3-5 years?". The answer? Upgrade the DB to SQL Server 2008 with compression set on. Talk about banking on technology improvements.

Something else that really baffles me is the apparent lack of referential integrity. There are also no stored procedures and very limited use of views. I understand that the product is also designed to run on Oracle but one has to ask the question. Why does a company that has developed a database and punted certain principles of design within that product group then write a product that forsakes all of them.

Accounting and Interface Design Principles

I have wrestled hard trying to come to terms with the design principles and how Axapta was conceived. It is both ingenious and ludicrous at the same time. The concept that one needs to get under the belt very early is that you can do just about anything from anywhere when it comes to journals. One is able to debit a supplier in Accounts Payable and credit a customer in Account Receivable directly etc etc. The system setup through it's posting profiles etc from all Subsidiary Ledgers keeps the General Ledger in balance at all times. With such abilities comes the inevitable framework to flummox even some of the most seasoned accountants. When configuring the system you have to keep your wits about you and really focus on the requirements. I cannot help but think that somebody with excellent systems design talent sat down with a bunch of accountants and when all was done and dusted he presented a system, not as the Royal Accounting Society would have done it, but rather as a Technological show piece. I like it, but it will take sometime for the business to fully come to terms with it.

External Interfacing

Dynamics AX is somewhat of a framework and does allow a number of ways to interface to it. The most prominent being through Web Services, the Application Integration Framework (AIF) and natively using X++. If you have read my prior blogs you will know that we hoofed the AIF due to bugs. We have used X++ to interface from our LOB Systems. (When I get a chance I will pen a full article on how we achieved this). After our experience with the AIF and BizTalk we have not endeavoured to test the Web Services.

Reporting

Dynamics AX does not ship with adequate report writing capabilities. Period. 

Frx Reporter is an additional cost and we all know that it is on it's way to the grave. PerformancePoint Management Reporter can be purchased BUT no standard direct data access at this point. My approach to this has been to keep the exact same PPS Financial Model as from our current ERP System and to just add to the data from AX after we go live. This way our Accountants will have one source of reporting data and it will have current data as well as years of history.

Overall Opinion

To date nothing in AX has wowed me. I am impressed with the development framework, disappointed with the reporting and pretty much neutral with everything else. We are not sure how it is going to perform in the wild but time will tell. Both our AOS and SQL Servers are way over spec'd so we do not really think that performance is going to be an issue. We followed the standard guidelines on setting them up. I do believe that we should however move to SQL Server 2008 as soon as we are comfortable with it. (Read- wait for SP1).

Management Reporter Considerations

When using Management Reporter (MR) with PerformancePoint Server Planning you must take certain things into consideration. To start with you must have a Financial Model as your source for MR. This will give the entity wizard a few things that are required such as currency and a calendar. It is also crucial that the calendar view selected for MR must have Years and Months.

How you set up your dimensions is also very important especially if have specific reporting requirements. None of the dimension attributes are exposed to MR so if you require them then create separate dimensions. For example you may have set Country as an attribute of your Entities and you will not see these in MR. Actually create a new dimension called Country and suddenly it will pop up in MR for use.

Another consideration of labeling your dimensions is that when exposed in MR, the hierarchies are not visible. It may be a good idea to identify your dimensions in some manner so that one can determine parents vs leaf level members. You may consider having an alternate hierarchy dimension for accounts that only show posting level accounts. This way you do not have the risk of adding already totalled accounts more than once.

By just keeping these few things in mind when creating models for reporting through MR will make life a lot easier.

- Paul Steynberg

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

Management Reporter - Scheduling Reports

When I first go my hands on Management Reporter last year I was very disappointed to find that you could not run the reports unattended on a schedule. You had to be logged into the Report Designer and had to physically run the reports one at a time. As this would not do I took my case to Jill Carter and her team while attending the AX conference in Orlando. I asked her who I would have to beg or crawl naked over broken glass in order to get this functionality.

She did not disappoint. They are in the final stages of a "solution" which they will make available to me in the next week. From what I can gather it allows you to schedule reports via the command line. This means you can schedule them using Task Scheduler or from SQL Server Agent. Either way the news is good.

As soon as I have it in my grubby little paws I will test and feedback.

- 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

PerformancePoint Server and the Art of Debits and Credits

All good accountants know that if you add up the trial balance for a company you should always get to zero. True? Maybe not when loading your data from your current General Ledger to PerformancePoint Server (PPS). An accountant is drilled from day one that debits are positive numbers and credit are negative numbers. This is the way most Financial Systems store the data and when you see that an asset is positive and a liability is negative it makes sense to everyone. Now try and explain to a non-accountant why income is a negative as well. Its a no go area and with good reason, it makes sense that everything good is positive and everything bad is negative.

Back to PPS, if you load your trial balance into the product with everything correctly signed, you will find that your trial balance when viewed from something like an Excel Pivot Table does not add up. So what has happened?

The answer is that the Account Dimension in the Planning Business Modeler has intelligence built into it. When you define an account you must also define the account type. Associated with the account type are time balance attributes as debit/credit attributes. In the screen shot below from the help file we can see what Microsoft attribute associations for income statement accounts.







So this tells us that for expenses the debit/credit attribute is debit and the incomes are credit. Just like the Prof at University said. As a result of this intelligence you must therefore be careful when loading your trial balance as you must switch the signs for all those accounts that you would normally show as negatives but have credit attributes in the account type.

The non-accountant likes to see sales and expenses and positive numbers because he subtracts one from the other. The account likes to see sales and a negative and expenses as a positive and adds them.


Below is an example of how the back ground intelligence works. The account series 1 are assets, 2 are liabilities, 3 sales, 4 cost of sales, 5 through 9 expenses. An account looking at this table would just add the whole lot up and inform you that it does not cast. The pivot table in Excel is reading from the cube directly and as you can see comes back to the holy grail of trial balances, ZERO.




Score? One for the man in the street and zero to the accountants.


- Paul Steynberg

The Flow Dimension for PerformancePoint Server Planning

One of the internal dimensions for PerformancePoint Server Planning is the Flow dimension. When creating a Financial Model with Shares Calculations it is standard and cannot be deleted from the model. You must however add it for a Financial Model without Share Calculations. A number of people new to the product do not understand what this dimension is used for and initially it does not really seem to be relevant. It's relevance however becomes transparent when you try and report on your financials. The standard dimensions cannot be deleted and here is a list of them on the left from the Planning Business Modeler (PBM).



To make it easier to understand just focus on 4 of the dimension members being NONE (None), OPE (Opening Balance), MVT (Movement) and CLO (Closing Balance). Now put them into perspective when looking at your trial balance. All income statement type accounts start each fiscal year with no value and accumulate until the end of the year. Each fiscal period has movement and one normally views them as monthly, year to date, quarterly etc etc. Balance sheet items on the other hand start every fiscal year with the closing balance from the prior fiscal year. They also have monthly movement and are normally viewed as at a point in time which in accounting terms is the closing balance. So to summarise, profit and loss (income statement) accounts have periodic movement and balance sheet accounts have an opening balance, periodic movement and a closing balance.


The next question you ask is the how do they tie up to the Flow Dimension and how does one load your current General Ledger (GL) information into PPS using them. Simple, load all income statement accounts with a Flow of NONE and load all balance sheet accounts with the closing balance (CLO) for each period. PPS then needs to calculate and populate the opening balances and periodic movement for the balance sheet accounts.


If you go to the business rules for the model and look under "System Defined Procedural Rules" you will find several rules that do just this. The first rule is called "Chart_CarryForward" and the second "Chart_GAP_Movement". These rules require parameters in order to run which are:

• Scenario
• Entity
• StartTimePeriod
• EndTimePeriod
• BusinessProcess

There is also a batch rule that will calculate the movement for all entities and scenarios called "Chart_Batch_GAP_Movement".


These calculations create records in the MG table for the model and when the cube is refreshed they appear for reporting purposes.


One may then question how do you arrive at YTD or CompareToLastYear totals for income statement accounts if you only have a Flow on NONE. This is calculated in the cube only under the definition rules called "TimeDataView Calculation". This rule will distinguish between balance sheet and income statement accounts and properly calculate what the YTD or comparative value are.


By using the combinations of the flow and the timedataview dimensions from within a tool such as Management Reporter one can create some complex reporting solutions.


- Paul Steynberg

PerformancePoint Server - Management Reporter

Management Reporter is bundled with PerformancePoint Server 2007 (PPS) and is actually a re-write of Frx Reporter which has been around for quite some time. It now runs on SQL Server and connects to the PPS Financial Models. The team has also buffed the UI and it has the Outlook pane look and feel. Unfortunately they did not have enough time to give it the new "ribbon" Office 2007 look.

In short it is a tool for writing financial reports which looks like it has been designed by accountants for accountants. It comprises 3 elements, the designer, the viewer and the database that stores the report layouts and data. From within the designer one creates reports using a "building block" method. The rows, columns and reporting trees are defined and then pulled together in a report definition. This methodology allows a tremendous amount of re-usability and flexibility. All terribly easy to use. I had no Frx experience and read the help file which has a mini tutorial look and feel about it and within a few hours had reproduced our Income Statement with some complex calculations for PMO and GP etc etc.

One can publish the reports to MS Reporting Server, export it to Excel or just view them in the MR Viewer. Facilities exist to e-mail the report to users as well.

I have been using it for the past 6 months or so and one does need to be aware of certain issues surrounding the installation and usage thereof.

Installing

If you try and install the database remotely you will get an error. You must actually install the product on the server that houses the Management Reporter (MR) database. Only once you have done this can you install the client on your desktop. The documentation is not entirely clear on this.

Another "gotcha" is that you must have the PPS Excel Client (Planning Business Client) installed on your PC in order for MR to work.

PPS Hierarchies

If you thought that your PPS account and entity hierarchies were going to be respected you will be disappointed. The pop up selection box for accounts and hierarchies does not distinguish between those accounts that are at the leaf level or those that are parents and thus summary level accounts. If your accounts do not have a distinguishing feature within the account naming you will be lost.

PPS Member Properties

Something else I found a bit lacking was that none of the member properties that one creates for dimensions within PPS are exposed to MR for reporting purposes. This means that if you require them for your financial reporting make sure that they are full dimensions. I got caught on this with my entities. I created a member property called Country but could not use this in MR. I recreated the model and made the Country a full dimension. It is now available in MR for reporting on.

Performance

Performance is something that Microsoft are addressing as an issue from my side. MR has been re-written to pull from PPS and it uses MDX to bring back the data from the model cubes. For some reason the performance is not up to scratch. We are hoping for a solution shortly.

Report Generation

The current version only allows report generation one at a time. You cannot select multiple reports and run them simultaneously. You also cannot run them unattended on a schedule. Microsoft are not sure when this type of functionality will be included.

Multiple Reports on the Same Page

It is not possible to create a number of reports and have them co-exist on the same page. This is something that all Management Accountants will curse.

Conclusion

All in all this product looks neat and should just about cover most of the reports required by the Accountants.

I will teaching some Accountants how to use the product over the next few weeks and will report back on that exercise.

- Paul Steynberg

Why I started this Blog?

Over the past few years I have noticed that the divide between IT and Finance is very rarely bridged in many organizations and in most cases they are pushed further apart by poor communication and misunderstanding. This blog will hopefully be a good read for both Financial and IT people and will primarily focus on Microsoft based products. Although my experiences are broad based and should apply to most Financial packages, my technical skills encompass MSSQL Server, MS Analysis Server, MS PerformancePoint Server, Windows Server, Excel etc (Show me an accountant who doesn’t love Excel!).

I have been through 2 implementations of ERP Systems and am embarking on my third which will be Dynamics AX 2009 (AX 5.0). I have worked on both sides of the fence, as client and consultant, and have managed my employers ERP Systems for the past 8 years.

During my time here I have also been fortunate enough to manage the Database Design and Development Team. This position brought about an incredible learning curve for me from both a technical and managerial aspect.

Many lessons have been learned and I hope to share them on this blog.

- Paul Steynberg