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;

TFS 2010 RTM installation and Reporting services Error

When you try to install TFS 2010 RTM, you may get the following error.

You need to edit rsreportserver.config file located at

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer and change the value of 'SecureConnectionLevel' to 0 (zero)

VS 2010 RTM and Silverlight 4

I am not sure what is causing this. When I wanted to change silverlight target version from 3 to 4, I was getting the following error:

In order to fix this problem, you need to change registry entry from V3 to V4 at

x86

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\10.0\DesignerPlatforms\Silverlight

x64

HKLM\Software\Wow6432Node\Microsoft\VisualStudio\10.0\DesignerPlatforms\Silverlight

Azure and .NET 4.0

After downloading latest VS 2010, I thought I will make my azure projects to target .NET 4.0

Looks like it is not supported yet.

I see the following compiler error:

Cloud Service projects currently support Roles that run on .NET Framework version 3.5.

Please set the Target Framework property in the project settings for this Role to .NET Framework 3.5

Data warehouse: Populating fact table with multiple date keys

In any practical data warehouse, there will be always few fact tables which will have more than one date key (order date, shipped date for example).

There are many ways by which people populate such facts. Here is what did today in one of my projects:

I am using Kimball's spread sheet to populate the date dimension. So, surrogate keys are kind of 'smart' (I know , I know you don't want surrogate keys to be smart ...)

For example key for '12/5/2009' will be '20091205'.

Assuming this is the scheme you are using for date dimension surrogate keys, you can use the following script to load the fact table

ORDER_DATE_KEY = RTRIM(LTRIM(STR(DATEPART(yyyy,@myDate)) +

RTRIM(CASE WHEN Month(@myDate) <>

THEN '0' + CONVERT(Char,DATEPART(Month,@myDate))

ELSE CONVERT(Char,DATEPART(Month,@myDate)) END) +

RTRIM(CASE WHEN Day(@myDate) <>

THEN '0' + CONVERT(Char,DATEPART(dd,@myDate))

ELSE CONVERT(Char,DATEPART(dd,@myDate)) END) )),

SHIP_DATE_KEY = RTRIM(LTRIM(STR(DATEPART(yyyy,@myDate2)) +

RTRIM(CASE WHEN Month(@myDate2) <>

THEN '0' + CONVERT(Char,DATEPART(Month,@myDate2))

ELSE CONVERT(Char,DATEPART(Month,@myDate2)) END) +

RTRIM(CASE WHEN Day(@myDate2) <>

THEN '0' + CONVERT(Char,DATEPART(dd,@myDate2))

ELSE CONVERT(Char,DATEPART(dd,@myDate2)) END) ))