Monthly Archives: September 2011

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;

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 different ways by which people populate such facts. I found the following method to be very useful.
I used 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) ))

Working with WebSphere MQ 7.x and WCF

Publish-Subscribe is such a powerful and useful pattern. IBM’s MQ is one of the best implementation of queue and broker on top of it. This post in intended to help people from the .NET world who are trying take advantage of MQ in their applications.

You can create WCF applications with MQ endpoint, just like MSMQ endpoint. But MQ 7.x offers lot more.

1. All the required components for WCF and MQ 7.x are installed.

2. You have to provide enought information to svcutil to be able generate proxies. In order to do this, you have to edit the configuration file of svcutil.exe. This file can be found in the following folder:

C:Program Files (x86)Microsoft SDKsWindowsv7.0ABin and name of file is: SvcUtil.exe.config. After making the changes it should like this:

3. Build your server application just like you did before. Your configuration for this server should look like this:

4. Now it is time to generate the proxy for the clients to use this service. Make sure that your server application is running. In your client application project choose to add service reference and provide the server URL. This will create some entries in the app.config. But, some entries will be missed and you have to add them manually. Finally, after making those manual edits, configuration file should look like this:

5. For my test application I created a queue manager called ‘QM1’ and a queue called ‘SampleQ’