What's the point of SQL joins if you have OLAP?

Question

I want to understand the purpose of using SQL joins from a business analysis perspective.

Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join statements if I can use a cube or tabular model, which seem omnipresent, even excel has this functionality. I can use point and click adhoc, slicers and other filtering and aggregation which if I understand correctly are a lot faster and if I want to do a very complex analys, I can write MDX queries or cubeformulas, in the excel example.

What can SQL joins can help me with?

Answer 1

You're basically talking about two entirely different technologies, used for two different purposes.

A relational database stores information, commonly in a normalized fashion which is most suited for day-to-day processing and storage of business information. Given this storage model, you need joins to.. well, join those relational tables together when you want to compile data and properties from those different sources.

OLAP cubes are not really used to store information, but rather duplicate and re-organize the information in a denormalized way so as to allow for typical analytical queries, using MDX for instance. What makes OLAP cubes so fast is that a lot of aggregates are pre-calculated, and this comes at a potentially great cost in storage.

Excel is not a realiable OLAP tool, imho - it can act as an interface to cubes, but if you try to use PowerPivot with any larger set of data, you'll very quickly find your workstation out of memory.

On a final note, I wouldn't say that cubes and tabular models are "omnipresent". I would say that they can often be found for specific end-user applications, and as such they are often stripped down to a minimum of dimensions, attributes and measures in order to avoid confusing the users. Power-users will often go directly to the relational database to get what they need.