The Financial System Manager's Toolbox (Part II)

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

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

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

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

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

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

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

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

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

- Paul Steynberg

Negotiating in the IT World

Purchasing software is quite an ordeal, especially ERP software. Here are a few tips and notes from some very recent first hand experience.

1. ERP software vendors are no better than second hand car salesmen. Listen well but make sure that you get any promises in writing or at least confirmation via e-mail. This has just saved me $45,000 and certain embarrassment. During the negotiation period they will promise everything and often state that "that functionality is standard". Be certain to check everything as often things don't add up when you get to sign the contract.
2. The RFP and negotiating always takes months. Find out when each of the ERP vendor's financial year end is. Aim to sign the final deal within a few weeks of this date. They will throw in the CEO's grandmother for free, if it means making target.
3. Plan your capacity with care. It often does not pay to purchase double the required licenses now at a great discounted price if you are not going to hit the numbers for quite some time.
4. A large portion of a major ERP project requires no software so do not buy the software up front as it will sit on your desk starring at you while your annual license fees for a gazillion concurrent users remains unused. So when you do go live you suddenly find yourself having to pay the annual fee again a few days later. Use a test environment until you really have to install the live one.
5. When one of the ERP vendors comes to visit during the negotiation stages always subtly remind them that this is a competition. For example when Oracle comes to visit make sure the pen you remove from your top pocket to make notes is from SAP. Don't show the SAP people out of the lobby with the Oracle bunch waiting for you. This may sound effective but they all know each other and you don't want them to start talking to each other.

Above all else, as mentioned in one of my other blogs, DO NOT believe all the white papers. Do your homework diligently and if you are offered site visits try and do them without vendor presence as you can really get some honest answers.

- 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 - Fixed or Not Fixed, That is the Question

For those of you following my Management Reporter performance tragedy here is Act II. I received my QFE . Now it sounds ironic that I would be experiencing a performance issue with something called PerformancePoint, but what is more ironic is that the fix did not really fix the problem. I have seen a marked improvement in the report generation times BUT it is still not good enough.

Most of the reports halved in the time taken to run. A report with a large reporting tree did not return in the previous version (canned after 8 hours) but took 23 minutes in the "fixed" version. So the gains are excellent but how does this stack up to the data. Not well, the 23 minute report only needed to fetch 3310 rows of data from the underlying PPS cube. I ran the report a number of times in succession, hoping that the trick of "warming" the cache might be my salvation. Performance increases were seen and this report took 12 minutes to run. So from not being rendered at all to just 12 minutes means that the people at Microsoft did some serious work. Now from a technical perspective one has to admire the effort. From an end users perspective I still say "toooooooo sloooooooowwwww".

Surely it cannot take 12 minutes to return 3310 rows of data from a technology such as OLAP which was designed for fast and efficient retrieval. So what is the problem? In order to make sense of this I profiled the cube being connected to by MR and what I found is quite interesting.

Reports are made up of a combination of rows, columns and then reporting trees. It seems that for every row in the report an MDX query is built to return the required data and then fired off to the server for each entity in the entity reporting tree. Here is an example of the MDX query. I have replaced the parameters with the actual member names. They follow the query in the profiler in XML format. Now take note of the red code which is the entity reference.

WITH
-- Column Sets and Members
-- Columns General: Define a set and calculated member for each unique filter criteria across all columns.
SET [Criteria480369GLScenarioCriteria] AS
{
([Scenario].[All Members].[Actual])
}
SET [Criteria480370GLTimeDataViewCriteria] AS
{
([TimeDataView].[All Members].[YTD])
}
SET [Criteria480371GLTimeCriteria] AS
{ [Time].[Financial Calendar].&[772007013] }
MEMBER [Time].[Financial Calendar].[Criteria480371GLTimeCriteria] AS SUM ([Criteria480371GLTimeCriteria])
SET [Criteria480372GLCurrencyCriteria] AS
{
([Currency].[All Members].[ZAR])
}
SET [Criteria480377GLScenarioCriteria] AS
{
([Scenario].[All Members].[Budget])
}
SET [Criteria480378GLTimeCriteria] AS
{ [Time].[Financial Calendar].&[772008025] }
MEMBER [Time].[Financial Calendar].[Criteria480378GLTimeCriteria] AS SUM ([Criteria480378GLTimeCriteria])
-- Column46363
SET [Column46363] AS
{
( Criteria480369GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480371GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Column46364
SET [Column46364] AS
{
( Criteria480377GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480378GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Column46365
SET [Column46365] AS
{
( Criteria480369GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480378GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Row Sets and Members
MEMBER [ValueMember] AS [Measures].[Value]
MEMBER [ValueMemberWithSignReversal] AS [Measures].[Value] * IIF([Account].[MemberId].CurrentMember.Properties("DebitCreditMemberId") = "Credit", -1, 1), NON_EMPTY_BEHAVIOR=[Measures].[Value]
-- Row 61640
MEMBER [61640] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[7XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA46])
)
-- Main Select Statement
SELECT
{ [Column46363], [Column46364], [Column46365] }
PROPERTIES MEMBER_CAPTION ON COLUMNS,
NON EMPTY
{ [61640] }
PROPERTIES MEMBER_CAPTION ON ROWS
FROM [FinancialBudgets]

This code returns 1 row of data.

So if you have 60 entities in your Reporting Tree Definition this code is going to run exactly as it is 60 times with only the row number and entity changing. So why not modify the MDX and send only 1 query through per line and modify the row set to include ALL entities? I tested my theory and modified the code with 3 additional entities. The run time was the same but it returned 3 rows of data.

Here is the part that is amended:

-- Row 38186
MEMBER [38186] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA62])
)
MEMBER [38187] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA63])
)
MEMBER [38188] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA64])
)
-- Main Select Statement
SELECT
{ [Column19387], [Column19388], [Column19389] }
PROPERTIES MEMBER_CAPTION ON COLUMNS,
NON EMPTY
{ [38186],[38187],[38188] }
PROPERTIES MEMBER_CAPTION ON ROWS
FROM [FinancialBudgets]

I am curious as to why MS has elected to fire 1 MDX query per report row for each entity and have put the question to them.

I did notice one additional feature with the hotfix. The "number of records processed" counter on the "Generate Report" screen now shows the count in 100 increments as the rows are retrieved. In the previous version you would have to wait until the end of the report for the total only so you had no idea of the progress.

I will report back once I have more information from Microsoft.

- Paul Steynberg