PerformancePoint 2007 Management Reporter was launched recently, with the specific intention of providing financial users with the ability to easily create financial reports that can be distributed across the organisation. Management Reporter can connect to PerformancePoint and there are plans to allow it to connect to ERP systems such as Dynamics AX, GP and SL, among others.
On the PerformancePoint side of things, the interesting feature to note is that you won't be able to connect to just any old PerformancePoint model - you can only connect to Financial Models that contain a a Currency Dimension. Once you are connected, its quite easy to create and publish reports. This is partly due to the excellent wizard, which essentially allows you to map your PerformancePoint account dimension members to the relevant section of a Balance Sheet or Income Statement report:
In addition, the product uses the idea of 'building blocks' in order to give users flexibility when designing reports. A building block is essentially a section of the report (e.g. the rows or columns) that you can re-use amongst multiple different reports. Therefore, if you have a common set of columns that you want to see on all your reports, then there's no need for you to manually re-create the column definitions for every report. The 'Report Designer' application contains an area where you can simply pick your row and column definitions based on building blocks that you have already created:
Management Reporter is actually based on a product called FRx, which has been around for a while and is able to connect to a lot of the major ERP systems. There are a lot of good resources on web for FRx, in particular FRxBuzz, which contains a few FRx tips and tricks that can be applied to Management Reporter.
I'm a big fan of schemas in SQL 2005, and like to use them for both organisation and security. However, I've found that PerformancePoint Planning Business Modeller doesn't appear to like schemas as much as I do....
It seems that if you try to build a dimension from a table that is not in the dbo schema, then Planning Business Modeller will throw the following error :
This will happen whether you've built your data source using the 'Database' or 'Data Source View' options in the create data source window. The same error will also appear if you try to populate a dimension using the 'Load Members' task in the Workspace Actions pane. In addition, the problem doesn't appear to be fixed by applying the recent PerformancePoint Cumulative Update, as detailed by Tim Kent here.
An obvious workaround is to create views in the dbo schema, or perhaps creating synonyms, but this could really be a hassle if you have a lot of tables/views affected. In the interest of getting this resolved, and to understand if a better workaround exists, I've posted this issue on the MS Connect site.
The issue can be found on Connect at: https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=333236&SiteID=181
I'll provide an update as this issue progresses.
Analysis Services 2005 comes with the useful Add Business Intelligence wizard, which will assist you with numerous calculations, including 12 Months to Date. However, I tend to write such calculations myself, especially having found out that the 12 Months to Date MDX generated by the Business Intelligence wizard didn't satisfy a client's requirements. Although the calculation works fine at the Month level in a Date dimension, it doesn't quite work at the day level - and it certainly doesn't take leap years into account.
The client's requirement for day level 12 Months to Date was to return an accumulated view of the last 365 days back in the calendar hierarchy. However, for leap years, the calculation should take into account the 29th of February if the current year is a leap year.
In order to get this to work, the first step is to modify the Date dimension so that it contains an attribute to signify whether the current day is affected by a leap year or not. In order to do this:
1. Add a bit column to your date dimension table called AffectedByLeapYear (or similar)
2. Run a SQL Update statement to populate your new column:
UPDATE dbo.DimTime
SET AffectedByLeapYear =
CASE
WHEN (CalendarYear % 4 = 0) AND (CalendarYear % 100 != 0 OR CalendarYear % 400 = 0)
AND DayNumberOfYear >= 60 THEN
1
ELSE
0
END 3. Refresh your data source view
4. Add a new attribute called 'Affected By Leap Year' to your date dimension:
Now we’re ready to write the MDX for 12 Months to Date. In the MDX Script, the first step is to scope on a member called [Time Analysis].[Time Analysis].&[2], which is the 12 Months to Date member in my Time Utility dimension:
Scope
(
//Scope on 12 months to date
[Time Analysis].[Time Analysis].&[2]
) ;Then we need a nested scope on the True member of the 'Affected By Leap Year' attribute. This is really important because it means we can isolate the section of the cube that is affected by the leap year. Also, by using Scope(), we can avoid complex IIf statements - everything is managed cleanly in the Scope() statement:
Scope
(
//This statement is key - we scope on the cells that we know are affected by the leap year
//This avoids a big and inefficient iif statement
[Date].[Calendar].[Date].Members,
[Date].[Affected By Leap Year].&[True]
) ;Now we're ready to perform the actual calculation. Seeing as this is for dates that are affected by a leap year, we need to aggregate a set with an extra member, meaning for any dates in a leap year beyond the 28th Feb, we will aggregate 366 days worth of data. Therefore, the following MDX statement is not too dissimilar to the kind of MDX that gets generated by the Business Intelligence wizard, aside from the fact its operating at the day level:
This = Aggregate
(
//We need to go back an extra day here
{[Time Analysis].[Time Analysis].&[1]} *
ParallelPeriod
(
[Date].[Calendar].[Date], 365, [Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
) ;We then have a similar statement for the normal dates, which are any dates prior to the 29th Feb in a leap year, or any dates not in a leap year. The difference here is that the set that get aggregated just includes 365 days worth of data. When we put all these pieces together, we get:
Scope
(
//Scope on 12 months to date
[Time Analysis].[Time Analysis].&[2]
) ;
Scope
(
//This statement is key - we scope on the cells that we know are affected by the leap year
//This avoids a big and inefficient iif statement
[Date].[Calendar].[Date].Members,
[Date].[Affected By Leap Year].&[True]
) ;
This = Aggregate
(
//We need to go back an extra day here
{[Time Analysis].[Time Analysis].&[1]} *
ParallelPeriod
(
[Date].[Calendar].[Date], 365, [Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
) ;
End Scope;
Scope
(
//Now we scope on the 'normal' unaffected dates
[Date].[Calendar].[Date].Members,
[Date].[Affected By Leap Year].&[False]
) ;
This = Aggregate
(
//We are in a normal year (or in a leap before 29th Feb), just go back the
//standard 365 days
{[Time Analysis].[Time Analysis].&[1]} *
ParallelPeriod
(
[Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
) ;
End Scope;
End Scope ;But we're not finished yet! We now need deal with the other levels in the Calendar hierarchy, namely Half Years, Quarters, Months and Years. These are all dealt with by a relatively simple statement that sets the current member of the respective hierarchy to its last child. So for a month, we pick up the 12 Months Year to Date figure for the end of the month, for the Quarter we pick up the 12 Months Year to Date for the last month in the Quarter etc etc. For example:
Scope
(
[Date].[Calendar].[Month].Members
) ;
//For months, always display data from the last day in the month
This = [Date].[Calendar].CurrentMember.LastChild;
End Scope;Now you have a 12 Months to Date calculation that works at all levels in the Calendar hierarchy. Perhaps some of this is overkill for your requirements? I have spoken with users in the past who have only requested 12 Months to Date at the Month level - which is obviously a lot simpler. If you're doing a 12 Months to Date calculation, then it almost goes without saying that you have to make it work at the Month level! But as for the other levels, such as Quarter, you may be ok just leaving these as NULL. Certainly the Time Intelligence wizard leaves Years, Half Years and Quarters out, just putting 'N/A'. But all comes down to user requirements - and as we all know - you have to keep the users happy :-)