New to BI and Data Warehousing. A common request we get for analytics is to provide a calculation on a rolling period. For example, we would like to see how many customers we had each month in the last 12 months. Let's say each month we add 100 customers but churn 50. Our response would look something like this:
| Month | Amount |
|---|---|
| Jan 2023 | 2,000 |
| Feb 2023 | 2,050 |
| Mar 2023 | 2,100 |
This table is later used in comparison to certain metrics like how many customers bought certain products in a given period, or what did usage look like compared to active customers. The question I have is should we create a table in our data warehouse that houses this information OR should I use CTEs in every query to generate this table in those that are needed?
The rolling period table would some times build in complexity since we would also some times want it broken down by how many users we have per certain product, location, etc. So it may look something like
| Month | State | Amount |
|---|---|---|
| Jan 2023 | CA | 1,000 |
| Jan 2023 | UT | 500 |
| Jan 2023 | FL | 500 |
So to reiterate the question: Should we create a table in our data warehouse that houses this information OR should we just use CTEs in every query to generate this table in those reports that need this?
I currently use CTEs to do this analysis but it's often the same CTEs so I figured it could be a table but not sure what best practices suggest.
Creating a view can offer several advantages in the context of your question about rolling period analysis.
Views allow you to abstract the complexity of your rolling period calculations into a single entity. This can make your queries cleaner and more readable, as users can focus on querying the view instead of dealing with the intricacies of CTEs or complex logic.