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

Dynamics AX 2009 - Inconsistent Data When Exporting to Excel from Grid

Sometime back I listed some bugs that we had found in Dynamics AX 2009. One of the bugs was exporting large record sets from the journal lines grids into Excel. See here.

Although we have not managed to get this fixed here is a workaround until Microsoft releases the fix.

1) Go to AOT – Forms - LedgerTransAccount [this is the form that corresponds to Chart of Account Details - Transactions ]

2) Expand the node to view the datasources. Select the datasource ‘LedgerTrans’

3) Click on the ‘Properties’ icon or alternatively, press ‘Alt+Enter’, to open up the Properties window.

4) The property ‘StartPosition’ by default is marked ‘Last. This results in the cursor being at the last whenever the particular form opens up. Change this property to ‘First’ so that the cursor will always be at the top of the grid for the mentioned form

So whenever one opens this grid it will automatically navigate to the first record and will thus export correctly to Excel.

- Paul Steynberg

Back from The UK

Back at the office after a 10 day whirlwind tour of the UK. And boy was it cold. Landed in snow at Heathrow but for the most of the trip it bounced between 0 and -4 degrees C. Now for someone who barely owns a jersey this was quite something.

A big thanks to Tim and Sacha from Adatis Consulting for buying me a few beers and getting the chance to finally put faces to the names. We had some interesting discussions around the potential and direction of PerformancePoint Server and I still rate their blogs as some of the best in the business. Keep it up guys.

So it now looks quite imminent that my family and I will be relocating to the UK. Our second child will be born here at the end of February and the whole family should be settled in the UK by mid April 2009. I hope to continue contributing to the Finance/BI community but if they become a bit scarce you know why.

- Paul Steynberg