SSAS Cube vs. SQL View 1

How is a Cube/Dimension/Measure different than a SQL table View?
[red]It's like asking how is a hammer different from a chain saw. You cannot really compare those two exact things. Typically, you choose between a star schema in an RDBMS, which would hopefully consist of denormalized tables and not views. Or you go ahead and feed that star schema into SSAS which would typically employ MOLAP storage. So I think what you are really asking is how is a standard star schema data warehouse different from a multidimensional database?

Well, here are the differences as I see them:

Relational Star Schema:
Pro: Easy to get at individual transactions
Con: Can be slower than SSAS aggregations
Con: Lacks UDM/Metadata layer to aid end-users in utilizing the dimensions and measures properly.
Pro: One less thing to backup and maintain

SSAS:
Pro: Can be faster as data is aggregated
Pro: Comes with UDM/metadata layer, meaning that any tool able to connect to it doesn't have to figure out what to sum, what to count, and other aggregation types. It's basically a data-model that has your business logic built in as opposed to a collection of tables
Pro: MDX is a powerful analytical language to use against SSAS
Con: More difficult to get at transactional data.
[/red]



Don't I still have to write a user facing application for the user to access the data from a SSAS object?
[red]
No. Excel 2007 and 2010 make it easy to connect to SSAS where your users can answer questions by utilizing pivot tables and pivot charts.
[/red]

Click to expand...