Oracle Date Numbers to SQL Server Datetime

File this under ‘blogging so I never have to recall this from memory’.  One of my current clients is extracting data from an Oracle database to be loaded into a SQL Server Analysis Services (SSAS) cube.  Of course, the date columns contain floats, representing the number of days since January 1, 1900 in the integer portion and the fraction is the portion of the date (time of day) for this datetime.

Fortunately, we are only concerned with the date grain of data, so I am able to use dateadd(day, <<column with oracle>>,’1/1/1900’) to calculate a SQL Server date time (this is a 2005 project).

File Under SSAS Demo Creation

In the course of creating a demo for a potential client, my team made the following observation. While the time dimension is always crucial to a BI solution, for a laptop portable demo stay away from models that incorporate two (or more) time dimensions. For example, in Hospitality refrain from using a model with Check-In and Check-Out dates, instead consider using Check-In Date (or Check-Out Date) and a measure indicating the interval, for example Nights. This helps avoid a sparse cube with few aggregatable date pair combinations.

Obviously, in a production environment the client may have valid reasons to employ both a Check-In and Check-Out date dimension role. However, production hardware deployment won't have the constraints of an all-up demo environment executing SQL Server, SharePoint and PerformancePoint an a single (laptop) machine.