Eu quero entender o propósito de usar junções SQL de uma perspectiva de análise de negócios.
Digamos que eu queira analisar os dados do cliente. Por que eu iria querer extrair dados de várias tabelas usando instruções de junção se posso usar um cubo ou modelo tabular, que parece onipresente, até mesmo o Excel tem essa funcionalidade. Posso usar apontar e clicar ad hoc, slicers e outras filtragens e agregações que se bem entendi são muito mais rápidas e se quiser fazer uma análise muito complexa, posso escrever consultas MDX ou cubeformulas, no exemplo do excel.
Em que as junções SQL podem me ajudar?
Você está basicamente falando sobre duas tecnologias totalmente diferentes, usadas para dois propósitos diferentes.
Um banco de dados relacional armazena informações, geralmente de maneira normalizada, mais adequada para processamento e armazenamento diários de informações de negócios. Dado esse modelo de armazenamento, você precisa de junções para... bem, juntar essas tabelas relacionais quando quiser compilar dados e propriedades dessas fontes diferentes.
Os cubos OLAP não são realmente usados para armazenar informações, mas sim duplicar e reorganizar as informações de maneira desnormalizada para permitir consultas analíticas típicas, usando MDX, por exemplo. O que torna os cubos OLAP tão rápidos é que muitos agregados são pré-calculados, e isso tem um custo potencialmente grande de armazenamento.
O Excel não é uma ferramenta OLAP confiável, imho - pode atuar como uma interface para cubos, mas se você tentar usar o PowerPivot com qualquer conjunto maior de dados, descobrirá rapidamente que sua estação de trabalho está sem memória.
Em uma nota final, eu não diria que cubos e modelos tabulares são "onipresentes". Eu diria que muitas vezes eles podem ser encontrados para aplicativos específicos do usuário final e, como tal, muitas vezes são reduzidos a um mínimo de dimensões, atributos e medidas para evitar confundir os usuários. Os usuários avançados geralmente vão diretamente ao banco de dados relacional para obter o que precisam.