Comment pouvez-vous utiliser des modèles dimensionnels pour améliorer les décisions commerciales ?

A well built dimensional model can be the cornerstone of long term growth and success for an organization. The unique ability to afford complex analysis and trend discovery without the need for complex technical skills can help key stakeholders drive critical business decisions with confidence. If setup properly, the same model can also be utilized for validation of decisions and long term tracking of A/B testing results. For the technical analytical team and partners, the model should provide unrivaled performance and efficiency when conducting highly complex reviews, while also ensuring auto ingestion of new dimension items as created; limiting time to market for any new or changed viewpoints and categories.

Costumo dizer que são bancos de dados otimizados para consulta (olap), enquanto os bancos de dados transacionais são otimizados para inserções, deleção e atualização (oltp). Sempre reforço a questão, consultas analíticas devem ser feitas em um modelo dimensional, obedecendo o conceito! Pois já vi diversos profissionais dizendo que usam uma modelagem dimensional e na verdade não! Para estes, recomendo a leitura do Kimball e do Inmon!

Dimensional models enhance business decisions by organizing data into a structure that aligns with business processes. They provide a clear, intuitive view of information, facilitating better analysis and decision-making. By incorporating dimensions like time, geography, and product categories, businesses gain insights into trends, patterns, and relationships. This model simplifies complex data, enabling users to easily query and extract relevant information. Additionally, dimensional models support scalability, making it easier to adapt to changing business needs. Overall, they empower organizations to make informed decisions, optimize processes, and respond more effectively to market dynamics.

Dimensional models organize and structure data for easier analysis and insight. They are useful for large amounts of data and multiple dimensions. Some of the benefits are as follows: - Query performance: faster and better for complex queries. - Data models: simpler and clearer for users and developers. - Report and analysis: more flexible and detailed for business questions.

Query Performance: Designed for optimal query performance, dimensional models support faster and more efficient retrieval of data, especially in analytical scenarios common in business intelligence. Flexibility : Dimensional models are adaptable to changes in business requirements. It is easier to add or modify dimensions and measures without significant disruption to existing structures. Aggregations: Dimensional models inherently support aggregations and summarization of data, facilitating the generation of reports at various levels of granularity. Ease of Understanding: Dimensional models are intuitive and closely mirror how business users perceive and understand data, making it easier for them to navigate and analyze information.

Tabelas fato, costumo explicar que contém fatos que aconteceram (uma compra, uma venda..) Já as dimensões, são como desejo detalhar (explodir) o fato em questão (por data, por cliente, por estado) Outra observação em relação a taba Fato, ela deve conter dados numéricos, datas e chaves para as dimensões! Uma tabela fato com o nome do cliente não é um fato! #dica

A dimensional model has 2 major components. 1 or more fact tables 1 or more dimension tables. Fact tables are tables with records of transactions. They typically would have columns that can be meaningfully aggregated. Dimension tables house data attributes. These attributes are the 4Ws. The who, what, when, and where attributes. These put together and connected using the common key columns creates a dimensional model

Categorical Attributes: Dimensions are descriptive data elements that provide context to the measures. Examples include time, geography, customer, and product. Dimensions are often hierarchically organized, allowing for easy navigation and analysis.

Details of Dimensions: Attributes provide additional details or characteristics about a dimension. For example, a "Time" dimension may have attributes like day, month, quarter, and year.

Em relação aos modelos, sempre comento dos tradicionais Star Schema e Snowflake. Star Schema, é composto de uma tabela fato rodeada de dimensões (formando uma "estrela") Já o Snowflake, é o Star Schema permitindo que dimensões tenham outras dimensões associadas! Costumo explicar assim de uma forma mais simples! Mas vai bem além disso!

1. Star Schema: Central fact table surrounded by dimension tables. Dimensions directly linked to the fact table. Simplified structure for ease of use and performance. 2. Snowflake Schema: Central fact table linked to dimension tables, but these dimension tables may further normalize into sub-dimensions. Hierarchical structure with more complex relationships. 3. Galaxy Schema: Multiple fact tables share dimension tables. Enables more complex relationships and analysis across different business processes.

There are 3 major schemas for dimensional models. Schemas are the pictorial representation of how the model is structured from the relationships that exist between the fact(s) and dimension(s) tables. 1. Star - a fact and multiple dimensions 2. Snowflake - a fact and multiple dimensions with dimensions falling off other dimensions as offshoots. 3. Galaxy or conformed - multiple facts comformed to speak to multiple dimensions

Dimensional models organize and structure data for easier analysis and insight. They are useful for large data and multiple dimensions. There are two types: - Star schema: A single fact table connected to multiple dimension tables, forming a star-like shape. Easy, fast, and flexible. The relationship model used in this schema is usually one to many. - Snowflake schema: Some dimension tables normalized into sub-dimension tables, forming a snowflake-like shape. Reduces redundancy and improves quality, but increases complexity and slows queries.

Handling Changes in Dimension Data Over Time: SCDs manage changes in dimension data over time. There are different types of SCDs, such as Type 1 (overwrite), Type 2 (add new version), and Type 3 (add a new attribute).

6 steps actually if you are building it out of a single flat denomalized table 1. Identify the table which has all the fields related to the business requirements for the project. 2. Write out all the column headers from the table on a paper 3. Split the column headers into values(typically columns that can be aggregateed) and reporting items (columns that can't be aggregated) 4. Look at the reporting items and out together those that are related into the same bucket. These naturally forms your dimension tables. 5. from (4), each buckets, identify the column with the lowest level of detail. This becomes your key to that table. 6. collate all the keys from (5) in addition to the values in (3) to create uour fact table.

O projeto de modelagem pode ser projetada de algumas maneiras, esse é um fator primordial! Como mencionado no artigo, as 4 etapas são fundamentais para o entendimento e projeto! Porque, a modelagem vai variar muito dependendo da necessidade! Muitos me perguntam, qual o melhor modelo, sempre digo, o que atende sua necessidade! Não existe melhor nem pior, existe o que lhe atende! Aqui sempre já penso na próxima etapa, aproveite a etapa 1, pra já ver com as áreas, qual a relação delas com as demais áreas da empresa! E já começo pensando em como podemos começar, montando um todo(dw)? Ou ir montando por partes (dm)?

Tudo começa pelo requisito, saber quais são os principais indicadores, como a área de negócio acessa essa informação atualmente, se existe trabalho manual para chegar nesta informação, mapear as fontes de dados, com base nisso o modelo de dados é criado e uma vez validado pelo cliente se durante a implementação o cliente questionar se falta algum indicador ou alguma tabela e esta informação não estiver no modelo de dados, esta demanda entra como melhoria após a entrega do projeto.

Dimensional models organize and structure data for easier analysis and insight. Steps to design them are: - Identify business requirements: Engage with stakeholders and end-users to gather insights into their reporting and analysis needs. - Identify grain: Define the level of detail at which the facts are captured. - Identify dimensions: Identify the characteristics by which you want to analyze your data. - Identify measures: Identify the numeric values that provide quantitative information about the business. - Define dimensional hierarchies: Identify and define the hierarchical relationships within each dimension. - Design fact tables: Design the fact tables based on the grain, dimensions, and measures.

Depois de montado falo aos usuários consumidores, façam seu show! Mas sempre, no momento da entrega, forneço um treinamento ao usuário! Explicando tudo de forma detalhada e deixo documentado! Aqui reforço sempre a importância do feedback, tá fazendo sentido, precisa de mais informações? Como tá a performance?

I would strongly recommend reading book "The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling" by Ralph Kimball , Margy Ross to get full understanding of dimensional modeling and its benefits

Disadvantages of Dimensional Modeling: Data Redundancy: Denormalization in dimensional models can lead to some degree of data redundancy, as certain information may be duplicated across different dimensions. Complexity in Maintenance: Over time, as business requirements evolve, maintaining and updating dimensional models can become complex. Potential for Inconsistency: Inconsistencies can arise if changes to dimensions or measures are not carefully managed, potentially leading to discrepancies in reporting. Resource Intensive: Building and maintaining dimensional models may require substantial computing resources, particularly in environments with large volumes of data and complex structures.

Dimensional models enhance business decisions by structuring data for intuitive analysis. Incorporating dimensions like time or product, these models provide comprehensive views of operations. By leveraging measures and dimensions, businesses can gain insights into trends and relationships within their data. This approach facilitates a deeper understanding of performance metrics. In summary, dimensional models support informed decision-making through versatile and holistic data exploration.