Your BI Tool May Be Giving You the Wrong Answers
Sr. Director, Solution Architecture and Evangelism, Sigma
2^53. What a boring way to begin a blog. But read on because that number may be sabotaging the answers you’re getting from your BI tool and you don’t even know it.
~ 9 quadrillion
This raises two interesting questions:
Why would a number ever be larger than 2^53?
What happens when the number is larger?
The answer to the first question is that it likely wasn’t in the early days. Fast forward to today and we find many enterprise applications regularly encounter large integers. One example is ID values that need to be globally unique, are auto-generated, and are most efficiently stored as integer data types.
If not handled correctly, rounding large numbers in a BI tool impacts rendering, filtering, aggregation, and row-wise updates. In short, you get inaccurate results. Let’s explore each one of these to see how.
Rendering means showing the number on the screen in the browser. For example, if a query returns the value “4” but shows “2” on the screen, that’s clearly wrong. This is what’s happening but for much larger numbers. If you observed this happening in your BI tool, you’d quickly lose trust in all future results.
Filtering can also break for similar reasons. If you see a value on screen that’s wrong and you filter on that value, it will either return zero rows or the wrong rows.
Sigma pushes compute down to the CDW
Finally, if your BI tool supports editing cell level data like Sigma’s newly introduced Warehouse Data Editing (WDE), but doesn’t handle large integers correctly, cell updates will fail. Updates rely on a primary key and these are often large integer IDs.
Correctness is paramount. You should always scrutinize the results in your BI product. The next time you see a large integer (such as an ID), do a spot check against the source data. Rest assured, if you are using Sigma this is handled correctly!