February 1, 2012 at 2:52 am
Hi All,
Looking for some opinions around the viability of using MS SSAS to implement a planning and forecasting solution, with scenario analysis and variance analysis aswell.
Business problem is to plan and forecast variable X which is a function of A, B, and C - so really we need solution for taking historic data, user estimates, macro variables using this as inputs into statistical models to predict A, B, and C over month 1 to year 5 horizon. Once we have forecasted values for A, B, and C finding X is a straight forward enough and well understood calculation. As well as this basic forecasting capability we also need to be able to deliver scenario analysis capabilities where users can alter A, B, and/or C (at various levels of granularity) to see overall impact on X. There is finally then the need for variance analysis to draw out the drivers of the difference between actual X and forecasted X in terms of its inputs A, B, and C.
The technical set up here is using a single SQL Server data warehouse to source all required inputs. The reporting layer for the current MI from the DWH is all Oracle OBIEE ideally this would be same way to deliver planning and forecast reports/results and scenario and variance analysis.
I am thinking of this problem in terms of a modelling engine we need to sit on top of the DWH to power these calculations and functionality.
Given it is a SQL Server DWH and we already have availability to MS SSAS my question is around the suitability of MS SSAS for such a solution. Does this sound like something you could use MS SSAS to achieve? Would it be very complicated MS SSAS project (IT in here has not got extensive experience of MS SSAS solutions)? Or does this sound like something that would be a bit much for MS SSAS and best delivered via some third party software such as SAS or SPSS etc. (note there are a few vendors in this space with specific solutions available but they would probably be more expensive - it's financial services industry).
From what information i can find i see MS SSAS as good way to deliver basic BI in sense of slicing and dicing data - intergration for reports,excel. Have not really been able to find much information around it in terms of implementing it as a modelling engine in sense i mean above.
Any advice or thoughts much appreciated.
Thanks
Andrew
February 1, 2012 at 4:31 am
I would argue that many people leave a lot of SSAS functionality on the table's and never use it for more than simple cubes, but that's a different topic for a different day. Here is a 2010 whitepaper from Microsoft that I think covers what you're looking to do. link[/b]
Steve.
February 1, 2012 at 6:10 am
Thanks for that Steve, will read now.
When i talk about vendor solutions here is an example from SAS that would i'm sure cover all the functionality we need, but have a feeling it may almost be too much in terms of only using a bit of it functionality so not being able to justify the cost. There is a conversation to be had with SAS here i'm sure to confirm my understanding.
http://www.sas.com/industry/financial-services/banking/capital-allocation/index.html#section=1
Is there such industry specific and focused offerings out there built on MS SSAS/SSIS/SSRS i wonder....so the option of using MS SSAS would not be starting from scratch...
February 1, 2012 at 9:42 am
That document seemed to push the more 'complicated' (in the sense of the application in the white paper) calculations into stored procedures in SSMS. Would not imagine SSMS procedures as a very powerful way to do things like scenario analysis, regression, other types of modelling...Definetly get impression trying to use MS SSAS as a complicated enough modelling engine may be wrong direction. Most important consideration around problem i face is the depth and ability of the modelling and complex calculation elements, their depth, coverage, flexibility, and useability in terms of maintanence etc..
White paper was pretty good though in showing interactions between business processes and MS software in sense of straight forward enough planning applications.
very useful thanks.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply