An Accountants views on Financial Systems and the Microsoft BI that goes along with them.
Sunday, 22 June 2008
The Financial System Manager's Toolbox (Part II)
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
Friday, 13 June 2008
Negotiating in the IT World
- 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.
- 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.
- 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.
- 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.
- 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
Thursday, 5 June 2008
Management Reporter - Scheduling Reports Act II
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.
- 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.
- Any reports run should not have "Select units at run time" ticked on the Settings Tab under "Reporting unit selection".
- 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.
- 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
Wednesday, 4 June 2008
Management Reporter - Some Feedback
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
Monday, 2 June 2008
Management Reporter - Fixed or Not Fixed, That is the Question
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]
-- 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]