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?

  • Even OLAP uses dimension tables. Do you really want to store information about every attribute of every dimension on every fact row? How do you deal with slowly changing dimensions then? – JNK Feb 2 '15 at 13:56

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.

  • Thank you for your detailed response! What I'm struggling to understand is why would a power user compile data and properties from the sources manually with queries when he can get a cube with all of the information and not bother with SQL queries. Are there some practical limitations? You mentioned storage cost, but isn't storage very cheap nowadays? I'm talking about small business, less than 200 employees. – Toranaga Feb 2 '15 at 14:35
  • 2
    For one, the datawarehouse will in all probability contain a lot more detail because you don't want to include too much detail in the cube (this may confuse end-users). Also, MDX can be quite cumbersome for most users, whereas SQL knowledge is a lot more widespread. – Daniel Hutmacher Feb 2 '15 at 14:38
  • Thank you again for your answer! I think I think I understand the landscape a bit better now. – Toranaga Feb 2 '15 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.