SSAS Cube vs. SQL View 1

[RileyCat]

How is a Cube/Dimension/Measure different than a SQL table View?

What are the PROS of a View over the SSAS object?

What are the PROS of the SSAS objects over a table view?

Don't I still have to write a user facing application for the user to access the data from a SSAS object?

THanks.


Stay Cool Ya'll!

-- Kristin

 

[RiverGuy]

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]

To sum it up, a relational data warehouse is really a collection of tables. You either have to get IT to write every report against it, or purchase some sort of tool where you map your columns and business logic to your datawarehouse database. Your users would utilize that tool which would translate their requests into SQL to run against the database.

With SSAS, you program your business logic and mappings directly into the multidimensional database. It allows you the flexibility of not requiring some sort of third-party metadata layers (such as Business Objects Universe). Your users can connect to it with a multitude of tools such as Excel.