In one of my dashboards, I faced performance and inaccuracy issues. Even though my data was clean, the report became slow when I opened it. When things run slowly, people don’t just want to know what’s wrong—they want to understand why.
To improve performance, I analyzed the data and found issues related to data cardinality and granularity mismatches caused by the star schema design. Here’s how I corrected these issues and improved my dashboard’s performance by up to 48%, while also improving accuracy.
LOW DATA CARDINALITY
I worked with a very large fact table that had millions of records where a column called shipment_status stored values like Pending, Shipped, Delivered, and Returned. Since this column had only a few possible values, many rows contained the same data.
I created a normal index on this column to improve performance, but instead of becoming faster, the query became slower. This happened because the column had low data cardinality, meaning the same values were repeated many times. Because of this, the database did not use the index and scanned the entire table, which took more time.
To fix the problem, I removed the normal index and applied three better solutions:
- Bitmap indexing Instead of a normal index, I used a bitmap index, which works better when a column has only a few values (like Pending or Delivered). This helped the database find rows much faster.
- Splitting the table by date (partitioning) The table was very big, so we split it into smaller parts based on date. Now, when we search for recent data, the database checks only that part of the table, not the whole table.
- Filtering using unique columns first I first filtered data using columns like date or customer ID, which have many different values. This reduced the number of rows early, so checking shipment_status became faster.
After applying these changes, the queries ran much faster and report performance improved significantly.
GRANULARITY MISMATCH
Also I faced a Granularity mismatch between Marketing Expenses and Sales Transactions. The Marketing table recorded costs for each month and each marketing campaign, while the Sales table recorded every single sale each day. When I tried to calculate how much profit we got from marketing, the numbers did not match. Joining the tables directly caused the marketing costs to be copied for every sale, making it look like we spent much more than we actually did.
To fix the problem, I applied three better solutions:
- Rolling Up the Sales Data I added up daily sales into monthly totals for each marketing campaign. This made the sales data match the marketing data level and avoided duplicate counting.
- Implementing a Linkage Key I created a common Campaign ID column in both tables. This allowed the database to join the tables correctly without duplicating rows or losing data.
- Splitting Monthly Budget Across Days For daily reports, I divided the monthly marketing budget into daily amounts based on past sales patterns. This made the numbers correct for each day.
After applying these changes, the reports became accurate
Although the Star Schema is widely considered a good design, I faced challenges in my projects. Even with clean and organized data, reports can become slow and inaccurate.
Problems like low data cardinality and granularity mismatch are just two issues I found the major issues in Star schema, let me do some more research whether I can improve my dashboard performance more.
Hope you found this useful. Follow me on Linkedin Jayasri Balakrishnan for more Business Intelligence insights!
