- •Contents at a glance
- •Contents
- •Introduction
- •Who this book is for
- •Assumptions about you
- •Organization of this book
- •Conventions
- •About the companion content
- •Acknowledgments
- •Errata and book support
- •We want to hear from you
- •Stay in touch
- •Chapter 1. Introduction to data modeling
- •Working with a single table
- •Introducing the data model
- •Introducing star schemas
- •Understanding the importance of naming objects
- •Conclusions
- •Chapter 2. Using header/detail tables
- •Introducing header/detail
- •Aggregating values from the header
- •Flattening header/detail
- •Conclusions
- •Chapter 3. Using multiple fact tables
- •Using denormalized fact tables
- •Filtering across dimensions
- •Understanding model ambiguity
- •Using orders and invoices
- •Calculating the total invoiced for the customer
- •Calculating the number of invoices that include the given order of the given customer
- •Calculating the amount of the order, if invoiced
- •Conclusions
- •Chapter 4. Working with date and time
- •Creating a date dimension
- •Understanding automatic time dimensions
- •Automatic time grouping in Excel
- •Automatic time grouping in Power BI Desktop
- •Using multiple date dimensions
- •Handling date and time
- •Time-intelligence calculations
- •Handling fiscal calendars
- •Computing with working days
- •Working days in a single country or region
- •Working with multiple countries or regions
- •Handling special periods of the year
- •Using non-overlapping periods
- •Periods relative to today
- •Using overlapping periods
- •Working with weekly calendars
- •Conclusions
- •Chapter 5. Tracking historical attributes
- •Introducing slowly changing dimensions
- •Using slowly changing dimensions
- •Loading slowly changing dimensions
- •Fixing granularity in the dimension
- •Fixing granularity in the fact table
- •Rapidly changing dimensions
- •Choosing the right modeling technique
- •Conclusions
- •Chapter 6. Using snapshots
- •Using data that you cannot aggregate over time
- •Aggregating snapshots
- •Understanding derived snapshots
- •Understanding the transition matrix
- •Conclusions
- •Chapter 7. Analyzing date and time intervals
- •Introduction to temporal data
- •Aggregating with simple intervals
- •Intervals crossing dates
- •Modeling working shifts and time shifting
- •Analyzing active events
- •Mixing different durations
- •Conclusions
- •Chapter 8. Many-to-many relationships
- •Introducing many-to-many relationships
- •Understanding the bidirectional pattern
- •Understanding non-additivity
- •Cascading many-to-many
- •Temporal many-to-many
- •Reallocating factors and percentages
- •Materializing many-to-many
- •Using the fact tables as a bridge
- •Performance considerations
- •Conclusions
- •Chapter 9. Working with different granularity
- •Introduction to granularity
- •Relationships at different granularity
- •Analyzing budget data
- •Using DAX code to move filters
- •Filtering through relationships
- •Hiding values at the wrong granularity
- •Allocating values at a higher granularity
- •Conclusions
- •Chapter 10. Segmentation data models
- •Computing multiple-column relationships
- •Computing static segmentation
- •Using dynamic segmentation
- •Understanding the power of calculated columns: ABC analysis
- •Conclusions
- •Chapter 11. Working with multiple currencies
- •Understanding different scenarios
- •Multiple source currencies, single reporting currency
- •Single source currency, multiple reporting currencies
- •Multiple source currencies, multiple reporting currencies
- •Conclusions
- •Appendix A. Data modeling 101
- •Tables
- •Data types
- •Relationships
- •Filtering and cross-filtering
- •Different types of models
- •Star schema
- •Snowflake schema
- •Models with bridge tables
- •Measures and additivity
- •Additive measures
- •Non-additive measures
- •Semi-additive measures
- •Index
- •Code Snippets
information is further used as a filter to perform an analysis of their behavior over time.
You have seen the following important points so far:
A snapshot makes perfect sense when you want to freeze the calculation. In this example, you wanted to focus on customers with a given ranking in one specific month. The snapshot offers you an easy way to do this.
If you need to filter the snapshot date but you don’t want this filter to propagate to the model, you can keep the table unrelated and use INTERSECT to activate the filter on demand.
You can use the snapshot as a tool to compute a filter over the customers. In the example, you wanted to examine the behavior of those customers in other periods of time.
One of the interesting aspects of the transition matrix is that you can use it to compute more complex numbers.
Conclusions
Snapshots are useful tools to reduce the size of a table at the price of granularity. By pre-aggregating data, your formulas will be much faster. In addition, as you have seen with the transition matrix pattern, you open a whole set of analytical possibilities by snapshotting the data. With that said, a snapshot comes with an increased complexity in the model. This chapter explored the following important points:
Snapshots almost always require aggregations other than a simple sum. You must carefully analyze the kind of aggregation you require or, at worst, fully avoid subtotals.
The granularity of snapshots is always different from the granularity of normal fact tables. You must take this into account when building reports, as speed comes with limitations.
You can often avoid derived snapshots if your model is not too large. Use derived snapshots as a last resource if optimizing your DAX code does not lead to acceptable performance.
As you saw with the transition matrix, snapshots open new possibilities in the analysis of data. There are many more possibilities you can explore, depending on the kind of business you need to analyze.
Using snapshots is not easy. This chapter provided some simple and advanced scenarios. We suggest you learn the simple scenarios, spend some time thinking
about how you can benefit from the harder ones, and move slowly through the use of snapshots and transition matrixes. Even seasoned data modelers will find it hard to author some of the code in this chapter. Nevertheless, if needed, transition matrixes are extremely powerful for grabbing insights from your data.