If we are using dimensional data model, make sure to build atomic grain base fact tables. It is true that they takes more space and performs slower than aggregated fact table. We may build aggregated fact table on-top to improve the performance but don't skip the atomic grain base fact table. It can partly act as the single version of truth in your solution. The atomic grain base fact table is also likely reducing the efforts to rebuild everything from zero again if any future changes.
For experience sharing, I had been suffered from it in a two phases project. In the phase one, we needed to deliver the reports in the facilities summary level. And required to provide the detail level reports (e.g. by facility by product) in phase two. I joined the development team in the middle of phase one and found that they are building the aggregated fact table skipping the base fact table. It means that we had to rebuild the atomic grain base fact table from zero in phase two. So, we required to redo nearly all the ETL processes.
We always recommend to start from the base fact table as atomic grain.
No comments:
Post a Comment