Business Intelligence – 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: