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

Question

Hello,

 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.

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!!

Answer 1

Hi,

i recommend to create cube rahter then dynamic views.because cubes are fast, scaleable and rich as compare to dynamic views. with cubes you can create adhoc reports and custom based reports as per your requirments, which are easily maintainable.

you can slice(parameter) and dice(attribute selection)  data as per your report requirment. basically we use reports for analysis purpose like sales ,transaction report etc which are the analysis of data.

Kind regards,

Zaim Raza.

Answer 2

Thanks. I think that Cubes are used for Analysis where you can answer " why this happened". Reports is just seeing the sales no for that moment of time.

Cubes would allow us to Drill down and find out the reason why it happened by digging into. 

Answer 3

With cubes, you are defining a scope of information provided to the end user, and letting him decide on the look and filtering of the said information. While in theory you could do something similar developing application and using dynamic sets/queries, I dont see the point. it would involve alot of development time for something that is provided by existing tools.

And thats if you ignore performance considerations, where cubes (olap) should be much more performant for your examples.

Marko Culo

Answer 4

I would actually recommend Stored Procedures over Views for taking paramaters and generating reporting information but either way should work for you are trying to do.

Views and SPs will in general be a very quick solution to develop compared to a cube (especially if you've never developed a cube before). However, a cube gives you a much more powerful and robust tool to develop reports against or do analysis - if you need it. Whoever is telling you that cubes are only good for analysis is completely wrong.

I would say that if your reporting requirements are fairly simple at the start, keep the development simple and start with Views/SPs to do your reporting. Unless you already have a .NET app, why not use SSRS and let your users enter they're paramaters and generate reports/data from there?

Answer 5

The key is to remember the difference between the way the aggregations are performed. A OLAP cube allow you to peform better aggregartion and reporting becuase its engine to tuned to handle processing of information in such a way. A relational database esp if its normalized is not optimized to handle these aggregations smoothly.

So , you can create a view and then explose it vies procedures but conisder this :-

Wont your design get more complicated it future releases.

What is a user want to aggregate for a scenrio you havent planned for.

What about data quality checks ( no etl since your querying off the source table hence no data quality checks)

Some day the app might need to service hundreds of users querying thousands of rows conncurrently , imagine how a simple SUM() or count() + Group by is going to behave now - eps since its not doing the aggregations before hand and need to be run everytime for each query and chage in input vairable.

Answer 6

In short the View/SPs are good for small scale reporting and less set of users . Also where aggregations are not that complex. Cubes would be better off if we have complex aggregations and if performance is considered along with large set of users.

Thank you all for the insightful points.