Imagine having an Excel spreadsheet with a million rows representing different points in time, every day you filled in a number to represent the amount of money you made that day–this is how a lot of OLAP cubes are built.
If all I want is to do ad hoc analysis through something like a PivotTable, this structure isn’t too bad because I know not to expect data in certain areas as I do my exploration.
But what about when I want to just get the “current” value (a common case for performance management and scorecarding)? This means looking through the spreadsheet and finding the last empty cell.
This is the idea that’s used in Business Scorecard Manager and it’s optimized for the scenario where the IT department built a cube for PivotTables that they don’t want to change and business users need to be able to build scorecards and KPIs off that same data source.
To address this, in the Business Scorecard Builder there’s UI in the “Data Mappings” pane when you edit a KPI’s actual or target to specify the parameters for finding the “current” value based on the last non-empty cell in some area of the cube.
You can pick the time dimension you want to look into, the level at which you want to look (year, quarter, month, week, day, etc.) and the “lag” from the last non-empty that you want to default to.
1) Iâ€™m looking at the “month” level in a cube and
2) it has numbers up to July 27, 2006 and
3) every day has $1 in sales, and
4) I have a lag of 0
I’ll return $27, which is a month-to-date number for July.
If I set my lag to 1, then Iâ€™m looking at the previous month’s total (June sales), which is $30,
So here are a couple of settings you might want to use:
Current lag: 0 // month-to-date number
Current lag: 1, Previous lag: 2 // compares last completed month with prior completed month
Current lag: 1, Previous lag: 12 // compares last completed month with prior period last year as previous