So you’re ready to roll out your dimensional data model and looking for ways to put the finishing touches on it. These 6 tips will help you take your data model to the next level so it can handle almost any question your business users throw at it. I’ll also share the ways Modern BI platforms that make it even easier to bulletproof your data model, without the hassle of these manual steps.
(In case you’re just starting out with your data modeling exercise, check out this quick guide.)
1. Always build a dimensional model around a business process.
After all, most questions are related to measurements of business processes like purchases, phone calls and orders. This strategy will make it easier for business users to navigate the data model and ultimately ask the right questions.
2. Use a date dimension table.
Date dimensions make it easy to add custom date attributes like holidays and fiscal quarters that can be used across the entire organization. Here’s a quick guide to get you started.
3. Make sure all rows in your fact table are at the same level of granularity.
Mixing daily and weekly aggregates in the same table can yield unexpected results for end users.
4. Create conformed dimensions where possible.
Conformed dimensions are dimension tables (like a date dimension table) that are shared across the organization. They allow you to do analysis across multiple fact tables and reduce management overhead and data redundancy.
5. Build summary views ahead of time.
If you know end users want to see weekly or monthly metrics and expect lightning fast performance, build these summary tables as part of your data model and save them the hassle upfront.
6. Handle schema complexity upstream.
This probably goes without saying, but complex data transformations and calculations are best handled in the database or ETL layer. It will help ensure consistency and performance across departments and take pressure off of BI tools which aren’t built to handle complex schemas.
While these tips will make your data model more flexible to change, ThoughtSpot eliminates the need for many of these steps in the analytics pipeline. ThoughtSpot’s Relational Search Engine can aggregate billions of rows of raw data and calculate custom metrics on-the-fly. This means you don’t need to worry about creating summary views or metrics outside of the BI environment. For example, business users can analyze weekly metrics for last fiscal quarter simply by typing in “weekly last quarter”.
ThoughtSpot’s BI & Visualization Server also makes it easy to use conformed dimensions to do analysis across any number of data sources without getting inflated results. To learn more about how ThoughtSpot simplifies the data modeling process and can accelerate time to insights, check out our 30-day free trial.
