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) ))

Snow picture

It snowed here this winter. Had an opportunity to take few good pictures.

Adding certificate to your Azure Service

• The following are the steps you need to take to enable SSL
• 1. Create the certificate request. I did this with IIS 7 Manager on windows 2008. For Common name I used my company name (instead of myapp.cloudapp.net).
• 2. I used godaddy (there are several places to get this certificate) to get my certificate. You have to upload the request created in step #1 (It is just a text file).
• 3. If you are the owner of the common name used in step #1, you will get an email to approve the certificate request. You approve it.
• 4. Go back to godaddy site, download the certificate (in my case it was .CRT file)
• 5. For azure, we need .PFX file. In order to create a pfx file from the certificate we just obtained from godaddy, first we need to install this certificate in IIS 7.0. I did this my double clicking on the downloaded certificate and installed it in my personal store. Exported to a pfx (right click and select export).
• 6. In the development environment (VS 2008/VS 2010), selected the appropriate role and added this certificate.
• 7. Uploaded the PFX file to my azure account.

VS 2010 RC installation

Installed VS 2010 RC successfully. List of things to keep in mind while installing

1. Uninstall VS 2010 beta 2
2. Uninstall .NET 4.0 (there are two items related to this that you have to uninstall)
3. Any VS 2010 Office development components

If you are developing azure applications

1. Uninstall Azure SDK
2. Uninstall VS 2010 and VS 2008 tools for Azure

----------------------------------------------------------
a] Install VS 2010 RC
b] Install VS2010 RC TFS Client

c] Install Azure SDK (1.1)
d] Install Feb 2010 release of Azure tools (not NOV 2009)

VS 2010 RC and SilverLight Client Proxy with collections

I have this application. Some of the data types in server application are collections of type ObservableCollection.

Example:
public class VisitCounts : ObservableCollection[visitcount]
{
}

In VS 2008 silverlight client application generates a proxy where I can still reference ObservableCollection in e.Result (of async calls).

For whatever reason, VS 2010 RC is not able to generate proxy with these special collection types (even after tweaking the advanced options).

SSIS and MERGE statement

I encountered an error while working on a DW/ETL project.

If you are using dynamic connection manager settings in your SSIS packages, you some how set the connection string as follows

Provider=SQLOLEDB.1;Initial Catalog=MYDB;Data Source=(local);User ID=myuser;Password=myPassword;

Well this works just fine as long as you are NOT using any of the SQL Server 2008 new features.

In my case, I had to use MERGE statement and above connection string did not work. After spending couple of hours, I found out the issue. You have to make sure that, new provider will be used in your connection string. The connection string should look like this.

Provider=SQLNCLI10;Initial Catalog=MYDB;Data Source=(local);User ID=myuser;Password=myPassword;

Hope this helps....

Creating connection strings for various sources

If you are like me, you are working with lot of different providers (ODBC, OLD DB, Analysis Services Cubes etc.). I can not remember the syntax of these connection strings. Sometimes I need this connection string to use it in my C# applications, sometimes I need them to work on PerformancePoint server etc.

So far this is the best way to create the connection strings. This tool saves the connectiong string in a text file.

1. Create a text with file with extension '.udl' and save the empty file.
2. Double click this file and enjoy....

Buttons with in a ListView and Animation

I would like to make the buttons bigger when user hovers the mouse on top of these buttons. The bitmap effects are working as desired. But, width and height setters are not working.

WPF - Command and User Interface

WPF is built from ground up. After trying out different scenarious, i am starting to beleive that WPF will let us seperate the UI and business logic (provided your design is sound).

I have built some prototypes using DM-VM-V pattern. This lets us seperate the UI and business logic. However I am seeing the following exceptions.

Here is the scenario.

I have a ListView with few rows and my VM has three commands

Add
Delete
Update

1. Delete and Update works very well. Your command deletes the record from your server. Assuming VM has some kind of call back interface implemented to refresh its collection, UI refreshes itself

2. Add
When uses clicks on 'Add', I would like to bring up a dataentry window ( say NewCustomerWindow). Where should the logic of creating/displaying should recide? I do not want my command to know about this page (in addition I do not want to hardcode this window either). There must be a way out there to specify this declaratively in the XAML. I have not found it yet.