current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 10 down vote favorite

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.

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

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.

share|improve this answer
3  
In addition, once the cubes are there you may come up with ways to use them. Data Warehouses are there for the questions that are not yet known - the ones that are known are something a simple query can handle. I definitely would make a prototype based on OLAP cubes and then present that to the stakeholders and explain the additional flexibility. –  TomTom Jun 14 '13 at 9:37
1  
I suspect the first option (with SSAS and cubes) may already be in place for the retail chain analysts. In retail they usually do the data mining stuff, but not deliver it to the end customers, yet. PS: You can read a short review about some working BI controls for web apps (in ASP.NET) in my SO answer. –  Marian Jun 14 '13 at 9:49
    
VERY Likely - that they already have some cubes. –  TomTom Jun 14 '13 at 12:42
add comment
up vote 7 down vote

SSAS is a very meaty topic. Almost none of what you know about the database engine can be applied to Analysis Services. If the only goal would be to provide a back-end for this report, then getting up to speed on Analysis Services and implementing the OLAP database would be a pretty substantial overhead compared to a more conventional approach of periodically refreshing some summary data stored in a relational database, or creating a Reporting Services report that runs from a periodically generated execution snapshot.

That said, if you genuinely have a long-term need for some of the strengths of Analysis Services, such as ad-hoc multidimensional reporting and MDX expressions (you can do some pretty cool stuff), and you're working with a very large data warehouse that allows it to significantly outperform a relational database, then it could be worth learning it. Don't expect to pick it up in a day, however.

share|improve this answer
add comment
up vote 3 down vote

Yes this is a very reasonable solution. I've got clients who have SSAS with similar load and it works fine. Like any database design the performance you get will be directly related to how good the cube design is.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.