BI SQL Server 2008 and NTILE

BI is all about converting data into 'information' . Even after this effort, you need to filter out noise from information before it is presented to the end user.

For example, every financial BI solution will have some sort of 'Balance' amount. What this means is, this money is owed to you, but not paid yet.

Instead of showing every row in your balance (fact) table, you can categorize the content coming from this row as 'Low', 'Medium' and 'High'. And when higher level people (decision makers) are looking at the information, I will just show the information that belong to 'High' category.

How do I do this at the data access layer?

SQL Server 2008 has NTILE functionality.

SELECT service_date, balance_amount,

CASE NTILE(3) OVER(ORDER BY balance_amount, service_date)

WHEN 1 THEN 'low'

WHEN 2 THEN 'medium'

WHEN 3 THEN 'high'

END AS lvl

FROM fact_balance

ORDER BY balance_amount, service_date;