A table in my model records building valuations over time. Is it a slowly-changing dimension table or a fact fable?
I'm building a data model for a report that allows users to analyze building valuations over time, and details about buildings and their current leases.
I have a fact table that contains leasing
You have two fact tables that differ only in terms of granularity. Your Fact_Leases table, for example, is a fact table at the granularity of a lease. I can assume this quite safely because it appears the Lease ID column is a primary key. Each row of that table represents a lease.
On the other hand, your ?_Valuations table is a fact table at the granularity of quarter-time-building. That is, each row not only represents a building but also a quarter time period. And one way you can sort of know that this is a fact table is by understanding that if you had a date-dimension table, you could relate the two on their Quarter columns (although it would be a many-to-many relationship). Therefore, your date-DIMENSION table would be explaining the facts of your valuations. (I'd recommend, however, replacing your Quarter column with actual dates, and allow the date-dimension table to inform the quarters. That's an aside, though.)
Now, the problem of repeating valuation metrics occurs because you are trying to combine two fact tables at different levels of granularity. When you try to apply the valuations to the Fact_Leases table, which is at the granularity of lease, Power BI (or any BI tool, for that matter) can't understand how to apportion the valuation at the BUILDING level down to the LEASE level of granularity. So it just repeats. And it's important to keep this in mind when developing your reporting. No visualizations built at the context level of lease will be able to include a valuation metric because valuations exist only at a higher level of granularity.
A Database Model for an Online Survey. Part 4
In this final article in a four-part series, I complete the design for an online survey database to provide flexibility for multiple surveys, question re-use, multiple choice answers, ordering of questions, conditional jumps in the survey based on responses, and control over the users' access to surveys via groups of survey owners.
Data Modeling - Relational Databases (SQL) vs Data Lake (File Based) - Confessions of a Data Guy
Data Modeling is a topic that never goes away. Sometimes I do reminisce about the good ol’ days of Kimball-style data models, it was so simple, straightforward, just the same thing for years. Then Big Data happened, Spark happened. Things just changed. There is a lot of new content coming out around Data Lakes and […]