Hello,
We are using SQL Server as our database server. We've built a consumer product and are looking to build a dashboard that has metrics on our user base.
We're a team of two and were wondering if SSAS or if TSQL was the way to go to get metrics.
Caveats
We've used SSRS, it is very easy to spin up new reports and to even have some non-devs create new reports quickly and easily. If you're only interested in creating tables and simple charts, and need to be able to create new ones frequently, SSRS is definitely the way to go.
However, it is not nearly as flexible as an ASP.Net solution, it has some quirks, and I believe you can get a much more polished site using ASP.Net. If you only want a single report on an existing site, ASP.Net may be the way to go.
Take a look at the Microsoft SQL Server 2008 Report Builder 2.0 and Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007, both part of the Microsoft SQL Server 2008 Feature Pack, April 2009.
Not only can you display a dashboard using some of the recently acquired Dundas charts, but you can also use the the data-mining tools from within Excel 2007 to look for trends, relationships and forecast.
If you need to aggregate data and make it available to slice and dice it at the speed of thought, you should use SSAS. There is no comparison, in this regard, between a multidimensional database (aka a cube, or what SSAS builds) and a relational one.
You can attach a lot of things to SSAS. Microsoft's product is PerformancePoint and ProClarity, but Excel comes with a native connector. If you have SharePoint, it can natively attach to KPI's from SSAS. You can also use SSRS to present the data. With a cube, you'll be amazed at how much more you can do than just look at a few metrics. It's truly awesome and powerful stuff. Also with a cube, you can more easily use Data Mining and the like.
I have to say, though, that you'll at least need to build a mart in a star schema to build the cube off of. There are plenty of fantastic books about this, but I'd recommend the Data Warehouse Lifecycle Toolkit by Ralph Kimball. It gives a great overview of what marts can do, and why you'd use them. For picking up SSAS, there's Analysis Services Step by Step, which guides you through the process of building up a cube.
After spouting on about how awesome cubes are, I have to give this disclaimer: If all you want to know are a few metrics from a small dataset, don't worry about a cube. T-SQL is better (by the way, SSRS works arguably better with T-SQL than with SSAS). If you really want to be able to explore the data and jump across all sorts of aggregated levels quickly, then you want a cube. After that, the presentation layer is what you feel most comfortable with. T-SQL and SSAS are compatible with the same MS products.