I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases (current month, previous month, year-to-date) over about 50 categories.
Data will be updated once every day.
I am thinking of putting up a SQL Server 2012 based OLAP cube and letting the website query this cube directly, leveraging features like proactive caching. However, being a developer at heart, I have next to no experience with the analysis services parts of SQL Server, so am quite concerned about the performance of this solution.
Does connecting a web site directly to an OLAP cube sound like a feasible solution? Do such systems react to the load from multiple users roughly like a SQL Server, making this a reasonable solution, or do they act completely differently?
I don't expect users to check their status very often and I will of course be using caching on the webserver etc.
Best Answer
You could do this with an OLAP system - some of the benefits of SSAS for this type of application include:
SSAS can readily scale out - especially as this is a read-only application with no requirements for cube writeback.
Aggregations can be tuned to minimise the I/O allowing the cubes to be tuned for efficiency.
OLAP client software and third party controls (web and rich client) are readily available from a number of vendors.
SQL Server 2012 Business Intelligence edition has pretty much all of the scalability features for SSAS, so it can be used as a cost-effective platform to front cubes for a SQL Server enterprise edition (or third party) database. Note that licensing may be an issue for this as B.I. edition is CAL-only.
SSAS has a data mining function that could be used to do a shopping basket analysis on the data and feed a 'suggested purchases' feature on the website.
On the other hand, the requirement is to show a relatively constrained data set, so the ad-hoc slice-and-dice capability of an OLAP server may be overkill, both in cost of software and cost of the hardware infrastructure to run it (SSAS is quite resource hungry). You could probably achieve your immediate requirement with a periodically refreshed summary database, and do it with less hardware and licensing costs.
From a first glance, I would suggest OLAP is probably not necessary to fulfil your existing requirement. However, it could certainly be done this way and you might get some mileage from the data mining features to provide a 'suggested purchases' feature.