CODEWITHSUNDEEP
sql-serverdatabasedata-warehouse
Man-with-a-e
Man-with-a-e

Reputation: 87

Need for OLAP cubes if we can Build views based directly off the RAW table

Assume that the table in the source data is is clean and in a state where they can be used directly.

I am trying to understand whether building views based off the RAW table is better than creating cubes. To make the VIEWS dynamic, we can have .NET application which would take paramteres for the view and execute a View with Parameters and get the data for Reporting and analysis.

If I want to view the Sales of a Product for United states in the Month of Februaray. So, I can create a view joining Product, Customer get the sales for a particular day in the month of February.

Instead of forming a Star Schema with Product, Date, Customer dimension. I am really trying to understand what is the standarad a company should go with.

I have folks telling Cubes are only good for analysis not good for reporting . Whatever information we want we can get it by creating a DYNAMIC Views

Any advise or ideas on this ?

Thanks!!

Upvotes: 1

Views: 2266

Answers (3)

paras_doshi
paras_doshi

Reputation: 997

Building views can be an alternative for small datasets. You could consider going that route BUT: 1) once the reports are taking a lot of time to load 2) It slows the transactional systems Then you'll have to consider cubes.

Upvotes: 0

Rich.Carpenter
Rich.Carpenter

Reputation: 1056

From my limited experience with Microsoft's Analysis Services, I would agree with Norla. If the execution time of the view is reasonable, that would be the way to go. Cubes can certainly be reported against, as SQL Reporting Services accommodates them fairly well, but the development process can often be much more involved when using a cube as your data source.

Upvotes: 1

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

As the name suggest SSAS (SQL Server Analysis Services) is indeed built for analysis. The reason for this is the highly normalized table structure (e.g., the star schema) which allows for super efficient indexing combined with the pre-processing of aggregated values.

Views are a great way to take data that already exists within your OLTP (as compared to OLAP) database and transform it in a manner that better fits your querying needs. This works in the same manner as "get" stored procedures.

Now for my opinion:

If you have a small amount of data (relative to the power of your server, as well as many other factors) and you're not performing intense aggregations of the data, consider using stored procedures to access your database. You can specify the parameter in .NET like any other function, making this method super easy.

If you have a lot of data (like, over 100 million rows), consider creating a cube. This will allow your queries to fly. There's a lot more work that goes into these, but the speed payoff is huge.

End note:

If the data in your reports is pretty similar to the data you already have in your database (including JOINing the tables) and you have under half a billion rows, just use a stored proc, and look into using SSRS (or not). If you have a ton of data that needs to be aggregated and transformed, look into SSAS OLAP cubes.

Upvotes: 3

Related Questions