Management Reporter/Frx Roadmap

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

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

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 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 - 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 Act II

Sometime back I requested the ability to schedule reports from PPS Management Reporter, a feature that was missing from V1. I put together a quick spec for Microsoft of what would pacify us until they put a proper full scheduler into future releases. This is what I requested as a minimum:

The command – ManagementReporter.exe

Flags/Parameters

-Generate (action to be done)
-Connection:DefaultConnection (connection created to the MR database.)
-Logon:”Mr Price Group” (The entity that you wish to log into)
-BuildingBlockGroup:Default (building block within logged on install that you wish to use)
-Report: “\Mr Price\SSMP1 Income Statement with PMO Monthly” (The report name and the subdirectory stored in)
-Output:”Management Reporter” (Could be “Management Reporter” or “Reporting Definition Language” or “Printer”

So what did we get? In short we get to use the same ReportGenerator.exe that is in the program files directory. We also get 3 of the 6 switches/parameters requested. These are:

-Generate
-BuildingBlockGroup
-Report

The report flag does not however require any sub directory as although the reports are stored in directories they MUST be unique in name.

Here is an example of the command syntax.

ReportDesigner.exe -Generate -BuildingBlockGroup:Default -Report:"SSAS4 Expenses Actual vs Budget Monthly"

We are extremely grateful for what we have received but it does come with some prerequisites as the Report Designer is actually opened and then closed once the report has completed.

1. Remember to tick the "save my credentials" box on the logon screen for Report Designer on the machine that you are going to run the scheduled reports. Also make sure that the appropriate default connection and entity are selected. The scheduled reports will use these credentials, connection and entity.
2. Any reports run should not have "Select units at run time" ticked on the Settings Tab under "Reporting unit selection".
3. You can only output to Management Reporter and not to file or RDL. Even if you select these they report will only be sent to the Report Viewer.
4. You cannot run more than one report at a time. If one report is running any subsequent attempt will realize that the Report Designer is already open and report a success message even though it has done nothing.

A list of error messages that you can encounter are:

0=Success
1=UnknownBuildingBlockGroup
2=UnknownReport
3=ReportViewerExportError
4=ReportFailedToLoad
5=ReportAlreadyGenerating
6=ReportingServicesGenerateError
7=UseRowDefinitionFromTree
8=ReportDesignerBadRepositoryPermissions
9=UnknownRepositoryError
10=FailedToConnectToReportingServices
11=BeforeGenerateValidationError
12=AcceptSecurityWarningError
13=ValidateDataError
14=BuildingBlocksNotValidated
15=UnableToCreateTree
16=CompanyCredentials
17=EnsureCompleteCompanyCredentialsFailed
18=EngineException
19=FailedToOpenReport
255=UnknownError

Reports can be run from the command line, task scheduler and from SQL Server Agent. Although this is not comparable to the Frx Reporter Scheduling it is a step in the right direction.

- Paul Steynberg

Management Reporter - Some Feedback

Thanks to Jill Carter from the Management Report Team for a prompt reply to my questions posed to them yesterday. Refer to my last post.

It seems that the team tried to "group" the entities together and fire of fewer queries but found that is was slower. Who would have thought?

There are apparently some improvements on SQL2008 which we can look forward to. I have also suggested that they multi-thread the queries but at this point it cannot be done as a hotfix as it my destabilize the product. Watch this space.

- 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

Management Reporter Live Recording

Hot off the press! Live Meeting recording from Microsoft about Performancepoint Server Management Reporter.

https://www107.livemeeting.com/cc/msevents/view?id=BI27AL&role=attend&pw=EDK389

On my way to check it out. Also MS are about to give me a QFE for the performance issue. Will feedback on that as soon as I have results.

- 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