Financial Systems and Business Intelligence

iXBRL - Don't Panic

paul.steynberg@yahoo.com (Paul Steynberg) — Mon, 12 Oct 2009 14:25:00 +0000

Introduction

Companies in the UK will have to submit financials to Her Majesty’s Revenue and Customs (HMRC) via an online process within the next 2 years. I had a session with Ernst & Young about the language to be used called XBRL. I have also attended a workshop at HMRC titled “Company Tax Returns and Online Filing”. This is a summary of my findings and my personal views on the impact.

Overview of XBRL

XBRL stands for eXtensible Business Reporting Language. It is one of a family of "XML" languages which is becoming a standard means of communicating information between businesses and on the internet. (1). iXBRL is a slight derivative of this language which stands for InLine eXtensible Business Reporting Language and allows the data to be read by computers and viewed by humans all from the same file.

Impact

All companies filing tax after 31^st March 2011 will have to comply with the requirements of submitting all the data online. This will affect 3 submissions as follows:

CT600 – Currently can be submitted via XML and will not change.
Accounts – Must be submitted online in iXBRL format.
Tax Computations – Must be submitted online in iXBRL format.

What will not change is WHAT you submit, WHO is responsible for submitting it and WHEN it must be submitted, only HOW you submit it will change.

4 Taxonomies will be supported by HMRC but for the first 2 years of implementation only a limited subset of each taxonomy will be required.

Taxonomy	Full Tag Set	Limited Tag Set
UK GAAP	4375	1182
UK IFRS	3400	<1600
UK Common Data	900	900
CT Computational	4548	1350

One is not restricted to the limited tag set but you are only required to at least submit these for the first 2 years. Thereafter the full tag set will be required.

The current versions of the taxonomies can be downloaded from this website.( http://www.xbrl.org/uk/Taxonomies/)

The Companies House and HMRC issued a joint statement on 1^st September 2009 stating that they are working together and that both their filing services are being aligned so that a single point of filing can be used.

Software

As with any technology solution we will find many ways of solving this challenge ranging from ERP Supplier based solutions right through to stand-alone file converters. Unfortunately iXBRL is not yet widely used so I have not yet managed to find anything worth taking a look at yet. HMRC is busy reviewing a number of products and certifying them but when pushed they did not want to yield any names or costs just in case they were seen to favour one supplier over another. They did assure us that a number of suppliers would be ready for market in Q1 of 2010.

Here is a list of companies who I believe are working on a solution. Do not take this as gospel, just through my googling.

www.savanet.net

www.corefiling.com

www.semansys.com

www.rivetsoftware.com

www.ibs.nl

www.edgarfilings.com

www.allocationsolutions.com

www.justsystems.com

Approach

Awareness of XBRL and its impact is at an acceptable level within the UK market. Based on the Q&A session with HMRC the first 2 years are really going to be a phased/teething process and companies will not be penalised or prosecuted if they don’t get it quite right. We also do not have any products to work with as they are still being developed.

I would suggest a measured approach to the online filing which starts with Finance obtaining the taxonomies that they intend to use for filing and to start manually mapping the figures across from the financials. This in my opinion is going to be the largest portion of the work required. A deadline of the mapping tables by end Q2 2010 should be achievable.

Get your IT Department to keep tabs on the development of the various alternatives in the software space and then organize demonstrations and evaluations as and when possible. I estimate that we should be able to find something by Q2 2010. Based on all the input I would at this point suggest that we look to purchasing an Office Add-In that will allow you to continue with our current method of producing the financials and thus reduce impact on the business.

Works Cited

1. XBRL International. What is XBRL. [Online] [Cited: October 9, 2009.] http://www.xbrl.org/WhatIsXBRL/.

- Paul Steynberg

Solvency II - Data Requirements

paul.steynberg@yahoo.com (Paul Steynberg) — Thu, 08 Oct 2009 09:17:00 +0000

Overview of Solvency II

Solvency II is a piece of legislation (directive to be exact) adopted by the European Parliament on 22nd April 2009 and is a fundamental review of the capital adequacy regime for European insurers and reinsurers. Planned effective date is October 2012 and it aims to establish a revised set of EU-wide capital requirements, valuation techniques and risk management standards that will replace the current Solvency I requirements. (2).

The full text of this legislation can be found here. Powers have been granted to CEIOPS in order to produce consulting papers and to engage with the industry in order ensure uniformity and clarity.

IT Impact

A section of the proposed framework deals with Standards of Data Quality which is outlined in a consultation paper from CEIOPS, referred to as CP43. (3). Data, according to this paper, is used to refer to all the information which is directly or indirectly needed in order to carry out a valuation of technical provisions, in particular enabling the use of appropriate actuarial and statistical methodologies, in line with the underlying (re)insurance obligations, undertaking’s specificities and with the principle of proportionality. In the context of this Paper, data comprises numerical, census or classification information but not qualitative information. Assumptions are not regarded as data, but it is noted that the use of data is an important basis in the development of actuarial assumptions.

Whereas this Paper is focused on setting out advice in the context of a valuation of technical provisions, it is noted that the issue of data quality is also relevant in other areas of a solvency assessment, for example for the calculation of the Solvency Capital Requirement (SCR) using the standard formula or internal models.

From the materials I have been reading and through a breakfast (1) I attended here are some issues that I would like to flag up. The CP papers expand on the concept that data should be Accurate, Complete and Appropriate. In doing so they highlight the following potential gaps.

Data Governance

Most IT policies focus on security. Substantial changes to policies will be required in order to ensure that we focus on data quality.
As a group you will need to understand what data quality means within your context and how to measure it. This also requires documenting.
Clear responsibility versus ownership of the data is required which also requires documenting. In my view this would be split between the business units and IT.
You need to start looking at the technologies/tools required within your Group in order to ensure data quality.

Monitoring

The subtleties of the text indicate a focus on both transactional and non transactional data.
Both regular and adhoc types of data will require a degree of monitoring and appropriate controls in place to deal with both types.
A move to focusing away from the accuracy of the data but rather its relevance and content is required.

Documentation

Additional documentation required with regular updating.
Decisions to data quality deviations now require specific documenting and approvals.

In order to ensure accuracy, any data deficiencies should be rectified, with each adjustment justified and documented and should not overwrite the raw data. It was also hinted that a more detailed review of the data is required to ensure that it is valid and appropriate. In other words the fact that data made it from your store system through a multitude of layers to a warehouse does not absolve you from the responsibility of ensuring that the original data was correct. This, in reality, leads me to believe that data profiling and review of individual pieces of data with appropriate monitoring tools is required. This applies to both external and internal data.

Addressing the issue of data quality will go to the core of your IT department and if the hype is to be believed will require substantial investment in ensuring that your processes and tools are up to the task. This will have an impact on your IT strategy both from a technology and approach view.

Audit Impact

Article 46 of the Directive Consolidated Text referred to as Insurance and reinsurance (Solvency II)(recast) defines the responsibility of the Internal Auditors (IA) within the scope of Solvency II. (4). This article requires the usual stance of being independent and specifically requires that IA provide an effective internal audit function which includes an evaluation of the adequacy and effectiveness of the internal control system and other elements of the governance system. (This gets a bit circular as the resolution passed under (18a) says that the governance system includes the risk management function, the compliance function, the internal audit function and the actuarial function, which implies that they audit the same system that they are part off).

CP33 from CEIOPS deals with the advice on Governance and makes reference to the functions of Internal Audit. (5).

Although the requirements do not appear to be any different from any IA business as usual functions they are advised to submit a written report on its findings to the administrative or management body at least annually. It makes sense then to assume that IA have a full understanding of the Solvency II requirements.

Conclusions

Solvency II is going to have quite an impact on the way we do business as an Insurance Company and is something that should be driven from the highest possible position in the company as it will require substantial resourcing. This initiative should not be driven from within one department.

Works Cited

1. Addressing the data and technology challenges of Solvency II. PriceWaterhouseCoopers. London : s.n., 2009.

2. Financial Services Authority. Insurance Risk Management: The Path to Solvency II. s.l. : Financial Services Authority, 2008.

3. CEIOPS. Consultation Paper No.43 - Technical Provisions - Article 85 f Standards for Data Quality. Frankfurt : CEIOPS, 2009.

4. European Parliment. Insurance and reinsurance (Solvency II) (recast). Strasbourg : s.n., 2009.

5. CEIOPS. Consultation Paper No.33 - Draft CEIOPS Advice for Level 2 Implementing Measures on Solvency II: System of Governance. Frankfurt : s.n., 2009.

Who Still Trusts the Gorilla?

paul.steynberg@yahoo.com (Paul Steynberg) — Thu, 01 Oct 2009 12:01:00 +0000

Where does a 500 pound gorilla sleep? Anywhere it wants. Enter left stage - Microsoft.

At the beginning of the year Microsoft canned development on PerformancePoint Server Planning and laid off a whole pile of people. Some of them by e-mail, I know, one of them was on site with me in the UK when he got the news via an e-mail. To me this was a pivotal moment in our relationship with Microsoft. A year ago nobody questioned Microsoft's commitment to software development and products. Today a very different story. A few weeks ago I was discussing the way forward in our ETL architecture with my current employer and we had 2 roads, either IBM DataStage or Microsoft SSIS. The most senior person in the room posed this question "Are we sure that Microsoft SSIS will still be around in the future given the demise of PPS?". A year ago that question would have elicited chuckles from the boardroom table as an obvious joke. Not anymore, it was a serious question which required follow up.

I don't think that Microsoft has any idea how much damage they have done to their reputation in the market.

What do you think? If you have time please complete my on-line quick poll on this.

- Paul Steynberg

Gartner on Mid Market ERP Systems

paul.steynberg@yahoo.com (Paul Steynberg) — Mon, 28 Sep 2009 14:56:00 +0000

Those of you who have followed my blog will know that prior to me leaving my South African employer I implemented Dynamics AX 2009 (Axapta 5.0). The project was a raging success and done within budget for considerably less than the alternatives on the list. It is always a good thing to have your actions supported in the future and I came across this Gartner report which put Dynamics AX in the top right hand quadrant of the famous Gartner Magic Quadrant.

This report is definitely worth a read and was published in June 2009.

- Paul Steynberg

Moving to the UK

paul.steynberg@yahoo.com (Paul Steynberg) — Sat, 26 Sep 2009 05:43:00 +0000

This post is a personal one to share my experiences with anyone who is looking to move to the UK.

When you move to the UK start off by renting at first. This may sound easy but in reality is fraught with potholes and pitfalls. Nothing is going to be a substitute for old fashioned research when it comes to starting out in a new country but here are some tips that I wish someone had given to me before we moved. Firstly if you have a job lined up find out where the offices are and get the UK postal code. Now this is worth an aside. The UK postal system is amazing (when you come from Africa) and you live and die by your postal code. Once you have this tit-bit of information you are set. Once you have code go to www.maps.google.co.uk and search on it. You can even use Google Street View to check out the surroundings. You would not want to commute more than 1 hour door to door each way. Google maps will show you both tube and overland train stations nearby. The underground (tube) works quite well but I personally do not like using them. If you click on an underground station Google maps will highlight the tube lines which are different colours. Go to www.tfl.gov.uk for more info on the tube. TFL (Transport for London) has an excellent journey planner. The next trick is to back track from your work location a travel route that is around 50 minutes. (Gives you 10 minutes to and from the stations). Depending on where you work you should start with one of the main train stations such as Waterloo, St Pancras or Liverpool Street etc. By using a combination of TFL, www.thetrainline.com or www.nationalrail.co.uk you can then locate stations outwards from that point. I work in the Insurance district close to the Lloyds building and use either London Bridge Station or Bank to get to Waterloo and then take a 30 minute train out to the suburbs in Surrey. Monthly cost for my ticket which combines all London zones and out to my station is £169.

Once you have a list of stations you can then start to look for property. I personally like www.rightmove.co.uk as you can put in the station name, give a radius in miles, number of rooms and maximum rental. Another good site is www.primelocation.com but in reality they both have the same properties listed.

Most rental agreements are 12 months with an opt out after 6 months. A 1.5 month deposit is also standard. Most letting agents will also ensure that the gas, electricity and water is put into your name before you arrive. You can bank on around £100-£120 average per month on Gas and Electricity if you have a moderate 3 bedroom house. Something else to remember, when you come from a place like South Africa a moderate house in London is more like a shack, so you will have to realign your expectations. You can save on utilities by getting some tips from www.moneysavingexpert.com. This website also has advice on a range of other items that you can save money on. Also try www.energyhelpline.com, www.uswitch.com, www.moneysupermarket.com or www.comparethemarket.com. You can compare insurance, mortgages, banks, phone, cars and the list goes on.

Your National Insurance Number is also very important for your employer to deduct the correct taxes from you. Get this as soon as possible by calling the Job Centre Plus offices (www.jobcentreplus.gov.uk) or read here for more info. They will mail you a form, you fill it out and hey presto they send you an NI number. (Have I mentioned how great the postal system is yet?).

Opening bank accounts can be tricky until you get your first utility bill, which could be up to 2 months from when you move in as they bill in arrears. The banks insist on a utility bill but you can get by this by requesting a letter from your employer confirming that you have moved from XXXX and are now residing at YYYY based on the employment records. Back this up with a copy of the lease and correspondence with the lease company. If you are married or have a partner may I suggest that you put all utilities in BOTH names so that both of you can use it for opening bank accounts and proving place of residence etc.

Getting around within London is easy using the transportation system. If you move out to the suburbs and like to get out a bit you may want to purchase a car. Try www.pistonheads.co.uk or www.autotrader.co.uk. Cars are relatively cheap in the UK but remember to shop around for insurance. Use the comparative shopping websites to find the cheapest insurance. From what I can gather Tom-Tom is the king of SatNav's in the UK (It was Garmin in South Africa). Buy one as soon as you can, without it life will be less than pleasant. Try Argos or Amazon or just pop into any electronics store on the way past. They start at around £80 and go up to past the £400 mark. Try and get a touch screen and blue tooth to pair up with your phone for hands free talking. Running you car in the UK can get expensive. Firstly the insurance, then MOT and also road tax. Depending on your vehicle value and co2 emissions cost could vary between £500 and £1000 per annum. You have to have insurance on your vehicle, this is not an option and they get very tough on it.

Well that is all for now, I will follow this post up with more tips and tricks later on in the year. If you have children then wait for my update about schools.

- Paul Steynberg

A Novel Way of Dealing with Excel Models

paul.steynberg@yahoo.com (Paul Steynberg) — Sat, 29 Aug 2009 11:01:00 +0000

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:

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

Pigeons, Rats, Squirrels and Babies

paul.steynberg@yahoo.com (Paul Steynberg) — Tue, 14 Jul 2009 15:16:00 +0000

Well it has been quite a while since I last posted anything on my blog, and with good reason. My family and I have made a monumental change by moving from South Africa to the UK. We are now residing in London, the city of pigeons, rats and squirrels.

I have taken up a position with one of the UK's leading Insurers as the Financial Systems Manager. So pretty much exactly what I was doing back in South Africa except instead of retail I now have insurance. I have to admit it is a very interesting sector to be in and in 2 months I will be writing my London Lloyds Introductory Exam.

So back to my blog heading, where does the baby come from. Well 6 weeks before we decided to move house, hemisphere, jobs etc my wife delivered a bouncing baby boy.

So as you can see the move with a 2 year old and a 6 week old was quite hectic and something had to give, hence the lack of blogs.

We are now settled in and hopefully I can put some together in the coming weeks.

- Paul Steynberg

Management Reporter/Frx Roadmap

paul.steynberg@yahoo.com (Paul Steynberg) — Fri, 13 Mar 2009 07:50:00 +0000

The big question has finally been answered, what is the future of Management Reporter, FRx Reporter, FRx Forecaster and Enterprise Reporting? Well MS has released this roadmap.

2010 - MR to Replace FRx Reporter as the reporting tool of choice for Dynamics AX. This will coincide with the release of AX2010 (6.0) and it will be called MR V2.
2012 - MR to be released as V3 with AX2012 (7.0) and will now include Forecaster.
2014 - MR to be released as V4 with AX2014 (8.0) and will include functionality from Enterprise Reporting.

Personally I would not move from FRx Reporter until 2012 having learned a very hard lesson over the last year or so with the current version of MR.

Regards

Paul

Cannot Install Management Report Directly to SQL Server 2008

paul.steynberg@yahoo.com (Paul Steynberg) — Wed, 11 Feb 2009 09:33:00 +0000

I found a bug some time back that did not allow one to install Management Reporter while linking directly to a SQL Server 2008 database. One had to install RTM and point to a database which was SQL Server 2005 and only once SP1 for MR (SP2 for PPS) was applied could you then change the database to SQL Server 2008.

Just had confirmation from Microsoft that this will be fixed in SP2 for Management Reporter.

SP1 for MR was actually packed with SP2 for PPS so we will get this in SP3 for PPS which will be released in the Summer.

- Paul Steynberg

AIF Updated Doc for AX 2009

paul.steynberg@yahoo.com (Paul Steynberg) — Sat, 31 Jan 2009 06:40:00 +0000

The documentation surrounding the use of the Application Integration Framework (AIF) with BizTalk has been updated to now include AX 2009. Here is the link. http://www.microsoft.com/downloads/details.aspx?familyid=edc62433-5b21-4f74-b065-b075ba6dc86d&displaylang=en&tm

- Paul Steynberg

PerformancePoint Server Management Reporter and SQL 2008

paul.steynberg@yahoo.com (Paul Steynberg) — Wed, 14 Jan 2009 21:14:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Thu, 18 Dec 2008 07:46:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Fri, 12 Dec 2008 06:53:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Wed, 10 Dec 2008 06:57:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Fri, 05 Dec 2008 15:01:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Fri, 05 Dec 2008 11:48:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Thu, 04 Dec 2008 09:28:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Wed, 03 Dec 2008 12:35:00 +0000

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

paul.steynberg@yahoo.com (Paul Steynberg) — Wed, 03 Dec 2008 11:45:00 +0000

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

Off to the UK

paul.steynberg@yahoo.com (Paul Steynberg) — Fri, 21 Nov 2008 14:21:00 +0000

Some of you may know that I am relocating to the UK in the new year. The reasons are many but primarily for the personal safety of my family as South Africa is becoming quite a dangerous place to live.

So I’m off to London tomorrow for 10 days to meet with some prospects and I believe that it is pretty damn cold at the moment. Wish me luck!

- Paul Steynberg

PerformancePoint Server Planning and Management Reporter - Further Considerations

paul.steynberg@yahoo.com (Paul Steynberg) — Tue, 18 Nov 2008 14:05:00 +0000

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

Interfaces to Dynamics AX 2009

paul.steynberg@yahoo.com (Paul Steynberg) — Sat, 15 Nov 2008 14:10:00 +0000

A while back I penned about our dismal interlude into the BizTalk to Dynamics Application Interface Framework. I am led to believe that the problem was not with the AIF but with Dynamics and that it has been resolved. We however did not have any slack time in our very ambitious timetable to wait for it so had to take a different route.

I spent countless hours talking to people and browsing the Internet trying to find a robust way of creating interfaces and found absolutely nothing worth reading. Here is the route that I took.

I created a new Database specifically for interfaces into Dynamics AX. This Database is housed on the same server as our Dynamics Database. Within this database we then created a number of tables that are populated by our other systems within the organization. Based on our install I had 2 distinct types of interfaces. Invoices for Accounts Payable and Journals for the General Ledger. Most of the fields for both are shared anyway. Each system that was being integrated was assigned a "Source" code so that we would never lose track of where the data originated from. In a more complex environment I would suggest that you create a schema within the database for each source system. This will help with control and security.

Within Dynamics AX (DAX) we then created a new setup table and created a form called "External Interface Import Parameters". This table/form held the following information:

Import Type – A short identifier of the interface
Name – A full description of the external interface
External Server Name – The name of the server that housed the Interface Database being called.
Database Name – The name of the interface database mentioned above.
Journal Name – We set up a different journal name for each interface so that they could easily be identified when looking at the transactions in an account.
One Voucher Number Only – A bit switch. If set on this will create one voucher number for the entire interface run. If set off it will generate a new voucher number when it reaches a point of balance.
Stored Procedure – This is the name of the stored procedure within the database to call in order to get the data.
Write Back Table Name – The table that will be used to stamp back information once the interface has successfully run.
ODBC Source Name – We had to use ODBC connections due to the way the systems run from the client or from the batch server. (Some history here but it was necessary).
Batch Class Name – If this job was going to be called by the Batch server this is the name of the job to call.
Auto Post – If set on it will load and auto post the interface journal.

This information is then used repeatedly throughout the interfaces. Here are the steps that all interfaces go through:

An interface call is made either from a menu item by an operator or from the batch manager in DAX.
Based on the Import Parameters mentioned above the job will then call a Stored Procedure on the Interface database and will pass it the Company code as the only parameter. This is often referred to as the DataAreaId in DAX.
The Stored Procedure fires on the Server and passes back to the job all the records for the interface.
DAX then creates the Journal based on the Journal Name and will either auto post or leave it as unposted.
When complete it will the write back to the table the Journal Number and Date/Time Completed.
The batch job will keep firing for the company until the stored procedure returns no records back.

So what do the Stored Procedures do?

All records that are new have a TransactionStatus of "NEW". Those in the process of being posted are stamped "WIP" and those completed are stamped "FIN". The stored procedures finds records that are "NEW" and then selects the Top 1 record summarized by your extraction criteria into a table variable. As an example our POS Journals are summarized by Company, Trading Date, Currency and ExtractID from the LOB System. This table variable is then used to join back to the table to return the result set. As part of this procedure we also stamp the records as "WIP" with the date/time it was sent to Dynamics.

Each interface has been specifically written to meet the LOB criteria. I am busy working on a more generic system that will completely future proof the business. Once the framework is in place no additional development will be required. The only work required will be set up a record in the Import Parameters Table, set up a Journal Name, Number Sequence and just populate the interface tables.

I would be very interested to hear from other parties how they tackled this.

- Paul Steynberg

Dynamics AX Implementation Hours and Costs

paul.steynberg@yahoo.com (Paul Steynberg) — Mon, 27 Oct 2008 08:46:00 +0000

When I started this Dynamics AX 2009 implementation process I hunted the net looking for a graph detailing the costs and cash outflows for the duration of the project. I could not find one so had to rely on my past experiences and the vendor to budget. As we stand, just a few weeks away from the consultants walking off site, I predict that we will be bang on target. We should spend about 95% of our allocated budget. Having gone through this process I thought that I should share my weekly cost and hours graph with you. To read the graph is quite simple. The red line represents that weekly cumulative cost of the project relative to the total cost of the project. I have actuals to the week ending 24th October 2008 and budgeted figures to the end. The graph starts at 61% which is the total cost of the software, training for my team and half a weeks consulting time. The final costs are expressed as a percentage of the total cost. (Values excluded to protect innocent bystanders).

Cost of Software (including first years maintenance) 58.36%
Training of myself and my team 1.93%
Consultants Time 39.71%

I have not included hidden costs such as training lunches, my staff time, material printing etc etc.

The blue line represents consultants hours by week.

- Paul Steynberg

Finding Your Dynamics AX Lead Blocker

paul.steynberg@yahoo.com (Paul Steynberg) — Thu, 23 Oct 2008 15:30:00 +0000

Dynamics AX 2009 does have some issues with SQL Server locks and quite often we are finding that people cannot access journal lines due to someone else blocking them. This then just hangs the client and it becomes unresponsive. Here is a quick method of finding the offender. The spid in SQL can be traced back to the user in Dynamics AX 2009 on the online users screen which can be accessed from "Administration". Each user that is working will have a SPID or sometimes several attached to his/her user name. Now you can call the lead blocker as you have a name and ask them to shut down AX.

If you do not have a tool such as Spotlight then create a view on the Master database that will show you the blockers. Here is the code for the view:

Create View vSysProcesses as

select p.spid, case when (p.blocked <> 0) then left(convert(varchar, b.spid) + ' - ' + case when (b.nt_username = '') then rtrim(b.loginame) else rtrim(b.nt_username) end -- UserName, + ' on ' + rtrim(b.hostname) + ', using ' + rtrim(b.program_name), 80) else null end as [BlockedBy], case when (p.nt_username = '') then left(p.loginame, 15) else left(p.nt_username, 15) end as UserName, left(p.hostname, 15) as HostName, left(p.program_name, 35) as Program, left(isnull(d.name, ''), 20) as DB, left(p.cmd, 25) as Command, left(p.status, 15) as Status, p.login_time as LogInTime, p.last_batch as LastBatchTime, left(p.lastwaittype, 20) as [LastWaitType], p.Open_Tran as [OpenTran], p.cpu as [CPU], p.physical_io as [PhysicalIO], p.[memusage] as [MemUsage], p.dbid as [dbId], (((1.0 + p.cpu) / 8500.0) * ((1.0 + p.physical_io) / 1100.0) * ((1.0 + p.[memusage]) / 38.0)) / (datediff(ss, p.login_time, GetDate())) as ResourceUsageFactorfrom master.dbo.sysprocesses pjoin master.dbo.sysdatabases don p.dbid = d.dbidleft join master.dbo.sysprocesses b -- blocking on p.blocked = b.spidwhere p.spid <> @@spid and p.Status <> 'background'

Once you have created the view run this against the view and you will find the blockers:

use master

select * from dbo.vSysProcesses

order by blockedby desc

- Paul Steynberg

Spotlight on.....

paul.steynberg@yahoo.com (Paul Steynberg) — Mon, 20 Oct 2008 15:53:00 +0000

At the beginning of my blogging career I penned the Financial System Manager’s Toolbox Series. This series covered amongst other essential products the likes of Speed SQL IDE and LiteSpeed from Quest Software. I have to add another product to this list called Spotlight also by Quest Software. We have found this product to be brilliant. Although specifically purchased to monitor our SQL instances it also has a wonderful Windows Operating System Dashboard. If it were not for Spotlight I am sure that we would have taken many more hours to find our Dynamics AX 2009 issue.

Spotlight gives you a visual interface into your systems inner workings and you can always see what is going on. The ability to also track back in time to see what was happening at a point in time is priceless. Here is an example of the dashboard for a SQL instance.

Other products are capable of doing this type of monitoring but we have just found this one to be easy and it looks really great.

- Paul Steynberg