How can you design a dimensional model for self-service analytics?

Before anything else - it's all about the business questions you are trying to answer with a self-service model. Identify as many as you can and write them down. Even for a self-service model this won't capture them all but it gets you ready for the next exercises which involve the specific measures needed to answer those questions and the business dimensions that categorize them. I'll pass the ball.

If you are not familiar with the business already, it is a good idea to start with the big picture. Part of the complexity with self-service analytics is balancing current and future needs. It is impossible to predict all future questions, so personally I suggest you don't waste too much time on that. And narrow focus on known use cases might produce models that are too rigid or don't have enough information to cover future use cases (more on this below). What we do know is that analytics serves the business. Investing time into conceptual understanding of the business processes will make easier to build a model that better matches future questions and applications.

?? Understand how processes from group of selected business teams work: people during activities, each steps in activities, teams use data generated from activities, rules of quality, business evaluation metrics, goals, etc. ?? Then understand how these processes are related among it: rules of quality, business evaluation metris, data , etc. ?? Work with selected group of teams; only one team is ok: because data modeling will be evolve and improve eventually.

In my experience, I have to be honest and say that it might be too early to choose between star and snowflake at this point. Especially if we haven't decided on what dimensions and facts we will be working with. Rather than letting the schema decide your modeling, you should let the realities of your business guide the schema. But if you feel that you need to choose, I have found that it is better to start simple (star) and you can keep that simplicity at the logical level. If your particular implementation works better by further normalizing the dimension tables, you can do it at the physical level.

In my opinion; these models are "ordered" in capacity: Snowflake schema ? Star schema ? Galaxy/constellation schema. ?Start schema: All in 1 level depth (fact?dims), performance depend how handle "fact- & dims" joinning, redundant data in dims. ?? Goal: balance performance and mantainance from data modeling. ??Snowflake schema: There are dims inside dims ( more level depth), performance depend how handle "fact & dims-dims" joinning, no redudant data in dim table. ?? Goal: balance performance and mantainance from data modeling without redundant data. ?? Galaxy/constellation schema: several facts connect with dims, performance depend how handle "facts & dims" joinning. ?? Goal: balance performance and business reality from data modeling.

Here is where the time invested in understanding the business pays off. In projects I have worked on, I have adapted a simplified version of the event bus matrix promoted by Kimball and the data warehouse matrix proposed by Stagnitto and Corr. This type of matrix has led to more intuitive conversations with stakeholders that are not used to ER diagrams. The matrix also makes it easier to visually identify dimensions that are common in multiple business processes by looking at the columns. This is often a good place to start. You can then also look at the rows and select the most important business processes. This can give you insight into which fact tables to use and the granularity (more on this below)

Let the nature of the business processes help you identify the granularity. Let's use a retail example and focus on the sales process. In my experience I have often found fact tables where each row is an item in an order. Deciding on a grain at the most atomic level is a best practice because it makes it possible for analysts to self-serve in multiple ways. They decide how to aggregate and filter the data. If the granularity is at the order level (maybe based on an initial use case), further down the line the team might be interested in knowing more specific details like discounts on products within each order and will need support to ingest the data again from the transactional database to get the missing details.

?? Like you are building a data modeling to consuming for clients inside your organization, implement functional, integral, unit tests based in business requirements like max tolerancing time to get updated data in data modeling for example.

Resist the urge to try to get it all right from the start which could lead to too much time spent on design up front. At the same time, business will want to see value quickly, making an iterative approach more suitable. But I have seen this approach degenerate into hundreds or thousands of tables after multiple iterations. One way of finding a good balance between just enough planning and quick iterations is to spend time learning data modeling patterns as proven starting points. For dimensional modeling I strongly suggest "Agile Warehouse Design" by Corr & Stagnitto with multiple chapters on dimensional design patterns. For general patterns, try Data Model Patters (Hays) or The Data Model Resource Book (Silverston).