How do you incorporate hierarchies and drill-downs in a star schema?

Another great example is Business Units, divisions and regions. Creating a hierarchy on geography allows users at different levels in organizations to view data customized to their contexts.

In recent years the availability of storage and processing power has reduced the need to create smaller sets of data which inherently increase complexity when creating models. The date dimension noted here is a great example of when snowflaking can be avoided as the width of the table will not create inefficiencies and grouping into levels will generally follow best practices of many of today’s technologies. There are times where snowflake models will allow for the ability to meet the business’ needs while adhering to technology best practice, such as creating multiple levels where unique values aren’t easily created or when changes over time need to be represented in reporting.

Hierarchies tend to be very exhausting when legal entities merge. For example when one app development startup is acquired by another company, they need to roll up the data points from different leaf nodes and attribute them to interim levels. Using the conventional normalized hierarchy is helpful though merging the data into the existing facts can be very tedious process. This still allows quick delivery of essential metrics. There is no easy path but normalization is pretty helpful and allows extensibility whereas denormalized dimension would have to be rebuilt.

Continuing on the example of geography, a degenerate id would be like region-key-id, bu-id and so on. So when reporting for BU level info, bu-id can be used for joins and so on for each key col.

Architectural considerations, complexity, and the tools available will inherently alter what your technology landscape will look like. Mature warehouses will typically take advantage of a data lake to store replicas of source transactional systems (especially if cloud sources are present), a warehouse where partially transformed data and metadata are stored, as well as various data marts where data are ready for end user consumption. This setup will also provide value to different clients with different levels of data literacy (i.e., a statistician can explore data in a lake while a decision maker can leverage canned reporting). Temporary tables are more likely in setups with limited access to data transformation technologies.

In addition to the already excellent points one thing to highlight is that most of the heavy lifting should be done in the prior steps (design, ETL/ELT) rather then relying on your reporting tool. For example MDX and DAX are Microsoft specific (Power BI) and while these languages are wonderful in their own respect if your company suddenly decides to switch to another reporting tool such as Tableau, Oracle or even using Python then any massaging using MDX/DAX would have to be recreated if the functionality even exists in the new reporting tool language. An analogy would be most of the prep and cooking should be done before reaching your table (reporting). Once its at the table you can add pepper and salt, but minimize any heavy manipulation.